基于Oracle的SQL优化（崔华著）-整理笔记-第2章“Oracle里的执行计划”

详细介绍了Oracle数据里与执行计划有关的各个方面的内容，包括执行计划的含义，加何查看执行计划，如何得到目标SQL真实的执行计划，如何查看执行计划的执行顺序，Oracle数据库里各种常见的执行计划的含义，以及如何在Oracle数据库中稳定执行计划。

 

2.1什么是执行计划

    为了执行SQL语句，Oracle在内部必须实现许多步骤，这些步骤可能是从数据库中物理检索数据行，或者用某种方法来准备数据行等，接着Oracle会按照一定的顺序依次执行这些步骤，最后将其执行结果作为目标SQL的最终执行结果返回给用户。Oracle用来执行目标SQL语句的这些步骤的组合就被称为执行计划。

我们来看_AI Oracle数据库中执行计划的实例 。

 

 

 

上述执行计划是在执行目标SQL (select／++real_exp_examplel */tl.coll,tl.c012,t2.c013 from tl,t2 where  tl.c012=t2.c012)后使用DBMS_XPLAN包中的方法DISPLAY_CURSOR得到的，这是目标SQL的真实执行计划。

从显示结果中我们可以看到，上述SQL的执行计划可以分为如下三个部分。

 

1．目标SQL的正文、SQL_ID和其执行计划所对应的PLAN HASH VALUE

    可以着到该SQL的

SQL ID		6fc6zasdtltr7
child number		0
SQL正文		“select／4+real_exp_examplel */tl.coll,tl.c012,t2.c013 from tl,t2 where tl.c012=t2.c012”
执行计划所对应的Plan Hash Value		282751716

 

 

2．执行计划的主体部分

    从其中我们可以看到Oracle在执行目标SQL时所用的内部执行步骤，这些步骤的执行顺序，所对应的谓

词信息、列信息，优化器评估出来执行这些步骤后返回结果集的Cardinality、成本等内容。

 

 

 

 

 

我们可以看到，Oracle在执行目标SQL时使用了对表Tl和T2的哈希连接，CBO评估出来上述哈希连接

后返回结果集的Cardinality(ROWS)的值是4，成本值(COST)为31。

执行计划中的“Query Block Name”和“Outline Data”部分是CBO在执行SQL时所用到的Query Block的名称和用于固定执行计划的内部Hint组合。实际上你可以将“Outline Data”部分的内容摘出来加到目标SQL中以固定其执行计划，即可以将目标SQL改写成如下的形式：

select /*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

            OPTIMIZER_FEATURES_ENABLE ( '10 . 2 . O . 5 ' )

ALL_ROWS

         OUTLIbfE_LEAF (@"SEL$1")

    FULL(@"SEL$1" "T2"@"SEL$l")

         FULL (@"SEL$1"  "Tl"@"SEL$l")

         LEADING (@' SEL$l"  "T2"@"SEL$l"  "Tl"@"SEL$l")

   USE_HASH(@"SEL$1" "Tl"@"SEL$l")

END_OUTLINE_DATA

 */

tl.coll, tl.c012, t2.c013

from tl, t2

where tl.c012 - t2.c012

 

    洼意，在上述执行计划中id 为1的执行步骤“HASH JOIN”前有一个星号（Id列的数字l前有一个“+”），

表示该执行步骤有对应的驱动或者过滤查询条件，这个星号所对应的具体的驱动或过滤查询条件可以从执行计

划的“Predicate Information (identified by operatlon id)”中找到。实际上，这部分的内容就是上述执行步骤所对

应的谓词信息：

