試談Oracle的性能優(yōu)化課件

Oracle的性能優(yōu)化Oracle的性能優(yōu)化1本章學(xué)習(xí)目標(biāo)

本章將介紹優(yōu)化和調(diào)整Oracle數(shù)據(jù)庫(kù)系統(tǒng)的一些相關(guān)命令和方法。第九章Oracle的性能優(yōu)化本章學(xué)習(xí)目標(biāo)第九章Oracle的性能優(yōu)化2本章內(nèi)容安排

9.1數(shù)據(jù)庫(kù)性能優(yōu)化概述9.2SQL語(yǔ)句的優(yōu)化

9.3Oracle運(yùn)行環(huán)境的優(yōu)化

9.4并發(fā)事件處理

9.5數(shù)據(jù)完整性9.7常見(jiàn)問(wèn)題處理

本章內(nèi)容安排9.1數(shù)據(jù)庫(kù)性能優(yōu)化概述39.1數(shù)據(jù)庫(kù)性能優(yōu)化概述9.1.1數(shù)據(jù)庫(kù)性能優(yōu)化的內(nèi)容

9.1.2不同類型系統(tǒng)的優(yōu)化9.1數(shù)據(jù)庫(kù)性能優(yōu)化概述9.1.1數(shù)據(jù)庫(kù)性能優(yōu)化的內(nèi)4（1）調(diào)整數(shù)據(jù)結(jié)構(gòu)的設(shè)計(jì)。（2）調(diào)整應(yīng)用程序結(jié)構(gòu)設(shè)計(jì)。（3）調(diào)整數(shù)據(jù)庫(kù)SQL語(yǔ)句。（4）調(diào)整服務(wù)器內(nèi)存分配。（5）調(diào)整硬盤(pán)I/O，這一步是在信息系統(tǒng)開(kāi)發(fā)之前完成的。（6）調(diào)整操作系統(tǒng)參數(shù)。9.1.1數(shù)據(jù)庫(kù)性能優(yōu)化的內(nèi)容（1）調(diào)整數(shù)據(jù)結(jié)構(gòu)的設(shè)計(jì)。9.1.1數(shù)據(jù)庫(kù)性能優(yōu)化的內(nèi)容51．在線事務(wù)處理信息系統(tǒng)（OLTP）這種類型的信息系統(tǒng)一般需要有大量的Insert、Update操作。OLTP系統(tǒng)需要保證數(shù)據(jù)庫(kù)的并發(fā)性、可靠性和最終用戶的速度，這類系統(tǒng)使用的Oracle數(shù)據(jù)庫(kù)需要主要考慮下述因素或參數(shù)：（1）數(shù)據(jù)庫(kù)回滾段是否足夠？（2）是否需要建立Oracle數(shù)據(jù)庫(kù)索引、聚集、散列？（3）系統(tǒng)全局區(qū)（SGA）大小是否足夠？（4）SQL語(yǔ)句是否高效？9.1.2不同類型系統(tǒng)的優(yōu)化1．在線事務(wù)處理信息系統(tǒng)（OLTP）9.1.2不同類型系62．?dāng)?shù)據(jù)倉(cāng)庫(kù)系統(tǒng)（DataWarehousing）這種信息系統(tǒng)的主要任務(wù)是從Oracle的海量數(shù)據(jù)中進(jìn)行查詢，得到數(shù)據(jù)之間的某些規(guī)律。數(shù)據(jù)庫(kù)管理員需要為這種類型的Oracle數(shù)據(jù)庫(kù)著重考慮下述因素或參數(shù)：（1）是否采用B*-索引或者bitmap索引？（2）是否采用并行SQL查詢以提高查詢效率？（3）是否采用PL/SQL函數(shù)編寫(xiě)存儲(chǔ)過(guò)程？（4）是否有必要建立并行數(shù)據(jù)庫(kù)，來(lái)提高數(shù)據(jù)庫(kù)的查詢效率。2．?dāng)?shù)據(jù)倉(cāng)庫(kù)系統(tǒng)（DataWarehousing）79.2.2SQL語(yǔ)句優(yōu)化的具體方法

9.2.1SQL語(yǔ)句的優(yōu)化規(guī)則

9.2SQL語(yǔ)句的優(yōu)化9.2.2SQL語(yǔ)句優(yōu)化的具體方法9.2.1SQL89.2.1SQL語(yǔ)句的優(yōu)化規(guī)則

（1）去掉不必要的大表、全表掃描。不必要的大表、全表掃描會(huì)造成不必要的輸入輸出，而且還會(huì)拖垮整個(gè)數(shù)據(jù)庫(kù)；（2）檢查優(yōu)化索引的使用這對(duì)于提高查詢速度來(lái)說(shuō)非常重要；（3）檢查子查詢，考慮SQL子查詢是否可以用簡(jiǎn)單連接的方式進(jìn)行重新書(shū)寫(xiě)；（4）調(diào)整PCTFREE和PCTUSED等存儲(chǔ)參數(shù)優(yōu)化插入、更新或者刪除等操作；（5）考慮數(shù)據(jù)庫(kù)的優(yōu)化器；（6）考慮數(shù)據(jù)表的全表掃描和在多個(gè)CPU的情況下考慮并行查詢。9.2.1SQL語(yǔ)句的優(yōu)化規(guī)則（1）去掉不必要的大表、91．索引的使用2.SQL語(yǔ)句排序優(yōu)化3.選擇聯(lián)合查詢的聯(lián)合次序

9.2.2SQL語(yǔ)句優(yōu)化的具體方法4.SQL子查詢的調(diào)整1．索引的使用2.SQL語(yǔ)句排序優(yōu)化3.選擇聯(lián)合查詢的聯(lián)合10

1、索引的使用（1）盡量使用索引是全表掃描還是索引范圍掃描主要考慮SQL的查詢速度問(wèn)題。試比較下面兩條SQL語(yǔ)句：①語(yǔ)句A：SELECTdname,deptnoFROMdeptWHEREdeptnoNOTIN（SELECTdeptnoFROMemp）;②語(yǔ)句B：SELECTdname,deptnoFROMdeptWHERENOTEXISTS（SELECTdeptnoFROMempWHEREdept.deptno=emp.deptno）;1、索引的使用11

（2）索引不起作用的情況①存在數(shù)據(jù)類型隱形轉(zhuǎn)換②列上有數(shù)學(xué)運(yùn)算③使用不等于（<>）運(yùn)算④使用substr字符串函數(shù)⑤‘%’通配符在第一個(gè)字符⑥字符串連接（||）（2）索引不起作用的情況12

（3）函數(shù)的索引例如，日期類型是經(jīng)常用到的，而且在SQL語(yǔ)句中會(huì)使用to_char函數(shù)以查詢具體的的范圍日期。如：select*fromstaff_memberwhereTO_CHAR（birth_day,’YYYY’）=’2003’;可以建立基于函數(shù)的索引如：CREATEINDEXInd_emp_birthONstaff_member（to_char（（birth_day,’YYYY’））;（3）函數(shù)的索引132.SQL語(yǔ)句排序優(yōu)化排序發(fā)生的情況如下：SQL中包含groupby子句SQL中包含orderby子句SQL中包含distinct子句SQL中包含minus或union操作2.SQL語(yǔ)句排序優(yōu)化143．選擇聯(lián)合查詢的聯(lián)合次序聯(lián)合查詢中如涉及到多個(gè)表的字段關(guān)聯(lián)及查詢，其SQL查詢語(yǔ)句聯(lián)合次序的不同寫(xiě)法，會(huì)導(dǎo)致語(yǔ)句對(duì)各表具體操作的步驟有不同的次序，所以雖然執(zhí)行結(jié)果相同，但執(zhí)行效率卻不同。

3．選擇聯(lián)合查詢的聯(lián)合次序15

４．SQL子查詢的調(diào)整（1）關(guān)聯(lián)子查詢和非關(guān)聯(lián)子查詢非關(guān)聯(lián)查詢的開(kāi)銷——非關(guān)聯(lián)查詢時(shí)子查詢只會(huì)執(zhí)行一次，而且結(jié)果是排序好的，并保存在一個(gè)Oracle的臨時(shí)段中，其中的每一個(gè)記錄在返回時(shí)都會(huì)被父查詢所引用。在子查詢返回大量的記錄的情況下，將這些結(jié)果集排序，以及將臨時(shí)數(shù)據(jù)段進(jìn)行排序會(huì)增加大量的系統(tǒng)開(kāi)銷。關(guān)聯(lián)查詢的開(kāi)銷——對(duì)返回到父查詢的記錄來(lái)說(shuō)，子查詢會(huì)每行執(zhí)行一次。因此，必須保證任何可能的時(shí)候子查詢用到索引。４．SQL子查詢的調(diào)整16（2）在子查詢中慎重使用IN或者NOTIN語(yǔ)句在子查詢中慎重使用IN或者NOTIN語(yǔ)句，使用where（NOT）exists的效果要好的多。①帶IN的關(guān)聯(lián)子查詢是多余的，因?yàn)镮N子句和子查詢中相關(guān)的操作的功能是一樣的。②為非關(guān)聯(lián)子查詢指定EXISTS子句是不適當(dāng)?shù)模驗(yàn)檫@樣會(huì)產(chǎn)生笛卡爾乘積。③盡量不要使用NOTIN子句。（2）在子查詢中慎重使用IN或者NOTIN語(yǔ)句17（3）慎重使用視圖的聯(lián)合查詢慎重使用視圖的聯(lián)合查詢，尤其是比較復(fù)雜的視圖之間的聯(lián)合查詢。一般對(duì)視圖的查詢最好都分解為對(duì)數(shù)據(jù)表的直接查詢效果要好一些?？梢栽趨?shù)文件中設(shè)置SHARED_POOL_RESERVED_SIZE參數(shù)，這個(gè)參數(shù)在SGA共享池中保留一個(gè)連續(xù)的內(nèi)存空間，連續(xù)的內(nèi)存空間有益于存放大的SQL程序包。（3）慎重使用視圖的聯(lián)合查詢189.3.2物理I/O的調(diào)整

9.3.3CPU的優(yōu)化調(diào)整

9.3.4網(wǎng)絡(luò)配置的優(yōu)化

9.3.1內(nèi)存結(jié)構(gòu)的調(diào)整

9.3Oracle運(yùn)行環(huán)境的優(yōu)化

9.3.5Oracle碎片整理

9.3.6Oracle系統(tǒng)參數(shù)的調(diào)整

