频道导航

Oracle执行计划讲解（一）

2020-07-03 Oracle 前端之家

前端之家收集整理的这篇文章主要介绍了Oracle执行计划讲解（一），前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

看懂Oracle执行计划是优化的第一步，让我们从下面的例子开始吧。

下面为补充内容

1、创建测试表

[sql]view plaincopy 
   
 sql>createtabletasselect1id,object_namefromdba_objects;
 
 Tablecreated
 sql>updatetsetid=99whererownum=1;
 1rowupdated
commit;
Commitcomplete
indext_indont(id);
Indexcreated

oracle优化器：RBO和CBO两种，从oracle10g开始优化器已经抛弃了RBO，下面的列子说明CBO大概是怎样的

copy 
    
   

/*+dynamic_sampling(t0)*/*

from

where

id=1;

50819rowsselected.
ExecutionPlan
----------------------------------------------------------
Planhashvalue:1376202287
-------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%cpu)|Time|
|0|SELECTSTATEMENT||195|15405|51(0)|00:00:01|
|1|TABLEACCESSBYINDEXROWID|T|195|15405|51(0)|00:00:01|
|*2|INDEXRANGESCAN|T_IND|78||50(0)|00:00:01|
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
2-access("ID"=1)

现象t表还没有被分析，提示/*+dynamic_sampling(t 0) */*的目的是让CBO无法通过动态采样获取表中的实际数据情况，此时CBO只能根据T表中非常有限的信息（比如表中的extents数量，数据块的数量）来猜测表中的数据。从结果中可以看到CBO猜出表中id=1的有195条，这个数值对于表的总数来说，是一个非常小的值，所以CBO选择了索引而不是全表扫描。
而实际情况如下所示：

copy 
    
   

where

id=1

2;
50819rowsselected.
ExecutionPlan
----------------------------------------------------------
Planhashvalue:1601196873
--------------------------------------------------------------------------
|Id|Operation|Time|
|0|SELECTSTATEMENT||49454|3815K|67(2)|00:00:01|
|*1|FULL|T|49454|3815K|67(2)|00:00:01|
--------------------------------------------------------------------------
1-filter("ID"=1)

通过动态取样，CBO估算出行数为49454，非常接近于真实50820数目。选择了全表扫描。
我们来收集一下统计信息

copy 
    
   

dbms_stats.gather_table_stats(

user

't'

cascade

true

);

Planhashvalue:1601196873
SELECTSTATEMENT||50815|1339K|67(2)|00:00:01|
|*1|FULL|T|50815|1339K|67(2)|00:00:01|
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
1-filter("ID"=1)

现在扫描过的行数为50815。

如果我们更新了所有的id为99看看。

copy 
    
   

id=99;

50820rowsupdated
whereid=99;
SELECTSTATEMENT||1|27|2(0)|00:00:01|
INDEXROWID|T|1|27|2(0)|00:00:01|
INDEXRANGESCAN|T_IND|1||1(0)|00:00:01|
2-access("ID"=99)

因为没有对表进行分析，所以表中的分析数据还是之前的信息，CBO并不知道。我们可以看出Rows值为1，也就是说CBO人为表T中的ID=99的值只有1条，所有选择仍然是索引。

我们收集一把统计信息。

copy 
    
   

PL/sqlproceduresuccessfullycompleted

1-filter("ID"=99)

上面为补充内容，下面正式开始

1、 sql的执行计划

创建测试表

copy 
    
   

t1(id

int

,153); background-color:inherit; font-weight:bold">name

varchar2(1000));

tablet2(idindexind_t1ont1(id);
Indexcreated
indexind_t2ont2(id);
indexind_t2_nameont2(name);
insertintot1selecta.OBJECT_ID,a.OBJECT_NAMEfromall_objectsa;
50206rowsinserted
intot2fromall_objectsawhererownum<=20;
20't1','t2',153); background-color:inherit; font-weight:bold">proceduresuccessfullycompleted

2、产生执行计划

copy 
    
   

t1,t2

where

t1.id=t2.id;

