Oracle常見問題集(三)

151. 如何監控 SGA 中字典緩衝區的命中率？select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100'miss ratio',(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 'Hit ratio' from v$rowcachewhere gets+getmisses <>0group by parameter, gets, getmisses;152. 如何監控 SGA 中共用緩存區的命中率，應該小於1% ？select sum(pins) 'Total Pins', sum(reloads) 'Total Reloads',sum(reloads)/sum(pins) *100 libcachefrom v$librarycache;select sum(pinhits-reloads)/sum(pins) 'hitradio',sum(reloads)/sum(pins) 'reload percent'from v$librarycache;153. 如何顯示所有資料庫物件的類別和大小？select count(name) num_instances ,type ,sum(source_size)source_size ,sum(parsed_size) parsed_size ,sum(code_size) code_size,sum(error_size) error_size,sum(source_size) +sum(parsed_size) +sum(code_size)+sum(error_size) size_requiredfrom dba_object_sizegroup by type order by 2;154. 監控 SGA 中重做日誌緩存區的命中率，應該小於1%SELECT name, gets, misses, immediate_gets, immediate_misses,Decode(gets,0,0,misses/gets*100) ratio1,Decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate_misses)*100) ratio2FROM v$latch WHERE name IN ('redo allocation', 'redo copy');155. 監控記憶體和硬碟的排序比率，最好使它小於 .10，增加 sort_area_sizeSELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)','sorts (disk)');156. 如何監控當前資料庫誰在運行什麽SQL語句？SELECT osuser, username, sql_text from v$session a, v$sqltext bwhere a.sql_address =b.address order by address, piece;157. 如何監控字典緩衝區？SELECT (SUM(PINS - RELOADS)) / SUM(PINS) 'LIB CACHE' FROMV$LIBRARYCACHE;SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) 'ROWCACHE' FROM V$ROWCACHE;SELECT SUM(PINS) 'EXECUTIONS', SUM(RELOADS) 'CACHE MISSES WHILEEXECUTING' FROM V$LIBRARYCACHE;後者除以前者,此比率小於1%,接近0%爲好。SELECT SUM(GETS) 'DICTIONARY GETS',SUM(GETMISSES) 'DICTIONARYCACHE GET MISSES'FROM V$ROWCACHE158. 監控 MTSselect busy/(busy+idle) 'shared servers busy' from v$dispatcher;此值大於0.5時，參數需加大select sum(wait)/sum(totalq) 'dispatcher waits' from v$queue wheretype='dispatcher';select count(*) from v$dispatcher;select servers_highwater from v$mts;servers_highwater接近mts_max_servers時，參數需加大159. 如何知道當前用戶的ID號 SQL>SHOW USER;ORSQL>select user from dual;160. 如何查看碎片程度高的表 SELECT segment_name table_name , COUNT(*) extentsFROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BYsegment_nameHAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUPBY segment_name);162. 如何知道表在表空間中的存儲情況 select segment_name,sum(bytes),count(*) ext_quan from dba_extentswheretablespace_name='&tablespace_name' and segment_type='TABLE' groupby tablespace_name,segment_name;163. 如何知道索引在表空間中的存儲情況 select segment_name,count(*) from dba_extents wheresegment_type='INDEX' and owner='&owner'group by segment_name;164、如何知道使用CPU多的用戶session 11是cpu used by this sessionselect a.sid,spid,status,substr(a.program,1,40)prog,a.terminal,osuser,value/60/100 valuefrom v$session a,v$process b,v$sesstat cwhere c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr order byvalue desc;165. 如何知道監聽器日誌文件 以8I爲例$Oracle_HOME/NETWORK/LOG/LISTENER.LOG166. 如何知道監聽器參數文件 以8I爲例$ORACLE_HOME/NETWORK/ADMIN/LISTENER.ORA167. 如何知道TNS 連接文件 以8I爲例$ORACLE_HOME/NETWORK/ADMIN/TNSNAMES.ORA168. 如何知道Sql*Net 環境文件 以8I爲例$ORACLE_HOME/NETWORK/ADMIN/SQLNET.ORA169. 如何知道警告日誌文件 以8I爲例$ORACLE_HOME/ADMIN/SID/BDUMP/SIDALRT.LOG170. 如何知道基本結構 以8I爲例$ORACLE_HOME/RDBMS/ADMIN/STANDARD.SQL171. 如何知道建立資料字典視圖 以8I爲例$ORACLE_HOME/RDBMS/ADMIN/CATALOG.SQL172. 如何知道建立審計用資料字典視圖 以8I爲例$ORACLE_HOME/RDBMS/ADMIN/CATAUDIT.SQL173. 如何知道建立快照用資料字典視圖 以8I爲例$ORACLE_HOME/RDBMS/ADMIN/CATSNAP.SQL本講主要講的是SQL語句的優化方法! 主要基於ORACLE9I的.174. /*+ALL_ROWS*/表明對語句塊選擇基於開銷的優化方法,並獲得最佳吞吐量,使資源消耗最小化.例如:SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHEREEMP_NO='CCBZZP';175. /*+FIRST_ROWS*/表明對語句塊選擇基於開銷的優化方法,並獲得最佳回應時間,使資源消耗最小化.例如:SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHEREEMP_NO='CCBZZP';176. /*+CHOOSE*/表明假如資料字典中有訪問表的統計資訊,將基於開銷的優化方法,並獲得最佳的吞吐量;表明假如資料字典中沒有訪問表的統計資訊,將基於規則開銷的優化方法;例如:SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHEREEMP_NO='CCBZZP';177. /*+RULE*/表明對語句塊選擇基於規則的優化方法.例如:SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHEREEMP_NO='CCBZZP';178. /*+FULL(TABLE)*/表明對表選擇全局掃描的方法.例如:SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHEREEMP_NO='CCBZZP';179. /*+ROWID(TABLE)*/提示明確表明對指定表根據ROWID進行訪問.例如:SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHEREROWID>='AAAAAAAAAAAAAA'AND EMP_NO='CCBZZP';180. /*+CLUSTER(TABLE)*/提示明確表明對指定表選擇簇掃描的訪問方法,它只對簇物件有效.例如:SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMSWHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;181. /*+INDEX(TABLE INDEX_NAME)*/表明對表選擇索引的掃描方法.例如:SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE AREFEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';182. /*+INDEX_ASC(TABLE INDEX_NAME)*/表明對表選擇索引昇冪的掃描方法.例如:SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHEREDPT_NO='CCBZZP';183. /*+INDEX_COMBINE*/爲指定表選擇點陣圖訪問路經,假如INDEX_COMBINE中沒有提供作爲參數的索引,將選擇出點陣圖索引的布林組合方式.例如:SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROMBSEMPMSWHERE SAL<5000000 AND HIREDATE<SYSDATE;< SPAN>184. /*+INDEX_JOIN(TABLE INDEX_NAME)*/提示明確命令優化器使用索引作爲訪問路徑.例如:SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATEFROM BSEMPMS WHERE SAL<60000;185. /*+INDEX_DESC(TABLE INDEX_NAME)*/表明對表選擇索引降冪的掃描方法.例如:SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHEREDPT_NO='CCBZZP';186. /*+INDEX_FFS(TABLE INDEX_NAME)*/對指定的表執行快速全索引掃描,而不是全表掃描的辦法.例如:SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHEREDPT_NO='TEC305';187. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/提示明確進行執行規劃的選擇,將幾個單列索引的掃描合起來.例如:SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROMBSEMPMS WHERE EMP_NO='CCBZZP' AND DPT_NO='TDC306';188. /*+USE_CONCAT*/對查詢中的WHERE後面的OR條件進行轉換爲UNION ALL的組合查詢.例如:SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' ANDSEX='M';189. /*+NO_EXPAND*/對於WHERE後面的OR 或者IN-LIST的查詢語句,NO_EXPAND將阻止其基於優化器對其進行擴展.例如:SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' ANDSEX='M';190. /*+NOWRITE*/禁止對查詢塊的查詢重寫操作.191. /*+REWRITE*/可以將視圖作爲參數.192. /*+MERGE(TABLE)*/能夠對視圖的各個查詢進行相應的合併.例如:SELECT /*+MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A(SELET DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHEREA.DPT_NO=V.DPT_NOAND A.SAL>V.AVG_SAL;193. /*+NO_MERGE(TABLE)*/對於有可合併的視圖不再合併.例如:SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMSA (SELET DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHEREA.DPT_NO=V.DPT_NOAND A.SAL>V.AVG_SAL;194. /*+ORDERED*/根據表出現在FROM中的順序,ORDERED使ORACLE依此順序對其連接.例如:SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2B,TABLE3 CWHERE A.COL1=B.COL1 AND B.COL1=C.COL1;195. /*+USE_NL(TABLE)*/將指定表與嵌套的連接的行源進行連接,並把指定表作爲內部表.例如:SELECT /*+ORDERED USE_NL(BSEMPMS)*/BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMSWHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;196. /*+USE_MERGE(TABLE)*/將指定的表與其他行源通過合併排序連接方式連接起來.例如:SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMSWHEREBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;197. /*+USE_HASH(TABLE)*/將指定的表與其他行源通過哈希連接方式連接起來.例如:SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHEREBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;198. /*+DRIVING_SITE(TABLE)*/強制與ORACLE所選擇的位置不同的表進行查詢執行.例如:SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHEREBSEMPMS.DPT_NO=DEPT.DPT_NO;199. /*+LEADING(TABLE)*/將指定的表作爲連接次序中的首表.200. /*+CACHE(TABLE)*/當進行全表掃描時,CACHE提示能夠將表的檢索塊放置在緩衝區緩存中最近最少列表LRU的最近使用端例如:SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;201. /*+NOCACHE(TABLE)*/當進行全表掃描時,CACHE提示能夠將表的檢索塊放置在緩衝區緩存中最近最少列表LRU的最近使用端例如:SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;202. /*+APPEND*/直接插入到表的最後,可以提高速度.insert /*+append*/ into test1 select * from test4 ;203. /*+NOAPPEND*/通過在插入語句生存期內停止並行模式來啓動常規插入.insert /*+noappend*/ into test1 select * from test4 ;ORACLE優化器. 選用適合的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(systemglobal area)的共用池(shared buffer pool)中的記憶體可以被所有的資料庫用戶共用.因此,當你執行一個SQL語句(有時被稱爲一個游標)時,假如它和之前的執行過的語句完全相同, ORACLE就能很快獲得已經被解析的語句以及最好的執行路徑. ORACLE的這個功能大大地提高了SQL的執行性能並節省了記憶體的使用.可惜的是ORACLE只對簡單的表提供高速緩衝(cache buffering) ,這個功能並不適用於多表連接查詢.資料庫治理員必須在init.ora中爲這個區域設置合適的參數,當這個記憶體區域越大,就可以保留更多的語句,當然被共用的可能性也就越大了.當你向ORACLE 提交一個SQL語句,ORACLE會首先在這塊記憶體中查找相同的語句.這裏需要注明的是,ORACLE對兩者採取的是一種嚴格匹配,要達成共用,SQL語句必須完全相同(包括空格,換行等).共用的語句必須滿足三個條件:A. 字元級的比較:當前被執行的語句和共用池中的語句必須完全相同.例如:SELECT * FROM EMP;和下列每一個都不同SELECT * from EMP;Select * From Emp;SELECT * FROM EMP;B. 兩個語句所指的物件必須完全相同:例如:用戶 物件名 如何訪問Jack sal_limit private synonymWork_city public synonymPlant_detail public synonymJill sal_limit private synonymWork_city public synonymPlant_detail table owner考慮一下下列SQL語句能否在這兩個用戶之間共用.SQL能否共用原因select max(sal_cap) from sal_limit;不能每個用戶都有一個private synonym - sal_limit , 它們是不同的物件select count(*0 from work_city where sdesc like 'NEW%';能兩個用戶訪問相同的物件public synonym - work_cityselect a.sdesc,b.location from work_city a , plant_detail b wherea.city_id = b.city_id不能用戶jack 通過private synonym訪問plant_detail 而jill 是表的所有者,物件不同.C. 兩個SQL語句中必須使用相同的名字的綁定變數(bind variables)例如：第一組的兩個SQL語句是相同的(可以共用),而第二組中的兩個語句是不同的(即使在運行時,賦於不同的綁定變數相同的值)a.select pin , name from people where pin = :blk1.pin;select pin , name from people where pin = :blk1.pin;b.select pin , name from people where pin = :blk1.ot_ind;select pin , name from people where pin = :blk1.ov_ind;4. 選擇最有效率的表名順序(只在基於規則的優化器中有效)ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫在最後的表(基礎表 drivingtable)將被最先處理. 在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 EWHERE E.EMP_NO BETWEEN 1000 AND 2000AND E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCN將比下列SQL更有效率SELECT *FROM EMP E ,LOCATION L ,CATEGORY CWHERE E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCNAND E.EMP_NO BETWEEN 1000 AND 20005. WHERE子句中的連接順序．ORACLE採用自下而上的順序解析WHERE子句,根據這個原理,表之間的連接必須寫在其他WHERE條件之前,那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾.例如:(低效,執行時間156.3秒)SELECT …FROM EMP EWHERE SAL > 50000AND JOB = ‘MANAGER’AND 25 < (SELECT COUNT(*) FROM EMPWHERE MGR=E.EMPNO);(高效,執行時間10.6秒)SELECT …FROM EMP EWHERE 25 < (SELECT COUNT(*) FROM EMPWHERE MGR=E.EMPNO)AND SAL > 50000AND JOB = ‘MANAGER’;6. SELECT子句中避免使用 ‘ * ‘當你想在SELECT子句中列出所有的COLUMN時,使用動態SQL列引用 ‘*’是一個方便的方法.不幸的是,這是一個非常低效的方法. 實際上,ORACLE在解析的過程中, 會將’*’ 依次轉換成所有的列名,這個工作是通過查詢資料字典完成的, 這意味著將耗費更多的時間.7. 減少訪問資料庫的次數當執行每條SQL語句時, ORACLE在內部執行了許多工作: 解析SQL語句, 估算索引的利用率, 綁定變數 , 讀資料塊等等.由此可見, 減少訪問資料庫的次數 , 就能實際上減少ORACLE的工作量.例如,以下有三種方法可以檢索出雇員號等於0342或0291的職員.方法1 (最低效)SELECT EMP_NAME , SALARY , GRADEFROM EMPWHERE EMP_NO = 342;SELECT EMP_NAME , SALARY , GRADEFROM EMPWHERE EMP_NO = 291;方法2 (次低效)DECLARECURSOR C1 (E_NO NUMBER) ISSELECT EMP_NAME,SALARY,GRADEFROM EMPWHERE EMP_NO = E_NO;BEGINOPEN 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.GRADEFROM EMP A,EMP BWHERE A.EMP_NO = 342AND B.EMP_NO = 291;注重:在SQL*Plus , SQL*Forms和Pro*C中重新設置ARRAYSIZE參數, 可以增加每次資料庫訪問的檢索資料量,建議值爲2008. 使用DECODE函數來減少處理時間使用DECODE函數可以避免重復掃描相同記錄或重復連接相同的表.例如:SELECT COUNT(*)，SUM(SAL)FROM　EMPWHERE DEPT_NO = 0020AND ENAME LIKE　‘SMITH%’;SELECT COUNT(*)，SUM(SAL)FROM　EMPWHERE DEPT_NO = 0030AND 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_SALFROM EMP WHERE ENAME LIKE ‘SMITH%’;類似的,DECODE函數也可以運用於GROUP BY 和ORDER BY子句中.9. 整合簡單,無關聯的資料庫訪問假如你有幾個簡單的資料庫查詢語句,你可以把它們整合到一個查詢中(即使它們之間沒有關係)例如:SELECT NAMEFROM EMPWHERE EMP_NO = 1234;SELECT NAMEFROM DPTWHERE DPT_NO = 10 ;SELECT NAMEFROM CATWHERE CAT_TYPE = ‘RD’;上面的3個查詢可以被合併成一個:SELECT E.NAME , D.NAME , C.NAMEFROM CAT C , DPT D , EMP E,DUAL XWHERE NVL(‘X’,X.DUMMY) = NVL(‘X’,E.ROWID(+))AND NVL(‘X’,X.DUMMY) = NVL(‘X’,D.ROWID(+))AND NVL(‘X’,X.DUMMY) = NVL(‘X’,C.ROWID(+))AND E.EMP_NO(+) = 1234AND D.DEPT_NO(+) = 10AND C.CAT_TYPE(+) = ‘RD’;(譯者按: 雖然採取這種方法,效率得到提高,但是程式的可讀性大大降低,所以讀者 還是要權衡之間的利弊)10. 刪除重復記錄最高效的刪除重復記錄方法 ( 因爲使用了ROWID)DELETE FROM EMP EWHERE E.ROWID > (SELECT MIN(X.ROWID)FROM EMP XWHERE X.EMP_NO = E.EMP_NO);11. 用TRUNCATE替代DELETE當刪除表中的記錄時,在通常情況下, 回滾段(rollback segments ) 用來存放可以被恢復的資訊.假如你沒有COMMIT事務,ORACLE會將資料恢復到刪除之前的狀態(準確地說是恢復到執行刪除命令之前的狀況)而當運用TRUNCATE時,回滾段不再存放任何可被恢復的資訊.當命令運行後,資料不能被恢復.因此很少的資源被調用,執行時間也會很短.(譯者按: TRUNCATE只在刪除全表適用,TRUNCATE是DDL不是DML)12. 儘量多使用COMMIT只要有可能,在程式中儘量多使用COMMIT, 這樣程式的性能得到提高,需求也會因爲COMMIT所釋放的資源而減少:COMMIT所釋放的資源:a. 回滾段上用於恢復資料的資訊.b. 被程式語句獲得的鎖c. redo log buffer 中的空間d. ORACLE爲治理上述3種資源中的內部花費(譯者按: 在使用COMMIT時必須要注重到事務的完整性,現實中效率和事務完整性往往是魚和熊掌不可得兼)13. 計算記錄條數和一般的觀點相反, count(*) 比count(1)稍快 , 當然假如可以通過索引檢索,對索引列的計數仍舊是最快的. 例如COUNT(EMPNO)(譯者按: 在CSDN論壇中,曾經對此有過相當熱烈的討論,作者的觀點並不十分準確,通過實際的測試,上述三種方法並沒有顯著的性能差別)14. 用Where子句替換HAVING子句避免使用HAVING子句, HAVING 只會在檢索出所有記錄之後才對結果集進行過濾. 這個處理需要排序,總計等操作.假如能通過WHERE子句限制記錄的數目,那就能減少這方面的開銷.例如:低效:SELECT REGION，AVG(LOG_SIZE)FROM LOCATIONGROUP BY REGIONHAVING REGION REGION ! = ‘SYDNEY’AND REGION != ‘PERTH’高效SELECT REGION，AVG(LOG_SIZE)FROM LOCATIONWHERE REGION REGION != ‘SYDNEY’AND REGION != ‘PERTH’GROUP BY REGION(譯者按: HAVING 中的條件一般用於對一些集合函數的比較,如COUNT() 等等.除此而外,一般的條件應該寫在WHERE子句中)15. 減少對表的查詢在含有子查詢的SQL語句中,要特別注重減少對表的查詢.例如:低效SELECT TAB_NAMEFROM TABLESWHERE TAB_NAME = ( SELECT TAB_NAMEFROM TAB_COLUMNSWHERE VERSION = 604)AND　DB_VER= ( SELECT DB_VERFROM TAB_COLUMNSWHERE VERSION = 604)高效SELECT TAB_NAMEFROM TABLESWHERE (TAB_NAME,DB_VER)= ( SELECT TAB_NAME,DB_VER)FROM TAB_COLUMNSWHERE VERSION = 604)Update 多個Column 例子:低效:UPDATE EMPSET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)WHERE EMP_DEPT = 0020;高效:UPDATE EMPSET (EMP_CAT, SAL_RANGE)= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)FROM EMP_CATEGORIES)WHERE EMP_DEPT = 0020;16. 通過內部函數提高SQL效率.SELECT H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*)FROM HISTORY_TYPE T,EMP E,EMP_HISTORY HWHERE H.EMPNO = E.EMPNOAND H.HIST_TYPE = T.HIST_TYPEGROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;通過調用下面的函數可以提高效率.FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2ASTDESC VARCHAR2(30);CURSOR C1 ISSELECT TYPE_DESCFROM HISTORY_TYPEWHERE HIST_TYPE = TYP;BEGINOPEN C1;FETCH C1 INTO TDESC;CLOSE C1;RETURN (NVL(TDESC,’ ’));END;FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2ASENAME VARCHAR2(30);CURSOR C1 ISSELECT ENAMEFROM EMPWHERE EMPNO=EMP;BEGINOPEN 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 HGROUP BY H.EMPNO , H.HIST_TYPE;(譯者按: 經常在論壇中看到如 ’能不能用一個SQL寫出….’ 的貼子, 殊不知複雜的SQL往往犧牲了執行效率.能夠把握上面的運用函數解決問題的方法在實際工作中是非常有意義的)17. 使用表的別名(Alias)當在SQL語句中連接多個表時,請使用表的別名並把別名字首於每個Column上.這樣一來,就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤. (譯者注:Column歧義指的是由於SQL中不同的表具有相同的Column名,當SQL語句中出現這個Column時,SQL解析器無法判斷這個Column的歸屬)18. 用EXISTS替代IN在許多基於基礎表的查詢中,爲了滿足一個條件,往往需要對另一個表進行聯接.在這種情況下, 使用EXISTS(或NOTEXISTS)通常將提高查詢的效率.低效:SELECT *FROM EMP (基礎表)WHERE EMPNO > 0AND DEPTNO IN (SELECT DEPTNOFROM DEPTWHERE LOC = ‘MELB’)高效:SELECT *FROM EMP (基礎表)WHERE EMPNO > 0AND EXISTS (SELECT ‘X’FROM DEPTWHERE DEPT.DEPTNO = EMP.DEPTNOAND LOC = ‘MELB’)(譯者按: 相對來說,用NOT EXISTS替換NOT IN 將更顯著地提高效率,下一節中將指出)19. 用NOT EXISTS替代NOT IN在子查詢中,NOT IN子句將執行一個內部的排序和合併. 無論在哪種情況下,NOT IN都是最低效的(因爲它對子查詢中的表執行了一個全表遍曆). 爲了避免使用NOT IN ,我們可以把它改寫成外連接(Outer Joins)或NOTEXISTS.例如:SELECT …FROM EMPWHERE DEPT_NO NOT IN (SELECT DEPT_NOFROM DEPTWHERE DEPT_CAT=’A’);爲了提高效率.改寫爲:(方法一: 高效)SELECT ….FROM EMP A,DEPT BWHERE A.DEPT_NO = B.DEPT(+)AND B.DEPT_NO IS NULLAND B.DEPT_CAT(+) = ‘A’(方法二: 最高效)SELECT ….FROM EMP EWHERE NOT EXISTS (SELECT ‘X’FROM DEPT DWHERE D.DEPT_NO = E.DEPT_NOAND DEPT_CAT = ‘A’);20. 用表連接替換EXISTS通常來說 , 採用表連接的方式比EXISTS更有效率SELECT ENAMEFROM EMP EWHERE EXISTS (SELECT ‘X’FROM DEPTWHERE DEPT_NO = E.DEPT_NOAND DEPT_CAT = ‘A’);(更高效)SELECT ENAMEFROM DEPT D,EMP EWHERE E.DEPT_NO = D.DEPT_NOAND DEPT_CAT = ‘A’ ;(譯者按: 在RBO的情況下,前者的執行路徑包括FILTER,後者使用NESTED LOOP)