9.3.2物理I/O的調(diào)整9.3.3CPU的優(yōu)化調(diào)19內(nèi)存參數(shù)的調(diào)整主要是指Oracle數(shù)據(jù)庫(kù)的系統(tǒng)全局區(qū)（SGA）的調(diào)整。SGA主要由三部分構(gòu)成：共享池、數(shù)據(jù)緩沖區(qū)、日志緩沖區(qū)。1．共享池共享池由兩部分構(gòu)成：共享SQL區(qū)和數(shù)據(jù)字典緩沖區(qū)。共享SQL區(qū)是存放用戶SQL命令的區(qū)域，數(shù)據(jù)字典緩沖區(qū)存放數(shù)據(jù)庫(kù)運(yùn)行的動(dòng)態(tài)信息。9.3.1內(nèi)存結(jié)構(gòu)的調(diào)整內(nèi)存參數(shù)的調(diào)整主要是指Oracle數(shù)據(jù)庫(kù)的系統(tǒng)全20（1）數(shù)據(jù)庫(kù)管理員通過(guò)執(zhí)行下述語(yǔ)句，來(lái)查看共享SQL區(qū)的使用率。select（sum（pins-reloads））/sum（pins）"LibCache"fromv$librarycache;共享SQL區(qū)的使用率應(yīng)該在90％以上，否則需要增加共享池的大小。（2）數(shù)據(jù)庫(kù)管理員可以執(zhí)行下述語(yǔ)句，查看數(shù)據(jù)字典緩沖區(qū)的使用率。select（sum（-getmisses-usage-fixed））/sum（gets）"RowCache"fromv$rowcache;數(shù)據(jù)字典緩沖區(qū)的使用率也應(yīng)該在90％以上，否則需要增加共享池的大小。（1）數(shù)據(jù)庫(kù)管理員通過(guò)執(zhí)行下述語(yǔ)句，來(lái)查看共享SQL區(qū)的使用212．?dāng)?shù)據(jù)緩沖區(qū)數(shù)據(jù)庫(kù)管理員可以通過(guò)下述語(yǔ)句，來(lái)查看數(shù)據(jù)庫(kù)數(shù)據(jù)緩沖區(qū)的使用情況。SELECTname，F(xiàn)ROMv$sysstatWHEREnameIN（'dbblockgets','consistentgets','physicalreads'）;根據(jù)查詢出來(lái)的結(jié)果可以計(jì)算出數(shù)據(jù)緩沖區(qū)的使用命中率：數(shù)據(jù)緩沖區(qū)的使用命中率＝1–(physicalreads/(dbblockgets+consistentgets))這個(gè)命中率應(yīng)該在90％以上，否則需要增加數(shù)據(jù)緩沖區(qū)的大小。2．?dāng)?shù)據(jù)緩沖區(qū)223．日志緩沖區(qū)數(shù)據(jù)庫(kù)管理員可以通過(guò)執(zhí)行下述語(yǔ)句，查看日志緩沖區(qū)的使用情況。selectname,valuefromv$sysstatwherenamein('redoentries','redologspacerequests');根據(jù)查詢出的結(jié)果可以計(jì)算出日志緩沖區(qū)的申請(qǐng)失敗率：申請(qǐng)失敗率＝requests/entries申請(qǐng)失敗率應(yīng)該接近于0，否則說(shuō)明日志緩沖區(qū)開(kāi)設(shè)太小，需要增加Oracle數(shù)據(jù)庫(kù)的日志緩沖區(qū)。3．日志緩沖區(qū)23（1）在磁盤(pán)上建立數(shù)據(jù)文件前首先運(yùn)行磁盤(pán)碎片整理程序?yàn)榱税踩卣泶疟P(pán)碎片，需關(guān)閉打開(kāi)數(shù)據(jù)文件的實(shí)例，并且停止服務(wù)。如果有足夠的連續(xù)磁盤(pán)空間建立數(shù)據(jù)文件，那么就很容易避免數(shù)據(jù)文件產(chǎn)生碎片。（2）不要使用磁盤(pán)壓縮Oracle數(shù)據(jù)文件不支持磁盤(pán)壓縮。（3）不要使用磁盤(pán)加密加密象磁盤(pán)壓縮一樣增加了一個(gè)處理層，降低磁盤(pán)讀寫(xiě)速度。如果擔(dān)心自己的數(shù)據(jù)可能泄密，可以使用dbms_obfuscation包和labelsecurity選擇性地加密數(shù)據(jù)的敏感部分。9.3.2物理I/O的調(diào)整（1）在磁盤(pán)上建立數(shù)據(jù)文件前首先運(yùn)行磁盤(pán)碎片整理程序9.3.24（5）使用RAIDRAID的使用應(yīng)注意：①選擇硬件RAID超過(guò)軟件RAID；②日志文件不要放在RAID5卷上，因?yàn)镽AID5讀性能高而寫(xiě)性能差；③把日志文件和歸檔日志放在與控制文件和數(shù)據(jù)文件分離的磁盤(pán)控制系統(tǒng)上。（6）分離頁(yè)面交換文件到多個(gè)磁盤(pán)物理卷跨越至少兩個(gè)磁盤(pán)建立兩個(gè)頁(yè)面文件?？梢越⑺膫€(gè)頁(yè)面文件并在性能上受益，確保所有頁(yè)面文件的大小之和至少是物理內(nèi)存的兩倍。（5）使用RAID251．查看CPU的使用情況使用操作命令可以看到CPU的使用情況，一般UNIX操作系統(tǒng)的服務(wù)器，可以使用sar–u命令查看CPU的使用率；NT操作系統(tǒng)的服務(wù)器，可以使用NT的性能管理器來(lái)查看CPU的使用率。出現(xiàn)CPU資源不足的情況是很多的：SQL語(yǔ)句的重解析、低效率的SQL語(yǔ)句、鎖沖突都會(huì)引起CPU資源不足。9.3.3CPU的優(yōu)化調(diào)整1．查看CPU的使用情況9.3.3CPU的優(yōu)化調(diào)整262．查看SQL語(yǔ)句的解析情況（1）數(shù)據(jù)庫(kù)管理員可以執(zhí)行下述語(yǔ)句來(lái)查看SQL語(yǔ)句的解析情況：SELECT*FROMV$SYSSTATWHERENAMEIN('parse_time_cpu','parse_time_elapsed','parse_count_hard');這里：①parse_time_cpu：是系統(tǒng)服務(wù)時(shí)間。②parse_time_elapsed：是響應(yīng)時(shí)間。而用戶等待時(shí)間為：waite_time=parse_time_elapsed–parse_time_cpu由此可以得到用戶SQL語(yǔ)句平均解析等待時(shí)間：用戶SQL語(yǔ)句平均解析等待時(shí)間＝waite_time/parse_count2．查看SQL語(yǔ)句的解析情況27（2）數(shù)據(jù)庫(kù)管理員還可以通過(guò)下述語(yǔ)句，查看低效率的SQL語(yǔ)句：SELECTBUFFER_GETS,EXECUTIONS,SQL_TEXTFROMV$SQLAREA;優(yōu)化這些低效率的SQL語(yǔ)句也有助于提高CPU的利用率。（2）數(shù)據(jù)庫(kù)管理員還可以通過(guò)下述語(yǔ)句，查看低效率的SQL語(yǔ)句283．查看Oracle數(shù)據(jù)庫(kù)的沖突情況數(shù)據(jù)庫(kù)管理員可以通過(guò)v$system_event數(shù)據(jù)字典中的“l(fā)atchfree”統(tǒng)計(jì)項(xiàng)查看Oracle數(shù)據(jù)庫(kù)的沖突情況，如果沒(méi)有沖突的話，latchfree查詢出來(lái)沒(méi)有結(jié)果。如果沖突太大的話，數(shù)據(jù)庫(kù)管理員可以降低spin_count參數(shù)值，來(lái)消除高的CPU使用率。3．查看Oracle數(shù)據(jù)庫(kù)的沖突情況294．CPU的優(yōu)化調(diào)整方法一些優(yōu)化CPU使用和配置的具體方法有：（1）取消屏幕保護(hù)。（2）把系統(tǒng)配置為應(yīng)用服務(wù)器。（3）監(jiān)視系統(tǒng)中消耗中斷的硬件。（4）保持最小的安全審計(jì)記錄。（5）在專用服務(wù)器上運(yùn)行Oracle。（6）禁止非必須的服務(wù)。4．CPU的優(yōu)化調(diào)整方法30網(wǎng)絡(luò)配置是性能調(diào)整的一項(xiàng)很重要的內(nèi)容，而且很容易隱藏性能瓶頸。（1）配置網(wǎng)卡使用最快速度和有效模式（2）刪除不需要的網(wǎng)絡(luò)協(xié)議（3）優(yōu)化網(wǎng)絡(luò)協(xié)議綁定順序（4）為Oracle禁止或優(yōu)化文件共享9.3.4網(wǎng)絡(luò)配置的優(yōu)化網(wǎng)絡(luò)配置是性能調(diào)整的一項(xiàng)很重要的內(nèi)容，而且很311．碎片是如何產(chǎn)生的2．碎片對(duì)系統(tǒng)的影響（1）導(dǎo)致系統(tǒng)性能減弱（2）浪費(fèi)大量的表空間9.3.5Oracle碎片整理1．碎片是如何產(chǎn)生的9.3.5Oracle碎片整理323．自由范圍的碎片計(jì)算用fsfi——freespacefragmentationindex（自由空間碎片索引）值來(lái)直觀體現(xiàn)：fsfi=100*sqrt(max(extent)/sum(extents))*1/sqrt(sqrt(count(extents)))3．自由范圍的碎片計(jì)算334．自由范圍的碎片整理可以將表空間的缺省存儲(chǔ)參數(shù)pctincrease改為非0。一般將其設(shè)為1，如：altertablespacetempdefaultstorage(pctincrease1);這樣smon便會(huì)將自由范圍自動(dòng)合并，達(dá)到碎片整理的目的。也可以采用如下語(yǔ)句，通過(guò)手工合并自由范圍來(lái)達(dá)到碎片整理的目的。altertablespacetempcoalesce;4．自由范圍的碎片整理345．段的碎片整理段由范圍組成，在有些情況下，有必要對(duì)段的碎片進(jìn)行整理。要查看段的有關(guān)信息，可查看數(shù)據(jù)字典DBA_segments，范圍的信息可查看數(shù)據(jù)字典DBA_extents。如果段的碎片過(guò)多，將其數(shù)據(jù)壓縮到一個(gè)范圍的最簡(jiǎn)單方法便是用正確的存儲(chǔ)參數(shù)將這個(gè)段重建，然后將舊表中的數(shù)據(jù)插入到新表，同時(shí)刪除舊表。這個(gè)過(guò)程可以用import/export（輸入/輸出）工具來(lái)完成。export（）命令有一個(gè)（壓縮）標(biāo)志，這個(gè)標(biāo)志在讀表時(shí)會(huì)引發(fā)export確定該表所分配的物理空間量，它會(huì)向輸出轉(zhuǎn)儲(chǔ)文件寫(xiě)入一個(gè)新的初始化存儲(chǔ)參數(shù)，等于全部所分配空間。若這個(gè)表關(guān)閉，則使用import（）工具重新生成。這樣，它的數(shù)據(jù)會(huì)放入一個(gè)新的、較大的初始段中。例如：expuser/passwordfile=exp.dmpcompress=ygrants=yindexes=ytables=(table1,table2);若輸出成功，則從庫(kù)中刪除已輸出的表，然后從輸出轉(zhuǎn)儲(chǔ)文件中輸入表：impuser/passwordfile=exp.dmpcommit=ybuffer=64000full=y;這種方法可用于整個(gè)數(shù)據(jù)庫(kù)。另外，應(yīng)該定期shutdowndatabase，從而清理momery碎片。5．段的碎片整理359.3.6Oracle系統(tǒng)參數(shù)的調(diào)整

