1、 4/4Oracle SQL性能優(yōu)化方法研究 Oracle SQL性能優(yōu)化方法探討 Oracle性能優(yōu)化方法(SQL篇) (1) 1綜述 (2) 2表分區(qū)的應(yīng)用 (2) 3訪問Table的方式 (3) 4共享SQL語句 (3) 5選擇最有效率的表名順序 (5) 6WHERE子句中的連接順序 (6) 7SELECT子句中幸免使用* (6) 8減少訪問數(shù)據(jù)庫的次數(shù) (6) 9使用DECODE函數(shù)來減少處理時刻 (7) 10整合簡單,無關(guān)聯(lián)的數(shù)據(jù)庫訪問 (8) 11刪除重復(fù)記錄 (8) 12用TRUNCATE替代DELETE (9) 13盡量多使用COMMIT (9) 14計算記錄條數(shù) (9) 15

2、用Where子句替換HAVING子句 (9) 16減少對表的查詢 (10) 17通過內(nèi)部函數(shù)提高SQL效率 (11) 18使用表的不名(Alias) (12) 19用EXISTS替代IN (12) 20用NOT EXISTS替代NOT IN (13) 21識不低效執(zhí)行的SQL語句 (13) 22使用TKPROF 工具來查詢SQL性能狀態(tài) (14) 23用EXPLAIN PLAN 分析SQL語句 (14) 24實時批量的處理 (16) 1綜述 ORACLE數(shù)據(jù)庫的性能調(diào)整是個重要，卻又有難度的話題，如何有效地進(jìn)行調(diào)整，需要通過反反復(fù)復(fù)的過程。在數(shù)據(jù)庫建立時，就能依照顧用的需要合理設(shè)計分配表空間以

3、及存儲參數(shù)、內(nèi)存使用初始化參數(shù)，對以后的數(shù)據(jù)庫性能有專門大的益處，建立好后，又需要在應(yīng)用中不斷進(jìn)行應(yīng)用程序的優(yōu)化和調(diào)整，這需要在大量的實踐工作中不斷地積存經(jīng)驗，從而更好地進(jìn)行數(shù)據(jù)庫的調(diào)優(yōu)。 數(shù)據(jù)庫性能調(diào)優(yōu)的方法 調(diào)整內(nèi)存 調(diào)整I/O 調(diào)整資源的爭用問題 調(diào)整操作系統(tǒng)參數(shù) 調(diào)整數(shù)據(jù)庫的設(shè)計 調(diào)整應(yīng)用程序 本文針對應(yīng)用程序的調(diào)整，來講明對數(shù)據(jù)庫性能如何進(jìn)行優(yōu)化。 2表分區(qū)的應(yīng)用 關(guān)于海量數(shù)據(jù)的表，能夠考慮建立分區(qū)以提高操作效率。建 立分區(qū)一般以關(guān)鍵字為分區(qū)的標(biāo)志，也能夠以其他字段作為分區(qū)的標(biāo)志，但效率不如關(guān)鍵字高。建立分區(qū)的語句在建表時能夠進(jìn)行講明： create table TABLENAME(

4、) partition by range (PutOutNo) (partition PART1 values lessthan (200312319999) partition PART2 values lessthan (200412319999) 。 如此，在進(jìn)行大部分?jǐn)?shù)據(jù)查詢，數(shù)據(jù)更新和數(shù)據(jù)插入時，Oracle自動推斷操作應(yīng)該在哪個分區(qū)進(jìn)行，幸免了整表操作，提高了執(zhí)行的效率 3訪問Table的方式 ORACLE 采納兩種訪問表中記錄的方式: 全表掃描 全表掃描確實是順序地訪問表中每條記錄. ORACLE采納一次讀入多個數(shù)據(jù)塊(database block)的方式優(yōu)化全表掃描. 通過RO

