如何获取执行计划

如何oracle的获取执行计划1.获取普通执行计划，效果类似于先执行set autot on exp;然后执行sql。 explan plan for your_sql; select * from table(dbms_xplan.display);2.获取具有outl本文来源gaodai$ma#com搞$代*码6网ine信息的执行计划，用sqlprofile调优时非常有用，或者用这个执行计划了

如何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)   

喜欢 (0)赏

[搞代码]

分享 (0)