Oracle查詢速度優(yōu)化問題

1. 選用適合的ORACLE優(yōu)化器

創(chuàng)新互聯建站成立以來不斷整合自身及行業(yè)資源、不斷突破觀念以使企業(yè)策略得到完善和成熟，建立了一套“以技術為基點，以客戶需求中心、市場為導向”的快速反應體系。對公司的主營項目，如中高端企業(yè)網站企劃 / 設計、行業(yè) / 企業(yè)門戶設計推廣、行業(yè)門戶平臺運營、App定制開發(fā)、手機網站制作設計、微信網站制作、軟件開發(fā)、服務器主機托管等實行標準化操作，讓客戶可以直觀的預知到從創(chuàng)新互聯建站可以獲得的服務效果。

ORACLE的優(yōu)化器共有3種:

a. RULE (基于規(guī)則) b. COST (基于成本) c. CHOOSE (選擇性)

設置缺省的優(yōu)化器,可以通過對init.ora文件中OPTIMIZER_MODE參數的各種聲明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你當然也在SQL句級或是會話(session)級對其進行覆蓋.

為了使用基于成本的優(yōu)化器(CBO, Cost-Based Optimizer) , 你必須經常運行analyze 命令,以增加數據庫中的對象統(tǒng)計信息(object statistics)的準確性.

如果數據庫的優(yōu)化器模式設置為選擇性(CHOOSE),那么實際的優(yōu)化器模式將和是否運行過analyze命令有關. 如果table已經被analyze過, 優(yōu)化器模式將自動成為CBO , 反之,數據庫將采用RULE形式的優(yōu)化器.

在缺省情況下,ORACLE采用CHOOSE優(yōu)化器, 為了避免那些不必要的全表掃描(full table scan) , 你必須盡量避免使用CHOOSE優(yōu)化器,而直接采用基于規(guī)則或者基于成本的優(yōu)化器.

2. 訪問Table的方式

ORACLE 采用兩種訪問表中記錄的方式:

a. 全表掃描

全表掃描就是順序地訪問表中每條記錄. ORACLE采用一次讀入多個數據塊(database block)的方式優(yōu)化全表掃描.

b. 通過ROWID訪問表

你可以采用基于ROWID的訪問方式情況,提高訪問表的效率, , ROWID包含了表中記錄的物理位置信息..ORACLE采用索引(INDEX)實現了數據和存放數據的物理位置(ROWID)之間的聯系. 通常索引提供了快速訪問ROWID的方法,因此那些基于索引列的查詢就可以得到性能上的提高.

3. 共享SQL語句

為了不重復解析相同的SQL語句,在第一次解析之后, ORACLE將SQL語句存放在內存中.這塊位于系統(tǒng)全局區(qū)域SGA(system global area)的共享池(shared buffer pool)中的內存可以被所有的數據庫用戶共享. 因此,當你執(zhí)行一個SQL語句(有時被稱為一個游標)時,如果它

和之前的執(zhí)行過的語句完全相同, ORACLE就能很快獲得已經被解析的語句以及最好的

執(zhí)行路徑. ORACLE的這個功能大大地提高了SQL的執(zhí)行性能并節(jié)省了內存的使用.

可惜的是ORACLE只對簡單的表提供高速緩沖(cache buffering) ,這個功能并不適用于多表連接查詢.

數據庫管理員必須在init.ora中為這個區(qū)域設置合適的參數,當這個內存區(qū)域越大,就可以保留更多的語句,當然被共享的可能性也就越大了.

當你向ORACLE 提交一個SQL語句,ORACLE會首先在這塊內存中查找相同的語句.

這里需要注明的是,ORACLE對兩者采取的是一種嚴格匹配,要達成共享,SQL語句必須完全相同(包括空格,換行等).

4.選擇最有效率的表名順序(只在基于規(guī)則的優(yōu)化器中有效)

ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫在最后的表(基礎表 driving table)將被最先處理. 在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎表.當ORACLE處理多個表時, 會運用排序及合并的方式連接它們.首先,掃描第一個表(FROM子句中最后的那個表)并對記錄進行派序,然后掃描第二個表(FROM子句中最后第二個表),最后將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合并.如果有3個以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎表, 交叉表是指那個被其他表所引用的表.

5. WHERE子句中的連接順序．

ORACLE采用自下而上的順序解析WHERE子句,根據這個原理,表之間的連接必須寫在其他WHERE條件之前, 那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾.

6. SELECT子句中避免使用 ‘ * ‘

