Optimizer queries to fetch the execution paln

Optimizer Known Records during Execution
*********************************************************************************
col savtime for a40
col object_name for a40
select savtime,owner,object_name,rowcnt,blkcnt From sys.WRI$_OPTSTAT_TAB_HISTORY w, dba_objects o
where o.owner='&owner' and o.object_name='&Obj_name' And o.object_id = w.obj#
order by o.owner, o.object_name,w.savtime;


*********************************************************************************
Blocked Session
-------------------
set lines 200 pages 1010
spool block_session.log append
COLUMN sess format A15
col BLOCK for 999
col lmode for 999
col CTIME for 9999
col REQUEST for 999
col USERNAME for a9
col STATUS for a8
col ID1 for 999999999
col ID2 for 9999999
col SERIAL# for 99999
col event for a20
select systimestamp from dual;
SELECT a.inst_id,substr(DECODE(request,0,'Holder: ','Waiter: ')||a.sid,1,15) sess, b.serial#, a.type, a.id1, a.id2,
        a.lmode, a.request,a.block, a.ctime, b.username, b.status, b.sql_id, b.event, b.ROW_WAIT_OBJ# from    gv$session b,
        (select distinct b.*
        from    gv$lock a,
                gv$lock b
        where   a.id1 = b.id1
        and     a.id2 = b.id2
        and     a.request > 0) a
where   a.sid = b.sid
and     a.inst_id = b.inst_id
order by a.id1, a.id2, a.block desc, ctime
/
select * from gv$access where object='PL_IMAGE_ITEM_GROUP';
SELECT * FROM V$SESSION WHERE STATUS = 'ACTIVE'
SELECT * FROM V$OPEN_CURSOR WHERE SID = --
SELECT s.inst_id, O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
 S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT
 FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, gV$SESSION S,
 V$PROCESS P, V$SQL SQ
 WHERE L.OBJECT_ID = O.OBJECT_ID
 AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR
 AND S.SQL_ADDRESS = SQ.ADDRESS;
SELECT
    O.OBJECT_NAME,
    S.SID,
    S.SERIAL#,
    P.SPID,
    S.PROGRAM,
    SQ.SQL_FULLTEXT,
    S.LOGON_TIME
FROM
    V$LOCKED_OBJECT L,
    DBA_OBJECTS O,
    V$SESSION S,
    V$PROCESS P,
    V$SQL SQ
WHERE
    L.OBJECT_ID = O.OBJECT_ID
    AND L.SESSION_ID = S.SID
    AND S.PADDR = P.ADDR
    AND S.SQL_ADDRESS = SQ.ADDRESS;


ActiveEvents
*********************************************************************************
select to_char(sysdate,'DD-MON-YY HH24:MI:SS') dt, inst_id, count(1) tot, count(case when username is null then 1 end) bg,
count(case when username is not null then 1 end) wbg,
count(case when status='ACTIVE' and username is not null then 1 end) act_user from gv$session group by inst_id
/
---------------------------------------------------
set lines 165 pages 1000 time on
spool wait_event07NovEvening.log append
set heading on
col inst for 99
col spid for a11
col username for a16
col osuser for a14
col event for a30 trunc
col inst for 99999
col sid for 99999
col et for 9999999
col dt for a20
col system_date for a18
col module for a18 trunc
col blocker for 999999
col bs for 99999
col Bi for 99
col rr for 99
col obj for 9999999
col wt for 99
select systimestamp from dual;
select s.inst_id as inst,s.sid as sid,p.spid as spid,s.username,s.status,s.sql_id,s.event,s.module,s.last_call_et as "et(s)",s.blocking_session bs,s.
row_wait_obj# as obj,BLOCKING_INSTANCE as Bi,ROW_WAIT_ROW# as rr, s.wait_time WT
from gv$session s,gv$process p
where wait_class !='Idle'and s.paddr = p.addr and s.inst_id=p.inst_id
order by inst,event
/
---------------------------------------------
select sql_id,event,count(*) from gv$session where event ='gc cr request' group by sql_id,event order by 3
--------------------------------------------------
set lines 1000
column module format a40
column event format a40
select inst_id, sid, serial#, username, status, sql_id, module, to_char(logon_time,'DD-MM HH24:MI') logon, (sysdate-logon_time)*24*60*60 diff, last_call_et lce, event from gv$session where wait_class !='Idle' and username is not null and username not like 'SYS' AND STATUS='ACTIVE'
---------------------------------------------------
select program,module,event,sql_id,prev_sql_id,status from gv$session where STATUS='ACTIVE' AND TYPE !='BACKGROUND';
----------------------------------------------------
select module,count(1) from gv$session group by module order by 2 desc;
oracle@HBFCPRDDB2:/home/oracle/amit$ cat lo.sql
set linesize 132
col target for a60
col "SID" for 99999
SELECT SID,  target || opname target , TOTALWORK, SOFAR, TIME_REMAINING, ELAPSED_SECONDS FROM V$SESSION_LONGOPS
where time_remaining > 0
order by time_remaining
/
-----------------------------------------------------
set lines 250 pages 9999
select inst_id,event,count(1) from gv$session where wait_class != 'Idle' group by inst_id,event order by 1,2 desc;
---------------------------------------------------
select inst_id,status,count(1) from gv$session where wait_class !='IDLE' and username is not null group by inst_id,status order by 1,2
----------------------------------------------------
select inst_id,count(*) from gv$session where status='ACTIVE' group by inst_id;
------------------------------------------------------
select sid,serial#,username,osuser,event,sql_id,p1,p2,p3,last_call_et as et from v$session where username='SYS'
---------------------

