SQL語句常用的優(yōu)化方法.ppt

1、背景：OLTP系統(tǒng)，ORACLE10G,作者: ZALBB,SQL語句常用的調(diào)優(yōu)方法,1 為什么要調(diào)優(yōu)SQL? 2 哪些SQL需要調(diào)優(yōu)? 3 如何獲取需要調(diào)優(yōu)的SQL? 4 如何手工調(diào)優(yōu)SQL? 5 另外一些調(diào)優(yōu)方法和工具。 6 11G在執(zhí)行計(jì)劃上的一些改進(jìn)。,目錄,為什么要調(diào)優(yōu)SQL？,通常來講，要打造高效快捷的應(yīng)用系統(tǒng)，需要從最初的業(yè)務(wù)需求 入手，在分析、整理出閉環(huán)的業(yè)務(wù)操作流程后，按照范式的要求，盡 量用簡單的數(shù)據(jù)結(jié)構(gòu)，來實(shí)現(xiàn)業(yè)務(wù)的運(yùn)行和流轉(zhuǎn)（可以考慮對(duì)基礎(chǔ)數(shù) 據(jù)作少量的數(shù)據(jù)冗余，以減少關(guān)聯(lián)）；同時(shí)，根據(jù)業(yè)務(wù)的需求，兼考 慮對(duì)歷史業(yè)務(wù)數(shù)據(jù)的遷移，只保留最近一段時(shí)期內(nèi)的數(shù)據(jù)，以便讓系 統(tǒng)輕

2、裝運(yùn)行。 但是，由于業(yè)務(wù)的復(fù)雜性，設(shè)計(jì)人員的知識(shí)、視野、前瞻性等的 局限，在系統(tǒng)結(jié)構(gòu)設(shè)計(jì)時(shí)，難以考慮周全；并且，由于開發(fā)人員的 水平參差不齊，編寫的代碼也存在缺陷。經(jīng)統(tǒng)計(jì)評(píng)估，排除系統(tǒng)結(jié)構(gòu) 設(shè)計(jì)不善導(dǎo)致的因素外，新的應(yīng)用系統(tǒng)，有80%的效率問題，是因?yàn)?低效的SQL導(dǎo)致，這就需要DBA找出這些低效的SQL，加以優(yōu)化。,例子,哪些SQL需要優(yōu)化？,運(yùn)行時(shí)間較長的SQL。 邏輯讀較高的SQL。 物理讀較高的SQL。,從哪里獲取需要調(diào)優(yōu)的SQL？,* AWR(ASH,ADDM)， 1 Elapsed Time(含CPU較高者) 2 Buffer Gets 3 Physical Reads,* EM，

