如何擷取執行計畫，擷取執行計畫

如何擷取執行計畫，擷取執行計畫

如何oracle的擷取執行計畫1.擷取普通執行計畫，效果類似於先執行set autot on exp;然後執行sql。  explan plan for your_sql;  select * from table(dbms_xplan.display);2.擷取具有outline資訊的執行計畫，用sqlprofile調優時非常有用，或者用這個執行計畫瞭解更多oracle內部的hint  explan plan for your_sql;  select * from table(dbms_xplan.display(null, null,'advanced -projection'))3.真實的執行計畫，可以看到實際的 Starts（執行次數） | E-Rows（估算的返回行數） | A-Rows（實際的返回行數）  ALTER SESSION SET STATISTICS_LEVEL=ALL;   execute your_sql;  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'))  那麼這3中擷取執行計畫的方式可以寫到一個指令碼getplan.sql，用的時候非常方便。--getplan.sqlset feedback off timing off ver offpro 'general,outline,starts'proacc type prompt 'Enter value for plan type:' default 'general'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')) where '&&type'='starts';select * from table(dbms_xplan.display) where '&&type'='general';select * from table(dbms_xplan.display(null, null,'advanced -projection')) where '&&type'='outline';set feedback on timing on ver onundef type測試如下：SQL> select * from a;        ID NAME---------- ----------         1 a1         2 a2         3 a3         4 a4         5 a5SQL> select * from b;        ID NAME---------- ----------         1 b1         2 b2                           --執行計畫1：普通執行計畫SQL> explain plan for select a.*,(select name from b where b.id=a.id) from a;Explained.Elapsed: 00:00:00.04SQL> @getplan'general,outline,starts'Enter value for plan type:                ----這裡輸入general或斷行符號PLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------Plan hash value: 3653839899--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     5 |   100 |     3   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| B    |     1 |    20 |     3   (0)| 00:00:01 ||   2 |  TABLE ACCESS FULL| A    |     5 |   100 |     3   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("B"."ID"=:B1)Note-----   - dynamic sampling used for this statement         --執行計畫2：outline執行計畫SQL> explain plan for select a.*,(select name from b where b.id=a.id) from a;Explained.Elapsed: 00:00:00.01SQL> @getplan'general,outline,starts'Enter value for plan type:outline           --這裡輸入outlinePLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------Plan hash value: 3653839899--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     5 |   100 |     3   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| B    |     1 |    20 |     3   (0)| 00:00:01 ||   2 |  TABLE ACCESS FULL| A    |     5 |   100 |     3   (0)| 00:00:01 |--------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - SEL$2 / B@SEL$2   2 - SEL$1 / A@SEL$1Outline Data-------------  /*+      BEGIN_OUTLINE_DATA      FULL(@"SEL$2" "B"@"SEL$2")      FULL(@"SEL$1" "A"@"SEL$1")      OUTLINE_LEAF(@"SEL$1")      OUTLINE_LEAF(@"SEL$2")      ALL_ROWS      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')      IGNORE_OPTIM_EMBEDDED_HINTS      END_OUTLINE_DATA  */Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("B"."ID"=:B1)Note-----   - dynamic sampling used for this statement   --執行計畫3：real執行計畫SQL> set serveroutput offSQL> ALTER SESSION SET STATISTICS_LEVEL=ALL;Session altered.Elapsed: 00:00:00.00SQL> select a.*,(select name from b where b.id=a.id) from a;        ID NAME       (SELECTNAM---------- ---------- ----------         1 a1         b1         2 a2         b2         3 a3         4 a4         5 a55 rows selected.Elapsed: 00:00:00.03SQL> @getplan'general,outline,starts'Enter value for plan type:starts                  --這裡輸入startsPLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------SQL_ID  8rv825dykpx1m, child number 0-------------------------------------select a.*,(select name from b where b.id=a.id) from aPlan hash value: 3653839899------------------------------------------------------------------------------------| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |------------------------------------------------------------------------------------|*  1 |  TABLE ACCESS FULL| B    |      5 |      1 |      2 |00:00:00.01 |      35 ||   2 |  TABLE ACCESS FULL| A    |      1 |      5 |      5 |00:00:00.01 |       8 |------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("B"."ID"=:B1)Note-----   - dynamic sampling used for this statement--注意：--第3種執行計畫不能多次擷取，只能執行1次，擷取一次，否則會擷取不到下面再次擷取一下試試：SQL> @getplan'general,outline,starts'Enter value for plan type:startsPLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------------SQL_ID  dvp8nn63wuhs8, child number 0-------------------------------------select * from table(dbms_xplan.display(null, null,'advanced -projection'))where 'starts'='outline'Plan hash value: 3440229843-------------------------------------------------------------------------------------| Id  | Operation                          | Name    | Starts | A-Rows |   A-Time   |-------------------------------------------------------------------------------------|*  1 |  FILTER                            |         |      1 |      0 |00:00:00.01 ||   2 |   COLLECTION ITERATOR PICKLER FETCH| DISPLAY |      0 |      0 |00:00:00.01 |   --第二次無法擷取真實的執行計畫-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter(NULL IS NOT NULL)Note-----   - rule based optimizer used (consider using cbo)      --第3種執行計畫要關掉set serveroutput off，否則也不能擷取執行計畫。測試如下：這裡和上面的測試是同一個會話，所以沒有再執行ALTER SESSION SET STATISTICS_LEVEL=ALL;了。SQL> set serveroutput onSQL> select a.*,(select name from b where b.id=a.id) from a;        ID NAME       (SELECTNAM---------- ---------- ----------         1 a1         b1         2 a2         b2         3 a3         4 a4         5 a55 rows selected.Elapsed: 00:00:00.03SQL> @getplan'general,outline,starts'Enter value for plan type:startsPLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------------------SQL_ID  9babjv8yq8ru3, child number 0BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;                    --也無法擷取真實的執行計畫NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0      Please verify value of SQL_ID and CHILD_NUMBER;      It could also be that the plan is no longer in cursor cache (check v$sql_plan)   

怎使用sqlplus或pl/sql developer擷取執行計畫

在PL/SQL DEVELOPER裡按F5
sqlplus中set autot trace
然後執行你sql語句！！
 
oracle 10g 語句的執行計畫怎獲得？

在PLSQL中選中要啟動並執行語句，按F5即可查看執行計畫咯