Oracle sql执行计划

时间:2022-10-16 04:32:41
explain plan
    explain plan for sql_statement
    select * from table(dbms_xplan.display)
DBMS_XPLAN
    1.select * from table(dbms_xplan.display);
    2.select * from table(dbms_xplan.display_cursor());
    3.select * from table(dbms_xplan.display_awr('sql_id'));
SQLPLUS的AUTOTRACE开关
    set autotrace [on|off|traceonly]
    [explain]
    [statistics]
    1.set autotrace on(包含执行结果、执行计划、统计信息)
    2.set autotrace traceonly(包含执行计划、统计信息)
    3.set autotrace explain(包含执行计划)
    4.set autotrace statistics(包含统计信息)
10046事件与tkprof
     1.当前session激活10046事件
         oradebug setmypid
         alter session set events '10046 trace name context forever,level 12'
         orabug event 10046 trace name context forever,level 12
     2.在此session执行SQL
         oradebug tracefile_name
     3.在此session关闭10046事件
         alter session set events '10046 trace name context off'
         orabug event 10046 trace name context off

tkprof tracefile outputfile

sql_trace
   1.alter session set seq_trace=true;
   2.执行SQL
   3.alter session set sql_trace=false;
   4.select distinct spid from v$process p,v$session s,v$mystat m where p.addr=s.paddr and s.sid=m.sid;
   5.show parameter user_dump_dest