1．SharedPoolandLibraryCachePerformanceTuning（共享池和LibraryCache）共享池調(diào)整的技巧主要有：（1）刷共享池刷（Flush）共享池可以使小塊的內(nèi)存合并為大塊的內(nèi)存。當(dāng)共享池的碎片過(guò)多時(shí)，能夠暫時(shí)恢復(fù)性能。刷共享池可以使用語(yǔ)句：altersystemflushshared_pool;（2）綁定變量9.3.6Oracle系統(tǒng)參數(shù)的調(diào)整1．Shared362．BufferCachePerformanceTuning（數(shù)據(jù)庫(kù)緩存調(diào)整）從緩存調(diào)整的角度看，應(yīng)力求避免以下的問(wèn)題：（1）“緩存的最近最少使用（LRN）鏈”（cachebuffersLRUchain）的加鎖競(jìng)爭(zhēng)；（2）“平均寫(xiě)隊(duì)列”（AverageWriteQueue）長(zhǎng)度過(guò)大；（3）過(guò)多時(shí)間花在等待“寫(xiě)完畢等待上”（writecompletewaits）；（4）過(guò)多時(shí)間花在等待“緩沖釋放等待”上（freebufferwaits）。2．BufferCachePerformanceTun373．LatchContention（加鎖或插銷競(jìng)爭(zhēng)）插銷加鎖是SGA中保護(hù)共享數(shù)據(jù)結(jié)構(gòu)的低層的串行化機(jī)制。插銷latch是一類可以非常快的獲得和釋放的鎖。插銷鎖的實(shí)現(xiàn)是依賴于操作系統(tǒng)的，尤其在關(guān)于一個(gè)進(jìn)程是否會(huì)等待一個(gè)鎖，和等多久方面。有如下的鎖（插銷）需要調(diào)整：（1）RedoCopy/AllocationLatch：重寫(xiě)日志的復(fù)制/分配插銷（2）SharedPoolLatch：共享池的插銷（3）LibraryCacheLatch：LibraryCache插銷3．LatchContention（加鎖或插銷競(jìng)爭(zhēng)）384．RedoLogBufferPerformanceTuning（重寫(xiě)日志緩沖的調(diào)整）LGWR將重寫(xiě)日志緩沖中的重寫(xiě)項(xiàng)寫(xiě)到重寫(xiě)日志文件中。一旦LGWR將這些項(xiàng)復(fù)制到重寫(xiě)日志文件中，用戶進(jìn)程就可以重寫(xiě)這些項(xiàng)。統(tǒng)計(jì)項(xiàng)目“redologspacerequests”反映了用戶進(jìn)程等待重寫(xiě)日志緩沖中空間的時(shí)間的數(shù)字。（1）設(shè)置重寫(xiě)日志大小的提示：“redologspacerequests”的值應(yīng)該接近0。（2）設(shè)定合適的重寫(xiě)日志的大小，建議每15-30分鐘進(jìn)行一次重寫(xiě)日志的切換。4．RedoLogBufferPerformance395．QueryPerformanceTuning（查詢效率的調(diào)整）如果查詢運(yùn)行得很慢，請(qǐng)考慮以下這些方面：（1）希望這個(gè)查詢運(yùn)行的有多快以及有理由這樣要求嗎？（2）優(yōu)化模式OPTIMIZER_MODE設(shè)為何值？（3）查詢涉及的索引都是有效的嗎？（4）在數(shù)據(jù)庫(kù)中有沒(méi)有其他的長(zhǎng)時(shí)間運(yùn)行的查詢（大查詢）。（5）表和索引上有統(tǒng)計(jì)信息嗎？（6）統(tǒng)計(jì)信息是被計(jì)算出來(lái)的還是被估計(jì)出來(lái)的？對(duì)于查詢的性能調(diào)整有兩個(gè)主要的調(diào)試工具：TKPROF和AUTOTRACE。5．QueryPerformanceTuning（查詢效406．TemporaryTablespacePerformanceTuning（臨時(shí)表空間的調(diào)整）

臨時(shí)表空間的調(diào)整的技巧如下：如果即使在穩(wěn)定的狀態(tài)下也存在很多的排序擴(kuò)展鎖（SortExtentPoollatch）的競(jìng)爭(zhēng)，應(yīng)該通過(guò)修改臨時(shí)表空間的DEFAULTSTORAGE子句的NEXT值來(lái)增大擴(kuò)展塊的大小。如果存在很多的排序擴(kuò)展鎖（SortExtentPoollatch）的競(jìng)爭(zhēng)并且這種等待是由于過(guò)多的并發(fā)的排序造成的，應(yīng)該增大SORT_AREA_SIZE參數(shù)的大小，以使更多的排序能保存在內(nèi)存中。建議讓擴(kuò)展塊的大小和SORT_AREA_SIZE參數(shù)相同。6．TemporaryTablespacePerform41

9.4.2鎖9.4.1并發(fā)事件的產(chǎn)生

9.4并發(fā)事件處理9.4.2鎖9.4.1并發(fā)事件的產(chǎn)生42

數(shù)據(jù)庫(kù)是一個(gè)共享資源，可為多個(gè)應(yīng)用程序所共享。這些程序可串行運(yùn)行，但在許多情況下，可能多個(gè)程序或一個(gè)程序的多個(gè)進(jìn)程并行地運(yùn)行，這就是數(shù)據(jù)庫(kù)的并行操作。在多用戶數(shù)據(jù)庫(kù)環(huán)境中，多個(gè)用戶程序可并行地存取數(shù)據(jù)庫(kù)，如果不對(duì)并發(fā)操作進(jìn)行控制，會(huì)存取不正確的數(shù)據(jù)，或破壞數(shù)據(jù)庫(kù)數(shù)據(jù)的一致性。9.4.1并發(fā)事件的產(chǎn)生數(shù)據(jù)庫(kù)是一個(gè)共享資源，可為多個(gè)應(yīng)用程序所共43（1）當(dāng)一個(gè)用戶正在修改表中數(shù)據(jù)的同時(shí)，另一個(gè)用戶正試圖刪除該表。（2）用戶A正試圖讀取用戶B的某個(gè)事務(wù)中的一些數(shù)據(jù)，在用戶A的事務(wù)開(kāi)始后，該事務(wù)由用戶B修改和提交。用戶A讀取用戶B提交的數(shù)據(jù)。這意味著在同一個(gè)事務(wù)中讀取的數(shù)據(jù)在某個(gè)時(shí)刻不一致。（3）某個(gè)用戶對(duì)數(shù)據(jù)進(jìn)行修改，另一個(gè)用戶在第一個(gè)用戶提交事務(wù)以前對(duì)同一行進(jìn)行修改；因此，第一個(gè)用戶所做的改變丟失了。（4）一個(gè)用戶從另一個(gè)用戶尚未提交的數(shù)據(jù)中讀取數(shù)據(jù)；也就是說(shuō)，在用戶B的改變提交以前，用戶A讀取用戶B正在修改的行。（1）當(dāng)一個(gè)用戶正在修改表中數(shù)據(jù)的同時(shí)，另一個(gè)用戶正試圖刪除449.4.2鎖1．鎖的功能2．鎖的類型3．鎖的模式

4．查看相關(guān)信息

