如何获取执行计划
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了如何获取执行计划，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7605字，纯文字阅读大概需要11分钟。
内容图文
如何oracle的获取执行计划1.获取普通执行计划，效果类似于先执行set autot on exp;然后执行sql。 explan plan for your_sql; select * from table(dbms_xplan.display);2.获取具有outline信息的执行计划，用sqlprofile调优时非常有用，或者用这个执行计划了
如何oracle的获取执行计划
1.获取普通执行计划，效果类似于先执行set autot on exp;然后执行sql。
explan plan for your_sql;
select * from table(dbms_xplan.display);
2.获取具有outline信息的执行计划，用sqlprofile调优时非常有用，或者用这个执行计划了解更多oracle内部的hint
explan plan for your_sql;
select * from table(dbms_xplan.display(null, null,'advanced -projection'))
3.真实的执行计划，可以看到实际的 Starts（执行次数） | E-Rows（估算的返回行数） | A-Rows（实际的返回行数）
ALTER SESSION SET STATISTICS_LEVEL=ALL;
execute your_sql;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'))
那么这3中获取执行计划的方式可以写到一个脚本getplan.sql，用的时候非常方便。
--getplan.sql
set feedback off timing off ver off
pro 'general,outline,starts'
pro
acc type prompt 'Enter value for plan type:' default 'general'
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')) where '&&type'='starts';
select * from table(dbms_xplan.display) where '&&type'='general';
select * from table(dbms_xplan.display(null, null,'advanced -projection')) where '&&type'='outline';
set feedback on timing on ver on
undef type
测试如下：
SQL> select * from a;
ID NAME
---------- ----------
1 a1
2 a2
3 a3
4 a4
5 a5
SQL> select * from b;
ID NAME
---------- ----------
1 b1
2 b2
--执行计划1：普通执行计划
SQL> explain plan for select a.*,(select name from b where b.id=a.id) from a;
Explained.
Elapsed: 00:00:00.04
SQL> @getplan
'general,outline,starts'
Enter value for plan type: ----这里输入general或回车
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 3653839899
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 100 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| B | 1 | 20 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| A | 5 | 100 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."ID"=:B1)
Note
-----
- dynamic sampling used for this statement
--执行计划2：outline执行计划
SQL> explain plan for select a.*,(select name from b where b.id=a.id) from a;
Explained.
Elapsed: 00:00:00.01
SQL> @getplan
'general,outline,starts'
Enter value for plan type:outline --这里输入outline
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 3653839899
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 100 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| B | 1 | 20 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| A | 5 | 100 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2 / B@SEL$2
2 - SEL$1 / A@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$2" "B"@"SEL$2")
FULL(@"SEL$1" "A"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."ID"=:B1)
Note
-----
- dynamic sampling used for this statement
--执行计划3：real执行计划
SQL> set serveroutput off
SQL> ALTER SESSION SET STATISTICS_LEVEL=ALL;
Session altered.
Elapsed: 00:00:00.00
SQL> select a.*,(select name from b where b.id=a.id) from a;
ID NAME (SELECTNAM
---------- ---------- ----------
1 a1 b1
2 a2 b2
3 a3
4 a4
5 a5
5 rows selected.
Elapsed: 00:00:00.03
SQL> @getplan
'general,outline,starts'
Enter value for plan type:starts --这里输入starts
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID 8rv825dykpx1m, child number 0
-------------------------------------
select a.*,(select name from b where b.id=a.id) from a
Plan hash value: 3653839899
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| B | 5 | 1 | 2 |00:00:00.01 | 35 |
| 2 | TABLE ACCESS FULL| A | 1 | 5 | 5 |00:00:00.01 | 8 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."ID"=:B1)
Note
-----
- dynamic sampling used for this statement
--注意：
--第3种执行计划不能多次获取，只能执行1次，获取一次，否则会获取不到
下面再次获取一下试试：
SQL> @getplan
'general,outline,starts'
Enter value for plan type:starts
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID dvp8nn63wuhs8, child number 0
-------------------------------------
select * from table(dbms_xplan.display(null, null,'advanced -projection'))
where 'starts'='outline'
Plan hash value: 3440229843
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time |
-------------------------------------------------------------------------------------
|* 1 | FILTER | | 1 | 0 |00:00:00.01 |
| 2 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 0 | 0 |00:00:00.01 | --第二次无法获取真实的执行计划
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
Note
-----
- rule based optimizer used (consider using cbo)
--第3种执行计划要关掉set serveroutput off，否则也不能获取执行计划。
测试如下：
这里和上面的测试是同一个会话，所以没有再执行ALTER SESSION SET STATISTICS_LEVEL=ALL;了。
SQL> set serveroutput on
SQL> select a.*,(select name from b where b.id=a.id) from a;
ID NAME (SELECTNAM
---------- ---------- ----------
1 a1 b1
2 a2 b2
3 a3
4 a4
5 a5
5 rows selected.
Elapsed: 00:00:00.03
SQL> @getplan
'general,outline,starts'
Enter value for plan type:starts
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID 9babjv8yq8ru3, child number 0
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END; --也无法获取真实的执行计划
NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
内容总结
以上是互联网集市为您收集整理的如何获取执行计划全部内容，希望文章能够帮你解决如何获取执行计划所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】