Oracle 获取执行计划的几种方法 获取SQL执行计划的常见几种方法

\u8bb2\u89e3ORACLE\u7684\u6267\u884c\u8ba1\u5212\u7684\u51e0\u79cd\u65b9\u6cd5

\u3000\u3000Oracle RDBMS\u6267\u884c\u6bcf\u4e00\u6761SQL\u8bed\u53e5\uff0c\u90fd\u5fc5\u987b\u7ecf\u8fc7Oracle\u4f18\u5316\u5668\u7684\u8bc4\u4f30\u3002\u6240\u4ee5\uff0c\u4e86\u89e3\u4f18\u5316\u5668\u662f\u5982\u4f55\u9009\u62e9(\u641c\u7d22)\u8def\u5f84\u4ee5\u53ca\u7d22\u5f15\u662f\u5982\u4f55\u88ab\u4f7f\u7528\u7684\uff0c\u5bf9\u4f18\u5316SQL\u8bed\u53e5\u6709\u5f88\u5927\u7684\u5e2e\u52a9\u3002Explain\u53ef\u4ee5\u7528\u6765\u8fc5\u901f\u65b9\u4fbf\u5730\u67e5\u51fa\u5bf9\u4e8e\u7ed9\u5b9aSQL\u8bed\u53e5\u4e2d\u7684\u67e5\u8be2\u6570\u636e\u662f\u5982\u4f55\u5f97\u5230\u7684\u5373\u641c\u7d22\u8def\u5f84(\u6211\u4eec\u901a\u5e38\u79f0\u4e3aAccess Path)\u3002\u4ece\u800c\u4f7f\u6211\u4eec\u9009\u62e9\u6700\u4f18\u7684\u67e5\u8be2\u65b9\u5f0f\u8fbe\u5230\u6700\u5927\u7684\u4f18\u5316\u6548\u679c\u3002
\u3000\u30001.1 \u3001\u5b89 \u88c5
\u3000\u3000\u8981\u4f7f\u7528EXPLAIN\u9996\u5148\u8981\u6267\u884c\u76f8\u5e94\u7684\u811a\u672c\uff0c\u521b\u5efa\u51faExplain_plan\u8868\u3002
\u3000\u3000\u5177\u4f53\u811a\u672c\u6267\u884c\u5982\u4e0b\uff1a
\u3000\u3000$ORACLE_HOME/rdbms/admin/utlxplan.sql (UNIX) \u8be5\u811a\u672c\u540e\u4f1a\u751f\u6210\u4e00\u4e2a\u8868\u8fd9\u4e2a\u7a0b\u5e8f\u4f1a\u521b\u5efa\u4e00\u4e2a\u540d\u4e3aplan_table\u7684\u8868\u3002
\u3000\u30001.2 \u3001\u4f7f\u7528
\u3000\u3000\u5e38\u89c4\u4f7f\u7528\u8bed\u6cd5\uff1a
\u3000\u3000explainPLAN[SETSTATEMENT_ID[=]<stringliteral][INTO<table_name]
\u3000\u3000FOR<sql_statement\u3000\u3000\u5176\u4e2d\uff1a\u3000\u3000STATEMENT_ID\uff1a\u662f\u4e00\u4e2a\u552f\u4e00\u7684\u5b57\u7b26\u4e32\uff0c\u628a\u5f53\u524d\u6267\u884c\u8ba1\u5212\u4e0e\u5b58\u50a8\u5728\u540c\u4e00PLAN\u4e2d\u7684\u5176\u5b83\u6267\u884c\u8ba1\u5212\u533a\u522b\u5f00\u6765\u3002
\u3000\u3000TABLE_NAME\uff1a\u662fplan\u8868\u540d\uff0c\u5b83\u7ed3\u6784\u5982\u524d\u6240\u793a\uff0c\u4f60\u53ef\u4ee5\u4efb\u610f\u8bbe\u5b9a\u8fd9\u4e2a\u540d\u79f0\u3002
\u3000\u3000SQL_STATEMENT\uff1a\u662f\u771f\u6b63\u7684SQL\u8bed\u53e5\u3002
\u3000\u3000\u6bd4\u5982\uff1a\u3000\u3000SQLexplainplansetstatement_id='T_TEST'forselect*fromt_test;\u3000\u3000SQL\u3000\u3000Explained
\u3000\u3000\u6267\u884c\u4e0b\u9762\u8bed\u53e5\u53ef\u4ee5\u67e5\u8be2\u5230\u6267\u884c\u8ba1\u5212

