語句級(jí)與策略級(jí)優(yōu)化小結(jié)

1、一、基本的Sql編寫注意事項(xiàng) · 盡量少用IN操作符，基本上所有的IN操作符都可以用EXISTS代替。 用表連接替換EXISTS，  通常來說 ， 采用表連接的方式比EXISTS更有效率，RBO中適用，因?yàn)榍罢咝枰狥ILTER，nested loops semi是nested loop連接的變種，又叫半連接。原理與nl相同，通常用于in,exist操作，這種操作join時(shí)候，通常查找到一條紀(jì)錄就可以了，所以用semi表示。與semi相似的有一種叫anti，反連接，一般用于not in,not exists，也有nest loop anti和hash anti兩種。

2、· 不用NOT IN操作符，可以用NOT EXISTS或者外連接+替代。 · Oracle在執(zhí)行IN子查詢時(shí)，首先執(zhí)行子查詢，將查詢結(jié)果放入臨時(shí)表再執(zhí)行主查詢。而EXIST則是首先檢查主查詢，然后運(yùn)行子查詢直到找到第一個(gè)匹配項(xiàng)。NOT EXISTS比NOT IN效率稍高。但具體在選擇IN或EXIST操作時(shí)，要根據(jù)主子表數(shù)據(jù)量大小來具體考慮。 · 不用“<>”或者“!=”操作符。對不等于操作符的處理會(huì)造成全表掃描，可以用“<” or “>”代替。 · Where子句中出現(xiàn)IS NULL或者IS NOT NULL時(shí)，Oracle會(huì)停止

3、使用索引而執(zhí)行全表掃描。可以考慮在設(shè)計(jì)表時(shí)，對索引列設(shè)置為NOT NULL。這樣就可以用其他操作來取代判斷NULL的操作。 · 當(dāng)通配符“%”或者“_”作為查詢字符串的第一個(gè)字符時(shí)，索引不會(huì)被使用。 · 對于有連接的列“|”，最后一個(gè)連接列索引會(huì)無效。盡量避免連接，可以分開連接或者使用不作用在列上的函數(shù)替代。 · 如果索引不是基于函數(shù)的，那么當(dāng)在Where子句中對索引列使用函數(shù)時(shí)，索引不再起作用。 · Where子句中避免在索引列上使用計(jì)算，否則將導(dǎo)致索引失效而進(jìn)行全表掃描。 · 對數(shù)據(jù)類型不同的列進(jìn)行比較時(shí)，會(huì)使索引失效。 · UN

4、ION操作符會(huì)對結(jié)果進(jìn)行篩選，消除重復(fù)，數(shù)據(jù)量大的情況下可能會(huì)引起磁盤排序。如果不需要?jiǎng)h除重復(fù)記錄，應(yīng)該使用UNION ALL。 · Order By語句中的非索引列會(huì)降低性能，可以通過添加索引的方式處理。嚴(yán)格控制在Order By語句中使用表達(dá)式。 · 不同區(qū)域出現(xiàn)的相同的Sql語句，要保證查詢字符完全相同，以利用SGA共享池，防止相同的Sql語句被多次分析。 · 多利用內(nèi)部函數(shù)提高Sql效率。 · 當(dāng)在Sql語句中連接多個(gè)表時(shí)，使用表的別名，并將之作為每列的前綴。這樣可以減少解析時(shí)間。 · 用TRUNCATE替代DELETE，開發(fā)中不準(zhǔn)使用

5、。     當(dāng)刪除表中的記錄時(shí)，在通常情況下， 回滾段(rollback segments ) 用來存放可以被恢復(fù)的信息。 如果你沒有COMMIT事務(wù)，ORACLE會(huì)將數(shù)據(jù)恢復(fù)到刪除之前的狀態(tài)(準(zhǔn)確地說是恢復(fù)到執(zhí)行刪除命令之前的狀況)。     而當(dāng)運(yùn)用TRUNCATE時(shí)， 回滾段不再存放任何可被恢復(fù)的信息。當(dāng)命令運(yùn)行后，數(shù)據(jù)不能被恢復(fù)。因此很少的資源被調(diào)用，執(zhí)行時(shí)間也會(huì)很短。· SELECT子句中避免使用 ' * '      當(dāng)你想在SELECT