STALE STATS
************
select table_name, stale_stats, last_analyzed from dba_tab_statistics where owner='&OWNER'order by last_analyzed desc

Plan
*********************************************************************************
set lines 100 pages 2000
select * from table(dbms_xplan.display_cursor('&sid'))
select * from table(dbms_xplan.display_cursor(null,null,'iostats last +cost'));
select * from table(dbms_xplan.display_cursor('&sql_id',&child))
select * from table(dbms_xplan.display_awr('&sql_id'))
select * from table(dbms_xplan.display_awr('&sql_id',&plan_h_v));
SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &child_no, 'ALLSTATS LAST +COST +BYTES')); (/*+ gather_plan_statistics */)
select * from table(dbms_xplan.display_awr('&sql_id',NULL,NULL,'ADVANCED'));
explain plan for select * from sh.sales where cust_id=:b1 and promo_id=:b2;
select * from table(dbms_xplan.display) ;

select t.*
from v$sql s,
table(dbms_xplan.display_cursor(s.sql_id, s.child_number, '+peeked_binds')) t
where sql_id = '&1'
/
*********************************************************************************
-- Show the Plan Hash Values for a Given SQLID Over a Given Period

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300

SELECT DISTINCT sql_id, plan_hash_value
FROM dba_hist_sqlstat q,
    (
    SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap
    FROM dba_hist_snapshot ss
    WHERE ss.begin_interval_time BETWEEN (SYSDATE - &No_Days) AND SYSDATE
    ) s
WHERE q.snap_id BETWEEN s.min_snap AND s.max_snap
  AND q.sql_id IN ( '&SQLID')
/
-----------------------
Audit
*********************************************************************************
select * from DBA_PRIV_AUDIT_OPTS
union all
select * from DBA_STMT_AUDIT_OPTS;
select * from dba_audit_trail;

Hard Parsing
*********************************************************************************
set line 200 pagesize 5000
col inst_id for 99
col sql_text for a100
col first_load_time for a20
col PARSING_SCHEMA_NAME for a14
SELECT inst_id,sql_id,first_load_time,PARSING_SCHEMA_NAME, sql_text
   FROM gv$sql
  WHERE parsing_schema_name not in ('XDB', 'SYS', 'DBSNMP', 'FLOWS_030000', 'EXFSYS', 'SPOTLIGHT')
and first_load_time > to_char(sysdate-1/(24*60), 'YYYY-MM-DD/HH24:MI:SS')
/
---------------
set line 200 pagesize 5000
col inst_id for 99
col sql_text for a100
col first_load_time for a20
col PARSING_SCHEMA_NAME for a14
SELECT inst_id,sql_id,first_load_time,PARSING_SCHEMA_NAME, sql_text
   FROM gv$sql
  WHERE parsing_schema_name not in ('XDB', 'SYS', 'DBSNMP', 'FLOWS_030000', 'EXFSYS', 'SPOTLIGHT')
and first_load_time > to_char(sysdate-1/(24*60), 'YYYY-MM-DD/HH24:MI:SS')
/

