1、Oracle的性能優化.本章學習目的 本章將引見優化和調整Oracle數據庫系統的一些相關命令和方法。第九章 Oracle的性能優化.本章內容安排 9.1 數據庫性能優化概述 9.2 SQL語句的優化 9.3 Oracle運轉環境的優化 9.4 并發事件處置 9.5 數據完好性 9.7 常見問題處置 .9.1 數據庫性能優化概述9.1.1 數據庫性能優化的內容 9.1.2 不同類型系統的優化.1調整數據構造的設計。2調整運用程序構造設計。3調整數據庫SQL語句。4調整效力器內存分配。5調整硬盤I/O，這一步是在信息系統開發之前完成的。6調整操作系統參數。9.1.1 數據庫性能優化的內容.1在線

2、事務處置信息系統OLTP 這種類型的信息系統普通需求有大量的Insert、Update操作。OLTP系統需求保證數據庫的并發性、可靠性和最終用戶的速度，這類系統運用的Oracle數據庫需求主要思索下述要素或參數：1數據庫回滾段能否足夠？2能否需求建立Oracle數據庫索引、聚集、散列？3系統全局區SGA大小能否足夠？4SQL語句能否高效？9.1.2 不同類型系統的優化.2數據倉庫系統Data Warehousing這種信息系統的主要義務是從Oracle的海量數據中進展查詢，得到數據之間的某些規律。數據庫管理員需求為這種類型的Oracle數據庫著重思索下述要素或參數：1能否采用B*-索引或者bi

3、tmap索引？2能否采用并行SQL查詢以提高查詢效率？3能否采用PL/SQL函數編寫存儲過程？4能否有必要建立并行數據庫，來提高數據庫的查詢效率。 .9.2.2 SQL語句優化的詳細方法 9.2.1 SQL語句的優化規那么 9.2 SQL語句的優化.9.2.1 SQL語句的優化規那么 1去掉不用要的大表、全表掃描。不用要的大表、全表掃描會呵斥不用要的輸入輸出，而且還會拖垮整個數據庫；2檢查優化索引的運用 這對于提高查詢速度來說非常重要；3檢查子查詢，思索SQL子查詢能否可以用簡單銜接的方式進展重新書寫；4調整PCTFREE和PCTUSED等存儲參數優化插入、更新或者刪除等操作；5思索數據庫的優

4、化器；6思索數據表的全表掃描和在多個CPU的情況下思索并行查詢。.1索引的運用2.SQL語句排序優化3. 選擇結合查詢的結合次序 9.2.2 SQL語句優化的詳細方法4.SQL子查詢的調整. 1、索引的運用1盡量運用索引 是全表掃描還是索引范圍掃描主要思索SQL的查詢速度問題。試比較下面兩條SQL語句：語句A：SELECT dname,deptno FROM dept WHERE deptno NOT INSELECT deptno FROM emp;語句B：SELECT dname,deptno FROM dept WHERE NOT EXISTSSELECT deptno FROM emp

5、 WHERE dept.deptno = emp.deptno;. 2索引不起作用的情況存在數據類型隱形轉換列上有數學運算運用不等于運算運用substr字符串函數%通配符在第一個字符字符串銜接|. 3函數的索引例如，日期類型是經常用到的，而且在SQL語句中會運用to_char函數以查詢詳細的的范圍日期。如：select * from staff_member where TO_CHARbirth_day,YYYY=2003;可以建立基于函數的索引如：CREATE INDEX Ind_emp_birth ON staff_member to_charbirth_day,YYYY;.2.SQL語句

6、排序優化排序發生的情況如下：SQL中包含group by 子句SQL 中包含order by 子句SQL 中包含 distinct 子句SQL 中包含 minus 或 union操作.3選擇結合查詢的結合次序 結合查詢中如涉及到多個表的字段關聯及查詢，其SQL查詢語句結合次序的不同寫法，會導致語句對各表詳細操作的步驟有不同的次序，所以雖然執行結果一樣，但執行效率卻不同。 . SQL子查詢的調整1關聯子查詢和非關聯子查詢 非關聯查詢的開銷非關聯查詢時子查詢只會執行一次，而且結果是排序好的，并保管在一個Oracle的暫時段中，其中的每一個記錄在前往時都會被父查詢所援用。在子查詢前往大量的記錄的情況

