什么是执行计划？
sql是一种傻瓜式语言，每一个条件就是一个需求，访问的顺序不同就形成了不同的执行计划。oracle必须做出选择，一次只能有一种访问路径。执行计划是一条查询语句在oracle中的执行过程或访问路径的描述。
执行计划的选择：
通常一条sql有多个执行计划，那我们如何选择？那种执行开销更低，就意味着性能更好，速度更快，我们就选哪一种，这个过程叫做oracle的解析过程，然后oracle会把更好的执行计划放到sga的shared pool里，后续再执行同样的sql只需在shared pool里获取就行了，不需要再去分析。
执行计划选定依据：
根据统计信息来选择执行计划。
统计信息：
什么是统计信息： 记录数、块数等，具体查看dba_tables / dba_indexes
动态采样：
oracle正常情况下会在每天的某段时间收集统计信息，对于新建的表，oracl如何收集统计信息？采用动态采样。
set autotrace on
set linesize 1000
--执行sql语句
--会出现dynamic sampling used for this statement(level=2)关键
六种执行计划
oracle提供了6种执行计划获取方法，各种方法侧重点不同：
选择时一般遵循以下规则：
1.如果sql执行很长时间才出结果或返回不了结果，用方法1：explain plan for
2.跟踪某条sql最简单的方法是方法1：explain plan for，其次是方法2：set autotrace on
3.如果相关察某个sql多个执行计划的情况，只能用方法4：dbms_xplan.display_cursor或方法6：awrsqrpt.sql
4.如果sql中含有函数，函数中有含有sql，即存在多层调用，想准确分析只能用方法5：10046追踪
5.想法看到真实的执行计划，不能用方法1：explain plan for和方法2：set autotrace on
6.想要获取表被访问的次数，只能用方法3：statistics_level = all
oracle如何收集统计信息：
1、oracle会选择在一个特定的时间段收集表和索引的统计信息（默认周一至周五：22：00，周六周日：06：00），用户可自行调整，主要为了避开高峰期；
2、表与索引的分析有阈值限制，超过阈值才会自动进行分析。如果数据变化量不大，oracle是不会去分析的；
3、收集方式灵活。可针对分区表的某个分区进行，可采用并行机制来收集表和索引的信息；
如何收集统计信息：
--收集表统计信息
exec dbms_stats.gather_table_stats(ownname => 'aaa', tabname => 'test02',estimate_percent =>10,method_opt => 'for all indexed columns');
--收集索引统计信息
exec dbms_stats.gather_index_stats(ownname => 'aaa',indname => 'id_idx',estimate_percent =>10,degree => '4');
--收集表与索引的统计信息
exec dbms_stats.gather_table_stats(ownname => 'aaa',tabname => 'test02',estimate_percent =>10,method_opt => 'for all indexed columns',cascade => true);
（1）explain plan for
sql> show user user 为 "hr"sql> set linesize 1000sql> set pagesize 2000sql> explain plan for2 select *3 from employees,jobs4 where employees.job_id=jobs.job_id5 and employees.department_id=50;已解释。 sql> select * from table(dbms_xplan.display()); plan_table_output----------------------------------------------------------------------------------------------------------------------------------------------------------------------------plan hash value: 303035560------------------------------------------------------------------------------------------| id | operation | name | rows | bytes | cost (%cpu)| time |------------------------------------------------------------------------------------------| 0 | select statement | | 45 | 4590 | 6 (17)| 00:00:01 || 1 | merge join | | 45 | 4590 | 6 (17)| 00:00:01 || 2 | table access by index rowid| jobs | 19 | 627 | 2 (0)| 00:00:01 || 3 | index full scan | job_id_pk | 19 | | 1 (0)| 00:00:01 ||* 4 | sort join | | 45 | 3105 | 4 (25)| 00:00:01 ||* 5 | table access full | employees | 45 | 3105 | 3 (0)| 00:00:01 |------------------------------------------------------------------------------------------predicate information (identified by operation id):---------------------------------------------------4 - access("employees"."job_id"="jobs"."job_id")filter("employees"."job_id"="jobs"."job_id")5 - filter("employees"."department_id"=50)已选择19行。
优点：无需真正执行，快捷方便；
缺点：
1、没有输出相关统计信息，例如产生了多少逻辑读，多少次物理读，多少次递归调用的情况；
2、无法判断处理了多少行；
3、无法判断表执行了多少次
（2）set autotrace on
用法：
命令作用：
set autot[race] off 停止autotrace
set autot[race] on 开启autotrace，显示autotrace信息和sql执行结果
set autot[race] traceonly 开启autotrace，仅显示autotrace信息
set autot[race] on explain 开启autotrace，仅显示autotrace的explain信息
set autot[race] on statistics 开启autotrace，仅显示autotrace的statistics信息
sql> set autotrace onsql> select * from employees,jobs where employees.job_id=jobs.job_id and employees.department_id=50;--输出结果（略）-- ...已选择45行。 执行计划----------------------------------------------------------plan hash value: 303035560------------------------------------------------------------------------------------------| id | operation | name | rows | bytes | cost (%cpu)| time |------------------------------------------------------------------------------------------| 0 | select statement | | 45 | 4590 | 6 (17)| 00:00:01 || 1 | merge join | | 45 | 4590 | 6 (17)| 00:00:01 || 2 | table access by index rowid| jobs | 19 | 627 | 2 (0)| 00:00:01 || 3 | index full scan | job_id_pk | 19 | | 1 (0)| 00:00:01 ||* 4 | sort join | | 45 | 3105 | 4 (25)| 00:00:01 ||* 5 | table access full | employees | 45 | 3105 | 3 (0)| 00:00:01 |------------------------------------------------------------------------------------------predicate information (identified by operation id):---------------------------------------------------4 - access("employees"."job_id"="jobs"."job_id")filter("employees"."job_id"="jobs"."job_id")5 - filter("employees"."department_id"=50)统计信息----------------------------------------------------------0 recursive calls0 db block gets13 consistent gets0 physical reads0 redo size5040 bytes sent via sql*net to client433 bytes received via sql*net from client4 sql*net roundtrips to/from client1 sorts (memory)0 sorts (disk)45 rows processed
优点：
1、可以输出运行时的相关统计信息（产生多少逻辑读、多少次递归调用、多少次物理读等）；
2、虽然要等语句执行完才能输出执行计划，但是可以有traceonly开关来控制返回结果不打屏输出；
缺点：
1、必须要等sql语句执行完，才出结果；
2、无法看到表被访问了多少次；
（3）statistics_level=all
步骤一：alter session set statistics_level=all;
步骤二：执行待分析的sql
步骤三：select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value’,null,'allstats last'));
sql> alter session set statistics_level=all;sql> select * from employees,jobs where employees.job_id=jobs.job_id and employees.department_id=50;--输出结果--...已选择45行。 sql> set linesize 1000sql> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); plan_table_output-----------------------------------------------------------------------------------------------------------------------------------sql_id d8jzhcdwmd9ut, child number 0-------------------------------------select * from employees,jobs where employees.job_id=jobs.job_id andemployees.department_id=50plan hash value: 303035560----------------------------------------------------------------------------------------------------------------------------------------| id | operation | name | starts | e-rows | a-rows | a-time | buffers | reads | omem |1mem | used-mem |----------------------------------------------------------------------------------------------------------------------------------------| 0 | select statement | | 1 | | 45 |00:00:00.01 | 13 | 8 | || |plan_table_output-------------------------------------------------------------------------------------------------------------------------------------| 1 | merge join | | 1 | 45 | 45 |00:00:00.01 | 13 | 8 | || || 2 | table access by index rowid| jobs | 1 | 19 | 19 |00:00:00.01 | 6 | 2 | || || 3 | index full scan | job_id_pk | 1 | 19 | 19 |00:00:00.01 | 3 | 1 | || ||* 4 | sort join | | 19 | 45 | 45 |00:00:00.01 | 7 | 6 | 6144 |6144 | 6144 (0)||* 5 | table access full | employees | 1 | 45 | 45 |00:00:00.01 | 7 | 6 | || |----------------------------------------------------------------------------------------------------------------------------------------predicate information (identified by operation id):---------------------------------------------------4 - access("employees"."job_id"="jobs"."job_id")plan_table_output-----------------------------------------------------------------------------------------------------------------------------filter("employees"."job_id"="jobs"."job_id")5 - filter("employees"."department_id"=50)已选择25行。
关键字解读：
1、starts：sql执行的次数；
2、e-rows：执行计划预计返回的行数；
3、r-rows：执行计划实际返回的行数；
4、a-time：每一步执行的时间(hh:mm:ss.ff)，根据这一行可知sql耗时在哪些地方；
5、buffers：每一步实际执行的逻辑读或一致性读；
6、reads：物理读；
优点：
1、可以清晰的从starts得出表被访问多少次；
2、可以从e-rows和a-rows得到预测的行数和真实的行数，从而可以准确判断oracle评估是否准确；
3、虽然没有准确的输出运行时的相关统计信息，但是执行计划中的buffers就是真实的逻辑读的数值；
缺点：
1、必须要等执行完后才能输出结果；
2、无法控制结果打屏输出，不像autotrace可以设置traceonly保证不输出结果；
3、看不出递归调用，看不出物理读的数值
（4）dbms_xplan.display_cursor获取
步骤1：select * from table( dbms_xplan.display_cursor('&sql_id') ); --该方法是从共享池得到
注释：
1、还有1种方法，select * from table( dbms_xplan.display_awr('&sql_id') ); --该方法是从awr性能视图里面获取
2、如果有多个执行计划，可用以下方法查出：
select * from table(dbms_xplan.display_cursor('&sql_id',0));select * from table(dbms_xplan.display_cursor('&sql_id',1));*/sql> select * from table(dbms_xplan.display_cursor('5hkd01f03y43d'));plan_table_output--------------------------------------------------------------------------------sql_id 5hkd01f03y43d, child number 0-------------------------------------select * from test where table_name = 'log$'plan hash value: 2408911181--------------------------------------------------------------------------------| id | operation | name | rows | bytes | cost (%cpu)|--------------------------------------------------------------------------------| 0 | select statement | | | | 2 (100)|| 1 | table access by index rowid| test | 1 | 241 | 2 (0)||* 2 | index range scan | idx_test_1 | 1 | | 1 (0)|--------------------------------------------------------------------------------predicate information (identified by operation id):---------------------------------------------------2 - access("table_name"='log$')19 rows selected
注释：如何查看1个sql语句的sql_id，可直接查看v$sql
优点：
1、知道sql_id即可得到执行计划，与explain plan for一样无需执行；
2、可得到真实的执行计划
缺点：
1、没有输出运行的统计相关信息；
2、无法判断处理了多少行；
3、无法判断表被访问了多少次；
（5）事件10046 trace跟踪
步骤1：alter session set events '10046 trace name context forever,level 12'; --开启追踪
步骤2：执行sql语句；
步骤3：alter session set events '10046 trace name context off'; --关闭追踪
步骤4：找到跟踪后产生的文件（开启10046前先用‘ls -lrt’看一下文件，执行结束后再看哪个是多出来的文件即可）
步骤5：tkprof trc文件 目标文件 sys=no sort=prsela,exeela,fchela --格式化命令
优点：
1、可以看出sql语句对应的等待事件；
2、如果函数中有sql调用，函数中有包含sql，将会被列出，无处遁形；
3、可以方便的看处理的行数，产生的逻辑物理读；
4、可以方便的看解析时间和执行时间；
5、可以跟踪整个程序包
缺点：
1、步骤繁琐；
2、无法判断表被访问了多少次；
3、执行计划中的谓词部分不能清晰的展现出来
推荐：oracle数据库学习教程
以上就是查看oracle执行计划的方法的详细内容。