#### explain ``` sql explain plan for ....; select * from table(dbms_xplan.display); --显示详细的执行计划 select * from table(dbms_xplan.display(null,null,'ALL')); ``` #### autotrace ``` sql set autotrace on--跟踪实际执行计划，有物理读逻辑读 --不显示查询结果 set autotrace traceonly --不实际执行 set autotrace trace explain --or alter session set statistics_level=all; sql... select * from table(dbms_xplan.display_cursor(null,null,'allstats +alias +outline')); ``` #### 10046 **10046级别** - 1: 启用标准的SQL_TRACE功能,等价于SQL_TRACE - 4: level 1 加上绑定值(bind values) - 8: Level 1 加上等待事件跟踪 - 12 : Level 1+4+8 ``` sql oradebug setospid 3525; --or oradebug setmypid oradebug event 10046 trace name context forever,level 12; oradebug event 10046 trace name context off oradebug tracefile_name --or alter system set events '10046 trace name context forver,level 12'; alter system set events '10046 trace name context off'; ``` **指定用户session跟踪** ``` sql --可以结合等待事件等确认sid 用户后使用该方式 select sid,serial#,username from v$session where username='FIRSOULER'; alter session set tracefile_identifier='10046'; --12是级别 exec dbms_system.set_ev(sid,serial#,10046,12,'USERNAME'); exec dbms_system.set_ev(sid,serial#,10046,0,'USERNAME'); --0 是关闭 --最后可通过 tkprof 格式化 查看,eg,sort默认是sql执行的先后顺序，不利于查看，三个排序时游标解析耗时、游标执行耗时、游标获取数据行耗时 tkprof tracefile outputfile sys=no sort=prsela,exeela,fchela ``` #### 10053 优化器选择 ``` sql oradebug setmypid oradebug unlimit oradebug event 10053 trace name context forever, level 1 ...enter your query here... oradebug event 10053 trace name context off oradebug tracefile_name ``` #### DBMS_XPLAN ``` sql --查看sqlplus刚执行过的sql select * from table(dbms_xplan.display_cursor(null,null,'advanced')); --指定sql id 游标查看 select * from table(dbms_xplan.displaly_cursor('sql_id/hash_value',child_cursor_number,'advanced')); --or 查看sql_id select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like ''; --sql历史执行支计划 select * from table(dbms_xplan.display_awr('sql_id')); ``` ### 各自优势 > 参考自《收获不止SQL优化》