Implementation Plan (Execution Plan) process analysis

2011-01-11  来源:本站原创  分类:Database  人气:102 

Simply put, the implementation plan is actually constituted by a series of data processing functions of a tree chain; those who have access to the physical data of the function is called the access path (Access Path, such as the Index Range Scan, Full Table Scan, etc. ); the lowest level of the function chain tree (leaf nodes) obtained from the physical object to the original data (Row Source), the data transmission from the bottom up, each node of the function to process it (such as JOIN matching, filtering etc.) to get a new Row Source, and continuing to the upper pass, until the root node, the data obtained at this time is the query results.

We are following through on the Row Source of the generation, implementation of the plan of the operation to be a simple analysis.

SQL code

  1. HELLODBA.COM> explain plan for
  2. 2 select / * + index (t1 t_test1_pk) index (t2 t_test2_pk) * /
  3. 3 t1.data_object_id,
  4. 4 t2.TABLE_NAME,
  5. 5 t2.STATUS,
  6. 6 t2.NUM_ROWS,
  7. 7 t2.COMPRESSION
  8. 8 from demo.t_test1 t1, demo.t_test2 t2
  9. 9 where t1.owner = t2.owner
  10. 10 and t1.object_name = t2.table_name
  11. 11 and t1.object_id <= 1000;
  12. Explained.
  13. HELLODBA.COM> select * from table (dbms_xplan.display ());
  14. PLAN_TABLE_OUTPUT
  15. -------------------------------------------------- ------------------------------------------------
  16. -------------------------------------------------- ------------------------------------------------
  17. Plan hash value: 2465336739
  18. -------------------------------------------------- -----------------------------------------
  19. | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
  20. -------------------------------------------------- -----------------------------------------
  21. | 0 | SELECT STATEMENT | | 905 | 71495 | 554 (0) | 00:09:15 |
  22. | 1 | NESTED LOOPS | | 905 | 71495 | 554 (0) | 00:09:15 |
  23. | 2 | TABLE ACCESS BY INDEX ROWID | T_TEST1 | 905 | 33485 | 11 (0) | 00:00:12 |
  24. | * 3 | INDEX RANGE SCAN | T_TEST1_PK | 905 | | 2 (0) | 00:00:02 |
  25. | 4 | TABLE ACCESS BY INDEX ROWID | T_TEST2 | 1 | 42 | 1 (0) | 00:00:02 |
  26. | * 5 | INDEX UNIQUE SCAN | T_TEST2_PK | 1 | | 1 (0) | 00:00:02 |
  27. -------------------------------------------------- -----------------------------------------
  28. Predicate Information (identified by operation id):
  29. -------------------------------------------------- -
  30. 3 - access ("T1". "OBJECT_ID" <= 1000)
  31. 5 - access ("T1". "OBJECT_NAME" = "T2". "TABLE_NAME" AND "T1". "OWNER" = "T2". "OWNER")
  32. 18 rows selected.

One T_TEST1_PK (OBJECT_ID) as T_TEST1 primary key, T_TEST2_PK (TABLE_NAME, OWNER) for the T_TEST2 primary key. We also do its follow-10046 and Row Source:

SQL code

  1. HELLODBA.COM> alter session set events '10046 trace name CONTEXT forever, level 1 ';
  2. Session altered.
  3. HELLODBA.COM> alter session set "_rowsrc_trace_level" = 4;
  4. Session altered.
  5. HELLODBA.COM> select / * + index (t1 t_test1_pk) index (t2 t_test2_pk) * /
  6. 2 t1.data_object_id,
  7. 3 t2.TABLE_NAME,
  8. 4 t2.STATUS,
  9. 5 t2.NUM_ROWS,
  10. 6 t2.COMPRESSION
  11. 7 from demo.t_test1 t1, demo.t_test2 t2
  12. 8 where t1.owner = t2.owner
  13. 9 and t1.object_name = t2.table_name
  14. 10 and t1.object_id <= 1000;
  15. ...

Here is the trace file has been generated on the Row Source analysis of the process:

SQL code

  1. PARSING IN CURSOR # 3 len = 243 dep = 0 uid = 0 oct = 3 lid = 0 tim = 990048514 hv = 3222390654 ad = '1 a5507f4 '
  2. select / * + index (t1 t_test1_pk) index (t2 t_test2_pk) * /
  3. t1.data_object_id,
  4. t2.TABLE_NAME,
  5. t2.STATUS,
  6. t2.NUM_ROWS,
  7. t2.COMPRESSION
  8. from demo.t_test1 t1, demo.t_test2 t2
  9. where t1.owner = t2.owner
  10. and t1.object_name = t2.table_name
  11. and t1.object_id <= 1000
  12. END OF STMT
  13. PARSE # 3: c = 390625, e = 372892, p = 0, cr = 276, cu = 32, mis = 1, r = 0, dep = 0, og = 4, tim = 990048507
  14. EXEC # 3: c = 0, e = 49, p = 0, cr = 0, cu = 0, mis = 0, r = 0, dep = 0, og = 4, tim = 990049106
  15. VR: rws [3], opn (1) [0] # INDEX RANGE SCAN (T_TEST1_PK), get in line Access Precidate ("T1". "OBJECT_ID" <= 1000) of the Row Source: upper nodes need Projection / Predication words segment and Rowid [ROWID]
  16. VR: rws [2], opn (3) [0,1,2] # TABLE ACCESS BY INDEX ROWID (T_TEST1), for Row Source: upper nodes need Projection / Predication field [DATA_OBJECT_ID, OWNER, OBJECT_NAME]
  17. # Of T_TEST2_PK do INDEX UNIQUE SCAN, there is no corresponding record, no Row Source
  18. VR: rws [3], opn (1) [0] # INDEX RANGE SCAN (T_TEST1_PK), get in line Access Precidate ("T1". "OBJECT_ID" <= 1000) of the Row Source: upper nodes need Projection / Predication words segment and Rowid [ROWID]
  19. VR: rws [2], opn (3) [0,1,2] # TABLE ACCESS BY INDEX ROWID (T_TEST1), for Row Source: upper nodes need Projection / Predication field [DATA_OBJECT_ID, OWNER, OBJECT_NAME]
  20. # Of T_TEST2_PK do INDEX UNIQUE SCAN, there is no corresponding record, no Row Source
  21. VR: rws [3], opn (1) [0] # INDEX RANGE SCAN (T_TEST1_PK), get in line Access Precidate ("T1". "OBJECT_ID" <= 1000) of the Row Source: upper nodes need Projection / Predication words segment and Rowid [ROWID]
  22. VR: rws [2], opn (3) [0,1,2] # TABLE ACCESS BY INDEX ROWID (T_TEST1), for Row Source: upper nodes need Projection / Predication field [DATA_OBJECT_ID, OWNER, OBJECT_NAME]
  23. VR: rws [5], opn (2) [0,1] # INDEX UNIQUE SCAN (T_TEST2_PK), get in line Access Precidate ("T1". "OBJECT_NAME" = "T2". "TABLE_NAME" AND "T1". " OWNER "=" T2 "." OWNER ") of the Row Source: upper nodes need Projection / Predication field and Rowid [DATA_OBJECT_ID, ROWID]
  24. VR: rws [4], opn (4) [0,1,2,3] # TABLE ACCESS BY INDEX ROWID (T_TEST1), for Row Source: upper nodes need Projection / Predication field [TABLE_NAME, STATUS, NUM_ROWS, COMPRESSION]
  25. VR: rws [1], opn (5) [0,1,2,3,4] # from node 2 and node 4 to get to the Row Source Projection field, is the ultimate in line with the conditions for the Row Source [DATA_OBJECT_ID, OBJECT_NAME, STATUS, NUM_ROWS, COMPRESSION]
  26. FETCH # 3: c = 0, e = 452, p = 0, cr = 9, cu = 0, mis = 0, r = 1, dep = 0, og = 4, tim = 990049641
  27. ...

