获取Oracle中SQL语句的执行计划
一、 PL/SQL Dev工具中的F5按钮
这是最简单的获取执行计划的方法，通过SQL语句一键获取，不需要做什么
这种方式获取的执行计划是Oracle通过统计信息预估的，因为SQL语句并没有真正执行。这种方式获取的执行计划也不会显示谓词过滤信息和排序信息等其他附加信息。
二、 EXPLAIN PLAN命令
SQL> explain plan for select * from t_bid_app t where t.app_id =:1; Explained SQL> select * from table(DBMS_XPLAN.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2111836046 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (% -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 59 | 2 | 1 | TABLE ACCESS BY INDEX ROWID| T_BID_APP | 1 | 59 | 2 |* 2 | INDEX UNIQUE SCAN | T_BID_APP_APP_ID | 1 | | 1 -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T"."APP_ID"=TO_NUMBER(:1)) 14 rows selected
通过explain plan命令结合DBMS_XPLAN包来获取SQL语句的执行计划。同样，这个执行计划是Oracle用统计信息估算的，SQL语句并没有实际执行，不一定是真实执行时走的执行计划。
三、 DBMS_XPLAN包
除了结合explain plan命令外，DBMS_XPLAN包还提供几种方式获取SQL语句的执行计划。
- 获取当前session上一条已经执行过的SQL语句的执行计划
SQL> set linesize 100; SQL> set pagesize 800; SQL> select * from t_bid_app where app_id=1234; no rows selected SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID b8xdya7qfkucm, child number 0 ------------------------------------- select * from t_bid_app where app_id=1234 Plan hash value: 2111836046 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| T_BID_APP | 1 | 59 | 2 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | T_BID_APP_APP_ID | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / T_BID_APP@SEL$1 2 - SEL$1 / T_BID_APP@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "T_BID_APP"@"SEL$1" ("T_BID_APP"."APP_ID")) END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("APP_ID"=1234) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "APP_ID"[NUMBER,22], "T_BID_APP"."MATTER_ID"[NUMBER,22], "T_BID_APP"."USER_ID"[NUMBER,22], "T_BID_APP"."QUAL_AUD"[NUMBER,22], "T_BID_APP"."SITE_AUD"[NUMBER,22], "T_BID_APP"."QUAL_USER_ID"[NUMBER,22], "T_BID_APP"."SITE_USER_ID"[NUMBER,22], "T_BID_APP"."APP_TIME"[DATE,7], "T_BID_APP"."QUAL_AUD_TIME"[DATE,7], "T_BID_APP"."SITE_AUD_TIME"[DATE,7] 2 - "T_BID_APP".ROWID[ROWID,10], "APP_ID"[NUMBER,22] 49 rows selected.
通过这种方式获取的执行计划是真实的，SQL语句已经真实执行过。
- 通过sql_id获取一条已经执行过的SQL语句的执行计划
SQL> select * from table (dbms_xplan.display_cursor('259n3s6dqgst8',0,'advanced')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 259n3s6dqgst8, child number 0 ------------------------------------- select * from t_bid_app t where t.app_id=1234 Plan hash value: 2111836046 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| T_BID_APP | 1 | 59 | 2 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | T_BID_APP_APP_ID | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / T@SEL$1 2 - SEL$1 / T@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T_BID_APP"."APP_ID")) END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T"."APP_ID"=1234) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "T"."APP_ID"[NUMBER,22], "T"."MATTER_ID"[NUMBER,22], "T"."USER_ID"[NUMBER,22], "T"."QUAL_AUD"[NUMBER,22], "T"."SITE_AUD"[NUMBER,22], "T"."QUAL_USER_ID"[NUMBER,22], "T"."SITE_USER_ID"[NUMBER,22], "T"."APP_TIME"[DATE,7], "T"."QUAL_AUD_TIME"[DATE,7], "T"."SITE_AUD_TIME"[DATE,7] 2 - "T".ROWID[ROWID,10], "T"."APP_ID"[NUMBER,22] 48 rows selected.
通过这种方式获取的执行计划是真实的，SQL语句已经真实执行过，但需要sql语句未被换出shared pool。如果SQL语句被换出shared pool或者执行alter system flush shared_pool，就无法获取执行计划了
- 通过AWR报告中的sql_id获取一条已经执行过的SQL语句的执行计划
这种方式常用于查询已经被换出shared pool 但已经被收集到AWR报告中的语句的执行计划。
select * from table(dbms_xplan.display_awr(sql_id))
同样，这种方式获取的执行计划也是真实的。
四、 AutoTrace
通过set autotrace on| traceonly| traceonly explain;来获取执行计划，这种方法虽然也执行了sql语句，但是执行计划不一定是真实的。
SQL> set autotrace on; SQL> select * from t_bid_app where app_id=1234; no rows selectedExecution Plan ---------------------------------------------------------- Plan hash value: 2111836046 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 59 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T_BID_APP | 1 | 59 | 2 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | T_BID_APP_APP_ID | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("APP_ID"=1234)Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 792 bytes sent via SQL*Net to client 468 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
引用一个经典的例子(不是我做的)来证明:autotrace on和explain plan的结果是使用index range scan，而实际执行计划是index fast full scan。如下所示
SQL> create table T1 as select * from dba_objects; Table created. SQL> insert into t1 select * from t1; 72827 rows created. SQL> / 145654 rows created. SQL> commit; Commit complete. SQL> select count(*) from t1; COUNT(*) ---------- 291308 SQL> create index idx_t1 on t1(object_id); Index created. SQL> exec dbms_stats.gather_table_stats('SALP','T1'); PL/SQL procedure successfully completed. SQL> set linesize 120; SQL> set pagesize 800; SQL> var x number; SQL> var y number; SQL> exec :x :=0 PL/SQL procedure successfully completed. SQL> exec :y :=100000 PL/SQL procedure successfully completed. SQL> set autotrace on; SQL> select count(*) from t1 where object_id between :x and :y; COUNT(*) ---------- 291304 Execution Plan ---------------------------------------------------------- Plan hash value: 2351893609 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 5 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | FILTER | | | | | | |* 3 | INDEX RANGE SCAN| IDX_T1 | 728 | 3640 | 5 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(TO_NUMBER(:Y)>=TO_NUMBER(:X)) 3 - access("OBJECT_ID">=TO_NUMBER(:X) AND "OBJECT_ID"<=TO_NUMBER(:Y)) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 656 consistent gets 0 physical reads 0 redo size 348 bytes sent via SQL*Net to client 479 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> set autotrace off; SQL> explain plan for select count(*) from t1 where object_id between :x and :y; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2351893609 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 5 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | FILTER | | | | | | |* 3 | INDEX RANGE SCAN| IDX_T1 | 728 | 3640 | 5 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(TO_NUMBER(:Y)>=TO_NUMBER(:X)) 3 - access("OBJECT_ID">=TO_NUMBER(:X) AND "OBJECT_ID"<=TO_NUMBER(:Y)) 16 rows selected. SQL> select count(*) from t1 where object_id between :x and :y; COUNT(*) ---------- 291304 SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ SQL_ID 9dhu3xk2zu531, child number 0 ------------------------------------- select count(*) from t1 where object_id between :x and :y Plan hash value: 1410530761 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 178 (100)| | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | FILTER | | | | | | |* 3 | INDEX FAST FULL SCAN| IDX_T1 | 291K| 1422K| 178 (2)| 00:00:01 | --------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 3 - SEL$1 / T1@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_FFS(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID")) END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 1 - :X (NUMBER): 0 2 - :Y (NUMBER): 100000 Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(:Y>=:X) 3 - filter(("OBJECT_ID">=:X AND "OBJECT_ID"<=:Y)) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] 52 rows selected.
五、 10046事件
10046事件是Oracle诊断事件之一，通过下面的命令开启
alter session set events '10046 trace name context forever, level 12'
或
oradebug event '10046 trace name context forever, level 12'
开启10046事件的会话会在user_dump_dest或diagnostic_dest目录下生成裸trace文件，通过tkprof格式化后可以成为较友好的格式，其中会记录开启10046期间所有执行过的语句的真实执行计划，因为10046事件是一大块内容，这里就不展开说明了，常规情况下一到四的方法足够使用了。