當你想在SELECT子句中列出所有的COLUMN時,使用動態(tài)SQL列引用 ‘*' 是一個方便的方法.不幸的是,這是一個非常低效的方法. 實際上,ORACLE在解析的過程中, 會將'*' 依次轉換成所有的列名, 這個工作是通過查詢數據字典完成的, 這意味著將耗費更多的時間.

7. 減少訪問數據庫的次數

當執(zhí)行每條SQL語句時, ORACLE在內部執(zhí)行了許多工作: 解析SQL語句, 估算索引的利用率, 綁定變量 , 讀數據塊等等. 由此可見, 減少訪問數據庫的次數 , 就能實際上減少ORACLE的工作量.

注意: 在SQL*Plus , SQL*Forms和Pro*C中重新設置ARRAYSIZE參數, 可以增加每次數據庫訪問的檢索數據量 ,建議值為200.

8. 使用DECODE函數來減少處理時間

使用DECODE函數可以避免重復掃描相同記錄或重復連接相同的表.

9. 整合簡單,無關聯的數據庫訪問

如果你有幾個簡單的數據庫查詢語句,你可以把它們整合到一個查詢中(即使它們之間沒有關系)

10. 刪除重復記錄

最高效的刪除重復記錄方法 ( 因為使用了ROWID)

DELETE FROM EMP E

WHERE E.ROWID (SELECT MIN(X.ROWID)

FROM EMP X

WHERE X.EMP_NO = E.EMP_NO);

11. 用EXISTS替代IN

在許多基于基礎表的查詢中,為了滿足一個條件,往往需要對另一個表進行聯接.在這種情況下, 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率.

12. 用NOT EXISTS替代NOT IN

在子查詢中,NOT IN子句將執(zhí)行一個內部的排序和合并. 無論在哪種情況下,NOT IN都是最低效的 (因為它對子查詢中的表執(zhí)行了一個全表遍歷). 為了避免使用NOT IN ,我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS

如何查看Oracle執(zhí)行計劃，并通過執(zhí)行計劃優(yōu)化SQL

一、通過PL/SQL Dev工具

1、直接File-New-Explain Plan Window，在窗口中執(zhí)行sql可以查看計劃結果。其中，Cost表示cpu的消耗，單位為n%，Cardinality表示執(zhí)行的行數，等價Rows。

2、先執(zhí)行 EXPLAIN PLAN FOR select * from tableA where paraA=1，再 select * from table(DBMS_XPLAN.DISPLAY)便可以看到oracle的執(zhí)行計劃了，看到的結果和1中的一樣，所以使用工具的時候推薦使用1方法。

注意：PL/SQL Dev工具的Command window中不支持set autotrance on的命令。還有使用工具方法查看計劃看到的信息不全，有些時候我們需要sqlplus的支持。

如何進行oracle數據庫性能優(yōu)化

