SQL是一种非过程化的语言，我们定义的sql语句仅仅告诉数据库我们需要什么样的数据，并没有告诉数据库如何获取这些数据。而执行计划正式数据库获取这些数据的方式和步骤。在日常开发中，为了优化数据库的需要，我们常常需要获取sql语句的执行计划，通过它判断
sql是一种非过程化的语言，我们定义的sql语句仅仅告诉数据库我们需要什么样的数据，并没有告诉数据库如何获取这些数据。而执行计划正式数据库获取这些数据的方式和步骤。在日常开发中，为了优化数据库的需要，我们常常需要获取sql语句的执行计划，通过它判断语句执行性能的高低，进而进行优化。那么怎么获得执行计划呢？
1、获取SQL执行计划的方式
- 使用explain plan工具
- 查询动态性能视图
- 启用执行计划跟踪功能，即autotrace功能
-
使用PL/SQL Developer提供的获取执行计划方法
下面分别详细介绍下及几种获取执行计划的方式：
1.1 使用explain plan工具
explain plan命令可以将一条SQL语句预估的执行计划插入到计划表plan table，然后查询该表来获取预估的执行计划。 explain plan命令将SQL语句的执行计划插入到计划表，并不会执行隐式commit，所以如果想要持久化到数据库，需要显示commit。
提示：explain plan命令获得的执行计划是预估的，因为它并不是该SQL语句真正执行时的执行计划，随着oracle库中参数、表数据等因素的变化（比如表的数据的太少，虽然某些字段上建了索引，在执行时索引并不一定真正其效果，但是explain plan获得执行计划可能是走索引的），预估的执行计划往往和实际是有出入的。
1.1.1 前提条件
预估的执行计划要存入plan table表，所以执行explain plan之前要确保plan table表的存在。在oracle 10g之前，plan table表是需要手动创建的，从oracle 10g开始，oracle会自动创建全局plan table表（表名为PLAN_TABLE）,供所有用户使用。 如何要创建表plan_table，或创建其他的计划表呢？ 找到$ORACLE_HOME/rdbms/admin/utlxplan.sql脚本文件，可以根据该脚本创建自己想要的计划表，只需要根据情况修改表名plan_table即可。 提示：关于计划表plan_table介绍请参考《Oracle 10g版本计划表PLAN_TABLE列的说明》
1.1.2 explain plan语法
EXPLAIN PLAN
[SET STATEMENT_ID = 'statement_id']
[INTO table_name]
FOR sql_statement
说明：
1、方括号[]中的语句是可选的。
2、相关选项说明
| 选项 |
说明 |
| statement_id |
标识存入表plan_table的sql语句的执行计划，不同sql语句的执行计划可以共用相同的statement_id标识，但是为了进行区分，尽量唯一，可以通过statement_id作为条件查询plan_table表中的执行计划。 |
| table_name |
执行计划表的名称，如果没有指定，默认表名plan_table，如果指定的表不是plan_table表，而是自定义的，必须要确保该表和标准的计划表结果相同。 |
| sql_statement |
sql语句，该sql语句可以包含绑定变量。 |
1.1.3 执行计划查询和展现
下面介绍两种查询执行计划的方法：使用dbms_xplan包、自定义层次查询语句，这两种方法都是从计划表查询数据，通过使用dbms_xplan包查询执行计划，默认计划表名为plan_table, 而且查询出来的参数数据也是固定的；但是通过自定义的层次查询语句不但可以自由指定计划表，还可以根据情况选择查询参数。
- 使用DBMS_XPLAN包查看执行计划
语句如下：
SELECT * FROM TABLE(dbms_xplan.display());
如：
SQL> explain plan
2 for select * from t_student where gid = 1;
Explained
SQL> SELECT * FROM TABLE(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1947974170
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 595 | 0 (0)
| 1 | TABLE ACCESS BY INDEX ROWID| T_STUDENT | 1 | 595 | 0 (0)
|* 2 | INDEX UNIQUE SCAN | SYS_C0010947 | 1 | | 0 (0)
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("GID"=1)
14 rows selected 这种查看执行计划的方法比较简便，但是也有局限性，它查询参数数据有限，只能从名为plan_table的计划表查询数据，而是展现参数也是固定的(仅限Operation、Name、Rows、Bytes和Cost的参数数据)，不过这些数据一般足够了。如果想获得更多的参数数据，可以使用自定义的层次查询语句。- 自定义层次查询语句执行计划
查询语句如下：
SELECT RTRIM(LPAD(' ', 2*LEVEL)||
RTRIM(operation)||' '||
RTRIM(options)||' '||
object_name) query_plan, cost, cardinality, time
FROM plan_table
CONNECT BY PRIOR id = parent_id
START WITH ID = 0;这种方法使用者可以根据自己的需要查看计划表中字段，上面语句只写了query_plan、cost、cardinality和time，其他字段请参考《Oracle 10g版本计划表PLAN_TABLE列的说明》。
如：
SQL> explain plan
2 for select * from t_student where gid = 1;
Explained
SQL> SELECT RTRIM(LPAD(' ', 2*LEVEL)||
2 RTRIM(operation)||' '||
3 RTRIM(options)||' '||
4 object_name) query_plan, cost, cardinality, time
5 FROM plan_table
6 CONNECT BY PRIOR id = parent_id
7 START WITH ID = 0;
QUERY_PLAN COST CARDINALITY TIME
-------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
SELECT STATEMENT 0 1 1
TABLE ACCESS BY INDEX ROWID T_STUDENT 0 1 1
INDEX UNIQUE SCAN SYS_C0010947 0 1 1
1.2、查询动态性能视图
上面1.1章节介绍查询的执行计划，都是预估的执行计划。而动态性能视图中缓存的SQL执行信息，则是真实的执行计划。
下面介绍一下几个动态视图：
- V$SQL:缓存着已执行的SQL语句的时间消耗、CPU或IO需求等信息。
- V$SQL_PLAN:缓存SQL语句的执行计划信息。
- V$SQL_PLAN_STATICS:记录着执行计划每个步骤执行次数、IO次数以及处理记录数。
通过动态性能视图查询较高SQL的执行计划的思路是：
1.通过挖掘V$SQL中具有较高时间消耗、CPU或IO需求的SQL语句。这一步通过自定义SQL完成。比如消耗时间最多的前10条语句，
查询语句如下：
SELECT sql_id, child_number, sql_text, elapsed_time
FROM ( SELECT sql_id, child_number, sql_text, elapsed_time, cpu_time, disk_reads,
RANK () OVER (ORDER BY elapsed_time DESC) AS elapsed_rank
FROM v$sql)
WHERE elapsed_rank <= 10; 2.通过使用V$SQL_PLAN和V$SQL_PLAN_STATICS找到不尽如人意的SQL语句的执行计划。这一步不需要我们写SQL,DBMS_XPLAN已经为提供可查询方法DBMS_XPLAN.display_cursor().具体使用请参考《DBMS_XPLAN包中函数的使用》。
例子：
SQL> SELECT sql_id, child_number, sql_text, elapsed_time
2 FROM ( SELECT sql_id, child_number, sql_text, elapsed_time, cpu_time, disk_reads,
3 RANK () OVER (ORDER BY elapsed_time DESC) AS elapsed_rank
4 FROM v$sql)
5 WHERE elapsed_rank <= 10;
SQL_ID CHILD_NUMBER SQL_TEXT ELAPSED_TIME
------------- ------------ -------------------------------------------------------------------------------- ------------
6gvch1xu9ca3g 0 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN : 40748737
b6usrg82hwsa3 0 call dbms_stats.gather_database_stats_job_proc ( ) 28589079
cvn54b7yz0s8u 0 select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ wher 13061969
5jvf84zg4c49n 0 select s.synonym_name as object_name, o.object_type from sys.all_synonyms s, 10839744
02577v815yp77 0 BEGIN :success := dbms_ha_alerts_prvt.post_instance_up; END; 8716832
3am9cfkvx7gq1 0 CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:target_guid,:metric_guid,:metric_val 7427452
4jrfrtx4u6zcx 0 SELECT TASK_TGT.TARGET_GUID TARGET_GUID, LEAD(TASK_TGT.TARGET_GUID,1) OVER (ORDE 6817926
3ktacv9r56b51 0 select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(proper 6396579
c0j6cx9kzjf7g 0 SELECT EXTRACTVALUE(VALUE(T), '/select_list_item/pos') + 1 POS, EXTRACTVALUE(VAL 6221768
f6cz4n8y72xdc 0 SELECT space_usage_kbytes FROM v$sysaux_occupants WHERE occupant_name = 'SQL_ 5162914
10 rows selected
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor('at5b8k7swu5w4', '0', 'TYPICAL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID at5b8k7swu5w4, child number 0
-------------------------------------
select * from t_student where gid = 2
Plan hash value: 1947974170
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)
| 1 | TABLE ACCESS BY INDEX ROWID| T_STUDENT | 1 | 595 | 0 (0)
|* 2 | INDEX UNIQUE SCAN | SYS_C0010947 | 1 | | 0 (0)
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("GID"=2)
19 rows selected1.3、启用执行计划跟踪功能
工具EXPLAIN PLAN和DBMS_XPLAN可以获取预估执行计划和缓存中的执行信息，他们是主要的SQL调优工具，但是他们只是获取单个SQL的执行信息。如果想获取特定事务或是应用优化一批SQL语句，跟踪（Tracing）是个有效的方法。跟踪可以提供EXPLAIN PLAN与DBMS_XPLAIN能提供的所有信息，并且更加准确详尽，因为它是执行SQL时的真实的执行计划。
<span> SQL跟踪包括两个基本组成部分： </span><span> 1. SQL跟踪：在当前会话或其他会话中启动跟踪。 </span><span><span> 2. tkprof工具：格式化跟踪文件，因为生成的跟踪文件对使用者不容易读取，使用该工具可以转换为使用者跟容易读取的格式。具体请参加《tkprof命令格式化分析跟踪文件》</span></span>
<span><span> <strong>启动跟踪的方式有： </strong></span></span><span><span> 1. <span>启动</span>当前会话的跟踪 </span></span><span><span> 2.启动其他会话的跟踪 </span></span><span><span> 3.使用登陆触发器启动跟踪</span></span>
下面分别详细介绍3种跟踪启动的方式。
1.3.1 启动当前会话的跟踪
1.3.1.1启动当前会话跟踪的两种方法
启动当前会话的跟踪的两种方法：
<span><span> <strong> 1.设置参数</strong><span><strong>SQL_TRACE 为true</strong>，语句如下： </span></span><span> <span> ALTER SESSION SET </span></span><span><span>SQL_TRACE </span><span>= TRUE;</span></span></span>
该语句创建基本的跟踪，跟踪信息包括SQL语句的执行统计信息与执行计划，但不会包含绑定变量的
手绘涂鸦教育信息图表矢量素材下载手绘涂鸦教育信息图表矢量素材适用于企业报告、项目管理工具、效率提升研讨会、时间线图表、商务演示文稿、数据分析报告、教育和培训材料、时间管理软件界面、会议和研讨会宣传材料、年度业绩回顾、员工绩效评估、市场研究和分析报告以及任何需要展示时间管理和统计数据的商务场合。设计的AI格式素材。
值，也不包含等待各种事件花费的时间。如果要获取这些信息，请使用第2种启动方式。
<span> <strong> 2.<span>调用DBMS_SESSION方法</span></strong><span>，语法如下：</span> </span><span><span><span> <span>DBMS_SESSION.session_trace_enable ( </span></span></span><span><span><span> waits IN BOOLEAN DEFAULT TRUE, </span></span></span><span><span><span> binds IN BOOLEAN DEFAULT FALSE</span></span></span><span><span></span></span><span><span><span> );</span></span></span></span>
下面介绍下函数参数的含义：
| 参数 | 说明 |
| waits | 如果为TRUE，将收集等待信息；如果为FALSE，则不收集。 |
| binds | 如果为TRUE，将收集绑定变量信息；如果为FALSE，则不收集。 |
<span></span>
调用例子，在Command Window中输入如下命令：
begin
dbms_session.session_trace_enable(true,
true);
end;
/
执行完后，执行如下语句查询是否执行成功：
SQL> SELECT t.sql_trace, t.sql_trace_waits, t.sql_trace_binds FROM v$session t WHERE t.sid = USERENV('SID');
SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS
--------- --------------- ---------------
ENABLED TRUE TRUE
收集的结果如下（tkprof工具格式化后）：
SQL ID: 518m7y4zt6xkp<span><span><span>
Plan Hash: 1947974170
select *
from
t_student where gid = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.01 2 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.02 2 2 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 79
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T_STUDENT (cr=2 pr=2 pw=0 time=0 us cost=1 size=19 card=1)
1 INDEX UNIQUE SCAN SYS_C0010947 (cr=1 pr=1 pw=0 time=0 us cost=0 size=0 card=1)(object id 73110)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.04 0.04
Disk file operations I/O 1 0.00 0.00
db file sequential read 2 0.01 0.01</span></span></span> 其中第20到26行就是时间等待信息，如果使用第1种方法启动跟踪信息，将没有这部分的内容。1.3.1.2 定位跟踪文件
启动会话跟踪之后，如何才能找到当前会话的跟踪文件呢？有两种方法
1.3.1.2.1 通过查询语句
下面语句用来查询当前会话跟踪文件：
select p.TRACEFILE from v$session s, v$process p
where s.PADDR = p.ADDR
and s.SID = USERENV('SID');
说明：USERENV('SID')得到的是当前会话的SID。
如下：
SQL> select p.TRACEFILE from v$session s, v$process p
2 where s.PADDR = p.ADDR
3 and s.SID = USERENV('SID');
TRACEFILE
--------------------------------------------------------------------------------
d:\oracle\diag\rdbms\david\orcl\trace\orcl_ora_7220.trc
1.3.1.2.2 标识跟踪文件
为会话指定一个跟踪文件的标识符，通过设置tracefile_identifier参数实现：
ALTER SESSION SET tracefile_identifier = 标识符
如果我们设定tracefile_identifier为LGL，即执行：
SQL> alter session set tracefile_identifier=LGL;
那么跟踪文件目录中将生成一个类似orcl_ora_3668_LGL.trc格式的跟踪文件。
跟踪文件的目录保存在user_dump_dest参数：
SQL> show parameter user_dump_dest; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ user_dump_dest string d:\oracle\diag\rdbms\david\orcl\trace
1.3.2 启动其他会话的跟踪
通过DBMS_MONITOR包中的session_trace_enable()方法启动别的会话的跟踪，语法如下：
<span><span><span><span> <span>DBMS_MONITOR.<span>session_trace_enable( </span></span></span></span></span><span><span><span><span><span> session_id IN BINARY DEFAULT NULL, </span></span></span></span></span><span><span><span><span><span> serial_num IN BINARY DEFAULT NULL, </span></span></span></span></span><span><span><span><span><span> waits IN BOOLEAN DEFAULT TRUE, </span></span></span></span></span><span><span><span><span><span> binds <span> IN BOOLEAN DEFAULT FALSE </span></span></span></span></span></span><span><span><span><span><span> )</span></span></span></span></span></span>
下面介绍下函数参数的含义：
| 参数 |
说明 |
| session_id | 对应V$SESSION视图中的列SID的值。 |
| serial_num | 对应V$SESSION视图中的列SERIAL#的值。 |
| waits |
如果为TRUE，将收集等待信息；如果为FALSE，则不收集。 |
| binds |
如果为TRUE，将收集绑定变量信息；如果为FALSE，则不收集。 |
在使用该函数之前先通过查询V$SESSION查找要跟踪会话，比如查询实例名包含david的会话状态：
SQL> SELECT t.sid, t.serial#, t.sql_trace FROM v$session t WHERE LOWER(t.service_name) LIKE '%david%';
SID SERIAL# SQL_TRACE
---------- ---------- ---------
7 249 DISABLED
8 41 ENABLED
71 125 ENABLED<span><span> 通过上面查询结果可知SID为7的会话的没有启动会话跟踪，使用<span>session_trace_enable()方法启动SID为7的 </span></span></span><span><span><span>会话的跟踪，打开Command Window窗口，执行如下语句：</span></span></span>
begin
dbms_monitor.session_trace_enable(session_id =>7,
serial_num =>249,
waits =>true,
binds =>true);
end;
/ 再次查询SID为7的会话跟踪情况，语句如下：
SQL> SELECT t.sid, t.serial#, t.sql_trace FROM v$session t WHERE t.sid = 7;
SID SERIAL# SQL_TRACE
---------- ---------- ---------
7 249 ENABLED从查询结果可以看出，SID为7的会话已经启动了跟踪。
1.3.3 使用登陆触发器启动跟踪
建立一个触发器(执行到sys用户中)，用户登录数据库之后就启动会话的跟踪，触发器创建脚本如下：
CREATE OR REPLACE TRIGGER trace_login_trigger
AFTER LOGON ON DATABASE
BEGIN
--设置用户ADMIN的会话跟踪
IF USER = 'ADMIN' THEN
--设置跟踪文件标识
EXECUTE IMMEDIATE 'alter session set tracefile_identifier=ADMINCC';
--启动会话跟踪
DBMS_SESSION.session_trace_enable( waits =>TRUE,
binds =>FALSE
);
END IF;
END;
/ 
