Home >Database >Mysql Tutorial >如何获取执行计划
如何获取执行计划
- WBOYOriginal
- 2016-06-07 16:09:531183browse
如何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)
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：C3P0多数据源的死锁问题Next article：rac_进行grid自检时提示执行runfixup.sh脚本一例