OraclePerformanceTuning入門手冊V

1、ORACLE SQL Performance Tuning TOC o 1-2 h z u HYPERLINK l _Toc168817003 1序言 PAGEREF _Toc168817003 h 3 HYPERLINK l _Toc168817004 2影響SQL Performance的關鍵因素和配置： PAGEREF _Toc168817004 h 3 HYPERLINK l _Toc168817005 2.1關于執(zhí)行計劃 PAGEREF _Toc168817005 h 3 HYPERLINK l _Toc168817006 2.2ORACLE優(yōu)化器 PAGEREF _Toc16881

2、7006 h 3 HYPERLINK l _Toc168817007 2.3結(jié)合BenQ Oracle ERP PAGEREF _Toc168817007 h 6 HYPERLINK l _Toc168817008 2.4跟蹤 SQL實際運行的Cost PAGEREF _Toc168817008 h 7 HYPERLINK l _Toc168817009 2.5小結(jié) PAGEREF _Toc168817009 h 7 HYPERLINK l _Toc168817010 3SQL語句的Tuning經(jīng)驗分享： PAGEREF _Toc168817010 h 8 HYPERLINK l _Toc16

3、8817011 3.1絕大多數(shù)情況下not exists比not in 效率高 PAGEREF _Toc168817011 h 8 HYPERLINK l _Toc168817012 3.2UNION ALL效率比UNION高很多 PAGEREF _Toc168817012 h 8 HYPERLINK l _Toc168817013 3.3一些很耗資源的SQL操作，在不必要的情況下不要使用 PAGEREF _Toc168817013 h 8 HYPERLINK l _Toc168817014 3.4通常聯(lián)接查詢比子查詢的效率要高很多 PAGEREF _Toc168817014 h 10 HYP

4、ERLINK l _Toc168817015 3.5用table 索引（index）欄位去做Table間的關聯(lián)，可避免費時的全表掃描 PAGEREF _Toc168817015 h 10 HYPERLINK l _Toc168817016 3.6在View中盡量不要使用 Package/function 來得到欄位值， PAGEREF _Toc168817016 h 11 HYPERLINK l _Toc168817017 3.7通過ROWID訪問表 PAGEREF _Toc168817017 h 11 HYPERLINK l _Toc168817018 3.8必要時，可在Oracle Sta

5、ndard Table上加索引 PAGEREF _Toc168817018 h 12 HYPERLINK l _Toc168817019 3.9合理排列WHERE子句中的連接順序 PAGEREF _Toc168817019 h 12 HYPERLINK l _Toc168817020 3.10合理排列FROM中的表名 PAGEREF _Toc168817020 h 13 HYPERLINK l _Toc168817021 3.11用WHERE子句替換HAVING子句 PAGEREF _Toc168817021 h 13 HYPERLINK l _Toc168817022 3.12關于使用索引（

6、Index）的一些注意點 PAGEREF _Toc168817022 h 13 HYPERLINK l _Toc168817023 3.13識別 “低效運行”的SQL語句 PAGEREF _Toc168817023 h 15 HYPERLINK l _Toc168817024 3.14一些特別的用法，不一定對提升Performance有效，此處僅列其使用方法，以供借鑒 PAGEREF _Toc168817024 h 15 HYPERLINK l _Toc168817025 3.15Query以外的SQL語句的Tuning PAGEREF _Toc168817025 h 18 HYPERLINK

7、 l _Toc168817026 4SQL語句的Tuning建議步驟 PAGEREF _Toc168817026 h 18 HYPERLINK l _Toc168817027 5金玉良言 PAGEREF _Toc168817027 h 19序言本文檔基于版本進行了更新和補充，由PTEG(Performance Tuning Expert Group, 2006-10-5成立)小組進行重新整理，生成了該1.1版本。這份文檔主要將影響SQL Performance的因素做個基礎的闡述，再將大家在平時寫SQL積累的經(jīng)驗做個總結(jié)。希望本文檔可以成為一份有價值的入門級資料。影響SQL Performan

8、ce的關鍵因素和配置：Oracle Database上的設置對Performance的影響很大，如Shared Pool Size、Buffer Cache Size、SGA Structures、Database I/O Configuration、Rollback Segments等等，這些是DBA要更據(jù)實際狀況去Tunning的部分，我們暫不詳細討論，DBA可對這塊進行補充；這份文檔我們將重點放在影響SQL執(zhí)行效率的一些關鍵因素和設置上。關于執(zhí)行計劃我們在平時工作中用到大量的View，View中SQL的寫法對performance的影響很大，首先有必要了解一條SQL語句是如何被執(zhí)行的。當