3、 性能分析- SQL Tuning,* 當(dāng)前庫， 根據(jù)V$SESSION.LAST_CALL_ET，找到運(yùn)行時(shí)間 最長的進(jìn)程，獲取SQL_ID，再找出SQL語句和執(zhí)行計(jì)劃。,AWR上要關(guān)注的SQL項(xiàng),如何手工調(diào)優(yōu)SQL?,A 如何獲取語句的執(zhí)行計(jì)劃？ B 如何解讀執(zhí)行計(jì)劃中的執(zhí)行順序？ C SQL語句的調(diào)優(yōu)原則。 D 一些調(diào)優(yōu)常識(shí)。 E 手工調(diào)優(yōu)的粗略思路。 F 10046事件的使用方法。 G 兩個(gè)案例。,如何獲取語句的執(zhí)行計(jì)劃？,2 根據(jù)SQL_ID查詢， select * from table (dbms_xplan.display_cursor( 還有：advanced,typical,

4、serial,basic. v$session.sql_child_number=0,1,3 從視圖v$sql_plan 中獲取。,1 直接解析SQL語句. Explain plan for XXX； Select * from table(dbms_xplan.display);,如何解讀執(zhí)行計(jì)劃中的執(zhí)行順序？,在獲取SQL語句的執(zhí)行計(jì)劃后，這樣解讀執(zhí)行順序： * 對(duì)同一凹層，先上后下執(zhí)行， * 對(duì)不同凹層，先里后外執(zhí)行。,對(duì)于同一凹層， 先上后下,對(duì)于不同凹層，先里后外。所以先NL,后 hash。,真正的執(zhí)行順序,執(zhí)行順序:3,5,4,2,7,6,1,0,SQL 語句的調(diào)優(yōu)原則,在一個(gè)OL

5、TP系統(tǒng)里，優(yōu)化SQL語句的原則，就是盡量 減少數(shù)據(jù)的讀取。調(diào)優(yōu)的目的，實(shí)際是設(shè)法讓語句在執(zhí)行過 程中，盡可能地只讀取必要的數(shù)據(jù)，不讀或盡量少讀不符合 要求的數(shù)據(jù)。,SQL調(diào)優(yōu)中的一些常識(shí)執(zhí)行計(jì)劃中涉及的一些概念,* 不論SQL中讀取多少個(gè)表，在執(zhí)行過程中，每次都是兩個(gè)表/結(jié) 果集操作，得到新的結(jié)果后，再和下一個(gè)表/結(jié)果集操作， 直到結(jié)束。 在一個(gè)多表關(guān)聯(lián)的執(zhí)行計(jì)劃中，必須包括這3要素： * 表/對(duì)象/數(shù)據(jù)集的讀取順序（ join order ）。 * 數(shù)據(jù)的讀取方法（ access path ）。 * 表/數(shù)據(jù)的關(guān)聯(lián)方法（join method）。 這3個(gè)要素是判斷執(zhí)行計(jì)劃優(yōu)秀與否的關(guān)鍵。

6、* 可選擇性(Selectivity) ，=0 and =1。 * 預(yù)估記錄數(shù)(Cardinality) ，表/視圖/操作后的結(jié)果集。 * 開銷(Cost) ，CBO選擇最佳執(zhí)行計(jì)劃的標(biāo)準(zhǔn)：越低越好。,ACCESS和FILTER的區(qū)別,在解析出SQL語句的執(zhí)行計(jì)劃后，在執(zhí)行計(jì)劃的末尾，通常會(huì)出現(xiàn) 這些信息：,FILTER 指按照某個(gè)條件過濾數(shù)據(jù)， ACCESS 指按照某個(gè)條件/關(guān)系獲取數(shù)據(jù)，,在本文中，這樣定義此詞匯,關(guān)聯(lián)條件：where a.col1 =b.col1， 過濾條件：where a.col1=103（常量），,關(guān)聯(lián)條件，和過濾條件都稱為約束條件。,手工調(diào)優(yōu)的粗略思路,1 獲取SQ

7、L的執(zhí)行計(jì)劃。 2 判斷當(dāng)前的執(zhí)行計(jì)劃是否正常： 手工計(jì)算Where語句后各過濾條件(非關(guān)聯(lián)條件)的預(yù)估數(shù)值，找出最強(qiáng) 的過濾條件（過濾后剩余數(shù)據(jù)最少的條件）。一般來講，若語句中各對(duì) 象的統(tǒng)計(jì)信息準(zhǔn)確，CBO經(jīng)過計(jì)算后，基本上都是從過濾條件最強(qiáng)的表 開始，判斷執(zhí)行計(jì)劃是否從此條件開始。 3 檢查執(zhí)行計(jì)劃中第1步的預(yù)估值，是否與實(shí)際值相近。否，轉(zhuǎn)步驟7。 4 根據(jù)過濾條件判斷，數(shù)據(jù)的讀取方式是否合適（讀表，讀索引，或根據(jù) 索引返回原表獲?。?5 找出與第1步要執(zhí)行的表存在關(guān)聯(lián)關(guān)系的表，根據(jù)其過濾后的結(jié)果集判斷 ，兩表間的關(guān)聯(lián)方法是否合適（也可能和一結(jié)果集關(guān)聯(lián)）。 6 再根據(jù)其它關(guān)聯(lián)條件，找出最

8、近的表/結(jié)果集和上述結(jié)果集，作關(guān)聯(lián)。如 估算不準(zhǔn)，可手工計(jì)算與剩下的各條件關(guān)聯(lián)后的結(jié)果集情況，再判斷。,7 若覺得計(jì)劃中的預(yù)估值與手工計(jì)算的結(jié)果相差太大，可以先對(duì)SQL中涉及到 的表作統(tǒng)計(jì)，或者，有針對(duì)性對(duì)約束條件中的字段/索引作統(tǒng)計(jì)。在這過程 中，分析數(shù)據(jù)的分布屬性，可考慮建索引，建分區(qū)等方法，盡 量讓執(zhí)行計(jì) 劃只讀取必要的數(shù)據(jù)。 8 在上述各環(huán)節(jié)的判斷過程中，可使用10046事件跟蹤部分SQL的執(zhí)行過程中 的運(yùn)行效率，判斷是否合理。還可使用HINT來改變執(zhí)行計(jì)劃中，表/結(jié)果集 的讀取順序，關(guān)聯(lián)方法，數(shù)據(jù)的讀取方法等。對(duì)比不同執(zhí)行計(jì)劃的效率，分 析原因，再調(diào)整改進(jìn)，包括改寫成等效的SQL。

9、9 必要時(shí)，可以考慮對(duì)不清晰，不符合判斷的部分SQL作10053事件分析。 對(duì)于由多個(gè)動(dòng)態(tài)視圖組成的復(fù)雜的語句，若發(fā)覺整個(gè)語句效率比拆分執(zhí)行 的總的耗時(shí)要多，可以先單獨(dú)拆分找出各個(gè)視圖的最佳執(zhí)行計(jì)劃，之后設(shè) 法確保整個(gè)語句按照拆分時(shí)各個(gè)模塊的執(zhí)行計(jì)劃執(zhí)行。 以上是我分析SQL語句的執(zhí)行計(jì)劃時(shí)的大致思路，現(xiàn)實(shí)中的情況千變?nèi)f化 ，可能與上面的思路稍有不同，但大體都是這樣，都是從最強(qiáng)條件入手，再往 外擴(kuò)展/關(guān)聯(lián)與上一步有關(guān)系對(duì)象。實(shí)際分析中，沒有我上述描述的那么復(fù)雜， DBA根據(jù)語句的約束條件，對(duì)比分析CBO給出的執(zhí)行計(jì)劃，大體一眼能找出計(jì) 劃中的疑點(diǎn)，再加以計(jì)算分析比較，即可找出問題的結(jié)癥。,數(shù)據(jù)

10、的采集統(tǒng)計(jì),推薦使用 DBMS_STATS.GATHER_XXX_STATS(); ORACLE不再改進(jìn)ANALYZE TABLE. 盡量不要鎖住表的統(tǒng)計(jì)信息. DBMS_STATS.LOCK_TABLE_STATS();,如何快速獲取語句所涉及到的表？,1 將語句創(chuàng)建成一視圖。 2 通過user_dependencies 視圖，查詢與此視圖相關(guān)的表。,給CBO采集系統(tǒng)的統(tǒng)計(jì)信息，執(zhí)行計(jì)劃將更優(yōu)。,1 Exec dbms_stats.gather_system_stats(INTERVAL, 180); 2 Select * from sys.aux_stats$;,4種關(guān)聯(lián)方式和兩個(gè)參數(shù),嵌

11、套連接 Nest Loops outer table inner table 對(duì)于從outer table 出來的每一條記錄，都要在inner table 里過濾一遍。 適用于小表間返回較少的結(jié)果集，并且有好的關(guān)聯(lián)關(guān)系。 哈希連接 適用等于條件下，大數(shù)據(jù)量的關(guān)聯(lián)產(chǎn)生大結(jié)果集。 排序合并連接 排序合并連接適用于兩個(gè)已經(jīng)按照關(guān)聯(lián)字段排序后的結(jié)果集間的關(guān)聯(lián)，尤其對(duì)大數(shù)據(jù)量 需要不全等于（, =, =）操作的情況下，效率要比NL好。 笛卡爾連接(Cartesian Joins),OPTIMIZER_INDEX_COST_ADJ: （1 to 10000） 用來給DBA人為對(duì)索引訪問的開銷作比例設(shè)定。缺

12、省值100 ，表示默認(rèn)情況下， Cbo將按照正常情況下計(jì)算出來的索引訪問開銷和全表掃描的開銷來比較。 DBA 調(diào)整此值后，CBO將這樣計(jì)算索引訪問的開銷：正常情況下計(jì)算出來的Cost * Optimizer_index_cost_adj。此值越小，則表示索引的開銷越小，Cbo將越傾 向于走索引；超過100，越大，Cbo將越傾向于走全表掃描。 OPTIMIZER_INDEX_CACHING：（0 to 100） 表示數(shù)據(jù)緩沖區(qū)中，緩存著的索引的數(shù)量。此值越大，意味著緩沖區(qū)中，緩存的 索引塊越多，這對(duì)于使用索引作嵌套循環(huán)的代價(jià)越低，此時(shí)CBO將更加偏向走 嵌套循環(huán)連接，而非哈?；蚺判蜻B接。,IN

13、和 EXISTS適用的場景,在一個(gè)帶子查詢的語句中，通常來講，若主語句上的約 束條件強(qiáng)(返回記錄數(shù)少)，則適合使用EXISTS；若是子查詢 語句上的約束條件強(qiáng)，則適用 IN。 這一點(diǎn)符合我在“手工調(diào)優(yōu)的粗略思路”章節(jié)中，提 到的思路：CBO盡可能從過濾性最強(qiáng)的條件入手。,例子,注意執(zhí)行計(jì)劃中的Cost,O.CUSTOMER_ID=144是最強(qiáng)的過濾條件，在這種條件下，語句適合用IN寫法，但語句使用了Exists,改用IN寫法后，COST大大降低,過濾條件e.department_id=80 在主語句上，此時(shí)應(yīng)用 exist, 但語句用了IN，看看其 cost,改為 exists后的 cost,

14、索引,確保唯一性（唯一性索引）。 加快數(shù)據(jù)查詢。,B樹索引。 升序，降序，反向。 2 位圖索引。 3 位圖連接索引。 4 函數(shù)索引。 5 應(yīng)用域索引。,提示： 可dump出 索引的結(jié)構(gòu)，來加強(qiáng)對(duì)索引結(jié)構(gòu)的了解，注： 33632 為索引的 Object_id: alter session set events immediate trace name treedump level 33632;,常用的HINT,All_rows, First_rows_n Optimization Goals Leading, Ordered - Join orders Full, Index, No_index

15、 -Access path Use_hash, Use_nl, Use_merge, Use_nl_with_index Join method 5 Append, Push_pred, Push_subq, Qb_name, Dynamic_sampling(X) , Gather_plan_statistics No_expand, Opt_param(optimizer_ind_ex_adj,10), Cardinality(alias 200), swap_join_inputs(), No_merge, Hash_Aj, Hash_SJ,善用分區(qū),分區(qū)，實(shí)際上是ORACLE提供的多種

16、視角，讓用戶根據(jù)不同性質(zhì)的 數(shù)據(jù)，去分組分割存放數(shù)據(jù)的方法。 正常情況下，系統(tǒng)設(shè)計(jì)人員/DBA應(yīng)該了解清楚各類型分區(qū)的特點(diǎn)， 系統(tǒng)設(shè)計(jì)時(shí)，根據(jù)業(yè)務(wù)的運(yùn)行需求，結(jié)合各種分區(qū)特性，事先規(guī)劃設(shè)計(jì) 好將來業(yè)務(wù)數(shù)據(jù)存儲(chǔ)方案，并將此思想和系統(tǒng)開發(fā)人員充分溝通，以便 開發(fā)人員在編寫程序時(shí)，利用好這些分區(qū)屬特性，編寫出高效SQL。,善用分區(qū),到11GR2為止，ORACLE提供的分區(qū)類型,各版本中的分區(qū)功能,與SQL調(diào)優(yōu)有關(guān)的幾個(gè)數(shù)字字典,v$session V$sqltext V$sql_plan V$sql_plan_statistics V$sql_plan_statistics_all V$ses_op

17、timizer_env User_table_histograms User_tab_statistics User_tab_col_statistics User_ind_statistics,10046事件,10046事件是oracle提供用于分析SQL語句性能最方便的工具。 使用10046事件，可以跟蹤某個(gè)SQL語句完整的執(zhí)行過程， 獲取其解析，執(zhí)行，CPU使用時(shí)間，等待事件，每個(gè)操作的 具體耗時(shí)等信息。這對(duì)獲取語句詳細(xì)的執(zhí)行計(jì)劃，分析定位 其效率問題，從而有針對(duì)性地優(yōu)化該語句，非常有用。,10046事件的用法，跟蹤級(jí)別,level 1： 跟蹤sql語句，包括解析、執(zhí)行、提取、提交和回

18、滾等。 level 4： 包括變量的詳細(xì)信息。 level 8： 包括等待事件。 level 12：包括綁定變量與等待事件。,Alter session set events 10046 trace name conetxt, level n; 執(zhí)行SQL。 Alter session set events 10046 trace name conetxt, off;,10046事件的查看方法,1 獲取當(dāng)前會(huì)話在操作系統(tǒng)下的進(jìn)程號(hào) SQLselect paddr from v$session where sid=(select sid from v$mystat group by sid);

19、PADDR - 0000000376B84438 Elapsed: 00:00:00.06 SQLselect spid from v$process where addr=0000000376B84438; SPID - 7548 2 查看文件路徑： SQLShow parameter user_dump_dest Elapsed: 00:00:00.01 3 在操作系統(tǒng)下，調(diào)用 Tkprof 格式化裸文件： C:Oraclediagrdbmsncbincbitracetkprof ncbi_ora_7548.trc ncbi_ora_7548.log,1 執(zhí)行計(jì)劃的詳細(xì)過程. 2 每個(gè)步驟

20、一致讀的個(gè)數(shù). 3 每個(gè)步驟的耗時(shí)，可根據(jù)耗時(shí)來 判斷步驟的優(yōu)劣.,語句在執(zhí)行過程中，各事件的耗時(shí)，,使用10046事件的前提條件 TIMED_STATISTICS=TRUE, SESSION級(jí)可設(shè)置。 MAX_DUMP_FILE_SIZE 要有足夠的空間，通常設(shè)置為 Umlimited. 10046事件為何有時(shí)沒有執(zhí)行計(jì)劃？ 這是因?yàn)樵撜Z句在執(zhí)行后，該語句的游標(biāo)沒有關(guān)閉，導(dǎo)致沒寫入執(zhí)行 計(jì)劃，可以在執(zhí)行完該語句后，執(zhí)行一簡單語句，如：select * from dual; 促使之前的游標(biāo)結(jié)束，即可得到執(zhí)行計(jì)劃信息。,列表分區(qū)優(yōu)化一例,SQLselect sid,serial#,seq#, e

