【文章內(nèi)容簡介】 建議 14 數(shù)據(jù)不能被恢復(fù) .因此很少的資源被調(diào)用 ,執(zhí)行時(shí)間也會(huì)很短 . . 降低數(shù)據(jù)庫競爭優(yōu)化 （ 建議） 盡量多使用 COMMIT 只要有可能 ,在程序中盡量多使用 COMMIT, 這樣程序的性能得到提高 ,需求也會(huì)因?yàn)?COMMIT 所釋放的資源而減少 . COMMIT 所釋放的資源 : ? 回滾段上用于恢復(fù)數(shù)據(jù)的信息 。 ? 被程序語句獲得的鎖 ? redo log buffer 中的空間 ? ORACLE 為管理上述 3 種資源中的內(nèi)部花費(fèi) . 用 Where子句替換 HAVING子句 （建議） 避免使用 HAVING 子句 , HAVING 只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過濾 . 這個(gè)處理需要排序 ,總計(jì)等操作 . 如果能通過 WHERE子句限制記錄的數(shù)目 ,那就能減少這方面的開銷 . 例如 : ? 低效 : SELECT REGION， AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION != ‘SYDNEY39。 AND REGION != ‘PERTH39。 ? 高效 SELECT REGION， AVG(LOG_SIZE) FROM LOCATION WHERE REGION != ‘SYDNEY39。 AND REGION != ‘PERTH39。 GROUP BY REGION 北六融合項(xiàng)目應(yīng)用性能與安全開發(fā)建議 15 . 減少對(duì)表的查詢 （建議） 在含有子查詢的 SQL 語句中 ,要特別注意減少對(duì)表的查詢 例如 : ? 低效 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 多個(gè) 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 北六融合項(xiàng)目應(yīng)用性能與安全開發(fā)建議 16 SET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY) , MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020。 . 通過內(nèi)部函數(shù)提高 SQL效率 (建議 ) SELECT ,COUNT(*) FROM HISTORY_TYPE T,EMP E,EMP_HISTORY H WHERE = AND = GROUP BY ,。 通過調(diào)用下面的函數(shù)可以提高效率 . 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,39。?39。))。 END。 北六融合項(xiàng)目應(yīng)用性能與安全開發(fā)建議 17 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,39。?39。))。 END。 SELECT ,LOOKUP_EMP(), ,LOOKUP_HIST_TYPE(),COUNT(*) FROM EMP_HISTORY H GROUP BY , 。 . 使用表的別名 (Alias)(嚴(yán)格執(zhí)行 ) 當(dāng)在 SQL 語句中連接多個(gè)表時(shí) , 請使用表的別名并把別名前綴于每個(gè) Column上 .這樣一來 ,就可以減少解析的時(shí)間并減少那些由 Column 歧義引起的語法錯(cuò)誤 . (譯者注 : Column歧義指的是由于 SQL中不同的表具有相同的 Column名 ,當(dāng) SQL語句中出現(xiàn)這個(gè) Column 時(shí) ,SQL 解析器無法判斷這個(gè) Column 的歸屬 ) 北六融合項(xiàng)目應(yīng)用性能與安全開發(fā)建議 18 . 用 EXISTS替代 IN(強(qiáng)烈建議 ) 在許多基于基礎(chǔ)表的查詢中 ,為了滿足一個(gè)條件 ,往往需要對(duì)另一個(gè)表進(jìn)行聯(lián)接 .在這種情況下 , 使用 EXISTS(或 NOT EXISTS)通常將提高查詢的效率 . ? 低效 : SELECT * FROM EMP (基礎(chǔ)表 ) WHERE EMPNO 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB39。) ? 高效 : SELECT * FROM EMP (基礎(chǔ)表 ) WHERE EMPNO 0 AND EXISTS (SELECT ‘X39。 FROM DEPT WHERE = AND LOC = ‘MELB39。) (譯者按 : 相對(duì)來說 ,用 NOT EXISTS 替換 NOT IN 將更顯著地提高效率 ,下一節(jié)中將指出 ) . 用 NOT EXISTS 替代 NOT IN(嚴(yán)格要求 ) 在子查詢中 ,NOT IN 子句將執(zhí)行一個(gè)內(nèi)部的排序和合并 . 無論在哪種情況下 ,NOT IN都是最低效的 (因?yàn)樗鼘?duì)子查詢中的表執(zhí)行了一個(gè)全表遍歷 ). 為了避免使用 NOT IN ,我們可以把它改寫成外連接 (Outer Joins)或 NOT EXISTS. 北六融合項(xiàng)目應(yīng)用性能與安全開發(fā)建議 19 例如 : SELECT ? FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=39。A39。)。 為了提高效率 .改寫為 : ? (方法一 : 高效 ) SELECT ?. FROM EMP A,DEPT B WHERE = (+) AND IS NULL AND (+) = ‘A39。 ? (方法二 : 最高效 ) SELECT ?. FROM EMP E WHERE NOT EXISTS (SELECT ‘X39。 FROM DEPT D WHERE = AND DEPT_CAT = ‘A39。)。 . 用表連接替換 EXISTS（建議） 通常來說 , 采用表連接的方式比 EXISTS 更有效率 SELECT ENAME FROM EMP E WHERE EXISTS (SELECT ‘X39。 FROM DEPT 北六融合項(xiàng)目應(yīng)用性能與安全開發(fā)建議 20 WHERE DEPT_NO = AND DEPT_CAT = ‘A39。)。 (更高效 ) SELECT ENAME FROM DEPT D,EMP E WHERE = AND DEPT_CAT = ‘A39。 。 (譯者按 : 在 RBO 的情況下 ,前者的執(zhí)行路徑包括 FILTER,后者使用 NESTED LOOP) . 用 EXISTS替換 DISTINCT(建議 ) 當(dāng)提交一個(gè)包含一對(duì)多表信息 (比如部門表和雇員表 )的查詢時(shí) ,避免在 SELECT子句中使用 DISTINCT. 一般可以考慮用 EXIST 替換 例如 : ? 低效 : SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE = ? 高效 : SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X39。 FROM EMP E WHERE = )。 EXISTS 使查詢更為迅速 ,因?yàn)?RDBMS 核心模塊將在子查詢的條件一旦滿足后 ,北六融合項(xiàng)目應(yīng)用性能與安全開發(fā)建議 21 立刻返回結(jié)果 . . 識(shí)別 39。低效執(zhí)行 39。的 SQL語句 (自查方法 ) 用下列 SQL 工具找出低效 SQL: SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, ROUND((BUFFER_GETSDISK_READS)/BUFFER_GETS,2) Hit_radio, ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS0 AND BUFFER_GETS 0 AND (BUFFER_GETSDISK_READS)/BUFFER_GETS ORDER BY 4 DESC。 (譯者按 : 雖然目前各種關(guān)于 SQL優(yōu)化的圖形化工具層出不窮 ,但是寫出自己的SQL 工具來解決問題始終是一個(gè)最好的方法 ) . 使用 TKPROF 工具來查詢 SQL性能狀態(tài) （自查方法） SQL trace 工具收集正在執(zhí)行的 SQL 的性能狀態(tài)數(shù)據(jù)并記錄到一個(gè)跟蹤文件中 . 這個(gè)跟蹤文件提供了許多有用的信息 ,例如解析次數(shù) .執(zhí)行次數(shù) ,CPU 使用時(shí)間等 .這些數(shù)據(jù)將可以用來優(yōu)化你的系統(tǒng) . 設(shè)置 SQL TRACE 在會(huì)話級(jí)別 : 有效 ALTER SESSION SET SQL_TRACE TRUE 設(shè)置 SQL TRACE 在 整個(gè)數(shù)據(jù)庫有效仿 , 你必須將 SQL_TRACE 參數(shù)在 中設(shè)為 TRUE, USER_DUMP_DEST 參數(shù)說明了生成跟蹤文件的目錄 (譯者按 : 這一節(jié)中 ,作者并沒有提到 TKPROF 的用法 , 對(duì) SQL TRACE 的用法也北六融合項(xiàng)目應(yīng)用性能與安全開發(fā)建議 22 不夠準(zhǔn)確 , 設(shè)置 SQL TRACE 首先要在 中設(shè)定 TIMED_STATISTICS, 這樣才能得到那些重要的時(shí)間狀態(tài) . 生成的 trace 文件是不可讀的 ,所以要用 TKPROF 工具對(duì)其進(jìn)行轉(zhuǎn)換 ,TKPROF有許多執(zhí)行參數(shù) . 大家可以參考 ORACLE手冊來了解具體的配置 . ) . 用 EXPLAIN PLAN 分析 SQL語句 （自查方法） EXPLAIN PLAN 是一個(gè)很好的分析 SQL 語句的工具 ,它甚至可以在不執(zhí)行 SQL 的情況下分析語句 . 通過分析 ,我們就可以知道 ORACLE 是怎么樣連接表 ,使用什么方式掃描表 (索引掃描或全表掃描 )以及使用到的索引名稱 . 你需要按照從里到外 ,從上到下的次序解讀分析的結(jié)果 . EXPLAIN PLAN 分析的結(jié)果是用縮進(jìn)的格式排列的 , 最內(nèi)部的操作將被最先解讀 , 如果兩個(gè)操作處于同一層中 ,帶有最小操作號(hào)的將被首先執(zhí)行 . NESTED LOOP 是 少數(shù)不按照上述規(guī)則處理的操作 , 正確的執(zhí)行路徑是檢查對(duì)NESTED LOOP 提供數(shù)據(jù)的操作 ,其中操作號(hào)最小的將被最先處理 . 譯者按 : 通過實(shí)踐 , 感到還是用 SQLPLUS 中的 SET TRACE 功能比較方便 . 舉例 : SQL list 1 SELECT * 2 FROM dept, emp 3* WHERE = SQL set autotrace traceonly /*traceonly 可以不顯示執(zhí)行結(jié)果 */ SQL / 14 rows selected. Execution Plan 北六融合項(xiàng)目應(yīng)用性能與安全開發(fā)建議 23 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NEST