sql执行计划：把SQL语句拆分为每个的操作步骤组合，按照一定的顺序执行得出结果，查看并看懂执行计划是调优的关键步骤
大多数人比较喜欢用工具直接看，以下来说明这些方式的不同用处
10g以后可以查看AWR中的语句执行计划，也可以查看当前内存中游标的执行计划。
主要有以下五个方法：
DISPLAY - 格式化并显示plan table中内容，类似工具的F5（EXPLAIN PLAN FOR …）
DISPLAY_AWR -格式和显示存储在AWR中的SQL语句的执行计划。数据来源: DBA_HIST_SQL_PLAN,DBA_HIST_SQLTEXT
DISPLAY_CURSOR -格式和显示任何加载游标执行计划的内容。数据来源：V$SQL_PLAN和V$SQL_PLAN_STATISTICS_ALL
DISPLAY_SQL_PLAN_BASELINE - 显示一个或多个执行计划的SQL语句通过SQL handle 标识 。数据来源：DBA_SQL_PLAN_BASELINES
DISPLAY_SQLSET - 格式和显示存储在sql调优集中的执行计划内容。数据来源：ALL_SQLSET_STATEMENTS、ALL_SQLSET_PLANS
最常用的DISPLAY_AWR和DISPLAY_CURSOR，来阐述具体的调用方式，显示的内容
显示当前shared pool->library cache中的执行计划
DBMS_XPLAN.DISPLAY_CURSOR( sql_id IN VARCHAR2 DEFAULT NULL, cursor_child_no IN NUMBER DEFAULT 0, format IN VARCHAR2 DEFAULT ‘TYPICAL‘);
| 参数 | 说明（常用说明） |
| sql_id | SQL的唯一标识，取SELECT SQL_ID FROM V$SQL，为null的情况下，取上一条语句的执行计划 |
| cursor_child_no | 子游标的id号，为null，取出全部子游标的执行计划 |
| format | 输出格式化 默认TYPICAL ADVANCED +PEEKED_BINDS ALLSTATS |
例子查看
#默认格式输出
select * from table(dbms_xplan.display_cursor(‘bjqjt2dfvya84‘,null)) SQL_ID bjqjt2dfvya84, child number 0 ------------------------------------- select * from emp where empno=7521 Plan hash value: 2949544139 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPNO"=7521) ＃格式化为ADVANCED +PEEKED_BINDS select * from table(dbms_xplan.display_cursor(‘bjqjt2dfvya84‘,null,‘ADVANCED +PEEKED_BINDS‘)) SQL_ID bjqjt2dfvya84, child number 0 ------------------------------------- select * from emp where empno=7521 Plan hash value: 2949544139 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| | -------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / EMP@SEL$1 2 - SEL$1 / EMP@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE(‘11.2.0.3‘) DB_VERSION(‘11.2.0.3‘) OPT_PARAM(‘optimizer_dynamic_sampling‘ 0) ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO")) END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPNO"=7521) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10], "EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22] 2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]
＃格式化ALLSTATS 两种处理方式： * hint ‘gather_plan_statistics‘ is used for the statement or * parameter ‘statistics_level‘ is set to ‘ALL‘, at session or system level select /*+gather_plan_statistics*/ * from emp where empno=7521 select * from table(dbms_xplan.display_cursor(‘dvj95t2z1gh2a‘,null,‘ALLSTATS‘)) SQL_ID dvj95t2z1gh2a, child number 0 ------------------------------------- select /*+gather_plan_statistics*/ * from emp where empno=7521 Plan hash value: 2949544139 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 1 |00:00:00.01 | 2 | |* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | 1 | 1 |00:00:00.01 | 1 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPNO"=7521)A-ROW是实际的行数，E-ROW是优化器评估的
显示AWR中的SQL的执行计划，对分析历史SQL慢的相当有帮组
DBMS_XPLAN.DISPLAY_AWR( sql_id IN VARCHAR2, plan_hash_value IN NUMBER DEFAULT NULL, db_id IN NUMBER DEFAULT NULL, format IN VARCHAR2 DEFAULT TYPICAL);
| 参数 | 常用说明 |
| SQL_ID | SQL的唯一标识，取SELECT SQL_ID FROM DBA_HIST_SQLTEXT |
| PLAN_HASH_VALUE | 指定SQL语句的PLAN_HASH_VALUE,忽略取全部的SQL_ID下的执行计划 |
| DB_ID | 默认取V$DATABASE.DATABASE_ID |
| FORMAT | 输出格式化 默认TYPICAL ADVANCED +PEEKED_BINDS |
例子查看
#默认输出的格式 select * from table(dbms_xplan.display_awr(‘bjqjt2dfvya84‘)); SQL_ID bjqjt2dfvya84 -------------------- select * from emp where empno=7521 Plan hash value: 2949544139 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 | | 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| | -------------------------------------------------------------------------------------- #通过格式化ADVANCED +PEEKED_BINDS select * from table(dbms_xplan.display_awr(‘bjqjt2dfvya84‘,FORMAT=>‘ADVANCED +PEEKED_BINDS‘)); SQL_ID bjqjt2dfvya84 -------------------- select * from emp where empno=7521 Plan hash value: 2949544139 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 | | 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| | -------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / EMP@SEL$1 2 - SEL$1 / EMP@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE(‘11.2.0.3‘) DB_VERSION(‘11.2.0.3‘) OPT_PARAM(‘optimizer_dynamic_sampling‘ 0) ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO")) END_OUTLINE_DATA */
scott@GULL> set autotrace
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
可以体现执行语句的执行计划和统计信息
scott@GULL> SET autotrace trace scott@GULL> select * from emp where empno=7521; 执行计划 ---------------------------------------------------------- Plan hash value: 2949544139 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPNO"=7521) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 902 bytes sent via SQL*Net to client 508 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed scott@GULL>
直接取数据字典的源数据
scott@GULL> col id format 99; scott@GULL> col parent_id format 99999; scott@GULL> col operation format a25; scott@GULL> col options format a20; scott@GULL> select id,parent_id,operation,options from V$SQL_PLAN where sql_id=‘bjqjt2dfvya84‘; ID PARENT_ID OPERATION OPTIONS --- --------- ------------------------- -------------------- 0 SELECT STATEMENT 1 0 TABLE ACCESS BY INDEX ROWID 2 1 INDEX UNIQUE SCAN
查看AWR中执行计划，调用这个脚本后，相关的直观展现
scott@GULL> @?/rdbms/admin/awrsqrpt.sql Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 3239200222 GULL 1 gullstby Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Would you like an HTML report, or a plain text report? Enter ‘html‘ for an HTML report, or ‘text‘ for plain text Defaults to ‘html‘ 输入 report_type 的值: html Type Specified: html Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ -------- ------------ ------------ ------------ * 3239200222 1 GULL gullstby gull02 Using 3239200222 for database Id Using 1 for instance number Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing <return> without specifying a number lists all completed snapshots. 输入 num_days 的值: 1 Listing the last day‘s Completed Snapshots Snap Instance DB Name Snap Id Snap Started Level ------------ ------------ --------- ------------------ ----- gullstby GULL 368 01 6月 2016 10:08 1 369 01 6月 2016 10:12 1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 输入 begin_snap 的值: 368 Begin Snapshot Id specified: 368 输入 end_snap 的值: 369 End Snapshot Id specified: 369 Specify the SQL Id ~~~~~~~~~~~~~~~~~~ 输入 sql_id 的值: bjqjt2dfvya84 SQL ID specified: bjqjt2dfvya84 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrsqlrpt_1_368_369.html. To use this name, press <return> to continue, otherwise enter an alternative. 输入 report_name 的值: /home/oracle/0601.html
工具类查看执行计划，非常快捷、简单、能清楚知道每部步骤的优先级，但是他们实际调用的是EXPLAIN PLAN for，写入plan_TABLE，是优化器的预评估，不是正在SQL执行时的执行计划。简单分析是有用的，但是在你的应用程序中SQL很慢，常用的SQL用toad等很快，此时就不要在用工具快捷方式去查看执行计划，要去分析正在执行的执行计划，采用以上的内容方式。
toad(ctrl+e)
10046这个事件是查看SQL具体的执行步骤，等待的是什么事件，实际内部调用的是哪些SQL。SQL语句执行很慢时，可以查看具体等待的是什么，导致执行这么慢。还有对我们了解create 、truncate具体在执行哪些语句有帮组。
SQL> conn /as sysdba 已连接。 SQL> ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 28‘; 会话已更改。 SQL> select * from scott.emp where empno=7521; SQL> select c.value || ‘/‘ || d.instance_name || ‘_ora_‘ || 2 a.spid || ‘.trc‘ || 3 case when e.value is not null then ‘_‘||e.value end trace 4 from v$process a, v$session b, v$parameter c, v$instance d, v$parameter e 5 where a.addr = b.paddr 6 and b.sid = userenv(‘sid‘) 7 and c.name = ‘user_dump_dest‘ 8 and e.name = ‘tracefile_identifier‘; TRACE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/orclstby/orcl/trace/orcl_ora_3288.trc
查看日志文件的内容 .... PARSING IN CURSOR #139774981787448 len=41 dep=0 uid=0 oct=3 lid=0 tim=1464851500943287 hv=3602254372 ad=‘d4180e00‘ sqlid=‘89mgdagbbc2j4‘ select * from scott.emp where empno=7521 END OF STMT PARSE #139774981787448:c=60990,e=224928,p=20,cr=444,cu=0,mis=1,r=0,dep=0,og=1,plh=2949544139,tim=1464851500943287 EXEC #139774981787448:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2949544139,tim=1464851500943287 WAIT #139774981787448: nam=‘SQL*Net message to client‘ ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=5473 tim=1464851500943295 WAIT #139774981787448: nam=‘Disk file operations I/O‘ ela= 29 FileOperation=2 fileno=4 filetype=2 obj#=75336 tim=1464851500943586 WAIT #139774981787448: nam=‘db file sequential read‘ ela= 11120 file#=4 block#=155 blocks=1 obj#=75336 tim=1464851500954730 WAIT #139774981787448: nam=‘db file sequential read‘ ela= 428 file#=4 block#=151 blocks=1 obj#=75335 tim=1464851500955277 FETCH #139774981787448:c=0,e=12010,p=2,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=2949544139,tim=1464851500955332 STAT #139774981787448 id=1 cnt=1 pid=0 pos=1 obj=75335 op=‘TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=2 pw=0 time=11994 us cost=1 size=38 card=1)‘ STAT #139774981787448 id=2 cnt=1 pid=1 pos=1 obj=75336 op=‘INDEX UNIQUE SCAN PK_EMP (cr=1 pr=1 pw=0 time=11487 us cost=0 size=0 card=1)‘ WAIT #139774981787448: nam=‘SQL*Net message from client‘ ela= 297 driver id=1650815232 #bytes=1 p3=0 obj#=75335 tim=1464851500955736 FETCH #139774981787448:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2949544139,tim=1464851500955773 WAIT #139774981787448: nam=‘SQL*Net message to client‘ ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=75335 tim=1464851500955791
采用tkprof格式化10046，看起来更加的清晰 [oracle@gull2 ~]$ tkprof /u01/app/oracle/diag/rdbms/orclstby/orcl/trace/orcl_ora_3288.trc /home/oracle/3288.trc 查看3288.trc SQL ID: 89mgdagbbc2j4 Plan Hash: 2949544139 select *afrom scott.emp where empno=7521 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.06 0.22 0 444 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.01 2 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.06 0.23 2 446 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=2 pw=0 time=11994 us cost=1 size=38 card=1) 1 1 1 INDEX UNIQUE SCAN PK_EMP (cr=1 pr=1 pw=0 time=11487 us cost=0 size=0 card=1)(object id 75336) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net break/reset to client 2 0.00 0.00 SQL*Net message to client 3 0.00 0.00 SQL*Net message from client 2 11.43 11.43 Disk file operations I/O 1 0.00 0.00 db file sequential read 2 0.01 0.01
10053这个事件是查看为什么SQL语句评估出发的执行计划是这样的，优化器如何评估出来这个执行计划的，记录优化器的计算值
SQL> conn /as sysdba 已连接。 SQL> oradebug setmypid; 已处理的语句 SQL> oradebug event 10053 trace name context forever ,level 1; 已处理的语句 SQL> select * from scott.emp where empno=7521; EMPNO ENAME JOB MGR ---------- ------------------------------ --------------------------- ---------- HIREDATE SAL COMM DEPTNO -------------- ---------- ---------- ---------- 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 SQL> oradebug tracefile_name /u01/app/oracle/diag/rdbms/orclstby/orcl/trace/orcl_ora_9109.trc ....... Starting SQL statement dump user_id=0 user_name=SYS module=sqlplus@gull2 (TNS V1-V3) action= sql_id=b250dw0hyz0rs plan_hash_value=-1345423157 problem_type=3 ----- Current SQL Statement for this session (sql_id=b250dw0hyz0rs) ----- select * from scott.emp where empno=7521 sql_text_length=49 sql= select * from scott.emp where empno=7521 ----- Explain Plan Dump ----- ----- Plan Table ----- ============ Plan Table ============ -----------------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | -----------------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 1 | | | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 1 | 00:00:01 | | 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 | | -----------------------------------------------+-----------------------------------+ Predicate Information: ---------------------- 2 - access("EMPNO"=7521) Content of other_xml column =========================== db_version : 11.2.0.3 parse_schema : SYS plan_hash : 2949544139 plan_hash_2 : 1767686164 Outline Data: /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE(‘11.2.0.3‘) DB_VERSION(‘11.2.0.3‘) ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO")) END_OUTLINE_DATA */
1、查看真实的执行计划，使用DBMS_XPLAN.DISPLAY_CUROR这个过程，可以清晰的展示SHARED_POOL-》libarary中的执行计划，不需要你重新去运行SQL语句，根据不同的格式输出你需要的内容，可以查看出统计信息是否陈旧、谓词中的字段类型是否有隐式转化（导致索引不能使用）等
2、DBMS_XPLAN.DISPLAY_AWR，查看AWR中的执行计划，是否存在多个执行计划，代价比较高-》消耗性能厉害的，更加直观的可以调用?/rdbms/admin/awrsqrpt.sql
3、sql*plus autotrace 需要重新去运行一遍SQL语句，查看现在运行的这个语句的执行计划，无法查看到，你程序中运行的那条语句的执行计划
4、工具调用（如toad 、PL/SQL DEV），实际采用的EXPLAIN PLAN FOR... 保存到plan_TABLE，是优化器的预评估。
5、10046的事件使用的范围在调试一个语句执行很慢，想清楚具体等待的是什么，慢在哪个阶段，还可以查看一些DDL的语句，内部语句的构造的方式，但查看执行计划，10046不是特别的方便，需要重新执行SQL语句，到服务器端拿日志文件
6、10053的事件可以用来分析 SQL语句为什么会做这个执行计划（如没有走索引，而是走了全表扫描）
原文：http://www.cnblogs.com/gull/p/5547905.html