Hidden Parameters
*********************************************************************************
col KSPPINM for a30
col ksppstvl for a30
SELECT a.ksppinm, b.ksppstvl, b.ksppstdf FROM x$ksppi a, x$ksppcv b WHERE a.indx = b.indx and a.ksppinm like '%&name%' ORDER BY a.ksppinm
/
--------------------

Bind Values
*********************************************************************************
select DISTINCT 'VAR ' || SUBSTR(NAME,2) || CASE WHEN DATATYPE_STRING='DATE' THEN ' VARCHAR2(20)' ELSE ' ' || DATATYPE_STRING END || ';' || CHR(10) ||'EXEC ' || NAME || ':=' || CASE WHEN DATATYPE_STRING='NUMBER' then VALUE_STRING ELSE '''' || VALUE_STRING || ''';' END "DECLARE_VAR"
from v$sql_bind_capture where sql_id='&sqlid';
select sn.END_INTERVAL_TIME, sb.NAME, sb.VALUE_STRING from DBA_HIST_SQLBIND sb, DBA_HIST_SNAPSHOT sn where sb.sql_id='4ywfmsqh97pdn' and sb.WAS_CAPTURED='YES' and
sn.snap_id=sb.snap_id order by sb.snap_id, sb.NAME;
--------------------

SQL HISTORY
*********************************************************************************
All SQL hist
----------------
select sql_id, sum(t.elapsed_time_delta/1000)/sum(t.executions_delta) " Executed(in ms)"
from   dba_hist_sqlstat t, dba_hist_snapshot s
where  t.snap_id = s.snap_id
and    t.dbid = s.dbid
and    t.instance_number = s.instance_number
and    s.begin_interval_time between trunc(sysdate)-1 and trunc(sysdate) -- yesterdays stats
and    executions_delta>0
group  by sql_id
/
-------------------------
set lines 300 pages 50000 verify off
select inst_id,sql_id,plan_hash_value, child_number, executions, buffer_gets, round(buffer_gets/decode(executions,0,1,executions)) gets_exec, disk_reads, elapsed_time/executions/1000 as etpe_ms, round(rows_processed/decode(executions,0,1,executions)) rows_exec from gv$sql where sql_id='&sql_id';
select inst_id,sql_id,plan_hash_value, child_number, executions, buffer_gets, round(buffer_gets/decode(executions,0,1,executions)) gets_exec, disk_reads,
elapsed_time/1000/1000 as "et in s", round(rows_processed/decode(executions,0,1,executions)) rows_exec from gv$sql where sql_id='&sql_id';
-------------------------
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000 avg_etime_ms,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','4dqs2k5tynk61')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
-------------------------
--Machine
--------------------
set lines 200 pages 200
col begin_interval_time for a30
--break on plan_hash_value on startup_time skip 1
select snap_id, node, begin_interval_time, sql_id, plan_hash_value, execs, avg_etime, avg_lio,m,
round(execs*avg_etime/60,0) total_time from (
select ss.snap_id, ss.instance_number node, begin_interval_time, s.sql_id, s.plan_hash_value,
nvl(executions_delta,0) execs,
round((elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000,3) avg_etime,
round((buffer_gets_delta/decode(nvl(executions_delta,0),0,1,executions_delta))) avg_lio,
S.module as m
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where s.sql_id = nvl('&sql_id','&sql_id')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0 --and plan_hash_value=3099046451
order by 1, 2, 3)
order by 2,1,3
/
---------------------------------------------------------
select to_char(min(s.end_interval_time),'DD-MON-YYYY DY HH24:MI') sample_end
, q.sql_id
, q.plan_hash_value
, q.SORTS_TOTAL
, q.SORTS_DELTA
, q.PARSING_SCHEMA_NAME
, sum(q.EXECUTIONS_DELTA) executions
, round(sum(DISK_READS_delta)/greatest(sum(executions_delta),1),1) pio_per_exec
, round(sum(BUFFER_GETS_delta)/greatest(sum(executions_delta),1),1) lio_per_exec
, round((sum(ELAPSED_TIME_delta)/greatest(sum(executions_delta),1)/1000),1) ela_exec_in_ms
from dba_hist_sqlstat q, dba_hist_snapshot s
where q.SQL_ID=trim('&SQLID')
and s.snap_id = q.snap_id
and s.dbid = q.dbid
and s.instance_number = q.instance_number
group by s.snap_id
, q.sql_id
, q.plan_hash_value
, q.SORTS_TOTAL
, q.SORTS_DELTA
, q.PARSING_SCHEMA_NAME
order by s.snap_id, q.sql_id, q.plan_hash_value;
---------------------------------------------------------
col BEGIN_INTERVAL_TIME for a30
select snap.begin_interval_time, snap.instance_number, dhs.sql_id, dhs.plan_hash_value, dhs.elapsed_time_delta, dhs.cpu_time_delta, dhs.executions_delta,
trunc(dhs.elapsed_time_delta/dhs.executions_delta/1000,2) elapsed_per_exec_ms
from DBA_HIST_SQLSTAT dhs, dba_hist_snapshot snap
where dhs.snap_id = snap.snap_id
and dhs.instance_number = snap.instance_number
and dhs.sql_id = '&sql_ID'
and dhs.executions_delta > 0
order by snap.begin_interval_time desc;
----------------------------------------------------------
column sql_text for a40 wrap
col module for a22
select sql_id,plan_hash_value,child_number, sql_text, executions, buffer_gets, buffer_gets/executions as buffer_exec,rows_processed/executions as rows_exec,
module
from gv$sql
where sql_id='&sql_id'
/
-----------------------
Average
-------
select ss.snap_id, ss.instance_number node, to_char(begin_interval_time,'DD-MON-YY HH24MI'), s.sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000 "avg_etime_(ms)",
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
(disk_reads_delta/decode(nvl(disk_reads_delta,0),0,1,executions_delta)) avg_pio,
(iowait_delta/decode(nvl(iowait_delta,0),0,1,executions_delta)) avg_iowait,
rows_processed_delta
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '&sql_id'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
------------------------
set long 90000
set lines 160 pages 2020
select sql_id, sql_fulltext from   v$sqlarea where  sql_id = '&entersqlid';
------------------------
select sql_fulltext from v$sqlarea where sql_id='&sql_id';
------------------------
select sql_id,sql_text from v$sqlarea where sql_text like '%&sqltext%';
------------------------
select inst_id, child_number, plan_hash_value, executions, buffer_gets, buffer_gets/executions as "buff_exe", rows_processed from gv$sql where sql_id='&sql_id';
------------------------
select s.inst_id,s.sid,s.sql_id,s.event,s.program,s.module,sa.executions,sa.buffer_gets/sa.executions gets,sa.ELAPSED_TIME/sa.executions elsped,sa.cluster_wait_time
from gv$session s,v$sqlarea sa where sa.sql_id=s.sql_id and s.status='ACTIVE' and s.type<>'BACKGROUND' and executions>0;
------------------------
select s.sid,s.sql_id,s.program,s.module,sa.executions,sa.buffer_gets/sa.executions gets,sa.ELAPSED_TIME/sa.executions elsped,sa.sql_text
from v$session s,v$sqlarea sa where sa.sql_id=s.sql_id
and s.sql_id='&sqlid';
-----------------------
select * from (select sql_id,sql_text,round(buffer_gets/decode(executions,0,1,executions)) gpe,executions,module,elapsed_time/1000000 ela
from gv$sqlarea order by 3 desc) where rownum <=10;

Table Details
*********************************************************************************
select owner,table_name,num_rows from dba_tables where table_name = '&name';
-----------------------
set lines 200
set pages 10000
set verify off;
col table_name for a30
select owner, table_name, partitioned, num_rows, blocks, last_analyzed,
global_stats, round(sample_size/decode (num_rows, 0, 1, null, 1, num_rows)*100,0) estimate_percent , partitioned, temporary
from dba_tables
where table_name = upper('&1');
set verify on;
-----------------------

*********************************************************************************
Index Details
*********************************************************************************
break on index_name skip 1
column column_name format a30
column column_expression for a30
set lines 120 pages 50000
select c.index_name,
        c.column_name "COLUMN_NAME", c.column_position,
        a.column_expression
from    dba_ind_columns c,
        dba_ind_expressions a
where   c.table_owner='&SCHEMA'
and     c.table_name='&table_name'
and     a.index_name(+) = c.index_name
and     a.table_owner(+) = c.table_owner
and     a.index_owner(+) = c.index_owner
and     a.table_name(+) = c.table_name
and     a.column_position(+) = c.column_position
order by 1,3
/
----------------
select owner, index_name, blevel, leaf_blocks, clustering_factor, degree from dba_indexes where owner='&owner' and table_name='&table' order by 1,2;
oracle@HBFCPRDDB2:/home/oracle/amit$ cat index _columns.sql
column column_name for a30
break on index_name skip 1 on report
select index_name, substr(column_name,1,30) column_name, COLUMN_POSITION from dba_ind_columns where TABLE_OWNER='&owner' and TABLE_NAME='&table' order by 1,3
/
----------------
select owner, Table_name, index_name, BLEVEL, LEAF_BLOCKS, last_analyzed, CLUSTERING_FACTOR, NUM_ROWS, PARTITIONED from dba_indexes where index_name='&ind_name';
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NUM_DISTINCT,NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE, HISTOGRAM from dba_tab_columns where column_name=''
------------------
*********************************************************************************
Object Details
*********************************************************************************
set lines 399 pages 1000
col owner for a20
col object_name for a30
select owner,object_name,object_type,status,object_id ,data_object_id from dba_objects where data_object_id=&obj
/
----------------
set lines 200 pages 1000
col object_name for a30
select owner,object_id,data_object_id,object_name,object_type,status from dba_objects where object_name='&object_name'
/
*********************************************************************************
REDO SIZE
*********************************************************************************
set lines 300
col module format a30
col redo for 9999999999999
select * from (
SELECT s.inst_id,s.sid, module,username,osuser, sql_hash_value, value redo, to_char(logon_time, 'dd/mm/yyyy hh24:mi:ss') lt
FROM gv$session s, gv$sesstat ss, gv$statname sn
WHERE s.sid = ss.sid
AND ss.statistic# = sn.statistic#
AND name = 'redo size'
ORDER BY redo desc
)where rownum<10
/

execute dbms_stats.gather_table_stats(ownname =>'SBIH',tabname=>'TBL_TRANSACTIONS',estimate_percent=>10,
method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',cascade=>true,degree=>16);

alter session set "_use_nosegment_indexes"=true;
CREATE INDEX "SYSADM"."PS_IDX01" ON "SYSADM"."PS_CT_LSTIMEDTLS"("CT_LS_ID") TABLESPACE "PSINDEX" nosegment;

select dbms_metadata.get_ddl('TABLE','&TAB_NAME','&SCHEMA') from dual;
select dbms_metadata.get_ddl('INDEX','&INDEX_NAME','&SCHEMA') from dual;

select id, parent_id, operation
from (
select level lvl, id, parent_id, lpad(' ',level) || operation || ' ' || options
|| ' ' || object_name as operation
from plan_table
start with id = 0
connect by prior id = parent_id
)
order by lvl desc, id;


exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'CUSTDOMAIN',tabname => 'FTIME' , estimate_percent => 100, block_sample => TRUE,
degree => 8, method_opt => 'FOR ALL COLUMNS SIZE 1', granularity => 'ALL', cascade => TRUE);
exec dbms_stats.gather_schema_stats( ownname => 'APR_LND',estimate_percent => dbms_stats.auto_sample_size, method_opt => 'FOR ALL COLUMNS SIZE AUTO',degree => 8,no_invalidate=>false , cascade => TRUE);


set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000 avg_etime_ms,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio, MODULE
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where s.parsing_schema_name='&USER'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and ss.begin_interval_time like '%30-AUG-16%'
order by 7 desc
/

SQLT and 10046 and 10053 trace

1. Getting SQLT XTRACT report for the poor performing query (baxk1rgw5ht0c). 

You may review the notes below for additional information: 

--> Download SQLT tool from here SQLT Diagnostic Tool ( Doc ID 215187.1 ) 
--> Install SQLT as per SQLT Usage Instructions ( Doc ID 1614107.1 ) 
--> Identify your SQL ID as per document How to Determine the SQL_ID for a SQL Statement ( Doc ID 1627387.1 ) 
--> Execute sqlt XTRACT method for your SQL_ID 

# cd sqlt/run 
# sqlplus apps 
SQL> START sqltxtract.sql <your_sql_id> sqltxplain_password 

SQLT XTRACT will display all the execution plans available for this query, so we can see if there is a plan stability issue. It will also provide STA report to see if any recommendations available. 
>>> FAQ: SQLT (SQLTXPLAIN) Frequently Asked Questions ( Doc ID 1454160.1 ) 

2. 10046 trace for "GOOD" and "BAD" execution: 
alter session set tracefile_identifier='10046_&your_name'; 
alter session set timed_statistics = true; 
alter session set statistics_level=all; 
alter session set max_dump_file_size = unlimited; 
alter session set events '10046 trace name context forever,level 12'; 
-- /*Run SQL here*/ ------ 
select 'CLOSE CURSOR' close_cursor from dual; --- to ensure the previous cursor is closed (or select * from dual;) 
alter session set events '10046 trace name context off'; 

3. 10053 trace for "GOOD" and "BAD" execution: 
alter session set tracefile_identifier='10053_&your_name'; 
alter session set timed_statistics = true; 
alter session set statistics_level=all; 
alter session set max_dump_file_size = unlimited; 
alter session set events '10053 trace name context forever, level 1'; 
-- /*Run SQL here*/ ------ 
alter session set events '10053 trace name context off'; 

'buffer busy waits' Wait Event explained

'log file sync' Wait Event Explained

'log file sync' Wait Event Explained

gc [current/cr] [2/3]-way Wait Event

A current or cr block was requested and received after 2 or 3 network hops. 
The request was processed immediately.

 If these events consume lot of time then check..

 - Network bandwidth saturation
 - Private Interconnect configuration
 - Socket send and receive buffer
 - Run queue length and system CPU usage

 Tuning
- System and load tuning to reduce latencies
- SQL and schema tuning to minimize IO and remote cache references 

"GC BUFFER BUSY" wait event explained

When a session wants to modify a buffer, which is cached in the remote instances, it waits on the buffer busy global cache event. This is similar to buffer busy waits in single instance architecture. This wait happens at cache layer. In other words, the session is waiting for a buffer in local cache, which is busy because it is waiting for a global cache request to complete. The waits at the RAC layer are accounted in the global cache busy wait event.

The interconnect statistics look good so this looks like a hot block issue.

You may want to investigate a hot block issue.

To resolve contention for this latch, you must first identify the segments that the blocks are associated with by doing the following 

Find the "hottest" child latches (usually top 5): 
SELECT addr, latch#, sleeps 
FROM v$latch_children 
WHERE name = 'cache buffers chains' 
AND sleeps > 0 
ORDER BY sleeps DESC 

Find the buffers associated with the hot latches: 
SELECT hladdr, dbarfil, dbablk, tch FROM x$bh 
WHERE hladdr = '&child_latch_addr' -- <== run this for each of the hot latches 
ORDER BY tch DESC 

Find the segments associated with the hot buffers: 
SELECT owner, segment_name, partition_name, segment_type, tablespace_name, 
relative_fno, block_id, blocks 
FROM dba_extents e 
WHERE &Block_No BETWEEN e.block_id AND (e.block_id + blocks) 
AND e.relative_fno = &File_no 

To relieve contention on the segments listed above: 

1) On rare occasions, a larger buffer cache may help relieve contention; if there is sufficient memory available, increase the buffer cache by 20% and check if the contention on this latch is reduced. (This almost never helps, but its simple to try). 

