获取执行计划的方法

文章版权所有Jusin Hao（luckyfriends），支持原创，转载请注明。

在sqlplus中执行：

方法1）

取出你要查的sql的相关信息（Oracle9i没有执行计划的cursor，要通过这样的查询来查看其计划）：

Select a.Sql_Text, a.Sql_Fulltext, a.Sql_Id, a.Hash_Value, a.Disk_Reads, a.Buffer_Gets, a.Plan_Hash_Value, a.Address,

a.Child_Number

From V$sql a

Where a.Sql_Text Like '%WA_PERIODSTATE.CLASSID%' Or a.Sql_Text Like '%wa_periodstate.classid%';

通过上面查到的Hash_Value和Address去执行计划

Select * From v$sql_plan a Where a.HASH_VALUE='3325151165' And a.ADDRESS='33D80198';

方法2）

取出你要查的sql的相关信息：

Select a.Sql_Text, a.Sql_Fulltext, a.Sql_Id, a.Hash_Value, a.Disk_Reads, a.Buffer_Gets, a.Plan_Hash_Value, a.Address,

a.Child_Number

From V$sql a

Where a.Sql_Text Like '%USER_TABLES%' Or a.Sql_Text Like '%user_tables%';

通过上面查到的Sql_Id和Child_Number去执行计划
Select * From Table(dbms_xplan.display_cursor('Sql_Id','Child_Number'));

select * from table(dbms_xplan.display_cursor(‘’));

select * from table(dbms_xplan.display_awr(‘’));

select * from table(dbms_xplan.display_sqlset(‘’));

方法3）

SQL> set timing on

SQL> set linesize 1600

SQL> set feedback off pagesize 0 tab off

SQL> set autotrace traceonly

SQL>select wa_periodstate.classid, wa_period.cyear, wa_period.cperiod, wa_period.cstartdate, wa_period.cenddate from wa_periodstate, wa_period where wa_periodstate.pk_periodset = wa_period.pk_wa_period and wa_periodstate.icheckflag = 0 and wa_periodstate.iaccountmark = 0 and wa_period.cenddate >= :1 and wa_periodstate.pk_corp = :2 and ( wa_period.cyear || wa_period.cperiod ) = ( select min ( wa_period.cyear || wa_period.cperiod ) from wa_periodstate period, wa_period where period.pk_periodset = wa_period.pk_wa_period and wa_periodstate.classid = period.classid and period.icheckflag = 0 and period.iaccountmark = 0 ) order by cstartdate ;

返回的内容就是执行计划

方法4）

4-1）先执行

Explain Plan For

Select Wa_Periodstate.Classid, Wa_Period.Cyear, Wa_Period.Cperiod, Wa_Period.Cstartdate, Wa_Period.Cenddate

From Wa_Periodstate, Wa_Period

Where Wa_Periodstate.Pk_Periodset = Wa_Period.Pk_Wa_Period And Wa_Periodstate.Icheckflag = 0 And

Wa_Periodstate.Iaccountmark = 0 And Wa_Period.Cenddate >= '2009-08-19' And Wa_Periodstate.Pk_Corp = '1240' And

(Wa_Period.Cyear || Wa_Period.Cperiod) =

(Select Min(Wa_Period.Cyear || Wa_Period.Cperiod)

From Wa_Periodstate Period, Wa_Period

Where Period.Pk_Periodset = Wa_Period.Pk_Wa_Period And Wa_Periodstate.Classid = Period.Classid And

Period.Icheckflag = 0 And Period.Iaccountmark = 0)

Order By Cstartdate

4-2）再执行Select * From Table(dbms_xplan.display());把返回的结果给我。

形如这样

方法5)

gather_plan_statistics 可以多显示一列

dbms_sqltune.report_sql_moitor

EM里的sql monitor