6、子句中列出所有的COLUMN時(shí)，使用動(dòng)態(tài)SQL列引用 '*' 是一個(gè)方便的方法。不幸的是，這是一個(gè)非常低效的方法。實(shí)際上，ORACLE在解析的過程中， 會(huì)將'*' 依次轉(zhuǎn)換成所有的列名，這個(gè)工作是通過查詢數(shù)據(jù)字典完成的， 這意味著將耗費(fèi)更多的時(shí)間。Count(*) 10G 中例外· 用Where子句替換HAVING子句     避免使用HAVING子句，HAVING 只會(huì)在檢索出所有記錄之后才對結(jié)果集進(jìn)行過濾。 這個(gè)處理需要排序，總計(jì)等操作。如果能通過WHERE子句限制記錄的數(shù)目，那就能減少這方面的開銷。

7、3; 需要注意的是，隨著Oracle的升級(jí)，查詢優(yōu)化器會(huì)自動(dòng)對Sql語句進(jìn)行優(yōu)化，某些限制可能在新版本的Oracle下不再是問題。尤其是采用CBO（Cost-Based Optimization，基于代價(jià)的優(yōu)化方式）時(shí)。二跟蹤和分析系統(tǒng)及SESSION級(jí)的SQL：跟蹤SQL語句SQL trace 工具收集正在執(zhí)行的SQL的性能狀態(tài)數(shù)據(jù)并記錄到一個(gè)跟蹤文件中. 這個(gè)跟蹤文件提供了許多有用的信息,例如解析次數(shù).執(zhí)行次數(shù),CPU使用時(shí)間等.這些數(shù)據(jù)將可以用來優(yōu)化你的系統(tǒng).設(shè)置SQL TRACE在會(huì)話級(jí)別: 有效 ALTER SESSION SET SQL_TRACE TRUE設(shè)置SQL TRACE