9、SQL語句進入Oracle的緩存后，在該語句準備執(zhí)行之前，DBMS將執(zhí)行下列步驟：SQL語法檢查：檢查SQL語句拼寫是否正確和詞序。SQL語義分析：核實所有的與數(shù)據(jù)字典不一致的表和列的名字。生成執(zhí)行計劃：使用優(yōu)化規(guī)則和數(shù)據(jù)字典中的統(tǒng)計表來決定最佳執(zhí)行計劃。建立可執(zhí)行的二進制代碼：基于執(zhí)行計劃，Oracle生成二進制執(zhí)行代碼。抓取并返回需要的數(shù)據(jù)。其中第三步生成執(zhí)行計劃非常關鍵，所謂執(zhí)行計劃，就是對一個查詢?nèi)蝿?，做出一份怎樣去完成任務的詳細方案。對于查詢而言，我們提交的SQL僅僅是描述出了我們的目的，但Oracle內(nèi)部怎么去得到這些數(shù)據(jù)，是由數(shù)據(jù)庫DBMS來決定的。所以執(zhí)行計劃產(chǎn)生的好壞直接影響

10、SQL 運行的Performance。我們平時對SQL做一些Tuning，為了得到相同的數(shù)據(jù)而去嘗試用不同的SQL寫法，目的就是能讓Oracle根據(jù)你的語句產(chǎn)生一個更好的執(zhí)行計劃，從而得到更好的performance。ORACLE優(yōu)化器在不同的情況下，同一條SQL可能有多種執(zhí)行計劃。但理論上在某一時點，一定只有一種執(zhí)行計劃是最優(yōu)的、花費時間是最少的。執(zhí)行計劃的工作是由優(yōu)化器(Optimizer)來完成的， 那優(yōu)化器是依據(jù)什么訊息去創(chuàng)建出最合理的執(zhí)行計劃呢？回答這個問題前先要了解一下ORACLE的優(yōu)化器：ORACLE優(yōu)化器的優(yōu)化方式ORACLE優(yōu)化器的優(yōu)化方式有兩大類，即基于規(guī)則的優(yōu)化方式(Ru

11、le-Based Optimization,簡稱為RBO)和基于代價的優(yōu)化方式(Cost-Based Optimization,簡稱為CBO)。RBO方式：優(yōu)化器在分析SQL語句時，根據(jù)數(shù)據(jù)庫中表和索引等定義信息，遵循的是Oracle內(nèi)部預定的一些規(guī)則。比如我們常見的：當一個where子句中的一列有索引時去走索引而不走全表掃描。 CBO方式：依詞義可知,它是看語句的代價(Cost)了?；诖鷥r的查詢，數(shù)據(jù)庫根據(jù)搜集的表和索引的數(shù)據(jù)的統(tǒng)計信息（統(tǒng)計信息通過analyze 命令或者使用dbms_stats包來搜集）綜合來決定選取一個數(shù)據(jù)庫認為最優(yōu)的執(zhí)行計劃（實際上不一定最優(yōu)）。統(tǒng)計信息給出表的大小

12、、有多少行、每行的長度等信息。注意：這些統(tǒng)計信息起初在庫內(nèi)是沒有的，是根據(jù)analyze 命令或者dbms_stats包來定期搜集后才出現(xiàn)的，所以很多的時侯過期統(tǒng)計信息會令優(yōu)化器做出一個錯誤的執(zhí)行計劃，因些我們應及時更新這些信息。為了使用基于成本的優(yōu)化器(CBO) , 你必須經(jīng)常運行analyze或dbms_stats命令,以增加數(shù)據(jù)庫中的對象統(tǒng)計信息(object statistics)的準確性。在Oracle8及以后的版本,Oracle強列推薦用CBO的方式。優(yōu)化器的優(yōu)化模式(Optermizer Mode)優(yōu)化模式包括Rule，Choose，F(xiàn)irst rows，All rows這四種方

