1.執(zhí)行計(jì)劃
執(zhí)行計(jì)劃就是Oracle基于成本(Cost)、算法和統(tǒng)計(jì)信息,最終得到資源消耗最低的SQL執(zhí)行步驟的組合。其中,成本的值是一個(gè)估算值,包括訪問路徑、關(guān)聯(lián)方式、I/O、CPU和內(nèi)存等。
2.獲取執(zhí)行計(jì)劃
2.1.EXPLAIN PLAN
優(yōu)化器基于當(dāng)前數(shù)據(jù)庫對(duì)象統(tǒng)計(jì)信息生成執(zhí)行計(jì)劃和相關(guān)信息,而不實(shí)際執(zhí)行語句。
SQL> explain plan for select e.empno,e.job,d.dname from scott.emp e,scott.dept d
2 where e.deptno=d.deptno
3 and d.dname = 'ACCOUNTING';
Explained.
SQL> select * from table(dbms_xplan.display(NULL,NULL,'ADVANCED'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 216 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 4 | 216 | 6 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DEPT | 1 | 22 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 12 | 384 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
2.2.SET AUTOTRACE
跟explain for一樣,set autotrac也是不實(shí)際執(zhí)行。
SQL> set autot
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> set autotrace traceonly
SQL> select e.empno,e.job,d.dname from scott.emp e,scott.dept d
2 where e.deptno=d.deptno;
12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 648 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 12 | 648 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 12 | 384 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
2.3.DBMS_XPLAN
不同于前兩種方法,DBMS_XPLAN查看的執(zhí)行計(jì)劃是真實(shí)的。
2.3.1.DISPLAY_CURSOR
用于獲取內(nèi)存中shared_pool游標(biāo)緩存。
select * from table(dbms_xplan.DISPLAY_CURSOR('sql_id','null',ADVANCED ALLSTATS LAST'));
--sql_id
--child_number,null表示顯示所有子游標(biāo)
--format: ALLSTATS LAST 、 ADVANCED ALLSTATS LAST
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR('2wnhpatm9a24s',null,'ADVANCED ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID 2wnhpatm9a24s, child number 0
-------------------------------------
select e.empno,e.job,d.dname from scott.emp e,scott.dept d where
e.deptno=d.deptno
Plan hash value: 615168685
--------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| | | | |
|* 1 | HASH JOIN | | 12 | 648 | 6 (0)| 00:00:01 | 1695K| 1695K| 1021K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 | | | |
| 3 | TABLE ACCESS FULL| EMP | 12 | 384 | 3 (0)| 00:00:01 | | | |
---------------------------------------------------------------------------
2.3.2.DISPLAY_AWR
用于獲取AWR基表WRH$_SQL_PLAN。
select * from table(dbms_xplan.display_awr('sql_id',plan_hash_value,db_id,'format'))
--sql_id
--NULL表示顯示所有
--默認(rèn)獲取本地v$database中的值
-- ALLSTATS LAST 、 ADVANCED ALLSTATS LAST
select * from table(dbms_xplan.display_awr('as3uq6ggb3gx6',null,null,'ADVANCED'));
2.4.獲取真實(shí)消耗資源
執(zhí)行計(jì)劃是SQL語句執(zhí)行前基于當(dāng)前的統(tǒng)計(jì)信息生成的,日中rows、bytes、cost、time等為評(píng)估值,為了獲取更為準(zhǔn)確的實(shí)際值,即A_ROWS,A_TIME等,數(shù)據(jù)庫在執(zhí)行SQL語句時(shí)需要做額外的收集。
--會(huì)話
SQL> alter session set STATISTICS_LEVEL=ALL;
Session altered.
SQL> select * from scott.dept d,scott.emp e where d.deptno=e.deptno and d.dname='SALES';
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null,null,'ADVANCED ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID bcym3bsvz65x7, child number 0
-------------------------------------
select * from scott.dept d,scott.emp e where d.deptno=e.deptno and
d.dname='SALES'
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation| Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows |A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)|
| 6 |00:00:00.02 | 15 | | | |
|* 1 | HASH JOIN | | 1 | 4 | 468 | 6 (0)| 00:00
:01 | 6 |00:00:00.02 | 15 | 1476K| 1476K| 471K (0)|
|* 2 | TABLE ACCESS FULL| DEPT | 1 | 1 | 30 | 3 (0)| 00:00
:01 | 1 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS FULL| EMP | 1 | 12 | 1044 | 3 (0)| 00:00
:01 | 12 |00:00:00.01 | 8 | | | |
---------------------------------------------------------------------------
--語句
select /*+ gather_plan_statistics */ .......
3.查看執(zhí)行計(jì)劃
3.1.右上原則
最右最上先執(zhí)行。
3.2.樹形圖解法

例如上述執(zhí)行計(jì)劃:
先畫出樹形結(jié)構(gòu):
- 自頂向下
- 最接近的上方,并且前進(jìn)一格為父子節(jié)點(diǎn)。
- 同一父親、相同縮進(jìn),為兄弟節(jié)點(diǎn)
1縮進(jìn)一格,為0的兒子; 2和4縮進(jìn)相同為兄弟節(jié)點(diǎn),同為1的兒子,2在上,為兄; 3相對(duì)2縮進(jìn)一格,為2的兒子;5為4的兒子;得到如下樹形圖

執(zhí)行順序: - 先遍歷左子樹
- 再遍歷右子樹
- 左節(jié)點(diǎn)先于右節(jié)點(diǎn)執(zhí)行
- 子節(jié)點(diǎn)先于父節(jié)點(diǎn)執(zhí)行
- 對(duì)于相同縮進(jìn)、上下同父的兄弟節(jié)點(diǎn),兄先執(zhí)行
- 最后訪問根節(jié)點(diǎn)
4.常用的執(zhí)行計(jì)劃查看語句
查看當(dāng)前sql在內(nèi)存中最后一次執(zhí)行計(jì)劃
--查看當(dāng)前SQL在內(nèi)存中的最后一次執(zhí)行計(jì)劃,命令如下:
SELECT RPAD('Inst: ' || v.inst_id, 9) || ' ' ||
RPAD('Child: ' || v.child_number, 11) inst_child,
t.plan_table_output
FROM gv$sql v,
TABLE(DBMS_XPLAN.DISPLAY('gv$sql_plan_statistics_all',
NULL,
'ADVANCED ALLSTATS LAST -Projection -Outline -Note',
'inst_id = ' || v.inst_id ||
' AND sql_id = ''' || v.sql_id ||
''' AND child_number = ' || v.child_number)) t
WHERE v.sql_id = '&SQL_ID'
AND v.loaded_versions > 0;
--查看當(dāng)前SQL在內(nèi)存中的所有執(zhí)行計(jì)劃,命令如下:
SELECT RPAD('Inst: ' || v.inst_id, 9) || ' ' ||
RPAD('Child: ' || v.child_number, 11) inst_child,
t.plan_table_output
FROM gv$sql v,
TABLE(DBMS_XPLAN.DISPLAY('gv$sql_plan_statistics_all',
NULL,
'ADVANCED ALLSTATS -Projection -Outline -Note',
'inst_id = ' || v.inst_id ||
' AND sql_id = ''' || v.sql_id ||
''' AND child_number = ' || v.child_number)) t
WHERE v.sql_id = '&SQL'
AND v.loaded_versions > 0
AND v.executions > 1;
--查看指定SQL的歷史執(zhí)行計(jì)劃,包括記錄在快照點(diǎn)中執(zhí)行計(jì)劃,命令如下:
SELECT t.plan_table_output
FROM (SELECT DISTINCT sql_id, plan_hash_value, dbid
FROM dba_hist_sql_plan
WHERE sql_id = '&SQL') v,
TABLE(DBMS_XPLAN.DISPLAY_AWR(v.sql_id,
v.plan_hash_value,
null,
'ADVANCED ALLSTATS')) t;
注:本文參考于:《DBA攻堅(jiān)指南》
最后修改時(shí)間:2022-11-04 09:10:54
「喜歡這篇文章,您的關(guān)注和贊賞是給作者最好的鼓勵(lì)」
關(guān)注作者
【版權(quán)聲明】本文為墨天輪用戶原創(chuàng)內(nèi)容,轉(zhuǎn)載時(shí)必須標(biāo)注文章的來源(墨天輪),文章鏈接,文章作者等基本信息,否則作者和墨天輪有權(quán)追究責(zé)任。如果您發(fā)現(xiàn)墨天輪中有涉嫌抄襲或者侵權(quán)的內(nèi)容,歡迎發(fā)送郵件至:contact@modb.pro進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),墨天輪將立刻刪除相關(guān)內(nèi)容。