2) Examine SQL statements with a high number of buffer gets and tune them to reduce the number of buffer gets per execution or change the application to execute the statements less frequently. 

3) Alter (or even rebuild) tables listed above to use a higher PCTFREE setting. This will reduce the number of rows per block and hopefully, spread out contention for the blocks (at the expense of wasting space). 

4) If the hot blocks are index leaf blocks, there may be contention due to key values that are increasing steadly (using a sequence). Look at using reverse key indexes (if range scans aren't commonly used against the segment). Or, consider changing the application to use natural keys, partitioning or otherwise reduce the dependence on the sequence-generated keys.

Adaptive Cursor Sharing (New feature in Oracle Database 11g Release1)

 We always encouraging developers to use bind variables, but it is dangerous using against skewed (massive duplicates) data. The bind variables are used against columns containing skewed data they sometimes lead to bad execution plans. This is because the optimizer peeks at the bind variable value during the hard parse ( very first execution) of the statement, so the value of a bind variable when the statement is first presented to the database can affect every execution of the statement, regardless of the bind variable values.

Oracle 11g uses Adaptive Cursor Sharing to solve this problem by allowing the database to compare the effectiveness of execution plans between executions with different bind variable values. If required CBO will recalculate the plan based on data skewness. If it notices suboptimal plans, it allows certain bind variable values, or ranges of values, to use alternate execution plans for the same statement. This functionality requires no additional configuration. The following code provides an example of adaptive cursor sharing.

• Setup
• Test Adaptive Cursor Sharing
• BIND_AWARE and NO_BIND_AWARE Hints

Setup

First we create and populate a CREDIT table.

DROP TABLE CREDIT;

CREATE TABLE CREDIT (

  id          NUMBER,

  credit_type NUMBER,

  description VARCHAR2(50),

  CONSTRAINT CREDIT_PK PRIMARY KEY (id)

);

CREATE INDEX CREDIT_credit_type_i ON CREDIT (credit_type);

DECLARE

  TYPE t_CREDIT IS TABLE OF CREDIT%ROWTYPE;

  l_tab t_CREDIT := t_CREDIT();

  BEGIN

  FOR i IN 1 .. 100000 LOOP

    l_tab.extend;

    IF MOD(i,2)=0 THEN

      l_tab(l_tab.last).credit_type := 2;

    ELSE

      l_tab(l_tab.last).credit_type := i;

    END IF;

   

    l_tab(l_tab.last).id          := i;

    l_tab(l_tab.last).description := 'Description for ' || i;

  END LOOP;

  FORALL i IN l_tab.first .. l_tab.last

    INSERT INTO CREDIT VALUES l_tab(i);

  COMMIT;

END;

/

EXEC DBMS_STATS.gather_table_stats(USER, 'CREDIT', method_opt=>'for all indexed columns size skewonly', cascade=>TRUE);

The data in the CREDIT_TYPE column is skewed, as shown by the presence of a histogram against the column.

SELECT column_name, histogram FROM user_tab_cols WHERE  table_name = 'CREDIT';

COLUMN_NAME                    HISTOGRAM

------------------------------ ---------------

ID                                        NONE

CREDIT_TYPE                    HEIGHT BALANCED

DESCRIPTION                    NONE

3 rows selected.

 Test Adaptive Cursor Sharing

Next, we query the table and limit the rows returned based on the CREDIT_TYPE column with a literal value of "1".

SET LINESIZE 200

SELECT MAX(id) FROM CREDIT WHERE credit_type = 1;

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);

   MAX(ID)

