ORACLE 执行计划
有关oracle 执行计划几个不错的连接执行计划的一些概念介绍：http://database.51cto.com/art/200611/34273.htm执行计划的例子：http://www.oracleblog.org/working-case/why-not-take-hash-join/执行计划的学习：http://wenku.baidu.com/view/aa01ba09581b6bd97f19eae9.html
ORACLE 执行计划
标签：
小编还为您整理了以下内容，可能对您也有帮助：
Oracle如何查看执行计划
一、通过PL/SQL Dev工具
1、直接File->New->Explain Plan Window，在窗口中执行sql可以查看计划结果。其中，Cost表示cpu的消耗，单位为n%，Cardinality表示执行的行数，等价Rows。
2、先执行 EXPLAIN PLAN FOR select * from tableA where paraA=1，再 select * from table(DBMS_XPLAN.DISPLAY)便可以看到oracle的执行计划了，看到的结果和1中的一样，所以使用工具的时候推荐使用1方法。
注意：PL/SQL Dev工具的Command window中不支持set autotrance on的命令。还有使用工具方法查看计划看到的信息不全，有些时候我们需要sqlplus的支持。
二、通过sqlplus
1．最简单的办法
Sql> set autotrace on
Sql> select * from al;
执行完语句后，会显示explain plan 与 统计信息。
这个语句的优点就是它的缺点，这样在用该方法查看执行时间较长的sql语句时，需要等待该语句执行成功后，才返回执行计划，使优化的周期大大增长。如果不想执行语句而只是想得到执行计划可以采用：
Sql> set autotrace traceonly
这样，就只会列出执行计划，而不会真正的执行语句，大大减少了优化时间。虽然也列出了统计信息，但是因为没有执行语句，所以该统计信息没有用处，如果执行该语句时遇到错误，解决方法为：
(1）在要分析的用户下：
Sqlplus > @ ?
dbmsadminutlxplan.sql
(2) 用sys用户登陆
Sqlplus > @ ?sqlplusadminplustrce.sql
Sqlplus > grant plustrace to user_name;
- - user_name是上面所说的分析用户
2．用explain plan命令
(1) sqlplus > explain plan for select * from testdb.myuser
(2) sqlplus > select * from table(dbms_xplan.display);
上面这2种方法只能为在本会话中正在运行的语句产生执行计划，即我们需要已经知道了哪条语句运行的效率很差，我们是有目的只对这条SQL语句去优化。其实，在很多情况下，我们只会听一个客户抱怨说现在系统运行很慢，而我们不知道是哪个SQL引起的。此时有许多现成的语句可以找出耗费资源比较多的语句，如：
SELECT ADDRESS, substr(SQL_TEXT,1,20) Text, buffer_gets, executions,
buffer_gets/executions AVG FROM v$sqlarea
WHERE executions>0 AND buffer_gets > 100000 ORDER BY 5;
ADDRESS TEXT BUFFER_GETS EXECUTIONS AVG
-------- ---------------------------------------- ----------- ---------- ------------------------------------------------------------
66D83D64 select t.name, (sel 421531 60104 7.01336017
66D9E8AC select t.schema, t.n 1141739 2732 417.913250
66B82BCC select s.synonym_nam 441261 6 73543.5
从而对找出的语句进行进一步优化。当然我们还可以为一个正在运行的会话中运行的所有SQL语句生成执行计划，这需要对该会话进行跟踪，产生trace文件，然后对该文件用tkprof程序格式化一下，这种得到执行计划的方式很有用，因为它包含其它额外信息，如SQL语句执行的每个阶段(如Parse、Execute、Fetch)分别耗费的各个资源情况(如CPU、DISK、elapsed等)。
3、启用SQL_TRACE跟踪所有后台进程活动：
全局参数设置： .OracleHome/admin/SID/pfile中指定: SQL_TRACE = true (10g)
当前session中设置：
SQL> alter session set SQL_TRACE=true;
SQL> select * from al;
SQL> alter session set SQL_TRACE=false;
对其他用户进行跟踪设置：
SQL> select sid,serial#,username from v$session where username='XXX';
SID SERIAL# USERNAME
------ ---------- ------------------
127 31923 A
128 54521 B
开启跟踪:SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,true);
关闭跟踪:SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,false);
然后使用oracle自带的tkprof命令行工具格式化跟踪文件。
4、使用10046事件进行查询：
10046事件级别：
Lv1 - 启用标准的SQL_TRACE功能,等价于SQL_TRACE
Lv4 - Level 1 + 绑定值(bind values)
Lv8 - Level 1 + 等待事件跟踪
Lv12 - Level 1 + Level 4 + Level 8
全局设定：
OracleHome/admin/SID/pfile中指定: EVENT="10046 trace name context forever,level 12"
当前session设定：
开启:SQL> alter session set events '10046 trace name context forever, level 8';
关闭:SQL> alter session set events '10046 trace name context off';
对其他用户进行设置：
SQL> select sid,serial#,username from v$session where username='XXX';
SID SERIAL# USERNAME
------ ---------- ------------------
127 31923 A
SQL> exec dbms_system.set_ev(127,31923,10046,8,'A');
5、使用tkprof格式化跟踪文件： (根据下面SQL语句得到的文件都不存在该目录下,郁闷啊,懵懂啊...)
一般，一次跟踪可以分为以下几步：
1、界定需要跟踪的目标范围，并使用适当的命令启用所需跟踪。
2、经过一段时间后，停止跟踪。此时应该产生了一个跟踪结果文件。
3、找到跟踪文件，并对其进行格式化，然后阅读或分析。
--使用一下SQL找到当前session的跟踪文件：
SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_name
from
( select p.spid from v$mystat m,v$session s, v$process p
where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
( select t.instance from v$thread t,v$parameter v
where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,
( select value from v$parameter where name = 'user_mp_dest' ) d;
-- 其它用户的 session
SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_name
from
( select p.spid from v$session s, v$process p
where s.sid= '27' and s. SERIAL#= '30' and p.addr = s.paddr) p,
( select t.instance from v$thread t,v$parameter v
where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,
( select value from v$parameter where name = 'user_mp_dest' ) d;
--查找后使用tkprof命令,将TRACE文件格式为到D盘的explain_format.txt文件中
SQL> $tkprof d:/oracle/admin/FZLGFM/ump/fzlgfm_ora_3468.trc d:/explain_format.txt
文件内容大致如下(看不太懂....懵懂啊.....天啊....神啊.....过几时就懂了/////////////)
TKPROF: Release 9.2.0.1.0 - Proction on 星期二 4月 20 13:59:20 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: d:/oracle/admin/FZLGFM/ump/fzlgfm_ora_3468.trc
Sort options: default
********************************************************************************
count = number of times OCI procere was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
alter session set events '10046 trace name context forever, level 8'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache ring parse: 0
Misses in library cache ring execute: 1
Optimizer goal: CHOOSE
Parsing user id: SYS
Oracle如何查看执行计划
一、通过PL/SQL Dev工具
1、直接File->New->Explain Plan Window，在窗口中执行sql可以查看计划结果。其中，Cost表示cpu的消耗，单位为n%，Cardinality表示执行的行数，等价Rows。
2、先执行 EXPLAIN PLAN FOR select * from tableA where paraA=1，再 select * from table(DBMS_XPLAN.DISPLAY)便可以看到oracle的执行计划了，看到的结果和1中的一样，所以使用工具的时候推荐使用1方法。
注意：PL/SQL Dev工具的Command window中不支持set autotrance on的命令。还有使用工具方法查看计划看到的信息不全，有些时候我们需要sqlplus的支持。
二、通过sqlplus
1．最简单的办法
Sql> set autotrace on
Sql> select * from al;
执行完语句后，会显示explain plan 与 统计信息。
这个语句的优点就是它的缺点，这样在用该方法查看执行时间较长的sql语句时，需要等待该语句执行成功后，才返回执行计划，使优化的周期大大增长。如果不想执行语句而只是想得到执行计划可以采用：
Sql> set autotrace traceonly
这样，就只会列出执行计划，而不会真正的执行语句，大大减少了优化时间。虽然也列出了统计信息，但是因为没有执行语句，所以该统计信息没有用处，如果执行该语句时遇到错误，解决方法为：
(1）在要分析的用户下：
Sqlplus > @ ?
dbmsadminutlxplan.sql
(2) 用sys用户登陆
Sqlplus > @ ?sqlplusadminplustrce.sql
Sqlplus > grant plustrace to user_name;
- - user_name是上面所说的分析用户
2．用explain plan命令
(1) sqlplus > explain plan for select * from testdb.myuser
(2) sqlplus > select * from table(dbms_xplan.display);
上面这2种方法只能为在本会话中正在运行的语句产生执行计划，即我们需要已经知道了哪条语句运行的效率很差，我们是有目的只对这条SQL语句去优化。其实，在很多情况下，我们只会听一个客户抱怨说现在系统运行很慢，而我们不知道是哪个SQL引起的。此时有许多现成的语句可以找出耗费资源比较多的语句，如：
SELECT ADDRESS, substr(SQL_TEXT,1,20) Text, buffer_gets, executions,
buffer_gets/executions AVG FROM v$sqlarea
WHERE executions>0 AND buffer_gets > 100000 ORDER BY 5;
ADDRESS TEXT BUFFER_GETS EXECUTIONS AVG
-------- ---------------------------------------- ----------- ---------- ------------------------------------------------------------
66D83D64 select t.name, (sel 421531 60104 7.01336017
66D9E8AC select t.schema, t.n 1141739 2732 417.913250
66B82BCC select s.synonym_nam 441261 6 73543.5
从而对找出的语句进行进一步优化。当然我们还可以为一个正在运行的会话中运行的所有SQL语句生成执行计划，这需要对该会话进行跟踪，产生trace文件，然后对该文件用tkprof程序格式化一下，这种得到执行计划的方式很有用，因为它包含其它额外信息，如SQL语句执行的每个阶段(如Parse、Execute、Fetch)分别耗费的各个资源情况(如CPU、DISK、elapsed等)。
3、启用SQL_TRACE跟踪所有后台进程活动：
全局参数设置： .OracleHome/admin/SID/pfile中指定: SQL_TRACE = true (10g)
当前session中设置：
SQL> alter session set SQL_TRACE=true;
SQL> select * from al;
SQL> alter session set SQL_TRACE=false;
对其他用户进行跟踪设置：
SQL> select sid,serial#,username from v$session where username='XXX';
SID SERIAL# USERNAME
------ ---------- ------------------
127 31923 A
128 54521 B
开启跟踪:SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,true);
关闭跟踪:SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,false);
然后使用oracle自带的tkprof命令行工具格式化跟踪文件。
4、使用10046事件进行查询：
10046事件级别：
Lv1 - 启用标准的SQL_TRACE功能,等价于SQL_TRACE
Lv4 - Level 1 + 绑定值(bind values)
Lv8 - Level 1 + 等待事件跟踪
Lv12 - Level 1 + Level 4 + Level 8
全局设定：
OracleHome/admin/SID/pfile中指定: EVENT="10046 trace name context forever,level 12"
当前session设定：
开启:SQL> alter session set events '10046 trace name context forever, level 8';
关闭:SQL> alter session set events '10046 trace name context off';
对其他用户进行设置：
SQL> select sid,serial#,username from v$session where username='XXX';
SID SERIAL# USERNAME
------ ---------- ------------------
127 31923 A
SQL> exec dbms_system.set_ev(127,31923,10046,8,'A');
5、使用tkprof格式化跟踪文件： (根据下面SQL语句得到的文件都不存在该目录下,郁闷啊,懵懂啊...)
一般，一次跟踪可以分为以下几步：
1、界定需要跟踪的目标范围，并使用适当的命令启用所需跟踪。
2、经过一段时间后，停止跟踪。此时应该产生了一个跟踪结果文件。
3、找到跟踪文件，并对其进行格式化，然后阅读或分析。
--使用一下SQL找到当前session的跟踪文件：
SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_name
from
( select p.spid from v$mystat m,v$session s, v$process p
where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
( select t.instance from v$thread t,v$parameter v
where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,
( select value from v$parameter where name = 'user_mp_dest' ) d;
-- 其它用户的 session
SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_name
from
( select p.spid from v$session s, v$process p
where s.sid= '27' and s. SERIAL#= '30' and p.addr = s.paddr) p,
( select t.instance from v$thread t,v$parameter v
where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,
( select value from v$parameter where name = 'user_mp_dest' ) d;
--查找后使用tkprof命令,将TRACE文件格式为到D盘的explain_format.txt文件中
SQL> $tkprof d:/oracle/admin/FZLGFM/ump/fzlgfm_ora_3468.trc d:/explain_format.txt
文件内容大致如下(看不太懂....懵懂啊.....天啊....神啊.....过几时就懂了/////////////)
TKPROF: Release 9.2.0.1.0 - Proction on 星期二 4月 20 13:59:20 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: d:/oracle/admin/FZLGFM/ump/fzlgfm_ora_3468.trc
Sort options: default
********************************************************************************
count = number of times OCI procere was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
alter session set events '10046 trace name context forever, level 8'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache ring parse: 0
Misses in library cache ring execute: 1
Optimizer goal: CHOOSE
Parsing user id: SYS
Oracle中获取执行计划的几种方法分析
以下是对Oracle中获取执行计划的几种方法进行了详细的分析介绍 需要的朋友可以参考下
预估执行计划 Explain Plan Explain plan以SQL语句作为输入 得到这条SQL语句的执行计划 并将执行计划输出存储到计划表中 首先 在你要执行的SQL语句前加explain plan for 此时将生成的执行计划存储到计划表中 语句如下 explain plan for SQL语句 然后 在计划表中查询刚刚生成的执行计划 语句如下 select * from table(dbms_xplan display); 注意 Explain plan只生成执行计划 并不会真正执行SQL语句 因此产生的执行计划有可能不准 因为
）当前的环境可能和执行计划生成时的环境不同 ）不会考虑绑定变量的数据类型 ）不进行变量窥视
查询内存中缓存的执行计划 （dbms_xplan display_cursor） 如果你想获取正在执行的或刚执行结束的SQL语句真实的执行计划（即获取library cache中的执行计划） 可以到动态性能视图里查询 方法如下
）获取SQL语句的游标 游标分为父游标和子游标 父游标由sql_id(或联合address和hash_value)字段表示 子游标由child_number字段表示
如果SQL语句正在运行 可以从v$session中获得它的游标信息 如 select status sql_id sql_child_number from v$session where status= ACTIVE and
如果知道SQL语句包含某些关键字 可以从v$sql视图中获得它的游标信息 如 select sql_id child_number sql_text from v$sql where sql_text like %关键字%‘
）获取库缓存中的执行计划 为了获取缓存库中的执行计划 可以直接查询动态性能视图v$sql_plan和v$sql_plan_statistics_all等 但更方便的方法是以sql_id和子游标为参数 执行如下语句 select * from table(dbms_xplan display_cursor( sql_id child_number));
）获取前一次的执行计划 set serveroutput off select * from table(dbms_xplan display_cursor(null null ALLSTATS LAST ));
查询历史执行计划（dbms_xplan display_awr） AWR会定时把动态性能视图中的执行计划保存到dba_hist_sql_plan视图中 如果你想要查看历史执行计划 可以采用如下方法查询 select * from table(dbms_xplan display_awr( sql_id );
在用sqlplus做SQL开发是（Autotrace） set autotrace是sqlplus工具的一个功能 只能在通过sqlplus连接的session中使用 它非常适合在开发时测试SQL语句的性能 有以下几种参数可供选择
SET AUTOTRACE OFF 不显示执行计划和统计信息 这是缺省模式 SET AUTOTRACE ON EXPLAIN 只显示优化器执行计划 SET AUTOTRACE ON STATISTICS 只显示统计信息 SET AUTOTRACE ON 执行计划和统计信息同时显示 SET AUTOTRACE TRACEONLY 不真正执行 只显示预期的执行计划 同explain plan 生成Trace文件查询详细的执行计划 （SQL_Trace ） SQL_TRACE 作为初始化参数可以在实例级别启用 也可以只在会话级别启用 在实例级别启用SQL_TRACE会导致所有进程的活动被跟踪 包括后台进程及所有用户进 程 这通常会导致比较严重的性能问题 所以在一般情况下 我们使用sql_trace跟踪当前进程 方法如下
SQL>alter session set sql_trace=true; 被跟踪的SQL语句 SQL>alter session set sql_trace=false; 如果要跟踪其它进程 可以通过Oracle提供的系统包DBMS_SYSTEM SET_SQL_TRACE_IN_SESSION来实现 例如 SQL> exec dbms_system set_sql_trace_in_session(sid serial# true) 开始跟踪 SQL> exec dbms_system set_sql_trace_in_session(sid serial# false) 结束跟踪
lishixin/Article/program/Oracle/201311/19003Oracle中获取执行计划的几种方法分析
以下是对Oracle中获取执行计划的几种方法进行了详细的分析介绍 需要的朋友可以参考下
预估执行计划 Explain Plan Explain plan以SQL语句作为输入 得到这条SQL语句的执行计划 并将执行计划输出存储到计划表中 首先 在你要执行的SQL语句前加explain plan for 此时将生成的执行计划存储到计划表中 语句如下 explain plan for SQL语句 然后 在计划表中查询刚刚生成的执行计划 语句如下 select * from table(dbms_xplan display); 注意 Explain plan只生成执行计划 并不会真正执行SQL语句 因此产生的执行计划有可能不准 因为
）当前的环境可能和执行计划生成时的环境不同 ）不会考虑绑定变量的数据类型 ）不进行变量窥视
查询内存中缓存的执行计划 （dbms_xplan display_cursor） 如果你想获取正在执行的或刚执行结束的SQL语句真实的执行计划（即获取library cache中的执行计划） 可以到动态性能视图里查询 方法如下
）获取SQL语句的游标 游标分为父游标和子游标 父游标由sql_id(或联合address和hash_value)字段表示 子游标由child_number字段表示
如果SQL语句正在运行 可以从v$session中获得它的游标信息 如 select status sql_id sql_child_number from v$session where status= ACTIVE and
如果知道SQL语句包含某些关键字 可以从v$sql视图中获得它的游标信息 如 select sql_id child_number sql_text from v$sql where sql_text like %关键字%‘
）获取库缓存中的执行计划 为了获取缓存库中的执行计划 可以直接查询动态性能视图v$sql_plan和v$sql_plan_statistics_all等 但更方便的方法是以sql_id和子游标为参数 执行如下语句 select * from table(dbms_xplan display_cursor( sql_id child_number));
）获取前一次的执行计划 set serveroutput off select * from table(dbms_xplan display_cursor(null null ALLSTATS LAST ));
查询历史执行计划（dbms_xplan display_awr） AWR会定时把动态性能视图中的执行计划保存到dba_hist_sql_plan视图中 如果你想要查看历史执行计划 可以采用如下方法查询 select * from table(dbms_xplan display_awr( sql_id );
在用sqlplus做SQL开发是（Autotrace） set autotrace是sqlplus工具的一个功能 只能在通过sqlplus连接的session中使用 它非常适合在开发时测试SQL语句的性能 有以下几种参数可供选择
SET AUTOTRACE OFF 不显示执行计划和统计信息 这是缺省模式 SET AUTOTRACE ON EXPLAIN 只显示优化器执行计划 SET AUTOTRACE ON STATISTICS 只显示统计信息 SET AUTOTRACE ON 执行计划和统计信息同时显示 SET AUTOTRACE TRACEONLY 不真正执行 只显示预期的执行计划 同explain plan 生成Trace文件查询详细的执行计划 （SQL_Trace ） SQL_TRACE 作为初始化参数可以在实例级别启用 也可以只在会话级别启用 在实例级别启用SQL_TRACE会导致所有进程的活动被跟踪 包括后台进程及所有用户进 程 这通常会导致比较严重的性能问题 所以在一般情况下 我们使用sql_trace跟踪当前进程 方法如下
SQL>alter session set sql_trace=true; 被跟踪的SQL语句 SQL>alter session set sql_trace=false; 如果要跟踪其它进程 可以通过Oracle提供的系统包DBMS_SYSTEM SET_SQL_TRACE_IN_SESSION来实现 例如 SQL> exec dbms_system set_sql_trace_in_session(sid serial# true) 开始跟踪 SQL> exec dbms_system set_sql_trace_in_session(sid serial# false) 结束跟踪
lishixin/Article/program/Oracle/201311/19003如何解析oracle执行计划
一、通过PL/SQL Dev工具
1、直接File->New->Explain Plan Window，在窗口中执行sql可以查看计划结果。其中，Cost表示cpu的消耗，单位为n%，Cardinality表示执行的行数，等价Rows。
2、先执行 EXPLAIN PLAN FOR select * from tableA where paraA=1，再 select * from table(DBMS_XPLAN.DISPLAY)便可以看到oracle的执行计划了，看到的结果和1中的一样，所以使用工具的时候推荐使用1方法。
注意：PL/SQL Dev工具的Command window中不支持set autotrance on的命令。还有使用工具方法查看计划看到的信息不全，有些时候我们需要sqlplus的支持。
二、通过sqlplus
1．最简单的办法
Sql> set autotrace on
Sql> select * from al;
执行完语句后，会显示explain plan 与 统计信息。
这个语句的优点就是它的缺点，这样在用该方法查看执行时间较长的sql语句时，需要等待该语句执行成功后，才返回执行计划，使优化的周期大大增长。如果不想执行语句而只是想得到执行计划可以采用：
Sql> set autotrace traceonly
这样，就只会列出执行计划，而不会真正的执行语句，大大减少了优化时间。虽然也列出了统计信息，但是因为没有执行语句，所以该统计信息没有用处，如果执行该语句时遇到错误，解决方法为：
(1）在要分析的用户下：
Sqlplus > @ ?
dbmsadminutlxplan.sql
(2) 用sys用户登陆
Sqlplus > @ ?sqlplusadminplustrce.sql
Sqlplus > grant plustrace to user_name;
- - user_name是上面所说的分析用户
2．用explain plan命令
(1) sqlplus > explain plan for select * from testdb.myuser
(2) sqlplus > select * from table(dbms_xplan.display);
上面这2种方法只能为在本会话中正在运行的语句产生执行计划，即我们需要已经知道了哪条语句运行的效率很差，我们是有目的只对这条SQL语句去优化。其实，在很多情况下，我们只会听一个客户抱怨说现在系统运行很慢，而我们不知道是哪个SQL引起的。此时有许多现成的语句可以找出耗费资源比较多的语句，如：
SELECT ADDRESS, substr(SQL_TEXT,1,20) Text, buffer_gets, executions,
buffer_gets/executions AVG FROM v$sqlarea
WHERE executions>0 AND buffer_gets > 100000 ORDER BY 5;
ADDRESS TEXT BUFFER_GETS EXECUTIONS AVG
-------- ---------------------------------------- ----------- ---------- ------------------------------------------------------------
66D83D64 select t.name, (sel 421531 60104 7.01336017
66D9E8AC select t.schema, t.n 1141739 2732 417.913250
66B82BCC select s.synonym_nam 441261 6 73543.5
从而对找出的语句进行进一步优化。当然我们还可以为一个正在运行的会话中运行的所有SQL语句生成执行计划，这需要对该会话进行跟踪，产生trace文件，然后对该文件用tkprof程序格式化一下，这种得到执行计划的方式很有用，因为它包含其它额外信息，如SQL语句执行的每个阶段(如Parse、Execute、Fetch)分别耗费的各个资源情况(如CPU、DISK、elapsed等)。
3、启用SQL_TRACE跟踪所有后台进程活动：
全局参数设置： .OracleHome/admin/SID/pfile中指定: SQL_TRACE = true (10g)
当前session中设置：
SQL> alter session set SQL_TRACE=true;
SQL> select * from al;
SQL> alter session set SQL_TRACE=false;
对其他用户进行跟踪设置：
SQL> select sid,serial#,username from v$session where username='XXX';
SID SERIAL# USERNAME
------ ---------- ------------------
127 31923 A
128 54521 B
开启跟踪:SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,true);
关闭跟踪:SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,false);
然后使用oracle自带的tkprof命令行工具格式化跟踪文件。
4、使用10046事件进行查询：
10046事件级别：
Lv1 - 启用标准的SQL_TRACE功能,等价于SQL_TRACE
Lv4 - Level 1 + 绑定值(bind values)
Lv8 - Level 1 + 等待事件跟踪
Lv12 - Level 1 + Level 4 + Level 8
全局设定：
OracleHome/admin/SID/pfile中指定: EVENT="10046 trace name context forever,level 12"
当前session设定：
开启:SQL> alter session set events '10046 trace name context forever, level 8';
关闭:SQL> alter session set events '10046 trace name context off';
对其他用户进行设置：
SQL> select sid,serial#,username from v$session where username='XXX';
SID SERIAL# USERNAME
------ ---------- ------------------
127 31923 A
SQL> exec dbms_system.set_ev(127,31923,10046,8,'A');
5、使用tkprof格式化跟踪文件： (根据下面SQL语句得到的文件都不存在该目录下,郁闷啊,懵懂啊...)
一般，一次跟踪可以分为以下几步：
1、界定需要跟踪的目标范围，并使用适当的命令启用所需跟踪。
2、经过一段时间后，停止跟踪。此时应该产生了一个跟踪结果文件。
3、找到跟踪文件，并对其进行格式化，然后阅读或分析。
--使用一下SQL找到当前session的跟踪文件：
SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_namefrom( select p.spid from v$mystat m,v$session s, v$process pwhere m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,( select t.instance from v$thread t,v$parameter vwhere v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,( select value from v$parameter where name = 'user_mp_dest' ) d;-- 其它用户的 session SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_name from ( select p.spid from v$session s, v$process p where s.sid= '27' and s. SERIAL#= '30' and p.addr = s.paddr) p, ( select t.instance from v$thread t,v$parameter v where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i, ( select value from v$parameter where name = 'user_mp_dest' ) d;
--查找后使用tkprof命令,将TRACE文件格式为到D盘的explain_format.txt文件中
SQL> $tkprof d:/oracle/admin/FZLGFM/ump/fzlgfm_ora_3468.trc d:/explain_format.txt
文件内容大致如下(看不太懂....懵懂啊.....天啊....神啊.....过几时就懂了/////////////)
TKPROF: Release 9.2.0.1.0 - Proction on 星期二 4月 20 13:59:20 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: d:/oracle/admin/FZLGFM/ump/fzlgfm_ora_3468.trc
Sort options: default
********************************************************************************
count = number of times OCI procere was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call********************************************************************************
alter session set events '10046 trace name context forever, level 8'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache ring parse: 0
Misses in library cache ring execute: 1
Optimizer goal: CHOOSE
Parsing user id: SYS
如何解析oracle执行计划
一、通过PL/SQL Dev工具
1、直接File->New->Explain Plan Window，在窗口中执行sql可以查看计划结果。其中，Cost表示cpu的消耗，单位为n%，Cardinality表示执行的行数，等价Rows。
2、先执行 EXPLAIN PLAN FOR select * from tableA where paraA=1，再 select * from table(DBMS_XPLAN.DISPLAY)便可以看到oracle的执行计划了，看到的结果和1中的一样，所以使用工具的时候推荐使用1方法。
注意：PL/SQL Dev工具的Command window中不支持set autotrance on的命令。还有使用工具方法查看计划看到的信息不全，有些时候我们需要sqlplus的支持。
二、通过sqlplus
1．最简单的办法
Sql> set autotrace on
Sql> select * from al;
执行完语句后，会显示explain plan 与 统计信息。
这个语句的优点就是它的缺点，这样在用该方法查看执行时间较长的sql语句时，需要等待该语句执行成功后，才返回执行计划，使优化的周期大大增长。如果不想执行语句而只是想得到执行计划可以采用：
Sql> set autotrace traceonly
这样，就只会列出执行计划，而不会真正的执行语句，大大减少了优化时间。虽然也列出了统计信息，但是因为没有执行语句，所以该统计信息没有用处，如果执行该语句时遇到错误，解决方法为：
(1）在要分析的用户下：
Sqlplus > @ ?
dbmsadminutlxplan.sql
(2) 用sys用户登陆
Sqlplus > @ ?sqlplusadminplustrce.sql
Sqlplus > grant plustrace to user_name;
- - user_name是上面所说的分析用户
2．用explain plan命令
(1) sqlplus > explain plan for select * from testdb.myuser
(2) sqlplus > select * from table(dbms_xplan.display);
上面这2种方法只能为在本会话中正在运行的语句产生执行计划，即我们需要已经知道了哪条语句运行的效率很差，我们是有目的只对这条SQL语句去优化。其实，在很多情况下，我们只会听一个客户抱怨说现在系统运行很慢，而我们不知道是哪个SQL引起的。此时有许多现成的语句可以找出耗费资源比较多的语句，如：
SELECT ADDRESS, substr(SQL_TEXT,1,20) Text, buffer_gets, executions,
buffer_gets/executions AVG FROM v$sqlarea
WHERE executions>0 AND buffer_gets > 100000 ORDER BY 5;
ADDRESS TEXT BUFFER_GETS EXECUTIONS AVG
-------- ---------------------------------------- ----------- ---------- ------------------------------------------------------------
66D83D64 select t.name, (sel 421531 60104 7.01336017
66D9E8AC select t.schema, t.n 1141739 2732 417.913250
66B82BCC select s.synonym_nam 441261 6 73543.5
从而对找出的语句进行进一步优化。当然我们还可以为一个正在运行的会话中运行的所有SQL语句生成执行计划，这需要对该会话进行跟踪，产生trace文件，然后对该文件用tkprof程序格式化一下，这种得到执行计划的方式很有用，因为它包含其它额外信息，如SQL语句执行的每个阶段(如Parse、Execute、Fetch)分别耗费的各个资源情况(如CPU、DISK、elapsed等)。
3、启用SQL_TRACE跟踪所有后台进程活动：
全局参数设置： .OracleHome/admin/SID/pfile中指定: SQL_TRACE = true (10g)
当前session中设置：
SQL> alter session set SQL_TRACE=true;
SQL> select * from al;
SQL> alter session set SQL_TRACE=false;
对其他用户进行跟踪设置：
SQL> select sid,serial#,username from v$session where username='XXX';
SID SERIAL# USERNAME
------ ---------- ------------------
127 31923 A
128 54521 B
开启跟踪:SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,true);
关闭跟踪:SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,false);
然后使用oracle自带的tkprof命令行工具格式化跟踪文件。
4、使用10046事件进行查询：
10046事件级别：
Lv1 - 启用标准的SQL_TRACE功能,等价于SQL_TRACE
Lv4 - Level 1 + 绑定值(bind values)
Lv8 - Level 1 + 等待事件跟踪
Lv12 - Level 1 + Level 4 + Level 8
全局设定：
OracleHome/admin/SID/pfile中指定: EVENT="10046 trace name context forever,level 12"
当前session设定：
开启:SQL> alter session set events '10046 trace name context forever, level 8';
关闭:SQL> alter session set events '10046 trace name context off';
对其他用户进行设置：
SQL> select sid,serial#,username from v$session where username='XXX';
SID SERIAL# USERNAME
------ ---------- ------------------
127 31923 A
SQL> exec dbms_system.set_ev(127,31923,10046,8,'A');
5、使用tkprof格式化跟踪文件： (根据下面SQL语句得到的文件都不存在该目录下,郁闷啊,懵懂啊...)
一般，一次跟踪可以分为以下几步：
1、界定需要跟踪的目标范围，并使用适当的命令启用所需跟踪。
2、经过一段时间后，停止跟踪。此时应该产生了一个跟踪结果文件。
3、找到跟踪文件，并对其进行格式化，然后阅读或分析。
--使用一下SQL找到当前session的跟踪文件：
SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_namefrom( select p.spid from v$mystat m,v$session s, v$process pwhere m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,( select t.instance from v$thread t,v$parameter vwhere v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,( select value from v$parameter where name = 'user_mp_dest' ) d;-- 其它用户的 session SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_name from ( select p.spid from v$session s, v$process p where s.sid= '27' and s. SERIAL#= '30' and p.addr = s.paddr) p, ( select t.instance from v$thread t,v$parameter v where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i, ( select value from v$parameter where name = 'user_mp_dest' ) d;
--查找后使用tkprof命令,将TRACE文件格式为到D盘的explain_format.txt文件中
SQL> $tkprof d:/oracle/admin/FZLGFM/ump/fzlgfm_ora_3468.trc d:/explain_format.txt
文件内容大致如下(看不太懂....懵懂啊.....天啊....神啊.....过几时就懂了/////////////)
TKPROF: Release 9.2.0.1.0 - Proction on 星期二 4月 20 13:59:20 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: d:/oracle/admin/FZLGFM/ump/fzlgfm_ora_3468.trc
Sort options: default
********************************************************************************
count = number of times OCI procere was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call********************************************************************************
alter session set events '10046 trace name context forever, level 8'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache ring parse: 0
Misses in library cache ring execute: 1
Optimizer goal: CHOOSE
Parsing user id: SYS
查看ORACLE执行计划的几种常用方法
SQL的执行计划实际代表了目标SQL在Oracle数据库内部的具体执行步骤，作为调优，只有知道了优化器选择的执行计划是否为当前情形下最优的执行计划，才能够知道下一步往什么方向。
执行计划的定义：执行目标SQL的所有步骤的组合。
我们首先列出查看执行计划的一些常用方法：
1.explain plan命令
PL/SQL Developer中通过快捷键F5就可以查看目标SQL的执行计划了。但其实按下F5后，实际后台调用的就是explain plan命令，相当于封装了该命令。
explain plan使用方法：
(1) 执行explain plan for + SQL
(2) 执行select * from table(dbms_xplan.display);
实验表准备：
SQL> desc test1;Name NullType----------------------------------------- -------- ----------------------------T1IDNOT NULL NUMBER(38)T1VVARCHAR2(10)
SQL> desc test2;Name NullType----------------------------------------- -------- ----------------------------T2IDNOT NULL NUMBER(38)T2VVARCHAR2(10)
实验：
SQL> set linesize 100
SQL> explain plan for select t1id, t1v, t2id, t2v from test1, test2 where test1.t1id = test2.t2id;
Explained.
第一步使用explain plan对目标SQL进行了explain，第二步使用select * from table(dbms_xplan.display)语句展示出该SQL的执行计划。
这里test2作为驱动表，进行了全表扫描，test1作为被驱动表，由于其包含主键，所以用的是索引全扫描。左侧ID带*号的第四步操作，表示有谓词条件，这里可以看到既使用了主键索引(access)，又使用了过滤条件(filter)。
2.DBMS_XPLAN包
(1) select * from table(dbms_xplan.display);--上面以说明。
(2)select * from table(dbms_xplan.display_cursor(null, null, ‘advanced‘));
(3)select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value‘, child_cursor_number, ‘advanced‘));
(4)select * from table(dbms_xplan.display_awr(‘sql_id‘));
(2)select * from table(dbms_xplan.display_cursor(null, null, ‘advanced‘));
主要用于SQLPLUS中查看刚执行过SQL的执行计划。首先第三个参数可以选择‘advanced‘：
接下来，第三个参数使用‘all‘：
可以看出‘advanced‘记录的信息要比‘all’多，主要就是多一个Outline Data。Outline Data主要是执行SQL时用于固定执行计划的内部HINT组合，可以将这部分内容摘出来加到目标SQL中以固定其执行计划。
(3)select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value‘, child_cursor_number, ‘advanced‘));
其中第一个参数可以输入SQL的sql_id或hash value，方法就是如果执行的SQL仍在库缓存中，则可以使用V$SQL查询：
其中，使用@dbsnake大牛的SQL可以知道SQL_ID和HASH_VALUE的一一对应关系：
隐藏问题1：
这里的截图可能有点问题，结果并不准确，问题就出在这个SQL中使用的算法中，在另一篇博文中会仔细说明这个问题。
使用：
SQL>select * from table(dbms_xplan.display_cursor(‘1p2fk2v00c865‘, 0, ‘advanced‘));
或
select * from table(dbms_xplan.display_cursor(‘3221627077‘, 0, ‘advanced‘));
就可以查出对应这条SQL的执行计划，内容同(2)中的‘advanced‘，这就不展示了。
注意这还有第二个参数child_cursor_number，指的是子游标编号，如果未生成新的子游标，则此处写的是0。
(2)和(3)的结论相近，区别就是(2)只是针对最近一次执行SQL查看执行计划，(3)可以针对仍在库缓存中的任意一次SQL查看执行计划。
(4)select * from table(dbms_xplan.display_awr(‘sql_id‘));
(1)是使用explain plan for +SQL作为前提，(2)和(3)的前提则是SQL的执行计划还在共享池中，具体讲是在库缓存中。如果已经被age out交换出共享池，则不能用这两种方法了。若该SQL的执行计划被采集到AWR库中，则可以用(4)来查询历史执行计划。
隐藏问题2：
实验这部分内容发现使用select * from table(dbms_xplan.display_awr(‘sql_id‘));并没有结果，@黄玮老师说有可能是AWR收集的是top的SQL，有可能测试用的SQL不是most intensive SQL，但我是用alter system flush shared_pool后执行的手工采集快照，还是未被AWR抓到，比较奇怪的问题，这个也会在另一篇博文中仔细说明。
查看ORACLE执行计划的几种常用方法
标签：
Oracle 获取执行计划的几种方法
SQL的执行计划实际代表了目标SQL在Oracle数据库内部的具体执行步骤，作为调优，只有知道了优化器选择的执行计划是否为当前情形下最优的执行计划，才能够知道下一步往什么方向。
执行计划的定义：执行目标SQL的所有步骤的组合。
我们首先列出查看执行计划的一些常用方法：
1. explain plan命令
PL/SQL Developer中通过快捷键F5就可以查看目标SQL的执行计划了。但其实按下F5后，实际后台调用的就是explain plan命令，相当于封装了该命令。
explain plan使用方法：
(1) 执行explain plan for + SQL
(2) 执行select * from table(dbms_xplan.display);
实验表准备：
SQL> desc test1;
Name Null Type
----------------------------------------- -------- ----------------------------
T1ID NOT NULL NUMBER(38)
T1V VARCHAR2(10)
SQL> desc test2;
Name Null Type
----------------------------------------- -------- ----------------------------
T2ID NOT NULL NUMBER(38)
T2V VARCHAR2(10)
实验：
SQL> set linesize 100
SQL> explain plan for select t1id, t1v, t2id, t2v from test1, test2 where test1.t1id = test2.t2id;
Explained.
第一步使用explain plan对目标SQL进行了explain，第二步使用select * from table(dbms_xplan.display)语句展示出该SQL的执行计划。
这里test2作为驱动表，进行了全表扫描，test1作为被驱动表，由于其包含主键，所以用的是索引全扫描。左侧ID带*号的第四步操作，表示有谓词条件，这里可以看到既使用了主键索引(access)，又使用了过滤条件(filter)。
2. DBMS_XPLAN包
(1) select * from table(dbms_xplan.display);--上面以说明。
(2) select * from table(dbms_xplan.display_cursor(null, null, ‘advanced‘));
(3) select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value‘, child_cursor_number, ‘advanced‘));
(4) select * from table(dbms_xplan.display_awr(‘sql_id‘));
(2) select * from table(dbms_xplan.display_cursor(null, null, ‘advanced‘));
主要用于SQLPLUS中查看刚执行过SQL的执行计划。首先第三个参数可以选择‘advanced‘：
接下来，第三个参数使用‘all‘：
可以看出‘advanced‘记录的信息要比‘all’多，主要就是多一个Outline Data。Outline Data主要是执行SQL时用于固定执行计划的内部HINT组合，可以将这部分内容摘出来加到目标SQL中以固定其执行计划。
(3) select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value‘, child_cursor_number, ‘advanced‘));
其中第一个参数可以输入SQL的sql_id或hash value，方法就是如果执行的SQL仍在库缓存中，则可以使用V$SQL查询：
其中，使用@dbsnake大牛的SQL可以知道SQL_ID和HASH_VALUE的一一对应关系：
隐藏问题1：
这里的截图可能有点问题，结果并不准确，问题就出在这个SQL中使用的算法中，在另一篇博文中会仔细说明这个问题。
使用：
SQL> select * from table(dbms_xplan.display_cursor(‘1p2fk2v00c865‘, 0, ‘advanced‘));
或
select * from table(dbms_xplan.display_cursor(‘3221627077‘, 0, ‘advanced‘));
就可以查出对应这条SQL的执行计划，内容同(2)中的‘advanced‘，这就不展示了。
注意这还有第二个参数child_cursor_number，指的是子游标编号，如果未生成新的子游标，则此处写的是0。
(2)和(3)的结论相近，区别就是(2)只是针对最近一次执行SQL查看执行计划，(3)可以针对仍在库缓存中的任意一次SQL查看执行计划。
(4) select * from table(dbms_xplan.display_awr(‘sql_id‘));
(1)是使用explain plan for +SQL作为前提，(2)和(3)的前提则是SQL的执行计划还在共享池中，具体讲是在库缓存中。如果已经被age out交换出共享池，则不能用这两种方法了。若该SQL的执行计划被采集到AWR库中，则可以用(4)来查询历史执行计划。
隐藏问题2：
实验这部分内容发现使用select * from table(dbms_xplan.display_awr(‘sql_id‘));并没有结果，@黄玮老师说有可能是AWR收集的是top的SQL，有可能测试用的SQL不是most intensive SQL，但我是用alter system flush shared_pool后执行的手工采集快照，还是未被AWR抓到，比较奇怪的问题，这个也会在另一篇博文中仔细说明。
查看ORACLE执行计划的几种常用方法
标签：
Oracle 获取执行计划的几种方法
SQL的执行计划实际代表了目标SQL在Oracle数据库内部的具体执行步骤，作为调优，只有知道了优化器选择的执行计划是否为当前情形下最优的执行计划，才能够知道下一步往什么方向。
执行计划的定义：执行目标SQL的所有步骤的组合。
我们首先列出查看执行计划的一些常用方法：
1. explain plan命令
PL/SQL Developer中通过快捷键F5就可以查看目标SQL的执行计划了。但其实按下F5后，实际后台调用的就是explain plan命令，相当于封装了该命令。
explain plan使用方法：
(1) 执行explain plan for + SQL
(2) 执行select * from table(dbms_xplan.display);
实验表准备：
SQL> desc test1;
Name Null Type
----------------------------------------- -------- ----------------------------
T1ID NOT NULL NUMBER(38)
T1V VARCHAR2(10)
SQL> desc test2;
Name Null Type
----------------------------------------- -------- ----------------------------
T2ID NOT NULL NUMBER(38)
T2V VARCHAR2(10)
实验：
SQL> set linesize 100
SQL> explain plan for select t1id, t1v, t2id, t2v from test1, test2 where test1.t1id = test2.t2id;
Explained.
第一步使用explain plan对目标SQL进行了explain，第二步使用select * from table(dbms_xplan.display)语句展示出该SQL的执行计划。
这里test2作为驱动表，进行了全表扫描，test1作为被驱动表，由于其包含主键，所以用的是索引全扫描。左侧ID带*号的第四步操作，表示有谓词条件，这里可以看到既使用了主键索引(access)，又使用了过滤条件(filter)。
2. DBMS_XPLAN包
(1) select * from table(dbms_xplan.display);--上面以说明。
(2) select * from table(dbms_xplan.display_cursor(null, null, ‘advanced‘));
(3) select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value‘, child_cursor_number, ‘advanced‘));
(4) select * from table(dbms_xplan.display_awr(‘sql_id‘));
(2) select * from table(dbms_xplan.display_cursor(null, null, ‘advanced‘));
主要用于SQLPLUS中查看刚执行过SQL的执行计划。首先第三个参数可以选择‘advanced‘：
接下来，第三个参数使用‘all‘：
可以看出‘advanced‘记录的信息要比‘all’多，主要就是多一个Outline Data。Outline Data主要是执行SQL时用于固定执行计划的内部HINT组合，可以将这部分内容摘出来加到目标SQL中以固定其执行计划。
(3) select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value‘, child_cursor_number, ‘advanced‘));
其中第一个参数可以输入SQL的sql_id或hash value，方法就是如果执行的SQL仍在库缓存中，则可以使用V$SQL查询：
其中，使用@dbsnake大牛的SQL可以知道SQL_ID和HASH_VALUE的一一对应关系：
隐藏问题1：
这里的截图可能有点问题，结果并不准确，问题就出在这个SQL中使用的算法中，在另一篇博文中会仔细说明这个问题。
使用：
SQL> select * from table(dbms_xplan.display_cursor(‘1p2fk2v00c865‘, 0, ‘advanced‘));
或
select * from table(dbms_xplan.display_cursor(‘3221627077‘, 0, ‘advanced‘));
就可以查出对应这条SQL的执行计划，内容同(2)中的‘advanced‘，这就不展示了。
注意这还有第二个参数child_cursor_number，指的是子游标编号，如果未生成新的子游标，则此处写的是0。
(2)和(3)的结论相近，区别就是(2)只是针对最近一次执行SQL查看执行计划，(3)可以针对仍在库缓存中的任意一次SQL查看执行计划。
(4) select * from table(dbms_xplan.display_awr(‘sql_id‘));
(1)是使用explain plan for +SQL作为前提，(2)和(3)的前提则是SQL的执行计划还在共享池中，具体讲是在库缓存中。如果已经被age out交换出共享池，则不能用这两种方法了。若该SQL的执行计划被采集到AWR库中，则可以用(4)来查询历史执行计划。
隐藏问题2：
实验这部分内容发现使用select * from table(dbms_xplan.display_awr(‘sql_id‘));并没有结果，@黄玮老师说有可能是AWR收集的是top的SQL，有可能测试用的SQL不是most intensive SQL，但我是用alter system flush shared_pool后执行的手工采集快照，还是未被AWR抓到，比较奇怪的问题，这个也会在另一篇博文中仔细说明。
查看ORACLE执行计划的几种常用方法
标签：
如何看懂ORACLE执行计划
一、什么是执行计划
An explain plan is a representation of the access path that is taken when a query is executed within Oracle.
二、如何访问数据
At the physical level Oracle reads blocks of data. The smallest amount of data read is a single Oracle block, the largest is constrained by operating system limits (and multiblock i/o). Logically Oracle finds the data to read by using the following methods:
Full Table Scan (FTS) --全表扫描
Index Lookup (unique & non-unique) --索引扫描（唯一和非唯一）
Rowid --物理行id
三、执行计划层次关系
When looking at a plan, the rightmost (ie most inndented) uppermost operation is the first thing that is executed. --采用最右最上最先执行的原则看层次关系，在同一级如果某个动作没有子ID就最先执行
1.一个简单的例子：
SQL> select /*+parallel (e 4)*/ * from emp e;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=82 Bytes=7134)
1 0 TABLE ACCESS* (FULL) OF 'EMP' (Cost=1 Card=82 Bytes=7134):Q5000
--[:Q5000]表示是并行方式
1 PARALLEL_TO_SERIAL SELECT /*+ NO_EXPAND ROWID(A1) */ A1."EMPNO"
,A1."ENAME",A1."JOB",A1."MGR",A1."HI
优化模式是CHOOSE的情况下，看Cost参数是否有值来决定采用CBO还是RBO：
SELECT STATEMENT [CHOOSE] Cost=1234--Cost有值，采用CBO
SELECT STATEMENT [CHOOSE] --Cost为空，采用RBO(9I是如此显示的)
2.层次的父子关系的例子：
PARENT1
**FIRST CHILD
****FIRST GRANDCHILD
**SECOND CHILD
Here the same principles apply, the FIRST GRANDCHILD is the initial operation then the FIRST CHILD followed by the SECOND CHILD and finally the PARENT collates the output.
四、例子解说
Execution Plan
----------------------------------------------------------
0 **SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=8 Bytes=248)
1 0 **HASH JOIN (Cost=3 Card=8 Bytes=248)
2 1 ****TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=3 Bytes=36)
3 1 ****TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=16 Bytes=304)
左侧的两排数据，前面的是序列号ID，后面的是对应的PID（父ID）。
A shortened summary of this is:
Execution starts with ID=0: SELECT STATEMENT but this is dependand on it's child objects
So it executes its first child step: ID=1 PID=0 HASH JOIN but this is dependand on it's child objects
So it executes its first child step: ID=2 PID=1 TABLE ACCESS (FULL) OF 'DEPT'
Then the second child step: ID=3 PID=2 TABLE ACCESS (FULL) OF 'EMP'
Rows are returned to the parent step(s) until finished
五、表访问方式
1.Full Table Scan (FTS) 全表扫描
In a FTS operation, the whole table is read up to the high water mark (HWM). The HWM marks the last block in the table that has ever had data written to it. If you have deleted all the rows then you will still read up to the HWM. Truncate resets the HWM back to the start of the table. FTS uses multiblock i/o to read the blocks from disk. --全表扫描模式下会读数据到表的高水位线（HWM即表示表曾经扩展的最后一个数据块），读取速度依赖于Oracle初始化参数db_block_multiblock_read_count(我觉得应该这样翻译:FTS扫描会使表使用上升到高水位(HWM),HWM标识了表最后写入数据的块,如果你用DELETE删除了所有的数据表仍然处于高水位(HWM),只有用TRUNCATE才能使表回归,FTS使用多IO从磁盘读取数据块).
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
**INDEX UNIQUE SCAN EMP_I1 --如果索引里就找到了所要的数据，就不会再去访问表
2.Index Lookup 索引扫描
There are 5 methods of index lookup:
index unique scan --索引唯一扫描
Method for looking up a single key value via a unique index. always returns a single value, You must supply AT LEAST the leading column of the index to access data via the index.
eg:SQL> explain plan for select empno,ename from emp where empno=10;
index range scan --索引局部扫描
Index range scan is a method for accessing a range values of a particular column. AT LEAST the leading column of the index must be supplied to access data via the index. Can be used for range operations (e.g. > < <> >= <= between) .
eg:SQL> explain plan for select mgr from emp where mgr = 5;
index full scan --索引全局扫描
Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort. For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order.
eg: SQL> explain plan for select empno,ename from big_emp order by empno,ename;
index fast full scan --索引快速全局扫描，不带order by情况下常发生
Scans all the block in the index, Rows are not returned in sorted order, Introced in 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO, may be hinted using INDEX_FFS hint, uses multiblock i/o, can be executed in parallel, can be used to access second column of concatenated indexes. This is because we are selecting all of the index.
eg: SQL> explain plan for select empno,ename from big_emp;
index skip scan --索引跳跃扫描，where条件列是非索引的前导列情况下常发生
Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the first column(s) ring the search.
eg:SQL> create index i_emp on emp(empno, ename);
SQL> select /*+ index_ss(emp i_emp)*/ job from emp where ename='SMITH';
3.Rowid 物理ID扫描
This is the quickest access method available.Oracle retrieves the specified block and extracts the rows it is interested in. --Rowid扫描是最快的访问数据方式
oracle sql执行计划怎么分析
在发现一个语句的执行计划有异常的时候，通常会生成一个sqlrpt看看使用的执行计划是否正确，如何来判断执行计划是否正确，将通过以下几个步骤来判断：
1.先查看sql语句的结构，看语句中连接是union，还是等值连接还是左、右连接,再看连接中的表数量。
2.查看执行计划中出现异常的部分。
3.查看各表的索引情况及表是否是分区的，在where条件上使用的索引列是否正确，看统计分析表中对表的分析结果是否正确
4.分析表的用途，表的数据日增长量。
5.分析为什么会出现异常的执行计划。
跟踪执行计划的方法：
(1) set autotrace on explain 只显示查询结果和执行计划
set autotrace on statistic 只显示查询结果统计信息
set autotrace on 显示前两者
set autotrace traceonly 不显示查询结果，只显示执行计划和统计信息
set autotrace off 关闭跟踪
要使用autotrace，必须在sqlplus里面使用，且使用的是sys用户。
（2）可以使用explain plan for select * from c_cons 可以解析执行计划，然后通过select * from table(dbms_xplain.display(null,null,’outline’,null));来显示执行计划。
（3）使用工具Toad for oracle使用sql_id来生成执行计划
oracle sql执行计划怎么分析
在发现一个语句的执行计划有异常的时候，通常会生成一个sqlrpt看看使用的执行计划是否正确，如何来判断执行计划是否正确，将通过以下几个步骤来判断：
1.先查看sql语句的结构，看语句中连接是union，还是等值连接还是左、右连接,再看连接中的表数量。
2.查看执行计划中出现异常的部分。
3.查看各表的索引情况及表是否是分区的，在where条件上使用的索引列是否正确，看统计分析表中对表的分析结果是否正确
4.分析表的用途，表的数据日增长量。
5.分析为什么会出现异常的执行计划。
跟踪执行计划的方法：
(1) set autotrace on explain 只显示查询结果和执行计划
set autotrace on statistic 只显示查询结果统计信息
set autotrace on 显示前两者
set autotrace traceonly 不显示查询结果，只显示执行计划和统计信息
set autotrace off 关闭跟踪
要使用autotrace，必须在sqlplus里面使用，且使用的是sys用户。
（2）可以使用explain plan for select * from c_cons 可以解析执行计划，然后通过select * from table(dbms_xplain.display(null,null,’outline’,null));来显示执行计划。
（3）使用工具Toad for oracle使用sql_id来生成执行计划
如何获取真实的执行计划
一般获取执行计划有四种途径：1、执行explain plan，查询结果输出表。2、查询动态性能视图，它显示缓存在库缓存中的执行计划（有时查不出结果是因为执行计划已经不在库缓存中）。3、查询AWR或Statspack表。4、启动提供执行计划的跟踪功能（set autotrace tra
一般获取执行计划有四种途径：1、执行explain plan，查询结果输出表。2、查询动态性能视图，它显示缓存在库缓存中的执行计划（有时查不出结果是因为执行计划已经不在库缓存中）。3、查询AWR或Statspack表。4、启动提供执行计划的跟踪功能（set autotrace trace explain）。使用得比较多的是第四种，简单又好用~本篇讲第一种方法。
首先，explainplan原理就是把一条SQL语句分析一下，将该语句的执行计划和相关信息存储到计划表（plan table）中。可以查看一下联机文档关于explain plan的语法，
这里的string是用于区分计划表中多个执行计划的，into后面的table就是“计划表”。一般的使用方法是explain plan for “sql text”;之后select * fromtable(dbms_xplan.display);有时出现这种情况，explain plan for insert into test select * from test; 此时任何事务并无变化，因为explainplan是DML语句，不会对当前事务隐式提交，仅仅是插入几条记录到计划表。
我们会把执行计划与相关信息存入计划表，默认计划表是sys下的一张表，但有一个plan_table公共同义词，当你想使用一个私有的计划表时，可以运行utlxplan.sql脚本。
当分析的sql语句有绑定变量时，一般使用绑定变量进行explain for，而不是使用几个常量代入绑定变量进行分析。在explain for使用绑定变量也有两个问题。第一，默认情况下绑定变量是varchar2类型，所以在执行计划access中会有一个显式类型转换；第二，不能使用bind peeking。
如何获取真实的执行计划
一般获取执行计划有四种途径：1、执行explain plan，查询结果输出表。2、查询动态性能视图，它显示缓存在库缓存中的执行计划（有时查不出结果是因为执行计划已经不在库缓存中）。3、查询AWR或Statspack表。4、启动提供执行计划的跟踪功能（set autotrace tra
一般获取执行计划有四种途径：1、执行explain plan，查询结果输出表。2、查询动态性能视图，它显示缓存在库缓存中的执行计划（有时查不出结果是因为执行计划已经不在库缓存中）。3、查询AWR或Statspack表。4、启动提供执行计划的跟踪功能（set autotrace trace explain）。使用得比较多的是第四种，简单又好用~本篇讲第一种方法。
首先，explainplan原理就是把一条SQL语句分析一下，将该语句的执行计划和相关信息存储到计划表（plan table）中。可以查看一下联机文档关于explain plan的语法，
这里的string是用于区分计划表中多个执行计划的，into后面的table就是“计划表”。一般的使用方法是explain plan for “sql text”;之后select * fromtable(dbms_xplan.display);有时出现这种情况，explain plan for insert into test select * from test; 此时任何事务并无变化，因为explainplan是DML语句，不会对当前事务隐式提交，仅仅是插入几条记录到计划表。
我们会把执行计划与相关信息存入计划表，默认计划表是sys下的一张表，但有一个plan_table公共同义词，当你想使用一个私有的计划表时，可以运行utlxplan.sql脚本。
当分析的sql语句有绑定变量时，一般使用绑定变量进行explain for，而不是使用几个常量代入绑定变量进行分析。在explain for使用绑定变量也有两个问题。第一，默认情况下绑定变量是varchar2类型，所以在执行计划access中会有一个显式类型转换；第二，不能使用bind peeking。
Oracle如何查看SQL实际执行计划
1、 查看最近执行的SQL语句
select /*recentsql*/s.SQL_ID,s.CHILD_NUMBER,s.HASH_VALUE,s.ADDRESS,s.EXECUTIONS,s.SQL_TEXT
from v$sql s
where s.PARSING_USER_ID = (
select u.user_id from all_users u
where u.username = 'YH_TEST'
) and s.COMMAND_TYPE in (2 ,3, 6,7 ,189)
and upper(s.SQL_TEXT) not like upper( '%recentsql%')
2、使用dbms_xplan.display_cursor查看执行计划，它的用法见笔记 《dbms_xplan.display_cursor的用法》,
注意了：若dbms_xplan.display_cursor要以ALLSTATS LAST格式输出的话，/*+gather_plan_statistics*/这个提示信息放到查询语句中是必须的。
select /*+gather_plan_statistics*/ /*plan_statistics1*/ name ,salary from test where name = 't1' ;
select s.SQL_ID,s.CHILD_NUMBER,s.HASH_VALUE,s.ADDRESS,s.EXECUTIONS,s.SQL_TEXT
from v$sql s
where upper(s.SQL_TEXT) like upper('%plan_statistics1%' )
and upper(s.SQL_TEXT) not like upper( '%v$sql%');
select * from table (dbms_xplan.display_cursor('4wktu80k1xy5k' , 0, 'ALLSTATS LAST cost' ));
Oracle如何查看SQL实际执行计划
1、 查看最近执行的SQL语句
select /*recentsql*/s.SQL_ID,s.CHILD_NUMBER,s.HASH_VALUE,s.ADDRESS,s.EXECUTIONS,s.SQL_TEXT
from v$sql s
where s.PARSING_USER_ID = (
select u.user_id from all_users u
where u.username = 'YH_TEST'
) and s.COMMAND_TYPE in (2 ,3, 6,7 ,189)
and upper(s.SQL_TEXT) not like upper( '%recentsql%')
2、使用dbms_xplan.display_cursor查看执行计划，它的用法见笔记 《dbms_xplan.display_cursor的用法》,
注意了：若dbms_xplan.display_cursor要以ALLSTATS LAST格式输出的话，/*+gather_plan_statistics*/这个提示信息放到查询语句中是必须的。
select /*+gather_plan_statistics*/ /*plan_statistics1*/ name ,salary from test where name = 't1' ;
select s.SQL_ID,s.CHILD_NUMBER,s.HASH_VALUE,s.ADDRESS,s.EXECUTIONS,s.SQL_TEXT
from v$sql s
where upper(s.SQL_TEXT) like upper('%plan_statistics1%' )
and upper(s.SQL_TEXT) not like upper( '%v$sql%');
select * from table (dbms_xplan.display_cursor('4wktu80k1xy5k' , 0, 'ALLSTATS LAST cost' ));
2020-01-20 oracle中sql如何执行，什么是硬解析和软解析
在Oracle中，SQL语句的执行过程涉及到两个概念：硬解析和软解析。硬解析是指Oracle在执行SQL语句前，首先要将语句进行解析，生成执行，然后才能执行该语句。这个过程需要一定的时间和资源，但执行的生成可以提高SQL语句的执行效率。软解析是指当Oracle在执行SQL语句时，发现该语句已经被解析过并且执行已经存在，那么就不需要再次解析，直接使用已有的执行进行执行。这样可以提高SQL语句的执行效率，省去解析的时间和资源。在实际应用中，SQL语句的执行过程中可能会出现多次软解析和硬解析的情况，具体会受到各种因素的影响，如语句本身的复杂性、缓存的大小、统计信息的准确性等。因此，在性能调优的过程中，需要根据实际情况对SQL语句进行优化，减少解析的次数，提高执行效率。