Oracle 執(zhí)行計(jì)劃（Explain Plan） 說明
一、通過PL/SQL Dev工具    1、直接File->New->Explain Plan Window，在窗口中執(zhí)行sql可以查看計(jì)劃結(jié)果。其中，Cost表示cpu的消耗，單位為n%，Cardinality表示執(zhí)行的行數(shù)，等價(jià)Rows。    2、先執(zhí)行 EXPLAIN PLAN FOR   select * from tableA where paraA=1，再 select * from table(DBMS_XPLAN.DISPLAY)便可以看到oracle的執(zhí)行計(jì)劃了，看到的結(jié)果和1中的一樣，所以使用工具的時(shí)候推薦使用1方法。注意：PL/SQL Dev工具的Command window中不支持set autotrance on的命令。還有使用工具方法查看計(jì)劃看到的信息不全，有些時(shí)候我們需要sqlplus的支持。 
二、通過sqlplus1．最簡(jiǎn)單的辦法Sql> set autotrace onSql> select * from dual;　　執(zhí)行完語句后，會(huì)顯示explain plan 與 統(tǒng)計(jì)信息?！　∵@個(gè)語句的優(yōu)點(diǎn)就是它的缺點(diǎn)，這樣在用該方法查看執(zhí)行時(shí)間較長(zhǎng)的sql語句時(shí)，需要等待該語句執(zhí)行成功后，才返回執(zhí)行計(jì)劃，使優(yōu)化的周期大大增長(zhǎng)。如果不想執(zhí)行語句而只是想得到執(zhí)行計(jì)劃可以采用：Sql> set autotrace traceonly這樣，就只會(huì)列出執(zhí)行計(jì)劃，而不會(huì)真正的執(zhí)行語句，大大減少了優(yōu)化時(shí)間。雖然也列出了統(tǒng)計(jì)信息，但是因?yàn)闆]有執(zhí)行語句，所以該統(tǒng)計(jì)信息沒有用處，如果執(zhí)行該語句時(shí)遇到錯(cuò)誤，解決方法為：(1）在要分析的用戶下：Sqlplus > @ ?dbmsadminutlxplan.sql(2) 用sys用戶登陸Sqlplus > @ ?sqlplusadminplustrce.sqlSqlplus > grant plustrace to user_name;- - user_name是上面所說的分析用戶 　2．用explain plan命令(1) sqlplus > explain plan for select * from testdb.myuser(2) sqlplus > select * from table(dbms_xplan.display);　　上面這2種方法只能為在本會(huì)話中正在運(yùn)行的語句產(chǎn)生執(zhí)行計(jì)劃，即我們需要已經(jīng)知道了哪條語句運(yùn)行的效率很差，我們是有目的只對(duì)這條SQL語句去優(yōu)化。其實(shí)，在很多情況下，我們只會(huì)聽一個(gè)客戶抱怨說現(xiàn)在系統(tǒng)運(yùn)行很慢，而我們不知道是哪個(gè)SQL引起的。此時(shí)有許多現(xiàn)成的語句可以找出耗費(fèi)資源比較多的語句，如：SELECT ADDRESS, substr(SQL_TEXT,1,20) Text, buffer_gets, executions,buffer_gets/executions AVG  FROM  v$sqlareaWHERE executions>0 AND buffer_gets > 100000   ORDER BY 5;ADDRESS      TEXT                     BUFFER_GETS    EXECUTIONS        AVG-------- ---------------------------------------- ----------- ---------- ------------------------------------------------------------66D83D64   select t.name,  (sel               421531        60104             7.0133601766D9E8AC   select t.schema, t.n               1141739        2732             417.91325066B82BCC   select s.synonym_nam             441261        6                73543.5　　從而對(duì)找出的語句進(jìn)行進(jìn)一步優(yōu)化。當(dāng)然我們還可以為一個(gè)正在運(yùn)行的會(huì)話中運(yùn)行的所有SQL語句生成執(zhí)行計(jì)劃，這需要對(duì)該會(huì)話進(jìn)行跟蹤，產(chǎn)生trace文件，然后對(duì)該文件用tkPRof程序格式化一下，這種得到執(zhí)行計(jì)劃的方式很有用，因?yàn)樗渌~外信息，如SQL語句執(zhí)行的每個(gè)階段(如Parse、Execute、Fetch)分別耗費(fèi)的各個(gè)資源情況(如CPU、DISK、elapsed等)。 3、啟用SQL_TRACE跟蹤所有后臺(tái)進(jìn)程活動(dòng)：全局參數(shù)設(shè)置： .OracleHome/admin/SID/pfile中指定: SQL_TRACE = true (10g)    當(dāng)前session中設(shè)置：    SQL> alter session set SQL_TRACE=true;    SQL> select * from dual;    SQL> alter session set SQL_TRACE=false;    對(duì)其他用戶進(jìn)行跟蹤設(shè)置：    SQL> select sid,serial#,username from v$session where username='XXX';       SID    SERIAL# USERNAME    ------ ---------- ------------------       127      31923 A       128      54521 B    開啟跟蹤:SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,true);    關(guān)閉跟蹤:SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,false);    然后使用oracle自帶的tkprof命令行工具格式化跟蹤文件。4、使用10046事件進(jìn)行查詢：    10046事件級(jí)別：    Lv1  - 啟用標(biāo)準(zhǔn)的SQL_TRACE功能,等價(jià)于SQL_TRACE    Lv4  - Level 1 + 綁定值(bind values)    Lv8  - Level 1 + 等待事件跟蹤    Lv12 - Level 1 + Level 4 + Level 8    全局設(shè)定：    OracleHome/admin/SID/pfile中指定: EVENT="10046 trace name context forever,level 12"    當(dāng)前session設(shè)定：    開啟:SQL> alter session set events '10046 trace name context forever, level 8';    關(guān)閉:SQL> alter session set events '10046 trace name context off';    對(duì)其他用戶進(jìn)行設(shè)置：    SQL> select sid,serial#,username from v$session where username='XXX';       SID    SERIAL# USERNAME    ------ ---------- ------------------       127      31923 A    SQL> exec dbms_system.set_ev(127,31923,10046,8,'A');5、使用tkprof格式化跟蹤文件： (根據(jù)下面SQL語句得到的文件都不存在該目錄下,郁悶啊,懵懂啊...)一般，一次跟蹤可以分為以下幾步：1、界定需要跟蹤的目標(biāo)范圍，并使用適當(dāng)?shù)拿顔⒂盟韪櫋?、經(jīng)過一段時(shí)間后，停止跟蹤。此時(shí)應(yīng)該產(chǎn)生了一個(gè)跟蹤結(jié)果文件。3、找到跟蹤文件，并對(duì)其進(jìn)行格式化，然后閱讀或分析。--使用一下SQL找到當(dāng)前session的跟蹤文件：SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_namefrom( select p.spid from v$mystat m,v$session s, v$process pwhere m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,( select t.instance from v$thread t,v$parameter vwhere v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,( select value from v$parameter where name = 'user_dump_dest' ) d;-- 其它用戶的 session    SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_name    from    ( select p.spid from v$session s, v$process p          where s.sid= '27' and s. SERIAL#= '30' and p.addr = s.paddr) p,    ( select t.instance from v$thread t,v$parameter v          where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,    ( select value from v$parameter where name = 'user_dump_dest' ) d; --查找后使用tkprof命令,將TRACE文件格式為到D盤的explain_format.txt文件中SQL> $tkprof d:/oracle/admin/FZLGFM/udump/fzlgfm_ora_3468.trc  d:/explain_format.txt文件內(nèi)容大致如下(看不太懂....懵懂啊.....天啊....神啊.....過幾時(shí)就懂了/////////////)TKPROF: Release 9.2.0.1.0 - Production on 星期二 4月 20 13:59:20 2010Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.Trace file: d:/oracle/admin/FZLGFM/udump/fzlgfm_ora_3468.trcSort options: default********************************************************************************count    = number of times OCI procedure was executedcpu      = cpu time in seconds executingelapsed  = elapsed time in seconds executingdisk     = number of physical reads of buffers from diskquery    = number of buffers gotten for consistent readcurrent  = number of buffers gotten in current mode (usually for update)rows     = number of rows processed by the fetch or execute call********************************************************************************alter session set events '10046 trace name context forever, level 8' call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        0      0.00       0.00          0          0          0           0Execute      1      0.00       0.00          0          0          0           0Fetch        0      0.00       0.00          0          0          0           0------- ------  -------- ---------- ---------- ---------- ----------  ----------total        1      0.00       0.00          0          0          0           0 Misses in library cache during parse: 0Misses in library cache during execute: 1Optimizer goal: CHOOSEParsing user id: SYS