13、式，先解釋一下：Rule：即走基于規(guī)則的方式。First_Rows：基于成本的方式。指執(zhí)行計劃采用最少資源盡快的返回部分結(jié)果給客戶端，它將是以最快的方式返回查詢的最先的幾行,從總體上減少了響應時間，對于排序分頁頁顯示這種查詢尤其適用。All_Rows：基于成本的方式。當一個表有統(tǒng)計信息時，它將以最快的方式返回表的所有的行，從總體上提高查詢的吞吐量。沒有統(tǒng)計信息則走基于規(guī)則的方式。Choose：這是我們應關注的，默認的情況下Oracle用的便是這種方式。指的是當一個表或索引有統(tǒng)計信息（指運行過analyze 命令或者使用過dbms_stats包來搜集），則走CBO的方式 （在CHOOSE模式下O

14、RACLE采用的是 FIRST_ROWS）；如果表或索引沒有統(tǒng)計信息，那么走RBO的方式。注：Oracle ERP 11i之前的版本，默認用RULE；Oracle ERP 11i之后的版本，默認用CHOOSE。Optimizer mode優(yōu)化模式級別的設定：A、Instance級別：我們可以通過在.ora文件中設定OPTIMIZER_MODE=去選用。B、Session級別：通過SQL ALTER SESSION SET OPTIMIZER_MODE=;來設定。C、語句級別，這些需要用到Hint,比如:SELECT /*+ rule */ ordh.order_number,ordl.orde

15、red_item FROM apps.oe_order_headers_all ordh, apps.oe_order_lines_all ordl WHERE ordh.header_id = ordl.header_id;查看對象統(tǒng)計信息(object statistics)對CBO模式，對象統(tǒng)計信息至關重要。如何查看對象統(tǒng)計信息(object statistics)？Oracle中關于表的統(tǒng)計信息是在數(shù)據(jù)字典中的，可以下SQL查詢到，eg：SELECT table_name ,num_rows ,blocks ,empty_blocks AS empty ,avg_space ,chai

16、n_cnt ,avg_row_len FROM dba_tables WHERE owner = ONT AND table_name = OE_ORDER_LINES_ALLTABLE_NAMENUM_ROWSBLOCKSEMPTYAVG_SPACECHAIN_CNTAVG_ROW_LENOE_ORDER_LINES_ALL5344505500441可以看到數(shù)據(jù)字典中統(tǒng)計到的該表有5344筆記錄，我們下SQL驗證一下：SELECT COUNT (*) FROM apps.oe_order_lines_all發(fā)現(xiàn)返回是16518筆記錄，可見這個表的統(tǒng)計信息是比較陳舊的，真實數(shù)據(jù)與統(tǒng)計到的數(shù)據(jù)有

17、較大的差別。在這種情況下，如果某個View用到此Table，且系統(tǒng)使用CBO的方式，則可能導致Oracle的optimizer給出效率低下的執(zhí)行計劃。此時可以用ANALYZE去重新統(tǒng)計OE_ORDER_LINES_ALL這個表，可以下SQL：ANALYZE TABLE ont.oe_order_lines_all COMPUTE STATISTICS;再次Query數(shù)據(jù)字典：TABLE_NAMENUM_ROWSBLOCKSEMPTYAVG_SPACECHAIN_CNTAVG_ROW_LENOE_ORDER_LINES_ALL1651815301035865257643發(fā)現(xiàn)此時的信息已是最新的了

18、。有了比較正確的統(tǒng)計信息，optimizer才能給出高效的執(zhí)行計劃。結(jié)合BenQ Oracle ERP優(yōu)化模式(Optermizer Mode)我們再來看一下我們ERP的DB的設置情況：以下是從我們ERP中BQE Production 環(huán)境取到的一些設置：/disk/BQE/bqedb/dbs/ifilecbo.ora# optimizer_mode# Prior to 11i, optimizer_mode was always set to rule. For 11i,# choose is mandatory. Although Applications modules will set

19、 the# optimizer mode to either first_rows or all_rows, depending on whether# online or batch, an Applications database MUST BE STARTED with the# optimizer mode set to CHOOSE. Many of the system dictionary views,in # particular export, still require the RBO.# # In general, the profile options will en

20、sure that on-line users use# first_rows, batch jobs use all_rows. # IMPORTANT : The CBO requires accurate table and index statistics,# and FND_STATS should be run regularly. See the FND# documentation for further details.#optimizer_mode = choose 可以看到默認Oracle ERP11i用的optimizer_mode是choose，且Oracle強烈建議

21、要定期運行FND_STATS。關于 Gather說到定期運行FND_STATS，不知大家是否會聯(lián)想到Oracle ERP中的一個Request：Gather？ Oracle ERP中有幾個與Gather有關的標準Request：Gather All Column Statistics-FND_STATS.GATHER_ALL_COLUMN_STATS()Gather Column Statistics-FND_STATS.GATHER_COLUMN_STATS()Gather Schema Statistics-FND_STATS.GATHER_SCHEMA_STATS()Gather Tab

