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

I have previously introduced four ways to view the execution plan in the Oracle database:

Explain plan command

DBMS_ XPLAN package

AUTOTRACE switch in SQLPLUS

10046 event

Except for the fourth method, the execution plan obtained by the other three methods may be inaccurate. Whether the execution plan obtained in the Oracle is accurate or not 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. But the judgment principle here does not strictly apply to AUTOTRACE switches, because all AUTOTRACE switches may show incorrect execution plans, 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 is accurate and which methods are likely to be inaccurate.

1. Explain plan command

For the execution plan obtained by this method, 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, the execution plan obtained by using explain plan on 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, Oralce may adjust the implementation plan of the above semi-finished products, and the execution plan obtained by using the explain plan command is not correct.

2. DBMS_ Xplan package

For this 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 not be allowed, because it is only used to view the execution plan of 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 correct. The execution plan obtained using the remaining three methods is accurate because the target SQL has already been actually executed.

3. AUTOTRACE switch

Using this method, you can choose three ways to turn on the TRACE switch

SET AUTOTRACE ON

SET AUTOTRACE TRACEONLY

SET AUTOTRACE TRACEONLY EXPLAIN

In the above three methods, 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 implemented that we can see the actual resource consumption of the target SQL in the case of SET AUTOTRACE ON and SET AUTOTRACE TRACEONLY. When using SET AUTOTRACE TRACEONLY EXPLAIN, 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 Oracle. Although the target SQL has actually been executed after using some SET AUTOTRACE commands, the resulting execution plan may not be correct, because the execution plan shown with the SET AUTOTRACE command comes from calling the explain plan command.

Here is an example to prove it:

Scott@ORCL > create table T1 as select * from dba_objects;Table created.scott@ORCL > insert into T1 select * from T1 political 86885 rows created.scott@ORCL > commit;Commit complete.scott@ORCL > select count (*) from T1; COUNT (*)-173770scott@ORCL > create index idx_t1 on T1 (object_id); Index created.scott@ORCL > exec dbms_stats.gather_table_stats (ownname= > 'SCOTT',tabname= >' T1century grammatical calendar = > 100 cores cascade> true); PL/SQL procedure successfully completed.scott@ORCL > var x number;scott@ORCL > var y number Scott@ORCL > exec: X: = 0 select from table procedure successfully completed.scott@ORCL > exec: y: = 1000000 select select SQL procedure successfully completed.scott@ORCL > explain plan for select count (*) SQL T1 where object_id between: X and: y position explored.scottables ORCL > select * SQL (dbms_xplan.display) PLAN_TABLE_OUTPUT- -Plan hash value: 2351893609 Murray- -| 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 | 2170 | 3 (0) | 00:00:01 |-Predicate Information (identified by operation id):- -2-filter (TO_NUMBER (: y) > = TO_NUMBER (: X)) 3-access ("OBJECT_ID" > = TO_NUMBER (: X) AND "OBJECT_ID" select count (*) from T1 where object_id between: X and: y COUNT (*)-173380scott@ORCL > 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: yPlan hash value: 1410530761 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | 107100 | 1 | SORT AGGREGATE | | 1 | 5 | | * 2 | FILTER | * 3 | INDEX FAST FULL SCAN | IDX_T1 | 172k | 843K | 107 (1) | 00:00:02 | -. Omit partial output scott@ORCL > set autotrace traceonlyscott@ORCL > select count (*) from T1 where object_id between: X and: y Execution Plan---Plan hash value: 2351893609 Murray- -| 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 | 434 | 2170 | 3 (0) | 00:00:01 |-

As you can see from the content shown above, the execution plan obtained by using SET AUTOTRACE ON is exactly the same as the previous execution plan obtained by explain plan, that is, the execution plan obtained by using SET AUTOTRACE ON is not allowed at this time.

In addition, if the execution plan of the target SQL has been age out out of the Shared Pool, how do you get the real execution plan of the SQL?

If it is Oracle 10g or above, and the execution plan of the SQL has been captured by Oracle and stored in AWR Repository, you can use AWR SQL reports to get the real historical execution plan.

In the case of Oracle 9i, there is usually no way to get an execution plan for that SQL unless additional Statspack reports are deployed and Statspack reports are collected with a level value greater than or equal to 6.

Use AWR SQL reports to get a real historical execution plan reference: http://hbxztc.blog.51cto.com/1587495/1897981

Refer to "SQL Optimization based on Oracle"

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.