`
yanhail
  • 浏览: 11801 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
最近访客 更多访客>>
社区版块
存档分类
最新评论

什么是Explain Plan(英文)

阅读更多

Interpreting Explain plan 
  1. Background information 
  1.1 What's an explain plan? 
  ~~~~~~~~~~~~~~~~~~~~~~~ 
  An explain plan is a representation of the access path that is taken when a query is executed within Oracle. 
  
  Query processing can be divided into 7 phases: 
  [1] Syntactic - checks the syntax of the query 
  [2] Semantic - checks that all objects exist and are accessible 
  [3] View Merging - rewrites query as join on base tables as 
  opposed to using views 
  [4] Statement Transformation - rewrites query transforming some complex 
  constructs into simpler ones where 
  appropriate (e.g. subquery unnesting, in/or 
  transformation) 
  [5] Optimization - determines the optimal access path for the 
  query to take. With the Rule Based 
  Optimizer (RBO) it uses a set of heuristics 
  to determine access path. With the Cost 
  Based Optimizer (CBO) we use statistics 
  to analyze the relative costs of accessing 
  objects. 
  [6] QEP Generation 
  [7] QEP Execution 
  (QEP = Query Evaluation Plan) 
  
  Steps [1]-[6] are handled by the parser. 
  Step [7] is the execution of the statement. 
  
  The explain plan is produced by the parser. Once the access path has been decided upon it is stored in the library cache together with the statement itself. We store queries in the library cache based upon a hashed representation of that query. When looking for a statement in the library cache, we first apply a hashing algorithm to the statement and then we look for this hash value in the library cache. 
  This access path will be used until the query is reparsed. 
  
  1.2 Terminology 
  ~~~~~~~~~~~ 
  Row Source - a set of rows used in a query 
  may be a select from a base object or the result set returned by 
  joining 2 earlier row sources 
  Predicate - where clause of a query 
  Tuples - rows 
  Driving Table - This is the row source that we use to seed the query. 
  If this returns a lot of rows then this can have a negative 
  affect on all subsequent operations 
  Probed Table - This is the object we lookup data in after we have retrieved 
  relevant key data from the driving table. 
  
  1.3 How does Oracle access data? 
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
  At the physical level Oracle reads blocks of data. The smallest amount of data read is a single Oracle block, the largest is constrained by operating system limits (and multiblock i/o). 
  Logically Oracle finds the data to read by using the following methods: 
  Full Table Scan (FTS) 
  Index Lookup (unique & non-unique) 
  Rowid 
  
  1.4 Explain plan Hierarchy 
  ~~~~~~~~~~~~~~~~~~~~~~ 
  Simple explain plan: 
  
  Query Plan 
  ----------------------------------------- 
  SELECT STATEMENT [CHOOSE] Cost=1234 
  TABLE ACCESS FULL LARGE [:Q65001] [ANALYZED] 
  
  The rightmost uppermost operation of an explain plan is the first thing that the explain plan will execute. In this case TABLE ACCESS FULL LARGE is the first operation. This statement means we are doing a full table scan of table LARGE. When this operation completes then the resultant row source is passed up to the next level of the query for processing. In this case it is the SELECT STATEMENT which is the top of the query. 
  [CHOOSE] is an indication of the optimizer_goal for the query. This DOES NOT necessarily indicate that plan has actually used this goal. The only way to confirm this is to check the cost= part of the explain plan as well. For example the following query indicates that the CBO has been used because there is a cost in the cost field: 
  
  SELECT STATEMENT [CHOOSE] Cost=1234 
  
  However the explain plan below indicates the use of the RBO because the cost field is blank: 
  
  SELECT STATEMENT [CHOOSE] Cost= 
  
  The cost field is a comparative cost that is used internally to determine the best cost for particular plans. The costs of different statements are not really directly comparable. 
  [:Q65001] indicates that this particular part of the query is being executed in parallel. This number indicates that the operation will be processed by a parallel query slave as opposed to being executed serially. 
  [ANALYZED] indicates that the object in question has been analyzed and there are currently statistics available for the CBO to use. There is no indication of the 'level' of analysis done. 
  2. Access Methods in detail 
  2.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. Multiblock i/o is controlled by the parameter . This defaults to: 
  db_block_buffers / ( (PROCESSES+3) / 4 ) 
  Maximum values are OS dependant 
  Buffers from FTS operations are placed on the Least Recently Used (LRU) end of the buffer cache so will be quickly aged out. FTS is not recommended for large tables unless you are reading >5-10% of it (or so) or you intend to run in parallel. 
  
  Example FTS explain plan: 
  ~~~~~~~~~~~~~~~~~~~~~~~~ 
  SQL> explain plan for select * from dual; 
  Query Plan 
  ----------------------------------------- 
  SELECT STATEMENT [CHOOSE] Cost= 
  TABLE ACCESS FULL DUAL 
  
  
  2.2 Index lookup 
  ~~~~~~~~~~~~ 
  Data is accessed by looking up key values in an index and returning rowids. A rowid uniquely identifies an individual row in a particular data block. This block is read via single block i/o. 
  In this example an index is used to find the relevant row(s) and then the table is accessed to lookup the ename column (which is not included in the index): 
  
  SQL> explain plan for 
  select empno,ename from emp where empno=10; 
  Query Plan 
  ------------------------------------ 
  SELECT STATEMENT [CHOOSE] Cost=1 
  TABLE ACCESS BY ROWID EMP [ANALYZED] 
  INDEX UNIQUE SCAN EMP_I1 
  
  Notice the 'TABLE ACCESS BY ROWID' section. This indicates that the table data is not being accessed via a FTS operation but rather by a rowid lookup. In this case the rowid has been produced by looking up values in the index first. 
  The index is being accessed by an 'INDEX UNIQUE SCAN' operation. This is explained below. The index name in this case is EMP_I1. 
  If all the required data resides in the index then a table lookup may be unnecessary and all you will see is an index access with no table access. 
  In the following example all the columns (empno) are in the index. Notice that no table access takes place: 
  
  SQL> explain plan for 
  select empno from emp where empno=10; 
  Query Plan 
  ------------------------------------ 
  SELECT STATEMENT [CHOOSE] Cost=1 
  INDEX UNIQUE SCAN EMP_I1 
  
  Indexes are presorted so sorting may be unnecessary if the sort order required is the same as the index. 
  
  e.g. 
  
  SQL> explain plan for select empno,ename from emp 
  where empno > 7876 order by empno; 
  
  Query Plan 

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics