View the execution sequence of the Oracle execution plan using the post-sequential traversal of the Binary Tree

Let's first use a small table to explain which aspects should be paid attention to in the Oracle execution plan.

HR @ orcl> set autotrace traceonlyhr @ orcl> select * from t; when fuzzy comparison of the two SQL statements, we recommend that you first check the following two values: cost (% CPU): CPU cost, this value must be an algebraic sum. For example, 3 + 3 = 6 consistent Gets: The value is usually executed several times! It makes sense to make him stable. Execution Plan: -------------------------------------------------------- plan hash value: 1601196873 the following method of this plan: from inside to outside, from top to bottom tables | ID | operation | Name | rows | bytes | cost (% CPU) | time | bytes | 0 | SELECT statement | 1 | 6 | 3 (0) | 00:00:01 | 1 | Table Access f Ull | T | 1 | 6 | 3 (0) | 00:00:01 | Statistics ---------------------------------------------------------- 228 recursive cballs -- access the data dictionary to obtain metadata. When the same statement is executed for the second time, the recursive call is basically zero. 0 dB block gets -- the number of data blocks obtained by the DML statement 33 consistent gets -- important !! Refers to the number of data blocks obtained by the SELECT statement 8 Physical reads -- data read from the hard disk 0 redo size -- generated log 414 bytes sent via SQL * Net to client -- network traffic indicator 385 bytes provisioned ed via SQL * Net from client -- network traffic indicator 2 SQL * Net roundtrips to/from client 4 sorts (memory) 0 sorts (Disk) 1 rows processed

Now, we use a large table to list the execution plan.

sys@ORCL> select * from dba_objects;50393 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 2127761497----------------------------------------------------------------------------------------------| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT               |             | 48669 |  8412K|   145   (5)| 00:00:02 ||   1 |  VIEW                          | DBA_OBJECTS | 48669 |  8412K|   145   (5)| 00:00:02 ||   2 |   UNION-ALL                    |             |       |       |            |          ||*  3 |    FILTER                      |             |       |       |            |          ||*  4 |     HASH JOIN                  |             | 51423 |  4670K|   143   (5)| 00:00:02 ||   5 |      TABLE ACCESS FULL         | USER$       |    62 |   868 |     2   (0)| 00:00:01 ||*  6 |      TABLE ACCESS FULL         | OBJ$        | 51423 |  3967K|   140   (4)| 00:00:02 ||*  7 |     TABLE ACCESS BY INDEX ROWID| IND$        |     1 |     8 |     2   (0)| 00:00:01 ||*  8 |      INDEX UNIQUE SCAN         | I_IND1      |     1 |       |     1   (0)| 00:00:01 ||   9 |    TABLE ACCESS BY INDEX ROWID | LINK$       |     1 |    88 |     0   (0)| 00:00:01 ||  10 |     NESTED LOOPS               |             |     1 |   102 |     2   (0)| 00:00:01 ||  11 |      TABLE ACCESS FULL         | USER$       |    62 |   868 |     2   (0)| 00:00:01 ||* 12 |      INDEX RANGE SCAN          | I_LINK1     |     1 |       |     0   (0)| 00:00:01 |----------------------------------------------------------------------------------------------

In fact, the first two columns are binary trees. We can use the V $ SQL _plan view to easily draw this tree.

sys@ORCL> select id,parent_id                         2         from v$sql_plan  3        where plan_hash_value=2127761497;        ID  PARENT_ID---------- ----------         0         1          0         2          1         3          2         4          3         5          4         6          4         7          3         8          7         9          2        10          9        11         10        12         10

The following figure shows the sub-nodes and parent nodes:

After traversing this binary tree, we can get the execution sequence of this SQL statement: 5, 6, 4, 8, 7, 3, 11, 12, 10, 9, 2, 1, 0.

Note:

1) The number in the figure is found from V $ SQL _plan.

2) drawing always starts from the left child

3) binary tree performs post-order traversal, which is the execution order of SQL statements.