獲取執行計劃的方法

獲取執行計劃的方法

文章版權所有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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14710393/viewspace-1084315/，如需轉載，請註明出處，否則將追究法律責任。