22、le Statistics-FND_STATS.GATHER_TABLE_STATS()查看FND_STATS 這個Package的寫法，其實它就是在調(diào)用Oracle DB中Standard的Package dbms_stats 中的某些Function。Oracle DB中常用的Gather有以下一些，DBA也可以直接在Database級別上定期Run這些Function，以便能讓Oracle統(tǒng)計到最新的數(shù)據(jù)庫狀況：dbms_stats.gather_database_stats();dbms_stats.gather_schema_stats();dbms_stats.gather_tab

23、le_stats(); dbms_stats.gather_index_stats();跟蹤 SQL實際運行的Cost執(zhí)行計劃是Oracle根據(jù)一些統(tǒng)計信息去“估計”出各個步驟所耗的Cost，與實際的執(zhí)行過程所耗Cost不見得一樣。實際執(zhí)行過程耗的CPU、Disk IO等資源的數(shù)量可以通過sql_trace統(tǒng)計出來。所以Tuning SQL不僅要看“執(zhí)行計劃”，有時還必須結(jié)合trace的Log去分析。For example，若我要查某段程序運行過程的所有SQL的Performance情況;可以程序邏輯開始前加上sql_trace=true，結(jié)束前結(jié)束sql_trace=false即可：alte

24、r session set sql_trace=true;程序邏輯herealter session set sql_trace=false;然后去OS上去找出這個trace file，用tkprof 去轉(zhuǎn)換，然后再看Log的詳細內(nèi)容。DEV2: /disk/DEV2/dev2db/admin/DEV2/udumptkprof dev2_ora_13148.trc log.txt附檔是轉(zhuǎn)出來的例子, 記得 , CPU + DISK 用的比較少的, 就會比較好!小結(jié)根據(jù)以上一些理論和我們ERP上的實際狀況，我們可以得到一些建議：（1）因為在Instance Level我們的optimizer_m

25、ode = choose ，所以定期運行ANALYZE 或dbms_stats非常重要，尤其是當上次統(tǒng)計后，數(shù)據(jù)量已發(fā)生較大變化之后。注意：統(tǒng)計操作是很耗資源的動作，要在系統(tǒng)Loading小的時候進行。（2）因為optimizer_mode優(yōu)化模式可以設定Sessions級別和語句級別，所以必要時可以通過改optimizer_mode的方式讓提高Performance。例如，某報表的View是EIS類型的，需要一次抓得所有資料，則可以使用Hint的方式使該SQL的optimizer_mode= ALL_ROWS，讓Oracle優(yōu)化器產(chǎn)生更好的執(zhí)行計劃。SQL語句的Tuning經(jīng)驗分享：絕大多數(shù)

26、情況下not exists比not in 效率高低效:（DEV2: 5秒）SELECT ordl.ordered_item FROM apps.oe_order_lines_all ordl WHERE ordl.header_id NOT IN (SELECT header_id FROM apps.oe_order_headers_all ordh WHERE ordh.flow_status_code = CLOSED);高效:（DEV2: 1秒）SELECT ordl.ordered_item FROM apps.oe_order_lines_all ordl WHERE NOT EX

27、ISTS ( SELECT 1 FROM apps.oe_order_headers_all ordh WHERE ordh.flow_status_code = CLOSED AND ordh.header_id = ordl.header_id);注：exists和 in 相比，效率有高有低，沒有明顯的差別。UNION ALL效率比UNION高很多（DEV2: 5秒）SELECT BQC_ORDER, ordh.order_number FROM apps.oe_order_headers_all ordh WHERE _id = 82UNIONSELECT WGQ_ORD

28、ER, ordh.order_number FROM apps.oe_order_headers_all ordh WHERE _id = 93（DEV2: 0.5秒）SELECT BQC_ORDER, ordh.order_number FROM apps.oe_order_headers_all ordh WHERE _id = 82UNION ALLSELECT WGQ_ORDER, ordh.order_number FROM apps.oe_order_headers_all ordh WHERE _id = 93一些很耗資源的SQL操

