151. 如何監(jiān)控 SGA 中字典緩衝區(qū)的命中率？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. 如何監(jiān)控 SGA 中共用緩存區(qū)的命中率，應(yīng)該小於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. 監(jiān)控 SGA 中重做日誌緩存區(qū)的命中率，應(yīng)該小於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. 監(jiān)控記憶體和硬碟的排序比率，最好使它小於 .10，增加 sort_area_sizeSELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)','sorts (disk)');156. 如何監(jiān)控當(dāng)前資料庫誰在運(yùn)行什麼SQL語句？SELECT osuser, username, sql_text from v$session a, v$sqltext bwhere a.sql_address =b.address order by address, piece;157. 如何監(jiān)控字典緩衝區(qū)？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%爲(wèi)好。SELECT SUM(GETS) 'DICTIONARY GETS',SUM(GETMISSES) 'DICTIONARYCACHE GET MISSES'FROM V$ROWCACHE158. 監(jiān)控 MTSselect busy/(busy+idle) 'shared servers busy' from v$dispatcher;此值大於0.5時(shí)，參數(shù)需加大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時(shí)，參數(shù)需加大159. 如何知道當(dāng)前用戶的ID號(hào) 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. 如何知道表在表空間中的存儲(chǔ)情況 select segment_name,sum(bytes),count(*) ext_quan from dba_extentswheretablespace_name='&tablespace_name' and segment_type='TABLE' groupby tablespace_name,segment_name;163. 如何知道索引在表空間中的存儲(chǔ)情況 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. 如何知道監(jiān)聽器日誌文件 以8I爲(wèi)例$Oracle_HOME/NETWORK/LOG/LISTENER.LOG166. 如何知道監(jiān)聽器參數(shù)文件 以8I爲(wèi)例$ORACLE_HOME/NETWORK/ADMIN/LISTENER.ORA167. 如何知道TNS 連接文件 以8I爲(wèi)例$ORACLE_HOME/NETWORK/ADMIN/TNSNAMES.ORA168. 如何知道Sql*Net 環(huán)境文件 以8I爲(wèi)例$ORACLE_HOME/NETWORK/ADMIN/SQLNET.ORA169. 如何知道警告日誌文件 以8I爲(wèi)例$ORACLE_HOME/ADMIN/SID/BDUMP/SIDALRT.LOG170. 如何知道基本結(jié)構(gòu) 以8I爲(wèi)例$ORACLE_HOME/RDBMS/ADMIN/STANDARD.SQL171. 如何知道建立資料字典視圖 以8I爲(wèi)例$ORACLE_HOME/RDBMS/ADMIN/CATALOG.SQL172. 如何知道建立審計(jì)用資料字典視圖 以8I爲(wèi)例$ORACLE_HOME/RDBMS/ADMIN/CATAUDIT.SQL173. 如何知道建立快照用資料字典視圖 以8I爲(wèi)例$ORACLE_HOME/RDBMS/ADMIN/CATSNAP.SQL本講主要講的是SQL語句的優(yōu)化方法! 主要基於ORACLE9I的.174. /*+ALL_ROWS*/表明對(duì)語句塊選擇基於開銷的優(yōu)化方法,並獲得最佳吞吐量,使資源消耗最小化.例如:SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHEREEMP_NO='CCBZZP';175. /*+FIRST_ROWS*/表明對(duì)語句塊選擇基於開銷的優(yōu)化方法,並獲得最佳回應(yīng)時(shí)間,使資源消耗最小化.例如:SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHEREEMP_NO='CCBZZP';176. /*+CHOOSE*/表明假如資料字典中有訪問表的統(tǒng)計(jì)資訊,將基於開銷的優(yōu)化方法,並獲得最佳的吞吐量;表明假如資料字典中沒有訪問表的統(tǒng)計(jì)資訊,將基於規(guī)則開銷的優(yōu)化方法;例如:SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHEREEMP_NO='CCBZZP';177. /*+RULE*/表明對(duì)語句塊選擇基於規(guī)則的優(yōu)化方法.例如:SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHEREEMP_NO='CCBZZP';178. /*+FULL(TABLE)*/表明對(duì)表選擇全局掃描的方法.例如:SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHEREEMP_NO='CCBZZP';179. /*+ROWID(TABLE)*/提示明確表明對(duì)指定表根據(jù)ROWID進(jìn)行訪問.例如:SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHEREROWID>='AAAAAAAAAAAAAA'AND EMP_NO='CCBZZP';180. /*+CLUSTER(TABLE)*/提示明確表明對(duì)指定表選擇簇掃描的訪問方法,它只對(duì)簇物件有效.例如: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)*/表明對(duì)表選擇索引的掃描方法.例如:SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE AREFEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';182. /*+INDEX_ASC(TABLE INDEX_NAME)*/表明對(duì)表選擇索引昇冪的掃描方法.例如:SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHEREDPT_NO='CCBZZP';183. /*+INDEX_COMBINE*/爲(wèi)指定表選擇點(diǎn)陣圖訪問路經(jīng),假如INDEX_COMBINE中沒有提供作爲(wèi)參數(shù)的索引,將選擇出點(diǎn)陣圖索引的布林組合方式.例如:SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROMBSEMPMSWHERE SAL<5000000 AND HIREDATE<SYSDATE;< SPAN>184. /*+INDEX_JOIN(TABLE INDEX_NAME)*/提示明確命令優(yōu)化器使用索引作爲(wèi)訪問路徑.例如:SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATEFROM BSEMPMS WHERE SAL<60000;185. /*+INDEX_DESC(TABLE INDEX_NAME)*/表明對(duì)表選擇索引降冪的掃描方法.例如:SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHEREDPT_NO='CCBZZP';186. /*+INDEX_FFS(TABLE INDEX_NAME)*/對(duì)指定的表執(zhí)行快速全索引掃描,而不是全表掃描的辦法.例如:SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHEREDPT_NO='TEC305';187. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/提示明確進(jìn)行執(zhí)行規(guī)劃的選擇,將幾個(gè)單列索引的掃描合起來.例如:SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROMBSEMPMS WHERE EMP_NO='CCBZZP' AND DPT_NO='TDC306';188. /*+USE_CONCAT*/對(duì)查詢中的WHERE後面的OR條件進(jìn)行轉(zhuǎn)換爲(wèi)UNION ALL的組合查詢.例如:SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' ANDSEX='M';189. /*+NO_EXPAND*/對(duì)於WHERE後面的OR 或者IN-LIST的查詢語句,NO_EXPAND將阻止其基於優(yōu)化器對(duì)其進(jìn)行擴(kuò)展.例如:SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' ANDSEX='M';190. /*+NOWRITE*/禁止對(duì)查詢塊的查詢重寫操作.191. /*+REWRITE*/可以將視圖作爲(wèi)參數(shù).192. /*+MERGE(TABLE)*/能夠?qū)σ晥D的各個(gè)查詢進(jìn)行相應(yīng)的合併.例如: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)*/對(duì)於有可合併的視圖不再合併.例如: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*/根據(jù)表出現(xiàn)在FROM中的順序,ORDERED使ORACLE依此順序?qū)ζ溥B接.例如: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)*/將指定表與嵌套的連接的行源進(jìn)行連接,並把指定表作爲(wèi)內(nèi)部表.例如: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)*/強(qiáng)制與ORACLE所選擇的位置不同的表進(jìn)行查詢執(zhí)行.例如:SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHEREBSEMPMS.DPT_NO=DEPT.DPT_NO;199. /*+LEADING(TABLE)*/將指定的表作爲(wèi)連接次序中的首表.200. /*+CACHE(TABLE)*/當(dāng)進(jìn)行全表掃描時(shí),CACHE提示能夠?qū)⒈淼臋z索塊放置在緩衝區(qū)緩存中最近最少列表LRU的最近使用端例如:SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;201. /*+NOCACHE(TABLE)*/當(dāng)進(jìn)行全表掃描時(shí),CACHE提示能夠?qū)⒈淼臋z索塊放置在緩衝區(qū)緩存中最近最少列表LRU的最近使用端例如:SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;202. /*+APPEND*/直接插入到表的最後,可以提高速度.insert /*+append*/ into test1 select * from test4 ;203. /*+NOAPPEND*/通過在插入語句生存期內(nèi)停止並行模式來啓動(dòng)常規(guī)插入.insert /*+noappend*/ into test1 select * from test4 ;ORACLE優(yōu)化器. 選用適合的ORACLE優(yōu)化器ORACLE的優(yōu)化器共有3種:a. RULE (基於規(guī)則) b. COST (基於成本) c. CHOOSE (選擇性)設(shè)置缺省的優(yōu)化器,可以通過對(duì)init.ora文件中OPTIMIZER_MODE參數(shù)的各種聲明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS. 你當(dāng)然也在SQL句級(jí)或是會(huì)話(session)級(jí)對(duì)其進(jìn)行覆蓋.爲(wèi)了使用基於成本的優(yōu)化器(CBO, Cost-Based Optimizer) , 你必須經(jīng)常運(yùn)行analyze命令,以增加資料庫中的物件統(tǒng)計(jì)資訊(object statistics)的準(zhǔn)確性.假如資料庫的優(yōu)化器模式設(shè)置爲(wèi)選擇性(CHOOSE),那麼實(shí)際的優(yōu)化器模式將和是否運(yùn)行過analyze命令有關(guān).假如table已經(jīng)被analyze過, 優(yōu)化器模式將自動(dòng)成爲(wèi)CBO , 反之,資料庫將採用RULE形式的優(yōu)化器.在缺省情況下,ORACLE採用CHOOSE優(yōu)化器, 爲(wèi)了避免那些不必要的全表掃描(full table scan) ,你必須儘量避免使用CHOOSE優(yōu)化器,而直接採用基於規(guī)則或者基於成本的優(yōu)化器.2. 訪問Table的方式ORACLE 採用兩種訪問表中記錄的方式:a. 全表掃描全表掃描就是順序地訪問表中每條記錄. ORACLE採用一次讀入多個(gè)資料塊(database block)的方式優(yōu)化全表掃描.b. 通過ROWID訪問表你可以採用基於ROWID的訪問方式情況,提高訪問表的效率, ,ROWID包含了表中記錄的物理位置資訊..ORACLE採用索引(INDEX)實(shí)現(xiàn)了資料和存放資料的物理位置(ROWID)之間的聯(lián)繫.通常索引提供了快速訪問ROWID的方法,因此那些基於索引列的查詢就可以得到性能上的提高.3. 共用SQL語句爲(wèi)了不重復(fù)解析相同的SQL語句,在第一次解析之後, ORACLE將SQL語句存放在記憶體中.這塊位於系統(tǒng)全局區(qū)域SGA(systemglobal area)的共用池(shared buffer pool)中的記憶體可以被所有的資料庫用戶共用.因此,當(dāng)你執(zhí)行一個(gè)SQL語句(有時(shí)被稱爲(wèi)一個(gè)游標(biāo))時(shí),假如它和之前的執(zhí)行過的語句完全相同, ORACLE就能很快獲得已經(jīng)被解析的語句以及最好的執(zhí)行路徑. ORACLE的這個(gè)功能大大地提高了SQL的執(zhí)行性能並節(jié)省了記憶體的使用.可惜的是ORACLE只對(duì)簡單的表提供高速緩衝(cache buffering) ,這個(gè)功能並不適用於多表連接查詢.資料庫治理員必須在init.ora中爲(wèi)這個(gè)區(qū)域設(shè)置合適的參數(shù),當(dāng)這個(gè)記憶體區(qū)域越大,就可以保留更多的語句,當(dāng)然被共用的可能性也就越大了.當(dāng)你向ORACLE 提交一個(gè)SQL語句,ORACLE會(huì)首先在這塊記憶體中查找相同的語句.這裏需要注明的是,ORACLE對(duì)兩者採取的是一種嚴(yán)格匹配,要達(dá)成共用,SQL語句必須完全相同(包括空格,換行等).共用的語句必須滿足三個(gè)條件:A. 字元級(jí)的比較:當(dāng)前被執(zhí)行的語句和共用池中的語句必須完全相同.例如:SELECT * FROM EMP;和下列每一個(gè)都不同SELECT * from EMP;Select * From Emp;SELECT * FROM EMP;B. 兩個(gè)語句所指的物件必須完全相同:例如:用戶 物件名 如何訪問Jack sal_limit private synonymWork_city public synonymPlant_detail public synonymJill sal_limit private synonymWork_city public synonymPlant_detail table owner考慮一下下列SQL語句能否在這兩個(gè)用戶之間共用.SQL能否共用原因select max(sal_cap) from sal_limit;不能每個(gè)用戶都有一個(gè)private synonym - sal_limit , 它們是不同的物件select count(*0 from work_city where sdesc like 'NEW%';能兩個(gè)用戶訪問相同的物件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. 兩個(gè)SQL語句中必須使用相同的名字的綁定變數(shù)(bind variables)例如：第一組的兩個(gè)SQL語句是相同的(可以共用),而第二組中的兩個(gè)語句是不同的(即使在運(yùn)行時(shí),賦於不同的綁定變數(shù)相同的值)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. 選擇最有效率的表名順序(只在基於規(guī)則的優(yōu)化器中有效)ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫在最後的表(基礎(chǔ)表 drivingtable)將被最先處理. 在FROM子句中包含多個(gè)表的情況下,你必須選擇記錄條數(shù)最少的表作爲(wèi)基礎(chǔ)表.當(dāng)ORACLE處理多個(gè)表時(shí),會(huì)運(yùn)用排序及合併的方式連接它們.首先,掃描第一個(gè)表(FROM子句中最後的那個(gè)表)並對(duì)記錄進(jìn)行派序,然後掃描第二個(gè)表(FROM子句中最後第二個(gè)表),最後將所有從第二個(gè)表中檢索出的記錄與第一個(gè)表中合適記錄進(jìn)行合併.例如:表 TAB1 16,384 條記錄表 TAB2 1 條記錄選擇TAB2作爲(wèi)基礎(chǔ)表 (最好的方法)select count(*) from tab1,tab2 執(zhí)行時(shí)間0.96秒選擇TAB2作爲(wèi)基礎(chǔ)表 (不佳的方法)select count(*) from tab2,tab1 執(zhí)行時(shí)間26.09秒假如有3個(gè)以上的表連接查詢, 那就需要選擇交叉表(intersection table)作爲(wèi)基礎(chǔ)表,交叉表是指那個(gè)被其他表所引用的表.例如: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子句,根據(jù)這個(gè)原理,表之間的連接必須寫在其他WHERE條件之前,那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾.例如:(低效,執(zhí)行時(shí)間156.3秒)SELECT …FROM EMP EWHERE SAL > 50000AND JOB = ‘MANAGER’AND 25 < (SELECT COUNT(*) FROM EMPWHERE MGR=E.EMPNO);(高效,執(zhí)行時(shí)間10.6秒)SELECT …FROM EMP EWHERE 25 < (SELECT COUNT(*) FROM EMPWHERE MGR=E.EMPNO)AND SAL > 50000AND JOB = ‘MANAGER’;6. SELECT子句中避免使用 ‘ * ‘當(dāng)你想在SELECT子句中列出所有的COLUMN時(shí),使用動(dòng)態(tài)SQL列引用 ‘*’是一個(gè)方便的方法.不幸的是,這是一個(gè)非常低效的方法. 實(shí)際上,ORACLE在解析的過程中, 會(huì)將’*’ 依次轉(zhuǎn)換成所有的列名,這個(gè)工作是通過查詢資料字典完成的, 這意味著將耗費(fèi)更多的時(shí)間.7. 減少訪問資料庫的次數(shù)當(dāng)執(zhí)行每條SQL語句時(shí), ORACLE在內(nèi)部執(zhí)行了許多工作: 解析SQL語句, 估算索引的利用率, 綁定變數(shù) , 讀資料塊等等.由此可見, 減少訪問資料庫的次數(shù) , 就能實(shí)際上減少ORACLE的工作量.例如,以下有三種方法可以檢索出雇員號(hào)等於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中重新設(shè)置ARRAYSIZE參數(shù), 可以增加每次資料庫訪問的檢索資料量,建議值爲(wèi)2008. 使用DECODE函數(shù)來減少處理時(shí)間使用DECODE函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表.例如: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函數(shù)高效地得到相同結(jié)果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函數(shù)也可以運(yùn)用於GROUP BY 和ORDER BY子句中.9. 整合簡單,無關(guān)聯(lián)的資料庫訪問假如你有幾個(gè)簡單的資料庫查詢語句,你可以把它們整合到一個(gè)查詢中(即使它們之間沒有關(guān)係)例如:SELECT NAMEFROM EMPWHERE EMP_NO = 1234;SELECT NAMEFROM DPTWHERE DPT_NO = 10 ;SELECT NAMEFROM CATWHERE CAT_TYPE = ‘RD’;上面的3個(gè)查詢可以被合併成一個(gè):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’;(譯者按: 雖然採取這種方法,效率得到提高,但是程式的可讀性大大降低,所以讀者 還是要權(quán)衡之間的利弊)10. 刪除重復(fù)記錄最高效的刪除重復(fù)記錄方法 ( 因爲(wèi)使用了ROWID)DELETE FROM EMP EWHERE E.ROWID > (SELECT MIN(X.ROWID)FROM EMP XWHERE X.EMP_NO = E.EMP_NO);11. 用TRUNCATE替代DELETE當(dāng)刪除表中的記錄時(shí),在通常情況下, 回滾段(rollback segments ) 用來存放可以被恢復(fù)的資訊.假如你沒有COMMIT事務(wù),ORACLE會(huì)將資料恢復(fù)到刪除之前的狀態(tài)(準(zhǔn)確地說是恢復(fù)到執(zhí)行刪除命令之前的狀況)而當(dāng)運(yùn)用TRUNCATE時(shí),回滾段不再存放任何可被恢復(fù)的資訊.當(dāng)命令運(yùn)行後,資料不能被恢復(fù).因此很少的資源被調(diào)用,執(zhí)行時(shí)間也會(huì)很短.(譯者按: TRUNCATE只在刪除全表適用,TRUNCATE是DDL不是DML)12. 儘量多使用COMMIT只要有可能,在程式中儘量多使用COMMIT, 這樣程式的性能得到提高,需求也會(huì)因爲(wèi)COMMIT所釋放的資源而減少:COMMIT所釋放的資源:a. 回滾段上用於恢復(fù)資料的資訊.b. 被程式語句獲得的鎖c. redo log buffer 中的空間d. ORACLE爲(wèi)治理上述3種資源中的內(nèi)部花費(fèi)(譯者按: 在使用COMMIT時(shí)必須要注重到事務(wù)的完整性,現(xiàn)實(shí)中效率和事務(wù)完整性往往是魚和熊掌不可得兼)13. 計(jì)算記錄條數(shù)和一般的觀點(diǎn)相反, count(*) 比count(1)稍快 , 當(dāng)然假如可以通過索引檢索,對(duì)索引列的計(jì)數(shù)仍舊是最快的. 例如COUNT(EMPNO)(譯者按: 在CSDN論壇中,曾經(jīng)對(duì)此有過相當(dāng)熱烈的討論,作者的觀點(diǎn)並不十分準(zhǔn)確,通過實(shí)際的測試,上述三種方法並沒有顯著的性能差別)14. 用Where子句替換HAVING子句避免使用HAVING子句, HAVING 只會(huì)在檢索出所有記錄之後才對(duì)結(jié)果集進(jìn)行過濾. 這個(gè)處理需要排序,總計(jì)等操作.假如能通過WHERE子句限制記錄的數(shù)目,那就能減少這方面的開銷.例如:低效: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 中的條件一般用於對(duì)一些集合函數(shù)的比較,如COUNT() 等等.除此而外,一般的條件應(yīng)該寫在WHERE子句中)15. 減少對(duì)表的查詢?cè)诤凶硬樵兊腟QL語句中,要特別注重減少對(duì)表的查詢.例如:低效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 多個(gè)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. 通過內(nèi)部函數(shù)提高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;通過調(diào)用下面的函數(shù)可以提高效率.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;(譯者按: 經(jīng)常在論壇中看到如 ’能不能用一個(gè)SQL寫出….’ 的貼子, 殊不知複雜的SQL往往犧牲了執(zhí)行效率.能夠把握上面的運(yùn)用函數(shù)解決問題的方法在實(shí)際工作中是非常有意義的)17. 使用表的別名(Alias)當(dāng)在SQL語句中連接多個(gè)表時(shí),請(qǐng)使用表的別名並把別名字首於每個(gè)Column上.這樣一來,就可以減少解析的時(shí)間並減少那些由Column歧義引起的語法錯(cuò)誤. (譯者注:Column歧義指的是由於SQL中不同的表具有相同的Column名,當(dāng)SQL語句中出現(xiàn)這個(gè)Column時(shí),SQL解析器無法判斷這個(gè)Column的歸屬)18. 用EXISTS替代IN在許多基於基礎(chǔ)表的查詢中,爲(wèi)了滿足一個(gè)條件,往往需要對(duì)另一個(gè)表進(jìn)行聯(lián)接.在這種情況下, 使用EXISTS(或NOTEXISTS)通常將提高查詢的效率.低效:SELECT *FROM EMP (基礎(chǔ)表)WHERE EMPNO > 0AND DEPTNO IN (SELECT DEPTNOFROM DEPTWHERE LOC = ‘MELB’)高效:SELECT *FROM EMP (基礎(chǔ)表)WHERE EMPNO > 0AND EXISTS (SELECT ‘X’FROM DEPTWHERE DEPT.DEPTNO = EMP.DEPTNOAND LOC = ‘MELB’)(譯者按: 相對(duì)來說,用NOT EXISTS替換NOT IN 將更顯著地提高效率,下一節(jié)中將指出)19. 用NOT EXISTS替代NOT IN在子查詢中,NOT IN子句將執(zhí)行一個(gè)內(nèi)部的排序和合併. 無論在哪種情況下,NOT IN都是最低效的(因爲(wèi)它對(duì)子查詢中的表執(zhí)行了一個(gè)全表遍曆). 爲(wèi)了避免使用NOT IN ,我們可以把它改寫成外連接(Outer Joins)或NOTEXISTS.例如:SELECT …FROM EMPWHERE DEPT_NO NOT IN (SELECT DEPT_NOFROM DEPTWHERE DEPT_CAT=’A’);爲(wèi)了提高效率.改寫爲(wèi):(方法一: 高效)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的情況下,前者的執(zhí)行路徑包括FILTER,後者使用NESTED LOOP)