Row Source Trace and compare the results of these queries, you can see the actual Row Source of data generation and consistent. To the above analysis, for example, T_TEST1 in line with Access Precidate 1, 2 does not correspond to the conditions recorded in the T_TEST2_PK record, the third record is a successful JOIN.

SQL code

  1. HELLODBA.COM> select / * + index (t1 t_test1_pk) index (t2 t_test2_pk) * / t1.OWNER, t1.OBJECT_NAME, t2.OWNER, t2.TABLE_NAME
  2. 2 from demo.t_test1 t1, demo.t_test2 t2
  3. 3 where t1.owner = t2.owner (+)
  4. 4 and t1.object_name = t2.table_name (+)
  5. 5 and t1.object_id <= 1000;
  6. OWNER OBJECT_NAME OWNER TABLE_NAME
  7. ------------------------------ -------------------- ---------- ------------------------------ ---------- -------------
  8. SYS C_OBJ #
  9. SYS I_OBJ #
  10. SYS TAB $ SYS TAB $
  11. SYS CLU $ SYS CLU $
  12. SYS C_TS #
  13. SYS I_TS #
  14. SYS C_FILE # _BLOCK #
  15. SYS I_FILE # _BLOCK #
  16. SYS C_USER #
  17. SYS I_USER #
  18. SYS FET $ SYS FET $
  19. SYS UET $ SYS UET $
  20. SYS SEG $ SYS SEG $
  21. SYS UNDO $ SYS UNDO $
  22. SYS TS $ SYS TS $
  23. ...

If we look at the query plan above to add the Filter Predication, Row Source is how the production process.

SQL code

  1. HELLODBA.COM> explain plan for
  2. 2 select / * + index (t1 t_test1_pk) index (t2 t_test2_pk) * /
  3. 3 t1.data_object_id,
  4. 4 t2.TABLE_NAME,
  5. 5 t2.STATUS,
  6. 6 t2.NUM_ROWS,
  7. 7 t2.COMPRESSION
  8. 8 from demo.t_test1 t1, demo.t_test2 t2
  9. 9 where t1.owner = t2.owner
  10. 10 and t1.object_name = t2.table_name
  11. 11 and t1.object_id <= 1000
  12. 12 and t1. Temporary = 'Y';
  13. Explained.
  14. HELLODBA.COM> select * from table (dbms_xplan.display ());
  15. PLAN_TABLE_OUTPUT
  16. -------------------------------------------------- -------------------------------------------------
  17. -------------------------------------------------- -------------------------------------------------
  18. Plan hash value: 2465336739
  19. -------------------------------------------------- -----------------------------------------
  20. | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
  21. -------------------------------------------------- -----------------------------------------
  22. | 0 | SELECT STATEMENT | | 453 | 36693 | 283 (0) | 00:04:44 |
  23. | 1 | NESTED LOOPS | | 453 | 36693 | 283 (0) | 00:04:44 |
  24. | * 2 | TABLE ACCESS BY INDEX ROWID | T_TEST1 | 453 | 17667 | 11 (0) | 00:00:12 |
  25. | * 3 | INDEX RANGE SCAN | T_TEST1_PK | 905 | | 2 (0) | 00:00:02 |
  26. | 4 | TABLE ACCESS BY INDEX ROWID | T_TEST2 | 1 | 42 | 1 (0) | 00:00:02 |
  27. | * 5 | INDEX UNIQUE SCAN | T_TEST2_PK | 1 | | 1 (0) | 00:00:02 |
  28. -------------------------------------------------- -----------------------------------------
  29. Predicate Information (identified by operation id):
  30. -------------------------------------------------- -
  31. 2 - filter ("T1". "TEMPORARY" = 'Y')
  32. 3 - access ("T1". "OBJECT_ID" <= 1000)
  33. 5 - access ("T1". "OBJECT_NAME" = "T2". "TABLE_NAME" AND "T1". "OWNER" = "T2". "OWNER")
  34. 19 rows selected.

