ホームページ >データベース >mysql チュートリアル >如何获取执行计划
如何oracle的获取执行计划1.获取普通执行计划,效果类似于先执行set autot on exp;然后执行sql。 explan plan for your_sql; select * from table(dbms_xplan.display);2.获取具有outline信息的执行计划,用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.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)