9.4.2鎖1．鎖的功能2．鎖的類型3．鎖的模式4451．鎖的功能（1）數(shù)據(jù)一致性（2）數(shù)據(jù)并行性（3）數(shù)據(jù)完整性1．鎖的功能46Oracle在兩個(gè)不同級(jí)上提供讀一致性：語(yǔ)句級(jí)讀一致性和事務(wù)級(jí)一致性。（1）語(yǔ)句級(jí)讀取一致性（2）事務(wù)級(jí)讀取一致性O(shè)racle在兩個(gè)不同級(jí)上提供讀一致性：語(yǔ)句級(jí)讀一致性和事務(wù)472．鎖的類型Oracle鎖具體分為以下幾類：（1）自動(dòng)鎖與顯示鎖按用戶與系統(tǒng)劃分，可以分為自動(dòng)鎖與顯示鎖。①自動(dòng)鎖：當(dāng)進(jìn)行一項(xiàng)數(shù)據(jù)庫(kù)操作時(shí)，缺省情況下，系統(tǒng)自動(dòng)為此數(shù)據(jù)庫(kù)操作獲得所有有必要的鎖。②顯示鎖：某些情況下，需要用戶顯示的鎖定數(shù)據(jù)庫(kù)操作要用到的數(shù)據(jù)，才能使數(shù)據(jù)庫(kù)操作執(zhí)行得更好，顯示鎖是用戶為數(shù)據(jù)庫(kù)對(duì)象設(shè)定的。2．鎖的類型48（2）共享鎖與排它鎖按鎖級(jí)別劃分，可分為共享鎖與排它鎖。①共享鎖：共享鎖允許相關(guān)資源可以共享，幾個(gè)用戶可同時(shí)讀同一數(shù)據(jù)，幾個(gè)事務(wù)可在同一資源上獲取共享封鎖。共享鎖比排它鎖具有更高的數(shù)據(jù)并行性。但拙劣的事務(wù)設(shè)計(jì)+共享鎖容易造成死鎖或數(shù)據(jù)更新丟失。②排它鎖：事務(wù)設(shè)置排它鎖后，該事務(wù)單獨(dú)獲得此資源，另一事務(wù)不能在此事務(wù)提交之前獲得相同對(duì)象的共享鎖或排它鎖。排它鎖禁止相關(guān)資源的共享，如果一事務(wù)以排它方式封鎖一資源，僅僅該事務(wù)可更改該資源，直至釋放排它封鎖。（2）共享鎖與排它鎖49（3）DML鎖和DDL鎖按操作劃分，可分為DML鎖、DDL鎖。①DML鎖又可以分為：行鎖、表鎖、死鎖。行鎖：當(dāng)事務(wù)執(zhí)行數(shù)據(jù)庫(kù)插入、更新、刪除操作時(shí)，該事務(wù)自動(dòng)獲得操作表中操作行的排它鎖。表鎖：當(dāng)事務(wù)獲得行鎖后，此事務(wù)也將自動(dòng)獲得該行的表鎖（共享鎖），以防止其它事務(wù)進(jìn)行DDL語(yǔ)句影響記錄行的更新。事務(wù)也可以在進(jìn)行過(guò)程中獲得共享鎖或排它鎖，只有當(dāng)事務(wù)顯示使用LOCKTABLE語(yǔ)句顯示的定義一個(gè)排它鎖時(shí)，事務(wù)才會(huì)獲得表上的排它鎖，也可使用LOCKTABLE顯示的定義。死鎖：當(dāng)兩個(gè)事務(wù)需要一組有沖突的鎖，而不能將事務(wù)繼續(xù)下去的話，就出現(xiàn)死鎖。（3）DML鎖和DDL鎖50②DDL鎖又可以分為：排它DDL鎖、共享DDL鎖、分析鎖。排它DDL鎖：創(chuàng)建、修改、刪除一個(gè)數(shù)據(jù)庫(kù)對(duì)象的DDL語(yǔ)句獲得操作對(duì)象的排它鎖。如使用altertable語(yǔ)句時(shí)，為了維護(hù)數(shù)據(jù)的完成性、一致性、合法性，該事務(wù)獲得一排它DDL鎖。共享DDL鎖：需在數(shù)據(jù)庫(kù)對(duì)象之間建立相互依賴關(guān)系的DDL語(yǔ)句通常需共享獲得DDL鎖。如創(chuàng)建一個(gè)包，該包中的過(guò)程與函數(shù)引用了不同的數(shù)據(jù)庫(kù)表，當(dāng)編譯此包時(shí)，該事務(wù)就獲得了引用表的共享DDL鎖。分析鎖：Oracle使用共享池存儲(chǔ)分析與優(yōu)化過(guò)的SQL語(yǔ)句及PL/SQL程序，使運(yùn)行相同語(yǔ)句的應(yīng)用速度更快。一個(gè)在共享池中緩存的對(duì)象獲得它所引用數(shù)據(jù)庫(kù)對(duì)象的分析鎖。分析鎖是一種獨(dú)特的DDL鎖類型，Oracle使用它追蹤共享池對(duì)象及它所引用數(shù)據(jù)庫(kù)對(duì)象之間的依賴關(guān)系。當(dāng)一個(gè)事務(wù)修改或刪除了共享池持有分析鎖的數(shù)據(jù)庫(kù)對(duì)象時(shí)，Oracle使共享池中的對(duì)象作廢，下次在引用這條SQL/PLSQL語(yǔ)句時(shí)，Oracle重新分析編譯此語(yǔ)句。②DDL鎖又可以分為：排它DDL鎖、共享DDL鎖、分析鎖。51（4）內(nèi)部閂鎖內(nèi)部閂鎖是Oracle中的一種特殊鎖，用于順序訪問(wèn)內(nèi)部系統(tǒng)結(jié)構(gòu)。當(dāng)事務(wù)需向緩沖區(qū)寫(xiě)入信息時(shí)，為了使用此塊內(nèi)存區(qū)域，Oracle首先必須取得這塊內(nèi)存區(qū)域的閂鎖，才能向此塊內(nèi)存寫(xiě)入信息。（4）內(nèi)部閂鎖523．鎖的模式（1）模式0：none。（2）模式1：null空。1級(jí)鎖有：Select，有時(shí)會(huì)在v$locked_object出現(xiàn)。（3）模式2：Row-S行共享（RS）：共享表鎖，subshare。2級(jí)鎖有：Selectforupdate、LockForUpdate、LockRowShare。selectforupdate當(dāng)對(duì)話使用forupdate子串打開(kāi)一個(gè)游標(biāo)時(shí)，所有返回集中的數(shù)據(jù)行都將處于行級(jí)（Row-X）獨(dú)占式鎖定，其他對(duì)象只能查詢這些數(shù)據(jù)行，不能進(jìn)行update、delete或selectforupdate操作。3．鎖的模式53（4）模式3：Row-X行獨(dú)占（RX）：用于行的修改，subexclusive。3級(jí)鎖有：Insert、Update、Delete、LockRowExclusive。沒(méi)有commit之前插入同樣的一條記錄會(huì)沒(méi)有反應(yīng)，因?yàn)楹笠粋€(gè)3的鎖會(huì)一直等待上一個(gè)3的鎖，必須釋放掉上一個(gè)才能繼續(xù)工作。（5）模式4：Share共享鎖（S）：阻止其他DML操作，share。4級(jí)鎖有：CreateIndex、LockShare。locked_mode為2、3、4不影響DML（insert、delete、update、select等）操作，但DDL（alter、drop等）操作會(huì)提示錯(cuò)誤。（4）模式3：Row-X行獨(dú)占（RX）：用于行的修改，su54（6）模式5：S/Row-X共享行獨(dú)占（SRX）：阻止其他事務(wù)操作，share/subexclusive。5級(jí)鎖有：LockShareRowExclusive。具體來(lái)講有主外鍵約束時(shí)update/delete...；可能會(huì)產(chǎn)生4，5的鎖。（7）模式6：exclusive獨(dú)占（X）：獨(dú)立訪問(wèn)使用，exclusive。6級(jí)鎖有：Altertable、Droptable、DropIndex、Truncatetable、LockExlusive。數(shù)字越大鎖級(jí)別越高，影響的操作越多。（6）模式5：S/Row-X共享行獨(dú)占（SRX）：阻止其他554．查看相關(guān)信息Oracle在動(dòng)態(tài)狀態(tài)表V$lock中存儲(chǔ)與數(shù)據(jù)庫(kù)中的鎖有關(guān)的所有信息。當(dāng)資源被鎖定時(shí)，可以檢查V$lock表，查看數(shù)據(jù)庫(kù)中的各種情況。4．查看相關(guān)信息569.5數(shù)據(jù)完整性

數(shù)據(jù)完整性是指數(shù)據(jù)的正確性和相容性。數(shù)據(jù)的完整性是為了防止數(shù)據(jù)庫(kù)存在不符合原義的數(shù)據(jù)，防止錯(cuò)誤信息輸入和輸出，即數(shù)據(jù)要遵守由DBA或應(yīng)用開(kāi)發(fā)者所決定的一組預(yù)定義的規(guī)則。9.5數(shù)據(jù)完整性數(shù)據(jù)完整性是指數(shù)據(jù)的正確571．?dāng)?shù)據(jù)完整性的類型Oracle應(yīng)用于關(guān)系數(shù)據(jù)庫(kù)的表的數(shù)據(jù)完整性有下列類型：（1）在插入或修改表的行時(shí)允許不允許包含有空值的列，稱為空與非空規(guī)則。（2）唯一列值規(guī)則，允許插入或修改的表行在該列上的值唯一。（3）引用完整性規(guī)則，同關(guān)系模型定義相符。（4）用戶對(duì)定義的規(guī)則，是復(fù)雜的完整性檢查。1．?dāng)?shù)據(jù)完整性的類型582．完整性約束及其優(yōu)點(diǎn)完整性約束，是對(duì)表的列定義規(guī)則的說(shuō)明性方法。Oracle利用完整性約束機(jī)制防止無(wú)效的數(shù)據(jù)進(jìn)入數(shù)據(jù)庫(kù)的基表，如果任何DML執(zhí)行結(jié)果破壞完整性約束，該語(yǔ)句被回滾并返回上一個(gè)錯(cuò)誤。2．完整性約束及其優(yōu)點(diǎn)59利用完整性約束實(shí)施數(shù)據(jù)完整性規(guī)則有下列優(yōu)點(diǎn)：（1）定義或更改表時(shí)，不需要程序設(shè)計(jì)，便很容易地編寫(xiě)程序并可消除程序性錯(cuò)誤，其功能是由Oracle控制。所以說(shuō)明性完整性約束優(yōu)于應(yīng)用代碼和數(shù)據(jù)庫(kù)觸發(fā)器。（2）對(duì)表所定義的完整性約束是存儲(chǔ)在數(shù)據(jù)字典中，所以由任何應(yīng)用進(jìn)入的數(shù)據(jù)都必須遵守與表相關(guān)聯(lián)的完整性約束。（3）具有最大的開(kāi)發(fā)能力。當(dāng)由完整性約束所實(shí)施的事務(wù)規(guī)則改變時(shí)，管理員只需改變完整性約束的定義，所有應(yīng)用自動(dòng)地遵守所修改的約束。利用完整性約束實(shí)施數(shù)據(jù)完整性規(guī)則有下列優(yōu)點(diǎn)：603．完整性約束的類型Oracle的DBA和應(yīng)用開(kāi)始者對(duì)列的值輸入可使用的完整性約束有下列類型：（1）NOTNULL約束：如果在表的一列的值不允許為空，則需在該列指定NOTNULL約束。（2）UNIQUE鍵約束：在表指定的列或組列上不允許兩行具有重復(fù)值時(shí)，則需要該列或組列上指定UNIQUE鍵完整性約束。在UNIQUE鍵約束定義中的列或組列稱為唯一鍵。所有唯一完整性約束是用索引方法實(shí)施。（3）PRIMARYKEY約束：在數(shù)據(jù)庫(kù)中每一個(gè)表可有一個(gè)PRIMARYKEY約束。包含在PRIMARYKEY完整性約束的列或組列稱為主鍵，每個(gè)表可有一個(gè)主鍵。Oracle使用索引實(shí)施PRIMARYKEY約束。3．完整性約束的類型61（4）FOREIGNKEY約束：在關(guān)系數(shù)據(jù)庫(kù)中表可通過(guò)公共列相關(guān)聯(lián)，該規(guī)則控制必須維護(hù)的列之間的關(guān)系。包含在引用完整性約束定義的列或組列稱為外來(lái)鍵。由外來(lái)鍵所引用的表中的唯一鍵，稱為引用鍵。包含有外來(lái)鍵的表稱為子表或從屬表。由子表的外來(lái)鍵所引用的表稱為雙親表或引用表。如果對(duì)表的每一行，其外來(lái)鍵的值必須與主鍵中一值相匹配，則需指定引用完整性約束。（5）CHECK約束：表的每行對(duì)一指定的條件必須是TRUE或未知，則需在一列或列組上指定CHECK完整性約束。如果在發(fā)出一個(gè)DML語(yǔ)句時(shí)，CHECK約束的條件計(jì)算得FALSE時(shí)，該語(yǔ)句被回滾。（4）FOREIGNKEY約束：在關(guān)系數(shù)據(jù)庫(kù)中表可通過(guò)公共624．?dāng)?shù)據(jù)庫(kù)觸發(fā)器數(shù)據(jù)庫(kù)觸發(fā)器，是使用非說(shuō)明方法實(shí)施完整性規(guī)則，利用數(shù)據(jù)庫(kù)觸發(fā)器可定義和實(shí)施任何類型的完整性規(guī)則。4．?dāng)?shù)據(jù)庫(kù)觸發(fā)器639.6數(shù)據(jù)庫(kù)性能優(yōu)化工具常用的數(shù)據(jù)庫(kù)性能優(yōu)化工具有：（1）Oracle數(shù)據(jù)庫(kù)在線數(shù)據(jù)字典（2）操作系統(tǒng)工具（3）SQL語(yǔ)言跟蹤工具（SQLTRACEFACILITY）（4）OracleEnterpriseManager（OEM）（5）EXPLAINPLANSQL語(yǔ)言優(yōu)化命令9.6數(shù)據(jù)庫(kù)性能優(yōu)化工具常用的數(shù)據(jù)庫(kù)性能優(yōu)化工具有：649.7.2確定Oracle數(shù)據(jù)庫(kù)表中重復(fù)的記錄9.7.1序號(hào)生成器