8、在整個(gè)數(shù)據(jù)庫有效仿, 你必須將SQL_TRACE參數(shù)在init.ora中設(shè)為TRUE, USER_DUMP_DEST參數(shù)說明了生成跟蹤文件的目錄跟蹤會(huì)話和系統(tǒng)跟蹤 跟蹤自己的會(huì)話或者是別人的會(huì)話 跟蹤自己的會(huì)話很簡單 Alter session set sql_trace true|false or exec dbms_session.set_sql_trace(TRUE); 如果跟蹤別人的會(huì)話，需要調(diào)用一個(gè)包 exec dbms_system.set_sql_trace_in_session(sid,serial#,true|false)或exec sys.dbms_system.set_e

9、v(sid,serial#,10046,12,'')停止TRACE(sid,serial#,10046,0,'')跟蹤的信息在user_dump_dest 目錄下可以找到 可以通過Tkprof來解析跟蹤文件，如 Tkprof 原文件 目標(biāo)文件 sys=n sort = exeela設(shè)置整個(gè)數(shù)據(jù)庫系統(tǒng)跟蹤 其實(shí)文檔上的alter system set sql_trace=true是不成功的 但是可以通過設(shè)置事件來完成這個(gè)工作，作用相等 alter system set events '10046 trace name context forever,lev

10、el 1' 如果關(guān)閉跟蹤，可以用如下語句 alter system set events '10046 trace name context off' 其中的level 1與上面的8都是跟蹤級(jí)別 level 0: 停止level 1：跟蹤SQL語句，等于sql_trace=true level 4：包括變量的詳細(xì)信息 level 8：包括等待事件 level 12：包括綁定變量與等待事件 eg:alter system set max_dump_file_size=unlimited;ALTER SESSION SET EVENTS '10046 trace n

11、ame context forever, level 12'ALTER SESSION SET EVENTS '942 trace name errorstack level 10'(對SQL TRACE的用法也不夠準(zhǔn)確, 設(shè)置SQL TRACE首先要在init.ora中設(shè)定TIMED_STATISTICS, 這樣才能得到那些重要的時(shí)間狀態(tài). 生成的trace文件是不可讀的,所以要用TKPROF工具對其進(jìn)行轉(zhuǎn)換,TKPROF有許多執(zhí)行參數(shù). 大家可以參考ORACLE手冊來了解具體的配置. )分析SQL語句     

12、60; 用EXPLAIN PLAN 分析SQL語句EXPLAIN PLAN 是一個(gè)很好的分析SQL語句的工具,它甚至可以在不執(zhí)行SQL的情況下分析語句. 通過分析,我們就可以知道ORACLE是怎么樣連接表,使用什么方式掃描表(索引掃描或全表掃描)以及使用到的索引名稱.你需要按照從里到外,從上到下的次序解讀分析的結(jié)果. EXPLAIN PLAN分析的結(jié)果是用縮進(jìn)的格式排列的, 最內(nèi)部的操作將被最先解讀, 如果兩個(gè)操作處于同一層中,帶有最小操作號(hào)的將被首先執(zhí)行.NESTED LOOP是少數(shù)不按照上述規(guī)則處理的操作, 正確的執(zhí)行路徑是檢查對NESTED LOOP提供數(shù)據(jù)的操作,其中操作號(hào)最小的將被最

13、先處理.通過實(shí)踐, 感到還是用SQLPLUS中的SET TRACE 功能比較方便.舉例:SQL> list 1 SELECT * 2 FROM dept, emp  3* WHERE emp.deptno = dept.deptnoSQL> set autotrace traceonly /*traceonly 可以不顯示執(zhí)行結(jié)果*/SQL> /14 rows selected.Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF

14、 ''''EMP'''' 3 1 TABLE ACCESS (BY INDEX ROWID) OF ''''DEPT'''' 4 3 INDEX (UNIQUE SCAN) OF ''''PK_DEPT'''' (UNIQUE)Statistics- 0 recursive calls 2 db block gets 30 consistent gets 0 physical reads 0 redo s

15、ize 2598 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed通過以上分析,可以得出實(shí)際的執(zhí)行步驟是:1.       TABLE ACCESS (FULL) OF ''''EMP'''' 2.&#

16、160;      INDEX (UNIQUE SCAN) OF ''''PK_DEPT'''' (UNIQUE)3.       TABLE ACCESS (BY INDEX ROWID) OF ''''DEPT''''4.       NESTED LOOPS (JOINING 1 AND 3)

17、注: 目前許多第三方的工具如TOAD和ORACLE本身提供的工具如OMS的SQL Analyze都提供了極其方便的EXPLAIN PLAN工具.也許喜歡圖形化界面的朋友們可以選用它們.10G：在同一個(gè)SESSION 中執(zhí)行以下兩句：EXPLAIN PLAN FOR SELECT * FROM C_CONS WHERE CONS_NO=:ASELECT dbms_xplan.display from dual;三Oracle優(yōu)化器Oracle優(yōu)化器（Optimizer）是Oracle在執(zhí)行SQL之前分析語句的工具。Oracle的優(yōu)化器有兩種優(yōu)化方式：基于規(guī)則的（RBO）和基于代價(jià)的（CBO）。

18、· RBO：優(yōu)化器遵循Oracle內(nèi)部預(yù)定的規(guī)則。 · CBO：依據(jù)語句執(zhí)行的代價(jià)，主要指對CPU和內(nèi)存的占用。優(yōu)化器在判斷是否使用CBO時(shí)，要參照表和索引的統(tǒng)計(jì)信息。統(tǒng)計(jì)信息要在對表做analyze后才會(huì)有。Oracle8及以后版本，推薦用CBO方式。 Oracle優(yōu)化器的優(yōu)化模式主要有四種： · Rule：基于規(guī)則； · Choose：默認(rèn)模式。根據(jù)表或索引的統(tǒng)計(jì)信息，如果有統(tǒng)計(jì)信息，則使用CBO方式；如果沒有統(tǒng)計(jì)信息，相應(yīng)列有索引，則使用RBO方式。 · First rows：與Choose類似。不同的是如果表有統(tǒng)計(jì)信息，它將以最快的方式

19、返回查詢的前幾行，以獲得最佳響應(yīng)時(shí)間。 · All rows：即完全基于Cost的模式。當(dāng)一個(gè)表有統(tǒng)計(jì)信息時(shí)，以最快方式返回表所有行，以獲得最大吞吐量。沒有統(tǒng)計(jì)信息則使用RBO方式。 設(shè)定優(yōu)化模式· Instance級(jí)別：在init<SID>.ora文件中設(shè)定OPTIMIZER_MODE； · Session級(jí)別：通過SQL> ALTER SESSION SET OPTIMIZER_MODE=;來設(shè)定。 · 語句級(jí)別：通過SQL> SELECT /*+ALL+_ROWS*/ ;來設(shè)定?？捎玫腍INT包括/*+ALL_ROWS*/、

20、/*+FIRST_ROWS*/、/*+CHOOSE*/、/*+RULE*/ 等。統(tǒng)計(jì)表信息 要注意的是，如果表有統(tǒng)計(jì)信息，則可能造成語句不走索引的結(jié)果?？梢杂肧QL>ANALYZE TABLE table_name DELETE STATISTICS; 刪除統(tǒng)計(jì)信息。對列和索引更新統(tǒng)計(jì)信息的SQL：SQL> ANALYZE TABLE table_name COMPUTE STATISTICS;SQL> ANALYZE INDEX index_name ESTIMATE STATISTICS; 10g:DBMS_STATS.四使用HINTOracle使用的hints調(diào)整機(jī)制一

21、直很復(fù)雜，Oracle Technical Network對使用hints調(diào)整Oracle SQL的過程有很好的全面評述。根據(jù)對10g數(shù)據(jù)庫的介紹，可使用更多新的optimizer hints來控制優(yōu)化行為?，F(xiàn)在讓我們迅速了解一下這些強(qiáng)大的新hints:Oracle使用的hints調(diào)整機(jī)制一直很復(fù)雜，Oracle Technical Network對使用hints調(diào)整Oracle SQL的過程有很好的全面評述。根據(jù)對10g數(shù)據(jù)庫的介紹，可使用更多新的optimizer hints來控制優(yōu)化行為?，F(xiàn)在讓我們迅速了解一下這些強(qiáng)大的新hints:spread_min_analysis使用這一hint

22、，你可以忽略一些關(guān)于如詳細(xì)的關(guān)系依賴圖分析等電子表格的編譯時(shí)間優(yōu)化規(guī)則。其他的一些優(yōu)化，如創(chuàng)建過濾以有選擇性的定位電子表格訪問結(jié)構(gòu)并限制修訂規(guī)則等，得到了繼續(xù)使用。由于在規(guī)則數(shù)非常大的情況下，電子表格分析會(huì)很長。這一提示可以幫助我們減少由此產(chǎn)生的數(shù)以百小時(shí)計(jì)的編譯時(shí)間。例如:SELECT /*+ SPREAD_MIN_ANALYSIS */ .spread_no_analysis通過這一hint，可以使無電子表格分析成為可能。同樣，使用這一hint可以忽略修訂規(guī)則和過濾產(chǎn)生。如果存在一電子表格分析，編譯時(shí)間可以被減少到最低程度。例如:SELECT /*+ SPREAD_NO_ANALYSIS

23、*/ .use_nl_with_index這項(xiàng)hint使CBO通過嵌套循環(huán)把特定的表格加入到另一原始行。只有在以下情況中，它才使用特定表格作為內(nèi)部表格:如果沒有指定標(biāo)簽，CBO必須可以使用一些標(biāo)簽，且這些標(biāo)簽至少有一個(gè)作為索引鍵值加入判斷;反之，CBO必須能夠使用至少有一個(gè)作為索引鍵值加入判斷的標(biāo)簽。例如:SELECT /*+ USE_NL_WITH_INDEX (polrecpolrind) */ .典型例子：電費(fèi)發(fā)行中的一句話。INSERT INTO a_rcvbl_pl_flow_tmp(rcvbl_pl_id, pl_amt, item_code

24、, acct_no, org_no, rcvbl_ym, rcved_amt, rcvbl_amt_id)SELECT /*+use_hash(b c) index(a CONSPRC_PA_FK)*/ pkg_sp_seq.f_a_rcvbl_pl_flow_rcvblid rcvblid, SUM(a.pl_amt) pl_amt, a.pl_code pl_code, v_acctno acct_no, b.o

25、rg_no org_no, b.ym ym, 0 rcved_amt, c.rcvbl_amt_idFROM e_pl_amt a, e_cons_prc_amt b, a_rcvbl_flow_tmp c WHERE _no = in_org_no AND a.ym = in_ym AND _no = in_org_no AN

26、D b.ym = in_ym AND b.app_code = in_app_no AND a.prc_amt_id = b.prc_amt_id AND _no = _no AND c.calc_id = b.calc_id GROUP BY a.pl_code, _no, b.ym, c.rcvbl_amt_id;

27、CARDINALITY此hint定義了對由查詢或查詢部分返回的基數(shù)的評價(jià)。注意如果沒有定義表格，基數(shù)是由整個(gè)查詢所返回的總行數(shù)。例如:SELECT /*+ CARDINALITY ( tablespec card ) */典型例子：臨時(shí)表的使用：SELECT x.*, x.rcvbl_owe + x.penalty owe_amt FROM (SELECT COUNT(1) row_count, COUNT(a.cons_no) cons_count, a.rcvbl_ym, SUM(a.rcvbl_amt - a.rcved_amt) rcvbl_owe, SUM(pkg_ca_common

28、.f_calcpenalty(a.rcvbl_amt_id) penalty FROM a_rcvbl_flow a, c_cons b WHERE a.cons_no = b.cons_no AND _no = _no AND _no IN (SELECT /*+ CARDINALITY(x 1) +*/ * FROM TABLE(v_orgnolist) x WHERE rownum >= 0) AND a.rcvbl_ym BETWEEN in_rcvblymbgn AND in_rcvblymend AND a.settle_flag IN (

29、9;01', '02') AND a.pay_mode LIKE in_paymode | '%' AND a.amt_type LIKE in_amttype | '%' AND nvl(b.cons_sort_code, '00') LIKE in_conssortcode | '%' AND b.elec_type_code LIKE in_electypecode | '%' AND nvl(a.period_num, '0') LIKE in_periodnum |

30、 '%' AND rcvbl_amt - rcved_amt >= v_compareamtbgn AND rcvbl_amt - rcved_amt <= v_compareamtend AND a.cons_no LIKE in_consno | '%' AND a.status_code <= in_showtype GROUP BY a.rcvbl_ym ORDER BY rcvbl_ym DESC) xno_use_nlHint no_use_nl使CBO執(zhí)行循環(huán)嵌套，通過把指定表格作為內(nèi)部表格，把每個(gè)指定表格連接到另一原始行。通過這

31、一hint，只有hash join和sort-merge joins會(huì)為指定表格所考慮。例如:SELECT /*+ NO_USE_NL ( employees ) */ .no_use_merge此hint使CBO通過把指定表格作為內(nèi)部表格的方式，拒絕sort-merge把每個(gè)指定表格加入到另一原始行。例如:SELECT /*+ NO_USE_MERGE ( employees dept ) */ .no_use_hash此hint使CBO通過把指定表格作為內(nèi)部表格的方式，拒絕hash joins把每個(gè)指定表格加入到另一原始行。例如:SELECT /*+ NO_USE_HASH ( emplo

32、yees dept ) */ .no_index_ffs此hint使CBO拒絕對指定表格的指定標(biāo)簽進(jìn)行fast full-index scan。Syntax: /*+ NO_INDEX_FFS ( tablespecindexspec ) */no_index_ss此hint使CBO拒絕對指定表格的指定標(biāo)簽進(jìn)行skip scan。Syntax: /*+ NO_INDEX_SS (tablespecindexspec ) */index_ss此hint明確地為指定表格選擇index skip scan。如果語句使用index range scan，Oracle將以對其索引值的升序排列來檢查索引入

33、口。在被分割的索引中，其結(jié)果為對每個(gè)部分內(nèi)部的升序排列。Syntax: /*+ INDEX_SS (tablespecindexspec) */cpu_costing此hint為SQL語句打開CPU costing。這是優(yōu)化器的默認(rèn)評估模式。優(yōu)化器評估當(dāng)執(zhí)行給定查詢時(shí)，數(shù)據(jù)庫需要運(yùn)行的IO操作數(shù)、IO操作種類、以及CPU周期數(shù)。Syntax: /*+ CPU_COSTING (tablespecindexspec) */no_cpu_costing此hint為SQL語句關(guān)閉CPU costing。然后CBO使用IO cost模式，此模式忽略CPU花費(fèi)，僅測量在single-block read

34、s中的所有指標(biāo)。Syntax: /*+ NO_CPU_COSTING */隨著Oracle優(yōu)化器越來越成熟，Oracle專家必須不斷增加自己對調(diào)整SQL語句的工具儲(chǔ)備。當(dāng)然，討論所有復(fù)雜的Oracle10g SQL新hints遠(yuǎn)遠(yuǎn)超出了本文的范圍，你可以從Mike Ault的新書Oracle Database 10g New Features中獲得關(guān)于Oracle10g的更多信息。三如何監(jiān)控索引的使用？研究發(fā)現(xiàn)，oracle數(shù)據(jù)庫使用的索引不會(huì)超過總數(shù)的25%，或者不易他們期望被使用的方式使用。通過 監(jiān)控?cái)?shù)據(jù)庫索引的使用，釋放那些未被使用的索引，從而節(jié)省維護(hù)索引的開銷，優(yōu)化性能。(用此理論基礎(chǔ)

35、測試各個(gè)數(shù)據(jù)庫的optimizer_index_cost_adj系統(tǒng)參數(shù)值)oracle9i中如何確定索引的使用情況在oracle9i中，情況會(huì)簡單得多，因?yàn)橛幸粋€(gè)新得字典視圖V$SQL_PLAN存儲(chǔ)了實(shí)際計(jì)劃，這些計(jì)劃用于執(zhí)行共享SQL區(qū)中得語句。V$SQL_PLAN視圖很類似與計(jì)劃表，但V$SQL_PLAN使用ADDRESS和HASH_VALUE列 來識(shí)別語句。下面的SQL顯示了在一個(gè)oracle9i數(shù)據(jù)庫中出現(xiàn)在共享SQL區(qū)中語句使用的所有索引select object_owner, object_name, options, count(*)from v$sql_planwhereop

36、eration='INDEX'andobject_owner!='SYS'groupbyobject_owner, object_name, operation, optionsorderby count(*) desc;所有基于共享SQL區(qū)中的信息來識(shí)別索引使用情況的方法， 都可能會(huì)收集到不完整的信息。共享SQL區(qū)是一個(gè)動(dòng)態(tài)結(jié)構(gòu)，除非能對它進(jìn)行足夠頻繁的采樣， 否則在有關(guān)索引使用的情況的信息被收集之前，SQL語句可 能就已經(jīng)(因?yàn)槔匣?被移出緩存了。oracle9i提供了解決這個(gè)問題的方案，即它為alter index提供了一個(gè)monitoring usage

37、子句。當(dāng)啟用monitoring usage 時(shí)，oralce記錄簡單的yes或no值，以指出在監(jiān)控間隔 期間某個(gè)索引是否被使用。SQL：select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;alter index test_pk monitoring usage;alter index test_pk nomonitoring usage;五其它共享SQL語句    Library cache     共享

38、的語句必須滿足三個(gè)條件：     A、 字符級(jí)的比較： 當(dāng)前被執(zhí)行的語句和共享池中的語句必須完全相同(連空格個(gè)數(shù)都一樣)。     B、 兩個(gè)語句所指的對象必須完全相同：     C、 兩個(gè)SQL語句中必須使用相同的名字的綁定變量(bind variables)。BIND變量對執(zhí)行計(jì)劃的影響執(zhí)行計(jì)劃分析PARTITION RANGE SINGLE/INLIST/INTE/iteration/allINDEX SCAN：快速全局掃描在Oracle7.3后就可以使用快速全局掃描(Fast Full Scan)這個(gè)選項(xiàng)。這個(gè)選項(xiàng)允許Oracle執(zhí)行一個(gè)全局索引掃描操作?？焖偃謷呙枳x取B-樹索引上所有樹葉塊。初始化文件中的DB_