Explain plan for one sql_id

16 Oct

SELECT * FROM TABLE(dbms_xplan.display_cursor('&SQLID'));
SELECT * FROM TABLE(dbms_xplan.display_cursor('&SQLID',null,'ADVANCED'));
SELECT * FROM TABLE(dbms_xplan.display_awr('&SQLID'));

SELECT
  s.elapsed_time_delta,
  s.buffer_gets_delta,
  s.disk_reads_delta,
  cursor(select * from table(dbms_xplan.display_awr(t.sql_id, s.plan_hash_value)))
FROM
  dba_hist_sqltext t,
  dba_hist_sqlstat s
WHERE
  t.dbid = s.dbid
  AND t.sql_id = s.sql_id
  AND s.snap_id between &v_snap_id-2 and &v_snap_id
  AND t.sql_id = '&sqlid';

SELECT  timestamp,operation, options, object_name, object_type, optimizer, id, cost, cpu_cost, io_cost  
FROM v$sql_plan
WHERE sql_id='&SQL_ID'
AND child_number=1;

SELECT '| Operation | PHV/Object Name | Rows | Bytes| Cost |'
as "Optimizer Plan:"
FROM dual
UNION ALL
SELECT * FROM (SELECT
rpad('|'||substr(lpad(' ',1*(depth-1))||operation||
decode(options, null,'',' '||options), 1, 32), 33, ' ')||'|'||
rpad(decode(id, 0, '----- '||to_char(hash_value)||' ['||to_char(child_number)||']
-----'
, substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null,
object_name)
||' ',1, 20)), 21, ' ')||'|'||
lpad(decode(cardinality,null,' ',
decode(sign(cardinality-1000), -1, cardinality||' ',
decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',
trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
lpad(decode(bytes,null,' ',
decode(sign(bytes-1024), -1, bytes||' ',
decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
lpad(decode(cost,null,' ',
decode(sign(cost-10000000), -1, cost||' ',
decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"
FROM v$sql_plan
WHERE address='&addr'
AND hash_value='&hash'
AND child_number=&child
ORDER BY hash_value,child_number,id);

Comments