Oracle execution plan in order to see how the

2011-01-13  来源:本站原创  分类:Database  人气:89 

Oracle execution plan ways to read:

Look to the right start at the very beginning has been, until you see the place where the far right side by side, for non-parallel, right, the first run: For side by side, relying on the first run.
Namely, in the indented block down from the executive, non-parallel indented block, from the bottom up implementation.

The following example:

Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_HOST_BATCH_DTL'
3 2 INDEX (RANGE SCAN) OF 'XIE2TB_HOST_BATCH_DTL' (NON-UNIQUE)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_BATCH_TRAN_CTRL'
5 4 INDEX (UNIQUE SCAN) OF 'SYS_C0034652' (UNIQUE)

We assume the right of the data line number, statement execution is carried out according to the block.
From the beginning of the implementation plan has been Look to the right until you find the best on the right side by side. That is, right from the beginning to find SELECT STATEMENT.
Find an indented block, only one, there is no priority at all, as follows:
1 0 NESTED LOOPS
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_HOST_BATCH_DTL'
3 2 INDEX (RANGE SCAN) OF 'XIE2TB_HOST_BATCH_DTL' (NON-UNIQUE)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_BATCH_TRAN_CTRL'
5 4 INDEX (UNIQUE SCAN) OF 'SYS_C0034652' (UNIQUE)

Then look at the secondary level, block indent indent blocks, find the two, as follows:
Indent the first two blocks:
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_HOST_BATCH_DTL'
3 2 INDEX (RANGE SCAN) OF 'XIE2TB_HOST_BATCH_DTL' (NON-UNIQUE)
Indent the second two blocks:
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_BATCH_TRAN_CTRL'
5 4 INDEX (UNIQUE SCAN) OF 'SYS_C0034652' (UNIQUE)

Parallel block in accordance with the principles of the implementation from the top down, the first two indent all the statements in the block will give priority to the second two indented block of statements executed.
Indent blocks of the two secondary executed to perform an indented block, this is because the indentation of non-parallel blocks, from the bottom up implementation.

Block in the secondary again to find three indented indented block.
The first two blocks only one of three levels of indentation indented block, as follows:
3 2 INDEX (RANGE SCAN) OF 'XIE2TB_HOST_BATCH_DTL' (NON-UNIQUE)
In the absence of a lower level of indent blocks, so the first implementation of the block. And then do two indented blocks.

The second block in the second indent indent only a three blocks, as follows:
5 4 INDEX (UNIQUE SCAN) OF 'SYS_C0034652' (UNIQUE)
Lower secondary level of indentation does not block indent the block, so, executed the first two statements indented block after all, they do this three indentation block, and then executes the corresponding block of the secondary indentation .

Indent blocks are the implementation of two secondary completion, and then block the implementation of the level of indentation.

Therefore, if the by-line order, the execution order of statements is: 3 -> 2 -> 5 -> 4 -> 1, namely:

3 2 INDEX (RANGE SCAN) OF 'XIE2TB_HOST_BATCH_DTL' (NON-UNIQUE) ->
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_HOST_BATCH_DTL' ->
5 4 INDEX (UNIQUE SCAN) OF 'SYS_C0034652' (UNIQUE) ->
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_BATCH_TRAN_CTRL' ->
1 0 NESTED LOOPS

Implementation plan in order to explain the theory:
According to certain rules of the implementation plan is shown as follows:
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_HOST_BATCH_DTL'
3 2 INDEX (RANGE SCAN) OF 'XIE2TB_HOST_BATCH_DTL' (NON-UNIQUE)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_BATCH_TRAN_CTRL'
5 4 INDEX (UNIQUE SCAN) OF 'SYS_C0034652' (UNIQUE)
The first column is the step ID (equivalent to us that the line number)
The second step of the ID column is the parent
The third column is the procedure to conduct the operation.
In the implementation process, the parent step depends on the sub-steps. Only the corresponding completion of all sub-steps to perform the parent step.

Therefore, the sub-steps steps 0 to 1, step 1 of the sub-steps 2,4, Step 2 of 3 sub-steps, Step 4 of 5 sub-steps, steps, 3,5 there is no sub-step.
Therefore, the module is located in step 3 or step 5 where the module is first executed. However, in Step 5 Step 3 front row, so step 3 where the first implementation of the module. Therefore, the order is still:
3 -> 2 -> 5 -> 4 -> 1