7、下，將這些結果集排序，以及將暫時數據段進展排序會添加大量的系統開銷。 關聯查詢的開銷對前往到父查詢的記錄來說，子查詢會每行執行一次。因此，必需保證任何能夠的時候子查詢用到索引。.2在子查詢中慎重運用IN或者NOT IN語句 在子查詢中慎重運用IN或者NOT IN語句，運用where NOTexists的效果要好的多。帶IN的關聯子查詢是多余的，由于IN子句和子查詢中相關的操作的功能是一樣的。為非關聯子查詢指定EXISTS子句是不適當的，由于這樣會產生笛卡爾乘積。盡量不要運用NOT IN子句。.3慎重運用視圖的結合查詢 慎重運用視圖的結合查詢，尤其是比較復雜的視圖之間的結合查詢。普通對視圖的查詢

8、最好都分解為對數據表的直接查詢效果要好一些。 可以在參數文件中設置SHARED_POOL_RESERVED_SIZE參數，這個參數在SGA共享池中保管一個延續的內存空間，延續的內存空間有益于存放大的SQL程序包。.9.3.2 物理I/O的調整 9.3.3 CPU的優化調整 9.3.4 網絡配置的優化 9.3.1 內存構造的調整 9.3 Oracle運轉環境的優化 9.3.5 Oracle碎片整理 9.3.6 Oracle系統參數的調整 . 內存參數的調整主要是指Oracle數據庫的系統全局區SGA的調整。SGA主要由三部分構成：共享池、數據緩沖區、日志緩沖區。1共享池 共享池由兩部分構成：共享

9、SQL區和數據字典緩沖區。共享SQL區是存放用戶SQL命令的區域，數據字典緩沖區存放數據庫運轉的動態信息。9.3.1 內存構造的調整.1數據庫管理員經過執行下述語句，來查看共享SQL區的運用率。select sumpins-reloads/sumpins Lib Cache from v$librarycache; 共享SQL區的運用率應該在90以上，否那么需求添加共享池的大小。2數據庫管理員可以執行下述語句，查看數據字典緩沖區的運用率。select sum-getmisses-usage-fixed/sumgets Row Cache from v$rowcache; 數據字典緩沖區的運用率