Here is the content tracking and analysis:

SQL code

  1. PARSING IN CURSOR # 2 len = 288 dep = 0 uid = 0 oct = 3 lid = 0 tim = 1850866257 hv = 1801581673 ad = '1 aa7beb4 '
  2. select / * + index (t1 t_test1_pk) index (t2 t_test2_pk) * /
  3. t1.data_object_id,
  4. t2.TABLE_NAME,
  5. t2.STATUS,
  6. t2.NUM_ROWS,
  7. t2.COMPRESSION
  8. from demo.t_test1 t1, demo.t_test2 t2
  9. where t1.owner = t2.owner
  10. and t1.object_name = t2.table_name
  11. and t1.object_id <= 1000
  12. and t1. temporary = 'Y'
  13. END OF STMT
  14. PARSE # 2: c = 0, e = 2770, p = 0, cr = 0, cu = 0, mis = 1, r = 0, dep = 0, og = 4, tim = 1850866251
  15. EXEC # 2: c = 0, e = 65, p = 0, cr = 0, cu = 0, mis = 0, r = 0, dep = 0, og = 4, tim = 1850906163
  16. VR: rws [3], opn (1) [0] # INDEX RANGE SCAN (T_TEST1_PK), get in line Access Precidate ("T1". "OBJECT_ID" <= 1000) of the Row Source: upper nodes need Projection / Predication words segment and Rowid [ROWID]
  17. # Row Source is Filter Predicate ("T1". "TEMPORARY" = 'Y') filter
  18. VR: rws [3], opn (1) [0] # INDEX RANGE SCAN (T_TEST1_PK), get in line Access Precidate ("T1". "OBJECT_ID" <= 1000) of the Row Source: upper nodes need Projection / Predication words segment and Rowid [ROWID]
  19. # Row Source is Filter Predicate ("T1". "TEMPORARY" = 'Y') filter
  20. ...
  21. VR: rws [3], opn (1) [0] # INDEX RANGE SCAN (T_TEST1_PK), get in line Access Precidate ("T1". "OBJECT_ID" <= 1000) of the Row Source: upper nodes need Projection / Predication words segment and Rowid [ROWID]
  22. VR: rws [2], opn (3) [0,1,2] # TABLE ACCESS BY INDEX ROWID (T_TEST1), for Row Source: upper nodes need Projection / Predication field [DATA_OBJECT_ID, OWNER, OBJECT_NAME]
  23. VR: rws [5], opn (2) [0,1] # INDEX UNIQUE SCAN (T_TEST2_PK), get in line Access Precidate ("T1". "OBJECT_NAME" = "T2". "TABLE_NAME" AND "T1". " OWNER "=" T2 "." OWNER ") of the Row Source: upper nodes need Projection / Predication field and Rowid [DATA_OBJECT_ID, ROWID]
  24. VR: rws [4], opn (4) [0,1,2,3] # TABLE ACCESS BY INDEX ROWID (T_TEST1), for Row Source: upper nodes need Projection / Predication field [TABLE_NAME, STATUS, NUM_ROWS, COMPRESSION]
  25. VR: rws [1], opn (5) [0,1,2,3,4] # from node 2 and node 4 to get to the Row Source Projection field, is the ultimate in line with the conditions for the Row Source [DATA_OBJECT_ID, OBJECT_NAME, STATUS, NUM_ROWS, COMPRESSION]
  26. FETCH # 2: c = 1281250, e = 1326739, p = 0, cr = 17, cu = 0, mis = 0, r = 1, dep = 0, og = 4, tim = 1852235821
  27. ...

You can see the Access Predicate and Filter Predicate important difference: Access Predicate done to determine the access data, the data does not meet the conditions will not form Row Source; The Filter Predicate that have been generated to determine the Row Source do, does not meet the conditions of the discarded (Throw-Away). The reduction in the execution plan Throw-Away is we do SQL tuning is an important reference, therefore, to some of the Filter Predicate Access Predicate method is also our important tuning tool.