29、作，在不必要的情況下不要使用Select *，Order by，Group by，Distinct, UNION, MINUS，INTERSECT 操作是相當耗時的，在View中能不使用就不要使用，Eg 1: order by（DEV2:0.4秒）SELECT * FROM apps.oe_order_lines_all ordl;（DEV2:23秒）SELECT * FROM apps.oe_order_lines_all ordlORDER BY ordl.creation_date;Eg 2: group by（DEV2: 9秒）SELECT SUBSTR (ordh.order_num

30、ber, 1, 50) AS order_number , ( NVL (SUM (ordl.ordered_quantity * ordl.unit_selling_price), 0) + NVL (SUM (ordl.tax_value), 0) ) AS amount FROM apps.oe_order_headers_all ordh ,apps.oe_order_lines_all ordl WHERE ordh.header_id = ordl.header_idGROUP BY SUBSTR (ordh.order_number, 1, 50);（DEV2: 0.5秒）SEL

31、ECT SUBSTR (ordh.order_number, 1, 50) AS order_number , (SELECT ( NVL (SUM (ordl.ordered_quantity * ordl.unit_selling_price) ,0 ) + NVL (SUM (ordl.tax_value), 0) ) FROM apps.oe_order_lines_all ordl WHERE header_id = ordh.header_id) AS amount FROM apps.oe_order_headers_all ordh;Eg 3: distinct（DEV2: 5

32、0秒）SELECT DISTINCT rctl.customer_trx_line_id ,rctl.inventory_item_id ,rctl.description item_desc ,rctl.extended_amount extended_amount ,itm.attribute1 AS item_type FROM apps.ra_customer_trx_lines_all rctl ,apps.mtl_system_items_b itm WHERE rctl.inventory_item_id = itm.inventory_item_id(+) AND rctl.l

33、ine_type = LINE;（DEV2: 0.1秒）SELECT rctl.customer_trx_line_id ,rctl.inventory_item_id ,rctl.description item_desc ,rctl.extended_amount extended_amount , (SELECT itm.attribute1 FROM apps.mtl_system_items_b itm WHERE rctl.inventory_item_id = itm.inventory_item_id AND ROWNUM = 1) item_type FROM apps.ra

34、_customer_trx_lines_all rctl ,apps.ra_customer_trx_all rcta WHERE rctl.customer_trx_id = rcta.customer_trx_id AND rctl.line_type = LINE;通常, 帶有UNION, MINUS , INTERSECT的SQL語句都可以用其它方式重寫.如果你的數(shù)據(jù)庫的SORT_AREA_SIZE調(diào)配得好, 使用UNION, MINUS, INTERSECT也是可以考慮的, 畢竟它們的可讀性很強。通常聯(lián)接查詢比子查詢的效率要高很多盡量在 SELECT 子句里面用聯(lián)接查詢，少子查詢。因

35、為Subquery所得到的子Table的數(shù)據(jù)量等訊息是Oracle無法事前統(tǒng)計出來的，所以優(yōu)化器也很難得出一個優(yōu)化過的執(zhí)行計劃。子查詢在以下情況比較適合：適合用于小 table。也就是說, 以大tables為base做join, 小table 做 subquery. (大 tables 之間最好用直接 join)不想該 table 成為限制條件時也可以勉強使用。需要取subquery 中summary 的值時可以考慮 因為 Group by (和 distinct)也是 SQL performance 的一大挑戰(zhàn)。用table 索引（index）欄位去做Table間的關聯(lián)，可避免費時的全表掃描

36、如果Table上有索引，則系統(tǒng)訪問帶索引的Field時，可通過訪問索引中的欄位來快速獲得相對應記錄的ROWID，而通常情況下，使用索引比全表掃描要塊幾倍乃至幾千倍。Oracle ERP中幾乎所有的Table都設有Index，盡量以Index中的欄位做 join，避免用我們認為值是唯一的欄位去串: 例如 sales order number; transaction number; inventory org code等eg，使用OM Sale Order的主檔表oe_order_headers_all 和fnd_lookup_values 串一個簡單的sql（這兩張table的數(shù)據(jù)量都會很大）

37、fnd_lookup_values的index有兩個：INDEX applsys.fnd_lookup_values_u1 ON applsys.fnd_lookup_values ( lookup_type ASC, view_application_id ASC, lookup_code ASC, security_group_id ASC, language ASC )INDEX applsys.fnd_lookup_values_u2 ON applsys.fnd_lookup_values ( lookup_type ASC, view_application_id ASC, mea

38、ning ASC, security_group_id ASC, language ASC )SQL1:SELECT order1.order_number, flv.meaning FROM apps.fnd_lookup_values flv ,apps.oe_order_headers_all order1 WHERE order1.shipping_method_code = flv.lookup_code使用時間：1.67sec，數(shù)據(jù)：22819筆（BQC環(huán)境）。這里只關聯(lián)了一個條件lookup_code，lookup_code的確是fnd_lookup_values Index：f