Predicate Information  (identified by operation id):

    1  - access（"T1"."COL2”= "T2"．"COLZ"）

    我们可以看到，“access("T1"."COL2”= "T2"．"COLZ")”前的数字也是l，这里关键字“access”表示驱动查

词条件，这就表明"T1"."COL2”= "T2"."COLZ"就是上述执行计划中Id为1的执行步骤“HASH JOIN”所对应的

驱动查询条件。

3．执行计划的额外补充信息

    从其中我们可以看到Oracle在执行目标SQL时有没有使用一些额外的技术手段，比如是否使用了动态采

样(dynamic sampling)，是否使用Cardinality Feedback（Oracle llg中引入的修正执行计划中返回结果集的

Cardinality的一种技术下段），是否使用SQL Profile（Oracle lOg中引入的调整、稳定执行计划的一种方法）等。

    这部分额外的补充信息可以从执行计划中的“Note”部分看到。本例的执行计划中“Note”部分的内容如

下所示：

    Note

   - - - - -

      - dynamic sampling used for this statement

我们可以看到Oracle在执行目标SQL时使用了动态采样。

 

以下是使用Cardinality Feedback后执行计划中的“Note”部分显示的内容：

 

 

2.2如何查看执行计划

在Oracle数据库里，我们通常可以使用如下这些方法（包括但不限于）得到目标SQL的执行计划：

(1) explain plan命令

(2) DBMS_XPLAN包

(3) SQLPLUS中的AUTOTRACE开关

(4) 10046事件

(5) 10053事件

(6) AWR报告或Statspack报告

(7)一些现成的脚本（如display_cursor_9i.sql等）

 

2.2.1 PLSQL中的 F5键 只是对  explain 、select * from table(dbms_xplan)的一层封装

2.2.2   DBMS_XPLAN包

    使用DBMS XPLAN包中的方法是在Oracle数据库中得到目标SQL的执行计划的第二种方法。针对不同

的应用场景，你可以选择如下四种方法中的一种：

    1.  select * from table (dbms_xplan.display);

 

2.  select * from  table (dbms_xplan.display_cursor(null,null, 'advanced')); 方法2用于在SQL_PLUS中查看刚刚执行过的SQL的执行计划(太被动了)。这里针对方法DBMS_XPLAN.DISPLAY_CURSOR所传入的第一个和第二个参数的值均为null，第三个参数的值是“advanced”，第三个输入参数的值也可以是“all”，只不过用“advanced”后的显示结果会 比用“all”的显示结果更详细一些。 在SQL执行完毕后紧跟着执行“ select * from table(dbms_xplan.display_cursor(null，null，'advanced' ))”，就可以查看该SQL的实际执行计划了：

 

 

 

3.  select * from table (dbms_xplan.display_cursor('sql_id/hash_value',  child_cursor_number,'advanced'))； 方法3用于查看指定SQL的执行计划。 这里针对方法DBMS__ XPLAN.DISPLAY_CURSOR所传入的第一个参数的值是指定SQL的SQL ID或者SQL HASH VALUE，第二个参数的值是要查看的执行计划所在的 Child Cursor Number，第三个参数的含义在介绍方法2时已经提过，这个参数的值我们一般都用“advanced”，因为这样展现出来的执行计划是最详细的。   本质上SQL ID和SQL HASH VALUE是一回事，它们是可以互相转换的，这也是方法 BMS_XPLAN.DISPLAY CURSOR所传入的第一个参数的值可以是SQL ID，也可以是SQL HASH VALUE SQL> select   lower(trim(13yfu3wh150aqt!))sql id, trunc(mod (sum((instr(10123456789abedfghikmnpqrstuvwxyz'            niintgsett)),level,1))-1)*power(32,1ength(trin( 3yfuswhis0asth   nnntsubstr(lower(trim('3yfu3whis0aqt')),level,1))-1)'pove    nint-level)),power(2,32)))hash value  from dual SeleCt*fromtable(dbms_xplan·display--Cursor('3yfu3whl50aqt',0,'adVanCed')); PLANTABLEOU丁PUT 3Q乙＿zD3yfu3材址50aqt,c城ldn皿bero 日。l.ctempno,.name,dnamefrom,cott.emp,3cott.d.ptwhere e爪p.deptno=dept.deptno Planha,hvalue:8咬4388907 工d operation N已垃e RO材， Byte, Co,t（协CPU)1Ti花e 11111 000OD ...．…… 00000 00000 ...．…… 00000 00000 飞．夕飞．了飞．了飞．了飞．夕飞．了 070080 0户吸、J‘、了‘、夕．、矛吸、 1 才．飞 662143 1111 O}3ELECT3T人TE加ENT 11MERGEJOIN 2}TABLEACCE33BYINDEX 3}INDEXFULLSCAN 吸！gORTJOIN 5}TASLEACCE33FULL D名PT pKDEPT 378 52 EHP 196 196 44444 111 去 山口口口口口口口口口口口口口口口口口口口口口口口口口口口自口口口口口 QueryBlockName/objeot人工ia二（identifiedbyoperationid): 1一3EL车1 2一3EL车1/DEPT日SEL与1 3一3EL$1/DEPT日SEL$1 5一SEL导1／包MPe3ELSI OutlineData" width="576" height="315">

 

