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;explainedsql> 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;explainedsql> 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 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 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 = 2plan 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 selected
1.3、启用执行计划跟踪功能 工具explain plan和dbms_xplan可以获取预估执行计划和缓存中的执行信息，他们是主要的sql调优工具，但是他们只是获取单个sql的执行信息。如果想获取特定事务或是应用优化一批sql语句，跟踪（tracing）是个有效的方法。跟踪可以提供explain plan与dbms_xplain能提供的所有信息，并且更加准确详尽，因为它是执行sql时的真实的执行计划。
sql跟踪包括两个基本组成部分： 1. sql跟踪：在当前会话或其他会话中启动跟踪。 2. tkprof工具：格式化跟踪文件，因为生成的跟踪文件对使用者不容易读取，使用该工具可以转换为使用者跟容易读取的格式。具体请参加《tkprof命令格式化分析跟踪文件》
启动跟踪的方式有： 1. 启动当前会话的跟踪 2.启动其他会话的跟踪 3.使用登陆触发器启动跟踪
下面分别详细介绍3种跟踪启动的方式。
1.3.1 启动当前会话的跟踪1.3.1.1启动当前会话跟踪的两种方法 启动当前会话的跟踪的两种方法：
1.设置参数sql_trace 为true，语句如下： alter session set sql_trace = true;
该语句创建基本的跟踪，跟踪信息包括sql语句的执行统计信息与执行计划，但不会包含绑定变量的
值，也不包含等待各种事件花费的时间。如果要获取这些信息，请使用第2种启动方式。
2.调用dbms_session方法，语法如下： dbms_session.session_trace_enable ( waits in boolean default true, binds in boolean default false );
下面介绍下函数参数的含义：
参数 说明
waits 如果为true，将收集等待信息；如果为false，则不收集。
binds 如果为true，将收集绑定变量信息；如果为false，则不收集。
调用例子，在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: 518m7y4zt6xkpplan hash: 1947974170select * from t_student where gid = 1call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------parse 1 0.01 0.01 0 0 0 0execute 1 0.00 0.00 0 0 0 0fetch 1 0.00 0.01 2 2 0 1------- ------ -------- ---------- ---------- ---------- ---------- ----------total 3 0.01 0.02 2 2 0 1misses in library cache during parse: 1optimizer mode: all_rowsparsing 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
其中第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()方法启动别的会话的跟踪，语法如下：
dbms_monitor.session_trace_enable( session_id in binary default null, serial_num in binary default null, waits in boolean default true, binds in boolean default false )
下面介绍下函数参数的含义：
参数
说明
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
通过上面查询结果可知sid为7的会话的没有启动会话跟踪，使用session_trace_enable()方法启动sid为7的会话的跟踪，打开command window窗口，执行如下语句：
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 databasebegin --设置用户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;/