39、nd_lookup_values_u1中的一個欄位，但觀察執(zhí)行計劃，它沒有去用Index。為什么？因為Oracle中，用作Index的多個COLUMN是有順序的，就剛才那個例子用lookup_code做關聯(lián)，它是在Index中第三位。所以，Oracle優(yōu)化器會去分析：如果使用INDEX的話，就要先FULL SCAN lookup_type，接著是view_application_id，再找到lookup_code ，分析結(jié)果后認為這樣反而比FULL SCAN TABLE還要慢，所以執(zhí)行計劃沒有使用INDEX，直接采用全表掃描。有些文檔直接說：如果索引是建立在多個列上， 只有在它的第一個列(le

40、ading column) 被where子句引用時，優(yōu)化器才會選擇使用該索引。所以為了讓使用fnd_lookup_values的fnd_lookup_values_u1 index，用一下SQL:SQL2：SELECT order1.order_number, flv.meaning FROM apps.fnd_lookup_values flv ,apps.oe_order_headers_all order1 WHERE order1.shipping_method_code = flv.lookup_code AND flv.lookup_type = SHIP_METHOD時間：0.0

41、0sec（BQC環(huán)境）這個SQL中使用了LOOKUP_TYPE=SHIP_METHOD ，這個正好是INDEX的第一位。Oracle優(yōu)化器分析：執(zhí)行計劃如果使用INDEX，就馬上找到LOOKUP_TYPE=SHIP_METHOD 的INDEX記錄，COST最小，分析的結(jié)果就是使用了INDEX， performance得到提升。在View中盡量不要使用 Package/function 來得到欄位值，在view中盡量不要引用function，否則會增加一定的通訊開銷。簡單的判斷盡量用decode，nvl，case when等實現(xiàn)。通過ROWID訪問表 ORACLE 采用兩種訪問表中記錄的方式:

42、a. 全表掃描 全表掃描就是順序地訪問表中每條記錄. ORACLE采用一次讀入多個數(shù)據(jù)塊(database block)的方式優(yōu)化全表掃描. b. 通過ROWID訪問表 如果可以，強烈采用基于ROWID的訪問方式情況以提高訪問表的效率。ROWID包含了表中記錄的物理位置信息，ORACLE采用索引(INDEX)實現(xiàn)了數(shù)據(jù)和存放數(shù)據(jù)的物理位置(ROWID)之間的聯(lián)系， 通常索引提供了快速訪問ROWID的方法，因此那些基于索引列的查詢就可以得到性能上的提高。 必要時，可在Oracle Standard Table上加索引eg:BQC收單用到如下一個簡單的SQL，耗時要1秒左右；測試后發(fā)現(xiàn)只要用到fl

