如何获取某个sql语句的执行计划---方法一(二)
o see if it has been captured in
DBA_HIST_SQLTEXT :
/*在这里需要更改awr的默认设置，使其能捕获所有sql语句*/
SQL> exec –
2> DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
-3> topnsql => 'MAXIMUM');
PL/SQL procedure successfully completed.
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
PL/SQL procedure successfully completed.
/*需要把之前改的topnsql改回来，以避免以后awr的报告捕获太多无用的sql */
SQL> exec –
2> DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(-
3> topnsql => 'DEFAULT');
PL/SQL procedure successfully completed.
SQL> SELECT SQL_ID, SQL_TEXT FROM dba_hist_sqltext WHERE SQL_ID ='
454rug2yva18w';
SQL_ID SQL_TEXT
-------------- -------------------------------
454rug2yva18w select /* example */ * from …
4. Use the DBMS_XPLAN.DISPLAY_AWR () function to retrieve the execution plan:
SQL>SELECT PLAN_TABLE_OUTPUT FROM TABLE
(DBMS_XPLAN.DISPLAY_AWR('454rug2yva18w’));
PLAN_TABLE_OUTPUT
---------------------------------------------SQL_ID 454rug2yva18w
--------------------select /* example */ * from hr.employees natural join hr.departments
Plan hash value: 2052257371
----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 7 (100)| |
| 1 | HASH JOIN | | 11 | 968 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPARTMENTS | 11 | 220 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 |
----------------------------------------------------------