Oracle执行计划讲解【精品文档】如有侵权,请联系网站删除,仅供学习与交流.....精品文档......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> comm...
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;
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.SQL> select empno from emp order by empno; 14 rows selected. Execution Plan Plan hash value: 179099197 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | 14 | 182 | 2 (0)| 00:00:01 | | 1 | INDEX FULL SCAN | PK_EMP | 14 | 182 | 2 (0)| 00:00:01 | Note - dynamic sampling used for this statement Statistics 4 recursive calls 0 db block gets 11 consistent gets 0 physical reads 0 redo size 676 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) 14 rows processed 4)indexfastfullscan --索引快速全局扫描,不带orderby情况下常发生Scansalltheblockintheindex,Rowsarenotreturnedinsortedorder,Introducedin7.3andrequiresV733_PLANS_ENABLED=TRUEandCBO,maybehintedusingINDEX_FFShint,usesmultiblocki/o,canbeexecutedinparallel,canbeusedtoaccesssecondcolumnofconcatenatedindexes.Thisisbecauseweareselectingalloftheindex.SQL> select empno from emp; 14 rows selected. Execution Plan Plan hash value: 366039554 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | 14 | 182 | 2 (0)| 00:00:01 | | 1 | INDEX FAST FULL SCAN| PK_EMP | 14 | 182 | 2 (0)| 00:00:01 | Note - dynamic sampling used for this statement Statistics 4 recursive calls 0 db block gets 13 consistent gets 0 physical reads 0 redo size 676 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) 14 rows processed 5)indexskipscan --索引跳跃扫描,where条件列是非索引的前导列情况下常发生Indexskipscanfindsrowsevenifthecolumnisnottheleadingcolumnofaconcatenatedindex.Itskipsthefirstcolumn(s)duringthesearch.SQL> create index i_emp on emp(empno, ename); Index created. SQL> select /*+ index_ss(emp i_emp)*/ job from emp where ename='SMITH'; Execution Plan Plan hash value: 98078853 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | 1 | 13 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 13 | 5 (0)| 00:00:01 | |* 2 | INDEX SKIP SCAN | I_EMP | 1 | | 4 (0)| 00:00:01 | Predicate Information (identified by operation id): 2 - access("ENAME"='SMITH') filter("ENAME"='SMITH') Note - dynamic sampling used for this statement Statistics 5 recursive calls 0 db block gets 11 consistent gets 0 physical reads 0 redo size 513 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) 1 rows processed 3.Rowid物理ID扫描Thisisthequickestaccessmethodavailable.Oracleretrievesthespecifiedblockandextractstherowsitisinterestedin. --Rowid扫描是最快的访问数据方式SQL> select * from emp where rowid='AAAjFUAAEAAABZ1AAM'; Execution Plan Plan hash value: 1116584662 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | 1 | 99 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY USER ROWID| EMP | 1 | 99 | 1 (0)| 00:00:01 | Statistics 1 recursive calls 0 db block gets 1 consistent gets 0 physical reads 0 redo size 983 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) 1 rows processed b、运算符1.sort --排序,很消耗资源Thereareanumberofdifferentoperationsthatpromotesorts:(1)orderbyclauses(2)groupby(3)sortmergejoin–-这三个会产生排序运算 2.filter --过滤,如notin、min函数等容易产生Hasanumberofdifferentmeanings,usedtoindicatepartitionelimination,mayalsoindicateanactualfilterstepwhereonerowsourceisfiltering,another,functionssuchasminmayintroducefilterstepsintoqueryplans. 3.view --视图,大都由内联视图产生(可能深入到视图基表)Whenaviewcannotbemergedintothemainqueryyouwilloftenseeaprojectionviewoperation.Thisindicatesthatthe'view'willbeselectedfromdirectlyasopposedtobeingbrokendownintojoinsonthebasetables.Anumberofconstructsmakeaviewnonmergeable.Inlineviewsarealsononmergeable.SQL> select ename,tot from emp,(select empno,sum(empno) tot from emp group by empno) tmp where emp.empno = tmp.empno; 14 rows selected. Execution Plan Plan hash value: 138960760 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | 14 | 644 | 4 (25)| 00:00:01 | | 1 | MERGE JOIN | | 14 | 644 | 4 (25)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 280 | 2 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | PK_EMPNO | 14 | | 1 (0)| 00:00:01 | |* 4 | SORT JOIN | | 14 | 364 | 2 (50)| 00:00:01 | | 5 | VIEW | | 14 | 364 | 1 (0)| 00:00:01 | | 6 | HASH GROUP BY | | 14 | 182 | 1 (0)| 00:00:01 | | 7 | INDEX FULL SCAN | PK_EMPNO | 14 | 182 | 1 (0)| 00:00:01 | Predicate Information (identified by operation id): 4 - access("EMP"."EMPNO"="TMP"."EMPNO") filter("EMP"."EMPNO"="TMP"."EMPNO") Note - dynamic sampling used for this statement Statistics 43 recursive calls 0 db block gets 61 consistent gets 0 physical reads 0 redo size 821 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 14 rows processed 4.partitionview --分区视图Partitionviewsarealegacytechnologythatweresupercededbythepartitioningoption.Thissectionofthearticleisprovidedasreferenceforsuchlegacysystems.3、让我们再看看统计信息部分SQL> set autotrace traceonly; SQL> select count(*) from emp; Execution Plan Plan hash value: 2083865914 | Id | Operation | Name | Rows | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| EMP | 14 | 3 (0)| 00:00:01 | Note - dynamic sampling used for this statement Statistics 5 recursive calls (归调用次数) 0 db block gets (从磁盘上读取的块数,即通过update/delete/select for update读的次数) 15 consistent gets (从内存里读取的块数,即通过不带for update的select 读的次数) 0 physical reads (物理读—从磁盘读到数据块数量,一般来说是'consistent gets' + 'db block gets') 0 redo size (重做数——执行SQL的过程中,产生的重做日志的大小) 515 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) (在硬盘中发生的排序) 1 rows processed说明:Cost=(Single block I/O cost+ Multiblock I/O cost+ CPU cost)/sreadtim 序号列名解释1dbblockgets从buffercache中读取的block的数量2consistentgets从buffercache中读取的undo数据的block的数量3physicalreads从磁盘读取的block的数量4redosizeDML生成的redo的大小5sorts(memory)在内存执行的排序量6sorts(disk)在磁盘上执行的排序量现在我们讲讲oracle执行计划里面每个参数的含义我们以下面的一个例子来讲解这里做个补充:trace的类型一共有以下几种序号命令解释1SET AUTOTRACE OFF此为默认值,即关闭Autotrace 2SET AUTOTRACE ON EXPLAIN只显示执行计划3SET AUTOTRACE ON STATISTICS 只显示执行的统计信息4SET AUTOTRACE ON 包含2,3两项内容5SET AUTOTRACE TRACEONLY 与ON相似,但不显示语句的执行结果 我喜欢SET AUTOTRACE TRACEONLY,我们以后的例子都是基于这种方式的SQL> select * from departments a where a.department_id in (select b.department_id from employees b where b.employee_id=205); Execution Plan Plan hash value: 2782876085 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | 1 | 27 | 2 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 27 | 2 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 7 | 1 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 540 | 1 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 | Predicate Information (identified by operation id): 3 - access("B"."EMPLO