如何获取某个sql语句的执行计划---方法一
使用DBMS_XPLAN.DISPLAY_CURSOR() 包可以获取之前执行的sql语句的执行计划，这是set autotrace on 所不能做到的
eg：执行一个语句：
SELECT e.last_name, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id =d.department_id；
获取这个sql语句的sql_id：
SQL> SELECT SQL_ID, SQL_TEXT FROM V$SQL
2 WHERE SQL_TEXT LIKE '%SELECT e.last_name,%' ; 注意like后子句的内容
SQL_ID SQL_TEXT
------------- ------------------------------
57wyj8q1usn9b SELECT SQL_ID, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%SELECT e.
fq57yxn3z59mg SELECT e.last_name, d.department_name FROM hr.employees e, hr.depa
通过 DBMS_XPLAN.DISPLAY_CURSOR()包获取某个sql语句的执行计划
fq57yxn3z59mg--------sql_id
SQL> SELECT PLAN_TABLE_OUTPUT FROM
2 TABLE(DBMS_XPLAN.DISPLAY_CURSOR('fq57yxn3z59mg'));
PLAN_TABLE_OUTPUT
--------------------------------------------
SQL_ID fq57yxn3z59mg, child number 0
-------------------------------------
SELECT e.last_name, d.department_name FROM hr.employees e, hr.departments d WHER
e.department_id =d.department_id
Plan hash value: 4201152682
--------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
--------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)
PLAN_TABLE_OUTPUT
--------------------------------------------
| 1 | NESTED LOOPS | | 106 | 2862 | 4 (0)
| 2 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1177 | 3 (0)
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0)
|* 4 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)
--------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
已选择22行。
这里介绍另一种方法也可以使用AWR来获取sql的执行计划，但个人觉得使用性不大，有兴趣的可以了解下。
1. Execute the SQL statement.
SQL> select /* example */ *
2> from hr.employees natural join hr.departments;
2. Query V$SQL_TEXTto obtain the SQL_ID .
SQL> select sql_id, sql_text from v$SQL
2> where sql_text like '%example%';
SQL_ID SQL_TEXT
------------- -------------------------------------------
F8tc4anpz5cdb select sql_id, sql_text from v$SQL …
454rug2yva18w select /* example */ * from …
3. Using the SQL_ID , verify that this statement has been captured in the DBA_HIST_SQLTEXT
dictionary view. If the query does not return rows, then it indicates that the statement has not yet
been loaded in the AWR.
SQL> SELECT SQL_ID, SQL_TEXT FROM dba_hist_sqltext WHERE SQL_ID ='
454rug2yva18w';
no rows selected
You can take a manual AWR snapshot rather than wait for the next snapshot (which occurs every
hour). The SQL may not be captured unless it is in the topnsqlrange, so you can force all the SQL
statements to be captured by changing the topnsqlrange with the
MODIFY_SNAPSHOT_SETTING procedure. Then check to 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 |
----------------------------------------------