㈠ Oracle查詢速度優化問題
1. 選用適合的ORACLE優化器
ORACLE的優化器共有3種:
a. RULE (基於規則) b. COST (基於成本) c. CHOOSE (選擇性)
設置預設的優化器,可以通過對init.ora文件中OPTIMIZER_MODE參數的各種聲明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你當然也在sql句級或是會話(session)級對其進行覆蓋.
為了使用基於成本的優化器(CBO, Cost-Based Optimizer) , 你必須經常運行analyze 命令,以增加資料庫中的對象統計信息(object statistics)的准確性.
如果資料庫的優化器模式設置為選擇性(CHOOSE),那麼實際的優化器模式將和是否運行過analyze命令有關. 如果table已經被analyze過, 優化器模式將自動成為CBO , 反之,資料庫將採用RULE形式的優化器.
在預設情況下,ORACLE採用CHOOSE優化器, 為了避免那些不必要的全表掃描(full table scan) , 你必須盡量避免使用CHOOSE優化器,而直接採用基於規則或者基於成本的優化器.
2. 訪問Table的方式
ORACLE 採用兩種訪問表中記錄的方式:
a. 全表掃描
全表掃描就是順序地訪問表中每條記錄. ORACLE採用一次讀入多個數據塊(database block)的方式優化全表掃描.
b. 通過ROWID訪問表
你可以採用基於ROWID的訪問方式情況,提高訪問表的效率, , ROWID包含了表中記錄的物理位置信息..ORACLE採用索引(INDEX)實現了數據和存放數據的物理位置(ROWID)之間的聯系. 通常索引提供了快速訪問ROWID的方法,因此那些基於索引列的查詢就可以得到性能上的提高.
3. 共享SQL語句
為了不重復解析相同的SQL語句,在第一次解析之後, ORACLE將SQL語句存放在內存中.這塊位於系統全局區域SGA(system global area)的共享池(shared buffer pool)中的內存可以被所有的資料庫用戶共享. 因此,當你執行一個SQL語句(有時被稱為一個游標)時,如果它
和之前的執行過的語句完全相同, ORACLE就能很快獲得已經被解析的語句以及最好的
執行路徑. ORACLE的這個功能大大地提高了SQL的執行性能並節省了內存的使用.
可惜的是ORACLE只對簡單的表提供高速緩沖(cache buffering) ,這個功能並不適用於多表連接查詢.
資料庫管理員必須在init.ora中為這個區域設置合適的參數,當這個內存區域越大,就可以保留更多的語句,當然被共享的可能性也就越大了.
當你向ORACLE 提交一個SQL語句,ORACLE會首先在這塊內存中查找相同的語句.
這里需要註明的是,ORACLE對兩者採取的是一種嚴格匹配,要達成共享,SQL語句必須完全相同(包括空格,換行等).
4.選擇最有效率的表名順序(只在基於規則的優化器中有效)
ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫在最後的表(基礎表 driving table)將被最先處理. 在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎表.當ORACLE處理多個表時, 會運用排序及合並的方式連接它們.首先,掃描第一個表(FROM子句中最後的那個表)並對記錄進行派序,然後掃描第二個表(FROM子句中最後第二個表),最後將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合並.如果有3個以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎表, 交叉表是指那個被其他表所引用的表.
5. WHERE子句中的連接順序．
ORACLE採用自下而上的順序解析WHERE子句,根據這個原理,表之間的連接必須寫在其他WHERE條件之前, 那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾.
6. SELECT子句中避免使用 『 * 『
當你想在SELECT子句中列出所有的COLUMN時,使用動態SQL列引用 『*' 是一個方便的方法.不幸的是,這是一個非常低效的方法. 實際上,ORACLE在解析的過程中, 會將'*' 依次轉換成所有的列名, 這個工作是通過查詢數據字典完成的, 這意味著將耗費更多的時間.
7. 減少訪問資料庫的次數
當執行每條SQL語句時, ORACLE在內部執行了許多工作: 解析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子句將執行一個內部的排序和合並. 無論在哪種情況下,NOT IN都是最低效的 (因為它對子查詢中的表執行了一個全表遍歷). 為了避免使用NOT IN ,我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS
㈡ 如何實現oracle 資料庫集群的優化
1、1、調整數據結構的設計。這一部分在開發信息系統之前完成，程序員需要考慮是否使用ORACLE資料庫的分區功能，對於經常訪問的資料庫表是否需要建立索引等。
2、2、調整應用程序結構設計。這一部分也是在開發信息系統之前完成，程序員在這一步需要考慮應用程序使用什麼樣的體系結構，是使用傳統的Client/Server兩層體系結構，還是使用Browser/Web/Database的三層體系結構。不同的應用程序體系結構要求的資料庫資源是不同的。
3、3、調整資料庫SQL語句。應用程序的執行最終將歸結為資料庫中的SQL語句執行，因此SQL語句的執行效率最終決定了ORACLE資料庫的性能。ORACLE公司推薦使用ORACLE語句優化器（Oracle Optimizer）和行鎖管理器（row-level manager）來調整優化SQL語句。
4、4、調整伺服器內存分配。內存分配是在信息系統運行過程中優化配置的，資料庫管理員可以根據資料庫運行狀況調整資料庫系統全局區（SGA區）的數據緩沖區、日誌緩沖區和共享池的大小；還可以調整程序全局區（PGA區）的大小。需要注意的是，SGA區不是越大越好，SGA區過大會佔用操作系統使用的內存而引起虛擬內存的頁面交換，這樣反而會降低系統。
5、5、調整硬碟I/O，這一步是在信息系統開發之前完成的。資料庫管理員可以將組成同一個表空間的數據文件放在不同的硬碟上，做到硬碟之間I/O負載均衡。
6、6、調整操作系統參數，例如：運行在UNIX操作系統上的ORACLE資料庫，可以調整UNIX數據緩沖池的大小，每個進程所能使用的內存大小等參數。
實際上，上述資料庫優化措施之間是相互聯系的。ORACLE資料庫性能惡化表現基本上都是用戶響應時間比較長，需要用戶長時間的等待。但性能惡化的原因卻是多種多樣的，有時是多個因素共同造成了性能惡化的結果，這就需要資料庫管理員有比較全面的計算機知識，能夠敏感地察覺到影響資料庫性能的主要原因所在。另外，良好的資料庫管理工具對於優化資料庫性能也是很重要的。
㈢ sql優化器基於規則優化器和基於成本優化器的區別
Oracle有兩種優化器：RBO和CBO。 RBO的最大的問題在於它是靠硬編碼在ORACLE資料庫代碼中的一系列規定的規則來決定目標SQL的執行計劃的，而並沒有考慮目標SQL中所涉及的對象的時間數據量，實際數據分布情況，這樣一旦規定規則並不適用於該SQL中所涉及的實際對象時，RBO根據規定規則產生的執行計劃就很可能不是當前情況下的最優執行計劃了。
下面我們來看如下的例子：
select * from EMP_TEMP where manager_id=100;
假設在EMP_TEMP的manager_id上事先有名為IDX_MGR_TEMP的單鍵值B數索引，如果我們用的是RBO，則不管EMP_TEMP的數據量多大，也不管MANAGER_ID的數據分布如何，ORACLE執行的時候始終會選擇做對IDX_MGR_TEMP的范圍索引掃描，並回表取得EMP_TEMP中的記錄。ORACLE是不會選擇全表掃描EMP_TEMP表的，因為對於RBO而言，全表掃描的等級值要高於索引范圍掃描值的等級值。
RBO的這種選擇在表EMP_TEMP的數據量不大，而且滿足manager_id=10的條件的記錄少的情況下是影響不大的，如果表EMP_TEMP的數據量非常大，例如1000萬條記錄，
而且這1000萬條記錄的MANAGER_ID的值都是100，在這種極端的情況下，如果是RBO,顯然它任然用IDX_MGR_TEMP索引范圍掃描，這個時候性能肯定是很差的。因為相當於以單塊順序掃描所有的1000萬行索引，然後再回表1000萬次。顯然沒有使用多塊以全表掃描方式直接掃描表EMP_TEMP的執行效率高。所以為了解決RBO的這個先天的缺陷，從ORACLE 7開始，ORACLE就引入了CBO。CBO在選擇目標SQL的執行計劃時，是用執行成本作為判斷原則的。CBO會從目標SQL諸多可能的執行路徑中選擇一條成本值最小的執行路徑作為其執行計劃，各條執行路徑的成本是根據目標SQL語句所涉及的表，索引，列等相關對象的統計信息計算出來的。這些信息存儲在ORACLE的資料庫的數據字典里，且從多個維度描述了ORACLE資料庫里相關對象的實際數據量，實際數據分布等詳細信息。
NOTE:ORACLE在對一條執行路徑計算成本時，並不一定從頭到尾完整計算完，只是要ORACLE在計算過程中發現算出來的部分成本值已經大於之前保存下來的到目前為止的最小成本值，就會馬上終止對當前執行路徑成本值的計算，並轉而開始計算下一條新的執行路徑的成本。這個過程會一直持續下去，直到目標SQL的給各個可能的執行路徑全部計算完畢或已經達到預先定義好的待計算的執行路徑數量的閥值。
RBO是根據硬編碼在ORACLE資料庫中來決定目標SQL的執行計劃的，並沒有考慮目標SQL所所涉及的對象的實際數據量，實際分布情況等。而CBO則恰恰相反，它會根據目標SQL的相關的對象的實際數據量，實際數據分布情況的統計信息來決定其執行計劃，即意味著CBO是隨著目標SQL中所涉及的對象的統計信息的變化而變化的。這就意味著只有統計信息相對准確，則用CBO來解析目標SQL會比同等條件下的RBO來解析得到正確執行計劃的概率要高。
當然CBO並不是完美的，它的缺陷主要表現在：
1，CBO會默認目標SQL語句的WHERE條件中出現的各個列之間是獨立的，沒有關系的。
2，CBO會假設所有的目標SQL都是單獨執行的，並且互不幹擾。
3，CBO對直方圖統計信息有諸多限制。
4，CBO在解析多個表關聯的目標SQL時，可能會漏掉正確的執行計劃。
㈣ 如何進行oracle資料庫性能優化
你最好買一本專門講ORACLE性能優化的書，好好看看\x0d\x0a1、調整資料庫伺服器的性能\x0d\x0aOracle資料庫伺服器是整個系統的核心，它的性能高低直接影響整個系統的性能，為了調整Oracle資料庫伺服器的性能，主要從以下幾個方面考慮： \x0d\x0a1.1、調整操作系統以適合Oracle資料庫伺服器運行\x0d\x0aOracle資料庫伺服器很大程度上依賴於運行伺服器的操作系統，如果操作系統不能提供最好性能，那麼無論如何調整，Oracle資料庫伺服器也無法發揮其應有的性能。 \x0d\x0a1.1.1、為Oracle資料庫伺服器規劃系統資源 \x0d\x0a據已有計算機可用資源, 規劃分配給Oracle伺服器資源原則是：盡可能使Oracle伺服器使用資源最大化,特別在Client/Server中盡量讓伺服器上所有資源都來運行Oracle服務。 \x0d\x0a1.1.2、調整計算機系統中的內存配置 \x0d\x0a多數操作系統都用虛存來模擬計算機上更大的內存，它實際上是硬碟上的一定的磁碟空間。當實際的內存空間不能滿足應用軟體的要求時，操作系統就將用這部分的磁碟空間對內存中的信息進行頁面替換，這將引起大量的磁碟I/O操作，使整個伺服器的性能下降。為了避免過多地使用虛存，應加大計算機的內存。 \x0d\x0a1.1.3、為Oracle資料庫伺服器設置操作系統進程優先順序 \x0d\x0a不要在操作系統中調整Oracle進程的優先順序，因為在Oracle資料庫系統中，所有的後台和前台資料庫伺服器進程執行的是同等重要的工作，需要同等的優先順序。所以在安裝時，讓所有的資料庫伺服器進程都使用預設的優先順序運行。 \x0d\x0a1.2、調整內存分配\x0d\x0aOracle資料庫伺服器保留3個基本的內存高速緩存，分別對應3種不同類型的數據：庫高速緩存，字典高速緩存和緩沖區高速緩存。庫高速緩存和字典高速緩存一起構成共享池，共享池再加上緩沖區高速緩存便構成了系統全程區(SGA)。SGA是對資料庫數據進行快速訪問的一個系統全程區，若SGA本身需要頻繁地進行釋放、分配，則不能達到快速訪問數據的目的，因此應把SGA放在主存中，不要放在虛擬內存中。內存的調整主要是指調整組成SGA的內存結構的大小來提高系統性能，由於Oracle資料庫伺服器的內存結構需求與應用密切相關，所以內存結構的調整應在磁碟I/O調整之前進行。 \x0d\x0a1.2.1、庫緩沖區的調整 \x0d\x0a庫緩沖區中包含私用和共享SQL和PL/SQL區，通過比較庫緩沖區的命中率決定它的大小。要調整庫緩沖區，必須首先了解該庫緩沖區的活動情況，庫緩沖區的活動統計信息保留在動態性能表v$librarycache數據字典中，可通過查詢該表來了解其活動情況，以決定如何調整。 \x0d\x0a \x0d\x0aSelect sum(pins),sum(reloads) from v$librarycache; \x0d\x0a \x0d\x0aPins列給出SQL語句，PL/SQL塊及被訪問對象定義的總次數；Reloads列給出SQL 和PL/SQL塊的隱式分析或對象定義重裝載時在庫程序緩沖區中發生的錯誤。如果sum(pins)/sum(reloads) ≈0，則庫緩沖區的命中率合適；若sum(pins)/sum(reloads)>1, 則需調整初始化參數 shared_pool_size來重新調整分配給共享池的內存量。 \x0d\x0a1.2.2、數據字典緩沖區的調整 \x0d\x0a數據字典緩沖區包含了有關資料庫的結構、用戶、實體信息。數據字典的命中率，對系統性能影響極大。數據字典緩沖區的使用情況記錄在動態性能表v$librarycache中，可通過查詢該表來了解其活動情況，以決定如何調整。 \x0d\x0a \x0d\x0aSelect sum(gets),sum(getmisses) from v$rowcache; \x0d\x0a \x0d\x0aGets列是對相應項請求次數的統計；Getmisses 列是引起緩沖區出錯的數據的請求次數。對於頻繁訪問的數據字典緩沖區，sum(getmisses)/sum(gets)<10%～15%。若大於此百分數，則應考慮增加數據字典緩沖區的容量，即需調整初始化參數shared_pool_size來重新調整分配給共享池的內存量。 \x0d\x0a1.2.3、緩沖區高速緩存的調整 \x0d\x0a用戶進程所存取的所有數據都是經過緩沖區高速緩存來存取，所以該部分的命中率，對性能至關重要。緩沖區高速緩存的使用情況記錄在動態性能表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的值是請求數據緩沖區中讀的總次數。physical reads的值是請求數據時引起從盤中讀文件的次數。從緩沖區高速緩存中讀的可能性的高低稱為緩沖區的命中率，計算公式： \x0d\x0a \x0d\x0aHit Ratio=1-(physical reds/(dbblock gets+consistent gets)) \x0d\x0a \x0d\x0a如果Hit Ratio<60%～70%，則應增大db_block_buffers的參數值。db_block_buffers可以調整分配給緩沖區高速緩存的內存量，即db_block_buffers可設置分配緩沖區高速緩存的數據塊的個數。緩沖區高速緩存的總位元組數=db_block_buffers的值*db_block_size的值。db_block_size 的值表示數據塊大小的位元組數，可查詢 v$parameter 表： \x0d\x0a \x0d\x0aselect name,value from v$parameter where name='db_block_size'; \x0d\x0a \x0d\x0a在修改了上述資料庫的初始化參數以後，必須先關閉資料庫，在重新啟動資料庫後才能使新的設置起作用。
㈤ oralce sql優化。
看下這個查詢sql是比你的查詢快還是慢
SELECT S_ECIF_IP_AND_AST_RLTNP.NEXTVAL,
C.CONT_ID,
A.HOLDING_ID,
2,
'',
'',
SYSDATE
FROM TEMP_HOUSE_CONTEQUIV A
INNER JOIN ECIFSGA.FCC_PRPCINSURED B ON A.PROPOSALNO = B.PROPOSALNO
INNER JOIN CONTEQUIV C ON B.INSUREDCODE = C.ADMIN_CLIENT_ID
AND C.ADMIN_SYS_TP_CD <> 100000
inner join CONTACT H on C.CONT_ID = H.CONT_ID
where INACTIVATED_DT IS NULL
AND SUBSTR(B.INSUREDFLAG, 0, 1) = 1
㈥ oracle plsql 如何優化sql
加索引，加分區，走索引，走分區
㈦ 在oracle資料庫中，影響優化器生成執行計劃的因素有哪些
9i前的RBO不熟，也就不敢妄言。
關於10g後的CBO，談下我的理解。
首先，影響優化器執行計劃最主要的因素是統計信息。優化器根據統計信息情況，單表上選擇全表掃描還是索引。表聯接方式上選擇嵌套，哈希還是合並排序。不同的統計信息將會生成不同的執行計劃。很多時候發現之前跑的好好的sql，突然變慢了，多數情況下重新收集下統計信息便解決了。統計信息這一塊需要關注直方圖這一塊，很多生產環境都存在數據傾斜的情況，如若未准確收集直方圖，那麼生成的執行計劃便有失偏頗。
--------------------------------------------------------------------------------------------------------------------
第二點，sql語句的寫法問題。比如欄位上有索引，但謂詞條件寫成like '%xxx%'方式，將導致該欄位上索引不可用。比如表連接方式用<>之類，將無法使用hash join。其實這些與其說是寫法問題，倒不如說是oracle自身有一定的編碼規則，符合該規則條件，方可用到index之類。
--------------------------------------------------------------------------------------------------------------------
第三點，也是最無奈的一點，CBO的自身缺陷問題。很多時候，統計信息是最新的，也符合寫法規范，但CBO就是不生成我們所期待的執行計劃。這個時候，通常要改變sql語句的邏輯寫法，比如標量子查詢可否換成左連接，用with as替換一些子查詢等，以期待oracle生成更高效率的執行計劃。另外，就是使用hint來迫使CBO生成你所期待的執行計劃，但CBO不一定就範。
談及缺陷，也不算不上缺陷，尤其是表越多，將會出現更多排列的可能性，oracle不可能將所有執行計劃都生成出來然後選擇一個最優的，定是按照一定比例擇取，但具體多少，我不詳。也就是說，DBA完全可以憑借自身對你所管理的oracle了解程度，給sql語句指定一個最優的執行計劃。
--------------------------------------------------------------------------------------------------------------------
其他還有一些細節問題，可在日常工作中慢慢體會，cbo還是相當強大的。
㈧ 執行一下Oracle SQL時，慢如蝸牛，如何優化
這樣給出的SQL沒有人可以幫助你優化，如果你真的希望有人可以幫助你，你需要將這個SQL的執行計劃和你的優化器模式貼出來，這樣才可以給你一點建議，
㈨ Oracle資料庫該如何著手優化一個SQL
SQL Profile是10g中的新特性，作為自動SQL調整過程的一部分。SQL Profile是一個對象，它包含了可以幫助查詢優化器為一個特定的SQL語句找到高效執行計劃的信息。這些信息包括執行環境、對象統計和對查詢優化器所做評估的修正信息。
它的最大優點之一就是在不修改SQL語句和會話執行環境的情況下影響查詢優化器的決定。SQL Profile中包含的並非單個執行計劃的信息，SQL Profile不會固定一個SQL語句的執行計劃。當表的數據增長或者索引創建、刪除，使用同一個SQL Profile的執行計劃可能會改變，而存儲在SQL Profile中的信息會繼續起作用。所以，經過一段很長的時間之後，它的信息有可能會過時，需要重新生成。