获得执行计划

参考盖国强《深入解析oracle》

2.1 v$sql_plan
通过这个视图，可以获取正在执行中或者仍然缓存着的 SQL 执行计划
通过 HASH_VALUE（可以通过 V$SESSION 或者 V$SQL、V$SQL_PLAN 视图获得 SQL的 HASH_VALUE）输入来获取 SQL 及其执行计划：
SQL> alter session set sql_trace=true;

Session altered.

SQL> select * from test.worker;

WNO       WNAME
--------- --------------------
001       adam
此时不断开会话，进入udump目录，在相应trc文件中可以发现如下内容
PARSING IN CURSOR #20 len=25 dep=0 uid=0 oct=3 lid=0 tim=1468637238547630 hv=3081506255 ad='a9861100'
select * from test.worker
END OF STMT
得到HASH_VALUE值
create table t as select * from v$sql_plan where hash_value=3081506255;
查看执行计划
SQL> set linesize 300
SQL> select operation,OBJECT_NAME,BYTES,COST from t;
OPERATION                                                    OBJECT_NAME                         BYTES       COST
------------------------------------------------------------ ------------------------------ ---------- ----------
SELECT STATEMENT                                                                                                3
TABLE ACCESS                                                 WORKER                                 33          3

2.2 EXPLAIN PLAN FOR 与 DBMS_XPLAN
EXPLAIN PLAN 命令可以在后台对 SQL 进行解析，并将 SQL 执行计划加载到执行计划表中（默认名称为 PLAN_TABLE）
执行DBMS_XPLAN.DISPLAY功能需要对V$SESSION、V$SQL、V$SQL_PLAN 、V$SQL_PLAN_STATISTICS_ALL 具有访问权限，授权语句如下
grant select on v_$session to scott;
grant select on v_$sql_plan to scott;
grant select on v_$sql to scott;

SQL> explain plan for select * from test.worker;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 435679406

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |    33 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| WORKER |     1 |    33 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Note
-----

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   - dynamic sampling used for this statement
再看diaplay_cursor的输出
SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7yhu6xmqzq6fm, child number 0
-------------------------------------
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)

Plan hash value: 2137789089

-------------------------------------------------------------
| Id  | Operation                         | Name    | Cost  |
-------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |    24 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |       |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------

Note
-----
   - cpu costing is off (consider enabling it)

可以通过 V$SESSION 或 V$SQL 等视图来获取不同会话的 SQL_ID 以及 SQL_CHILD_NUMBER 来获得其 SQL 执行计划
某些 SQL 的执行计划可能老化无法获取
SQL> select sid,username,sql_id,sql_child_number from v$session where sql_id is not null;

       SID USERNAME                       SQL_ID        SQL_CHILD_NUMBER
---------- ------------------------------ ------------- ----------------
      1623                                4gd6b1r53yt88                0
      1644 SYS                            gyhjra6q6xc8m                0
      1646                                4gd6b1r53yt88                0

SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('gyhjra6q6xc8m',0,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gyhjra6q6xc8m, child number 0
-------------------------------------
select sid,username,sql_id,sql_child_number from v$session where sql_id
is not null

Plan hash value: 3733760267

---------------------------------------------------------------------------------
| Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                 |       |       |     1 (100)|

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS            |                 |     1 |   116 |     0   (0)|
|*  2 |   FIXED TABLE FULL       | X$KSUSE         |     1 |   103 |     0   (0)|
|*  3 |   FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |    13 |     0   (0)|
---------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5C160134
   2 - SEL$5C160134 / S@SEL$3
   3 - SEL$5C160134 / E@SEL$3

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("S"."KSUSESQI" IS NOT NULL AND
              "S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND
              BITAND("S"."KSUSEFLG",1)<>0))
   3 - filter("S"."KSUSEOPC"="E"."INDX")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

   1 - "S"."INDX"[NUMBER,22], "S"."KSUUDLNA"[VARCHAR2,30],
       "S"."KSUSESQI"[VARCHAR2,13], "S"."KSUSESCH"[NUMBER,22]
   2 - "S"."INDX"[NUMBER,22], "S"."INST_ID"[NUMBER,22],
       "S"."KSSPAFLG"[NUMBER,22], "S"."KSUUDLNA"[VARCHAR2,30],
       "S"."KSUSEFLG"[NUMBER,22], "S"."KSUSESQI"[VARCHAR2,13],
       "S"."KSUSESCH"[NUMBER,22], "S"."KSUSEOPC"[NUMBER,22]
   3 - "E"."INDX"[NUMBER,22]


42 rows selected.

2.3通过AWR获取SQL执行计划
select * from table(dbms_xplan.display_awr('4gd6b1r53yt88'));
通过 dbms_xplan.display_awr 函数获取的 SQL 执行计划来自 dba_hist_sql_plan 视图，通过历史数据记录，甚至一些被老化的 SQL 执行计划仍然可以被查询到