----------

         1

1 row selected.

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------------------------------------

SQL_ID  cgt92vnmcytb0, child number 0

-------------------------------------

SELECT MAX(id) FROM CREDIT WHERE credit_type = 1

Plan hash value: 3987223107

-----------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                            |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE              |                            |     1 |     9 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| CREDIT               |     1 |     9 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | CREDIT_CREDIT_TYPE_I |     1 |       |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------------

This query has used the index as we would expect. Now we repeat the query, but this time use a bind variable.

VARIABLE l_credit_type NUMBER;

EXEC :l_credit_type := 1;

SELECT MAX(id) FROM CREDIT WHERE credit_type = :l_credit_type;

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);

   MAX(ID)

----------

         1

1 row selected.

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------------------------------------

SQL_ID  9bmm6cmwa8saf, child number 0

-------------------------------------

SELECT MAX(id) FROM CREDIT WHERE credit_type = :l_credit_type

Plan hash value: 3987223107

-----------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                            |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE              |                            |     1 |     9 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| CREDIT               |     1 |     9 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | CREDIT_CREDIT_TYPE_I |     1 |       |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------------

So we ran what amounted to the same query, and got the same result and execution plan. The optimizer picked an execution plan that it thinks is optimium for query by peeking at the value of the bind variable. The only problem is, this would be totally the wrong thing to do for other bind values.