你最好買一本專門講ORACLE性能優(yōu)化的書，好好看看\x0d\x0a1、調整數據庫服務器的性能\x0d\x0aOracle數據庫服務器是整個系統(tǒng)的核心，它的性能高低直接影響整個系統(tǒng)的性能，為了調整Oracle數據庫服務器的性能，主要從以下幾個方面考慮： \x0d\x0a1.1、調整操作系統(tǒng)以適合Oracle數據庫服務器運行\(zhòng)x0d\x0aOracle數據庫服務器很大程度上依賴于運行服務器的操作系統(tǒng)，如果操作系統(tǒng)不能提供最好性能，那么無論如何調整，Oracle數據庫服務器也無法發(fā)揮其應有的性能。 \x0d\x0a1.1.1、為Oracle數據庫服務器規(guī)劃系統(tǒng)資源 \x0d\x0a據已有計算機可用資源, 規(guī)劃分配給Oracle服務器資源原則是：盡可能使Oracle服務器使用資源最大化,特別在Client/Server中盡量讓服務器上所有資源都來運行Oracle服務。 \x0d\x0a1.1.2、調整計算機系統(tǒng)中的內存配置 \x0d\x0a多數操作系統(tǒng)都用虛存來模擬計算機上更大的內存，它實際上是硬盤上的一定的磁盤空間。當實際的內存空間不能滿足應用軟件的要求時，操作系統(tǒng)就將用這部分的磁盤空間對內存中的信息進行頁面替換，這將引起大量的磁盤I/O操作，使整個服務器的性能下降。為了避免過多地使用虛存，應加大計算機的內存。 \x0d\x0a1.1.3、為Oracle數據庫服務器設置操作系統(tǒng)進程優(yōu)先級 \x0d\x0a不要在操作系統(tǒng)中調整Oracle進程的優(yōu)先級，因為在Oracle數據庫系統(tǒng)中，所有的后臺和前臺數據庫服務器進程執(zhí)行的是同等重要的工作，需要同等的優(yōu)先級。所以在安裝時，讓所有的數據庫服務器進程都使用缺省的優(yōu)先級運行。 \x0d\x0a1.2、調整內存分配\x0d\x0aOracle數據庫服務器保留3個基本的內存高速緩存，分別對應3種不同類型的數據：庫高速緩存，字典高速緩存和緩沖區(qū)高速緩存。庫高速緩存和字典高速緩存一起構成共享池，共享池再加上緩沖區(qū)高速緩存便構成了系統(tǒng)全程區(qū)(SGA)。SGA是對數據庫數據進行快速訪問的一個系統(tǒng)全程區(qū)，若SGA本身需要頻繁地進行釋放、分配，則不能達到快速訪問數據的目的，因此應把SGA放在主存中，不要放在虛擬內存中。內存的調整主要是指調整組成SGA的內存結構的大小來提高系統(tǒng)性能，由于Oracle數據庫服務器的內存結構需求與應用密切相關，所以內存結構的調整應在磁盤I/O調整之前進行。 \x0d\x0a1.2.1、庫緩沖區(qū)的調整 \x0d\x0a庫緩沖區(qū)中包含私用和共享SQL和PL/SQL區(qū)，通過比較庫緩沖區(qū)的命中率決定它的大小。要調整庫緩沖區(qū)，必須首先了解該庫緩沖區(qū)的活動情況，庫緩沖區(qū)的活動統(tǒng)計信息保留在動態(tài)性能表v$librarycache數據字典中，可通過查詢該表來了解其活動情況，以決定如何調整。 \x0d\x0a \x0d\x0aSelect sum(pins),sum(reloads) from v$librarycache; \x0d\x0a \x0d\x0aPins列給出SQL語句，PL/SQL塊及被訪問對象定義的總次數；Reloads列給出SQL 和PL/SQL塊的隱式分析或對象定義重裝載時在庫程序緩沖區(qū)中發(fā)生的錯誤。如果sum(pins)/sum(reloads) ≈0，則庫緩沖區(qū)的命中率合適；若sum(pins)/sum(reloads)1, 則需調整初始化參數 shared_pool_size來重新調整分配給共享池的內存量。 \x0d\x0a1.2.2、數據字典緩沖區(qū)的調整 \x0d\x0a數據字典緩沖區(qū)包含了有關數據庫的結構、用戶、實體信息。數據字典的命中率，對系統(tǒng)性能影響極大。數據字典緩沖區(qū)的使用情況記錄在動態(tài)性能表v$librarycache中，可通過查詢該表來了解其活動情況，以決定如何調整。 \x0d\x0a \x0d\x0aSelect sum(gets),sum(getmisses) from v$rowcache; \x0d\x0a \x0d\x0aGets列是對相應項請求次數的統(tǒng)計；Getmisses 列是引起緩沖區(qū)出錯的數據的請求次數。對于頻繁訪問的數據字典緩沖區(qū)，sum(getmisses)/sum(gets)10%～15%。若大于此百分數，則應考慮增加數據字典緩沖區(qū)的容量，即需調整初始化參數shared_pool_size來重新調整分配給共享池的內存量。 \x0d\x0a1.2.3、緩沖區(qū)高速緩存的調整 \x0d\x0a用戶進程所存取的所有數據都是經過緩沖區(qū)高速緩存來存取，所以該部分的命中率，對性能至關重要。緩沖區(qū)高速緩存的使用情況記錄在動態(tài)性能表v$sysstat中，可通過查詢該表來了解其活動情況，以決定如何調整。 \x0d\x0a \x0d\x0aSelect name,value from v$sysstat where name in ('dbblock gets','consistent gets','physical reads'); \x0d\x0a \x0d\x0adbblock gets和consistent gets的值是請求數據緩沖區(qū)中讀的總次數。physical reads的值是請求數據時引起從盤中讀文件的次數。從緩沖區(qū)高速緩存中讀的可能性的高低稱為緩沖區(qū)的命中率，計算公式： \x0d\x0a \x0d\x0aHit Ratio=1-(physical reds/(dbblock gets+consistent gets)) \x0d\x0a \x0d\x0a如果Hit Ratio60%～70%，則應增大db_block_buffers的參數值。db_block_buffers可以調整分配給緩沖區(qū)高速緩存的內存量，即db_block_buffers可設置分配緩沖區(qū)高速緩存的數據塊的個數。緩沖區(qū)高速緩存的總字節(jié)數=db_block_buffers的值*db_block_size的值。db_block_size 的值表示數據塊大小的字節(jié)數，可查詢 v$parameter 表： \x0d\x0a \x0d\x0aselect name,value from v$parameter where name='db_block_size'; \x0d\x0a \x0d\x0a在修改了上述數據庫的初始化參數以后，必須先關閉數據庫，在重新啟動數據庫后才能使新的設置起作用。

