語句級與策略級優(yōu)化小結

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

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

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

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

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

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

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

8、在整個數據庫有效仿, 你必須將SQL_TRACE參數在init.ora中設為TRUE, USER_DUMP_DEST參數說明了生成跟蹤文件的目錄跟蹤會話和系統(tǒng)跟蹤 跟蹤自己的會話或者是別人的會話 跟蹤自己的會話很簡單 Alter session set sql_trace true|false or exec dbms_session.set_sql_trace(TRUE); 如果跟蹤別人的會話，需要調用一個包 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 原文件 目標文件 sys=n sort = exeela設置整個數據庫系統(tǒng)跟蹤 其實文檔上的alter system set sql_trace=true是不成功的 但是可以通過設置事件來完成這個工作，作用相等 alter system set events '10046 trace name context forever,lev

10、el 1' 如果關閉跟蹤，可以用如下語句 alter system set events '10046 trace name context off' 其中的level 1與上面的8都是跟蹤級別 level 0: 停止level 1：跟蹤SQL語句，等于sql_trace=true level 4：包括變量的詳細信息 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的用法也不夠準確, 設置SQL TRACE首先要在init.ora中設定TIMED_STATISTICS, 這樣才能得到那些重要的時間狀態(tài). 生成的trace文件是不可讀的,所以要用TKPROF工具對其進行轉換,TKPROF有許多執(zhí)行參數. 大家可以參考ORACLE手冊來了解具體的配置. )分析SQL語句     

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

13、先處理.通過實踐, 感到還是用SQLPLUS中的SET TRACE 功能比較方便.舉例:SQL> list 1 SELECT * 2 FROM dept, emp  3* WHERE emp.deptno = dept.deptnoSQL> set autotrace traceonly /*traceonly 可以不顯示執(zhí)行結果*/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通過以上分析,可以得出實際的執(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：在同一個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）和基于代價的（CBO）。

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

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

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

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

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

23、*/ .use_nl_with_index這項hint使CBO通過嵌套循環(huán)把特定的表格加入到另一原始行。只有在以下情況中，它才使用特定表格作為內部表格:如果沒有指定標簽，CBO必須可以使用一些標簽，且這些標簽至少有一個作為索引鍵值加入判斷;反之，CBO必須能夠使用至少有一個作為索引鍵值加入判斷的標簽。例如:SELECT /*+ USE_NL_WITH_INDEX (polrecpolrind) */ .典型例子：電費發(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定義了對由查詢或查詢部分返回的基數的評價。注意如果沒有定義表格，基數是由整個查詢所返回的總行數。例如:SELECT /*+ CARDINALITY ( tablespec card ) */典型例子：臨時表的使用：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)嵌套，通過把指定表格作為內部表格，把每個指定表格連接到另一原始行。通過這

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

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

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

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

35、測試各個數據庫的optimizer_index_cost_adj系統(tǒng)參數值)oracle9i中如何確定索引的使用情況在oracle9i中，情況會簡單得多，因為有一個新得字典視圖V$SQL_PLAN存儲了實際計劃，這些計劃用于執(zhí)行共享SQL區(qū)中得語句。V$SQL_PLAN視圖很類似與計劃表，但V$SQL_PLAN使用ADDRESS和HASH_VALUE列 來識別語句。下面的SQL顯示了在一個oracle9i數據庫中出現在共享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ū)中的信息來識別索引使用情況的方法， 都可能會收集到不完整的信息。共享SQL區(qū)是一個動態(tài)結構，除非能對它進行足夠頻繁的采樣， 否則在有關索引使用的情況的信息被收集之前，SQL語句可 能就已經(因為老化)被移出緩存了。oracle9i提供了解決這個問題的方案，即它為alter index提供了一個monitoring usage

37、子句。當啟用monitoring usage 時，oralce記錄簡單的yes或no值，以指出在監(jiān)控間隔 期間某個索引是否被使用。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、的語句必須滿足三個條件：     A、 字符級的比較： 當前被執(zhí)行的語句和共享池中的語句必須完全相同(連空格個數都一樣)。     B、 兩個語句所指的對象必須完全相同：     C、 兩個SQL語句中必須使用相同的名字的綁定變量(bind variables)。BIND變量對執(zhí)行計劃的影響執(zhí)行計劃分析PARTITION RANGE SINGLE/INLIST/INTE/iteration/allINDEX SCAN：快速全局掃描在Oracle7.3后就可以使用快速全局掃描(Fast Full Scan)這個選項。這個選項允許Oracle執(zhí)行一個全局索引掃描操作?？焖偃謷呙枳x取B-樹索引上所有樹葉塊。初始化文件中的DB_