Use AWR to view SQL Execution plans

Use AWR to view the SQL Execution Plan. Find the problematic SQL statement in AWR and want to know the specific execution plan of the SQL statement, so I used the SQL id obtained in the AWR report to query several dynamic performance views such as V $ SQL, however, it is found that no corresponding SQL id record is found in the V $ SQL or V $ SQL _PLAN view. Generally, these statements have been replaced from the shared pool. In this case, we can use the DBMS_XPLAN.DISPLAY_AWR stored procedure to extract information about the SQL statements recorded in the Oracle Automatic Workload Repository Automatic Load Repository, for example:

SYS @ orcl> select/* extrace_awr */count (*) from redo_test; COUNT (*) ---------- explain 495sys @ orcl> select SQL _id from v $ SQL where SQL _text like '% extrace_awr %' and SQL _text not like '% like %'; SQL _ID ----------- response @ orcl> exec cancel (); PL/SQL procedure successfully completed. SYS @ orcl> select * from table (dbms_xplan.display_cursor ('7d0r6vd8yccuy', 0); PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------SQL_ID 7d0r6vd8yccuy, child number 0 distinct select/* extrace_awr */count (*) from redo_testPlan hash value: 4170294667 bytes | Id | Operation | Name | Rows | Cost (% CPU) | Time | percent | 0 | select statement | 874 (100) | 1 | sort aggregate | 1 | 2 | table access full | REDO_TEST | 189K | 874 (1) | 00:00:11 | notice Note ------dynamic sampling used for this statement (level = 2) # records in dynamic views such as v $ SQL/V $ SQL _PLAN disappear after the Shared Pool is scrubbed, # Note that if the shared pool is washed out before the AWR snapshot is automatically generated, the SQL Execution statistics may be lost. SYS @ orcl> alter system flush shared_pool; System altered. SYS @ orcl> select * from table (dbms_xplan.display_cursor ('7d0r6vd8yccuy', 0); PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------SQL_ID: 7d0r6vd8yccuy, child number: 0 cannot be found # You can use the dbms_xplan.display_awr stored procedure to capture the execution plan SYS @ orcl> select * from table (dbms_xplan.display_awr ('7d0r6vd8yccuy') corresponding to the SQL _ID ')); PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------SQL_ID 7d0r6vd8yccuy ------------------ select/* extrace_awr */count (*) from redo_testPlan hash value: 4170294667 bytes | Id | Operation | Name | Rows | Cost (% CPU) | Time | ------------------------------------------------------------------------ | 0 | select statement | 874 (100) | 1 | sort aggregate | 1 | 2 | table access full | REDO_TEST | 189K | 874 (1) | 00:00:11 | notice Note ------dynamic sampling used for this statement (level = 2) SYS @ orcl> select * from table (dbms_xplan.display_awr ('7d0r6vd8yccuy', null, null, 'all'); PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------SQL_ID 7d0r6vd8yccuy -------------------- select/* extrace_awr */count (*) from redo_testPlan hash value: 4170294667 bytes | Id | Operation | Name | Rows | Cost (% CPU) | Time | ------------------------------------------------------------------------ | 0 | select statement | 874 (100) | 1 | sort aggregate | 1 | 2 | table access full | REDO_TEST | 189K | 874 (1) | 00:00:11 | ------------------------------------------------------------------------ Query Block Name/Object Alias (identified by operation id ): ----------------------------------------------------------- 1-SEL $1 2-SEL $1/REDO_TEST @ SEL $ 1Note ------dynamic sampling used for this statement (level = 2) # You can also directly observe the statements SYS @ orcl> select operation, options, object_name from dba_hist_plan/dba_hist_sqltext and other awr history views corresponding to the SQL ID from the awr history view '; operation options OBJECT_NAME ------------------------------ ------------------------------- select statementsort aggregatetable access full REDO_TEST