4.  select * from table (dbms_xplan.display_awr('sql_id')); 方法4用于查看指定SQL的所有历史执行计划。   使用方法2、3能够显示目标SQL执行计划的前提条件是该SQL的执行计划还在Shared Pool中，而如果该SQL的执行计划已经被age out出Shared Pool，那么只要该SQL的执行计划被Oracle采集到AWR Repository中，我们就可以使用方法4来查看该SQL的所有历史执行计划。     我们来看一个用方法4来查看指定SQL所有历史执行计划的实例。从如下查询结果可以看出，目标SQL现在的SQL_ID是“79glp919t7x4u”。注意，该SQL对应的列VERSION COUNT的值为2，这说明该SQL有两个Child Cursor，即该SQL很可能有两个不同的执行计划。     SQL>  select   *  from  table (dbms_xplan.display_cursor ( ' 79glp919t7x4u' , 1, 'advanced' J ) ;   PLAN TABLE_OUTPUT --------------------------------------------------------------- soL_ID: 7991p919t7x4u, child number: 1 cannot be found       由于之前我们已经通过手工采集AWR报告的方式将目标SQL的执行计划采集到了AWR Repository中，所以现在我们可以通过执行方法4（即执行DBMS XPLAN.DISPLAY AWR）来得到该SQL的所有历史执行计划 用  DBMS_ XPLAN．DISPLAY_AWR和之前用 DBMS_XPLAN.DISPLAY__ CURSOR显示的执行计划相比，有一个非常不好的地方一一就是用 DBMS__ XPLAN.DISPLAY_AWR显示的执行计划中看不到执行步骤对应的谓词条件，我个人认为这很糟糕！因为谓词条件对于我们理解执行计划，特别是理解复杂执行计划是不可或缺的。这里的根本原因是Oracle在把执行计划的采样数据从V$SQL_PLAN挪到AWR Repository的基表WRH$_SQL_PLAN中时没有保留V$SQL_PLAN中记录谓词条件（包括驱动查询条件和过滤查询条件）的列ACCESS—PREDICATES和FILTER PREDICATES的值，所以不是DBMS XPLAN.DISPLAY__ CURSOR不想显示谓词条件，而是根本就没有谓词条件可供其显示。

 

2,2.4 1 0046事件与tkprof命令

    使用10046事件是在Oracle数据库中查看目标SQL的执行计划的另一种方法。这种方法与使用explain plan命令、DBMS_XPLAN包和AUTOTRACE开关的不同之处在于，所得到的执行计划中明确显示了目标SQL实际执行计划中每一个执行步骤所消耗的逻辑读、物理读和花费的时间。这种细粒度的明细显示在我们诊断复杂SQL的性能问题时尤为有用，而且这也是其他三种方法所不能提供的（实际上，用

GATHER_PLAN_STATISTICS Hint配合DBMS_XPLAN包一起使用也可以达到类似10046事件这种绌粒度的明细显示效果，对于GATHER_PLAN_STATISTICS Hint的用法，会在“第6章Oracle里的Hint”中详细说明，这里不再赘述）。

 

    用10046事件得到目标SQL的执行计划是很容易的，只需要依次执行如下三个步骤：

·首先在当前Session中激活10046事件：     ·接着在此Session中执行目标SQL;     ·最后在此Session中关闭10046事件。

当执行完上述步骤后，Oracle就会将目标SQL的执行计划和明细资源消耗写入此Session所对应的trace文件中，查看这个trace文件就能知道目标SQL的执行计划和资源消耗明细了。Oracle会在参数

USER。DUMP—DEST所代表的目录下生成这个trace文件，其命名格式为“实例名ora当前Session的spid.trc”，例如ipratest_ora_856454．trc。

    通常可以使用如下这两种方法在当前Session中激活10046事件：

·在当前session中执行alter session set events '10046 trace name context forever,level 12

·在当前session中执行oradebug event 10046 trace name context forever,level 12

    上述命令中的关键字“level”后的数字是表示设置的10046事件的level值。这个值是可以修改的，我们通常使用的值为12，表示在产生的trace文件中除了有目标SQL的执行计划和资源消耗明细之外，还会包含目标SQL所使用的绑定变量的值以及陔Session所经历的等待事件。除了上述level值之外，其他的部分都是固定的语法，我们无法修改。

 

 

 

 

2.3如何得到真实的执行计划

    在2.2节中介绍了在Oracle数据库里通常可以使用如下这四种方法来得到目标SQL的执行计划：

    ‘explain plan命令

    ·DBMS_ XPLAN包

    ·SQLPLUS中的AUTOTRACE开关

    ·10046事件

    这其中除了第四种方法之外，其他三种方法得到的执行计划都有可能是不准确的。在Oracle数据库中判断得到的执行计划是否准确，就是看目标SQL是否被真正执行，真正执行过的SQL所对应的执行计划就是准的，反之则有可能不准。注意，这里的判断原则从严格意义上来说并不适用于AUTOTRACE开关，因为所有使用AUTOTRACE开关所显示的执行计划都有可能是不准的，即使对应的目标SQL实际上已经执行过，这一点在后面会详细解释。

 

    下画我们就用上述原则来判断除第4种以外的其他三种方法中哪些方法得到的执行计划是准的，哪些方法得到的执行计划有可能不准。

 

    对使用第一种方法（即使用explain plan命令）得到的执行计划而言，因为此时目标SQL并没有被实际执行，所以用该方法得到的执行计划有可能是不准的，尤其是在目标SQL包含绑定变量时。在默认丌启绑定变量窥探(Bind Peeking)的情况下，对含绑定变量的目标SQL使用explain plan得到的执行计划只是一个半成品，Oracle在随后对该SQL的绑定变量进行窥探后就得到了这些绑定变量具体的值，此时Oracle很可能会对上述半成品的执行计划做调整，一旦做了调整，使用explain plan命令得到的执行计划就不准了（关于绑定变量和绑定变量窥探，会在“第3章Oracle里的Cursor和绑定变量”中详细介绍，这里不再赘述）。

    对使用第二种方法（即使用DBMS__ XPLAN包）而言，针对不同的应用场景，你可以选择如下四种方式中的一种：

    select *  from table (dbms_xplan.display);

    select *  from table(dbms_xplan.display_cursor(null, null， ‘advanced'));

    select  *    from  table (dbms_xplan.display_cursor(’sql_id/hash_value’,  child_cursor_number,’advanced'))；

    select *  from table (dbms_xplan.display_awr(’sql_id‘));

 

    显然，执行select} from table(dbms_xplan.display)所得到的执行计划可能是不准的，因为它只是用于查看使用explain plan命令得到的目标SQL的执行计划，目标SQL此时还没有被真正执行，所以用它得到的执行计划可能是不准的。使用利下的三种方式所得到的执行计划都是准的，因为此时目标SQL都已经被实际执行过了。

    对使用第三种方法（即使用SQLPLUS中的AUTOTRACE开关）而言，你可以选择执行如下三种方式中的一种来开启AUTOTRACE开关：

 

SET AUTOTRACE ON（可以简写为SET  AUTOT  ON）．

SET AUTOTRACE TRACEONLY（可以简写为SET AUTOT  TRACE）：

SET AUTOTRACE TRACEONLY EXPLAIN(可以简写为SET AUTOT TRACE EXP);  (慎用，因为select操作并没有被执行、DML语句才被真正执行了 )

 

    上述三种方式中，当使用SET AUTOTRACE ON和SET AUTOTRACE TRACEONLY时，目标SQL都已经被实际执行过了，正是因为被实际执行过，所以在SET AUTOTRACE ON和SET AUTOTRACE TRACEONLY的情况下我们能看到目标SQL的实际资源消耗情况。当使用SET AUTOTRACE TRACEONLY EXPLAIN时，如果执行的是SELECT语句，则该SELECT语句并没有被Oracle实际执行，但如果执行的是DML语句，情况就不一样了，此时的DML语句是会被Oracle实际执行的。

 

 

soL> set autotrace traceonly explain soL> select count(*) from emp where ename='CUIHUAl'; 执行计划     煞后再次查询V$SQLAREA: soL> selec七 sql_text, executions from v$sqlarea where sql_text like  ' select count (*)  from empoo ';   SQL TEXT                                                                                                    EXECUTIONS ------------------------------------------------------------                       ------------------------------------------------ select count(*) from emp where ename=,CUIHUAll'                                                   O       从上述查询结果中可以看到该SELECT语句所对应的EXECUTIONS的值为O，这说明Oracle刚才确实只是解析了该SELECT句但并没有实际执行它。即我们之前的论点（当使用SET AUTOTRACE TRACEONLY  EXPLAIN时，如果执行的是SELECT语句，则该SELECT语句并没有被Oracle实际执行）得到了证实。   虽然使用部分SET AUTOTRACE命令后目标SQL实际上已经执行过了，但所 有使用SET AUTOTRACE命令(包括SET AUTOTRACE ON、SET AUTOTRACE TRACEONLY、SET AUTOTRACE TRACEONLY EXPLAIN)断得到的执行计划都有可能是不准的，因为使用SET AUTOTRACE 命令所显示的执行计划都是来源于调用explain plan命令。   请看以下case 1、执行测试sql语句： SELECT T1.*,T2.* FROM T_0504 T1,T_0504 T2;   2、找出该sql的 ASH_VALUE、CHILD_NUMBER select t1.SQL_TEXT,t1.HASH_VALUE,t1.CHILD_NUMBER from v$sql t1 where t1.SQL_TEXT like '%SELECT T1,*,T2.* FROM T_0504 T1,T_0504 T2%';   ------------------------------------------------------------------------------------------------------------------------------ SELECT T1,*,T2.* FROM T_0504 T1,T_0504 T2         4015614548        0   现在执行脚本display_cursor_9i.sql是可以得到上述SQL的真实执行计划的： SQL> @'E:\display_cursor_9i.sql' 4015614548  0       2.4如何查看执行计划的执行顷序 这些年来，我一直沿用如下的口诀来查看目标SQL执行计划的执行顺序：先从最开头一直连续往右看，直到看到最右边的并列的地方；对于不并列的，靠右的先执行；如果见到并列的，就从上往下看，对于并列的部分，靠上的先执行。   seleCt"fromtable(dbms_Xplan.displa光Cursor); PL人NT人BLEOUTPUT SQLID 01nxqd仰71址xs,childnumbero 3electcount（奋）fro琳（,electo.oatfna,o.aatprf,a.oat吏m,o·oattkt,o·oatcpn,o·oatp3t,,ub,tr(u·u ted二p,1,5) 0.aatcp下’ 二O。Oat七k七 a3 .rrn以，0.oatcdt,0.oatcidfro价。，batno,upltoru肺here(0.oatcer=’丫’and D'6nd 已nd u.utetkt=o.oattktandu.ro甘id=(selectmax(ro姗id)fro址uplter utefna==0.oatfna))or0.oatp．七声’D'oro·OatP，七二’I'OrO·OatpS七二’ 材he【已Ute七kt 护’) Planha,hvalue:2105702960 口口口口口口口口口口口口口口口口口口口口口口口 。peration 日ELECTST人T艺从ENT 3ORT人GGR月G人TE CONC人TEN人TION 从ERGEJOINC人RTE3工人N TABLEACCES3FULL BUFFERSORT TABLEACCEBSFULL PILTER N已刀比e RO脚3 syte, CO，七 3082 （愁CPU) Time (100) 1 54 41吸遨‘ 2111 …，．… 0000 0000 .．…… 00DD DDDO 电tJ．飞月刀飞．J电刀口 4533 夕．飞夕t飞矛f飞夕．飞 O脚B^TN UPLTER 47DK 1 316R 316K 24M 34 6175K '175K 1969 851 1118 1118 0123弓567" width="576" height="248">       疋确的执行顺序应该是： 先执行TABLE ACCESS FULL  I OWBATN l    l l    34 1  851  (5)[ 00:00:11 再执行TABLE ACCESS FULL  l UPLTER J  316KI 6175KI 1118  (3)1 00:00:14 最后执行BUFFER SORT  316KI   6175KI   1118     (3)1 00:00:14       这样我们就解析出来了上述执行计划中最内层三条语句的执行顺序，再应用口诀解析其外层语句，一层一层剥离，就很容易得到整个执行计划的执行顺序。这里把按照口诀解析出来的执行计划的执行顺序完整写出来，   如下所示。 第 1步:  TABLE ACCESS FULLI OWBATN I         I I       341     851     (5)1 00:00:11 第 2步:  TABLE ACCESS FULLI UPLTERI     316KI   6175KI    1118     (3)1 00:00:14 第 3步:  BUFFER SORT {        1     316KI   6175KI   1118     (3)1 00:00:14 笫 4步:  MERGE JOIN CARTESIAN I        I     470KI      24Ml   1969     (4)1 00:00:24 第 5步:  TABLE ACCESS FULLI OWBATN I 20956 1     695KI     852     (5)1 00:00:11 第 6步:   INDEX FAST FULL SCANI IDX_UPLTER_UTETKT I 316KI 6175KI246 (6)1 00:00:03 第 7步:  HASH JOrN I       I 37s22 [   1978K[   1109    (6)1 00:00:14 I 第 8步:   INDEX RANGE SCANI IDX_UPLTER_UTETKT I 2 1 1 3 (0)1 00:00:01  I 第 9步:   TABLE ACCESS BY INDEX ROMDI UPLTER I l I 38 14 (0)1 00:00:01 I 第 10步: SORTAGGREGATEI    I      11    381             I           I 第11步：FILTER l    l 第12步：CONCATENATION 第 13步: SORTAGGREGATE  I    I      11    541             I 第 14步:  SELECT STATEMENT    I    I          I          I   3082 (100)     如果以执行计划中执行步骤前的列Id的值来代表其执行顺序的话，则正确执行顺序（从左至右，依次先后执行）为：4-6-5-3-9-10-8-13-12-11-7-2-1-0。只要掌握了上述口诀，无论多复杂的执行计划都能准确无误地判断出其正确的执行顺序。     上述口诀虽然好用，但它的缺点是需要人眼来仔细分辨，这在面对夏杂的执行计划时会显得不那么直观，而且容易出错。这里提供一种直观的、一目了然的查看执行计划执行顺序的方法，即在目标数据库中安装并使用XPLAN包。XPLAN包其实是对DBMS__ XPLAN包的封装，使用XPLAN包就可以很清晰地看到执行计划中每一步的执行顺序。执行顺序在XPLAN包的显示结果中以列Order来显示，Order的值从1开始递增，表示执行顺序的先后。     XPLAN包的源代码在脚本xplan.sql中 安装完后你会发现XPLAN包里的方法只有三个，而且就是我们熟悉的DBMS XPLAN包中的同名方法： SQL> desc xplan; FUNCTION DISPLAY RETURNS XPLAN_NTT  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  P_TABLE_NAME                   VARCHAR2                IN     DEFAULT  P_STATEMENT_ID                 VARCHAR2                IN     DEFAULT  P_FORMAT                       VARCHAR2                IN     DEFAULT FUNCTION DISPLAY_CURSOR RETURNS XPLAN_NTT  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  P_SQL_ID                       VARCHAR2                IN     DEFAULT  P_CURSOR_CHILD_NO              NUMBER(38)              IN     DEFAULT  P_FORMAT                       VARCHAR2                IN     DEFAULT   2.5.2  与B树索引相关的执行计划     索引唯一扫描  ，关键字是“INDEX UNIQUE SCAN” 、 索引范围扫描  ，关键字是“INDEX RANGE SCAN”、 索引全扫描    ，关键字是“INDEX FULL SCAN”、 索引快速全扫描   ，关键字是“INDEX FAST FULL SCAN”、 索引跳跃式扫描   ，关键字是“INDEX SKIP SCAN”。   请看以下案例 1、创建测试表 t_0504    Create table t_0504 as select * from dba_objects;   2、执行sql语句 SQL> select * from t_0504 where object_id=3849;   OWNER                          OBJECT_NAME                                                                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE           CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- --------- --------- ------------------- ------- - - - ---------- ------------------------------ PUBLIC                         LOADER_TAB_INFO                                                                                                                                                       3849         SYNONYM      15-AUG-09 15-AUG-09 2009-08-15:00:18:24 VALID   N N N          1   3、查看执行计划 SQL> select plan_table_output from table(dbms_xplan.display_cursor(null,null,'all'));   PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID  47pk7dt3uvc3v, child number 0 ------------------------------------- select * from t_0504 where object_id=3849   Plan hash value: 2183564195   ---------------------------------------------------------------------------- | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |        |       |       |   290 (100)|          | |*  1 |  TABLE ACCESS FULL| T_0504 |     1 |    97 |   290   (1)| 00:00:04 | ---------------------------------------------------------------------------- 此时执行计划走的是 全表扫描   4、对字段object_id 创建 非唯一B树索引 SQL> create index idx_objid on t_0504(object_id);   5、再次执行sql SQL> select * from t_0504 where object_id=3849;   OWNER                          OBJECT_NAME                                                                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE           CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- --------- --------- ------------------- ------- - - - ---------- ------------------------------ PUBLIC                         LOADER_TAB_INFO                                                                                                                                                       3849         SYNONYM      15-AUG-09 15-AUG-09 2009-08-15:00:18:24 VALID   N N N          1   6、再次查看执行计划 SQL> select plan_table_output from table(dbms_xplan.display_cursor(null,null,'all'));   PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID  47pk7dt3uvc3v, child number 0 ------------------------------------- select * from t_0504 where object_id=3849   Plan hash value: 3536181773   ----------------------------------------------------------------------------------------- | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |           |       |       |     2 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID| T_0504    |     1 |    97 |     2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | IDX_OBJID |     1 |       |     1   (0)| 00:00:01 | ----------------------------------------------------------------------------------------- 此时sql的执行计划走的是对 索引 idx_objid的索引范围扫描   7、修改字段object_id not null SQL> alter table t_0504  modify object_id not null;   Table altered.   8、重新收集表的统计信息 SQL> exec dbms_stats.gather_table_stats(tabname=>'T_0504',ownname=>'OWNER');   PL/SQL procedure successfully completed.   9、执行sql SQL> select object_id  from t_0504 ；   10、再次查看sql的执行计划 SQL> select plan_table_output from table(dbms_xplan.display_cursor(null,null,'all'));   PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID  2f27v52fahj14, child number 0 ------------------------------------- select object_id from t_0504   Plan hash value: 3954131137   ---------------------------------------------------------------------------------- | Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------- |   0 | SELECT STATEMENT     |           |       |       |    45 (100)|          | |   1 |  INDEX FAST FULL SCAN| IDX_OBJID | 72517 |   354K|    45   (0)| 00:00:01 | ---------------------------------------------------------------------------------- 此时sql的执行计划走是对索引 objid 的索引快速全扫描     10、再次执行带有 hint的 sql     SQL> select /*+ index(t_0504 idx_objid */   object_id  from t_0504  order by object_id asc；   11、再次查看sql的执行计划 SQL> select plan_table_output from table(dbms_xplan.display_cursor(null,null,'all'));   PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID  17rrq8fvu09u4, child number 0 ------------------------------------- select /*+ index(t_0504 idx_objid */   object_id  from t_0504 order by object_id asc   Plan hash value: 2939063042   ------------------------------------------------------------------------------ | Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT |           |       |       |   162 (100)|          | |   1 |  INDEX FULL SCAN | IDX_OBJID | 72517 |   354K|   162   (0)| 00:00:02 | ------------------------------------------------------------------------------

 

 

转载于:https://www.cnblogs.com/iyoume2008/p/6838402.html