10、也應該在90以上，否那么需求添加共享池的大小。 .2數據緩沖區 數據庫管理員可以經過下述語句，來查看數據庫數據緩沖區的運用情況。SELECT name， FROM v$sysstat WHERE name IN db block gets,consistent gets,physical reads; 根據查詢出來的結果可以計算出數據緩沖區的運用命中率：數據緩沖區的運用命中率1 ( physical reads/(db block gets + consistent gets)這個命中率應該在90以上，否那么需求添加數據緩沖區的大小。.3日志緩沖區 數據庫管理員可以經過執行下述語句，查看日志緩

11、沖區的運用情況。select name,value from v$sysstat where name in (redo entries,redo log space requests);根據查詢出的結果可以計算出日志緩沖區的懇求失敗率：懇求失敗率requests/entries懇求失敗率應該接近于0，否那么闡明日志緩沖區開設太小，需求添加Oracle數據庫的日志緩沖區。.1在磁盤上建立數據文件前首先運轉磁盤碎片整理程序 為了平安地整理磁盤碎片，需封鎖翻開數據文件的實例，并且停頓效力。假設有足夠的延續磁盤空間建立數據文件，那么就很容易防止數據文件產生碎片。2不要運用磁盤緊縮 Oracle數據文

12、件不支持磁盤緊縮。3不要運用磁盤加密 加密象磁盤緊縮一樣添加了一個處置層，降低磁盤讀寫速度。假設擔憂本人的數據能夠泄密，可以運用dbms_obfuscation包和label security選擇性地加密數據的敏感部分。9.3.2 物理I/O的調整.5運用RAID RAID的運用應留意：選擇硬件RAID超越軟件RAID；日志文件不要放在RAID 5卷上，由于RAID 5讀性能高而寫性能差；把日志文件和歸檔日志放在與控制文件和數據文件分別的磁盤控制系統上。6分別頁面交換文件到多個磁盤物理卷 跨越至少兩個磁盤建立兩個頁面文件。可以建立四個頁面文件并在性能上受害，確保一切頁面文件的大小之和至少是物理

13、內存的兩倍。.1查看CPU的運用情況 運用操作命令可以看到CPU的運用情況，普通UNIX操作系統的效力器，可以運用sar u命令查看CPU的運用率；NT操作系統的效力器，可以運用NT的性能管理器來查看CPU的運用率。 出現CPU資源缺乏的情況是很多的：SQL語句的重解析、低效率的SQL語句、鎖沖突都會引起CPU資源缺乏。9.3.3 CPU的優化調整.2查看SQL語句的解析情況1數據庫管理員可以執行下述語句來查看SQL語句的解析情況：SELECT * FROM V$SYSSTAT WHERE NAME IN (parse_time_cpu,parse_time_elapsed,parse_cou

14、nt_ hard);這里：parse_time_cpu：是系統效力時間。parse_time_elapsed：是呼應時間。而用戶等待時間為：waite_time = parse_time_elapsed parse_time_cpu由此可以得到用戶SQL語句平均解析等待時間：用戶SQL語句平均解析等待時間waite_time/parse_count.2數據庫管理員還可以經過下述語句，查看低效率的SQL語句：SELECT BUFFER_GETS,EXECUTIONS,SQL_TEXT FROM V$SQLAREA;優化這些低效率的SQL語句也有助于提高CPU的利用率。.3查看Oracle數據庫的

15、沖突情況 數據庫管理員可以經過v$system_event數據字典中的“latch free統計項查看Oracle數據庫的沖突情況，假設沒有沖突的話，latch free查詢出來沒有結果。假設沖突太大的話，數據庫管理員可以降低spin_count參數值，來消除高的CPU運用率。 .4CPU的優化調整方法 一些優化CPU運用和配置的詳細方法有：1取消屏幕維護。2把系統配置為運用效力器。3監視系統中耗費中斷的硬件。4堅持最小的平安審計記錄。5在公用效力器上運轉Oracle。6制止非必需的效力。. 網絡配置是性能調整的一項很重要的內容，而且很容易隱藏性能瓶頸。1配置網卡運用最快速度和有效方式2刪除不

16、需求的網絡協議3優化網絡協議綁定順序4為Oracle制止或優化文件共享9.3.4 網絡配置的優化.1碎片是如何產生的2碎片對系統的影響1導致系統性能減弱2浪費大量的表空間9.3.5 Oracle碎片整理.3自在范圍的碎片計算 用fsfifree space fragmentation index自在空間碎片索引值來直觀表達：fsfi=100*sqrt(max(extent)/sum(extents)*1/sqrt(sqrt(count(extents).4自在范圍的碎片整理 可以將表空間的缺省存儲參數pctincrease改為非0。普通將其設為1，如：alter tablespace temp

17、 default storage(pctincrease 1); 這樣smon便會將自在范圍自動合并，到達碎片整理的目的。 也可以采用如下語句，經過手工合并自在范圍來到達碎片整理的目的。alter tablespace temp coalesce;.5段的碎片整理 段由范圍組成，在有些情況下，有必要對段的碎片進展整理。要查看段的有關信息，可查看數據字典DBA_segments，范圍的信息可查看數據字典DBA_extents。假設段的碎片過多， 將其數據緊縮到一個范圍的最簡一方法便是用正確的存儲參數將這個段重建，然后將舊表中的數據插入到新表，同時刪除舊表。這個過程可以用import/export

18、輸入/輸出工具來完成。 export命令有一個緊縮標志，這個標志在讀表時會引發export確定該表所分配的物理空間量，它會向輸出轉儲文件寫入一個新的初始化存儲參數，等于全部所分配空間。假設這個表封鎖， 那么運用import工具重新生成。這樣，它的數據會放入一個新的、較大的初始段中。例如：exp user/password file=exp.dmp compress=y grants=y indexes=y tables=(table1,table2);假設輸出勝利，那么從庫中刪除已輸出的表，然后從輸出轉儲文件中輸入表：imp user/password file=exp.dmp commit=

19、y buffer=64000 full=y;這種方法可用于整個數據庫。另外，應該定期shutdown database，從而清理momery碎片。.9.3.6 Oracle系統參數的調整 1Shared Pool and Library Cache Performance Tuning共享池和Library Cache共享池調整的技巧主要有：1刷共享池刷 Flush共享池可以使小塊的內存合并為大塊的內存。當共享池的碎片過多時，可以暫時恢復性能。刷共享池可以運用語句：alter system flush shared_pool;2綁定變量.2Buffer Cache Performance Tu

20、ning數據庫緩存調整從緩存調整的角度看，應力求防止以下的問題：1“緩存的最近最少運用LRN鏈cache buffers LRU chain的加鎖競爭；2“平均寫隊列Average Write Queue長度過大；3過多時間花在等待“寫終了等待上write complete waits；4過多時間花在等待“緩沖釋放等待上free buffer waits。.3Latch Contention加鎖或插銷競爭 插銷加鎖是SGA中維護共享數據構造的低層的串行化機制。插銷latch是一類可以非常快的獲得和釋放的鎖。插銷鎖的實現是依賴于操作系統的，尤其在關于一個進程能否會等待一個鎖，和等多久方面。有如下

21、的鎖插銷需求調整：1Redo Copy/Allocation Latch：重寫日志的復制/分配插銷2Shared Pool Latch：共享池的插銷3Library Cache Latch：Library Cache插銷.4Redo Log Buffer Performance Tuning重寫日志緩沖的調整 LGWR 將重寫日志緩沖中的重寫項寫到重寫日志文件中。一旦LGWR將這些項復制到重寫日志文件中，用戶進程就可以重寫這些項。統計工程“redo log space requests反映了用戶進程等待重寫日志緩沖中空間的時間的數字。1設置重寫日志大小的提示：“redo log space r

22、equests的值應該接近0。2設定適宜的重寫日志的大小，建議每15-30分鐘進展一次重寫日志的切換。.5Query Performance Tuning查詢效率的調整 假設查詢運轉得很慢，請思索以下這些方面：1希望這個查詢運轉的有多快以及有理由這樣要求嗎？2優化方式OPTIMIZER_MODE 設為何值？3查詢涉及的索引都是有效的嗎？4在數據庫中有沒有其他的長時間運轉的查詢大查詢。5表和索引上有統計信息嗎？6統計信息是被計算出來的還是被估計出來的？對于查詢的性能調整有兩個主要的調試工具：TKPROF和AUTOTRACE。.6Temporary Tablespace Performance T

23、uning暫時表空間的調整 暫時表空間的調整的技巧如下： 假設即使在穩定的形狀下也存在很多的排序擴展鎖Sort Extent Pool latch的競爭，應該經過修正暫時表空間的DEFAULT STORAGE 子句的NEXT值來增大擴展塊的大小。假設存在很多的排序擴展鎖Sort Extent Pool latch的競爭并且這種等待是由于過多的并發的排序呵斥的，應該增大SORT_AREA_SIZE參數的大小，以使更多的排序能保管在內存中。建議讓擴展塊的大小和SORT_AREA_SIZE參數一樣。. 9.4.2 鎖9.4.1 并發事件的產生 9.4 并發事件處置. 數據庫是一個共享資源，可為多個運

24、用程序所共享。這些程序可串行運轉，但在許多情況下，能夠多個程序或一個程序的多個進程并行地運轉，這就是數據庫的并行操作。在多用戶數據庫環境中，多個用戶程序可并行地存取數據庫，假設不對并發操作進展控制，會存取不正確的數據，或破壞數據庫數據的一致性。9.4.1 并發事件的產生.1當一個用戶正在修正表中數據的同時，另一個用戶正試圖刪除該表。2用戶A正試圖讀取用戶B的某個事務中的一些數據，在用戶A的事務開場后，該事務由用戶B修正和提交。用戶A讀取用戶B提交的數據。這意味著在同一個事務中讀取的數據在某個時辰不一致。3某個用戶對數據進展修正，另一個用戶在第一個用戶提交事務以前對同一行進展修正；因此，第一個用

25、戶所做的改動喪失了。4一個用戶從另一個用戶尚未提交的數據中讀取數據；也就是說，在用戶B的改動提交以前，用戶A讀取用戶B正在修正的行。.9.4.2 鎖1鎖的功能 2鎖的類型 3鎖的方式 4查看相關信息 .1鎖的功能1數據一致性2數據并行性3數據完好性.Oracle在兩個不同級上提供讀一致性：語句級讀一致性和事務級一致性。1語句級讀取一致性2事務級讀取一致性.2鎖的類型Oracle鎖詳細分為以下幾類：1自動鎖與顯示鎖按用戶與系統劃分，可以分為自動鎖與顯示鎖。自動鎖：當進展一項數據庫操作時，缺省情況下，系統自動為此數據庫操作獲得一切有必要的鎖。顯示鎖：某些情況下，需求用戶顯示的鎖定數據庫操作要用到的

26、數據，才干使數據庫操作執行得更好，顯示鎖是用戶為數據庫對象設定的。.2共享鎖與排它鎖按鎖級別劃分，可分為共享鎖與排它鎖。共享鎖：共享鎖允許相關資源可以共享，幾個用戶可同時讀同一數據，幾個事務可在同一資源上獲取共享封鎖。共享鎖比排它鎖具有更高的數據并行性。但拙劣的事務設計+共享鎖容易呵斥死鎖或數據更新喪失。排它鎖：事務設置排它鎖后，該事務單獨獲得此資源，另一事務不能在此事務提交之前獲得一樣對象的共享鎖或排它鎖。排它鎖制止相關資源的共享，假設一事務以排它方式封鎖一資源，僅僅該事務可更改該資源，直至釋放排它封鎖。.3DML鎖和DDL鎖按操作劃分，可分為DML鎖、DDL鎖。DML鎖又可以分為：行鎖、表

27、鎖、死鎖。 行鎖：當事務執行數據庫插入、更新、刪除操作時，該事務自動獲得操作表中操作行的排它鎖。 表鎖：當事務獲得行鎖后，此事務也將自動獲得該行的表鎖共享鎖，以防止其它事務進展DDL語句影響記錄行的更新。事務也可以在進展過程中獲得共享鎖或排它鎖，只需當事務顯示運用LOCK TABLE語句顯示的定義一個排它鎖時，事務才會獲得表上的排它鎖，也可運用LOCK TABLE顯示的定義。 死鎖：當兩個事務需求一組有沖突的鎖，而不能將事務繼續下去的話，就出現死鎖。.DDL鎖又可以分為：排它DDL鎖、共享DDL鎖、分析鎖。排它DDL鎖：創建、修正、刪除一個數據庫對象的DDL語句獲得操作對象的排它鎖。如運用al

28、ter table語句時，為了維護數據的完成性、一致性、合法性，該事務獲得一排它DDL鎖。共享DDL鎖：需在數據庫對象之間建立相互依賴關系的DDL語句通常需共享獲得DDL鎖。如創建一個包，該包中的過程與函數援用了不同的數據庫表，當編譯此包時，該事務就獲得了援用表的共享DDL鎖。分析鎖：Oracle運用共享池存儲分析與優化過的SQL語句及PL/SQL程序，使運轉一樣語句的運用速度更快。一個在共享池中緩存的對象獲得它所援用數據庫對象的分析鎖。分析鎖是一種獨特的DDL鎖類型，Oracle運用它追蹤共享池對象及它所援用數據庫對象之間的依賴關系。當一個事務修正或刪除了共享池持有分析鎖的數據庫對象時，Or

29、acle使共享池中的對象作廢，下次在援用這條SQL/PLSQL語句時，Oracle重新分析編譯此語句。.4內部閂鎖內部閂鎖是Oracle中的一種特殊鎖，用于順序訪問內部系統構造。當事務需向緩沖區寫入信息時，為了運用此塊內存區域，Oracle首先必需獲得這塊內存區域的閂鎖，才干向此塊內存寫入信息。.3鎖的方式1方式0：none。2方式1：null 空。1級鎖有：Select，有時會在v$locked_object出現。3方式2：Row-S 行共享RS：共享表鎖，sub share。2級鎖有：Select for update、Lock For Update、Lock Row Share。sele

30、ct for update當對話運用for update子串翻開一個游標時，一切前往集中的數據行都將處于行級Row-X獨占式鎖定，其他對象只能查詢這些數據行，不能進展update、delete或select for update操作。.4方式3：Row-X 行獨占RX：用于行的修正，sub exclusive。3級鎖有：Insert、Update、Delete、Lock Row Exclusive。沒有commit之前插入同樣的一條記錄會沒有反響， 由于后一個3的鎖會不斷等待上一個3的鎖，必需釋放掉上一個才干繼續任務。5方式4：Share 共享鎖S：阻止其他DML操作，share。4級鎖有：C

31、reate Index、Lock Share。locked_mode為2、3、4不影響DMLinsert、delete、update、select等操作， 但DDLalter、drop等操作會提示錯誤。.6方式5：S/Row-X 共享行獨占SRX：阻止其他事務操作，share/sub exclusive。5級鎖有：Lock Share Row Exclusive。詳細來講有主外鍵約束時update/delete . ； 能夠會產生4，5的鎖。7方式6：exclusive 獨占X：獨立訪問運用，exclusive。6級鎖有：Alter table、Drop table、Drop Index、Tr

32、uncate table、Lock Exlusive。數字越大鎖級別越高， 影響的操作越多。.4查看相關信息 Oracle在動態形狀表V$lock中存儲與數據庫中的鎖有關的一切信息。當資源被鎖定時， 可以檢查V$lock表，查看數據庫中的各種情況。.9.5 數據完好性 數據完好性是指數據的正確性和相容性。數據的完好性是為了防止數據庫存在不符合原義的數據，防止錯誤信息輸入和輸出，即數據要遵守由DBA或運用開發者所決議的一組預定義的規那么。.1數據完好性的類型 Oracle運用于關系數據庫的表的數據完好性有以下類型：1在插入或修正表的行時允許不允許包含有空值的列，稱為空與非空規那么。2獨一列值規那

33、么，允許插入或修正的表行在該列上的值獨一。3援用完好性規那么，同關系模型定義相符。4用戶對定義的規那么，是復雜的完好性檢查。.2完好性約束及其優點 完好性約束，是對表的列定義規那么的闡明性方法。 Oracle利用完好性約束機制防止無效的數據進入數據庫的基表，假設任何DML執行結果破壞完好性約束，該語句被回滾并前往上一個錯誤。 .利用完好性約束實施數據完好性規那么有以下優點：1定義或更改表時，不需求程序設計，便很容易地編寫程序并可消除程序性錯誤，其功能是由Oracle控制。所以闡明性完好性約束優于運用代碼和數據庫觸發器。2對表所定義的完好性約束是存儲在數據字典中，所以由任何運用進入的數據都必需遵

34、守與表相關聯的完好性約束。3具有最大的開發才干。當由完好性約束所實施的事務規那么改動時，管理員只需改動完好性約束的定義，一切運用自動地遵守所修正的約束。.3完好性約束的類型 Oracle的DBA和運用開場者對列的值輸入可運用的完好性約束有以下類型：1NOT NULL約束：假設在表的一列的值不允許為空，那么需在該列指定NOT NULL約束。2UNIQUE鍵約束：在表指定的列或組列上不允許兩行具有反復值時，那么需求該列或組列上指定UNIQUE鍵完好性約束。在UNIQUE鍵約束定義中的列或組列稱為獨一鍵。一切獨一完好性約束是用索引方法實施。3PRIMARY KEY約束：在數據庫中每一個表可有一個PRIMARY KEY約束。包含在PRIMARY KEY完好性約束的列或組列稱為主鍵，每個表可有一個主鍵。Oracle運用索引實施PRIMARY KEY約束。 .4FOREIGN KEY約束：在關系數據庫中表可經過公共列相關聯，該規那么控制必需維護的列之間的關系。包含在援用完好性約束定義的列或組列稱為外來鍵。由外來鍵所援用的表中的獨一鍵，稱為援用鍵。包含有