43、ow_status_code欄位，速度就很慢。SELECT ordl.ordered_item AS inventory_item ,ordl.subinventory , (NVL (ordl.ordered_quantity, 0) AS qty FROM apps.oe_order_lines_all ordl WHERE ordl.flow_status_code = ENTERED于是手工在apps.oe_order_lines_all加上索引：CREATE INDEX ont.oe_order_lines_q1 ON ont.oe_order_lines_all (flow_sta

44、tus_code ASC)再次運行此SQL，耗時基本為0秒。合理排列WHERE子句中的連接順序ORACLE采用自下而上的順序解析WHERE子句，根據(jù)這個原理，那些可以過濾掉最大數(shù)量記錄的條件最好寫在WHERE子句的末尾。雖然對簡單SQL，Oracle優(yōu)化器自動會去調(diào)整順序，但還是建議將能過濾掉最多記錄的Where條件放在最后。合理排列FROM中的表名ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫在最后的表(基礎表 driving table)將被最先處理. 在FROM子句中包含多個表的情況下,你必須選擇記錄條數(shù)最少的表作為基礎表.當ORACLE處理多個表時

45、, 會運用排序及合并的方式連接它們.首先,掃描第一個表(FROM子句中最后的那個表)并對記錄進行派序,然后掃描第二個表(FROM子句中最后第二個表),最后將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合并.注: 這兩點可不再考慮, CBO情況下ORACLE會自動調(diào)整連接順序用WHERE子句替換HAVING子句 避免使用HAVING子句, HAVING 只會在檢索出所有記錄之后才對結(jié)果集進行過濾。 這個處理需要排序,總計等操作。 如果能通過WHERE子句限制記錄的數(shù)目，那就能減少這方面的開銷。 例如: 低效：SELECT ordl.ordered_item ,SUM (ordl.orde

46、red_quantity * ordl.unit_selling_price) FROM apps.oe_order_lines_all ordlGROUP BY ordl.ordered_item HAVING ordl.ordered_item LIKE 00%;高效：SELECT ordl.ordered_item ,SUM (ordl.ordered_quantity * ordl.unit_selling_price) FROM apps.oe_order_lines_all ordl WHERE ordl.ordered_item LIKE 00%GROUP BY ordl.ord

47、ered_item;關于使用索引（Index）的一些注意點而通常情況下，使用索引比全表掃描要塊幾倍至幾千倍，所以對索引要有比較深入的了解。某些情況下SELECT 語句中的WHERE子句用到索引列，但生成的執(zhí)行計劃卻不不使用索引。這里有一些例子. !=,NOT操作將不使用索引. 記住, 索引只能告訴你什么存在于表中, 而不能告訴你什么不存在于表中 不使用索引: SELECT account_name FROM TRANSACTION WHERE amount != 0|是字符連接函數(shù). 就象其它函數(shù)那樣, 停用了索引. 不使用索引: SELECT account_name, amount FRO

48、M TRANSACTION WHERE account_name | account_type = AMEXA使用索引: SELECT account_name, amount FROM TRANSACTION WHERE account_name = AMEX AND account_type = A相同的索引列不能互相比較,這將會啟用全表掃描. 不使用索引: SELECT account_name, amount FROM TRANSACTION WHERE account_name = NVL (:acc_name, account_name)使用索引: SELECT account_n

49、ame, amount FROM TRANSACTION WHERE account_name LIKE NVL (:acc_name, %)如果一定要對使用函數(shù)的列啟用索引, ORACLE新的功能: 基于函數(shù)的索引(Function-Based Index) 也許是一個較好的方案. CREATE INDEX emp_i ON emp (UPPER(ename);/*建立基于函數(shù)的索引*/ SELECT * FROM emp WHERE UPPER(ename) = BLACKSNAIL; /*將使用索引*/避免在索引列上使用計算 WHERE子句中，如果索引列是函數(shù)的一部分，優(yōu)化器將不使用索引

50、而使用全表掃描。這是一個非常實用的規(guī)則，請務必牢記。舉例: 低效： SELECT FROM dept WHERE sal * 12 25000高效: SELECT FROM dept WHERE sal 25000 / 12基于成本的優(yōu)化器(CBO)會對索引的選擇性進行判斷，來決定是否使用索引 索引的選擇性: 那就是說Table中該Field的所有值中不重復的索引鍵值的比率。比如， 表中共有100條記錄而其中有80個不重復的索引鍵值，這個索引的選擇性就是80/100 = 0.8 。選擇性越高, 通過索引鍵值檢索出的記錄就越少。如果索引的選擇性很低，檢索數(shù)據(jù)就需要大量的索引范圍查詢操作和ROWI

51、D 訪問表的操作， 也許會比全表掃描的效率還低。 Index信息的重新統(tǒng)計有時候，陳舊的Index信息會Oracle產(chǎn)生的執(zhí)行計劃不使用Index，所以對Oracle Index 的Statistics也需要經(jīng)常去做。Oracle ERP中的Analyze All Index Column Statistics- Analyze All Index Column Statistics就是在做這樣的動作。分析函數(shù)Package使索引失效當你所Query的View中使用了分析函數(shù)，它會導致一段調(diào)用的Package的SQL中，Oracle對Table的統(tǒng)計信息失誤，進行CBO分析出的執(zhí)行計劃走了低效

52、路徑。舉例：不使用索引：SELECT rcv.hawb FROM apps.xxpo_rcv_ship_h_l_v rcv WHERE rcv.sort_no = 1 AND rcv.po_no = NVL (apps.xxom_shipreport_pkg.get_dropship_po_number (7345) ,0000000 );Blocks: 1,126 Est. Rows: 17,010 of 17,010 Cost: 173使用索引：SELECT rcv.hawb FROM apps.xxpo_rcv_ship_h_l_v rcv WHERE rcv.sort_no = 1 A

53、ND rcv.po_no = NVL (, )Blocks: 1,126 Est. Rows: 1 of 17,010 Cost: 3View中有如下一列值ROW_NUMBER() OVER(PARTITION BY h.ordno ORDER BY h.rowid) AS SORT_NO訪分析函數(shù)的使用導致使用這個View去查詢時，若調(diào)Package，則無法走索引，它會令Oracle統(tǒng)計信息錯誤。識別 “低效運行”的SQL語句 用下列語句找出與我們客制有關的低效SQL:SELECT executions ,disk_reads ,buffer_gets ,ROUND (buffer_gets

54、 - disk_reads) / buffer_gets, 2) hit_radio ,ROUND (disk_reads / executions, 2) reads_per_run ,sql_text FROM v$sqlarea WHERE sql_text LIKE %XX% AND executions 0 AND buffer_gets 0 AND (buffer_gets - disk_reads) / buffer_gets 0.8ORDER BY 4 DESC一些特別的用法，不一定對提升Performance有效，此處僅列其使用方法，以供借鑒分析函數(shù)簡介分析函數(shù)表達方法FUN

55、CTION_NAME ( )OVER ( , , )說明：1.Function Name：分析函數(shù)的名稱，Oracle 有二十多個分析函數(shù)，其中一些與聚合函數(shù)同名2.OVER：分析函數(shù)的關鍵字，這是區(qū)分同名的分析函數(shù)與聚合函數(shù)的關鍵字3.Partition Clause：分區(qū)子句，作用類似于 Group By，將結(jié)果集分組，分析函數(shù)分別作用于每一組，在各組內(nèi)獨立計算，當切換組時，計算結(jié)果復位并重新開始計算。查詢中分析函數(shù)的每個實例允許有不同的分區(qū)子句。4.Order by Clause：排序子句，排序子句指定數(shù)據(jù)在每個組（分區(qū)）內(nèi)是按什么樣的順序來計算的。ORDER BY expr 5.Win

56、dowing Clause:開窗子句，指定分析函數(shù)當前可見的窗口（作用域）與 Partition Clause 的區(qū)別ROWS/RANGE UNBOUNDED/num PRECEDING-樣例With Clause -樣例CREATE OR REPLACE VIEW zz_test_viewAS WITH ou AS (SELECT * FROM apps.hr_operating_units) SELECT h.* FROM apps.oe_order_headers_all h ,ou WHERE _id = anization_id-樣例(/*+ materiali

57、ze */）CREATE OR REPLACE VIEW zz_test_viewAS WITH ou AS (SELECT /*+ materialize */ * FROM apps.hr_operating_units) SELECT l.* FROM apps.oe_order_lines_all l ,ou WHERE _id = anization_id動態(tài)SQL-樣例EXECUTE IMMEDIATE SELECT NVL( SUM( NVL( fulfilled_quantity, 0 ), 0 ) FROM apps.oe_order_lines_all

58、WHERE NVL( line_set_id, 0 ) = :p_line_set_id AND line_number = :p_line_number AND header_id = :p_header_id INTO v_shipped_quantityUSING p_line_set_id, p_line_number, p_header_id;-游標變量綁定樣例(1)把游標聲明成 Reference Cursor 類型，即所謂的“游標變量”，聲明的時候是不需要指定 SELECT 語句的。(2)在 Open 的時候，才指定 SELECT 語句，這時候就可以套用動態(tài) SQL 了。OPEN

59、 cur_test FOR SELECT name FROM apps.xx_test_d_sql WHERE id = :id USING v_id;Hint說明：這種方法通常不一定奏效，因為Oracle CBO執(zhí)行計劃生成的是不無道理的。只有特定情況下，才會強制HINT。以下舉幾種強制索引的方式：FULL hint 告訴ORACLE使用全表掃描的方式訪問指定表.例如: SELECT /*+ FULL(EMP) */ * FROM EMP WHERE EMPNO = 7893; ROWID hint 告訴ORACLE使用TABLE ACCESS BY ROWID的操作訪問表. 通常, 你需要采用TABLE ACCESS BY ROWID的方式特別是當訪問大表的時候, 使用這種方式, 你需要知道ROIWD的值或者使用索引. 如果一個大表沒有被設定為緩存(CACHED)表而你希望它的數(shù)據(jù)在查詢結(jié)束是仍然停留在SGA中,你就可以使用CACHE hint 來告訴優(yōu)化器把數(shù)據(jù)保留在SGA中. 通常CACHE hint 和 FULL hint 一起使用.例如:SELECT /*+ FULL(WORKER) CACHE(WORKER)*/ *FROM WORK; 索引hint