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關于執行計劃 PAGEREF _Toc168817005 h 3 HYPERLINK l _Toc168817006 2.2ORACLE優化器 PAGEREF _Toc16881

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

3、8817011 3.1絕大多數情況下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通常聯接查詢比子查詢的效率要高很多 PAGEREF _Toc168817014 h 10 HYP

4、ERLINK l _Toc168817015 3.5用table 索引（index）欄位去做Table間的關聯，可避免費時的全表掃描 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積累的經驗做個總結。希望本文檔可以成為一份有價值的入門級資料。影響SQL Performan

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

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

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

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

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

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

14、RACLE采用的是 FIRST_ROWS）；如果表或索引沒有統計信息，那么走RBO的方式。注：Oracle ERP 11i之前的版本，默認用RULE；Oracle ERP 11i之后的版本，默認用CHOOSE。Optimizer mode優化模式級別的設定：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;查看對象統計信息(object statistics)對CBO模式，對象統計信息至關重要。如何查看對象統計信息(object statistics)？Oracle中關于表的統計信息是在數據字典中的，可以下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可以看到數據字典中統計到的該表有5344筆記錄，我們下SQL驗證一下：SELECT COUNT (*) FROM apps.oe_order_lines_all發現返回是16518筆記錄，可見這個表的統計信息是比較陳舊的，真實數據與統計到的數據有

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

18、。有了比較正確的統計信息，optimizer才能給出高效的執行計劃。結合BenQ Oracle ERP優化模式(Optermizer Mode)我們再來看一下我們ERP的DB的設置情況：以下是從我們ERP中BQE Production 環境取到的一些設置：/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，不知大家是否會聯想到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的寫法，其實它就是在調用Oracle DB中Standard的Package dbms_stats 中的某些Function。Oracle DB中常用的Gather有以下一些，DBA也可以直接在Database級別上定期Run這些Function，以便能讓Oracle統計到最新的數據庫狀況：dbms_stats.gather_database_stats();dbms_stats.gather_schema_stats();dbms_stats.gather_tab

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

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

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

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語句都可以用其它方式重寫.如果你的數據庫的SORT_AREA_SIZE調配得好, 使用UNION, MINUS, INTERSECT也是可以考慮的, 畢竟它們的可讀性很強。通常聯接查詢比子查詢的效率要高很多盡量在 SELECT 子句里面用聯接查詢，少子查詢。因

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

36、如果Table上有索引，則系統訪問帶索引的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的數據量都會很大）

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，數據：22819筆（BQC環境）。這里只關聯了一個條件lookup_code，lookup_code的確是fnd_lookup_values Index：f

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

40、ading column) 被where子句引用時，優化器才會選擇使用該索引。所以為了讓使用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環境）這個SQL中使用了LOOKUP_TYPE=SHIP_METHOD ，這個正好是INDEX的第一位。Oracle優化器分析：執行計劃如果使用INDEX，就馬上找到LOOKUP_TYPE=SHIP_METHOD 的INDEX記錄，COST最小，分析的結果就是使用了INDEX， performance得到提升。在View中盡量不要使用 Package/function 來得到欄位值，在view中盡量不要引用function，否則會增加一定的通訊開銷。簡單的判斷盡量用decode，nvl，case when等實現。通過ROWID訪問表 ORACLE 采用兩種訪問表中記錄的方式:

42、a. 全表掃描 全表掃描就是順序地訪問表中每條記錄. ORACLE采用一次讀入多個數據塊(database block)的方式優化全表掃描. b. 通過ROWID訪問表 如果可以，強烈采用基于ROWID的訪問方式情況以提高訪問表的效率。ROWID包含了表中記錄的物理位置信息，ORACLE采用索引(INDEX)實現了數據和存放數據的物理位置(ROWID)之間的聯系， 通常索引提供了快速訪問ROWID的方法，因此那些基于索引列的查詢就可以得到性能上的提高。 必要時，可在Oracle Standard Table上加索引eg:BQC收單用到如下一個簡單的SQL，耗時要1秒左右；測試后發現只要用到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子句，根據這個原理，那些可以過濾掉最大數量記錄的條件最好寫在WHERE子句的末尾。雖然對簡單SQL，Oracle優化器自動會去調整順序，但還是建議將能過濾掉最多記錄的Where條件放在最后。合理排列FROM中的表名ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫在最后的表(基礎表 driving table)將被最先處理. 在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎表.當ORACLE處理多個表時

45、, 會運用排序及合并的方式連接它們.首先,掃描第一個表(FROM子句中最后的那個表)并對記錄進行派序,然后掃描第二個表(FROM子句中最后第二個表),最后將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合并.注: 這兩點可不再考慮, CBO情況下ORACLE會自動調整連接順序用WHERE子句替換HAVING子句 避免使用HAVING子句, HAVING 只會在檢索出所有記錄之后才對結果集進行過濾。 這個處理需要排序,總計等操作。 如果能通過WHERE子句限制記錄的數目，那就能減少這方面的開銷。 例如: 低效：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子句用到索引列，但生成的執行計劃卻不不使用索引。這里有一些例子. !=,NOT操作將不使用索引. 記住, 索引只能告訴你什么存在于表中, 而不能告訴你什么不存在于表中 不使用索引: SELECT account_name FROM TRANSACTION WHERE amount != 0|是字符連接函數. 就象其它函數那樣, 停用了索引. 不使用索引: 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, %)如果一定要對使用函數的列啟用索引, ORACLE新的功能: 基于函數的索引(Function-Based Index) 也許是一個較好的方案. CREATE INDEX emp_i ON emp (UPPER(ename);/*建立基于函數的索引*/ SELECT * FROM emp WHERE UPPER(ename) = BLACKSNAIL; /*將使用索引*/避免在索引列上使用計算 WHERE子句中，如果索引列是函數的一部分，優化器將不使用索引

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

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

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訪分析函數的使用導致使用這個View去查詢時，若調Package，則無法走索引，它會令Oracle統計信息錯誤。識別 “低效運行”的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有效，此處僅列其使用方法，以供借鑒分析函數簡介分析函數表達方法FUN

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

56、dowing Clause:開窗子句，指定分析函數當前可見的窗口（作用域）與 Partition Clause 的區別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動態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 語句，這時候就可以套用動態 SQL 了。OPEN

59、 cur_test FOR SELECT name FROM apps.xx_test_d_sql WHERE id = :id USING v_id;Hint說明：這種方法通常不一定奏效，因為Oracle CBO執行計劃生成的是不無道理的。只有特定情況下，才會強制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)表而你希望它的數據在查詢結束是仍然停留在SGA中,你就可以使用CACHE hint 來告訴優化器把數據保留在SGA中. 通常CACHE hint 和 FULL hint 一起使用.例如:SELECT /*+ FULL(WORKER) CACHE(WORKER)*/ *FROM WORK; 索引hint