9.7常見(jiàn)問(wèn)題處理9.7.2確定Oracle數(shù)據(jù)庫(kù)表中重復(fù)的記65

（1）Oracle序號(hào)生成的命令格式為：create序號(hào)名incrementby每次增長(zhǎng)數(shù)startwith起始序號(hào)（2）可以用下面的語(yǔ)句得到當(dāng)前的序號(hào)：selectseq1.currvalfromdual;（3）也可以用下面的語(yǔ)句得到下一個(gè)序號(hào)：insertintodeptvalues(seq1.nextval,...);9.7.1序號(hào)生成器（1）Oracle序號(hào)生成的命令格式為：9.7.1序號(hào)66（4）刪除序號(hào)的語(yǔ)句為：DROPSEQUENCEorder_seq;（5）影響Sequence的初始化參數(shù)：SEQUENCE_CACHE_ENTRIES=設(shè)置能同時(shí)被cache的sequence數(shù)目（4）刪除序號(hào)的語(yǔ)句為：671．自關(guān)聯(lián)查詢方法2．GROUPBY/HAVING查詢方法9.7.2確定Oracle數(shù)據(jù)庫(kù)表中重復(fù)的記錄3．Exceptioninto子句1．自關(guān)聯(lián)查詢方法2．GROUPBY/HAVING查詢方法681．自關(guān)聯(lián)查詢方法Oracle系統(tǒng)中，對(duì)于所有的表都存在一個(gè)唯一的列，這就是rowid。對(duì)該列使用最大（max）或者最?。╩in）函數(shù)可以非常容易地確定重復(fù)的行。1．自關(guān)聯(lián)查詢方法692．GROUPBY/HAVING查詢方法

利用分組函數(shù)GROUPBY/HAVING也很容易確定重復(fù)的行。以需要?jiǎng)?chuàng)建唯一索引的列分組并統(tǒng)計(jì)每組的個(gè)數(shù)，很明顯如果組中記錄數(shù)超過(guò)1個(gè)就存在重復(fù)的行。2．GROUPBY/HAVING查詢方法703．Exceptioninto子句采用altertable命令中的Exceptioninto子句也可以確定出表中重復(fù)的記錄。這種方法稍微麻煩一些，為了使用“excepeioninto”子句，必須首先創(chuàng)建EXCEPTIONS表。創(chuàng)建該表的SQL腳本文件為utlexcpt.sql。對(duì)于NT系統(tǒng)和UNIX系統(tǒng)，Oracle存放該文件的位置稍有不同。將exceptions表和原表通過(guò)rowid關(guān)聯(lián)起來(lái)即可得到原表中重復(fù)的記錄。3．Exceptioninto子句71Oracle的性能優(yōu)化Oracle的性能優(yōu)化72本章學(xué)習(xí)目標(biāo)

本章將介紹優(yōu)化和調(diào)整Oracle數(shù)據(jù)庫(kù)系統(tǒng)的一些相關(guān)命令和方法。第九章Oracle的性能優(yōu)化本章學(xué)習(xí)目標(biāo)第九章Oracle的性能優(yōu)化73本章內(nèi)容安排

9.1數(shù)據(jù)庫(kù)性能優(yōu)化概述9.2SQL語(yǔ)句的優(yōu)化

9.3Oracle運(yùn)行環(huán)境的優(yōu)化

9.4并發(fā)事件處理

9.5數(shù)據(jù)完整性9.7常見(jiàn)問(wèn)題處理

本章內(nèi)容安排9.1數(shù)據(jù)庫(kù)性能優(yōu)化概述749.1數(shù)據(jù)庫(kù)性能優(yōu)化概述9.1.1數(shù)據(jù)庫(kù)性能優(yōu)化的內(nèi)容

9.1.2不同類型系統(tǒng)的優(yōu)化9.1數(shù)據(jù)庫(kù)性能優(yōu)化概述9.1.1數(shù)據(jù)庫(kù)性能優(yōu)化的內(nèi)75（1）調(diào)整數(shù)據(jù)結(jié)構(gòu)的設(shè)計(jì)。（2）調(diào)整應(yīng)用程序結(jié)構(gòu)設(shè)計(jì)。（3）調(diào)整數(shù)據(jù)庫(kù)SQL語(yǔ)句。（4）調(diào)整服務(wù)器內(nèi)存分配。（5）調(diào)整硬盤(pán)I/O，這一步是在信息系統(tǒng)開(kāi)發(fā)之前完成的。（6）調(diào)整操作系統(tǒng)參數(shù)。9.1.1數(shù)據(jù)庫(kù)性能優(yōu)化的內(nèi)容（1）調(diào)整數(shù)據(jù)結(jié)構(gòu)的設(shè)計(jì)。9.1.1數(shù)據(jù)庫(kù)性能優(yōu)化的內(nèi)容761．在線事務(wù)處理信息系統(tǒng)（OLTP）這種類型的信息系統(tǒng)一般需要有大量的Insert、Update操作。OLTP系統(tǒng)需要保證數(shù)據(jù)庫(kù)的并發(fā)性、可靠性和最終用戶的速度，這類系統(tǒng)使用的Oracle數(shù)據(jù)庫(kù)需要主要考慮下述因素或參數(shù)：（1）數(shù)據(jù)庫(kù)回滾段是否足夠？（2）是否需要建立Oracle數(shù)據(jù)庫(kù)索引、聚集、散列？（3）系統(tǒng)全局區(qū)（SGA）大小是否足夠？（4）SQL語(yǔ)句是否高效？9.1.2不同類型系統(tǒng)的優(yōu)化1．在線事務(wù)處理信息系統(tǒng)（OLTP）9.1.2不同類型系772．?dāng)?shù)據(jù)倉(cāng)庫(kù)系統(tǒng)（DataWarehousing）這種信息系統(tǒng)的主要任務(wù)是從Oracle的海量數(shù)據(jù)中進(jìn)行查詢，得到數(shù)據(jù)之間的某些規(guī)律。數(shù)據(jù)庫(kù)管理員需要為這種類型的Oracle數(shù)據(jù)庫(kù)著重考慮下述因素或參數(shù)：（1）是否采用B*-索引或者bitmap索引？（2）是否采用并行SQL查詢以提高查詢效率？（3）是否采用PL/SQL函數(shù)編寫(xiě)存儲(chǔ)過(guò)程？（4）是否有必要建立并行數(shù)據(jù)庫(kù)，來(lái)提高數(shù)據(jù)庫(kù)的查詢效率。2．?dāng)?shù)據(jù)倉(cāng)庫(kù)系統(tǒng)（DataWarehousing）789.2.2SQL語(yǔ)句優(yōu)化的具體方法

9.2.1SQL語(yǔ)句的優(yōu)化規(guī)則

9.2SQL語(yǔ)句的優(yōu)化9.2.2SQL語(yǔ)句優(yōu)化的具體方法9.2.1SQL799.2.1SQL語(yǔ)句的優(yōu)化規(guī)則

（1）去掉不必要的大表、全表掃描。不必要的大表、全表掃描會(huì)造成不必要的輸入輸出，而且還會(huì)拖垮整個(gè)數(shù)據(jù)庫(kù)；（2）檢查優(yōu)化索引的使用這對(duì)于提高查詢速度來(lái)說(shuō)非常重要；（3）檢查子查詢，考慮SQL子查詢是否可以用簡(jiǎn)單連接的方式進(jìn)行重新書(shū)寫(xiě)；（4）調(diào)整PCTFREE和PCTUSED等存儲(chǔ)參數(shù)優(yōu)化插入、更新或者刪除等操作；（5）考慮數(shù)據(jù)庫(kù)的優(yōu)化器；（6）考慮數(shù)據(jù)表的全表掃描和在多個(gè)CPU的情況下考慮并行查詢。9.2.1SQL語(yǔ)句的優(yōu)化規(guī)則（1）去掉不必要的大表、801．索引的使用2.SQL語(yǔ)句排序優(yōu)化3.選擇聯(lián)合查詢的聯(lián)合次序

9.2.2SQL語(yǔ)句優(yōu)化的具體方法4.SQL子查詢的調(diào)整1．索引的使用2.SQL語(yǔ)句排序優(yōu)化3.選擇聯(lián)合查詢的聯(lián)合81

1、索引的使用（1）盡量使用索引是全表掃描還是索引范圍掃描主要考慮SQL的查詢速度問(wèn)題。試比較下面兩條SQL語(yǔ)句：①語(yǔ)句A：SELECTdname,deptnoFROMdeptWHEREdeptnoNOTIN（SELECTdeptnoFROMemp）;②語(yǔ)句B：SELECTdname,deptnoFROMdeptWHERENOTEXISTS（SELECTdeptnoFROMempWHEREdept.deptno=emp.deptno）;1、索引的使用82

