oraclesql性能優化
SQL Profile是10g中的新特性，作為自動SQL調整過程的一部分。SQL Profile是一個對象，它包含了可以幫助查詢優化器為一個特定的SQL語句找到高效執行計劃的信息。這些信息包括執行環境、對象統計和對查詢優化器所做評估的修正信息。
它的最大優點之一就是在不修改SQL語句和會話執行環境的情況下影響查詢優化器的決定。SQL Profile中包含的並非單個執行計劃的信息，SQL Profile不會固定一個SQL語句的執行計劃。當表的數據增長或者索引創建、刪除，使用同一個SQL Profile的執行計劃可能會改變，而存儲在SQL Profile中的信息會繼續起作用。所以，經過一段很長的時間之後，它的信息有可能會過時，需要重新生成。
❷ 怎麼使用 基於oracle的sql優化
（1） 選擇最有效率的表名順序(只在基於規則的優化器中有效)：
ORACLE的解析器按照從右到左的順序處理FROM子句中的表名，FROM子句中寫在最後的表(基礎表 driving table)將被最先處理，在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎表。如果有3個以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎表, 交叉表是指那個被其他表所引用的表.
（2） WHERE子句中的連接順序．：
ORACLE採用自下而上的順序解析WHERE子句,根據這個原理,表之間的連接必須寫在其他WHERE條件之前, 那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾.
（3） SELECT子句中避免使用 『 * 『：
ORACLE在解析的過程中, 會將'*' 依次轉換成所有的列名, 這個工作是通過查詢數據字典完成的, 這意味著將耗費更多的時間
（4） 減少訪問資料庫的次數：
ORACLE在內部執行了許多工作: 解析SQL語句, 估算索引的利用率, 綁定變數 , 讀數據塊等；
（5） 在SQL*Plus , SQL*Forms和Pro*C中重新設置ARRAYSIZE參數, 可以增加每次資料庫訪問的檢索數據量 ,建議值為200
（6） 使用DECODE函數來減少處理時間：
使用DECODE函數可以避免重復掃描相同記錄或重復連接相同的表.
（7） 整合簡單,無關聯的資料庫訪問：
如果你有幾個簡單的資料庫查詢語句,你可以把它們整合到一個查詢中(即使它們之間沒有關系)
（8） 刪除重復記錄：
最高效的刪除重復記錄方法 ( 因為使用了ROWID)例子：
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
（9） 用TRUNCATE替代DELETE：
當刪除表中的記錄時,在通常情況下, 回滾段(rollback segments ) 用來存放可以被恢復的信息. 如果你沒有COMMIT事務,ORACLE會將數據恢復到刪除之前的狀態(准確地說是恢復到執行刪除命令之前的狀況) 而當運用TRUNCATE時, 回滾段不再存放任何可被恢復的信息.當命令運行後,數據不能被恢復.因此很少的資源被調用,執行時間也會很短. (譯者按: TRUNCATE只在刪除全表適用,TRUNCATE是DDL不是DML)
（10） 盡量多使用COMMIT：
只要有可能,在程序中盡量多使用COMMIT, 這樣程序的性能得到提高,需求也會因為COMMIT所釋放的資源而減少:
COMMIT所釋放的資源:
a. 回滾段上用於恢復數據的信息.
b. 被程序語句獲得的鎖
c. redo log buffer 中的空間
d. ORACLE為管理上述3種資源中的內部花費
（11） 用Where子句替換HAVING子句：
避免使用HAVING子句, HAVING 只會在檢索出所有記錄之後才對結果集進行過濾. 這個處理需要排序,總計等操作. 如果能通過WHERE子句限制記錄的數目,那就能減少這方面的開銷. (非oracle中)on、where、having這三個都可以加條件的子句中，on是最先執行，where次之，having最後，因為on是先把不符合條件的記錄過濾後才進行統計，它就可以減少中間運算要處理的數據，按理說應該速度是最快的，where也應該比having快點的，因為它過濾數據後才進行sum，在兩個表聯接時才用on的，所以在一個表的時候，就剩下where跟having比較了。在這單表查詢統計的情況下，如果要過濾的條件沒有涉及到要計算欄位，那它們的結果是一樣的，只是where可以使用rushmore技術，而having就不能，在速度上後者要慢如果要涉及到計算的欄位，就表示在沒計算之前，這個欄位的值是不確定的，根據上篇寫的工作流程，where的作用時間是在計算之前就完成的，而having就是在計算後才起作用的，所以在這種情況下，兩者的結果會不同。在多表聯接查詢時，on比where更早起作用。系統首先根據各個表之間的聯接條件，把多個表合成一個臨時表後，再由where進行過濾，然後再計算，計算完後再由having進行過濾。由此可見，要想過濾條件起到正確的作用，首先要明白這個條件應該在什麼時候起作用，然後再決定放在那裡
（12） 減少對表的查詢：
在含有子查詢的SQL語句中,要特別注意減少對表的查詢.例子：
SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT
TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)
（13） 通過內部函數提高SQL效率.：
復雜的SQL往往犧牲了執行效率. 能夠掌握上面的運用函數解決問題的方法在實際工作中是非常有意義的
（14） 使用表的別名(Alias)：
當在SQL語句中連接多個表時, 請使用表的別名並把別名前綴於每個Column上.這樣一來,就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤.
（15） 用EXISTS替代IN、用NOT EXISTS替代NOT IN：
在許多基於基礎表的查詢中,為了滿足一個條件,往往需要對另一個表進行聯接.在這種情況下, 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率. 在子查詢中,NOT IN子句將執行一個內部的排序和合並. 無論在哪種情況下,NOT IN都是最低效的 (因為它對子查詢中的表執行了一個全表遍歷). 為了避免使用NOT IN ,我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS.
例子：
（高效）SELECT * FROM EMP (基礎表) WHERE EMPNO > 0 AND EXISTS (SELECT 『X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = 『MELB')
(低效)SELECT * FROM EMP (基礎表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = 『MELB')
（16） 識別'低效執行'的SQL語句：
雖然目前各種關於SQL優化的圖形化工具層出不窮,但是寫出自己的SQL工具來解決問題始終是一個最好的方法：
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;
（17） 用索引提高效率：
索引是表的一個概念部分,用來提高檢索數據的效率，ORACLE使用了一個復雜的自平衡B-tree結構. 通常,通過索引查詢數據比全表掃描要快. 當ORACLE找出執行查詢和Update語句的最佳路徑時, ORACLE優化器將使用索引. 同樣在聯結多個表時使用索引也可以提高效率. 另一個使用索引的好處是,它提供了主鍵(primary key)的唯一性驗證.。那些LONG或LONG RAW數據類型, 你可以索引幾乎所有的列. 通常, 在大型表中使用索引特別有效. 當然,你也會發現, 在掃描小表時,使用索引同樣能提高效率. 雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價. 索引需要空間來存儲,也需要定期維護, 每當有記錄在表中增減或索引列被修改時, 索引本身也會被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁碟I/O . 因為索引需要額外的存儲空間和處理,那些不必要的索引反而會使查詢反應時間變慢.。定期的重構索引是有必要的.：
ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>
（18） 用EXISTS替換DISTINCT：
當提交一個包含一對多表信息(比如部門表和雇員表)的查詢時,避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXIST替換, EXISTS 使查詢更為迅速,因為RDBMS核心模塊將在子查詢的條件一旦滿足後,立刻返回結果. 例子：
(低效):
SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E
WHERE D.DEPT_NO = E.DEPT_NO
(高效):
SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT 『X'
FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
（19） sql語句用大寫的；因為oracle總是先解析sql語句，把小寫的字母轉換成大寫的再執行
（20） 在java代碼中盡量少用連接符「＋」連接字元串！
（21） 避免在索引列上使用NOT 通常，
我們要避免在索引列上使用NOT, NOT會產生在和在索引列上使用函數相同的影響. 當ORACLE」遇到」NOT,他就會停止使用索引轉而執行全表掃描.
（22） 避免在索引列上使用計算．
WHERE子句中，如果索引列是函數的一部分．優化器將不使用索引而使用全表掃描．
舉例:
低效：
SELECT … FROM DEPT WHERE SAL * 12 > 25000;
高效:
SELECT … FROM DEPT WHERE SAL > 25000/12;
（23） 用>=替代>
高效:
SELECT * FROM EMP WHERE DEPTNO >=4
低效:
SELECT * FROM EMP WHERE DEPTNO >3
兩者的區別在於, 前者DBMS將直接跳到第一個DEPT等於4的記錄而後者將首先定位到DEPTNO=3的記錄並且向前掃描到第一個DEPT大於3的記錄.
（24） 用UNION替換OR (適用於索引列)
通常情況下, 用UNION替換WHERE子句中的OR將會起到較好的效果. 對索引列使用OR將造成全表掃描. 注意, 以上規則只針對多個索引列有效. 如果有column沒有被索引, 查詢效率可能會因為你沒有選擇OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引.
高效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE REGION = 「MELBOURNE」
低效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10 OR REGION = 「MELBOURNE」
如果你堅持要用OR, 那就需要返回記錄最少的索引列寫在最前面.
（25） 用IN來替換OR
這是一條簡單易記的規則，但是實際的執行效果還須檢驗，在ORACLE8i下，兩者的執行路徑似乎是相同的．
低效:
SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30
高效
SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30);
（26） 避免在索引列上使用IS NULL和IS NOT NULL
避免在索引中使用任何可以為空的列，ORACLE將無法使用該索引．對於單列索引，如果列包含空值，索引中將不存在此記錄. 對於復合索引，如果每個列都為空，索引中同樣不存在此記錄.如果至少有一個列不為空，則記錄存在於索引中．舉例: 如果唯一性索引建立在表的A列和B列上, 並且表中存在一條記錄的A,B值為(123,null) , ORACLE將不接受下一條具有相同A,B值（123,null）的記錄(插入). 然而如果所有的索引列都為空，ORACLE將認為整個鍵值為空而空不等於空. 因此你可以插入1000 條具有相同鍵值的記錄,當然它們都是空! 因為空值不存在於索引列中,所以WHERE子句中對索引列進行空值比較將使ORACLE停用該索引.
低效: (索引失效)
SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;
高效: (索引有效)
SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;
（27） 總是使用索引的第一個列：
如果索引是建立在多個列上, 只有在它的第一個列(leading column)被where子句引用時,優化器才會選擇使用該索引. 這也是一條簡單而重要的規則，當僅引用索引的第二個列時,優化器使用了全表掃描而忽略了索引
（28） 用UNION-ALL 替換UNION ( 如果有可能的話)：
當SQL語句需要UNION兩個查詢結果集合時,這兩個結果集合會以UNION-ALL的方式被合並, 然後在輸出最終結果前進行排序. 如果用UNION ALL替代UNION, 這樣排序就不是必要了. 效率就會因此得到提高. 需要注意的是，UNION ALL 將重復輸出兩個結果集合中相同記錄. 因此各位還是要從業務需求分析使用UNION ALL的可行性. UNION 將對結果集合排序,這個操作會使用到SORT_AREA_SIZE這塊內存. 對於這塊內存的優化也是相當重要的. 下面的SQL可以用來查詢排序的消耗量
低效：
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
UNION
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
高效:
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
UNION ALL
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
（29） 用WHERE替代ORDER BY：
ORDER BY 子句只在兩種嚴格的條件下使用索引.
ORDER BY中所有的列必須包含在相同的索引中並保持在索引中的排列順序.
ORDER BY中所有的列必須定義為非空.
WHERE子句使用的索引和ORDER BY子句中所使用的索引不能並列.
例如:
表DEPT包含以下列:
DEPT_CODE PK NOT NULL
DEPT_DESC NOT NULL
DEPT_TYPE NULL
低效: (索引不被使用)
SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE
高效: (使用索引)
SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0
（30） 避免改變索引列的類型.:
當比較不同數據類型的數據時, ORACLE自動對列進行簡單的類型轉換.
假設 EMPNO是一個數值類型的索引列.
SELECT … FROM EMP WHERE EMPNO = 『123'
實際上,經過ORACLE類型轉換, 語句轉化為:
SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(『123')
幸運的是,類型轉換沒有發生在索引列上,索引的用途沒有被改變.
現在,假設EMP_TYPE是一個字元類型的索引列.
SELECT … FROM EMP WHERE EMP_TYPE = 123
這個語句被ORACLE轉換為:
SELECT … FROM EMP WHERETO_NUMBER(EMP_TYPE)=123
因為內部發生的類型轉換, 這個索引將不會被用到! 為了避免ORACLE對你的SQL進行隱式的類型轉換, 最好把類型轉換用顯式表現出來. 注意當字元和數值比較時, ORACLE會優先轉換數值類型到字元類型
（31） 需要當心的WHERE子句:
某些SELECT 語句中的WHERE子句不使用索引. 這里有一些例子.
在下面的例子里, (1)『!=' 將不使用索引. 記住, 索引只能告訴你什麼存在於表中, 而不能告訴你什麼不存在於表中. (2) 『||'是字元連接函數. 就象其他函數那樣, 停用了索引. (3) 『+'是數學函數. 就象其他數學函數那樣, 停用了索引. (4)相同的索引列不能互相比較,這將會啟用全表掃描.
（32） a. 如果檢索數據量超過30%的表中記錄數.使用索引將沒有顯著的效率提高.
b. 在特定情況下, 使用索引也許會比全表掃描慢, 但這是同一個數量級上的區別. 而通常情況下,使用索引比全表掃描要塊幾倍乃至幾千倍!
（33） 避免使用耗費資源的操作:
帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語句會啟動SQL引擎
執行耗費資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執行兩次排序. 通常, 帶有UNION, MINUS , INTERSECT的SQL語句都可以用其他方式重寫. 如果你的資料庫的SORT_AREA_SIZE調配得好, 使用UNION , MINUS, INTERSECT也是可以考慮的, 畢竟它們的可讀性很強
（34） 優化GROUP BY:
提高GROUP BY 語句的效率, 可以通過將不需要的記錄在GROUP BY 之前過濾掉.下面兩個查詢返回相同結果但第二個明顯就快了許多.
低效:
SELECT JOB , AVG(SAL)
FROM EMP
GROUP JOB
HAVING JOB = 『PRESIDENT'
OR JOB = 『MANAGER'
高效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = 『PRESIDENT'
OR JOB = 『MANAGER'
GROUP JOB
❸ 關於SQL和Oracle資料庫的優化建議
在實際的工作中,尤其是在生產環境里邊,SQL語句的優化問題十分的重要,它對資料庫的性能的提升也起著顯著的作用.我們總是在抱怨機器的性能問題,總是在抱怨並發訪問所帶來的瑣問題,但是如果我們對沒一條SQL語句進行優化,盡管不能說可以解決全部問題,但是至少可以解決大部分問題. 1.Top排序問題. 我們經常要對表某個欄位進行排序,然後取前N名.所以我們會寫如下的SQL語句: selecttop100*from表 orderbyScoredesc 如果表非常大的話，那麼這樣的操作是非常消耗資源的，因為SQLServer要對整個表進行排序,然後取前N條記錄.這樣的造作是在Temdb里邊進行的，所以極端的時候會報Log已滿這樣的錯誤.為了避免進行全表的排序,我們要做的僅僅是在Score上建立索引,這樣因為Score索引的葉級是有序的,只要在Score所以的頁級取前100個，然後根據書簽查找到實際的記錄，這樣對DB的性能就會有極大的提升.
2.同一天問題. 我們經常要查找和一個日期同一天的記錄,所以我們回寫如下的SQL語句;
declare@DateTimedatetime
set@DateTime=getdate()
select*from表
whereconvert(char(10),F_Time,120)=convert(char(10),@DateTime,120) 但是這樣寫的SQL語句帶來的問題就是不能使用F_Time上的索引了.為了近可能的使用F_Time上的索引,我們可以使用時間段查詢的方式來代替上邊的語句. declare@startdatetime declare@enddatetime declare@datetimedatetime set@datetime=getdate()
set@start=convert(char(10),@datetime,120) -- 一天的其始時間 set@end=dateadd(ss,-1,dateadd(d,1,@start)) -- 一天的結束時間
select* from表 whereF_Timebetween@startand@end 這樣就解決了使用不上索引的問題. 3.利用索引進行分組操作. 我們經常要對某一欄位進行分組,而對另外一些欄位進行聚合操作.如果我們對分組的欄位合理的使用索引,可以加快我們分組的速度.下邊以Northwind的Orders表為例:
-- orders表的EmployeeID上建有索引.
selectEmployeeID,count(*)
fromorders
groupbyEmployeeID -- 查看執行計劃,此查詢利用了EmployeeID上的索引.如改成如下查詢: selectEmployeeID,sum(Freight)
fromorders
groupbyEmployeeID -- 查看執行計劃,此查詢則沒有使用EmployeeID上的索引.而是使用了全表掃描.那麼原因是什麼呢?是因為Freight沒有在EmployeeID的索引上,所以通過索引不能得到結果.而如果通過書簽查詢的成本太高,所以SQLSERVER選擇了使用全表掃描.而如果我們執行在EmployeeID和Freight上建立復合索引呢? createindexidx_EmployeeIDonOrders(EmployeeID,Freight)
-- 再次執行第二個查詢.查看執行計劃.SQLSERVER使用的我們建立的索引.只需要使用索引就可以查詢到結果,極大的提高了我們的查詢速度.
❹ 如何進行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語句。
❺ oracle資料庫的性能優化有哪些方法
你最好買一本專門講ORACLE性能優化的書，好好看看
1、調整資料庫伺服器的性能
Oracle資料庫伺服器是整個系統的核心，它的性能高低直接影響整個系統的性能，為了調整Oracle資料庫伺服器的性能，主要從以下幾個方面考慮：
1.1、調整操作系統以適合Oracle資料庫伺服器運行
Oracle資料庫伺服器很大程度上依賴於運行伺服器的操作系統，如果操作系統不能提供最好性能，那麼無論如何調整，Oracle資料庫伺服器也無法發揮其應有的性能。
1.1.1、為Oracle資料庫伺服器規劃系統資源
據已有計算機可用資源, 規劃分配給Oracle伺服器資源原則是：盡可能使Oracle伺服器使用資源最大化,特別在Client/Server中盡量讓伺服器上所有資源都來運行Oracle服務。
1.1.2、調整計算機系統中的內存配置
多數操作系統都用虛存來模擬計算機上更大的內存，它實際上是硬碟上的一定的磁碟空間。當實際的內存空間不能滿足應用軟體的要求時，操作系統就將用這部分的磁碟空間對內存中的信息進行頁面替換，這將引起大量的磁碟I/O操作，使整個伺服器的性能下降。為了避免過多地使用虛存，應加大計算機的內存。
1.1.3、為Oracle資料庫伺服器設置操作系統進程優先順序
不要在操作系統中調整Oracle進程的優先順序，因為在Oracle資料庫系統中，所有的後台和前台資料庫伺服器進程執行的是同等重要的工作，需要同等的優先順序。所以在安裝時，讓所有的資料庫伺服器進程都使用預設的優先順序運行。
1.2、調整內存分配
Oracle資料庫伺服器保留3個基本的內存高速緩存，分別對應3種不同類型的數據：庫高速緩存，字典高速緩存和緩沖區高速緩存。庫高速緩存和字典高速緩存一起構成共享池，共享池再加上緩沖區高速緩存便構成了系統全程區(SGA)。SGA是對資料庫數據進行快速訪問的一個系統全程區，若SGA本身需要頻繁地進行釋放、分配，則不能達到快速訪問數據的目的，因此應把SGA放在主存中，不要放在虛擬內存中。內存的調整主要是指調整組成SGA的內存結構的大小來提高系統性能，由於Oracle資料庫伺服器的內存結構需求與應用密切相關，所以內存結構的調整應在磁碟I/O調整之前進行。
1.2.1、庫緩沖區的調整
庫緩沖區中包含私用和共享SQL和PL/SQL區，通過比較庫緩沖區的命中率決定它的大小。要調整庫緩沖區，必須首先了解該庫緩沖區的活動情況，庫緩沖區的活動統計信息保留在動態性能表v$librarycache數據字典中，可通過查詢該表來了解其活動情況，以決定如何調整。
Select sum(pins),sum(reloads) from v$librarycache;
Pins列給出SQL語句，PL/SQL塊及被訪問對象定義的總次數；Reloads列給出SQL 和PL/SQL塊的隱式分析或對象定義重裝載時在庫程序緩沖區中發生的錯誤。如果sum(pins)/sum(reloads) ≈0，則庫緩沖區的命中率合適；若sum(pins)/sum(reloads)>1, 則需調整初始化參數 shared_pool_size來重新調整分配給共享池的內存量。
1.2.2、數據字典緩沖區的調整
數據字典緩沖區包含了有關資料庫的結構、用戶、實體信息。數據字典的命中率，對系統性能影響極大。數據字典緩沖區的使用情況記錄在動態性能表v$librarycache中，可通過查詢該表來了解其活動情況，以決定如何調整。
Select sum(gets),sum(getmisses) from v$rowcache;
Gets列是對相應項請求次數的統計；Getmisses 列是引起緩沖區出錯的數據的請求次數。對於頻繁訪問的數據字典緩沖區，sum(getmisses)/sum(gets)<10%～15%。若大於此百分數，則應考慮增加數據字典緩沖區的容量，即需調整初始化參數shared_pool_size來重新調整分配給共享池的內存量。
1.2.3、緩沖區高速緩存的調整
用戶進程所存取的所有數據都是經過緩沖區高速緩存來存取，所以該部分的命中率，對性能至關重要。緩沖區高速緩存的使用情況記錄在動態性能表v$sysstat中，可通過查詢該表來了解其活動情況，以決定如何調整。
Select name,value from v$sysstat where name in ('dbblock gets','consistent gets','physical reads');
dbblock gets和consistent gets的值是請求數據緩沖區中讀的總次數。physical reads的值是請求數據時引起從盤中讀文件的次數。從緩沖區高速緩存中讀的可能性的高低稱為緩沖區的命中率，計算公式：
Hit Ratio=1-(physical reds/(dbblock gets+consistent gets))
如果Hit Ratio<60%～70%，則應增大db_block_buffers的參數值。db_block_buffers可以調整分配給緩沖區高速緩存的內存量，即db_block_buffers可設置分配緩沖區高速緩存的數據塊的個數。緩沖區高速緩存的總位元組數=db_block_buffers的值*db_block_size的值。db_block_size 的值表示數據塊大小的位元組數，可查詢 v$parameter 表：
select name,value from v$parameter where name='db_block_size';
在修改了上述資料庫的初始化參數以後，必須先關閉資料庫，在重新啟動資料庫後才能使新的設置起作用。
❻ oracle sql性能優化需注意哪些
1，sql的寫法，有很多資料，不一一列舉
比如 >= , <= 在一起的時候，直接用between and 等等。。。
2，加index
❼ oracle資料庫性能優化什麼意思
應用架構優化，主要優化應用對資料庫的調用，資料庫的數據結構，這個是對系統性能提高最多的部分，一般在系統架構時期要做好，後期很浪費人力
oracle實例配置優化，主要優化實例的內存使用，IO性能
sql語句優化，主要通過修改sql的優化查詢速度，表索引的合理性
硬體架構，提高硬碟的IO速度，redo的磁碟分布，硬碟的raid，rac通信網速等。
一時就想到這么多
❽ oracle中如何優化sql語句的，用什麼方法
2. 選擇最有效率的表名順序(只在基於規則的優化器中有效)
ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫在最後的表(基礎表 driving table)將被最先處理. 在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎表.當ORACLE處理多個表時, 會運用排序及合並的方式連接它們.首先,掃描第一個表(FROM子句中最後的那個表)並對記錄進行派序,然後掃描第二個表(FROM子句中最後第二個表),最後將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合並.
例如: 表 TAB1 16,384 條記錄
表 TAB2 1 條記錄
選擇TAB2作為基礎表 (最好的方法)
select count(*) from tab1,tab2 執行時間0.96秒
選擇TAB2作為基礎表 (不佳的方法)
select count(*) from tab2,tab1 執行時間26.09秒
如果有3個以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎表, 交叉表是指那個被其他表所引用的表.
例如: EMP表描述了LOCATION表和CATEGORY表的交集.
SELECT *
FROM LOCATION L ,
CATEGORY C,
EMP E
WHERE E.EMP_NO BETWEEN 1000 AND 2000
AND E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
將比下列SQL更有效率
SELECT *
FROM EMP E ,
LOCATION L ,
CATEGORY C
WHERE E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
AND E.EMP_NO BETWEEN 1000 AND 2000
3. WHERE子句中的連接順序．
ORACLE採用自下而上的順序解析WHERE子句,根據這個原理,表之間的連接必須寫在其他WHERE條件之前, 那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾.
例如:
(低效,執行時間156.3秒)
SELECT …
FROM EMP E
WHERE SAL > 50000
AND JOB = 『MANAGER』
AND 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO);
(高效,執行時間10.6秒)
SELECT …
FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO)
AND SAL > 50000
AND JOB = 『MANAGER』;
4. SELECT子句中避免使用 『 * 『
當你想在SELECT子句中列出所有的COLUMN時,使用動態SQL列引用 『*』 是一個方便的方法.不幸的是,這是一個非常低效的方法. 實際上,ORACLE在解析的過程中, 會將』*』 依次轉換成所有的列名, 這個工作是通過查詢數據字典完成的, 這意味著將耗費更多的時間.
5. 減少訪問資料庫的次數
當執行每條SQL語句時, ORACLE在內部執行了許多工作: 解析SQL語句, 估算索引的利用率, 綁定變數 , 讀數據塊等等. 由此可見, 減少訪問資料庫的次數 , 就能實際上減少ORACLE的工作量.
例如,
以下有三種方法可以檢索出雇員號等於0342或0291的職員.
方法1 (最低效)
SELECT EMP_NAME , SALARY , GRADE
FROM EMP
WHERE EMP_NO = 342;
SELECT EMP_NAME , SALARY , GRADE
FROM EMP
WHERE EMP_NO = 291;
方法2 (次低效)
DECLARE
CURSOR C1 (E_NO NUMBER) IS
SELECT EMP_NAME,SALARY,GRADE
FROM EMP
WHERE EMP_NO = E_NO;
BEGIN
OPEN C1(342);
FETCH C1 INTO …,..,.. ;
OPEN C1(291);
FETCH C1 INTO …,..,.. ;
CLOSE C1;
END;
方法3 (高效)
SELECT A.EMP_NAME , A.SALARY , A.GRADE,
B.EMP_NAME , B.SALARY , B.GRADE
FROM EMP A,EMP B
WHERE A.EMP_NO = 342
AND B.EMP_NO = 291;
注意:
在SQL*Plus , SQL*Forms和Pro*C中重新設置ARRAYSIZE參數, 可以增加每次資料庫訪問的檢索數據量 ,建議值為200.
6. 使用DECODE函數來減少處理時間
使用DECODE函數可以避免重復掃描相同記錄或重復連接相同的表.
例如:
SELECT COUNT(*)，SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0020
AND ENAME LIKE 『SMITH%』;
SELECT COUNT(*)，SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0030
AND ENAME LIKE 『SMITH%』;
你可以用DECODE函數高效地得到相同結果
SELECT COUNT(DECODE(DEPT_NO,0020,』X』,NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO,0030,』X』,NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
FROM EMP WHERE ENAME LIKE 『SMITH%』;
類似的,DECODE函數也可以運用於GROUP BY 和ORDER BY子句中.
7. 用TRUNCATE替代DELETE
當刪除表中的記錄時,在通常情況下, 回滾段(rollback segments ) 用來存放可以被恢復的信息. 如果你沒有COMMIT事務,ORACLE會將數據恢復到刪除之前的狀態(准確地說是恢復到執行刪除命令之前的狀況)
而當運用TRUNCATE時, 回滾段不再存放任何可被恢復的信息.當命令運行後,數據不能被恢復.因此很少的資源被調用,執行時間也會很短.
TRUNCATE只在刪除全表或分區適用,TRUNCATE是DDL不是DML
8. 用Where子句替換HAVING子句
避免使用HAVING子句, HAVING 只會在檢索出所有記錄之後才對結果集進行過濾. 這個處理需要排序,總計等操作. 如果能通過WHERE子句限制記錄的數目,那就能減少這方面的開銷.
例如:
低效:
SELECT REGION，AVG(LOG_SIZE)
FROM LOCATION
GROUP BY REGION
HAVING REGION REGION != 『SYDNEY』
AND REGION != 『PERTH』
高效
SELECT REGION，AVG(LOG_SIZE)
FROM LOCATION
WHERE REGION REGION != 『SYDNEY』
AND REGION != 『PERTH』
GROUP BY REGION
HAVING 中的條件一般用於對一些集合函數的比較,如COUNT() 等等. 除此而外,一般的條件應該寫在WHERE子句中
9. 減少對表的查詢
在含有子查詢的SQL語句中,要特別注意減少對表的查詢.
例如:
低效
SELECT TAB_NAME
FROM TABLES
WHERE TAB_NAME = ( SELECT TAB_NAME
FROM TAB_COLUMNS
WHERE VERSION = 604)
AND DB_VER= ( SELECT DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604)
高效
SELECT TAB_NAME
FROM TABLES
WHERE (TAB_NAME,DB_VER)
= ( SELECT TAB_NAME,DB_VER)
FROM TAB_COLUMNS
WHERE VERSION = 604)
Update 多個Column 例子:
低效:
UPDATE EMP
SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),
SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
高效:
UPDATE EMP
SET (EMP_CAT, SAL_RANGE)
= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)
FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
10. 通過內部函數提高SQL效率.
SELECT H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*)
FROM HISTORY_TYPE T,EMP E,EMP_HISTORY H
WHERE H.EMPNO = E.EMPNO
AND H.HIST_TYPE = T.HIST_TYPE
GROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;
通過調用下面的函數可以提高效率.
FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2
AS
TDESC VARCHAR2(30);
CURSOR C1 IS
SELECT TYPE_DESC
FROM HISTORY_TYPE
WHERE HIST_TYPE = TYP;
BEGIN
OPEN C1;
FETCH C1 INTO TDESC;
CLOSE C1;
RETURN (NVL(TDESC,』?』));
END;
FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2
AS
ENAME VARCHAR2(30);
CURSOR C1 IS
SELECT ENAME
FROM EMP
WHERE EMPNO=EMP;
BEGIN
OPEN C1;
FETCH C1 INTO ENAME;
CLOSE C1;
RETURN (NVL(ENAME,』?』));
END;
SELECT H.EMPNO,LOOKUP_EMP(H.EMPNO),
H.HIST_TYPE,LOOKUP_HIST_TYPE(H.HIST_TYPE),COUNT(*)
FROM EMP_HISTORY H
GROUP BY H.EMPNO , H.HIST_TYPE;
經常在論壇中看到如 』能不能用一個SQL寫出….』 的貼子, 殊不知復雜的SQL往往犧牲了執行效率. 能夠掌握上面的運用函數解決問題的方法在實際工作中是非常有意義的
11. 使用表的別名(Alias)
當在SQL語句中連接多個表時, 請使用表的別名並把別名前綴於每個Column上.這樣一來,就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤.
Column歧義指的是由於SQL中不同的表具有相同的Column名,當SQL語句中出現這個Column時,SQL解析器無法判斷這個Column的歸屬
12. 用EXISTS替代IN
在許多基於基礎表的查詢中,為了滿足一個條件,往往需要對另一個表進行聯接.在這種情況下, 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率.
低效:
SELECT *
FROM EMP (基礎表)
WHERE EMPNO > 0
AND DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC = 『MELB』)
高效:
SELECT *
FROM EMP (基礎表)
WHERE EMPNO > 0
AND EXISTS (SELECT 『X』
FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = 『MELB』)
相對來說,用NOT EXISTS替換NOT IN 將更顯著地提高效率,下一節中將指出
13. 用NOT EXISTS替代NOT IN
在子查詢中,NOT IN子句將執行一個內部的排序和合並. 無論在哪種情況下,NOT IN都是最低效的 (因為它對子查詢中的表執行了一個全表遍歷). 為了避免使用NOT IN ,我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS.
例如:
SELECT …
FROM EMP
WHERE DEPT_NO NOT IN (SELECT DEPT_NO
FROM DEPT
WHERE DEPT_CAT=』A』);
為了提高效率.改寫為:
(方法一: 高效)
SELECT ….
FROM EMP A,DEPT B
WHERE A.DEPT_NO = B.DEPT(+)
AND B.DEPT_NO IS NULL
AND B.DEPT_CAT(+) = 『A』
(方法二: 最高效)
SELECT ….
FROM EMP E
WHERE NOT EXISTS (SELECT 『X』
FROM DEPT D
WHERE D.DEPT_NO = E.DEPT_NO
AND DEPT_CAT = 『A』);
14. 用表連接替換EXISTS
通常來說 , 採用表連接的方式比EXISTS更有效率
SELECT ENAME
FROM EMP E
WHERE EXISTS (SELECT 『X』
FROM DEPT
WHERE DEPT_NO = E.DEPT_NO
AND DEPT_CAT = 『A』);
(更高效)
SELECT ENAME
FROM DEPT D,EMP E
WHERE E.DEPT_NO = D.DEPT_NO
AND DEPT_CAT = 『A』 ;
在RBO的情況下,前者的執行路徑包括FILTER,後者使用NESTED LOOP
15. 用EXISTS替換DISTINCT
當提交一個包含一對多表信息(比如部門表和雇員表)的查詢時,避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXIST替換
例如:
低效:
SELECT DISTINCT DEPT_NO,DEPT_NAME
FROM DEPT D,EMP E
WHERE D.DEPT_NO = E.DEPT_NO
高效:
SELECT DEPT_NO,DEPT_NAME
FROM DEPT D
WHERE EXISTS ( SELECT 『X』
FROM EMP E
WHERE E.DEPT_NO = D.DEPT_NO);
EXISTS 使查詢更為迅速,因為RDBMS核心模塊將在子查詢的條件一旦滿足後,立刻返回結果.
16. 基礎表的選擇
基礎表(Driving Table)是指被最先訪問的表(通常以全表掃描的方式被訪問). 根據優化器的不同, SQL語句中基礎表的選擇是不一樣的.
如果你使用的是CBO (COST BASED OPTIMIZER),優化器會檢查SQL語句中的每個表的物理大小,索引的狀態,然後選用花費最低的執行路徑.
如果你用RBO (RULE BASED OPTIMIZER) , 並且所有的連接條件都有索引對應, 在這種情況下, 基礎表就是FROM 子句中列在最後的那個表.
舉例:
SELECT A.NAME , B.MANAGER
FROM WORKER A,
LODGING B
WHERE A.LODGING = B.LODING;
由於LODGING表的LODING列上有一個索引, 而且WORKER表中沒有相比較的索引, WORKER表將被作為查詢中的基礎表.
17. 多個平等的索引
當SQL語句的執行路徑可以使用分布在多個表上的多個索引時, ORACLE會同時使用多個索引並在運行時對它們的記錄進行合並, 檢索出僅對全部索引有效的記錄.
在ORACLE選擇執行路徑時,唯一性索引的等級高於非唯一性索引. 然而這個規則只有
當WHERE子句中索引列和常量比較才有效.如果索引列和其他表的索引類相比較. 這種子句在優化器中的等級是非常低的.
如果不同表中兩個想同等級的索引將被引用, FROM子句中表的順序將決定哪個會被率先使用. FROM子句中最後的表的索引將有最高的優先順序.
如果相同表中兩個想同等級的索引將被引用, WHERE子句中最先被引用的索引將有最高的優先順序.
舉例:
DEPTNO上有一個非唯一性索引,EMP_CAT也有一個非唯一性索引.
SELECT ENAME,
FROM EMP
WHERE DEPT_NO = 20
AND EMP_CAT = 『A』;
這里,DEPTNO索引將被最先檢索,然後同EMP_CAT索引檢索出的記錄進行合並. 執行路徑如下:
TABLE ACCESS BY ROWID ON EMP
AND-EQUAL
INDEX RANGE SCAN ON DEPT_IDX
INDEX RANGE SCAN ON CAT_IDX
18. 等式比較和范圍比較
當WHERE子句中有索引列, ORACLE不能合並它們,ORACLE將用范圍比較.
舉例:
DEPTNO上有一個非唯一性索引,EMP_CAT也有一個非唯一性索引.
SELECT ENAME
FROM EMP
WHERE DEPTNO > 20
AND EMP_CAT = 『A』;
這里只有EMP_CAT索引被用到,然後所有的記錄將逐條與DEPTNO條件進行比較. 執行路徑如下:
TABLE ACCESS BY ROWID ON EMP
INDEX RANGE SCAN ON CAT_IDX
19. 不明確的索引等級
當ORACLE無法判斷索引的等級高低差別,優化器將只使用一個索引,它就是在WHERE子句中被列在最前面的.
舉例:
DEPTNO上有一個非唯一性索引,EMP_CAT也有一個非唯一性索引.
SELECT ENAME
FROM EMP
WHERE DEPTNO > 20
AND EMP_CAT > 『A』;
這里, ORACLE只用到了DEPT_NO索引. 執行路徑如下:
TABLE ACCESS BY ROWID ON EMP
INDEX RANGE SCAN ON DEPT_IDX
我們來試一下以下這種情況:
SQL> select index_name, uniqueness from user_indexes where table_name = 'EMP';
INDEX_NAME UNIQUENES
------------------------------ ---------
EMPNO UNIQUE
EMPTYPE NONUNIQUE
SQL> select * from emp where empno >= 2 and emp_type = 'A' ;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'EMPTYPE' (NON-UNIQUE)
雖然EMPNO是唯一性索引,但是由於它所做的是范圍比較, 等級要比非唯一性索引的等式比較低!
20. 強制索引失效
如果兩個或以上索引具有相同的等級,你可以強制命令ORACLE優化器使用其中的一個(通過它,檢索出的記錄數量少) .
舉例:
SELECT ENAME
FROM EMP
WHERE EMPNO = 7935
AND DEPTNO + 0 = 10 /*DEPTNO上的索引將失效*/
AND EMP_TYPE || 『』 = 『A』 /*EMP_TYPE上的索引將失效*/
這是一種相當直接的提高查詢效率的辦法. 但是你必須謹慎考慮這種策略,一般來說,只有在你希望單獨優化幾個SQL時才能採用它.
這里有一個例子關於何時採用這種策略,
假設在EMP表的EMP_TYPE列上有一個非唯一性的索引而EMP_CLASS上沒有索引.
SELECT ENAME
FROM EMP
WHERE EMP_TYPE = 『A』
AND EMP_CLASS = 『X』;
優化器會注意到EMP_TYPE上的索引並使用它. 這是目前唯一的選擇. 如果,一段時間以後, 另一個非唯一性建立在EMP_CLASS上,優化器必須對兩個索引進行選擇,在通常情況下,優化器將使用兩個索引並在他們的結果集合上執行排序及合並. 然而,如果其中一個索引（EMP_TYPE）接近於唯一性而另一個索引（EMP_CLASS）上有幾千個重復的值. 排序及合並就會成為一種不必要的負擔. 在這種情況下,你希望使優化器屏蔽掉EMP_CLASS索引.
用下面的方案就可以解決問題.
SELECT ENAME
FROM EMP
WHERE EMP_TYPE = 『A』
AND EMP_CLASS||』』 = 『X』;
21. 避免在索引列上使用計算．
WHERE子句中，如果索引列是函數的一部分．優化器將不使用索引而使用全表掃描．
舉例:
低效：
SELECT …
FROM DEPT
WHERE SAL * 12 > 25000;
高效:
SELECT …
FROM DEPT
WHERE SAL > 25000/12;
這是一個非常實用的規則，請務必牢記
22. 自動選擇索引
如果表中有兩個以上（包括兩個）索引，其中有一個唯一性索引，而其他是非唯一性．
在這種情況下，ORACLE將使用唯一性索引而完全忽略非唯一性索引．
舉例:
SELECT ENAME
FROM EMP
WHERE EMPNO = 2326
AND DEPTNO = 20 ;
這里，只有EMPNO上的索引是唯一性的，所以EMPNO索引將用來檢索記錄．
TABLE ACCESS BY ROWID ON EMP
INDEX UNIQUE SCAN ON EMP_NO_IDX
23. 避免在索引列上使用NOT
通常， 我們要避免在索引列上使用NOT, NOT會產生在和在索引列上使用函數相同的
影響. 當ORACLE」遇到」NOT ,!=,他就會停止使用索引轉而執行全表掃描.
舉例:
低效: (這里,不使用索引)
SELECT …
FROM DEPT
WHERE DEPT_CODE != 0;
高效: (這里,使用了索引)
SELECT …
FROM DEPT
知道的基本就這么多了，要加分啊~~O(∩_∩)O~