VARIABLE l_credit_type NUMBER;

EXEC :l_credit_type := 2;

SELECT MAX(id) FROM CREDIT WHERE credit_type = :l_credit_type;

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);

   MAX(ID)

----------

    100000

1 row selected.

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------------------------------------

SQL_ID  9bmm6cmwa8saf, child number 0

-------------------------------------

SELECT MAX(id) FROM CREDIT WHERE credit_type = :l_credit_type

Plan hash value: 3987223107

-----------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                            |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE              |                            |     1 |     9 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| CREDIT               |     1 |     9 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | CREDIT_CREDIT_TYPE_I |     1 |       |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------------

If we look at the V$SQL view entry for this query, we can see the IS_BIND_SENSITIVE column is marked as 'Y', so Oracle is aware this query may require differing execution plans depending on the bind variable values, but currently the IS_BIND_AWARE column is marked as 'N', so Oracle has not acted on this yet.

SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware FROM   v$sql

WHERE  sql_text = 'SELECT MAX(id) FROM CREDIT WHERE credit_type = :l_credit_type';

SQL_ID        CHILD_NUMBER I I

------------- ------------ - -

9bmm6cmwa8saf            0 Y N

1 row selected.

If we run the statement using the second bind variable again, we can see that Oracle has decided to use an alternate, more efficient plan for this statement.

