怎样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.sql set feedback off timing off ver off pro 'general,outline,starts' pro acc 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 on undef type 測试例如以下： SQL> select * from a; ID NAME ---------- ---------- 1 a1 2 a2 3 a3 4 a4 5 a5 SQL> 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.04 SQL> @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.01 SQL> @getplan 'general,outline,starts' Enter value for plan type:outline --这里输入outline 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 | -------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$2 / B@SEL$2 2 - SEL$1 / A@SEL$1 Outline 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 off SQL> ALTER SESSION SET STATISTICS_LEVEL=ALL; Session altered. Elapsed: 00:00:00.00 SQL> 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 a5 5 rows selected. Elapsed: 00:00:00.03 SQL> @getplan 'general,outline,starts' Enter value for plan type:starts --这里输入starts PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------- SQL_ID 8rv825dykpx1m, child number 0 ------------------------------------- select a.*,(select name from b where b.id=a.id) from a Plan 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:starts PLAN_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 on SQL> 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 a5 5 rows selected. Elapsed: 00:00:00.03 SQL> @getplan 'general,outline,starts' Enter value for plan type:starts PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------- SQL_ID 9babjv8yq8ru3, child number 0 BEGIN 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)