如何提高oracle模糊查詢的性能？

1、使用兩邊加‘%’號的查詢，Oracle是不通過索引的，所以查詢效率很低。

例如：select count(*) from lui_user_base t where t.user_name like '%cs%';

2、like '...%'和 like'%...'雖然走了索引，但是效率依然很低。

3、有人說使用如下sql，他的效率提高了10倍，但是數據量小的時候

select count(*) from lui_user_base where rowid in (select rowid from lui_user_base t where t.user_name like '%cs%')

我拿100w跳數據做了測試，效果一般，依然很慢，原因：

select rowid from lui_user_base t where t.user_name like '%cs%' ? 這條sql執(zhí)行很快，那是相當的快，但是放到select count(*) from lui_user_base where rowid in（）里后，效率就會變的很慢了。

4、select count(*) from lui_user_base t where instr(t.user_name,'cs') 0

這種查詢效果很好，速度很快，推薦使用這種。因為我對oracle內部機制不是很懂，只是對結果做了一個說明。

5、有人說了用全文索引，我看了，步驟挺麻煩，但是是個不錯的方法，留著備用：

對cmng_custominfo 表中的address字段做全文檢索：

1，在oracle9201中需要創(chuàng)建一個分詞的東西：

BEGIN

ctx_ddl.create_preference ('SMS_ADDRESS_LEXER', 'CHINESE_LEXER');

--ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer'); 不用

end;

2，創(chuàng)建全文檢索：

CREATE INDEX INX_CUSTOMINFO_ADDR_DOCS ON cmng_custominfo(address) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('LEXER SMS_ADDRESS_LEXER');

3，查詢時候，使用：

select * from cmng_custominfo where contains (address, '金色新城')1;

4，需要定期進行同步和優(yōu)化：

同步：根據新增記錄的文本內容更新全文搜索的索引。

begin

ctx_ddl.sync_index('INX_CUSTOMINFO_ADDR_DOCS');

end;

優(yōu)化：根據被刪除記錄清除全文搜索索引中的垃圾

begin

ctx_ddl.optimize_index('INX_CUSTOMINFO_ADDR_DOCS', 'FAST');

end;

5，采用job做步驟4中的工作：

1)該功能需要利用oracle的JOB功能來完成

因為oracle9I默認不啟用JOB功能，所以首先需要增加ORACLE數據庫實例的JOB配置參數:

job_queue_processes=5

重新啟動oracle數據庫服務和listener服務。

2)同步 和 優(yōu)化

--同步 sync:

variable jobno number;

BEGIN

DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.sync_index(''INX_CUSTOMINFO_ADDR_DOCS'');',

SYSDATE, 'SYSDATE + (1/24/4)');

commit;

END;

--優(yōu)化

variable jobno number;

begin

DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.optimize_index(''INX_CUSTOMINFO_ADDR_DOCS'',''FULL'');', SYSDATE, 'SYSDATE + 1');

commit;

END;

其中， 第一個job的SYSDATE + (1/24/4)是指每隔15分鐘同步一次，第二個job的SYSDATE + 1是每隔1天做一次全優(yōu)化。具體的時間間隔，可以根據應用的需要而定。

6，索引重建

重建索引會刪除原來的索引，重新生成索引，需要較長的時間。

重建索引語法如下：

ALTER INDEX INX_CUSTOMINFO_ADDR_DOCS REBUILD;

據網上一些用家的體會，oracle重建索引的速度也是比較快的，有一用家這樣描述：

Oracle 的全文檢索建立和維護索引要比ms sql server都要快得多，筆者的65萬記錄的一個表建立索引只需要20分鐘，同步一次只需要1分鐘。

因此，也可以考慮用job的辦法定期重建索引。