Dbms_xplan.display_awr method to obtain the experiment of the Execution Plan and the previous misleading, dbmsxplan. display

Dbms_xplan.display_awr method to obtain the experiment of the Execution Plan and the previous misleading, dbmsxplan. display

This blog article "view several common methods of Oracle execution plans-Series 1" (http://blog.csdn.net/bisal/article/details/38919181) has mentioned a hidden problem:

"Hide Question 2:

This experiment shows that the use of select * from table (dbms_xplan.display_awr ('SQL _ id') has no results. @ Huang Wei said that AWR may collect top SQL statements, it is possible that the SQL used for testing is not most intensive SQL, but I used the manual snapshot collection executed after alter system flush shared_pool, or was not caught by AWR, which is a strange problem, this will also be carefully described in another blog."

Background:

"Select * from table (dbms_xplan.display_awr ('SQL _ id '));

(1) explain plan for + SQL is used as the premise, (2) and (3) the premise is that the SQL Execution plan is still in the shared pool, specifically, it is in the database cache. If the shared pool has been switched out by age out, the two methods cannot be used. If the execution plan of the SQL statement is collected to the AWR database, you can use (4) the preceding SQL statement to query the historical execution plan ."

You can use this SQL statement to view the execution plans saved in the AWR database. But I tried experimenting with dual tables and found that his execution plan was not saved by the AWR Library (http://www.itpub.net/forum.php? Mod = viewthread & tid = 1886046 & extra = ).

Lab:

1. Create a test table:

2. Check whether select count (*) from has been cached in the Shared Pool.Awr_tblExecution Plan:

3.Manually collect AWR reports and clear SharedPOool Buffer Pool:

The buffer is cleared. The SQL information that can be queried from v $ sqlarea has been deleted.

4.Use the "select * from table (dbms_xplan.display_awr ('SQL _ id');" mentioned above to check the saved SQLInformation:

What is the difference between dbms_xplan.display_awr ('SQL _ id') and dbms_xplan.display_cursor described by @ dbsnake?The predicate information cannot be displayed because the predicates access_predicates and filter_predicates are not imported into the AWR base table WRH $ _ SQL _PLAN from V $ SQL _PLAN., Also made an experiment:

Select count (*) from sys_awr where object_name = 'sys _ awr'; statement, where the predicate information in V $ SQL _PLAN is: "OBJECT_NAME =" = 'sys _ awr ', however, from WRH $ _ SQL _PLAN, the two fields are empty:

Summary:

(1) select * from table (dbms_xplan.display );

(2) select * from table (dbms_xplan.display_cursor (null, null, 'advanced '));

(3) select * from table (dbms_xplan.display_cursor ('SQL _ id/hash_value', child_cursor_number, 'advanced '));

(4) select * from table (dbms_xplan.display_awr ('SQL _ id '));

The preceding four methods are used to view execution plans using dbms packages:

(1) it must be used with the explain plan.

(2), (3) the SQL statement is still in the Shared Pool.

(4) The AWR database is required to save the SQL information. In addition, no predicate information is displayed.

Select different methods for reading execution plans for different scenarios.

During the experiment, I used select * from dual as the test SQL statement, but did not get the above results, it is considered that the operation on DUAL tables is not performed in Oracle like normal table retrieval, and there is a chance to explore. (Http://www.itpub.net/forum.php? Mod = viewthread & tid = 1886046 & extra =)