Planhashvalue:828990364
--------------------------------------------------------------------------------------
SELECTSTATEMENT||20|780|43(0)|00:00:01|
INDEXROWID|T1|1|28|2(0)|00:00:01|
|2|NESTEDLOOPS||20|780|43(0)|00:00:01|
|3|FULL|T2|20|220|3(0)|00:00:01|
|*4|INDEXRANGESCAN|IND_T1|1||1(0)|00:00:01|
4-access("T1"."ID"="T2"."ID")
Statistics
1recursivecalls
0dbblockgets
37consistentgets
0physicalreads
0redosize
1452bytessentviasql*Nettoclient
503bytesreceivedviasql*Netfromclient
3sql*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
20rowsprocessed

看执行计划时，我们首先从缩进最大的行读取，它是最先被执行的步骤。在执行计划中：id=3和id=4是最先被执行的，

copy 
    
   

|3|FULL|T2|20|220|3(0)|00:00:01|

|*4|INDEXRANGESCAN|IND_T1|1||1(0)|00:00:01|

两行缩进一样的，最上面的最先被执行，在这里就是id=3

copy 
    
   

|T2|20|220|3(0)|00:00:01|

选择次之缩进的行数id=2，表连接方式为NESTED LOOPS。

copy 
    
   

|2|NESTEDLOOPS||20|780|43(0)|00:00:01|

然后是id=1，扫描表的方式为TABLE ACCESS BY INDEX ROWID

copy 
    
   

|1|INDEXROWID|T1|1|28|2(0)|00:00:01|

最后是id=0

copy 
    
   

|0|SELECTSTATEMENT||20|780|43(0)|00:00:01|

我们翻译成语言大概如下，
从t2表第一行读取，查看每一行是否符合下面条件：
"T1"."ID"="T2"."ID"
如果符合就拿出一行来，扫描整个t2表，这个过程就叫NESTED LOOPS
当整个t2表被扫描完之后，会产生一个结果集，这个结果集是IND_T1的一个索引集，然后oracle根据索引键值上的rowid去T1表中找到相应的记录，就是这一步：TABLE ACCESS BY INDEX ROWID
然后将结果返回：SELECT STATEMENT
id列为： id=3->id=4->id=2->id=1->id=0
让我们再看一看表中每一行表示什么含义：
1）Operation 列：当前操作的内容。
2）Rows 列：就是当前操作的 cardinality ，Oracle估算当前操作的返回结果集。
3）Cost (%cpu) : Oracle计算出来的一个数值（代价），用于说明sql执行的代价。
4）Time 列：Oracle估算当前操作的时间。

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("T1"."ID"="T2"."ID")

这里有access和filter区别，access就表示这个谓词的条件的值将会影响数据的访问路径（一般针对索引），filter只起过滤作用。
举个例子

copy 
    
   

where

t1.

name

'AA'

;

norowsselected
Planhashvalue:3617692013
SELECTSTATEMENT||2|56|69(2)|00:00:01|
FULL|T1|2|56|69(2)|00:00:01|
1-filter("T1"."NAME"='AA')

懂了吧。

下面我们来仔细分析Operation里面的内容

copy 
    
   

<prename="code"class="sql">a、表访问方式<spanstyle="font-family:宋体;color:#333333;font-size:14px;line-height:26px"><spanstyle="font-family:宋体;font-size:9pt">1.FullTableScan(FTS)<spanstyle="font-family:宋体;font-size:9pt">全表扫描