相关文章
  • Oracle execution plan in order to see how the 2011-01-13

    Oracle execution plan ways to read: Look to the right start at the very beginning has been, until you see the place where the far right side by side, for non-parallel, right, the first run: For side by side, relying on the first run. Namely, in the i

  • Oracle execution plan to see how the order is 2011-01-13

    Reading oracle execution plan approach: Start at the very beginning has been to right, side by side until you see the most right place for non-parallel, right, the first execution: For side by side, relying on the first run. That is parallel to inden

  • Oracle execution plan from the start view 2010-08-29

    Consider the following situation (1) If your data table to manage 40 million video information; (2) If you manage 10 million kinds of product data sheets; A simple paging query, you truly feel its efficiency over it? If users complain about slow resp

  • (Installed) oracle execution plan 2011-07-20

    One. Related Concepts Rowid concept Recursive Sql concept Predicate (verb) DRiving Table (table-driven) Probed Table (by probe table) Combination index (concatenated index) Selectivity (selectivity) II. oracle access to the data access methods 1) Ful

  • oracle execution plan 3 2010-03-02

    Environment: oracle 817 + linux + array cabinet swd_billdetail Table 50 million data SUPER_USER Table 2800 data Connect columns are indexed, and super_user in a table corresponds to swd_billdetail many records Table and Index are also analyzed. Pract

  • oracle execution plan 4 2010-03-02

    The statement by adding hints, so that the optimizer oracle nested loop, and a large table as the driving table, generate a new execution plan: select / * + ORDERED USE_NL (A) * / count (a.CHANNEL | | B.user_class) from swd_billdetail B, SUPER_USER A

  • Oracle execution plan from the beginning to view 2010-08-29

    Consider the following scenario, (1) If your data tables to manage 30 million line of card user information; (2) If you manage 10 million kinds of product data sheets; A simple paging query, you truly felt it efficient? If users complain about slow r

  • oracle execution plan cost, card meaning 2011-03-19

    This card is a step in the planned number of rows processed. cost refers to the cbo in the resources spent on this step, this value is a relative value. This step refers to the cbo bytes to process all records of the number of bytes, is estimated fro

  • oracle execution plan 1 2010-03-02

    1. Related concepts Rowid concept: rowid is a pseudo-column, since it is pseudo-column, then this column is not user-defined, but the system itself to add the. Each table has a rowid pseudo-column, but the table does not physically store the value of

  • oracle execution plan 2 2010-03-02

    3. Form the connection between Join an attempt to combine the two tables predicate, one can only connect two tables, table join can also be referred to as worksheets. In the following description, we will use the "row source" instead of "ta

  • oracle execution plan explained 2011-03-31

    [Transfer] http://www.iteye.com/topic/586256 I. Concepts 1 · rowid, pseudo-column: is the system to add their own, and each table has a pseudo-column, not the physical presence. It can not be modified, deleted, and added, rowid in the life cycle of t

  • oracle execution plan 2010-07-19

    Set the implementation of the plan: set autotrace on; set timing on; Execution time format: Elapsed: 00:00:00.30 Hours : Minutes : Seconds : Seconds /100 Switch User: SQL> connect username/[email protected]

  • Oracle: implementation plan: access path connection table, suggesting Statistical information bind variables 2011-09-09

    SQL execution: Analysis: Hard analysis, soft analysis Implementation Plan: access path (access path), table join (table join), statistics (statistics), bind variable (bind variable), tips (hints) ================================================== ===

  • oracle - how to analyze the execution plan 2011-08-15

    Example 1: Suppose LARGE_TABLE is a large table, and there is no index on the username column, then run the following statement: SQL> SELECT * FROM LARGE_TABLE where USERNAME = 'TEST'; Query Plan ----------------------------------------- SELECT STATE

  • oracle sql statement execution plan in the analysis method 2011-01-14

    How to generate explain plan? Answer: Run utlxplan.sql. The establishment plan table Specific SQL statement, using the explain plan set statement_id = 'tst1' into plan_table for sql statement Run explain plan look utlxplp.sql or utlxpls.sql (Select *

  • Oracle how to analyze the execution plan (reproduced) 2011-03-31

    Example 1: Suppose LARGE_TABLE is a large table, and there is no index on the username column, then run the following statement: SQL> SELECT * FROM LARGE_TABLE where USERNAME = 'TEST'; Query Plan ----------------------------------------- SELECT STATE

  • How do I view the execution plan oracle sql 2011-04-09

    Method One The first step: login sql / plus the command (no order) set time on; (Note: Open time display) set autotrace on; (Note: Turn on Automatic statistical analysis, and display the results of an SQL statement) set autotrace traceonly; (Note: Op

  • Oracle using sql plus view the execution plan (reprint) 2011-04-26

    In sql * plus, enter set autotrace on, and then directly execute sql statements; will automatically display the execution plan and statistics. Disadvantages: This method is used to view the sql statement execution time is longer, you need to wait for

  • Oracle: hint to manually change the sql execution plan 2011-07-06

    Tips (hint) from Oracle7 introduced, aimed at cost-based optimizer to make up for deficiencies. Tips are usually used to manually change the SQL execution plan to improve the efficiency of enforcement. hints is the oracle to provide a mechanism to te

  • How to view the execution plan oracle 2011-07-07

    See individual SQL method I. using explain plan If PLAN_TABLE table does not exist, execute $ ORACLE_HOME / rdbms / admin / utlxplan.sql create plan_table table. The first step: explain plan for select * from ...... Step two: select * from table (DBM