Do you know the sql plan?

Maclean Liu(刘相兵

Do you know the plan?

ABSTRACT
This document describes the evolution of the Plan Table, V$SQL_PLAN, and DBMS_XPLAN from 9i to 11g and how they can aid in effective and efficient SQL Tuning. DBMS_XPLAN specifically has much to offer to the DBA and the Developer DBMS_XPLAN has evolved from 1 function in 9i to 6 in 10g and 14 in 11g, and will be demonstrated in detail.
PLAN_TABLE
We begin by asking the question. What happens when Oracle parses a SQL Statement? The Oracle Database after doing all the necessary checks and after being evaluated by the Cost Based Optimizer, transforms the SQL Statement into a set of row source operations. The set of row source operation is called the Plan for the SQL Statement under consideration.
Row Source Operations
Row source operations are specific data access and join/sort methods Oracle is going to execute to fulfill the request for data done by the query.
Some of the row source operations are:
Index Scans (Unique, Range, Skip etc.)
Table Access (By Index Rowid, Full Table Scans)
Joins (Nested Loops, Hash, Sort-Merge)
Sort Operations
The row source operations and their corresponding details are stored in the PLAN_TABLE when the SQL Statement is explained by the Oracle Database. The PLAN_TABLE contains the id and the parent_id columns and describes the relationship between the row source operations or steps in the plan. A step will either be a parent or a child. Parent steps can have one or more children depending on the operation. Children will always produce a result set and pass that set to a parent step. Some operations pass rows to their parent as soon as they have been acquired (NESTED LOOPS), while other operations must acquire the entire result set before passing it to its parent. When a parent has multiple child steps, the first child is the first step after the parent. The other child row will be further down the plan.
Common parent step operations include: NESTED LOOPS, SORT-MERGE JOIN, HASH JOIN, INLIST ITERATOR, UNION ALL, CONCATENATION, and FILTER.
Common child step operations include: Index Scans, Table Access by Index Rowid and Full Table Scans.


Do you know the plan.doc (127 KB, 下载次数: 11, 售价: 10 金钱)

2012-4-21 09:08 上传

点击文件名下载附件
售价: 10 金钱  [记录]

zhuqibs

这个基本的，还是要看一下的！

klby

这个要下先。

bjchangxiao

好东西，要学习