InaFTSoperation,thewholetableisreaduptothehighwatermark(HWM).TheHWMmarksthelastblockinthetablethathaseverhaddatawrittentoit.IfyouhavedeletedalltherowsthenyouwillstilltotheHWM.TruncateresetstheHWMbacktothestartofthetable.FTSusesmultiblocki/otoreadtheblocksfromdisk.<spanstyle="color:#ff0000;"><spanstyle="color:#ff0000;">--全表扫描模式下会读数据到表的高水位线（HWM即表示表曾经扩展的最后一个数据块），读取速度依赖于Oracle初始化参db_block_multiblock_read_count(我觉得应该这样翻译:FTS扫描会使表使用上升到高水位(HWM),HWM标识了表最后写入数据的块,如果你用DELETE删除了所有的数据表仍然处于高水位(HWM),只有用TRUNCATE才能使表回归,FTS使用多IO从磁盘读取数据块).QueryPlan
------------------------------------
SELECTSTATEMENT[CHOOSE]Cost=1
**INDEXUNIQUESCANEMP_I1<spanstyle="color:#ff0000;">--如果索引里就找到了所要的数据，就不会再去访问表<spanstyle="color:#ff0000;">
2.IndexLookup索引扫描
Thereare5methodsofindexlookup:
1）indexuniquescan--索引唯一扫描
Methodforlookingupasinglekeyvalueviaauniqueindex.alwaysreturnsasinglevalue,YoumustsupplyATLEASTtheleadingcolumntoaccessdataviatheindex.
eg:sql>selectempno,enamefromempwhereempno=10;<prename="code"class="sql">sql>whereempno=10;
Planhashvalue:2949544139
SELECTSTATEMENT||1|20|1(0)|00:00:01|
INDEXROWID|EMP|1|20|1(0)|00:00:01|
UNIQUESCAN|PK_EMP|1||1(0)|00:00:01|
2-access("EMPNO"=10)
24recursivecalls
3consistentgets
385bytessentviasql*Net 481bytesreceivedviasql*Net 1sql*Netroundtrips 0rowsprocessed
</pre>
2）indexrangescan--索引局部扫描
Indexrangescanisamethodforaccessingarangevaluesofaparticularcolumn.indexmustbesuppliedindex.Canbeusedforrangeoperations(e.g.><<>>=<=between).
<preselectempnowhereEMPNO>=7902;
Planhashvalue:1567865628
---------------------------------------------------------------------------
SELECTSTATEMENT||2|26|2(0)|00:00:01|
INDEXRANGESCAN|PK_EMP|2|26|2(0)|00:00:01|
---------------------------------------------------------------------------
1-access("EMPNO">=7902)
Note
-----
-dynamicsamplingusedforthisstatement
0recursivecalls
2consistentgets
569bytessentviasql*Net 492bytesreceivedviasql*Net 2sql*Netroundtrips 2
3）fullscan--索引全局扫描
indexscansareonlyavailableintheCBOasotherwiseweareunabletodeterminewhetherafullscanwouldbeagoodideaornot.WechooseanFullScanwhenwehavestatisticsthatindicatethatitisgoingtobemoreefficientthanatable
scanandasort.Forexamplewemaydoaindexscanwhenwedoanunboundedscanofanindexandwantthedatatobeorderedorder.
orderbyempno;
14 Planhashvalue:179099197
SELECTSTATEMENT||14|182|2(0)|00:00:01|
|1|FULLSCAN|PK_EMP|14|182|2(0)|00:00:01|
Note
-----
-forthisstatement
Statistics
4recursivecalls
0dbblockgets
11consistentgets
0physicalreads
0redosize
676bytessentviasql*Nettoclient
492bytesreceivedviasql*Net 2sql*Netroundtrips 0sorts(memory)
0sorts(disk)
14rowsprocessed
</pre>
4）indexfast--索引快速全局扫描，不带orderby情况下常发生
Scansalltheblockindex,153); background-color:inherit; font-weight:bold">Rowsarenotreturnedinsortedorder,Introducedin7.3andrequiresV733_PLANS_ENABLED=TRUEandCBO,maybehintedusingINDEX_FFShint,usesmultiblocki/o,canbeexecutedinparallel,canbeusedtoaccesssecondcolumn
ofconcatenatedindexes.Thisisbecauseweareselectingallindex.
<prefromemp;
Planhashvalue:366039554
-------------------------------------------------------------------------------
SELECTSTATEMENT||14|182|2(0)|00:00:01|
INDEXFASTFULLSCAN|PK_EMP|14|182|2(0)|00:00:01|
-------------------------------------------------------------------------------
4recursivecalls
13consistentgets
676bytessentviasql*Net 5）indexskipscan--索引跳跃扫描，where条件列是非索引的前导列情况下常发生
Indexskipscanfindsrowseveniftheisnottheleadingofaconcatenatedindex.Itskipsthefirstcolumn(s)duringthesearch.
indexi_emponemp(empno,ename);
Indexcreated.
select/*+index_ss(empi_emp)*/jobwhereename='SMITH';
Planhashvalue:98078853
SELECTSTATEMENT||1|13|5(0)|00:00:01|
INDEXROWID|EMP|1|13|5(0)|00:00:01|
INDEXSKIPSCAN|I_EMP|1||4(0)|00:00:01|
2-access("ENAME"='SMITH')
filter("ENAME"='SMITH')
5recursivecalls
513bytessentviasql*Net 1
3.Rowid物理ID扫描
Thisisthequickestaccessmethodavailable.Oracleretrievesthespecifiedblockandextractstherowsitisinterestedin.
--Rowid扫描是最快的访问数据方式
whererowid='AAAjFUAAEAAABZ1AAM';
Planhashvalue:1116584662
-----------------------------------------------------------------------------------
SELECTSTATEMENT||1|99|1(0)|00:00:01|
BYUSERROWID|EMP|1|99|1(0)|00:00:01|
-----------------------------------------------------------------------------------
1consistentgets
983bytessentviasql*Net 1
b、运算符
1.sort--排序，很消耗资源
Thereareanumberofdifferentoperationsthatpromotesorts:
(1)byclauses(2)groupby(3)sortmergejoin–-这三个会产生排序运算
2.filter--过滤，如notin、min函数等容易产生
Hasanumberofdifferentmeanings,usedtoindicatepartitionelimination,mayalsoindicateanactualfilterstepwhereonerowsourceisfiltering,another,functionssuchminmayintroducefilterstepsintoqueryplans.
3.view--视图，大都由内联视图产生(可能深入到视图基表)
Whenaviewcannotbemergedintothemainqueryyouwilloftenseeaprojectionviewoperation.Thisindicatesthatthe'view'willbeselectedfromdirectlyasopposedtobeingbrokendownintojoinsonthebasetables.Anumberofconstructsmakeaview
nonmergeable.Inlineviewsarealsononmergeable.
selectename,totfromemp,(sum(empno)totbyempno)tmpwhereemp.empno=tmp.empno;
Planhashvalue:138960760
-----------------------------------------------------------------------------------------
SELECTSTATEMENT||14|644|4(25)|00:00:01|
|1|MERGEJOIN||14|644|4(25)|00:00:01|
|2|INDEXROWID|EMP|14|280|2(0)|00:00:01|
|3|FULLSCAN|PK_EMPNO|14||1(0)|00:00:01|
|*4|SORTJOIN||14|364|2(50)|00:00:01|
|5|VIEW||14|364|1(0)|00:00:01|
|6|HASHGROUPBY||14|182|1(0)|00:00:01|
|7|FULLSCAN|PK_EMPNO|14|182|1(0)|00:00:01|
-----------------------------------------------------------------------------------------
4-access("EMP"."EMPNO"="TMP"."EMPNO")
filter("EMP"."EMPNO"="TMP"."EMPNO")
43recursivecalls
61consistentgets
821bytessentviasql*Net 5sorts(memory)
4.partition--分区视图
Partitionviewsarealegacytechnologythatweresupercededbythepartitioningoption.Thissectionofthearticleisprovidedasreferenceforsuchlegacysystems.
3、让我们再看看统计信息部分
setautotracetraceonly;
selectcount(*) Planhashvalue:2083865914
-------------------------------------------------------------------
Rows|Cost(%cpu)|SELECTSTATEMENT||1|3(0)|00:00:01|
|1|SORTAGGREGATE||1|||
|2|FULL|EMP|14|3(0)|00:00:01|
5recursivecalls(归调用次数)
0dbblockgets(从磁盘上读取的块数,即通过update/delete/selectforupdate读的次数)
15consistentgets(从内存里读取的块数,即通过不带update的select读的次数)
0physicalreads(物理读—从磁盘读到数据块数量,一般来说是'consistentgets'+'dbblockgets')
size(重做数——执行sql的过程中，产生的重做日志的大小)
515bytessentviasql*Net 0sorts(memory)(在内存中发生的排序)
0sorts(disk)(在硬盘中发生的排序)
name="code"class="sql">说明：<prename="code"class="sql"><spanstyle="color:#CC0000;">Cost=（SingleblockI/Ocost+MultiblockI/Ocost+cpucost）/sreadtim</pre>
<tableclass=""border="0"cellpadding="0"cellspacing="0"height="331"width="611">
<colgroup><colwidth="90"><colwidth="257"><colwidth="461"></colgroup>
<tbody>
<trheight="38">
<td>
序号
</td>
<td>
列名
</td>
解释
</tr>
<trheight="56">
1
dbblockgets
从buffercache中读取的block的数量
</tr>
2
consistentgets
从buffercache中读取的undo数据的block的数量
<trheight="18">
3
physicalreads
从磁盘读取的block的数量
<trheight="56">
4
redosize
DML生成的redo的大小
5
sorts(memory)
在内存执行的排序量
6
sorts(disk)
在磁盘上执行的排序量
</tbody>
</table>
<pre></pre>
<pre></pre>