--- Fuyuncat ---

相关文章
  • Implementation Plan (Execution Plan) process analysis 2011-01-11

    Simply put, the implementation plan is actually constituted by a series of data processing functions of a tree chain; those who have access to the physical data of the function is called the access path (Access Path, such as the Index Range Scan, Ful

  • By analyzing the SQL statement SQL statement execution plan optimization 2010-09-08

    By analyzing the SQL statement SQL statement execution plan optimization Routine maintenance on the database, adjust the individual performance of poorly performing SQL statements is a challenging task. The key is how to get SQL statement execution p

  • 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 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

  • Analysis using the SQLPLUS SQL statement (query execution plan tracking) 2010-11-10

    Method One: autotrace 1, connect sys / password as sysdba, the sys user run $ ORACLE_HOME / sqlplus / admin / plustrce.sql This sql actual content of the following: set echo on drop role plustrace; create role plustrace; Grant Select on V_ $ sesstat

  • Analysis of how to use the SQLPLUS SQL statement (query execution plan tracking) 2011-01-09

    Method One: autotrace 1, connect sys / password as sysdba, the sys user run $ ORACLE_HOME / sqlplus / admin / plustrce.sql The actual content of this sql is as follows: set echo on drop role plustrace; create role plustrace; grant select on v_ $ sess

  • Analysis of how to use SQLPLUS SQL statement (query execution plan tracking) 2010-08-20

    Method One: autotrace 1, connect sys / password as sysdba, run the sys user $ ORACLE_HOME / sqlplus / admin / plustrce.sql The actual content of this sql is as follows: set echo on drop role plustrace; create role plustrace; grant select on v_ $ sess

  • Analysis using SQLPLUS SQL statement (query execution plan tracking) 2010-11-10

    Method One: autotrace 1, connect sys / password as sysdba, run the sys user $ ORACLE_HOME / sqlplus / admin / plustrce.sql This sql actual content of the following: set echo on drop role plustrace; create role plustrace; Grant SELECT on v_ $ sesstat

  • 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 *

  • db2 execution plan analysis 2011-06-26

    During a second-tier system of technical maintenance, the front-line colleagues reported a service-based implementation of a particularly long time, the analysts an update operation is performed for two hours and found that the operation involving tw

  • oracle view the SQL execution plan parameters analysis 2010-04-14

    sql_trace been tracking the implementation plan: Call Count CPU Time Elapsed Time Disk Query Current Rows ------- ------ -------- ------------ ---------- ------- --- ---------- ---------- Parse 1 0.000 0.000 0 0 0 0 Execute 1 0.000 0.000 0 0 0 0 Fetc

  • Reposted elsewhere - by analyzing the SQL statement execution plan optimization of SQL (summary) 2010-06-30

    By analyzing the SQL statement execution plan optimization of SQL (summary) DBA did almost 7 years, and sentiment among many. In the DBA's daily work, to adjust individual performance to a less challenging when the SQL statement of work. The key lies

  • 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

  • By analyzing the SQL statements SQL statement execution plan optimization 2010-09-08

    By analyzing the SQL statements SQL statement execution plan optimization Routine maintenance in the database, adjust the individual performance of the SQL statement is a less challenging job. The key is how to get SQL statement execution plans and h

  • (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 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

  • How to use the ordered hint to change the SQL execution plan 2011-05-05

    ORDERED hint to force the Oracle tables in the From clause in accordance with the order form connections there. Through the ordered hint to avoid CBO SQL table join in the resolution process assessment, thus avoiding the error of the execution plan O

  • 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

  • Oracle Implementation Plan (Explain Plan) Description 2011-07-07

    http://blog.csdn.net/tianlesoftware/article/details/5827245 http://blog.csdn.net/tianlesoftware/article/details/5826546 If you want to analyze the SQL performance on a particular issue, usually we have to look at the SQL execution plan to see each st

  • 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