關於Oracle的性能調整一般包括兩個方面一是指Oracle數據庫本身的調整比如SGAPGA的優化設置二是連接Oracle的應用程序以及SQL語句的優化做好這兩個方面的優化就可以使一套完整的Oracle應用系統處於良好的運行狀態
本文主要是把一些Oracle Tuning的文章作了一個簡單的總結力求以實際可操作為目的配合講解部分理論知識使大部分具有一般Oracle知識的使用者能夠對Oracle Tuning有所了解並且能夠根據實際情況對某些參數進行調整關於更加詳細的知識請參見本文結束部分所提及的推薦書籍同時由於該話題內容太多且復雜本文必定有失之偏頗甚至錯誤的地方請不吝賜教並共同進步
SGA的設置
在Oracle Tuning中對SGA的設置是關鍵SGA是指Shared Global Area 或者是 System Global Area 稱為共享全局區或者系統全局區
對於SGA區域內的內存來說是共享的全局的在UNIX 上必須為oracle 設置共享內存段（可以是一個或者多個）因為oracle 在UNIX上是多進程而在WINDOWS上oracle是單進程（多個線程）所以不用設置共享內存段
SGA的各個組成部分
下面用 sqlplus 查詢舉例看一下 SGA 各個組成部分的情況
SQL> select * from v$sga;
NAME VALUE
Fixed Size
Variable Size
Database Buffers
Redo Buffers
或者
SQL> show sga
Total System Global Area bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers bytes
Fixed Size
oracle 的不同平台和不同版本下可能不一樣但對於確定環境是一個固定的值裡面存儲了SGA 各部分組件的信息可以看作引導建立SGA的區域
Variable Size
包含了shared_pool_sizejava_pool_sizelarge_pool_size 等內存設置
Database Buffers
指數據緩沖區在i 中包含db_block_buffer*db_block_sizebuffer_pool_keepbuffer_pool_recycle 三部分內存在i 中包含db_cache_sizedb_keep_cache_sizedb_recycle_cache_sizedb_nk_cache_size
Redo Buffers
指日志緩沖區log_buffer在這裡要額外說明一點的是對於v$parameterv$sgastatv$sga查詢值可能不一樣v$parameter 裡面的值是指用戶在初始化參數文件裡面設置的值v$sgastat是oracle 實際分配的日志緩沖區大小（因為緩沖區的分配值實際上是離散的也不是以block 為最小單位進行分配的）v$sga 裡面查詢的值是在oracle 分配了日志緩沖區後為了保護日志緩沖區設置了一些保護頁通常我們會發現保護頁大小是k(不同環境可能不一樣)參考如下內容
SQL> select substr(name) namesubstr(value) value
from v$parameter where name = log_buffer;
NAME VALUE
log_buffer
SQL> select * from v$sgastat where pool is null;
POOL NAME BYTES
fixed_sga
db_block_buffers
log_buffer
SQL> select * from v$sga;
NAME VALUE
Fixed Size
Variable Size
Database Buffers
Redo Buffers
– =
（以上試驗數據是在 HP B + Oracle 環境下得到的）
SGA的大小設置
在對SGA的結構進行簡單分析以後下面是關於如何根據系統的情況正確設置SGA大小的問題
SGA是一塊內存區域占用的是系統物理內存因此對於一個Oracle應用系統來說SGA決不是越大越好這就需要尋找一個系統優化的平衡點
設置參數前的准備
在設置SGA的內存參數之前我們首先要問自己幾個問題
一物理內存多大
二操作系統估計需要使用多少內存
三數據庫是使用文件系統還是裸設備
四有多少並發連接
五應用是OLTP 類型還是OLAP 類型
根據這幾個問題的答案我們可以粗略地為系統估計一下內存設置那我們現在來逐個問題地討論首先物理內存多大是最容易回答的一個問題然後操作系統估計使用多少內存呢？從經驗上看不會太多通常應該在M 以內（不包含大量進程PCB）
接下來我們要探討一個重要的問題那就是關於文件系統和裸設備的問題這往往容易被我們所忽略操作系統對於文件系統使用了大量的buffer 來緩存操作系統塊這樣當數據庫獲取數據塊的時候雖然SGA 中沒有命中但卻實際上可能是從操作系統的文件緩存中獲取的而假如數據庫和操作系統支持異步IO則實際上當數據庫寫進程DBWR寫磁盤時操作系統在文件緩存中標記該塊為延遲寫等到真正地寫入磁盤之後操作系統才通知DBWR寫磁盤完成對於這部分文件緩存所需要的內存可能比較大作為保守的估計我們應該考慮在 —— 倍內存大小但是如果我們使用的是裸設備則不考慮這部分緩存的問題這樣的情況下SGA就有調大的機會
關於數據庫有多少並發連接這實際上關系到PGA 的大小（MTS 下還有large_pool_size）事實上這個問題應該說還跟OLTP 類型或者OLAP 類型相關對於OLTP類型oracle 傾向於可使用MTS對於OLAP 類型使用獨立模式同時OLAP 還可能涉及到大量的排序操作的查詢這些都影響到我們內存的使用那麼所有的問題綜合起來實際上主要反映在UGA的大小上UGA主要包含以下部分內存設置
SQL> show parameters area_size
NAME TYPE VALUE
bitmap_merge_area_size integer
create_bitmap_area_size integer
hash_area_size integer
sort_area_size integer
SQL>
在這部分內存中我們最關注的通常是sort_area_size這是當查詢需要排序的時候數據庫會話將使用這部分內存進行排序當內存大小不足的時候使用臨時表空間進行磁盤排序由於磁盤排序效率和內存排序效率相差好幾個數量級所以這個參數的設置很重要
當出現大量排序時的磁盤I/O操作時可以考慮增加sort_area_size的值sort_area_size是Oracle用於一次排序所需的最大內存數在排序結束但是結果列返回之前Oracle會釋放sort_area_size大小的內存但是會保留sort_area_retained_size大小的內存知道最後一行結果列返回以後才釋放所有的內存
會導致排序的語句有 SELECT DISTINCT MINUS INTERSECT UNION 和 min（）max（）count（） 操作而不會導致排序的語句有 UPDATE 帶BETWEEN子句的SELECT 等等
這四個參數都是針對會話進行設置的是單個會話使用的內存的大小而不是整個數據庫使用的偶爾會看見有人誤解了這個參數以為是整個數據庫使用的大小這是極其嚴重的錯誤假如設置了MTS則UGA被分配在large_pool_size也就是說放在了共享內存裡面不同進程（線程）之間可以共享這部分內存在這個基礎上我們假設數據庫存在並發執行server process 為 個根據上面我們 個參數在oracle 下的默認值我們來計算獨立模式下PGA 的大致大小由於會話並不會經常使用create_bitmap_area_size bitmap_merge_area_size所以我們通常不對四個參數求和在考慮到除這四個參數外會話所保存的變量堆棧等信息我們估計為M則 個進程最大可能使用M 的PGA
一個經驗公式
現在根據上面這些假定我們來看SGA 實際能達到多少內存在G 的內存的服務器上我們能分配給SGA 的內存大約為—M若是G 的內存大約可以分到G的內存給SGAG 的內存可以分到G的內存給SGA當然我們這裡是以默認的排序部分內存sort_area_size=k進行衡量的假如我們需要調大該參數和hash_area_size等參數然後我們應該根據並發的進程的數量來衡量考慮這個問題
事實上通常我們更習慣通過直觀的公式化來表達這樣的問題
OS 使用內存+SGA+並發執行進程數*(sort_area_size+hash_ara_size+M) < *總內存
(公式是死的系統是活的實際應用的調整不必框公式這不過是一個參考建議)
在我們的實際應用中假如采用的是裸設備我們可適當的增大SGA(如果需要的話)由於目前幾乎所有的操作系統都使用虛擬緩存所以實際上如果就算SGA 設置的比較大也不會導致錯誤而是可能出現頻繁的內存頁的換入與換出(page in/out)在操作系統一級如果觀察到這個現象那麼我們就需要調整內存的設置
各個參數的設置
那麼SGA中的各個參數具體應該按照什麼樣的原則來設置呢下面進行討論
log_buffer
對於日志緩沖區的大小設置通常我覺得沒有過多的建議因為參考LGWR寫的觸發條件之後我們會發現通常超過M意義不是很大作為一個正式系統可能考慮先設置這部分為log_buffer=—M 大小然後針對具體情況再調整
large_pool_size
對於大緩沖池的設置假如不使用MTS建議在—M 足夠了這部分主要用來保存並行查詢時候的一些信息還有就是RMAN 在備份的時候可能會使用到如果設置了MTS則由於UGA部分要移入這裡則需要具體根據session最大數量和 sort_ares_size 等相關會話內存參數的設置來綜合考慮這部分大小的設置一般可以考慮為 session * (sort_area_size + M)這裡要提醒一點不是必須使用MTS我們都不主張使用MTS尤其同時在線用戶數小於的情況下
java_pool_size
假如數據庫沒有使用JAVA我們通常認為保留—M大小足夠了事實上可以更少甚至最少只需要k但具體跟安裝數據庫的時候的組件相關(比如http server)
shared_pool_size
這是迄今為止最具有爭議的一部分內存設置按照很多文檔的描述這部分內容應該幾乎和數據緩沖區差不多大小但實際上情況卻不是這樣的首先我們要考究一個問題那就是這部分內存的作用它是為了緩存已經被解析過的SQL而使其能被重用不再解析這樣做的原因是因為對於一個新的SQL（shared_pool 裡面不存在已經解析的可用的相同的SQL）數據庫將執行硬解析這是一個很消耗資源的過程而若已經存在則進行的僅僅是軟分析（在共享池中尋找相同SQL）這樣消耗的資源大大減少所以我們期望能多共享一些SQL並且如果該參數設置不夠大經常會出現ora錯誤表示為了解析新的SQL沒有可用的足夠大的連續空閒空間這樣自然我們期望該參數能大一些但是該參數的增大卻也有負面的影響因為需要維護共享的結構內存的增大也會使得SQL 的老化的代價更高帶來大量的管理的開銷所有這些可能會導致CPU 的嚴重問題
在一個充分使用綁定變量的比較大的系統中shared_pool_size 的開銷通常應該維持在M 以內除非系統使用了大量的存儲過程函數包比如oracle erp 這樣的應用可能會達到M甚至更高於是我們假定一個G內存的系統可能考慮設置該參數為MG 的系統考慮設置為MG 的系統可以考慮設置為—M
對於一個沒有充分使用或者沒有使用綁定變量系統這可能給我們帶來一個嚴重的問題所謂沒有使用bind var 的SQL我們稱為Literal SQL也就是比如這樣的兩句SQL我們認為是不同的SQL需要進行 次硬解析
select * from EMP where name = TOM;
select * from EMP where name = JERRY;
假如把 TOM 和 JERRY 換做變量V那就是使用了bind var我們可以認為是同樣的SQL 從而能很好地共享共享SQL 本來就是shared_pool_size 這部分內存存在的本意oracle的目的也在於此而我們不使用bind var 就是違背了oracle 的初衷這樣將給我們的系統帶來嚴重的問題當然如果通過在操作系統監控沒有發現嚴重的cpu問題我們如果發現該共享池命中率不高可以適當的增加shred_pool_size但是通常我們不主張這部分內存超過M（特殊情況下可以更大）
事實上可能的話我們甚至要想辦法避免軟分析這在不同的程序語言中實現方式有差異我們也可能通過設置session_cached_cursors 參數來獲得幫助（這將增大PGA）
關於使用綁定變量的話題在下面的應用優化中繼續討論
Data buffer
現在我們來談數據緩沖區在確定了SGA 的大小並分配完了前面部分的內存後其余的都分配給這部分內存通常在允許的情況下我們都嘗試使得這部分內存更大這部分內存的作用主要是緩存 DB BLOCK減少甚至避免從磁盤上獲取數據在i中通常是由db_block_buffers*db_block_size 來決定大小的如果我們設置了buffer_pool_keep 和buffer_pool_recycle則應該加上後面這兩部分內存的大小
可以看出設置SGA時基本上應該掌握的原則是
data buffer 一般可以盡可能的大
shared_pool_size 應該適度
log buffer 在 MB 以內就可以了
假定oracle是 bit 服務器RAM大於G 注意你的PGA的情況則建議
shared_pool_size + data buffer +large_pool_size + java_pool_size < G
再具體化如果M RAM
建議 shared_pool_size = M data buffer = M
如果G RAM
shared_pool_size = M data buffer = M
如果G RAM
shared_pool_size = M data buffer = G
物理內存再大已經跟參數沒有關系了
假定 bit ORACLE
內存G
shared_pool_size = M data buffer = G
內存G
shared_pool_size = M data buffer = G
內存 G
shared_pool_size = MM data buffer = G
bit 與 bit 對SGA的影響
為什麼在上面SGA大小設置的經驗規則中要分 bit Oracle 和 bit Oracle 呢是因為這關系到SGA大小的上限問題在bit的數據庫下通常oracle只能使用不超過G的內存即使我們擁有G的內存但是我們卻只能使用G這是一個莫大的遺憾假如我們安裝bit的數據庫我們就可以使用很大的內存幾乎不可能達到上限但是bit 的數據庫必須安裝在bit 的操作系統上可惜目前windows上只能安裝bit的數據庫我們通過下面的方式可以查看數據庫是 bit 還是 bit
SQL> select * from v$version;
BANNER
Oraclei Enterprise Edition Release Production
PL/SQL Release Production
CORE Production
TNS for bit Windows: Version Production
NLSRTL Version – Production
在UNIX平台下的顯示有所不同明顯可以看出是 bit Oracle 比如在HPUX平台上
SQL> select * from v$version;
BANNER
Oraclei Enterprise Edition Release bit Production
PL/SQL Release Production
CORE Production
TNS for HPUX: Version Production
NLSRTL Version – Production
bit的oracle無論跑在bit或者bit的平台都有SGA的限制的而對於bit的平台只能跑bit的oracle但是在特定的操作系統下可能提供了一定的手段使得我們可以使用超過G 的內存達到G 以上甚至更多由於我們現在一般都使用bit Oracle因此關於如何在bit平台上擴展SGA大小的問題不再贅述
i中相關參數的變化
oracle的版本的更新總是伴隨著參數的變化並且越來越趨向於使得參數的設置更簡單因為復雜的參數設置使得DBA們經常焦頭爛額關於內存這部分的變化我們可以考察下面的參數事實上在i中數據庫本身可以給出一組適合當前運行系統的SGA相關部分的參數調整值（參考V$DB_CACHE_ADVICEV$SHARED_POOL_ADVICE）關於PGA也有相關視圖V$PGA_TARGET_ADVICE 等
Data buffer
i 中保留了i中的參數如設置了新的參數則忽略舊的參數i中用db_cache_size來取代db_block_buffers 用db_keep_cache_size 取代buffer_pool_keep 用db_recycle_cache_size 取代buffer_pool_recycle這裡要注意i 中設置的是實際的緩存大小而不再是塊的數量另外i新增加了db_nk_cache_size這是為了支持在同一個數據庫中使用不同的塊大小而設置的對於不同的表空間可以定義不同的數據塊的大小而緩沖區的定義則依靠該參數的支持其中n 可以為 等不同的值在這裡順便提及的一個參數就是db_block_lru_latches該參數在i中已經成為了保留參數不推薦手工設置
PGA
在i 裡面這部分也有了很大的變化在獨立模式下i已經不再主張使用原來的UGA相關的參數設置而代之以新的參數假如workarea_size_policy=AUTO（缺省）則所有的會話的UGA 共用一大塊內存該內存由 pga_aggregate_target 設置在我們根據前面介紹的方法評估了所有進程可能使用的最大PGA 內存之後我們可以通過在初始化參數中設置這個參數從而不再關心其他 *_area_size 參數
SGA_MAX_SIZE
在i中若設置了SGA_MAX_SIZE則在總和小於等於這個值內可以動態的調整數據緩沖區和共享池的大小
SQL> show parameters sga_max_size
NAME TYPE VALUE
sga_max_size unknown
SQL>
SQL> alter system set db_cache_size = ;
System altered
SQL> alter system set shared_pool_size = ;
System altered
lock_sga = true 的問題
由於幾乎所有的操作系統都支持虛擬內存所以即使我們使用的內存小於物理內存也不能避免操作系統將SGA 換到虛擬內存（SWAP）所以我們可以嘗試使得SGA 鎖定在物理內存中不被換到虛擬內存中這樣減少頁面的換入和換出從而提高性能但在這裡遺憾的是windows 是無法避免這種情況的下面我們來參考在不同的幾個系統下怎麼實現lock_sga
AIX L（AIX 以上）
logon aix as root
cd /usr/samples/kernel
/vmtune (信息如下) v_pingshm已經是
/vmtune S
然後oracle用戶修改initSIDora 中 lock_sga = true
重新啟動數據庫
HP UNIX
Root身份登陸
Create the file /etc/privgroup: vi /etc/privgroup
Add line dba MLOCK to file
As root run the command /etc/setprivgrp f /etc/privgroup:
$/etc/setprivgrp f /etc/privgroup
oracle用戶修改initSIDora中lock_sga=true
重新啟動數據庫
SOLARIS (solaris以上)
i版本以上數據庫默認使用隱藏參數 use_ism = true 自動鎖定SGA於內存中不用設置lock_sga 如果設置 lock_sga =true 使用非 root 用戶啟動數據庫將返回錯誤
WINDOWS
不能設置lock_sga=true可以通過設置pre_page_sga=true使得數據庫啟動的時候就把所有內存頁裝載這樣可能起到一定的作用
應用優化
下面我們從技術的角度入手來探討數據庫優化方面的問題通常作為優化Oracle系統的人或者是DBA其實很多時候對應用並不很了解甚至可以說是完全不了解更不要說對應用程序代碼的了解事實上呢一個系統運行的快或者慢相信大家都明白第一重要的是數據庫的設計然後是應用的設計SQL語句的編寫最後才是數據庫參數的調整和硬件網絡的問題等等所以在我們不了解一個系統的時候來優化數據庫應用不是一個輕松的容易的事情那麼我們第一步應該怎麼做呢？
通常有兩類方法其中一個方法就是我們常用的使用statspack來進行診斷系統的瓶頸所在在statspack中oracle給出了幾乎涵蓋oracle大部分重要內容的信息
另外一種方式就是trace session假如某個session運行很慢或者某個用戶的某個查詢很慢那麼這個時候我們可以通過trace session的方式來診斷到底是慢在哪裡看究竟執行計劃是怎樣的然後在user_dump_dest下根據該session的進程號或者線程號可以找到一個產生的trace文件通過使用tkprof格式化文件之後我們就可以看見很多的統計信息這裡包括了執行計劃parse/fetch等步驟消耗cpu的時間通常我們是觀察query模式下的consistent gets來首先看sql是否使用了索引然後看執行計劃是不是正常是不是有調整的余地當然如果您沒有實際做過的話這些內容說起來很抽象這是在不了解應用和程序下針對特定session的診斷和調整過程
trace session的方式是一種自下而上的方法從sql入手而statspack是自頂向下的方法也就是從宏觀上先診斷數據庫的瓶頸在哪裡然後從瓶頸入手來做調整這個習慣上又可以稱為通過等待事件（wait event）入手的方法
使用statspack
statspack是一個性能診斷工具首先發布於Oracle版本在版本中功能得到加強Statspack除了查找實例中的性能問題外還可以查找應用程序中高負荷的SQL語句很容易確定Oracle 數據庫的瓶頸所在並且記錄數據庫性能狀態
在數據庫中Statspack 的腳本位於$ORACLE_HOME/RDBMS/ADMIN 目錄下對於ORACLE是一組以stat 開頭的文件對於ORACLE是一組以sp 開頭的文件
在Statspack 發布之前我們通常能夠使用診斷數據庫的工具是兩個腳本UTLBSTATSQL 和UTLESTATSQLBSTAT/ESTAT 是一個非常簡單的性能診斷工具UTLBSTAT 獲得開始時很多V$視圖的快照UTLESTAT 通過先前的快照和當前視圖生成一個報表
該報表實際上相當於statspack 中的兩個采樣點
Statspack 通過連續的采樣能夠給我們提供至關重要的趨勢分析數據這是一個巨大的進步能夠使用Statspack 的環境我們就盡量不要使用BSTAT/ESTAT 的方式來診斷數據庫問題
安裝statapack
§ 步驟一
為了能夠順利安裝和運行Statspack 首先需要設置以下兩個系統參數
job_queue_processes
為了能夠建立自動任務執行數據收集該參數需要大於你可以在初試化參數文件中修改該參數(使該參數在重起後以然有效)
該參數可以在系統級動態修改(重起後失效)
SQL> alter system set job_queue_processes = ;
System altered
在Oraclei 當中可以指定范圍如 both這樣該修改在當前及之後保持有效(僅當你使用spfile 時如果在i 中仍然使用pfile那麼更改方法同i 相同):
SQL> alter system set job_queue_processes = scope=both;
System altered
timed_statistics
收集操作系統的計時信息這些信息可被用來顯示時間等統計信息優化數據庫和 SQL 語句要防止因從操作系統請求時間而引起的開銷請將該值設置為False
使用statspack 收集統計信息時建議將該值設置為 TRUE否則收集的統計信息大約只能起到%的作用將timed_statistics 設置為True 所帶來的性能影響與好處相比是微不足道的
該參數使收集的時間信息存儲在在V$SESSTATS 和V$SYSSTATS 等動態性能視圖中
timed_statistics 參數也可以在實例級進行更改
SQL> alter system set timed_statistics = true;
System altered
如果你擔心一直啟用timed_statistics 對於性能的影響你可以在使用statspack 之前在system 更改采樣過後把該參數動態修改成false
§ 步驟二
需要單獨為statspack創建一個存儲數據的表空間如果采樣間隔較短周期較長打算長期使用那麼可能需要一個大一點的表空間如果每個半個小時采樣一次連續采樣一周數據量是很大的下面的例子中創建了一個M 的測試表空間
注意: 這裡創建的表空間不能太小如果太小的話創建對象會失敗建議至少建立M 表空間
SQL> create tablespace perfstat
datafile /oracle/oradata/oradata/res/perfstatdbf
size M;
Tablespace created
§ 步驟三
在 sqlplus 中用internal 身份登陸或者擁有SYSDBA(connect / as sysdba)權限的用戶登陸
注: 在Oraclei 中不存在internal 用戶可以使用sys 用戶以sysdba 身份連接
先轉到$ORACLE_HOME/RDBMS/ADMIN 目錄檢查安裝腳本是否存在同時我們執行腳本也可以方便些
$ cd $ORACLE_HOME/rdbms/admin
$ ls l sp*sql
rwrr oracle other Feb spautosql
rwrr oracle other Jun spcpkgsql
rwrr oracle other Feb spcreatesql
rwrr oracle other Jun spctabsql
rwrr oracle other Jun spcusrsql
rwrr oracle other Jun spdropsql
rwrr oracle other Jun spdtabsql
rwrr oracle other Jun spdusrsql
rwrr oracle other Jun sppurgesql
rwrr oracle other Jul spreportsql
rwrr oracle other Jun sptruncsql
rwrr oracle other Jun spupsql
$
接下來我們就可以開始安裝Statspack 了在Oracle 版本中運行statscresql; 在Oracle 版本中運行spcreatesql
這期間會提示你輸入缺省表空間和臨時表空間的位置輸入我們為 perfstat 用戶創建的表空間和你的臨時表空間安裝腳本會自動創建perfstat 用戶
$ sqlplus
SQL*Plus: Release Production on Sat Jul ::
(c) Copyright Oracle Corporation All rights reserved
Enter username: internal
Connected to:
Oraclei Enterprise Edition Release Production
With the Partitioning option
JServer Release Production
SQL>
SQL> @spcreate
Installing Required Packages
Package created
Grant succeeded
View created
Package body created
Package created
Synonym dropped
Synonym created
……
Specify PERFSTAT users default tablespace
Enter value for default_tablespace: perfstat
Using perfstat for the default tablespace
User altered
User altered
Specify PERFSTAT users temporary tablespace
Enter value for temporary_tablespace: temp
Using temp for the temporary tablespace
User altered
NOTE:
SPCUSR complete Please check spcusrlis for any errors
……
如果安裝成功你可以接著看到如下的輸出信息
…
Creating Package STATSPACK
Package created
No errors
Creating Package Body STATSPACK
Package body created
No errors
NOTE:
SPCPKG complete Please check spcpkglis for any errors
可以查看lis 文件查看安裝時的錯誤信息
§ 步驟四
如果安裝過程中出現錯誤那麼可以運行spdropsql 腳本來刪除這些安裝腳本建立的對象然後重新運行spcreatesql來創建這些對象
SQL> @spdrop
Dropping old versions (if any)
Synonym dropped
Sequence dropped
Synonym dropped
Table dropped
Synonym dropped
View dropped
……
NOTE:
SPDUSR complete Please check spdusrlis for any errors
（以上的安裝過程描述是在 HP + Oracle 平台上得到的）
測試statspack
運行statspacksnap 可以產生系統快照運行兩次然後執行spreportsql 就可以生成一個基於兩個時間點的報告
如果一切正常說明安裝成功
SQL>execute statspacksnap
PL/SQL procedure successfully completed
SQL>execute statspacksnap
PL/SQL procedure successfully completed
SQL>@spreportsql
可是有可能你會得到以下錯誤
SQL> exec statspacksnap;
BEGIN statspacksnap; END;
*
ERROR at line :
ORA: inserted value too large for column
ORA: at PERFSTATSTATSPACK line
ORA: at PERFSTATSTATSPACK line
ORA: at PERFSTATSTATSPACK line
ORA: at line
這是Oracle 的一個BugBug 號
該Bug 自 後修正
這個問題只會出現在多位的字符集 需要修改spcpkgsql 腳本$ORACLE_HOME/rdbms/admin/spcpkgsql將substr 修改為 substrb然後重新運行該腳本
該腳本錯誤部分
select l_snap_id
p_dbid
p_instance_number
substr(sql_text)
．．．．．．．．．．．
substr 會將多位的字符 當作一個bytesubstrb 則會當作多個byte在收集數據時 statpack 會將 top 的 sql 前 個字節 存入數據表中若在SQL 的前 個字有中文就會出現此錯誤
注意運行 spcpkgsql 也需要以 internal 用戶登錄 sqlplus
生成statspack報告
調用spreportsql 可以生成分析報告
當調用spreprotsql 時系統首先會查詢快照列表然後要求你選擇生成報告的開始快照ID(begin_snap)和結束快照ID(end_snap)生成一個報告
為了生成一個report我們至少需要兩次采樣:
SQL> @spreport
DB Id DB Name Inst Num Instance
RES res
Completed Snapshots
Snap Snap
Instance DB Name Id Snap Started Level Comment
res RES Jul :
Jul :
Jul :
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:
Begin Snapshot Id specified:
Enter value for end_snap:
End Snapshot Id specified:
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp__ To use this name
press to continue otherwise enter an alternative
Enter value for report_name: reptxt
……
End of Report
在運行 spreportsql 生成 statspack 報告的過程中會有三個地方提示用戶輸入
開始快照ID
結束快照ID
輸出報告文件的文件名缺省的文件名是sp__
上面輸入的開始快照ID是開始快照ID是輸出報告文件的文件名是reptxt
成功運行一次 statspacksnap 就會產生一個 snapshot 在生成 statspack 報告的時候就可以看到這個 snap id 和 snap 運行的時間運行 statspacksnap 就是上面所說的采樣statspack 報告是分析兩個采樣點之間各種情況
刪除歷史快照數據
前面講過成功運行一次 statspacksnap 就會產生一個 snapshot 這個 snapshot 的基本信息是存放在 PERFSTATstats$snapshot 表中的生成 statspack報告時會查詢該表的數據供用戶選擇准備分析的 snapshot 如果運行 statspacksnap 次數多了以後該表的數據也會增加歷史數據會影響正常運行的效果因此需要定時清理一下歷史快照數據
刪除stats$snapshot 數據表中的相應數據其他表中的數據會相應的級連刪除
SQL> select max(snap_id) from stats$snapshot;
MAX(SNAP_ID)
SQL> delete from stats$snapshot where snap_id < = ;
rows deleted
你可以更改snap_id 的范圍以保留你需要的數據
在以上刪除過程中你可以看到所有相關的表都被鎖定
SQL> select aobject_idaoracle_username bobject_name
from v$locked_object adba_objects b
where aobject_id = bobject_id
/
OBJECT_ID ORACLE_USERNAME OBJECT_NAME
PERFSTAT SNAP$
PERFSTAT STATS$LIBRARYCACHE
PERFSTAT STATS$ROLLSTAT
PERFSTAT STATS$SGA
PERFSTAT STATS$PARAMETER
PERFSTAT STATS$SQL_STATISTICS
PERFSTAT STATS$SQL_SUMMARY
PERFSTAT STATS$ENQUEUESTAT
PERFSTAT STATS$WAITSTAT
PERFSTAT STATS$BG_EVENT_SUMMARY
PERFSTAT STATS$SYSTEM_EVENT
PERFSTAT STATS$SYSSTAT
PERFSTAT STATS$SGASTAT
PERFSTAT STATS$ROWCACHE_SUMMARY
PERFSTAT STATS$BUFFER_POOL_STATISTICS
PERFSTAT STATS$LATCH_MISSES_SUMMARY
PERFSTAT STATS$SNAPSHOT
PERFSTAT STATS$FILESTATXS
PERFSTAT STATS$LATCH
PERFSTAT JOB$
rows selected
Oracle 還提供了系統腳本用於Truncate 這些統計信息表這個腳本名字是: sptruncsql (ii 都相同)
該腳本主要內容如下裡面看到的就是statspack 相關的所有系統表
truncate table STATS$FILESTATXS;
truncate table STATS$LATCH;
truncate table STATS$LATCH_CHILDREN;
truncate table STATS$LATCH_MISSES_SUMMARY;
truncate table STATS$LATCH_PARENT;
truncate table STATS$LIBRARYCACHE;
truncate table STATS$BUFFER_POOL_STATISTICS;
truncate table STATS$ROLLSTAT;
truncate table STATS$ROWCACHE_SUMMARY;
truncate table STATS$SGA;
truncate table STATS$SGASTAT;
truncate table STATS$SYSSTAT;
truncate table STATS$SESSTAT;
truncate table STATS$SYSTEM_EVENT;
truncate table STATS$SESSION_EVENT;
truncate table STATS$BG_EVENT_SUMMARY;
truncate table STATS$WAITSTAT;
truncate table STATS$ENQUEUESTAT;
truncate table STATS$SQL_SUMMARY;
truncate table STATS$SQL_STATISTICS;
truncate table STATS$SQLTEXT;
truncate table STATS$PARAMETER;
delete from STATS$SNAPSHOT;
delete from STATS$DATABASE_INSTANCE;
commit;
一些重要腳本
．通過導出保存及共享數據
在診斷系統問題時可能需要向專業人士提供原始數據這時我們可以導出Statspack 表數據
其中我們可能用到spuexppar
其內容主要為
file=spuexpdmp log=spuexplog compress=y grants=y indexes=y rows=y constraints=y owner=PERFSTAT consistent=y
我們可以導出如下
exp userid=perfstat/my_perfstat_password parfile=spuexppar
．刪除數據
spdropsql 在執行時主要調用兩個腳本: spdtabsql spdusrsql
前者刪除表及同義詞等數據後者刪除用戶
．Oracle 中新增加的腳本
） 用於升級statspack 對象的腳本這些腳本需要以具有SYSDBA 權限的用戶運行 升級前請先
備份存在的Schema 數據:
spupsql: 用於升級 版本的模式至 版本
spupsql: 如果從Statspack 升級需要運行這個腳本
spupsql: 從Statspack 升級需要運行這個腳本然後運行spupsql
） sprepsqlsql 用於根據給定的SQL Hash 值生成SQL 報告
調整statspack的收集門限
Statspack 有兩種類型的收集選項
．級別（level）控制收集數據的類型
Statspack 共有三種快照級別默認值是
a level : 一般性能統計包括等待事件系統事件系統統計回滾段統計行緩存SGA會話鎖緩沖池統計等等
b level : 增加SQL 語句除了包括level 的所有內容還包括SQL 語句的收集收集結果記錄在stats$sql_summary 中
c level : 增加子鎖存統計包括level 的所有內容並且還會將附加的子鎖存存入stats$lathc_children 中在使用這個級別時需要慎重建議在Oracle support 的指導下進行
可以通過statspack 包修改缺省的級別設置
SQL>execute statspacksnap(i_snap_level=>i_modify_parameter=>true);
通過這樣的設置以後的收集級別都將是 級
如果你只是想本次改變收集級別可以忽略i_modify_parameter 參數
SQL>execute statspacksnap(i_snap_level=>);
．快照門限設置收集的數據的阈值
快照門限只應用於stats$sql_summary 表中獲取的SQL 語句
因為每一個快照都會收集很多數據每一行都代表獲取快照時數據庫中的一個SQL 語句所以stats$sql_summary 很快就會成為Statspack 中最大的表
門限存儲在stats$statspack_parameter 表中讓我們了結一下各種門限
a executions_th 這是SQL 語句執行的數量(默認值是)
b disk_reads_tn 這是SQL 語句執行的磁盤讀入數量（默認值是）
c parse_calls_th 這是SQL 語句執行的解析調用的數量（默認值是）
d buffer_gets_th 這是SQL 語句執行的緩沖區獲取的數量（默認值是）
任何一個門限值超過以上參數就會產生一條記錄
通過調用statspackmodify_statspack_parameter 函數我們可以改變門限的默認值
例如
SQL>execute statspackmodify_statspack_parameter(i_buffer_gets_th=>i_disk_reads_th=>;
對statspack報告的分析
從上面的描述可以看出產生一個statspack報告是比較簡單的但是如何讀懂statspack報告卻不是那麼容易需要對Oracle的體系架構內存結構等待事件以及應用系統有充分的了解加上不斷的實踐才能基本讀懂statspack報告並且從報告中找到調整優化Oracle的途徑
下面接合一個實際的statspack報告大致分析一下
基本信息分析
DB Name DB Id Instance Inst Num Release OPS Host
RES res NO res
Snap Id Snap Time Sessions
Begin Snap: Jul ::
End Snap: Jul ::
Elapsed: (mins)
Statspack報告首先描述了數據庫的基本情況比如數據庫名實例名實例個數oracle版本號等等然後是該報告的開始快照和結束快照的信息包括 snap id snap time 等等最後是該報告經過的時間跨度單位是分鐘(mins)
Cache Sizes
~~~~~~~~~~~
db_block_buffers: log_buffer:
db_block_size: shared_pool_size:
然後描述了Oracle內存結構中幾個重要的參數
內存信息分析
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
Redo size:
Logical reads:
Block changes:
Physical reads:
Physical writes:
User calls:
Parses:
Hard parses:
Sorts:
Logons:
Executes:
Transactions:
% Blocks changed per Read: Recursive Call %:
Rollback per transaction %: Rows per Sort:
Redo size: 是日志的生成量分為每秒和每事務所產生的通常在很繁忙的系統中日志生成量可能達到上百k甚至幾百k
Logical reads: 邏輯讀實際上就是logical IO=buffer gets表示的含義我們可以這樣認為block在內存中我們每一次讀一塊內存就相當於一次邏輯讀
Parses 和 Hard parses: Parse 和 hard parse通常是很容易出問題的部分%的系統的慢都是由於這個原因所導致的
所謂parse分soft parse 和hard parsesoft parse是當一條sql傳進來後需要在shared pool中找是否有相同的sql如果找到了那就是soft parse如果沒有找著那就開始hard parse實際上hard parse主要是檢查該sql所涉及到的所有的對象是否有效以及權限等關系hard parse之後才根據rule/cost模式生成執行計劃再執行sql
而hard parse的根源基本都是由於不使用bind var所導致的不使用bind var違背了oracle的shared pool的設計的原則違背了這個設計用來共享的思想這樣導致shared_pool_size裡面命中率下降因此不使用bind var將導致cpu使用率的問題極有使得性能急劇下降
還有就是為了維護internal structure需要使用latchlatch是一種Oracle低級結構用於保護內存資源是一種內部生命周期很短的lock大量使用latch將消耗大量的cpu資源
Sorts: 表示排序的數量
Executes: 表示執行次數
Transactions: 表示事務數量
Rollback per transaction %: 表示數據庫中事務的回退率如果不是因為業務本身的原因通常應該小於%為好回退是一個很消耗資源的操作
Instance Efficiency Percentages (Target %)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: Redo NoWait %:
Buffer Hit %: Inmemory Sort %:
Library Hit %: Soft Parse %:
Execute to Parse %: Latch Hit %:
Parse CPU to Parse Elapsd %: % NonParse CPU:
Buffer Hit %: 數據緩沖區命中率通常應該大於%
Library Hit %: libaray cache的命中率通常應該大於%
Inmemory Sort %: 排序在內存的比例如果這個比例過小可以考慮增大sort_area_size使得排序在內存中進行而不是在temp表空間中進行
Soft Parse %: 軟解析的百分比這個百分比也應該很大才好因為我們要盡量減少hard parse soft parse 百分比=soft/(soft+hard)
Execute to Parse %: 這個數字也應該是越大越好接近%最好有些報告中這個值是負的看上去很奇怪事實上這表示一個問題sql如果被age out的話就可能出現這種情況也就是sql老化或執行alter system flush shared_pool等
Shared Pool Statistics Begin End
Memory Usage %:
% SQL with executions>:
% Memory for SQL w/exec>:
% SQL with executions>: 這個表示SQL被執行次數多於一次的比率也應該大為好小則表示很多sql只被執行了一次說明沒有使用bind var
等待事件分析
接下來statspack報告中描述的是等待事件（Wait Events）這是Oracle中比較復雜難懂的概念
Oracle 的等待事件是衡量Oracle 運行狀況的重要依據及指標
等待事件的概念是在Oracle 中引入的大致有 個等待事件在Oracle 中這個數目增加到了大約 個在Oraclei 中大約有 個事件在Oraclei 中大約有 個等待事件
主要有兩種類別的等待事件即空閒（idle）等待事件和非空閒（nonidle）等待事件
空閒事件指Oracle 正等待某種工作在診斷和優化數據庫的時候我們不用過多注意這部分事件
常見的空閒事件有:
? dispatcher timer
? lock element cleanup
? Null event
? parallel query dequeue wait
? parallel query idle wait Slaves
? pipe get
? PL/SQL lock timer
? pmon timer pmon
? rdbms ipc message
? slave wait
? smon timer
? SQL*Net break/reset to client
? SQL*Net message from client
? SQL*Net message to client
? SQL*Net more data to client
? virtual circuit status
? client message
非空閒等待事件專門針對Oracle 的活動指數據庫任務或應用運行過程中發生的等待這些等待事件是我們在調整數據庫的時候應該關注與研究的
一些常見的非空閒等待事件有:
? db file scattered read
? db file sequential read
? buffer busy waits
? free buffer waits
? enqueue
? latch free
? log file parallel write
? log file sync
下面接合statspack中的一些等待事件進行講述
Top Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
db file scattered read
db file parallel write
log file parallel write
direct path write
control file parallel write
db file scattered read DB文件分散讀取這個等待事件很常見經常在top中出現這表示一次從磁盤讀數據進來的時候讀了多於一個block的數據而這些數據又被分散的放在不連續的內存塊中因為一次讀進來的是多於一個block的
通常來說我們可以認為是全表掃描類型的讀因為根據索引讀表數據的話一次只讀一個block如果這個數字過大就表明該表找不到索引或者只能找到有限的索引可能是全表掃描過多需要檢查sql是否合理的利用了索引或者是否需要建立合理的索引
當全表掃描被限制在內存時它們很少會進入連續的緩沖區內而是分散於整個緩沖存儲器中盡管在特定條件下執行全表掃描可能比索引掃描更有效但如果出現這種等待時最好檢查一下這些全表掃描是否必要是否可以通過建立合適的索引來減少對於大表全表掃描所產生的大規模數據讀取
對於經常使用的小表應該盡量把他們pin 在內存中避免不必要的老化清除及重復讀取
db file sequential read DB文件連續讀取通常顯示單個塊的讀取（通常指索引讀取）表示的是讀進磁盤的block被放在連續的內存塊中
事實上大部分基本代表著單個block的讀入可以說象征著 IO 或者說通過索引讀入的比較多 因為一次IO若讀進多個的block放入連續的內存塊的幾率是很小的分布在不同block的大量記錄被讀入就會遇到此事件因為根據索引讀數據的話假設條記錄根據索引不算索引本身的讀而根據索引每個值去讀一下表數據理論上最多可能產生 buffer gets而如果是full table scan則條數據完全可能在一個block裡面則幾乎一次就讀過這個block了就會產生這麼大的差異
這種等待的數目很多時可能顯示表的連接順序不佳或者不加選擇地進行索引
對於高級事務處理（hightransaction）調整良好（welltuned）的系統這一數值很大是很正常的但在某些情況下它可能暗示著系統中存在問題
你應當將這一等待統計量與Statspack 報告中的已知問題（如效率較低的SQL）聯系起來檢查索引掃描以保證每個掃描都是必要的並檢查多表連接的連接順序
DB_CACHE_SIZE 也是這些等待出現頻率的決定因素有問題的散列區域（Hasharea）連接應當出現在PGA 內存中但它們也會消耗大量內存從而在順序讀取時導致大量等待它們也可能以直接路徑讀／寫等待的形式出現
Free Buffer Wait: 釋放緩沖區
這種等待表明系統正在等待內存中的緩沖因為內存中已經沒有可用的緩沖空間了如果所有SQL 都得到了調優這種等待可能表示你需要增大DB_BUFFER_CACHE釋放緩沖區等待也可能表示不加選擇的SQL 導致數據溢出了帶有索引塊的緩沖存儲器沒有為等待系統處理的特定語句留有緩沖區
這種情況通常表示正在執行相當多數量的DML（插入／更新／刪除）並且可能說明DBWR 寫的速度不夠快緩沖存儲器可能充滿了相同緩沖器的多個版本從而導致效率非常低為了解決這個問題可能需要考慮增加檢查點利用更多的DBWR 進程或者增加物理磁盤的數量
Buffer Busy Wait: 緩沖區忙
該等待事件表示正在等待一個以unshareable方式使用的緩沖區或者表示當前正在被讀入buffer cache也就是當進程想獲取或者操作某個block的時候卻發現被別的進程在使用而出現等待一般來說Buffer Busy Wait不應大於%
檢查緩沖等待統計部分（或V$WAITSTAT）看一下等待是否位於段頭如果是可以考慮增加自由列表（freelist對於Oraclei DMT）或者增加freelist groups
其修改語法為
SQL> alter table sp_item storage (freelists );
Table altered
對於Oraclei而言增加freelist參數在很多時候可以明顯緩解等待如果使用LMT也就是Local Manangement Tablespace區段的管理就相對簡單還可以考慮修改數據塊的pctused\pctfree值比如增大pctfree可以擴大數據的分布在某種程度上就可以減少熱點塊的競爭
如果這一等待位於undo header可以通過增加回滾段（rollback segment）來解決緩沖區的問題
如果等待位於undo block上我們可能需要檢查相關應用適當減少大規模的一致性讀取或者降低一致性讀取（consistent read）的表中的數據密度或者增大DB_CACHE_SIZE如果等待處於data block可以考慮將頻繁並發訪問的表或數據移到另一數據塊或者進行更大范圍的分布（可以增加pctfree 值 擴大數據分布減少競爭）以避開這個熱點數據塊或者可以考慮增加表中的自由列表或使用本地化管理的表空間（Locally Managed Tablespaces）
如果等待處於索引塊應該考慮重建索引分割索引或使用反向鍵索引反向鍵索引在很多情況下可以極大地緩解競爭其原理有點類似於hash分區的功效反向鍵索引（reverse key index）常建在一些值是連續增長的列上例如列中的值是由sequence產生的
為了防止與數據塊相關的緩沖忙等待也可以使用較小的塊在這種情況下單個塊中的記錄就較少所以這個塊就不是那麼繁忙或者可以設置更大的pctfree使數據擴大物理分布減少記錄間的熱點競爭
在執行DML （insert/update/ delete）時Oracle向數據塊中寫入信息對於多事務並發訪問的數據表關於ITL的競爭和等待可能出現為了減少這個等待可以增加initrans使用多個ITL槽
以下是一個生產系統v$waitstat 試圖所顯示的等待信息:
SQL> select * from v$waitstat where count<> or time <>;
CLASS COUNT TIME
data block
undo header
undo block
latch free: latch釋放
latch 是一種低級排隊機制用於保護SGA 中共享內存結構
latch就像是一種快速地被獲取和釋放的內存鎖latch用於防止共享內存結構被多個用戶同時訪問如果latch不可用就會記錄latch釋放失敗(latch free miss)
有兩種與闩有關的類型
■ 立刻
■ 可以等待
假如一個進程試圖在立刻模式下獲得闩而該闩已經被另外一個進程所持有如果該闩不能立刻可用的話那麼該進程就不會為獲得該闩而等待它將繼續執行另一個操作
大多數latch 問題都與以下操作相關沒有很好的是用綁定變量（library cache latch）重作生成問題（redo allocation latch）緩沖存儲器競爭問題（cache buffers LRU chain）以及buffer cache中的存在熱點塊（cache buffers chain）
通常我們說如果想設計一個失敗的系統不考慮綁定變量這一個條件就夠了對於異構性極強的系統不使用綁定變量的後果是極其嚴重的
另外也有一些latch 等待與bug 有關應當關注Metalink 相關bug 的公布及補丁的發布
當latch miss ratios大於%時就應當研究這一問題
Oracle 的 latch 機制是競爭其處理類似於網絡裡的CSMA/CD所有用戶進程爭奪latch 對於願意等待類型（willingtowait）的latch如果一個進程在第一次嘗試中沒有獲得latch那麼它會等待並且再嘗試一次如果經過_spin_count 次爭奪不能獲得latch 然後該進程轉入睡眠狀態持續一段指定長度的時間然後再次醒來按順序重復以前的步驟在i/i 中默認值是 _spin_count=如果SQL語句不能調整在版本以上Oracle提供了一個新的初始化參數 CURSOR_SHARING可以通過設置CURSOR_SHARING = force 在服務器端強制綁定變量設置該參數可能會帶來一定的副作用對於Java的程序有相關的bug具體應用應該關注Metalink的bug公告
enqueue
enqueue 是一種保護共享資源的鎖定機制該鎖定機制保護共享資源如記錄中的數據以避免兩個人在同一時間更新同一數據enqueue 包括一個排隊機制即FIFO（先進先出）排隊機制
Enqueue 等待常見的有STHW TX TM 等ST enqueue 用於空間管理和字典管理的表空間（DMT）的分配對於支持LMT 的版本可以考慮使用本地管理表空間對於Oraclei因為相關bug 不要把臨時表空間設置為LMT 或者考慮預分配一定數量的區
HW enqueue 指段的高水位標記相關等待手動分配適當區段可以避免這一等待
TX 是最常見的enqueue 等待TX enqueue 等待通常是以下三個問題之一產生的結果
第一個問題是唯一索引中的重復索引你需要執行提交（commit）/回滾（rollback）操作來釋放enqueue第二個問題是對同一位圖索引段的多次更新因為單個位圖段可能包含多個行地址（rowid）所以當多個用戶試圖更新同一段時等待出現直到提交或回滾 enqueue 釋放
第三個問題也是最可能發生的問題是多個用戶同時更新同一個塊如果沒有自由的ITL 槽就會發生塊級鎖定通過增大initrans 和/或maxtrans 以允許使用多個ITL 槽或者增大表上的pctfree值就可以很輕松地避免這種情況
TM enqueue 在DML 期間產生以避免對受影響的對象使用DDL如果有外鍵一定要對它們進行索引以避免這種常見的鎖定問題
Log Buffer Space: 日志緩沖空間
當你將日志緩沖（log buffer）產生重做日志的速度比LGWR 的寫出速度快或者是當日志轉換（log switch）太慢時就會發生這種等待為解決這個問題可以增大日志文件的大小或者增加日志緩沖器的大小
另外一個可能的原因是磁盤I/O 存在瓶頸可以考慮使用寫入速度更快的磁盤
log file switch (archiving needed)
這個等待事件出現時通常是因為日志組循環寫滿以後第一個日志歸檔尚未完成出現該等待可能是 IO 存在問題
解決辦法
可以考慮增大日志文件和增加日志組
移動歸檔文件到快速磁盤
調整log_archive_max_processes
log file switch (checkpoint incomplete): 日志切換（檢查點未完成）
當你的日志組都寫完以後LGWR 試圖寫第一個log file如果這時數據庫沒有完成寫出記錄在第一個log file 中的dirty 塊時（例如第一個檢查點未完成）該等待事件出現
該等待事件說明你的日志組過少或者日志文件過小
你可能需要增加你的日志組或日志文件大小
Log File Switch: 日志文件轉換
所有的提交請求都需要等待日志文件轉換（必要的歸檔）或日志文件轉換（chkpt不完全）確保歸檔磁盤未滿並且速度不太慢DBWR 可能會因為輸入/輸出（I／O）操作而變得很慢你可能需要增加更多或更大的重做日志而且如果DBWxR 是問題症結所在的話可能需要增加數據庫書寫器
log file sync: 日志文件同步
當一個用戶提交或回滾數據時LGWR 將session 會話的重做由redo buffer 寫入到重做日志中
log file sync 必須等待這一過程成功完成(Oracle 通過寫redo log file 保證commit 成功的數據不丟失)這個事件說明提交可能過於頻繁批量提交可以最大化LGWR 的效率過分頻繁的提交會引起LGWR頻繁的激活擴大了LGWR 的寫代價
為了減少這種等待事件可以嘗試每次提交更多的記錄
將重做日志置於較快的磁盤上或者交替使用不同物理磁盤上的重做日志以降低歸檔對LGWR的影響
對於軟RAID一般來說不要使用RAID RAID 對於頻繁寫入得系統會帶來較大的性能損失可以考慮使用文件系統直接輸入/輸出或者使用裸設備（raw device）這樣可以獲得寫入的性能提高
log file single write
該事件僅與寫日志文件頭塊相關通常發生在增加新的組成員和增進序列號時頭塊寫單個進行因為頭塊的部分信息是文件號每個文件不同更新日志文件頭這個操作在後台完成一般很少出現等待無需太多關注
log file parallel write
從log buffer 寫redo 記錄到redo log 文件主要指常規寫操作(相對於log file sync)
如果你的Log group 存在多個組成員當flush log buffer 時寫操作是並行的這時候此等待事件可能出現
盡管這個寫操作並行處理直到所有I/O 操作完成該寫操作才會完成(如果你的磁盤支持異步IO或者使用IO SLAVE那麼即使只有一個redo log file member也有可能出現此等待)
這個參數和log file sync 時間相比較可以用來衡量log file 的寫入成本通常稱為同步成本率
control file parallel write: 控制文件並行寫
當server 進程更新所有控制文件時這個事件可能出現
如果等待很短可以不用考慮如果等待時間較長檢查存放控制文件的物理磁盤I/O 是否存在瓶頸
多個控制文件是完全相同的拷貝用於鏡像以提高安全性對於業務系統多個控制文件應該存放在不同的磁盤上一般來說三個是足夠的如果只有兩個物理硬盤那麼兩個控制文件也是可以接受的在同一個磁盤上保存多個控制文件是不具備實際意義的
減少這個等待可以考慮如下方法
減少控制文件的個數(在確保安全的前提下)
如果系統支持使用異步IO
轉移控制文件到IO 負擔輕的物理磁盤
control file sequential read/ control file single write
控制文件連續讀/控制文件單個寫
對單個控制文件I/O 存在問題時這兩個事件會出現
如果等待比較明顯檢查單個控制文件看存放位置是否存在I/O 瓶頸
使用查詢獲得控制文件訪問狀態
select P from V$SESSION_WAIT
where EVENT like control file% and STATE=WAITING;
解決辦法
移動有問題的控制文件到快速磁盤
如果系統支持啟用異步I/O
direct path write: 直接路徑寫
該等待發生在等待確認所有未完成的異步I/O 都已寫入磁盤
你應該找到I/O 操作頻繁的數據文件調整其性能
也有可能存在較多的磁盤排序臨時表空間操作頻繁可以考慮使用Local 管理表空間分成多個小文件寫入不同磁盤或者裸設備
SQL*Net message from dblink
該等待通常指與分布式處理（從其他數據庫中SELECT）有關的等待
這個事件在通過DBLINKS 聯機訪問其他數據庫時產生如果查找的數據多數是靜態的可以考慮移動這些數據到本地表並根據需要刷新通過快照或者物化視圖來減少跨數據庫的訪問會在性能上得到很大的提高
slave wait: 從屬進程等
Slave Wait 是Slave I/O 進程等待請求是一個空閒參數一般不說明問題
High Load SQL 分析
對於一個特定的應用程序或者系統來講要調整優化其性能最好的方法是檢查程序的代碼和用戶使用的SQL語句
如果使用了 level 級別的 snapshot 那麼statspack生成的報告中就會顯示系統中高負荷SQL語句（High Load SQL）的信息而其詳細信息可以在 stats$sql_summary 表中查到缺省情況下 snapshot 的級別是 level
按照 buffer gets physical reads executions memory usage and version count 等參數的降序排列順序把SQL語句分為幾個部分羅列在報告中
報告的其他部分
statspack報告的其他部分包括了 Instance Activity StatsTablespace IO StatsBuffer Pool StatisticsBuffer wait StatisticsRollback Segment StatsLatch ActivityDictionary Cache StatsLibrary Cache ActivitySGA breakdown difference 以及 initora 參數等等目前本文不對這些內容進行詳細討論請參加其他詳細文檔
trace session
基於成本的優化器技術內幕
Oracle基於成本的優化器（Oracles costbased SQL optimizer 簡稱CBO）是Oracle裡面非常復雜的一個部分 它決定了Oracle裡面每個SQL的執行路徑CBO是一項評價SQL語句和產生最好執行計劃的具有挑戰性的工作所以也使它成Oracle最復雜的軟件組成部分
眾所周知SQL的執行計劃幾乎是Oracle性能調整最重要的方面了所以想要學會如何調整Oracle數據庫的性能就要學會如何對SQL進行調整就需要深入透徹理解CBO CBO的執行路徑取決於一些外部因素內部的Oracle統計數據以及數據是如何分布的
我們將要討論下面的話題CBO的參數我們從基本的優化器參數開始學習然後學習每個優化器參數是如何影響Oracle的優化器的執行的
CBO的統計這裡我們將討論使用Analyze或者DBMS_STATS來收集正確的統計數據對Oracle 優化器而言是多麼的重要我們還將學習如何把優化器的統計數據從一個系統拷貝到另外一個系統這樣可以確保開發環境和產品數據庫環境下SQL的執行路徑不會變化
下面我們開始討論CBO優化模式以及影響CBO的Oracle參數
CBO的參數
CBO受一些重要參數的影響修改這些參數後可以看到CBO性能上戲劇性的變化首先從設置CBO的optimizer_mode參數開始然後討論其他重要參數的設置
在 Oracle i 中optimizer_mode 參數有四種取值決定了四種優化模式 rule choose all_rows 和 first_rows其中 rule 和 choose 兩種模式表示目前已經過時的基於規則的優化器模式（rulebased optimizer簡稱RBO）所以我們在此著重討論後兩種CBO模式
優化模式的設置可以在系統級進行也可以對某個會話（session）進行設置或者對某個SQL語句進行設置對應的語句如下
alter system set optimizer_mode=first_rows_;
alter session set optimizer_goal = all_rows;
select /*+ first_rows() */ from student;
我們首先需要知道對一個SQL語句來說什麼是最好的執行計劃（the best execution plan）？是使SQL語句返回結果的速度最快還是使SQL語句占用系統資源最少？顯然這個答案取決於數據庫的處理方式
舉一個簡單的例子比如有下列SQL語句
select customer_name
from
customer
where
region = south
order by
customer_name;
如果最好的執行計劃是返回結果的速度最快那麼就需要使用 region 列和 customer_name 列上的索引從 customer 表中按照正確的順序快速讀取所有的列而不用管是否從物理上讀取了很多不連續的數據塊導致的大量IO操作（見下圖）
假設這個執行計劃從開始到返回結果耗時 秒同時產生了 個 db_block_gets 但是如果你的目標是計算資源的最小化呢？如果這個SQL語句是在一個批處理程序中執行也許對返回結果的速度要求就不那麼重要了而另一個執行計劃則可能耗費更少的系統資源
在下圖所示的例子中並行的全表掃描由於不需要按照排序重新讀取數據塊所以耗系統資源較少並且IO操作也不多當然由於SQL語句執行過程中沒有排序得到預期結果的時間就長了而資源耗費少了假設這個執行計劃從開始到返回結果耗時 秒同時產生了 個 db_block_gets
Oracle提供了幾個 optimizer_mode 的設置參數使你能夠得到想要的最好的執行計劃
optimizer_mode = first_rows
設置為這種CBO模式以後SQL語句返回結果的速度會盡可能的快而不管系統全部的查詢是否會耗時較長或者耗系統資源過多由於利用索引會使查詢速度加快所以 first_rows 優化模式會在全表掃描上進行索引掃描這種優化模式一般適合於一些OLTP系統滿足用戶能夠在較短時間內看到較小查詢結果集的要求
optimizer_mode = all_rows
設置為這種CBO模式以後將保證消耗的所有計算資源最小盡管有時查詢結束以後沒有結果返回all_rows 的優化模式更傾向於全表掃描而不是全索引掃描和利用索引排序因此這種優化模式適合於數據查看實時性不是那麼強的數據倉庫決策支持系統和面向批處理的數據庫（batchoriented databases）等
optimizer_mode = first_rows_n
Oracle i 對一些預期返回結果集的數據量小的SQL語句優化模式進行了加強增加了四個參數值first_rows_first_rows_first_rows_first_rows_CBO通過 first_rows_n 中的 n 值決定了返回結果集數量的基數我們可能僅僅需要查詢結果集中的一部分CBO就根據這樣的 n 值來決定是否使用索引掃描
optimizer_mode = rule
基於規則的優化器模式RBO是早期Oracle版本使用過的一種優化模式由於RBO不支持自年Oracle版本的新特性如 bitmap indexestable partitionsfunctionbased indexes等所以在以後Oracle版本中已經不再更新RBO並且也不推薦用戶使用RBO這種優化模式了
從上面的討論可以看出optimizer_mode 參數的設置對CBO是非常重要的決定了CBO的基本模式同時還有一些其他的參數也對CBO有著極大的影響由於CBO的重要性Oracle提供了一些系統級的參數來調整CBO的全局性能這些調整參數包括索引掃描與全部掃描的選擇表連接方式的選擇等等下面簡單討論一下
optimizer_index_cost_adj
這個參數用於調整使用索引的訪問路徑的成本算法參數值越小索引訪問的成本就越低
optimizer_index_caching
這個參數告訴Oracle在內存緩沖區中索引的數量該參數的設置會影響CBO如何決定使用表連接（嵌套循環）的索引還是使用全表掃描
db_file_multiblock_read_count
這個參數的值被設置較大的時候CBO就會認為離散的多數據塊的讀取會比順序讀取的代價更低使得CBO更傾向於全表掃描
parallel_automatic_tuning
這個參數值被設置為 on 的時候表示使用並行的全表掃描由於並行的全表掃描比較快所以CBO認為索引的訪問是高成本的同時就更傾向於全表掃描
hash_area_size
如果不使用 pga_aggregate_target 參數的話該參數有效該參數的設置大小決定CBO是否更加傾向於 hash joins 而不是嵌套循環和表連接的索引合並
sort_area_size
如果不使用 pga_aggregate_target 參數的話該參數有效該參數的設置大小影響CBO決定是否進行索引訪問和結果集的排序參數值越大在內存中排序的可能性就越大CBO也就更加傾向於排序
由於對這些參數值的修改會影響到系統中成千上萬的SQL語句的執行計劃所以Oracle並不推薦修改這些參數的缺省值
在對CBO的參數有了大致的了解以後下面討論如何根據提供給CBO的數據幫助CBO制定出一個好的執行計劃
CBO的統計
對於CBO來說最重要的是定義和管理好你的統計數據為了使CBO能夠為你的SQL語句產生一個最好的執行計劃必須要有與SQL語句相關的表和索引統計數據只有當CBO知道了相關的信息如表的大小分布基數以及列值的可選性等才能對SQL語句作出正確的判斷從而得到最好的執行計劃
下面討論一下如何獲得高質量的CBO統計數據如何為你的數據庫系統創建一個適當的CBO環境
CBO產生最好執行計劃的能力來自於統計數據的有效性獲得統計數據的比較過時的方法是 analyze table 和 dbms_utility 這兩種方法對SQL語句的性能有一些危害因為我們知道CBO是使用對象統計數據（object statistics）來為所有的SQL語句選擇最好的執行計劃
dbms_stats 應用功能包是產生統計數據較好的方法特別對大型分區表而言下面看一個使用 dbms_stats 的例子
exec dbms_statsgather_schema_stats(
ownname => SCOTT
options => GATHER AUTO
estimate_percent => dbms_statsauto_sample_size
method_opt => for all columns size repeat
degree =>
)
上面例子中的options參數的幾個可選值需要說明一下
GATHER 重新分析整個schema產生統計數據
GATHER EMPTY 僅分析那些還沒有統計數據的表
GATHER STALE 僅重新分析那些發生了％變化的表（變化原因可能是 inserts updates deletes ）
GATHER AUTO 僅重新分析那些還沒有統計數據和發生了％變化的表該選項相當於 GATHER EMPTY 和 GATHER STALE 同時使用
使用 GATHER AUTO 和 GATHER STALE 兩個選項需要進行監控如果你執行了 ALTER TABLE XXX MONITORING 命令Oracle利用 dba_tab_modifications 視圖跟蹤表的變化記錄了最近一次統計數據分析以來的 insert update delete 的准確記錄數
SQL> desc dba_tab_modifications;
Name Type
TABLE_OWNER VARCHAR()
TABLE_NAME VARCHAR()
PARTITION_NAME VARCHAR()
SUBPARTITION_NAME VARCHAR()
INSERTS NUMBER
UPDATES NUMBER
DELETES NUMBER
TIMESTAMP DATE
TRUNCATED VARCHAR()
比較有趣的一個選項是 GATHER STALE 比如在一個數據更新頻繁的OLTP系統中幾乎所有的統計數據都會很快的過時而我們必須記住 GATHER STALE 選項是在表中％的記錄發生變化時才對該表重新分析產生統計數據因此除了只讀表以外的所有表幾乎使用 GATHER STALE 選項重新分析產生統計數據所以 GATHER STALE 選項主要還是用於一些主要是只讀表組成的系統中
在上面使用 dbms_stats 的例子中我們看到了一個參數 estimate_percent 它的值是 dbms_statsauto_sample_size 這個參數值是 Oracle i 才開始使用的這個參數值的出現極大方便了統計數據的分析產生
我們知道統計數據的質量越高CBO產生最好執行計劃的能力就越強但是由於數據庫統計采樣大小的問題對一個大型數據庫系統做一個完整的統計數據分析產生將會耗時數天最好的辦法就是在高質量的統計數據和數據庫統計采樣大小之間得到一個平衡點
在早一些的Oracle版本中為了得到統計數據DBA不得不猜測一個最好的數據采樣大小百分比但是從 Oracle i 開始可以通過 dbms_stats 包來自己指定 estimate_percent 參數的值了那就是 dbms_statsauto_sample_size
通過這種方式設置了自動采樣大小以後我們可以通過下列數據字典視圖的 sample_size 字段來驗證這些自動產生的統計采樣大小
DBA_ALL_TABLES
DBA_INDEXES
DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
DBA_OBJECT_TABLES
DBA_PART_COL_STATISTICS
DBA_SUBPART_COL_STATISTICS
DBA_TABLES
DBA_TAB_COLS
DBA_TAB_COLUMNS
DBA_TAB_COL_STATISTICS
DBA_TAB_PARTITIONS
DBA_TAB_SUBPARTITIONS
使用自動統計采樣以後Oracle會根據表的大小和列值的分布在％到％之間取值記住你的統計數據質量越高CBO作出的決定就越對你有利
現在我們對CBO統計數據應該有一些了解了下面來看看在一個成功的Oracle系統是如何管理CBO統計數據
CBO的正確環境
成功使用CBO的關鍵是穩定性下面是一些成功使用CBO的基本事項
●只在必需的時候才進行統計數據的重新分析
Oracle DBA們最容易犯的一個普遍錯誤就是經常性的對系統的統計數據進行重新分析記住做這件事的唯一目的是改變SQL語句的執行計劃如果這個執行計劃沒有被破壞就不要去修復它如果你對SQL語句的性能還滿意的話重新分析產生統計數據以後可能會產生較大的性能問題並給開發團隊帶來影響實際運用中也是極少數的Oracle系統才會周期性的對統計數據進行重新分析
一般來講一個數據庫應用系統的基本架構是不會輕易改變大數據量的表仍然是很大索引列的分布基數值等等也很少變化只有下列幾種情況的數據庫才可能經常對整個系統的統計數據重新分析
用於數據分析的數據庫
有一些由於科學試驗數據分析的數據庫系統經常會更換整個一套的試驗數據那麼這種情況下當數據庫重新load了一套數據以後可以立即重新對統計數據進行分析
高度變化的數據庫
這是極少數的例子表的大小或者索引列的數據在劇烈的變化比如一張表有條記錄一周以後就變成條記錄這種情況下也可以考慮周期性的進行統計數據分析
●強迫開發人員調整自己的SQL
很多開發人員錯誤的認為他們的任務就是編寫SQL語句然後從數據庫中獲得正確的數據但是實際上編寫出SQL語句只是開發人員一半的工作在一個成功的Oracle應用系統中會要求開發人員的SQL語句采用最優化的方式訪問數據庫並且保證SQL語句的執行計劃在新的SQL之間的可移植性
令人驚訝的是在許多Oracle應用系統中都不怎麼考慮具體SQL語句的執行計劃認為CBO是很智能的無論如何都可以為我們提供最好的SQL語句執行計劃
同一個查詢在SQL語句中可能有不同方式的寫法而每一種寫法都可能有不同的執行計劃觀察下面的例子每一個查詢的結果都是一樣的但是執行計劃卻相去甚遠
使用了不正確的子查詢
select
book_title
from
book
where
book_key not in (select book_key from sales);
Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=)
FILTER
TABLE ACCESS (FULL) OF BOOK (Cost= Card= Bytes=)
TABLE ACCESS (FULL) OF SALES (Cost= Card= Bytes=)
使用了兩張表的外連接
select
book_title
from
book b
sales s
where
bbook_key = sbook_key(+)
and
quantity is null;
Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=)
FILTER
FILTER
HASH JOIN (OUTER)
TABLE ACCESS (FULL) OF BOOK (Cost= Card= Bytes=)
TABLE ACCESS (FULL) OF SALES (Cost= Card= Bytes=)
使用了三個正確的子查詢
select
book_title
from
book
where
book_title not in (
select
distinct
book_title
from
book
sales
where
bookbook_key = salesbook_key
and
quantity > );
Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=)
FILTER
TABLE ACCESS (FULL) OF BOOK (Cost= Card= Bytes=)
FILTER
NESTED LOOPS (Cost= Card= Bytes=)
TABLE ACCESS (FULL) OF SALES (Cost= Card= Bytes=)
TABLE ACCESS (BY INDEX ROWID) OF BOOK (Cost= Card=)
INDEX (UNIQUE SCAN) OF PK_BOOK (UNIQUE)
我們看到正確的SQL語句寫法產生的執行計劃是如此的不同明智的開發人員知道如何去編寫能夠產生最好執行計劃的SQL語句明智的Oracle應用系統也會主動訓練開發人員去編寫最有效的SQL語句
下面是一些幫助開發人員優化SQL語句的技巧
使用 autotrace 和 TKPROF 功能去分析SQL語句的執行計劃
保證所有生產環境中的SQL語句都是在測試環境中經過優化的
制定一個性能優化的標准而不是只要求開發人員編寫出最快的SQL語句根據這種標准好的開發人員應該能夠寫出最有效的SQL語句
●謹慎管理CBO統計數據
成功的Oracle系統會謹慎管理他們的CBO統計數據以保證CBO在測試環境和生產環境中以同樣的方式工作一個聰明的DBA會在得到高質量的CBO統計數據以後把這些統計數據移植到測試環境中這樣SQL語句的執行計劃在測試環境和生產環境中就是一樣的了
對DBA來說一個重要的工作就是收集和發布CBO統計數據並隨時保持一套當前運行環境的最精確的統計數據在一些情況下可能會有不止一套的優化統計數據比如對OLTP運行的最好的統計數據可能對數據倉庫運行卻不是最好的在這種情況下DBA就需要保持兩套統計數據並根據不同的運行條件導入系統
可以使用 dbms_stats 包中的 export_system_stats 存儲過程來完成CBO統計數據的導出下面的例子中我們把當前CBO統計數據導出到一張名叫 stats_table_oltp的表中
dbms_statsexport_system_stats(stats_table_oltp)
導出以後我們就可以把這張表拷貝到別的實例中當系統的運行模式改變以後使用 dbms_stats 包中的 import_system_stats 存儲過程來完成CBO統計數據的導入
dbms_statsimport_system_stats(stats_table_oltp)
●千萬不要隨便改動CBO參數的值
改動CBO相關參數的值是非常危險的因為一個小小的改動可能就會對整個系統的執行性能帶來極大的負面影響只有在經過嚴格的系統測試以後才能改動這些參數的值可能帶來極大影響的參數值包括optimizer_mode optimizer_index_cost_adj and optimizer_index_caching而其他參數比如 hash_area_size sort_area_size 參數值的改變就不是那麼危險了可以在會話級進行改變以幫助CBO優化查詢
●保證靜態的執行計劃
成功的CBO應用會通過謹慎管理統計數據來鎖定SQL執行計劃同時保證存儲的優化計劃的穩定性或者在具體的SQL語句中加入一些細節上的提示
記住重新分析一個系統的統計數據可能會導致成千上萬的SQL語句改變其執行計劃許多Oracle應用系統要求所有的SQL語句在測試環境中經過驗證保證在功能上和生產環境是一致的
CBO的思考
盡管我們已經對CBO的不少細節有了了解但是由於隨著Oracle新版本的不斷推出CBO變得越來越強大同時也越來越復雜我們仍然有許多關於CBO的知識需要學習
下面是一些關於CBO調整的提綱性的建議供准備進行CBO調整的DBA們思考
●DBA可以提供一些Oracle參數的配置對CBO進行控制但是只能在有限的環境下謹慎的改變這些參數
●CBO依靠統計數據來產生SQL語句的優化的執行計劃可以通過 dbms_stats 包來分析產生統計數據
●DBA們的一項重要任務就是收集管理CBO統計數據這些數據可以被收集存儲也可以在相關的實例中進行移植以保證執行計劃的連貫性
●在沒有使用 export_system_stats 存儲過程導出原來的統計數據以前重新對系統的統計數據進行分析是十分危險的因為成千上萬的SQL語句的執行計劃將可能全部改變而你卻不能恢復原來的SQL性能只有在系統的數據發生巨大變化時才可能需要對整個系統的統計數據進行重新分析
Trackback: x?PostId=
From:http://tw.wingwit.com/Article/program/Oracle/201311/16802.html