（2）索引不起作用的情況①存在數(shù)據(jù)類型隱形轉(zhuǎn)換②列上有數(shù)學(xué)運(yùn)算③使用不等于（<>）運(yùn)算④使用substr字符串函數(shù)⑤‘%’通配符在第一個(gè)字符⑥字符串連接（||）（2）索引不起作用的情況83

（3）函數(shù)的索引例如，日期類型是經(jīng)常用到的，而且在SQL語(yǔ)句中會(huì)使用to_char函數(shù)以查詢具體的的范圍日期。如：select*fromstaff_memberwhereTO_CHAR（birth_day,’YYYY’）=’2003’;可以建立基于函數(shù)的索引如：CREATEINDEXInd_emp_birthONstaff_member（to_char（（birth_day,’YYYY’））;（3）函數(shù)的索引842.SQL語(yǔ)句排序優(yōu)化排序發(fā)生的情況如下：SQL中包含groupby子句SQL中包含orderby子句SQL中包含distinct子句SQL中包含minus或union操作2.SQL語(yǔ)句排序優(yōu)化853．選擇聯(lián)合查詢的聯(lián)合次序聯(lián)合查詢中如涉及到多個(gè)表的字段關(guān)聯(lián)及查詢，其SQL查詢語(yǔ)句聯(lián)合次序的不同寫(xiě)法，會(huì)導(dǎo)致語(yǔ)句對(duì)各表具體操作的步驟有不同的次序，所以雖然執(zhí)行結(jié)果相同，但執(zhí)行效率卻不同。

3．選擇聯(lián)合查詢的聯(lián)合次序86

４．SQL子查詢的調(diào)整（1）關(guān)聯(lián)子查詢和非關(guān)聯(lián)子查詢非關(guān)聯(lián)查詢的開(kāi)銷——非關(guān)聯(lián)查詢時(shí)子查詢只會(huì)執(zhí)行一次，而且結(jié)果是排序好的，并保存在一個(gè)Oracle的臨時(shí)段中，其中的每一個(gè)記錄在返回時(shí)都會(huì)被父查詢所引用。在子查詢返回大量的記錄的情況下，將這些結(jié)果集排序，以及將臨時(shí)數(shù)據(jù)段進(jìn)行排序會(huì)增加大量的系統(tǒng)開(kāi)銷。關(guān)聯(lián)查詢的開(kāi)銷——對(duì)返回到父查詢的記錄來(lái)說(shuō)，子查詢會(huì)每行執(zhí)行一次。因此，必須保證任何可能的時(shí)候子查詢用到索引。４．SQL子查詢的調(diào)整87（2）在子查詢中慎重使用IN或者NOTIN語(yǔ)句在子查詢中慎重使用IN或者NOTIN語(yǔ)句，使用where（NOT）exists的效果要好的多。①帶IN的關(guān)聯(lián)子查詢是多余的，因?yàn)镮N子句和子查詢中相關(guān)的操作的功能是一樣的。②為非關(guān)聯(lián)子查詢指定EXISTS子句是不適當(dāng)?shù)模驗(yàn)檫@樣會(huì)產(chǎn)生笛卡爾乘積。③盡量不要使用NOTIN子句。（2）在子查詢中慎重使用IN或者NOTIN語(yǔ)句88（3）慎重使用視圖的聯(lián)合查詢慎重使用視圖的聯(lián)合查詢，尤其是比較復(fù)雜的視圖之間的聯(lián)合查詢。一般對(duì)視圖的查詢最好都分解為對(duì)數(shù)據(jù)表的直接查詢效果要好一些?？梢栽趨?shù)文件中設(shè)置SHARED_POOL_RESERVED_SIZE參數(shù)，這個(gè)參數(shù)在SGA共享池中保留一個(gè)連續(xù)的內(nèi)存空間，連續(xù)的內(nèi)存空間有益于存放大的SQL程序包。（3）慎重使用視圖的聯(lián)合查詢899.3.2物理I/O的調(diào)整

9.3.3CPU的優(yōu)化調(diào)整

9.3.4網(wǎng)絡(luò)配置的優(yōu)化

9.3.1內(nèi)存結(jié)構(gòu)的調(diào)整

9.3Oracle運(yùn)行環(huán)境的優(yōu)化

9.3.5Oracle碎片整理

9.3.6Oracle系統(tǒng)參數(shù)的調(diào)整

9.3.2物理I/O的調(diào)整9.3.3CPU的優(yōu)化調(diào)90內(nèi)存參數(shù)的調(diào)整主要是指Oracle數(shù)據(jù)庫(kù)的系統(tǒng)全局區(qū)（SGA）的調(diào)整。SGA主要由三部分構(gòu)成：共享池、數(shù)據(jù)緩沖區(qū)、日志緩沖區(qū)。1．共享池共享池由兩部分構(gòu)成：共享SQL區(qū)和數(shù)據(jù)字典緩沖區(qū)。共享SQL區(qū)是存放用戶SQL命令的區(qū)域，數(shù)據(jù)字典緩沖區(qū)存放數(shù)據(jù)庫(kù)運(yùn)行的動(dòng)態(tài)信息。9.3.1內(nèi)存結(jié)構(gòu)的調(diào)整內(nèi)存參數(shù)的調(diào)整主要是指Oracle數(shù)據(jù)庫(kù)的系統(tǒng)全91（1）數(shù)據(jù)庫(kù)管理員通過(guò)執(zhí)行下述語(yǔ)句，來(lái)查看共享SQL區(qū)的使用率。select（sum（pins-reloads））/sum（pins）"LibCache"fromv$librarycache;共享SQL區(qū)的使用率應(yīng)該在90％以上，否則需要增加共享池的大小。（2）數(shù)據(jù)庫(kù)管理員可以執(zhí)行下述語(yǔ)句，查看數(shù)據(jù)字典緩沖區(qū)的使用率。select（sum（-getmisses-usage-fixed））/sum（gets）"RowCache"fromv$rowcache;數(shù)據(jù)字典緩沖區(qū)的使用率也應(yīng)該在90％以上，否則需要增加共享池的大小。（1）數(shù)據(jù)庫(kù)管理員通過(guò)執(zhí)行下述語(yǔ)句，來(lái)查看共享SQL區(qū)的使用922．?dāng)?shù)據(jù)緩沖區(qū)數(shù)據(jù)庫(kù)管理員可以通過(guò)下述語(yǔ)句，來(lái)查看數(shù)據(jù)庫(kù)數(shù)據(jù)緩沖區(qū)的使用情況。SELECTname，F(xiàn)ROMv$sysstatWHEREnameIN（'dbblockgets','consistentgets','physicalreads'）;根據(jù)查詢出來(lái)的結(jié)果可以計(jì)算出數(shù)據(jù)緩沖區(qū)的使用命中率：數(shù)據(jù)緩沖區(qū)的使用命中率＝1–(physicalreads/(dbblockgets+consistentgets))這個(gè)命中率應(yīng)該在90％以上，否則需要增加數(shù)據(jù)緩沖區(qū)的大小。2．?dāng)?shù)據(jù)緩沖區(qū)933．日志緩沖區(qū)數(shù)據(jù)庫(kù)管理員可以通過(guò)執(zhí)行下述語(yǔ)句，查看日志緩沖區(qū)的使用情況。selectname,valuefromv$sysstatwherenamein('redoentries','redologspacerequests');根據(jù)查詢出的結(jié)果可以計(jì)算出日志緩沖區(qū)的申請(qǐng)失敗率：申請(qǐng)失敗率＝requests/entries申請(qǐng)失敗率應(yīng)該接近于0，否則說(shuō)明日志緩沖區(qū)開(kāi)設(shè)太小，需要增加Oracle數(shù)據(jù)庫(kù)的日志緩沖區(qū)。3．日志緩沖區(qū)94（1）在磁盤(pán)上建立數(shù)據(jù)文件前首先運(yùn)行磁盤(pán)碎片整理程序?yàn)榱税踩卣泶疟P(pán)碎片，需關(guān)閉打開(kāi)數(shù)據(jù)文件的實(shí)例，并且停止服務(wù)。如果有足夠的連續(xù)磁盤(pán)空間建立數(shù)據(jù)文件，那么就很容易避免數(shù)據(jù)文件產(chǎn)生碎片。（2）不要使用磁盤(pán)壓縮Oracle數(shù)據(jù)文件不支持磁盤(pán)壓縮。（3）不要使用磁盤(pán)加密加密象磁盤(pán)壓縮一樣增加了一個(gè)處理層，降低磁盤(pán)讀寫(xiě)速度。如果擔(dān)心自己的數(shù)據(jù)可能泄密，可以使用dbms_obfuscation包和labelsecurity選擇性地加密數(shù)據(jù)的敏感部分。9.3.2物理I/O的調(diào)整（1）在磁盤(pán)上建立數(shù)據(jù)文件前首先運(yùn)行磁盤(pán)碎片整理程序9.3.95（5）使用RAIDRAID的使用應(yīng)注意：①選擇硬件RAID超過(guò)軟件RAID；②日志文件不要放在RAID5卷上，因?yàn)镽AID5讀性能高而寫(xiě)性能差；③把日志文件和歸檔日志放在與控制文件和數(shù)據(jù)文件分離的磁盤(pán)控制系統(tǒng)上。（6）分離頁(yè)面交換文件到多個(gè)磁盤(pán)物理卷跨越至少兩個(gè)磁盤(pán)建立兩個(gè)頁(yè)面文件?？梢越⑺膫€(gè)頁(yè)面文件并在性能上受益，確保所有頁(yè)面文件的大小之和至少是物理內(nèi)存的兩倍。（5）使用RAID961．查看CPU的使用情況使用操作命令可以看到CPU的使用情況，一般UNIX操作系統(tǒng)的服務(wù)器，可以使用sar–u命令查看CPU的使用率；NT操作系統(tǒng)的服務(wù)器，可以使用NT的性能管理器來(lái)查看CPU的使用率。出現(xiàn)CPU資源不足的情況是很多的：SQL語(yǔ)句的重解析、低效率的SQL語(yǔ)句、鎖沖突都會(huì)引起CPU資源不足。9.3.3CPU的優(yōu)化調(diào)整1．查看CPU的使用情況9.3.3CPU的優(yōu)化調(diào)整972．查看SQL語(yǔ)句的解析情況（1）數(shù)據(jù)庫(kù)管理員可以執(zhí)行下述語(yǔ)句來(lái)查看SQL語(yǔ)句的解析情況：SELECT*FROMV$SYSSTATWHERENAMEIN('parse_time_cpu','parse_time_elapsed','parse_count_hard');這里：①parse_time_cpu：是系統(tǒng)服務(wù)時(shí)間。②parse_time_elapsed：是響應(yīng)時(shí)間。而用戶等待時(shí)間為：waite_time=parse_time_elapsed–parse_time_cpu由此可以得到用戶SQL語(yǔ)句平均解析等待時(shí)間：用戶SQL語(yǔ)句平均解析等待時(shí)間＝waite_time/parse_count2．查看SQL語(yǔ)句的解析情況98（2）數(shù)據(jù)庫(kù)管理員還可以通過(guò)下述語(yǔ)句，查看低效率的SQL語(yǔ)句：SELECTBUFFER_GETS,EXECUTIONS,SQL_TEXTFROMV$SQLAREA;優(yōu)化這些低效率的SQL語(yǔ)句也有助于提高CPU的利用率。（2）數(shù)據(jù)庫(kù)管理員還可以通過(guò)下述語(yǔ)句，查看低效率的SQL語(yǔ)句993．查看Oracle數(shù)據(jù)庫(kù)的沖突情況數(shù)據(jù)庫(kù)管理員可以通過(guò)v$system_event數(shù)據(jù)字典中的“l(fā)atchfree”統(tǒng)計(jì)項(xiàng)查看Oracle數(shù)據(jù)庫(kù)的沖突情況，如果沒(méi)有沖突的話，latchfree查詢出來(lái)沒(méi)有結(jié)果。如果沖突太大的話，數(shù)據(jù)庫(kù)管理員可以降低spin_count參數(shù)值，來(lái)消除高的CPU使用率。3．查看Oracle數(shù)據(jù)庫(kù)的沖突情況1004．CPU的優(yōu)化調(diào)整方法一些優(yōu)化CPU使用和配置的具體方法有：（1）取消屏幕保護(hù)。（2）把系統(tǒng)配置為應(yīng)用服務(wù)器。（3）監(jiān)視系統(tǒng)中消耗中斷的硬件。（4）保持最小的安全審計(jì)記錄。（5）在專用服務(wù)器上運(yùn)行Oracle。（6）禁止非必須的服務(wù)。4．CPU的優(yōu)化調(diào)整方法101網(wǎng)絡(luò)配置是性能調(diào)整的一項(xiàng)很重要的內(nèi)容，而且很容易隱藏性能瓶頸。（1）配置網(wǎng)卡使用最快速度和有效模式（2）刪除不需要的網(wǎng)絡(luò)協(xié)議（3）優(yōu)化網(wǎng)絡(luò)協(xié)議綁定順序（4）為Oracle禁止或優(yōu)化文件共享9.3.4網(wǎng)絡(luò)配置的優(yōu)化網(wǎng)絡(luò)配置是性能調(diào)整的一項(xiàng)很重要的內(nèi)容，而且很1021．碎片是如何產(chǎn)生的2．碎片對(duì)系統(tǒng)的影響（1）導(dǎo)致系統(tǒng)性能減弱（2）浪費(fèi)大量的表空間9.3.5Oracle碎片整理1．碎片是如何產(chǎn)生的9.3.5Oracle碎片整理1033．自由范圍的碎片計(jì)算用fsfi——freespacefragmentationindex（自由空間碎片索引）值來(lái)直觀體現(xiàn)：fsfi=100*sqrt(max(extent)/sum(extents))*1/sqrt(sqrt(count(extents)))3．自由范圍的碎片計(jì)算1044．自由范圍的碎片整理可以將表空間的缺省存儲(chǔ)參數(shù)pctincrease改為非0。一般將其設(shè)為1，如：altertablespacetempdefaultstorage(pctincrease1);這樣smon便會(huì)將自由范圍自動(dòng)合并，達(dá)到碎片整理的目的。也可以采用如下語(yǔ)句，通過(guò)手工合并自由范圍來(lái)達(dá)到碎片整理的目的。altertablespacetempcoalesce;4．自由范圍的碎片整理1055．段的碎片整理段由范圍組成，在有些情況下，有必要對(duì)段的碎片進(jìn)行整理。要查看段的有關(guān)信息，可查看數(shù)據(jù)字典DBA_segments，范圍的信息可查看數(shù)據(jù)字典DBA_extents。如果段的碎片過(guò)多，將其數(shù)據(jù)壓縮到一個(gè)范圍的最簡(jiǎn)單方法便是用正確的存儲(chǔ)參數(shù)將這個(gè)段重建，然后將舊表中的數(shù)據(jù)插入到新表，同時(shí)刪除舊表。這個(gè)過(guò)程可以用import/export（輸入/輸出）工具來(lái)完成。export（）命令有一個(gè)（壓縮）標(biāo)志，這個(gè)標(biāo)志在讀表時(shí)會(huì)引發(fā)export確定該表所分配的物理空間量，它會(huì)向輸出轉(zhuǎn)儲(chǔ)文件寫(xiě)入一個(gè)新的初始化存儲(chǔ)參數(shù)，等于全部所分配空間。若這個(gè)表關(guān)閉，則使用import（）工具重新生成。這樣，它的數(shù)據(jù)會(huì)放入一個(gè)新的、較大的初始段中。例如：expuser/passwordfile=exp.dmpcompress=ygrants=yindexes=ytables=(table1,table2);若輸出成功，則從庫(kù)中刪除已輸出的表，然后從輸出轉(zhuǎn)儲(chǔ)文件中輸入表：impuser/passwordfile=exp.dmpcommit=ybuffer=64000full=y;這種方法可用于整個(gè)數(shù)據(jù)庫(kù)。另外，應(yīng)該定期shutdowndatabase，從而清理momery碎片。5．段的碎片整理1069.3.6Oracle系統(tǒng)參數(shù)的調(diào)整