VARIABLE l_credit_type NUMBER;

EXEC :l_credit_type := 2;

SELECT MAX(id) FROM CREDIT WHERE credit_type = :l_credit_type;

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);

   MAX(ID)

----------

    100000

1 row selected.

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------------

SQL_ID  9bmm6cmwa8saf, child number 1

-------------------------------------

SELECT MAX(id) FROM CREDIT WHERE credit_type = :l_credit_type

Plan hash value: 509473618

-----------------------------------------------------------------------------------

| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |              |       |       |   138 (100)|          |

|   1 |  SORT AGGREGATE    |              |     1 |     9 |            |          |

|*  2 |   TABLE ACCESS FULL| CREDIT | 48031 |   422K|   138   (2)| 00:00:02 |

-----------------------------------------------------------------------------------

This change in behavior is also reflected in the V$SQL view, which now has the IS_BIND_AWARE column maked as "Y".

SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware

FROM   v$sql

WHERE  sql_text = 'SELECT MAX(id) FROM CREDIT WHERE credit_type = :l_credit_type';

SQL_ID        CHILD_NUMBER I I

------------- ------------ - -

9bmm6cmwa8saf            0 Y N

9bmm6cmwa8saf            1 Y Y

2 rows selected.

Information about the cursor sharing histograms, statistics and selectivity is displayed using the V$SQL_CS_HISTOGRAM, V$SQL_CS_STATISTICS andV$SQL_CS_SELECTIVITY views respectively.

