Shulou(Shulou.com)06/01 Report--

Typically, we can use the following four methods to get the execution plan of the target sql:

(1) explain plan command

(2) dbms_xplan package

(3) autotrace switch in sqlplus

(4) 10046 incident

Except for the fourth method, the execution plan obtained by the other three methods may be inaccurate. The accuracy of the execution plan judged in the oracle database depends on whether the target sql is really executed, and the execution plan corresponding to the really executed sql is accurate, otherwise it may not be accurate. Note that the judgment principle here does not strictly apply to the autotrace switch, because all execution plans shown using the autotrace switch may be incorrect, even if the corresponding target sql has actually been executed.

Let's use the above principles to determine which of the other three methods except the fourth method has an accurate implementation plan and which methods may get an inaccurate implementation plan.

For the execution plan obtained using the first method (explain plan), because the target sql is not actually executed at this time, the execution plan obtained by this method may not be correct, especially if the target sql contains bound variables. When binding variable snooping (bind peeking) is enabled by default, using explain plan to get the execution plan for the target sql with bound variables is only a semi-finished product. After snooping on the bound variables of the sql, oracle gets the specific values of these bound variables. At this time, oracle is likely to adjust with the above semi-finished product implementation plan. Once adjusted, the execution plan obtained by using the explain plan command is not correct.

For using the second method, you can choose one of the following four ways for different application scenarios:

Select * from table (dbms_xplan.display)

Select * from table (dbms_xplan.display_cursor (null,null,'advanced'))

Select * from table (dbms_xplan.display_cursor ('sql__id/hash_value',child_cursor_number,'advanced'))

Select * from table (dbms_xplan.display_awr ('sql_id'))

Obviously, the execution plan obtained by executing select * from table (dbms_xplan.display) may be inaccurate, because it only has an execution plan that looks at the target sql obtained using the explain plan command, and the target sql has not been really executed at this time, so the execution plan obtained with it may not be accurate. The execution plan obtained using the remaining three methods is accurate because the target sql has already been actually executed.

For the third method (the autotrace switch in sqlplus), you can choose to turn on the autotrace switch by performing one of the following three ways

Set autotrace on (set antot on)

Set autotrace traceonly (set autot trace)

Set autotrace traceonly explain (set autot trace exp)

In the above three ways, when using set autotrace on and set autotrace traceonly, the target sql has been actually executed, and it is precisely because it has been actually executed, so we can see the actual resource consumption of the target sql in the case of set autotrace on and set autotrace traceonly. When set autotrace traceonly explain is used, if the select statement is executed, the select statement is not actually executed by oracle, but if the DML statement is executed, the situation is different, and the DML statement is actually executed by the actual oracle.

Let's now prove the above point about set autotrace traceonly explain. First, execute the following sql normally:

SQL > select count (*) from emp where ename='JAMES'

COUNT (*)

-1

From the query results below, you can see that the value of the executions corresponding to the above sql is 1, which indicates that oracle did execute the above sql just now.

SQL > select sql_text,executions from v$sqlarea where sql_text like 'select count (*) from emp%'

SQL_TEXT EXECUTIONS

-

Select count (*) from emp where ename='JAMES' 1

Now clear the shared pool

SQL > alter system flush shared_pool

System altered.

You can see from the query results below that the shared cursor corresponding to the above sql is no longer in the shared pool.

SQL > select sql_text,executions from v$sqlarea where sql_text like 'select count (*) from emp%'

No rows selected

Execute the above sql after the autotrace is opened in traceonly explain mode in the current session

SQL > set autotrace traceonly explain

SQL > select count (*) from scott.emp where ename='JAMES'

2

Execution Plan

Plan hash value: 2083865914

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 1 | 6 | 3 (0) | 00:00:01 |

| | 1 | SORT AGGREGATE | | 1 | 6 |

| | * 2 | TABLE ACCESS FULL | EMP | 1 | 6 | 3 (0) | 00:00:01 |

Predicate Information (identified by operation id):

2-filter ("ENAME" = 'JAMES')

Let's query v$sqlare again.

SQL > select sql_text,executions from v$sqlarea where sql_text like 'select count (*) from scott.emp%'

SQL_TEXT EXECUTIONS

-

Select count (*) from scott.emp where ename='JAMES' 0

From the above query results, you can see that the EXECUTIONS corresponding to the select statement is 0, which means that oracle did parse only the select sentence but did not actually execute them. Prove the above point (when using set autot trace exp, if the select statement is executed, the select statement is not actually executed by oracle)