1．SharedPoolandLibraryCachePerformanceTuning（共享池和LibraryCache）共享池調(diào)整的技巧主要有：（1）刷共享池刷（Flush）共享池可以使小塊的內(nèi)存合并為大塊的內(nèi)存。當(dāng)共享池的碎片過(guò)多時(shí)，能夠暫時(shí)恢復(fù)性能。刷共享池可以使用語(yǔ)句：altersystemflushshared_pool;（2）綁定變量9.3.6Oracle系統(tǒng)參數(shù)的調(diào)整1．Shared1072．BufferCachePerformanceTuning（數(shù)據(jù)庫(kù)緩存調(diào)整）從緩存調(diào)整的角度看，應(yīng)力求避免以下的問(wèn)題：（1）“緩存的最近最少使用（LRN）鏈”（cachebuffersLRUchain）的加鎖競(jìng)爭(zhēng)；（2）“平均寫(xiě)隊(duì)列”（AverageWriteQueue）長(zhǎng)度過(guò)大；（3）過(guò)多時(shí)間花在等待“寫(xiě)完畢等待上”（writecompletewaits）；（4）過(guò)多時(shí)間花在等待“緩沖釋放等待”上（freebufferwaits）。2．BufferCachePerformanceTun1083．LatchContention（加鎖或插銷競(jìng)爭(zhēng)）插銷加鎖是SGA中保護(hù)共享數(shù)據(jù)結(jié)構(gòu)的低層的串行化機(jī)制。插銷latch是一類可以非?？斓墨@得和釋放的鎖。插銷鎖的實(shí)現(xiàn)是依賴于操作系統(tǒng)的，尤其在關(guān)于一個(gè)進(jìn)程是否會(huì)等待一個(gè)鎖，和等多久方面。有如下的鎖（插銷）需要調(diào)整：（1）RedoCopy/AllocationLatch：重寫(xiě)日志的復(fù)制/分配插銷（2）SharedPoolLatch：共享池的插銷（3）LibraryCacheLatch：LibraryCache插銷3．LatchContention（加鎖或插銷競(jìng)爭(zhēng)）1094．RedoLogBufferPerformanceTuning（重寫(xiě)日志緩沖的調(diào)整）LGWR將重寫(xiě)日志緩沖中的重寫(xiě)項(xiàng)寫(xiě)到重寫(xiě)日志文件中。一旦LGWR將這些項(xiàng)復(fù)制到重寫(xiě)日志文件中，用戶進(jìn)程就可以重寫(xiě)這些項(xiàng)。統(tǒng)計(jì)項(xiàng)目“redologspacerequests”反映了用戶進(jìn)程等待重寫(xiě)日志緩沖中空間的時(shí)間的數(shù)字。（1）設(shè)置重寫(xiě)日志大小的提示：“redologspacerequests”的值應(yīng)該接近0。（2）設(shè)定合適的重寫(xiě)日志的大小，建議每15-30分鐘進(jìn)行一次重寫(xiě)日志的切換。4．RedoLogBufferPerformance1105．QueryPerformanceTuning（查詢效率的調(diào)整）如果查詢運(yùn)行得很慢，請(qǐng)考慮以下這些方面：（1）希望這個(gè)查詢運(yùn)行的有多快以及有理由這樣要求嗎？（2）優(yōu)化模式OPTIMIZER_MODE設(shè)為何值？（3）查詢涉及的索引都是有效的嗎？（4）在數(shù)據(jù)庫(kù)中有沒(méi)有其他的長(zhǎng)時(shí)間運(yùn)行的查詢（大查詢）。（5）表和索引上有統(tǒng)計(jì)信息嗎？（6）統(tǒng)計(jì)信息是被計(jì)算出來(lái)的還是被估計(jì)出來(lái)的？對(duì)于查詢的性能調(diào)整有兩個(gè)主要的調(diào)試工具：TKPROF和AUTOTRACE。5．QueryPerformanceTuning（查詢效1116．TemporaryTablespacePerformanceTuning（臨時(shí)表空間的調(diào)整）

臨時(shí)表空間的調(diào)整的技巧如下：如果即使在穩(wěn)定的狀態(tài)下也存在很多的排序擴(kuò)展鎖（SortExtentPoollatch）的競(jìng)爭(zhēng)，應(yīng)該通過(guò)修改臨時(shí)表空間的DEFAULTSTORAGE子句的NEXT值來(lái)增大擴(kuò)展塊的大小。如果存在很多的排序擴(kuò)展鎖（SortExtentPoollatch）的競(jìng)爭(zhēng)并且這種等待是由于過(guò)多的并發(fā)的排序造成的，應(yīng)該增大SORT_AREA_SIZE參數(shù)的大小，以使更多的排序能保存在內(nèi)存中。建議讓擴(kuò)展塊的大小和SORT_AREA_SIZE參數(shù)相同。6．TemporaryTablespacePerform112

