chap14_2.html

来自「Oracle资料大集合」· HTML 代码 · 共 592 行 · 第 1/2 页

<p>

The EXPLAIN PLAN statement stores its results in a table called PLAN_TABLE. This table must exist prior to EXPLAIN PLAN execution and fortunately Oracle provides a script file to create this table. The script抯 name is system-dependent; under Windows NT, it resides in the \ORANT\RDBMS80\ADMIN subdirectory. After invocation of EXPLAIN PLAN, query the PLAN_TABLE to reveal the execution of a particular SQL statement.

<p>

Table 14.3 shows the structure of PLAN_TABLE. The most important output of EXPLAIN PLAN resides in
PLAN_TABLE抯 OPERATION column, which reveals how Oracle has processed the statement on a physical level. The possible values of this attribute appear in Table 14.4.

<p>

Table 14.3 The structure of PLAN_TABLE

<p>
<table cellpadding=3 cellspacing=3 border=0 bgcolor="cdcdcd">
<TR><td>Column</td>
<td>Column Description</td></TR>

<TR><td>STATEMENT_ID</td>
<td>Statement ID (user-specified)</td></TR>

<TR><td>TIMESTAMP</td>
<td>Date and time of EXPLAIN PLAN execution</td></TR>

<TR><td>REMARKS</td>
<td>User-specified comments for EXPLAIN PLAN steps</td></TR>

<TR><td>OPERATION</td>
<td>Oracle-performed operation in this step</td></TR>

<TR><td>OPTIONS</td>
<td>More information on OPERATION</td></TR>

<TR><td>OBJECT_NODE</td>
<td>Database link name used to reference the object</td></TR>

<TR><td>OBJECT_OWNER</td>
<td>Username of schema owner containing the object</td></TR>

<TR><td>OBJECT_NAME</td>
<td>Table or index name</td></TR>

<TR><td>OBJECT_INSTANCE</td>
<td>Object抯 position</td></TR>

<TR><td>OBJECT_TYPE</td>
<td>Object descriptive information</td></TR>

<TR><td>OPTIMIZER</td>
<td>Optimizer mode in effect at execution time</td></TR>

<TR><td>SEARCH_COLUMNS</td>
<td>Not used</td></TR>

<TR><td>ID</td>
<td>Number of step in the execution plan</td></TR>

<TR><td>PARENT_ID</td>
<td>Number of the next step</td></TR>

<TR><td>POSITION</td>
<td>Processing order for each step with this PARENT_ID</td></TR>

<TR><td>OTHER</td>
<td>Other step-related information</td></TR>

<TR><td>OTHER_TAG</td>
<td>Further description of OTHER</td></TR>

<TR><td>PARTITION_START</td>
<td>Starting partition</td></TR>

<TR><td>PARTITION_STOP</td>
<td>Last partition accessed</td></TR>

<TR><td>PARTITION_ID</td>
<td>Number of step that computed PARTITION_START and PARTITION STOP</td></TR>

<TR><td>COST</td>
<td>If optimizer is in COST mode, this is the relative cost of the operation, and it is null if
optimizer is in rule mode</td></TR>

<TR><td>CARDINALITY</td>
<td>Estimated number of rows accessed by OPERATION</td></TR>

<TR><td>BYTES</td>
<td>Estimated number of bytes accessed by OPERATION</td></TR>
</table>

<p>

Table 14.4 Possible values of the OPERATION column in PLAN_TABLE

<p>

<table cellpadding=3 cellspacing=3 border=0 bgcolor="cdcdcd">
<TR><td>Operation</td>
<td>Option</td>
<td>Operation Description</td></TR>

<TR><td>AND-EQUAL</td>
<td>&nbsp;</td>
<td>Returns intersection of multiple ROWIDs without duplicates</td></TR>

<TR><td>CONNECT BY</td>
<td>&nbsp;</td>
<td>Tree walk (hierarchical access)</td></TR>

<TR><td>CONCATENATION</td>
<td>&nbsp;</td>
<td>UNION of multiple rows</td></TR>

<TR><td>COUNT</td>
<td>&nbsp;</td>
<td>Row count</td></TR>

<TR><td>COUNT</td>
<td>STOPKEY</td>
<td>Row count limited by WHERE clause ROWNUM expression</td></TR>

<TR><td>FILTER</td>
<td>&nbsp;</td>
<td>Row elimination based on a WHERE clause</td></TR>

<TR><td>FIRST ROW</td>
<td>&nbsp;</td>
<td>Query that returns the first row only</td></TR>

<TR><td>FOR UPDATE</td>
<td>&nbsp;</td>
<td>Row locking via the FOR UPDATE statement</td></TR>

<TR><td>INDEX*</td>
<td>UNIQUE SCAN</td>
<td>Index used to return one row</td></TR>

