select substr(sql_text,1,40) sql_text, hash_value, cpu_time
from v$sql
where cpu_time > 10000000
order by cpu_time
/
SQL_TEXT HASH_VALUE CPU_TIME
---------------------------------------- ---------- ---------- UPDATE DL_PROCESS_SUMMARY SET DPS_PROCES 1123679722 38749307 UPDATE DL_HRA_TRANSACTIONS SET LTRA_DL_L 3458994038 58269393 SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS 2035003868 161452815 SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS 3963169483 164423751 BEGIN s_dl_HRA_TRANSACTIONS.DATALOAD_VAL 1335393380 3076207562
Mapping hash values to sql text and explain plans
You can obtain an explain plan for any sql held in the shared pool by querying this view. The script below takes the hash value of the sql and will return the text of the query along with the explain plan as it was executed:
set pagesize 600
set tab off
set linesize 140
set echo off
set long 4000
col TQID format A4
col “SLAVE SQL” format A95 WORD_WRAP
col address format A12
col sql_hash format A15
col exec format 9999
col sql_text format A75 WORD_WRAP
repfooter off;
set timing off veri off space 1 flush on pause off termout on numwidth 10;
alter session set “_complex_view_merging”=false;
undefine hashvalue
select hash_value||decode(child_number, 0, ”, ‘/’||child_number) sql_hash, sql_text
from v$sql
where child_number= 0 and hash_value= &&hashvalue;
select ‘| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |’ as “Plan Table” from dual
union all /* QWEKLOIPYRTJHH7 */
select ‘————————————————————————————————————————‘
from dual
union all
select rpad(‘| ‘||substr(lpad(‘ ‘,1*(depth))||operation|| decode(options, null,”,’ ‘||options), 1, 33), 34, ‘ ‘)||’|’||
rpad(substr(object_name||’ ‘,1, 19), 20, ‘ ‘)||’|’||
lpad(decode(starts,null,’ ‘,
decode(sign(starts-1000), -1, starts||’ ‘,
decode(sign(starts-1000000), -1, round(starts/1000)||’K’,
decode(sign(starts-1000000000), -1, round(starts/1000000)||’M’,
round(starts/1000000000)||’G’)))), 8, ‘ ‘) || ‘|’ ||
lpad(decode(cardinality,null,’ ‘,
decode(sign(cardinality-1000), -1, cardinality||’ ‘,
decode(sign(cardinality-1000000), -1, round(cardinality/1000)||’K’,
decode(sign(cardinality-1000000000), -1, round(cardinality/1000000)||’M’,
round(cardinality/1000000000)||’G’)))), 8, ‘ ‘) || ‘|’ ||
lpad(decode(outrows,null,’ ‘,
decode(sign(outrows-1000), -1, outrows||’ ‘,
decode(sign(outrows-1000000), -1, round(outrows/1000)||’K’,
decode(sign(outrows-1000000000), -1, round(outrows/1000000)||’M’,
round(outrows/1000000000)||’G’)))), 8, ‘ ‘) || ‘|’ ||
lpad(decode(crgets,null,’ ‘,
decode(sign(crgets-10000000), -1, crgets||’ ‘,
decode(sign(crgets-1000000000), -1, round(crgets/1000000)||’M’,
round(crgets/1000000000)||’G’))), 9, ‘ ‘) || ‘|’ ||
lpad(decode(reads,null,’ ‘,
decode(sign(reads-10000000), -1, reads||’ ‘,
decode(sign(reads-1000000000), -1, round(reads/1000000)||’M’,
round(reads/1000000000)||’G’))), 8, ‘ ‘) || ‘|’ ||
lpad(decode(writes,null,’ ‘,
decode(sign(writes-10000000), -1, writes||’ ‘,
decode(sign(writes-1000000000), -1, round(writes/1000000)||’M’,
round(writes/1000000000)||’G’))), 8, ‘ ‘) || ‘|’ ||
lpad(decode(etime,null,’ ‘,
decode(sign(etime-10000000), -1, etime||’ ‘,
decode(sign(etime-1000000000), -1, round(etime/1000000)||’M’,
round(etime/1000000000)||’G’))), 8, ‘ ‘) || ‘|’ as “Explain plan”
from
(select /*+ no_merge */
p.HASH_VALUE, p.ID, p.DEPTH, p.POSITION, p.OPERATION,
p.OPTIONS, p.COST COST, p.CARDINALITY CARDINALITY,
p.BYTES BYTES, p.OBJECT_NODE, p.OBJECT_OWNER,
p.OBJECT_NAME, p.OTHER_TAG, p.PARTITION_START,
p.PARTITION_STOP, p.DISTRIBUTION, pa.starts,
pa.OUTPUT_ROWS outrows, pa.CR_BUFFER_GETS crgets,
pa.DISK_READS reads, pa.DISK_WRITES writes,
pa.ELAPSED_TIME etime
from v$sql_plan_statistics_all pa,
V$sql_plan p
where p.hash_value = &&hashvalue
and p.CHILD_NUMBER= 0
and p.id= pa.id(+)
and p.hash_value = pa.hash_value(+)
and pa.child_number(+) = 0
order by p.id )
union all
select ‘————————————————————————————————————————‘ from dual;
REM
REM Print slave sql
REM
select /* QWEKLOIPYRTJHH7 */
decode(object_node,null,”, substr(object_node,length(object_node)-3,1) || ‘,’ ||
substr(object_node,length(object_node)-1,2)) TQID,
other “SLAVE SQL”
from v$sql_plan vp
where other is not NULL
and hash_value = &&hashvalue
and CHILD_NUMBER= 0;
Running this will return something like
SQL> @sqlplanSession altered. Enter value for hashvalue: 3909026319 SQL_HASH SQL_TEXT --------------- --------------------------------------------------------------------------- 3909026319 SELECT AGR.AGR_AUN_CODE_PARENT FROM ADMIN_GROUPINGS_SELF AGR, ADMIN_PROPERTIES APR WHERE APR.APR_PRO_REFNO = :B2 AND AGR.AGR_AUN_CODE_CHILD = APR.APR_AUN_CODE AND AGR.AGR_AUY_CODE_PARENT = :B1 Plan Table ----------------------------------------------------------------------------------------------------- |Operation |Name |Starts|E-Rows|A-Rows|Buffers|Reads|Writes|E-Time| ----------------------------------------------------------------------------------------------------- |SELECT STATEMENT | | | | | | | | | | TABLE ACCESS BY INDEX ROWID |ADMIN_GROUPINGS_SEL| | 1| | | | | | | NESTED LOOPS | | | 5| | | | | | | TABLE ACCESS BY INDEX ROWID|ADMIN_PROPERTIES | | 9| | | | | | | INDEX RANGE SCAN |APR_PRO_FK_I | | 9| | | | | | | INDEX RANGE SCAN |AGS_PK | | 9| | | | | | -----------------------------------------------------------------------------------------------------
9 rows selected.no rows selected
Philip Howe's Oracle Blog 