Oracle执行计划讲解复习课程.doc

资源ID：51380709 资源大小：252KB 全文页数：89页
资源格式： DOC 下载积分：20金币

快捷下载

会员登录下载

微信登录下载

三方登录下载：

微信扫一扫登录

下载资源需要20金币

邮箱/手机：
温馨提示：	快捷下载时，用户名和密码都是您填写的邮箱或者手机号，方便查询和重复下载（系统自动生成）。如填写123，账号就是123，密码也是123。
支付方式：
验证码：	换一换

账号：
密码：
验证码：	换一换
当日自动登录忘记密码？

友情提示

1、下载资料失败解决办法

2、PDF文件下载后，可能会被浏览器默认打开，此种情况可以点击浏览器菜单，保存网页到桌面，就可以正常下载了。

3、本站不支持迅雷下载，请使用电脑自带的IE浏览器，或者360浏览器、谷歌浏览器下载即可。

4、本站资源下载后的文档和图纸-无水印,预览文档经过压缩，下载后原文更清晰。

5、试题试卷类文档，如果标题没有明确说明有答案则都视为没有答案，请知晓。

网站客服

侵权投诉

Oracle执行计划讲解复习课程.doc

Good is good, but better carries it.精益求精，善益求善。Oracle执行计划讲解-Oracle执行计划讲解看懂Oracle执行计划是优化的第一步，让我们从下面的例子开始吧。下面为补充内容1、创建测试表 SQL> create table t as select 1 id,object_name from dba_objects; Table created SQL> update t set id=99 where rownum=1; 1 row updated SQL> commit; Commit complete SQL> create index t_ind on t(id); Index created oracle优化器：RBO和CBO两种，从oracle10g开始优化器已经抛弃了RBO，下面的列子说明CBO大概是怎样的SQL> select /*+dynamic_sampling(t 0) */* from t where id=1; 50819 rows selected. Execution Plan - Plan hash value: 1376202287 - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 195 | 15405 | 51 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 195 | 15405 | 51 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T_IND | 78 | | 50 (0)| 00:00:01 | - Predicate Information (identified by operation id): - 2 - access("ID"=1) 现象t表还没有被分析，提示/*+dynamic_sampling(t0)*/*的目的是让CBO无法通过动态采样获取表中的实际数据情况，此时CBO只能根据T表中非常有限的信息（比如表中的extents数量，数据块的数量）来猜测表中的数据。从结果中可以看到CBO猜出表中id=1的有195条，这个数值对于表的总数来说，是一个非常小的值，所以CBO选择了索引而不是全表扫描。而实际情况如下所示：SQL> select * from t where id=1 2 50819 rows selected. Execution Plan - Plan hash value: 1601196873 - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 49454 | 3815K| 67 (2)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T | 49454 | 3815K| 67 (2)| 00:00:01 | - Predicate Information (identified by operation id): - 1 - filter("ID"=1) 通过动态取样，CBO估算出行数为49454，非常接近于真实50820数目。选择了全表扫描。我们来收集一下统计信息SQL> exec dbms_stats.gather_table_stats(user,'t',cascade => true); SQL> select * from t where id=1; 50819 rows selected. Execution Plan - Plan hash value: 1601196873 - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 50815 | 1339K| 67 (2)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T | 50815 | 1339K| 67 (2)| 00:00:01 | - Predicate Information (identified by operation id): - 1 - filter("ID"=1) 现在扫描过的行数为50815。如果我们更新了所有的id为99看看。SQL> update t set id=99; 50820 rows updated SQL> select * from t where id=99; Execution Plan - Plan hash value: 1376202287 - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 1 | 27 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 27 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T_IND | 1 | | 1 (0)| 00:00:01 | - Predicate Information (identified by operation id): - 2 - access("ID"=99) 因为没有对表进行分析，所以表中的分析数据还是之前的信息，CBO并不知道。我们可以看出Rows值为1，也就是说CBO人为表T中的ID=99的值只有1条，所有选择仍然是索引。我们收集一把统计信息。 SQL> exec dbms_stats.gather_table_stats(user,'t',cascade => true); PL/SQL procedure successfully completed SQL> select * from t where id=99; 50820 rows selected. Execution Plan - Plan hash value: 1601196873 - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 50815 | 1339K| 67 (2)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T | 50815 | 1339K| 67 (2)| 00:00:01 | - Predicate Information (identified by operation id): - 1 - filter("ID"=99) 上面为补充内容，下面正式开始1、sql的执行计划创建测试表SQL> create table t1(id int,name varchar2(1000); Table created SQL> create table t2(id int,name varchar2(1000); Table created SQL> create index ind_t1 on t1(id); Index created SQL> create index ind_t2 on t2(id); Index created SQL> create index ind_t2_name on t2(name); Index created SQL> insert into t1 select a.OBJECT_ID,a.OBJECT_NAME from all_objects a; 50206 rows inserted SQL> insert into t2 select a.OBJECT_ID,a.OBJECT_NAME from all_objects a where rownum<=20; 20 rows inserted SQL> commit; Commit complete SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade => true); PL/SQL procedure successfully completed SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade => true); PL/SQL procedure successfully completed 2、产生执行计划SQL> select * from t1,t2 where t1.id= t2.id; 20 rows selected. Execution Plan - Plan hash value: 828990364 - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 20 | 780 | 43 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 28 | 2 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 20 | 780 | 43 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | T2 | 20 | 220 | 3 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | IND_T1 | 1 | | 1 (0)| 00:00:01 | - Predicate Information (identified by operation id): - 4 - access("T1"."ID"="T2"."ID") Statistics - 1 recursive calls 0 db block gets 37 consistent gets 0 physical reads 0 redo size 1452 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 20 rows processed 看执行计划时，我们首先从缩进最大的行读取，它是最先被执行的步骤。在执行计划中：id=3和id=4是最先被执行的，| 3 | TABLE ACCESS FULL | T2 | 20 | 220 | 3 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | IND_T1 | 1 | | 1 (0)| 00:00:01 | 两行缩进一样的，最上面的最先被执行，在这里就是id=3| 3 | TABLE ACCESS FULL | T2 | 20 | 220 | 3 (0)| 00:00:01 | 选择次之缩进的行数id=2，表连接方式为NESTEDLOOPS。| 2 | NESTED LOOPS | | 20 | 780 | 43 (0)| 00:00:01 | 然后是id=1，扫描表的方式为TABLEACCESSBYINDEXROWID| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 28 | 2 (0)| 00:00:01 | 最后是id=0| 0 | SELECT STATEMENT | | 20 | 780 | 43 (0)| 00:00:01 | 我们翻译成语言大概如下，从t2表第一行读取，查看每一行是否符合下面条件： "T1"."ID"="T2"."ID" 如果符合就拿出一行来，扫描整个t2表，这个过程就叫NESTEDLOOPS 当整个t2表被扫描完之后，会产生一个结果集，这个结果集是IND_T1的一个索引集，然后oracle根据索引键值上的rowid去T1表中找到相应的记录，就是这一步：TABLEACCESSBYINDEXROWID 然后将结果返回：SELECTSTATEMENT 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估算当前操作的时间。PredicateInformation(identifiedbyoperationid):- 4-access("T1"."ID"="T2"."ID")这里有access和filter区别，access就表示这个谓词的条件的值将会影响数据的访问路径（一般针对索引），filter只起过滤作用。举个例子SQL> select * from t1 where t1.name='AA' no rows selected Execution Plan - Plan hash value: 3617692013 - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 2 | 56 | 69 (2)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 2 | 56 | 69 (2)| 00:00:01 | - Predicate Information (identified by operation id): - 1 - filter("T1"."NAME"='AA') 懂了吧。下面我们来仔细分析Operation里面的内容<pre name="code" class="sql">a、表访问方式1.Full Table Scan (FTS) 全表扫描 In a FTS operation, the whole table is read up to the high water mark (HWM). The HWM marks the last block in the table that has ever had data written to it. If you have deleted all the rows then you will still read up to the HWM. Truncate resets the HWM back to the start of the table. FTS uses multiblock i/o to read the blocks from disk. -全表扫描模式下会读数据到表的高水位线（HWM即表示表曾经扩展的最后一个数据块），读取速度依赖于Oracle初始化参db_block_multiblock_read_count(我觉得应该这样翻译:FTS扫描会使表使用上升到高水位(HWM),HWM标识了表最后写入数据的块,如果你用DELETE删除了所有的数据表仍然处于高水位(HWM),只有用TRUNCATE才能使表回归,FTS使用多IO从磁盘读取数据块).Query Plan - SELECT STATEMENT CHOOSE Cost=1 *INDEX UNIQUE SCAN EMP_I1 -如果索引里就找到了所要的数据，就不会再去访问表 2.Index Lookup 索引扫描 There are 5 methods of index lookup: 1）index unique scan -索引唯一扫描 Method for looking up a single key value via a unique index. always returns a single value, You must supply AT LEAST the leading column of the index to access data via the index. eg:SQL> select empno,ename from emp where empno=10;<pre name="code" class="sql">SQL> select empno,ename from emp where empno=10; no rows selected Execution Plan - Plan hash value: 2949544139 - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 | - Predicate Information (identified by operation id): - 2 - access("EMPNO"=10) Statistics - 24 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 385 bytes sent via SQL*Net to client 481 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed 2）indexrangescan -索引局部扫描Indexrangescanisamethodforaccessingarangevaluesofaparticularcolumn.ATLEASTtheleadingcolumnoftheindexmustbesuppliedtoaccessdataviatheindex.Canbeusedforrangeoperations(e.g.><<>>=<=between).SQL> select empno from emp where EMPNO>=7902; Execution Plan - Plan hash value: 1567865628 - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 2 | 26 | 2 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| PK_EMP | 2 | 26 | 2 (0)| 00:00:01 | - Predicate Information (identified by operation id): - 1 - access("EMPNO">=7902) Note - - dynamic sampling used for this statement Statistics - 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 569 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed 3）indexfullscan -索引全局扫描FullindexscansareonlyavailableintheCBOasotherwiseweareunabletodeterminewhetherafullscanwouldbeagoodideaornot.WechooseanindexFullScanwhenwehavestatisticsthatindicatethatitisgoingtobemoreefficientthanaFulltablescanandasort.ForexamplewemaydoaFullindexscanwhenwedoanunboundedscanofanindexandwantthedatatobeorderedintheindexorder.

注意事项

本文（Oracle执行计划讲解复习课程.doc）为本站会员（1595****071）主动上传，淘文阁 - 分享文档赚钱的网站仅提供信息存储空间，仅对用户上传内容的表现方式做保护处理，对上载内容本身不做任何修改或编辑。若此文所含内容侵犯了您的版权或隐私，请立即通知淘文阁 - 分享文档赚钱的网站（点击联系客服），我们立即给予删除！

温馨提示：如果因为网速或其他原因下载失败请重新下载，重复下载不扣分。