Oracle SQL执行计划

Oracle SQL执行计划

要得到Oracle的执行计划有很多种方法，这里只是介绍几种比较少人提及的。
（1）To get plan from Memory
For SQL ID :
select * from table(dbms_xplan.display_cursor('&sql_id'));
select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALL'));
For SQL ID, Child Cursor :
select * from table(dbms_xplan.display_cursor('&sql_id', &child_number, 'ALL'));

For SQL Text :
select t.*
from v$sql s, table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'ALL')) t
where s.sql_text like '%&querystring%';

（2）From AWR:
For SQL ID :
select * from table(dbms_xplan.display_awr('&sql_id')) ;
select * from table(dbms_xplan.display_awr('&sql_id', NULL, NULL, 'ALL')) ;

For SQL ID, Plan Hash Value in the current database :
select * from table(dbms_xplan.display_awr('&sql_id', '&plan_hash_value', NULL, 'ALL')) ;

For SQL ID, Plan Hash Value in a different database ID :
select * from table(dbms_xplan.display_awr('&sql_id', '&plan_hash_value', &dbid, 'ALL')) ;

For SQL ID that was executed during a certain period :
select t.*
from (select distinct sql_id, plan_hash_value, dbid
from dba_hist_sqlstat
where sql_id = '&sql_id'
and snap_id between &begin_snap and &end_snap) s,
table(dbms_xplan.display_awr(s.sql_id, s.plan_hash_value, s.dbid, 'ALL')) t;

For SQL Text :
select t.*
from (select distinct r.sql_id sql_id, r.plan_hash_value plan_hash_value, r.dbid dbid
from dba_hist_sqltext q, dba_hist_sqlstat r
where q.sql_id = r.sql_id
and q.sql_text like '%&querystring%') s,
table(dbms_xplan.display_awr(s.sql_id, s.plan_hash_value, s.dbid, 'ALL')) t;

（3）通过查询V$SQL_PLAN视图构造执行计划
使用这个视图可以获得当前数据库实例library cache中保存的SQL执行计划，由于是在内存中保存的，因此这个视图所能查看的信息也有限制，如果信息已被换出内存，将无法查看到。
select '| Operation                         |Object Name                    |  Rows | Bytes|   Cost |'
as "Explain Plan in library cache:" from dual
union all
select rpad('| '||substr(lpad(' ',1*(depth-1))||operation||
       decode(options, null,'',' '||options), 1, 35), 36, ' ')||'|'||
       rpad(decode(id, 0, '----------------------------',
       substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name)
       ||' ',1, 30)), 31, ' ')||'|'|| 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 sp
 where sp.hash_value=&hash_value;