关于如何理解Explain Plan的输出
2024-07-21 02:36:10
供稿：网友
关于怎样解释EXPlain的输出曾经一直是一个困扰我的问题，后来我在Metalink上找到这篇文章，顿时豁然开朗。
希望有同样问题的同志能从这篇文章有所收获，曾经想翻译成中文，但实在没有时间，有心的同志可以试试。
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
-----------------------------------------------------