SQL> SELECT * FROM v$sql_cs_histogram WHERE sql_id = '9bmm6cmwa8saf';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT

-------- ---------- ------------- ------------ ---------- ----------

319A4A1C 4171522382 9bmm6cmwa8saf            1          0          0

319A4A1C 4171522382 9bmm6cmwa8saf            1          1          1

319A4A1C 4171522382 9bmm6cmwa8saf            1          2          0

319A4A1C 4171522382 9bmm6cmwa8saf            0          0          1

319A4A1C 4171522382 9bmm6cmwa8saf            0          1          1

319A4A1C 4171522382 9bmm6cmwa8saf            0          2          0

6 rows selected.

SQL> SELECT * FROM v$sql_cs_statistics WHERE sql_id = '9bmm6cmwa8saf';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME

-------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------

319A4A1C 4171522382 9bmm6cmwa8saf            1          2064090006 Y          1          50001         499          0

319A4A1C 4171522382 9bmm6cmwa8saf            0          2342552567 Y          1              3           3          0

2 rows selected.

SQL> SELECT * FROM v$sql_cs_selectivity WHERE sql_id = '9bmm6cmwa8saf';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH

-------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------

319A4A1C 4171522382 9bmm6cmwa8saf            1 =L_RECORD_T                                       0 0.432283   0.528346

1 row selected.

BIND_AWARE and NO_BIND_AWARE Hints

From 11.1.0.7 onward it is possible to skip the monitoring that is required to detect bind-sensitive queries by using the BIND_AWARE hint. In the following example, the presence of the hint tells the optimizer that we believe the query is bind-sensitive, so it should use bind-aware cursor sharing from the first execution.

SELECT /*+ BIND_AWARE */ MAX(id) FROM CREDIT WHERE credit_type = :l_credit_type;

The hint will only work if the query uses bind variables in WHERE clause predicates referencing columns with histograms.

There is also a NO_BIND_AWARE hint that tells the optimizer to ignore bind-sensitive queries, effectively hiding the query from the adaptive cursor sharing functionality.

Bind-aware cursor sharing has a small overhead associated with it, which is why Oracle use the "adaptive" approach to identifying queries that would benefit from bind-aware cursor sharing. Adding the hint to queries that will not benefit from it is a waste.