9.4.2鎖9.4.1并發(fā)事件的產(chǎn)生

9.4并發(fā)事件處理9.4.2鎖9.4.1并發(fā)事件的產(chǎn)生113

數(shù)據(jù)庫(kù)是一個(gè)共享資源，可為多個(gè)應(yīng)用程序所共享。這些程序可串行運(yùn)行，但在許多情況下，可能多個(gè)程序或一個(gè)程序的多個(gè)進(jìn)程并行地運(yùn)行，這就是數(shù)據(jù)庫(kù)的并行操作。在多用戶數(shù)據(jù)庫(kù)環(huán)境中，多個(gè)用戶程序可并行地存取數(shù)據(jù)庫(kù)，如果不對(duì)并發(fā)操作進(jìn)行控制，會(huì)存取不正確的數(shù)據(jù)，或破壞數(shù)據(jù)庫(kù)數(shù)據(jù)的一致性。9.4.1并發(fā)事件的產(chǎn)生數(shù)據(jù)庫(kù)是一個(gè)共享資源，可為多個(gè)應(yīng)用程序所共114（1）當(dāng)一個(gè)用戶正在修改表中數(shù)據(jù)的同時(shí)，另一個(gè)用戶正試圖刪除該表。（2）用戶A正試圖讀取用戶B的某個(gè)事務(wù)中的一些數(shù)據(jù)，在用戶A的事務(wù)開(kāi)始后，該事務(wù)由用戶B修改和提交。用戶A讀取用戶B提交的數(shù)據(jù)。這意味著在同一個(gè)事務(wù)中讀取的數(shù)據(jù)在某個(gè)時(shí)刻不一致。（3）某個(gè)用戶對(duì)數(shù)據(jù)進(jìn)行修改，另一個(gè)用戶在第一個(gè)用戶提交事務(wù)以前對(duì)同一行進(jìn)行修改；因此，第一個(gè)用戶所做的改變丟失了。（4）一個(gè)用戶從另一個(gè)用戶尚未提交的數(shù)據(jù)中讀取數(shù)據(jù)；也就是說(shuō)，在用戶B的改變提交以前，用戶A讀取用戶B正在修改的行。（1）當(dāng)一個(gè)用戶正在修改表中數(shù)據(jù)的同時(shí)，另一個(gè)用戶正試圖刪除1159.4.2鎖1．鎖的功能2．鎖的類型3．鎖的模式

4．查看相關(guān)信息

9.4.2鎖1．鎖的功能2．鎖的類型3．鎖的模式41161．鎖的功能（1）數(shù)據(jù)一致性（2）數(shù)據(jù)并行性（3）數(shù)據(jù)完整性1．鎖的功能117Oracle在兩個(gè)不同級(jí)上提供讀一致性：語(yǔ)句級(jí)讀一致性和事務(wù)級(jí)一致性。（1）語(yǔ)句級(jí)讀取一致性（2）事務(wù)級(jí)讀取一致性O(shè)racle在兩個(gè)不同級(jí)上提供讀一致性：語(yǔ)句級(jí)讀一致性和事務(wù)1182．鎖的類型Oracle鎖具體分為以下幾類：（1）自動(dòng)鎖與顯示鎖按用戶與系統(tǒng)劃分，可以分為自動(dòng)鎖與顯示鎖。①自動(dòng)鎖：當(dāng)進(jìn)行一項(xiàng)數(shù)據(jù)庫(kù)操作時(shí)，缺省情況下，系統(tǒng)自動(dòng)為此數(shù)據(jù)庫(kù)操作獲得所有有必要的鎖。②顯示鎖：某些情況下，需要用戶顯示的鎖定數(shù)據(jù)庫(kù)操作要用到的數(shù)據(jù)，才能使數(shù)據(jù)庫(kù)操作執(zhí)行得更好，顯示鎖是用戶為數(shù)據(jù)庫(kù)對(duì)象設(shè)定的。2．鎖的類型119（2）共享鎖與排它鎖按鎖級(jí)別劃分，可分為共享鎖與排它鎖。①共享鎖：共享鎖允許相關(guān)資源可以共享，幾個(gè)用戶可同時(shí)讀同一數(shù)據(jù)，幾個(gè)事務(wù)可在同一資源上獲取共享封鎖。共享鎖比排它鎖具有更高的數(shù)據(jù)并行性。但拙劣的事務(wù)設(shè)計(jì)+共享鎖容易造成死鎖或數(shù)據(jù)更新丟失。②排它鎖：事務(wù)設(shè)置排它鎖后，該事務(wù)單獨(dú)獲得此資源，另一事務(wù)不能在此事務(wù)提交之前獲得相同對(duì)象的共享鎖或排它鎖。排它鎖禁止相關(guān)資源的共享，如果一事務(wù)以排它方式封鎖一資源，僅僅該事務(wù)可更改該資源，直至釋放排它封鎖。（2）共享鎖與排它鎖120（3）DML鎖和DDL鎖按操作劃分，可分為DML鎖、DDL鎖。①DML鎖又可以分為：行鎖、表鎖、死鎖。行鎖：當(dāng)事務(wù)執(zhí)行數(shù)據(jù)庫(kù)插入、更新、刪除操作時(shí)，該事務(wù)自動(dòng)獲得操作表中操作行的排它鎖。表鎖：當(dāng)事務(wù)獲得行鎖后，此事務(wù)也將自動(dòng)獲得該行的表鎖（共享鎖），以防止其它事務(wù)進(jìn)行DDL語(yǔ)句影響記錄行的更新。事務(wù)也可以在進(jìn)行過(guò)程中獲得共享鎖或排它鎖，只有當(dāng)事務(wù)顯示使用LOCKTABLE語(yǔ)句顯示的定義一個(gè)排它鎖時(shí)，事務(wù)才會(huì)獲得表上的排它鎖，也可使用LOCKTABLE顯示的定義。死鎖：當(dāng)兩個(gè)事務(wù)需要一組有沖突的鎖，而不能將事務(wù)繼續(xù)下去的話，就出現(xiàn)死鎖。（3）DML鎖和DDL鎖121②DDL鎖又可以分為：排它DDL鎖、共享DDL鎖、分析鎖。排它DDL鎖：創(chuàng)建、修改、刪除一個(gè)數(shù)據(jù)庫(kù)對(duì)象的DDL語(yǔ)句獲得操作對(duì)象的排它鎖。如使用altertable語(yǔ)句時(shí)，為了維護(hù)數(shù)據(jù)的完成性、一致性、合法性，該事務(wù)獲得一排它DDL鎖。共享DDL鎖：需在數(shù)據(jù)庫(kù)對(duì)象之間建立相互依賴關(guān)系的DDL語(yǔ)句通常需共享獲得DDL鎖。如創(chuàng)建一個(gè)包，該包中的過(guò)程與函數(shù)引用了不同的數(shù)據(jù)庫(kù)表，當(dāng)編譯此包時(shí)，該事務(wù)就獲得了引用表的共享DDL鎖。分析鎖：Oracle使用共享池存儲(chǔ)分析與優(yōu)化過(guò)的SQL語(yǔ)句及PL/SQL程序，使運(yùn)行相同語(yǔ)句的應(yīng)用速度更快。一個(gè)在共享池中緩存的對(duì)象獲得它所引用數(shù)據(jù)庫(kù)對(duì)象的分析鎖。分析鎖是一種獨(dú)特的DDL鎖類型，Oracle使用它追蹤共享池對(duì)象及它所引用數(shù)據(jù)庫(kù)對(duì)象之間的依賴關(guān)系。當(dāng)一個(gè)事務(wù)修改或刪除了共享池持有分析鎖的數(shù)據(jù)庫(kù)對(duì)象時(shí)，Oracle使共享池中的對(duì)象作廢，下次在引用這條SQL/PLSQL語(yǔ)句時(shí)，Oracle重新分析編譯此語(yǔ)句。②DDL鎖又可以分為：排它DDL鎖、共享DDL鎖、分析鎖。122（4）內(nèi)部閂鎖內(nèi)部閂鎖是Oracle中的一種特殊鎖，用于順序訪問(wèn)內(nèi)部系統(tǒng)結(jié)構(gòu)。當(dāng)事務(wù)需向緩沖區(qū)寫(xiě)入信息時(shí)，為了使用此塊內(nèi)存區(qū)域，Oracle首先必須取得這塊內(nèi)存區(qū)域的閂鎖，才能向此塊內(nèi)存寫(xiě)入信息。（4）內(nèi)部閂鎖1233．鎖的模式（1）模式0：none。（2）模式1：null空。1級(jí)鎖有：Select，有時(shí)會(huì)在v$locked_object出現(xiàn)。（3）模式2：Row-S行共享（RS）：共享表鎖，subshare。2級(jí)鎖有：Selectforupdate、LockForUpdate、LockRowShare。selectforupdate當(dāng)對(duì)話使用forupdate子串打開(kāi)一個(gè)游標(biāo)時(shí)，所有返回集中的數(shù)據(jù)行都將處于行級(jí)（Row-X）獨(dú)占式鎖定，其他對(duì)象只能查詢這些數(shù)據(jù)行，不能進(jìn)行update、delete或selectforupdate操作。3．鎖的模式124（4）模式3：Row-X行獨(dú)占（RX）：用于行的修改，subexclusive。3級(jí)鎖有：Insert、Update、Delete、LockRowExclusive。沒(méi)有commit之前插入同樣的一條記錄會(huì)沒(méi)有反應(yīng)，因?yàn)楹笠粋€(gè)3的鎖會(huì)一直等待上一個(gè)3的鎖，必須釋放掉上一個(gè)才能繼續(xù)工作。（5）模式4：Share共享鎖（S）：阻止其他DML操作，share。4級(jí)鎖有：CreateIndex、LockShare。locked_mode為2、3、4不影響DML（insert、delete、update、select等）操作，但DDL（alter、drop等）操作會(huì)提示錯(cuò)誤。（4）模式3：Row-X行獨(dú)占（RX）：用于行的修改，su125（6）模式5：S/Row-X共享行獨(dú)占（SRX）：阻止其他事務(wù)操作，share/subexclusive。5級(jí)鎖有：LockShareRowExclusive。具體來(lái)講有主外鍵約束時(shí)update/delete...；可能會(huì)產(chǎn)生4，5的鎖。（7）模式6：exclu