1. \u9884\u4f30\u6267\u884c\u8ba1\u5212 - Explain Plan
Explain plan\u4ee5SQL\u8bed\u53e5\u4f5c\u4e3a\u8f93\u5165\uff0c\u5f97\u5230\u8fd9\u6761SQL\u8bed\u53e5\u7684\u6267\u884c\u8ba1\u5212\uff0c\u5e76\u5c06\u6267\u884c\u8ba1\u5212\u8f93\u51fa\u5b58\u50a8\u5230\u8ba1\u5212\u8868\u4e2d\u3002
\u9996\u5148\uff0c\u5728\u4f60\u8981\u6267\u884c\u7684SQL\u8bed\u53e5\u524d\u52a0explain plan for\uff0c\u6b64\u65f6\u5c06\u751f\u6210\u7684\u6267\u884c\u8ba1\u5212\u5b58\u50a8\u5230\u8ba1\u5212\u8868\u4e2d\uff0c\u8bed\u53e5\u5982\u4e0b\uff1a
explain plan for SQL\u8bed\u53e5
\u7136\u540e\uff0c\u5728\u8ba1\u5212\u8868\u4e2d\u67e5\u8be2\u521a\u521a\u751f\u6210\u7684\u6267\u884c\u8ba1\u5212\uff0c\u8bed\u53e5\u5982\u4e0b\uff1a
select * from table(dbms_xplan.display);
\u6ce8\u610f\uff1aExplain plan\u53ea\u751f\u6210\u6267\u884c\u8ba1\u5212\uff0c\u5e76\u4e0d\u4f1a\u771f\u6b63\u6267\u884cSQL\u8bed\u53e5\uff0c\u56e0\u6b64\u4ea7\u751f\u7684\u6267\u884c\u8ba1\u5212\u6709\u53ef\u80fd\u4e0d\u51c6\uff0c\u56e0\u4e3a\uff1a
1\uff09\u5f53\u524d\u7684\u73af\u5883\u53ef\u80fd\u548c\u6267\u884c\u8ba1\u5212\u751f\u6210\u65f6\u7684\u73af\u5883\u4e0d\u540c\uff1b
2\uff09\u4e0d\u4f1a\u8003\u8651\u7ed1\u5b9a\u53d8\u91cf\u7684\u6570\u636e\u7c7b\u578b\uff1b
3\uff09\u4e0d\u8fdb\u884c\u53d8\u91cf\u7aa5\u89c6\u3002
2. \u67e5\u8be2\u5185\u5b58\u4e2d\u7f13\u5b58\u7684\u6267\u884c\u8ba1\u5212 \uff08dbms_xplan.display_cursor\uff09
\u5982\u679c\u4f60\u60f3\u83b7\u53d6\u6b63\u5728\u6267\u884c\u7684\u6216\u521a\u6267\u884c\u7ed3\u675f\u7684SQL\u8bed\u53e5\u771f\u5b9e\u7684\u6267\u884c\u8ba1\u5212\uff08\u5373\u83b7\u53d6library cache\u4e2d\u7684\u6267\u884c\u8ba1\u5212\uff09\uff0c\u53ef\u4ee5\u5230\u52a8\u6001\u6027\u80fd\u89c6\u56fe\u91cc\u67e5\u8be2\u3002\u65b9\u6cd5\u5982\u4e0b\uff1a
1\uff09\u83b7\u53d6SQL\u8bed\u53e5\u7684\u6e38\u6807
\u6e38\u6807\u5206\u4e3a\u7236\u6e38\u6807\u548c\u5b50\u6e38\u6807\uff0c\u7236\u6e38\u6807\u7531sql_id(\u6216\u8054\u5408address\u548chash_value)\u5b57\u6bb5\u8868\u793a\uff0c\u5b50\u6e38\u6807\u7531child_number\u5b57\u6bb5\u8868\u793a\u3002
\u5982\u679cSQL\u8bed\u53e5\u6b63\u5728\u8fd0\u884c\uff0c\u53ef\u4ee5\u4ecev$session\u4e2d\u83b7\u5f97\u5b83\u7684\u6e38\u6807\u4fe1\u606f\uff0c\u5982\uff1a
select status, sql_id, sql_child_number from v$session where status='ACTIVE' and ....
\u5982\u679c\u77e5\u9053SQL\u8bed\u53e5\u5305\u542b\u67d0\u4e9b\u5173\u952e\u5b57\uff0c\u53ef\u4ee5\u4ecev$sql\u89c6\u56fe\u4e2d\u83b7\u5f97\u5b83\u7684\u6e38\u6807\u4fe1\u606f\uff0c\u5982\uff1a
select sql_id, child_number, sql_text from v$sql where sql_text like '%\u5173\u952e\u5b57%\u2018
2\uff09\u83b7\u53d6\u5e93\u7f13\u5b58\u4e2d\u7684\u6267\u884c\u8ba1\u5212
\u4e3a\u4e86\u83b7\u53d6\u7f13\u5b58\u5e93\u4e2d\u7684\u6267\u884c\u8ba1\u5212\uff0c\u53ef\u4ee5\u76f4\u63a5\u67e5\u8be2\u52a8\u6001\u6027\u80fd\u89c6\u56fev$sql_plan\u548cv$sql_plan_statistics_all\u7b49\uff0c\u4f46\u66f4\u65b9\u4fbf\u7684\u65b9\u6cd5\u662f\u4ee5sql_id\u548c\u5b50\u6e38\u6807\u4e3a\u53c2\u6570\uff0c\u6267\u884c\u5982\u4e0b\u8bed\u53e5\uff1a
select * from table(dbms_xplan.display_cursor('sql_id',child_number));
3\uff09\u83b7\u53d6\u524d\u4e00\u6b21\u7684\u6267\u884c\u8ba1\u5212\uff1a
set serveroutput off
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
3. \u67e5\u8be2\u5386\u53f2\u6267\u884c\u8ba1\u5212\uff08dbms_xplan.display_awr\uff09
AWR\u4f1a\u5b9a\u65f6\u628a\u52a8\u6001\u6027\u80fd\u89c6\u56fe\u4e2d\u7684\u6267\u884c\u8ba1\u5212\u4fdd\u5b58\u5230dba_hist_sql_plan\u89c6\u56fe\u4e2d\uff0c\u5982\u679c\u4f60\u60f3\u8981\u67e5\u770b\u5386\u53f2\u6267\u884c\u8ba1\u5212\uff0c\u53ef\u4ee5\u91c7\u7528\u5982\u4e0b\u65b9\u6cd5\u67e5\u8be2\uff1a
select * from table(dbms_xplan.display_awr('sql_id');
4. \u5728\u7528sqlplus\u505aSQL\u5f00\u53d1\u662f\uff08Autotrace\uff09
set autotrace\u662fsqlplus\u5de5\u5177\u7684\u4e00\u4e2a\u529f\u80fd\uff0c\u53ea\u80fd\u5728\u901a\u8fc7sqlplus\u8fde\u63a5\u7684session\u4e2d\u4f7f\u7528\uff0c\u5b83\u975e\u5e38\u9002\u5408\u5728\u5f00\u53d1\u65f6\u6d4b\u8bd5SQL\u8bed\u53e5\u7684\u6027\u80fd\uff0c\u6709\u4ee5\u4e0b\u51e0\u79cd\u53c2\u6570\u53ef\u4f9b\u9009\u62e9\uff1a
SET AUTOTRACE OFF ---------------- \u4e0d\u663e\u793a\u6267\u884c\u8ba1\u5212\u548c\u7edf\u8ba1\u4fe1\u606f\uff0c\u8fd9\u662f\u7f3a\u7701\u6a21\u5f0f
SET AUTOTRACE ON EXPLAIN ------ \u53ea\u663e\u793a\u4f18\u5316\u5668\u6267\u884c\u8ba1\u5212
SET AUTOTRACE ON STATISTICS -- \u53ea\u663e\u793a\u7edf\u8ba1\u4fe1\u606f
SET AUTOTRACE ON ----------------- \u6267\u884c\u8ba1\u5212\u548c\u7edf\u8ba1\u4fe1\u606f\u540c\u65f6\u663e\u793a
SET AUTOTRACE TRACEONLY ------ \u4e0d\u771f\u6b63\u6267\u884c\uff0c\u53ea\u663e\u793a\u9884\u671f\u7684\u6267\u884c\u8ba1\u5212\uff0c\u540cexplain plan

5. \u751f\u6210Trace\u6587\u4ef6\u67e5\u8be2\u8be6\u7ec6\u7684\u6267\u884c\u8ba1\u5212 \uff08SQL_Trace, 10046\uff09
SQL_TRACE\u4f5c\u4e3a\u521d\u59cb\u5316\u53c2\u6570\u53ef\u4ee5\u5728\u5b9e\u4f8b\u7ea7\u522b\u542f\u7528\uff0c\u4e5f\u53ef\u4ee5\u53ea\u5728\u4f1a\u8bdd\u7ea7\u522b\u542f\u7528\uff0c\u5728\u5b9e\u4f8b\u7ea7\u522b\u542f\u7528SQL_TRACE\u4f1a\u5bfc\u81f4\u6240\u6709\u8fdb\u7a0b\u7684\u6d3b\u52a8\u88ab\u8ddf\u8e2a\uff0c\u5305\u62ec\u540e\u53f0\u8fdb\u7a0b\u53ca\u6240\u6709\u7528\u6237\u8fdb\u7a0b\uff0c\u8fd9\u901a\u5e38\u4f1a\u5bfc\u81f4\u6bd4\u8f83\u4e25\u91cd\u7684\u6027\u80fd\u95ee\u9898\uff0c\u6240\u4ee5\u5728\u4e00\u822c\u60c5\u51b5\u4e0b\uff0c\u6211\u4eec\u4f7f\u7528sql_trace\u8ddf\u8e2a\u5f53\u524d\u8fdb\u7a0b\uff0c\u65b9\u6cd5\u5982\u4e0b\uff1a
SQL>alter session set sql_trace=true;
...\u88ab\u8ddf\u8e2a\u7684SQL\u8bed\u53e5...
SQL>alter session set sql_trace=false;
\u5982\u679c\u8981\u8ddf\u8e2a\u5176\u5b83\u8fdb\u7a0b\uff0c\u53ef\u4ee5\u901a\u8fc7Oracle\u63d0\u4f9b\u7684\u7cfb\u7edf\u5305DBMS_SYSTEM. SET_SQL_TRACE_IN_SESSION\u6765\u5b9e\u73b0\uff0c\u4f8b\u5982\uff1a
SQL> exec dbms_system.set_sql_trace_in_session(sid,serial#,true) --\u5f00\u59cb\u8ddf\u8e2a
SQL> exec dbms_system.set_sql_trace_in_session(sid,serial#,false) --\u7ed3\u675f\u8ddf\u8e2a
\u751f\u6210trace\u6587\u4ef6\u540e\uff0c\u518d\u7528tkprof \u5de5\u5177\u5c06sql trace \u751f\u6210\u7684\u8ddf\u8e2a\u6587\u4ef6\u8f6c\u6362\u6210\u6613\u8bfb\u7684\u683c\u5f0f\uff0c\u8bed\u6cd5\u5982\u4e0b\uff1a
tkprof inputfile outputfile
10046\u4e8b\u4ef6\u662fSQL_TRACE\u7684\u4e00\u4e2a\u5347\u7ea7\u7248\uff0c\u5b83\u4e5f\u662f\u8ffd\u8e2a\u4f1a\u8bdd\uff0c\u751f\u6210Trace\u6587\u4ef6\uff0c\u53ea\u662f\u5b83\u91cc\u9762\u7684\u5185\u5bb9\u66f4\u8be6\u7ec6\uff0c

1.1. 获取执行计划
1. explain plan for获取；
2. set autotrace on ；
3. statistics_level=all;
4. 通过dbms_xplan.display_cursor输入sql_id参数直接获取
5. 10046 trace跟踪
6. awrsqrpt.sql

l 如果某SQL执行很长时间才出结果戒返回不了结果，这时就只能用方法1；
l 跟踪某条SQL最简单的方法是方法1，其次就是方法2；
l 如果想观察到某条SQL有多条执行计划的情况，只能用方法4和方法6；
l 如果SQL中含有函数，函数中套有SQL等多层调用，想准确分析只能使用方法5；
l 要想确保看到真实的执行计划，不能用方法1和方法2；
l 要想获取表被访问的次数，只能使用方法3；

方法一

步骤1：explain plan for "你的SQL"
步骤2：select * from table(dbms_xplan.display());
等同于F5

方法二

步骤1：set autotrace on
步骤2：在此处执行你的SQL即可，后续自然会有结果输出
另，有如下几种方式：
set autotrace on (得到执行计划，输出运行结果）
set autotrace traceonly (得到执行计划，不输出运行结果）
set autotrace traceonly explain (得到执行计划，不输出运行结果和统计信息部分，仅展现执行计划部分）
set autotrace traceonl statistics(不输出运行结果和执行计划部分，仅展现统计信息部分）

方法三

步骤1：alter session set statistics_level=all ;
步骤2：在此处执行你的SQL
步骤3：select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
另注：
1. 如果你用 /*+ gather_plan_statistics */的方法，可以省略步骤1，直接步骤2,3。
2. 关键字解读（其中OMem、1Mem和User-Mem在后续的课程中会陆续见到）：
Starts为该sql执行的次数。
E-Rows为执行计划预计的行数。
A-Rows为实际返回的行数。A-Rows跟E-Rows做比较，就可以确定哪一步执行计划出了问题。
A-Time为每一步实际执行的时间（HH：MM：SS.FF），根据这一行可以知道该sql耗时在了哪个地方。
Buffers为每一步实际执行的逻辑读或一致性读。
Reads为物理读。
OMem:当前操作完成所有内存工作区(Work Aera)操作所总共使用私有内存(PGA)中工作区的大小，
这个数据是由优化器统计数据以及前一次执行的性能数据估算得出的
1Mem:当工作区大小无法满足操作所需的大小时，需要将部分数据写入临时磁盘空间中(如果仅需要写入一次就可以完成操作，
就称一次通过，One-Pass;否则为多次通过，Multi_Pass).该列数据为语句最后一次执行中，单次写磁盘所需要的内存
大小，这个由优化器统计数据以及前一次执行的性能数据估算得出的
User-Mem:语句最后一次执行中，当前操作所使用的内存工作区大小，括号里面为(发生磁盘交换的次数,1次即为One-Pass,
大于1次则为Multi_Pass,如果没有使用磁盘，则显示OPTIMAL)
OMem、1Mem为执行所需的内存评估值，0Mem为最优执行模式所需内存的评估值，1Mem为one-pass模式所需内存的评估值。
0/1/M 为最优/one-pass/multipass执行的次数。Used-Mem耗的内存

方法四

select sql_id,child_number from v$sql where sql_text like '%……%';
步骤1： select * from table(dbms_xplan.display_cursor('&sq_id')); （该方法是从共享池里得到）

注：
1. 还有一个方法，select * from table(dbms_xplan.display_awr('&sq_id'));（这是awr性能视图里获取到的）
2. 如果有多执行计划，可以用类似方法查出
select * from table(dbms_xplan.display_cursor('cyzznbykb509s',0));
select * from table(dbms_xplan.display_cursor('cyzznbykb509s',1));

方法五

步骤1：alter session set events '10046 trace name context forever,level 12'; （开启跟踪）
步骤2：执行你的语句
步骤3：alter session set events '10046 trace name context off'; （关闭跟踪）
步骤4：找到跟踪后产生的文件
步骤5：tkprof trc文件 目标文件 sys=no sort=prsela,exeela,fchela （格式化命令）

方法六

步骤1：@?/rdbms/admin/awrsqrpt.sql
步骤2：选择你要的断点（begin snap 和end snap)
步骤3：输入你的sql_id

扩展阅读：oracle查看执行计划方法 ... oracle 查看执行计划 ... 查询执行计划 oracle ... oracle执行计划怎么解读 ... 查看执行计划的sql语句 ... oracle走错执行计划分析 ... oracle执行计划语句 ... oracle解释计划怎么看 ... oracle怎么看执行计划好坏 ...