如何擷取執行計畫

EXPLAIN PLAN  - 通過該命令產生sql語句的執行計畫，但是該語句並沒有真正執行

1. 字典視圖- 通過oracle字典視圖我們可以查詢快取在記憶體中的已經執行的sql語句的執行計畫.

EXPLAIN PLAN command

V$SQL_PLAN

Automatic Workload Repository (AWR)

SQL Tuning Set (STS)

SQL Plan Baseline (SPM)

使用EXPLAIN PLAN

SQL> explain plan for select count(*) from products;已解釋。SQL> select * from table(dbms_xplan.display('','','basic'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------Plan hash value: 589338964------------------------------------------------------------------| Id  | Operation      | Name |------------------------------------------------------------------|   0 | SELECT STATEMENT      |  ||   1 |  SORT AGGREGATE       |  ||   2 |   BITMAP CONVERSION COUNT     |  ||   3 |    BITMAP INDEX FAST FULL SCAN| PRODUCTS_PROD_STATUS_BIX |------------------------------------------------------------------已選擇10行。

如果使用綁定變數，explain plan的輸出結果

SQL> var v numberSQL> exec :v := 145PL/SQL 過程已成功完成。SQL> explain plan for select count(*) from products where prod_id = :v;已解釋。SQL> select * from table(dbms_xplan.display('','','TYPICAL +PEEKED_BINDS'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------Plan hash value: 2065297493----------------------------------------------------------------------------------| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------|   0 | SELECT STATEMENT   | |     1 |     4 |     0   (0)| 00:00:01 ||   1 |  SORT AGGREGATE    | |     1 |     4 |      |  ||*  2 |   INDEX UNIQUE SCAN| PRODUCTS_PK |     1 |     4 |     0   (0)| 00:00:01 |----------------------------------------------------------------------------------Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------------------------------------------   2 - access("PROD_ID"=TO_NUMBER(:V))  --進行了類型轉換已選擇14行。

使用V$SQL_PLAN

通過DBMS_XPLAN.DISPLAY_CURSOR我們可以訪問存放在V$SQL_PLAN 中的執行計畫，文法格式如下

FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE參數名稱       類型       輸入/輸出預設值?------------------------------ ----------------------- ------ -------- SQL_ID VARCHAR2IN     DEFAULT CURSOR_CHILD_NONUMBER(38)IN     DEFAULT FORMAT VARCHAR2IN     DEFAULT

如果省略SQL_ID,CURSOR_CHILD_NO則預設查詢當前session中最後執行的sql執行計畫

SQL> var v numberSQL> exec :v := 144PL/SQL 過程已成功完成。SQL> select count(*) from products where prod_id = :v;  COUNT(*)---------- 1SQL> select * from table(dbms_xplan.display_cursor('','','typical +PEEKED_BINDS'));PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------SQL_ID9gnda3r7m7jvw, child number 0-------------------------------------select count(*) from products where prod_id = :vPlan hash value: 2065297493-----------------------------------------------------------------------| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)|-----------------------------------------------------------------------|   0 | SELECT STATEMENT   | | | |     1 (100)||   1 |  SORT AGGREGATE    | |     1 |     4 |      |PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------|*  2 |   INDEX UNIQUE SCAN| PRODUCTS_PK |     1 |     4 |     0   (0)|-----------------------------------------------------------------------Peeked Binds (identified by position):--------------------------------------   1 - :V (NUMBER): 144   --在EXPLAIN PLAN時，是沒有該段內容的Predicate Information (identified by operation id):---------------------------------------------------PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------   2 - access("PROD_ID"=:V)已選擇24行。

通過AWR

與通過v$sql_plan相似，也可以通過awr來擷取執行計畫，採用的過程時DBMS_XPLAN.DISPLAY_AWR(),該函數的文法格式如下：

FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE參數名稱       類型       輸入/輸出預設值?------------------------------ ----------------------- ------ -------- SQL_ID VARCHAR2IN PLAN_HASH_VALUENUMBER(38)IN     DEFAULT DB_IDNUMBER(38)IN     DEFAULT FORMAT VARCHAR2IN     DEFAULT

樣本如下：

SQL> select * from table(dbms_xplan.display_awr('1v44r7vam2wbt'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID 1v44r7vam2wbt--------------------delete from WRH$_IOSTAT_FUNCTION tab where (:beg_snap <= tab.snap_idand    tab.snap_id <= :end_snap and dbid = :dbid)  andnot exists (select 1 from WRM$_BASELINE b     where(tab.dbid = b.dbid) and  (tab.snap_id >=b.start_snap_id) and      (tab.snap_id <=b.end_snap_id))Plan hash value: 1772041547PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------------------|   0 | DELETE STATEMENT       | | | |     4 (100)|  ||   1 |  DELETE        | WRH$_IOSTAT_FUNCTION | | |      |  ||   2 |   FILTER       | | | |      |  ||   3 |    INDEX RANGE SCAN       | WRH$_IOSTAT_FUNCTION_PK |     1 |    17 |     2   (0)| 00:00:01 ||   4 |     TABLE ACCESS BY INDEX ROWID| WRM$_BASELINE |     1 |    33 |     2   (0)| 00:00:01 ||   5 |      INDEX RANGE SCAN       | WRM$_BASELINE_PK |     1 | |     1   (0)| 00:00:01 |----------------------------------------------------------------------------------------------------------已選擇22行。

通過sqlplanbaseline

通過sqlplan baseline，也可以擷取執行計畫。sqlplan baseline是在11g中引入的新技術，主要用來支援SPM。下面看一下如何通過sqlplan baseline來擷取執行計畫。

SQL> alter session set optimizer_capture_sql_plan_baselines=true;會話已更改。SQL> select count(*) from t1;  COUNT(*)----------     74008SQL> /  COUNT(*)----------     74008SQL> select sql_handle,plan_name,accepted from dba_sql_plan_baselines where sql_text like 'select count(*) from t1';SQL_HANDLE       PLAN_NAME      ACC------------------------------ ------------------------------ ---SQL_e208a16bb98b6a04       SQL_PLAN_f4251dfwsquh4dcd11e45 YESSQL> select * from table(dbms_xplan.display_sql_plan_baseline('SQL_e208a16bb98b6a04'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL handle: SQL_e208a16bb98b6a04SQL text: select count(*) from t1----------------------------------------------------------------------------------------------------------------------------------------------------------------Plan name: SQL_PLAN_f4251dfwsquh4dcd11e45  Plan id: 3704692293Enabled: YES Fixed: NOAccepted: YES  Origin: AUTO-CAPTURE--------------------------------------------------------------------------------PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------Plan hash value: 129980005----------------------------------------------------------------------| Id  | Operation      | Name | Rows  | Cost (%CPU)| Time     |----------------------------------------------------------------------|   0 | SELECT STATEMENT      |      |   1 |  46   (0)| 00:00:01 ||   1 |  SORT AGGREGATE       |      |   1 |  |     ||   2 |   INDEX FAST FULL SCAN| I1   | 74008 |  46   (0)| 00:00:01 |----------------------------------------------------------------------已選擇20行。