Dbms_xplan.display_awr way to get execution plan experiment and before misleading

the see several common methods of Oracle execution plan-Series 1(http://blog.csdn.net/bisal/article/details/38919181) This blog post has mentioned a hidden issue:

" hidden Issue 2:

This part of the experiment finds the use of select * FROM table (Dbms_xplan.display_awr (' sql_id ')), and there is no result, @ Yap says it is possible that AWR collects top SQL, It is possible that the test SQL is not the most intensive SQL, but I am using the ALTER system flush Shared_pool after the manual capture snapshot, or is not caught by awr, the more strange problem, this will be in another blog post carefully explained. "

the background is:

"Select * FROM Table (Dbms_xplan.display_awr (' sql_id '));

(1) is the premise of using explain plan for +sql, (2) and (3) The premise is that the SQL execution plan is also in the shared pool, specifically in the library cache. If you have already swapped out a shared pool by age out, you cannot use either of these methods. If the SQL execution plan is captured in the AWR library, you can use (4) The SQL above to query the historical execution plan. ”

Even with this SQL, you can view the execution plans saved in the AWR library. But I tried to experiment with the dual table and found that the AWR library was not saving his execution plan (http://www.itpub.net/forum.php?mod=viewthread&tid=1886046&extra=).

Experiment :

1. Create a test table :

2. Query whether the execution plan for select COUNT (*) from AWR_TBL has been cached in the shared pool :

3. manually collect the AWR report and empty the shared Pool buffer pool :

See the buffer is emptied, the SQL information that you can query from V$sqlarea has been deleted.

4. Use the "SELECT * from table" mentioned above(Dbms_xplan.display_awr (' sql_id '));" Look at the SQL information saved in awr :

For @dbsnake said dbms_xplan.display_awr (' sql_id ') and Dbms_xplan.display_cursor the difference is that the predicate information cannot be displayed because the predicate fields Access_predicates and filter_predicates are not imported when the AWR base table Wrh$_sql_plan is imported from V$sql_plan . An experiment was also done:

Select COUNT (*) from Sys_awr where object_name= ' sys_awr '; statement, predicate information exists in V$sql_plan: "object_name=" = ' sys_awr ', but from Wrh$_sql_ The two fields are empty in plan:

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 above is the four ways to view the execution plan using the DBMS package, where:

(1) to be used in conjunction with explain plan.

(2), (3) requires SQL to remain in the shared pool.

(4) The AWR library is required to save the SQL information. Additionally , the predicate information is not displayed.

Choose different ways to read the execution plan for different scenarios.

During the experiment I used select * from dual as the test SQL, but did not get the result as above, by the @ Yap Master on-demand, think that may be for the operation of the dual table Oracle internal is not like normal table retrieval method to execute, there is an opportunity to explore. (http://www.itpub.net/forum.php?mod=viewthread&tid=1886046&extra=)

Dbms_xplan.display_awr way to get execution plan experiment and before misleading