<TR><td>INDEX*</td>
<td>RANGE SCAN </td>
<td>Ascending index-based retrieval of multiple ROWIDs</td></TR>

<TR><td>INDEX*</td>
<td>RANGE SCAN DESC</td>
<td>Descending index-based retrieval of multiple ROWIDs</td></TR>

<TR><td>INTERSECTION</td>
<td>&nbsp;</td>
<td>Accepts two sets of rows, returns intersection without duplicates</td></TR>

<TR><td>MERGE JOIN</td>
<td>&nbsp;</td>
<td>Accepts and combines two sorted row sets and returns matching rows</td></TR>

<TR><td>MERGE JOIN</td>
<td>OUTER</td>
<td>MERGE-JOIN with outer join</td></TR>

<TR><td>MINUS</td>
<td>&nbsp;</td>
<td>Accepts two row sets and returns rows in first set but not in second via MINUS operator</td></TR>

<TR><td>NESTED LOOPS</td>
<td>&nbsp;</td>
<td>Accepts outer and inner rows sets and compares each outer row with each inner row</td></TR>

<TR><td>NESTED LOOPS</td>
<td>OUTER</td>
<td>NESTED LOOPS with outer join</td></TR>

<TR><td>PROJECTION</td>
<td>&nbsp;</td>
<td>Returns subset of table columns</td></TR>

<TR><td>REMOTE</td>
<td>&nbsp;</td>
<td>Use of database link</td></TR>

<TR><td>SEQUENCE</td>
<td>&nbsp;</td>
<td>Access sequence values</td></TR>

<TR><td>SORT</td>
<td>AGGREGATE</td>
<td>Returns single row from group operation</td></TR>

<TR><td>SORT</td>
<td>UNIQUE</td>
<td>Returns set of rows, duplicates eliminated</td></TR>

<TR><td>SORT</td>
<td>GROUP</td>
<td>BY	Accepts and groups set of rows for GROUP BY processing</td></TR>

<TR><td>SORT</td>
<td>JOIN</td>
<td>Accepts and sorts a row set in preparation for a merge-join operation.</td></TR>

<TR><td>SORT</td>
<td>ORDER BY</td>
<td>Accepts and sorts a row set in preparation for an ORDER BY operation.</td></TR>

<TR><td>TABLE ACCESS</td>
<td>FULL</td>
<td>Row retrieval via a full table scan</td></TR>

<TR><td>TABLE ACCESS</td>
<td>CLUSTER</td>
<td>Row retrieval via indexed cluster</td></TR>

<TR><td>TABLE ACCESS</td>
<td>HASH</td>
<td>Row retrieval via hash cluster</td></TR>

<TR><td>TABLE ACCESS</td>
<td>BY ROWID</td>
<td>Row retrieval based on ROWID</td></TR>

<TR><td>UNION</td>
<td>&nbsp;</td>
<td>Accepts two sets of rows and returns union; eliminates duplicates.</td></TR>

<TR><td>VIEW</td>
<td>&nbsp;</td>
<td>Performs view-based retrieval</td></TR>
</table>
<p>
<B>Steps</B>
<p>
<B>1.</B>	Run SQL*Plus and connect as the WAITE user. Use the start command with a fully qualified path name describing the location of the UTLXPLAN script. The script creates a version of PLAN_TABLE in the schema of the invoking user. The invocation syntax under Windows NT appears below.
<p>
<code><b>SQL> start \orant\rdbms80\admin\utlxplan.sql</b></code>
<p>
Table created.
<p>
<B>2.</B>	Use the START command to load and execute the script CHP14_2.SQL. The script and output appear in Listing 14.1.
<p>
Listing 14.1 Running the CHP14_2.SQL script in SQL*Plus to create some sample objects
<p>
<code><b>SQL> start chp14_2
<p>
SQL>
<p>
SQL> CREATE TYPE emp_type14 AS OBJECT
<p>
2  (empno     number(4),
<p>
3   ename     varchar2(10)
<p>
4  );
<p>
Type created.
<p>
SQL>
<p>
SQL> CREATE TYPE emp_table14 AS TABLE OF emp_type14;
<p>
Type created.
<p>
SQL>
<p>
SQL> CREATE TABLE dept14
<p>
2  (
<p>
3   deptno   number(2),
<p>
4   dname       varchar2(14),
<p>
5   emps        emp_table14
<p>
6  )
<p>
7  NESTED TABLE emps STORE AS store_dept_emps14;
<p>
Table created.
<p>
SQL>
<p>
SQL> CREATE INDEX idx_dept14
<p>
2     ON dept14 (deptno);
<p>
Index created.
<p>
SQL>
<p>
SQL> CREATE INDEX idx_emp_table14
<p>
2    ON store_dept_emps14 (empno);
<p>
Index created.
<p>
SQL>
<p>
SQL> INSERT INTO dept14 VALUES
<p>
2  (1, 慏ATA MGMT