查看执行计划的方法
Explain Plan For SQL
不实际执行SQL语句，生成的计划未必是真实执行的计划
必须要有plan_table
SQLPLUS AUTOTRACE
除set autotrace traceonly explain外均实际执行SQL，但仍未必是真实计划
必须要有plan_table
SQL TRACE
需要启用10046戒者SQL_TRACE
一般用tkprof看的更清楚些，当然10046里本身也有执行计划信息
V$SQL和V$SQL_PLAN
可以查询到多个子游标的计划信息了，但是看起来比较费劲
Enterprise Manager
可以图形化显示执行计划，但并非所有环境有EM可用
其他第三方工具
注意 PL/SQL developer之类工具F5看到的执行计划未必是真实的
推荐的方法 DBMS_XPLAN
select * from table(dbms_xplan….);
dbms_xplan.display()
数据来源是Plan Table
dbms_xplan.display_cursor
数据来源是Shared pool中的游标缓存
FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN DEFAULT
CURSOR_CHILD_NO NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
推荐的使用参数为：
select * from table(dbms_xplan.display_cursor('sqlId',null,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));
如果sqlId为NULL，则显示当前session的执行计划。
select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));
其中format的解释如下：
IOSTATS: Assuming that basic plan statistics are
--- collected when SQL statements are executed (either by
--- using the gather_plan_statistics hint or by setting the
--- parameter statistics_level to ALL), this format will show
--- IO statistics for all (or only for the last as shown below)
--- executions of the cursor.
---
--- MEMSTATS: Assuming that PGA memory management is enabled (i.e
--- pga_aggregate_target parameter is set to a non 0 value),
--- this format allows to display memory management
--- statistics (e.g. execution mode of the operator, how
--- much memory was used, number of bytes spilled to
--- disk, ...). These statistics only apply to memory
--- intensive operations like hash-joins, sort or some bitmap
--- operators.
---
--- ROWSTATS: Assuming that basic plan statistics are
--- collected when SQL statements are executed (either by
--- using the gather_plan_statistics hint or by setting the
--- parameter statistics_level to ALL), this format will show
--- row count statistics for all (or only for the last as
--- shown below) executions of the cursor.
---
--- ALLSTATS: A shortcut for 'IOSTATS MEMSTATS ROWSTATS'
---
--- LAST: By default, plan statistics are shown for all executions of
--- the cursor. The keyword LAST can be specified to see only
--- the statistics for the last execution.
---
--- PEEKED_BINDS：显示解析时使用的绑定变量。
dbms_xplan.display_awr
数据来源是AWR仓库基表WRH$_SQL_PLAN
FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER(38) IN DEFAULT
DB_ID NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
CON_ID NUMBER(38) IN DEFAULT
dbms_xplan.display_sqlset
数据来源是SQL Set视图
以上内容主要整理自maclean的oracle执行计划教学视频和ppt：
/archives/read-sql-execution-plan.html