Next, execute the following DML statement in the current session:

SQL > delete from scott.emp where ename='JAMES'

1 row deleted.

Execution Plan

Plan hash value: 161811703

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | DELETE STATEMENT | | 1 | 13 | 3 (0) | 00:00:01 |

| | 1 | DELETE | EMP |

| | * 2 | TABLE ACCESS FULL | EMP | 1 | 13 | 3 (0) | 00:00:01 |

Predicate Information (identified by operation id):

2-filter ("ENAME" = 'JAMES')

As you can see from the query results, the above DML statement has actually been executed:

SQL > select count (*) from scott.emp where ename='JAMES'

COUNT (*)

-

0

SQL > select sql_text,executions from v$sqlarea where sql_text like 'delete from scott.emp%'

SQL_TEXT EXECUTIONS

-

Delete from scott.emp where ename='JAMES' 1

From the above example, we can see that the DML statement is executed after using set autotrace traceonly explain, and the DML statement is indeed actually executed by oracle, so be careful when using set autotrace on,set autotrace traceonly and set autotrace traceonly explain to obtain the execution plan of the DML statement, because these DML statements have actually been executed.

It is important to note here that although the target sql has actually been executed after using the set autot command, all execution plans obtained by using the set autotrace command (including set autotrace on,set autotrace traceonly,set autotrace traceonly explain) may not be allowed, because the execution plan displayed with the set autotrace command comes from calling the explain plan command.

Let's look at an example where the execution plan obtained by using the explain plan command and the set autotrace command is not the actual execution plan of the target sql. Create a test table T1 and insert some data:

SQL > create table T1 as select * from dba_objects

Table created.

SQL > insert into T1 select * from T1

87205 rows created.

SQL > commit

Commit complete.

Now the amount of data in Table T1 is more than 170000.

SQL > select count (*) from T1

COUNT (*)

-

174410

Create a single-key B-tree index IDX_T1 on column object_id of table T1

SQL > create index idx_t1 on T1 (object_id)

Index created.

Collect a statistic from table T1

SQL > exec dbms_stats.gather_table_stats (ownname= > 'SYS',tabname= >' T _ 1 parallel _ cascade = > 100 _ true)

PL/SQL procedure successfully completed.

Create two binding variables x and y, and assign them values 0 and 100000, respectively

SQL > var x number

SQL > var y number

SQL > exec: xchang0

SQL > exec: XRO

PL/SQL procedure successfully completed.

SQL > exec: YRV 100000

PL/SQL procedure successfully completed.

Use explain plan to generate an execution plan for the following sql:

SQL > explain plan for select count (*) from T1 where object_id between: X and: y

Explained.

SQL > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

-

Plan hash value: 2351893609

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 1 | 5 | 3 (0) | 00:00:01 |

| | 1 | SORT AGGREGATE | | 1 | 5 |

| | * 2 | FILTER | | |

| | * 3 | INDEX RANGE SCAN | IDX_T1 | 436 | 2180 | 3 (0) | 00:00:01 |

PLAN_TABLE_OUTPUT

-

Predicate Information (identified by operation id):

2-filter (TO_NUMBER (: y) > = TO_NUMBER (: X))

3-access ("OBJECT_ID" > = TO_NUMBER (: X) AND "OBJECT_ID" exec: XRV

PL/SQL procedure successfully completed.

SQL > exec: YRV 10000

PL/SQL procedure successfully completed.

SQL > select count (*) from T1 where object_id between: X and: y

COUNT (*)

-

19610

SQL > select * from table (dbms_xplan.display_cursor (null,null,'ADVANCED'))

PLAN_TABLE_OUTPUT

-

SQL_ID 9dhu3xk2zu531, child number 0

-

Select count (*) from T1 where object_id between: X and: y

Plan hash value: 1410530761

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | 107100 | | |

| | 1 | SORT AGGREGATE | | 1 | 5 |

PLAN_TABLE_OUTPUT

-

| | * 2 | FILTER | | |

| | * 3 | INDEX FAST FULL SCAN | IDX_T1 | 174k | 851 K | 107 (1) | 00:00:01 |

52 rows selected.

As can be seen from the above display, the execution plan of the target sql is actually a fast full scan of the index IDX_T1, which is the real execution plan of the target sql. The execution plan obtained by using the explain plan command is not accurate.

Similarly, the execution plan obtained by the set autotrace on method is not accurate.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.