5、WID訪問表 能夠采納基于ROWID的訪問方式情況,提高訪問表的效率, , ROWID包含了表中記錄的物理位置信息.ORACLE采納索引(INDEX)實現(xiàn)了數(shù)據(jù)和存放數(shù)據(jù)的物理位置(ROWID)之間的聯(lián)系. 通常索引提供了快速訪問ROWID的方法,因此那些基于索引列的查詢就能夠得到性能上的提高. 4共享SQL語句 為了不重復(fù)解析相同的SQL語句,在第一次解析之后, ORACLE將SQL語句存放在內(nèi)存中.這塊位于系統(tǒng)全局區(qū)域SGA(system global area)的共享池(shared buffer pool)中的內(nèi)存能夠被所有的數(shù)據(jù)庫用戶共享. 因此,當(dāng)執(zhí)行一個SQL語句(有時被稱為一個

6、游標(biāo))時,假如它和之前的執(zhí)行過 的語句完全相同, ORACLE就能專門快獲得差不多被解析的語句以及最好的執(zhí)行路徑. ORACLE的那個功能大大地提高了SQL的執(zhí)行性能并節(jié)約了內(nèi)存的使用.但是ORACLE只對簡單的表提供高速緩沖(cache buffering) ,那個功能并不適用于多表連接查詢. 數(shù)據(jù)庫治理員必須在init.ora中為那個區(qū)域設(shè)置合適的參數(shù),當(dāng)那個內(nèi)存區(qū)域越大,就能夠保留更多的語句,因此被共享的可能性也就越大了. 當(dāng)向ORACLE 提交一個SQL語句,ORACLE會首先在這塊內(nèi)存中查找相同的語句.那個地點需要注明的是,ORACLE對兩者采取的是一種嚴(yán)格匹配,要達(dá)成共享,SQL語

7、句必須完全相同(包括空格,換行等). 共享的語句必須滿足三個條件: 字符級的比較: 當(dāng)前被執(zhí)行的語句和共享池中的語句必須完全相同.例如: SELECT * FROM EMP; 和下列每一個都不同 SELECT * from EMP; Select * From Emp; SELECT * FROM EMP; 兩個語句所指的對象必須完全相同: 例如: 用戶對象名如何訪問 Jack sal_limit private synonym Work_city public synonym Plant_detail public synonym Jill sal_limit private synonym

8、 Work_city public synonym Plant_detail table owner 下列SQL語句不能在這兩個用戶之間共享. select max(sal_cap) from sal_limit; 緣故 每個用戶都有一個private synonym - sal_limit , 它們是不同的對象 下列SQL語句能在這兩個用戶之間共享. select count(*) from work_city where sdesc like NEW%; 緣故：兩個用戶訪問相同的對象public synonym - work_city 下列SQL語句不能在這兩個用戶之間共享. select

9、 a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id 緣故：用戶jack 通過private synonym訪問plant_detail 而jill 是表的所有者,對象不同. 兩個SQL語句中必須使用相同的名字的綁定變量(bind variables) 例如：第一組的兩個SQL語句是相同的(能夠共享),而第二組中的兩個語句是不同的(即使在運行時,賦于不同的綁定變量相同的值) 1. select pin , name from people where pin = :blk1.pin;

10、select pin , name from people where pin = :blk1.pin; 2. select pin , name from people where pin = :blk1.ot_ind; select pin , name from people where pin = :blk1.ov_ind; 5選擇最有效率的表名順序 ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫在最后的表(基礎(chǔ)表 driving table)將被最先處理. 在FROM子句中包含多個表的情況下,你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表.當(dāng)ORACLE處

11、理多個表時, 會運用排序及合并的方式連接它們.首先,掃描第一個表(FROM子句中最后的那個表)并對記錄進(jìn)行派序,然后掃描第二個表(FROM子句中最后第二個表),最后將所有從第二個表中檢索出的記錄與第一個表中合適記錄進(jìn)行合并. 例如: 表 TAB1 16,384 條記錄,表 TAB2 1 條記錄 選擇TAB2作為基礎(chǔ)表 (最好的方法) select count(*) from tab1,tab2 選擇TAB2作為基礎(chǔ)表 (不佳的方法) select count(*) from tab2,tab1 假如有3個以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎(chǔ)表,

12、 交叉表是指那個被其他 表所引用的表. 例如: 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 6WHERE子句中的連接順序 ORACLE采納自下而上的順