21、rminal,machine,last_call_et call_et,module, 2 (select object_name from user_objects where a.row_wait_obj#=object_id) object_name, event,wait_class, 3 row_wait_file# r_w_f#,row_wait_block# r_w_b#,row_wait_row# r_w_r#,p1,p2,p3 from gv$session a 4 where a.status = ACTIVE and a.username is not null and

22、wait_class!=Idle order by last_call_et; SID SERIAL# SEQ# TERMINAL MACHINE CALL_ET MODULE OBJECT_NAME EVENT - - - - - - - - - - - - 1540 3915 5154 unknown s55 1038 JDBC Thin Client IC_GENERAL_B db file sequential read 2150 23384 17019 unknown s30 2395 JDBC Thin Client ARAP_DJFB db file sequential rea

23、d 23 rows selected. Last_call_et ：當(dāng)前狀態(tài)的持續(xù)時(shí)間，若是某個(gè)查詢，則意味著該查詢已經(jīng)執(zhí)行的時(shí)長。 上面SID=2150的進(jìn)程中，當(dāng)前語句已經(jīng)運(yùn)行了2395秒。,運(yùn)行的語句,select zb.vouchid, fb.fb_oid, xyb.fkxyb_oid, zb.ywbm, fb.hbbm, fb.deptid, fb.ywybm, fb.jobid, fb.szxmid, fb.cinventoryid, fb.ddh, fb.fph, zb.djdl, zb.djbh, fb.flbh, zb.djrq, zb.shrq, zb.effectdate

24、, fb.ordercusmandoc, xyb.xydqr, fb.hsdj, zb.kmbm, fb.kmbm, xyb.ybye, xyb.fbye, xyb.bbye, ductline, zb.xslxbm, pk_salestru, ( fb.jfybje + fb.dfybje ), fb.ybye, zb.dwbm, fb.wldx from arap_djfkxyb xyb, arap_djfb fb, bd_cumandoc, arap_djzb zb where xyb.fb_oid = fb.fb_oid and fb.ksbm_cl = bd_cumand

25、oc.pk_cumandoc and bd_cumandoc.pk_salestru = 0001AA1000000001ELSE and fb.ksbm_cl is not null and fb.vouchid = zb.vouchid and ( zb.dwbm = 1023 and fb.dwbm = 1023 and fb.wldx = 0 and zb.sxbz = 10 and fb.verifyfinshed = N and ( fb.xgbh 1 ) and ( fb.pausetransact is null or fb.pausetransact = N ) and xy

26、b.dr = 0 and fb.fx = 1 and zb.djdl = ys and ( fb.ybye 0 ) and fb.bzbm = 00010000000000000001 and ( fb.bz_date = 2009-07-01 and zb.djrq = 2009-07-01 and fb.billdate = 2009-07-31 and bd_cumandoc.pk_corp = 1023 ) order by xyb.xydqr, zb.djbh, fb.fb_oid 紅色部分是經(jīng)計(jì)算后，較強(qiáng)的過濾條件。,- | Id | Operation | Name | Rows

27、 | Bytes | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | | | 530 (100)| | | 1 | SORT ORDER BY | | 8 | 4024 | 530 (1)| 00:00:04 | | 2 | NESTED LOOPS | | 8 | 4024 | 529 (1)| 00:00:04 | | 3 | NESTED LOOPS | | 8 | 3520 | 522 (1)| 00:00:03 | | 4 | NESTED LOOPS | | 8 | 2520 | 519 (1)| 00:00:03 | | 5 |

28、TABLE ACCESS BY INDEX ROWID| BD_CUMANDOC | 8 | 264 | 2 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | I_BD_CUMANDOC_SALESTRU | 8 | | 1 (0)| 00:00:01 | |* 7 | TABLE ACCESS BY INDEX ROWID| ARAP_DJFB | 1 | 282 | 65 (2)| 00:00:01 | |* 8 | INDEX RANGE SCAN | I_ARAP_DJFB_TY1 | 259 | | 1 (0)| 00:00:01 | |* 9 |

29、TABLE ACCESS BY INDEX ROWID | ARAP_DJZB | 1 | 125 | 1 (0)| 00:00:01 | |* 10 | INDEX UNIQUE SCAN | PK_ARAP_DJZB | 1 | | 1 (0)| 00:00:01 | |* 11 | TABLE ACCESS BY INDEX ROWID | ARAP_DJFKXYB | 1 | 63 | 1 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | I_ARAP_FKXYB01 | 1 | | 1 (0)| 00:00:01 | - Predicate Inf

30、ormation (identified by operation id): - 6 - access(BD_CUMANDOC.PK_SALESTRU=0001AA1000000001ELSE AND BD_CUMANDOC.PK_CORP=1023) 7 - filter(FB.YBYE0 OR FB.YBYE=2009-07-01 AND FB.DWBM=1023 AND FB.VERIFYFINSHED=N AND FB.WLDX=0 AND FB.BILLDATE1 AND (FB.PAUSETRANSACT IS NULL OR FB.PAUSETRANSACT=N) AND FB.

31、BZBM=00010000000000000001 AND (FB.BZ_DATE IS NULL OR FB.BZ_DATE=2009-07-01 AND ZB.DWBM=1023 AND ZB.DJDL=ys AND ZB.DJRQ=2009-07-31 AND ZB.SXBZ=10) 10 - access(FB.VOUCHID=ZB.VOUCHID) 11 - filter(XYB.DR=0) 12 - access(XYB.FB_OID=FB.FB_OID) 但是，該條件并沒有被用于ACCESS，而是被FILTER。,分析原因,分析計(jì)算語句中的約束條件，發(fā)現(xiàn)語句中，過濾性最強(qiáng)的是這個(gè)

32、 條件：zb.dwbm =1023，djrq = 2009-07-01 and djrq create index IDX_ARAP_DJZB_20090326_0703 on ARAP_DJZB_20090326(DJRQ,DJDL,LRR) tablespace nnc_index03 local; Index created. Elapsed: 00:01:34.69,創(chuàng)建列表分區(qū)表,改成分區(qū)表后語句的耗時(shí)，不到原來的1/330。,使用MV優(yōu)化SQL,某個(gè)報(bào)表，過程語句有40+條，其中關(guān)鍵耗時(shí)的語句有8條，經(jīng)調(diào)優(yōu)，在系統(tǒng)空閑時(shí) 間運(yùn)行，大概耗時(shí)25M，但在系統(tǒng)運(yùn)行時(shí)查詢，耗時(shí)40-50M

33、，無法滿足需求，求改善。 經(jīng)了解，業(yè)務(wù)人員只要求查詢昨天某分公司的數(shù)據(jù)，此條件一直不變。由于之前已 經(jīng)獲知此報(bào)表涉及到的SQL，經(jīng)思考，決定使用MV+OUTLINE來實(shí)現(xiàn)此需求。 1 對(duì)此8條耗時(shí)語句，修改其中的日期后，建成MV。 2 對(duì)此8條耗時(shí)語句，修改其中的日期，加入提示 /*+ rewrite */，之后生成 OUTLINE。 3 對(duì)此8條耗時(shí)語句，修改其中的日期，生成OUTLINE(此是生成的是正常查詢 語句的OUTLINE)。 4 對(duì)2，3步的OUTLINE作交換，目的是迫使正常查詢時(shí)，使用步驟2 的執(zhí)行計(jì)劃， 此時(shí)該語句將取讀取步驟1生成的MV里的數(shù)據(jù)。 5 修改參數(shù)query_

34、rewrite_integrity= stale_tolerated，并建一SCHEDULER JOB，定時(shí)運(yùn)行上述過程。 經(jīng)上述優(yōu)化后，業(yè)務(wù)人員在查詢報(bào)表時(shí)，CBO將直接讀取事先生成的MV里的數(shù)據(jù)， 在本地查詢時(shí)，2分鐘內(nèi)出結(jié)果。語句的執(zhí)行計(jì)劃如下：,另外一些調(diào)優(yōu)方法和工具,執(zhí)行大綱的本意，就是對(duì)某些特定語句，使用指定/固 定的執(zhí)行計(jì)劃。步驟如下： 1 先創(chuàng)建原始語句的OUTLINE。 2 創(chuàng)建特定語句的OUTLINE，此語句通常是加了 HINT來達(dá)到使用指定執(zhí)行計(jì)劃。 3 交換步驟1，2的執(zhí)行計(jì)劃。 4 啟用outline目錄。,執(zhí)行大綱的使用方法,SQLTXPLAN,SQLTXPLAN，

35、ORACLE的內(nèi)部工具，可用來協(xié)助診斷效率差的 SQL，詳細(xì)用法請(qǐng)上MOS查閱相關(guān)文檔ID 215187.1,DBMS_SQLTUNE,Dbms_sqltune 是ORACLE在10G版本里推出的Sql調(diào)優(yōu)工具。 用戶可以通過創(chuàng)建作業(yè)來把要調(diào)優(yōu)的Sql語句放入Dbms_sqltune 包， 運(yùn)行該作業(yè)，之后查詢相關(guān)視圖，獲取該語句最佳的執(zhí)行計(jì)劃及建議。 如下步驟： 1 創(chuàng)建優(yōu)化任務(wù)。 DBMS_SQLTUNE.CREATE_TUNING_TASK. 2 運(yùn)行優(yōu)化任務(wù)。 3 查詢視圖，獲取任務(wù)的當(dāng)前狀態(tài)。 select status from user_advisor_taks where ta

36、sk_name=; 4 查詢語句的優(yōu)化結(jié)果。 select dbms_sqltune.report_tuning_task() from dual; 此時(shí)可以看到最佳的執(zhí)行計(jì)劃及ORACLE的優(yōu)化建議。 5 刪除優(yōu)化任務(wù)。 exec dbms_sqltune.drop_tuning_task();,DBMS_ADVISOR,10G版本新推出的顧問框架，支持各種性能調(diào)優(yōu)需求，通 過DBMS_ADVISOR包來支持各種需求，如，SQL調(diào)優(yōu)， MV，索引建議等。,DBMS_PROFILE,DBMS_PROFILE 包主要用來調(diào)優(yōu)存儲(chǔ)過程的執(zhí)行過 程，通過跟蹤記錄存儲(chǔ)過程中每條SQL的執(zhí)行時(shí)間，來確定 哪些SQL語句最耗時(shí)，從而定位出需要調(diào)優(yōu)的SQL語句。,11G在執(zhí)行計(jì)劃上的一些改進(jìn),自適應(yīng)共享游標(biāo)(ACS) ，克服帶綁定變量的執(zhí)行計(jì)劃存在 的弊端。 2 在收集統(tǒng)計(jì)信息上的改進(jìn) A 設(shè)置收集統(tǒng)計(jì)信息時(shí)的選項(xiàng)，可對(duì)指定的表作單獨(dú) 設(shè)置。 B 對(duì)聯(lián)合列收集統(tǒng)計(jì)信息。 C 對(duì)函數(shù)以及表達(dá)式收集統(tǒng)計(jì)信息。 Spm ，Sql paln management ， Sql 計(jì)劃管理，實(shí)際是 10G中顧問框架（DBMS_ADVISOR）的升級(jí)版，通過這 個(gè)特性，ORACLE自動(dòng)判斷某個(gè)SQL新的