Oracle Tuning效能調整的一些總結

關於Oracle的效能調整，一般包括兩個方面，一是指Oracle資料庫本身的調整，比如SGA、PGA的最佳化設定，二是連線Oracle的應用程式以及SQL語句的最佳化。做好這兩個方面的最佳化，就可以使一套完整的Oracle應用系統處於良好的執行狀態。
本文主要是把一些Oracle Tuning的文章作了一個簡單的總結，力求以實際可操作為目的，配合講解部分理論知識，使大部分具有一般Oracle知識的使用者能夠對Oracle Tuning有所瞭解，並且能夠根據實際情況對某些引數進行調整。關於更加詳細的知識，請參見本文結束部分所提及的推薦書籍，同時由於該話題內容太多且複雜，本文必定有失之偏頗甚至錯誤的地方，請不吝賜教，並共同進步。

1. SGA的設定
在Oracle Tuning中，對SGA的設定是關鍵。SGA，是指Shared Global Area , 或者是 System Global Area , 稱為共享全域性區或者系統全域性區，結構如下圖所示。

對於SGA區域內的記憶體來說，是共享的、全域性的，在UNIX 上，必須為oracle 設定共享記憶體段（可以是一個或者多個），因為oracle 在UNIX上是多程式；而在WINDOWS上oracle是單程式（多個執行緒），所以不用設定共享記憶體段。

1.1 SGA的各個組成部分
下面用 sqlplus 查詢舉例看一下 SGA 各個組成部分的情況：

SQL> select * from v$sga; NAME VALUE -------------------- ---------- Fixed Size 104936 Variable Size 823164928 Database Buffers 1073741824 Redo Buffers 172032

或者

SQL> show sga Total System Global Area 1897183720 bytes Fixed Size 104936 bytes Variable Size 823164928 bytes Database Buffers 1073741824 bytes Redo Buffers 172032 bytes Fixed Size

oracle 的不同平臺和不同版本下可能不一樣，但對於確定環境是一個固定的值，裡面儲存了SGA 各部分元件的資訊，可以看作引導建立SGA的區域。

Variable Size
包含了shared_pool_size、java_pool_size、large_pool_size 等記憶體設定

Database Buffers
指資料緩衝區，在8i 中包含db_block_buffer*db_block_size、buffer_pool_keep、buffer_pool_recycle 三部分記憶體。在9i 中包含db_cache_size、db_keep_cache_size、db_recycle_cache_size、 db_nk_cache_size。

Redo Buffers
指日誌緩衝區，log_buffer。在這裡要額外說明一點的是，對於v$parameter、v$sgastat、v$sga查詢值可能不一樣。v$ parameter 裡面的值，是指使用者在初始化引數檔案裡面設定的值，v$sgastat是oracle 實際分配的日誌緩衝區大小（因為緩衝區的分配值實際上是離散的，也不是以block 為最小單位進行分配的），v$sga 裡面查詢的值，是在oracle 分配了日誌緩衝區後，為了保護日誌緩衝區，設定了一些保護頁，通常我們會發現保護頁大小是8k(不同環境可能不一樣)。參考如下內容

SQL> select substr(name,1,10) name,substr(value,1,10) value 2 from v$parameter where name = 'log_buffer'; NAME VALUE -------------------- -------------------- log_buffer 163840 SQL> select * from v$sgastat where pool is null; POOL NAME BYTES ----------- -------------------------- ---------- fixed_sga 104936 db_block_buffers 1073741824 log_buffer 163840 SQL> select * from v$sga; NAME VALUE -------------------- ---------- Fixed Size 104936 Variable Size 823164928 Database Buffers 1073741824 Redo Buffers 172032 172032 – 163840 = 8192

（以上試驗資料是在 HP B.11.11 + Oracle 8.1.7.4 環境下得到的）

1.2 SGA的大小設定
在對SGA的結構進行簡單分析以後，下面是關於如何根據系統的情況正確設定SGA大小的問題。
SGA是一塊記憶體區域，佔用的是系統實體記憶體，因此對於一個Oracle應用系統來說，SGA決不是越大越好，這就需要尋找一個系統最佳化的平衡點。

1.2.1 設定引數前的準備
在設定SGA的記憶體引數之前，我們首先要問自己幾個問題
一：實體記憶體多大
二：作業系統估計需要使用多少記憶體
三：資料庫是使用檔案系統還是裸裝置
四：有多少併發連線
五：應用是OLTP 型別還是OLAP 型別

根據這幾個問題的答案，我們可以粗略地為系統估計一下記憶體設定。那我們現在來逐個問題地討論，首先實體記憶體多大是最容易回答的一個問題，然後作業系統估計使用多少記憶體呢？從經驗上看，不會太多，通常應該在200M 以內（不包含大量程式PCB）。
接下來我們要探討一個重要的問題，那就是關於檔案系統和裸裝置的問題，這往往容易被我們所忽略。作業系統對於檔案系統，使用了大量的buffer 來快取作業系統塊。這樣當資料庫獲取資料塊的時候，雖然SGA 中沒有命中，但卻實際上可能是從作業系統的檔案快取中獲取的。而假如資料庫和作業系統支援非同步IO，則實際上當資料庫寫程式DBWR寫磁碟時，作業系統在檔案快取中標記該塊為延遲寫，等到真正地寫入磁碟之後，作業系統才通知DBWR寫磁碟完成。對於這部分檔案快取，所需要的記憶體可能比較大，作為保守的估計，我們應該考慮在 0.2——0.3 倍記憶體大小。但是如果我們使用的是裸裝置，則不考慮這部分快取的問題。這樣的情況下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 1048576 create_bitmap_area_size integer 8388608 hash_area_size integer 131072 sort_area_size integer 65536 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 為100 個，根據上面我們4 個引數在oracle8.1.7 下的預設值，我們來計算獨立模式下PGA 的大致大小。由於會話並不會經常使用create_bitmap_area_size 、bitmap_merge_area_size，所以我們通常不對四個引數求和。在考慮到除這四個引數外會話所儲存的變數、堆疊等資訊，我們估計為 2M，則200 個程式最大可能使用200M 的PGA。

1.2.2 一個經驗公式
現在，根據上面這些假定，我們來看SGA 實際能達到多少記憶體。在1G 的記憶體的伺服器上，我們能分配給SGA 的記憶體大約為400—500M。若是2G 的記憶體，大約可以分到1G的記憶體給SGA，8G 的記憶體可以分到5G的記憶體給SGA。當然我們這裡是以預設的排序部分記憶體sort_area_size=64k進行衡量的，假如我們需要調大該引數和 hash_area_size等引數，然後我們應該根據併發的程式的數量，來衡量考慮這個問題。

事實上，通常我們更習慣透過直觀的公式化來表達這樣的問題：
OS 使用記憶體+SGA+併發執行程式數*(sort_area_size+hash_ara_size+2M) < 0.7*總記憶體

(公式是死的，系統是活的，實際應用的調整不必框公式，這不過是一個參考建議)

在我們的實際應用中，假如採用的是裸裝置，我們可適當的增大SGA(如果需要的話)。由於目前幾乎所有的作業系統都使用虛擬快取，所以實際上如果就算SGA 設定的比較大也不會導致錯誤，而是可能出現頻繁的記憶體頁的換入與換出(page in/out)。在作業系統一級如果觀察到這個現象，那麼我們就需要調整記憶體的設定。

1.2.3 各個引數的設定
那麼SGA中的各個引數具體應該按照什麼樣的原則來設定呢，下面進行討論：
log_buffer
對於日誌緩衝區的大小設定，通常我覺得沒有過多的建議，因為參考LGWR寫的觸發條件之後，我們會發現通常超過3M意義不是很大。作為一個正式系統，可能考慮先設定這部分為log_buffer=1—3M 大小，然後針對具體情況再調整。
large_pool_size
對於大緩衝池的設定，假如不使用MTS，建議在20—30M 足夠了。這部分主要用來儲存並行查詢時候的一些資訊，還有就是RMAN 在備份的時候可能會使用到。如果設定了MTS，則由於UGA部分要移入這裡，則需要具體根據session最大數量和 sort_ares_size 等相關會話記憶體引數的設定來綜合考慮這部分大小的設定，一般可以考慮為 session * (sort_area_size + 2M)。這裡要提醒一點，不是必須使用MTS，我們都不主張使用MTS，尤其同時線上使用者數小於500的情況下。。
java_pool_size
假如資料庫沒有使用JAVA，我們通常認為保留10—20M大小足夠了。事實上可以更少，甚至最少只需要32k，但具體跟安裝資料庫的時候的元件相關(比如http server)。
shared_pool_size
這是迄今為止最具有爭議的一部分記憶體設定。按照很多文件的描述，這部分內容應該幾乎和資料緩衝區差不多大小。但實際上情況卻不是這樣的。首先我們要考究一個問題，那就是這部分記憶體的作用，它是為了快取已經被解析過的SQL，而使其能被重用，不再解析。這樣做的原因是因為，對於一個新的SQL （shared_pool 裡面不存在已經解析的可用的相同的SQL），資料庫將執行硬解析，這是一個很消耗資源的過程。而若已經存在，則進行的僅僅是軟分析（在共享池中尋找相同 SQL），這樣消耗的資源大大減少。所以我們期望能多共享一些SQL，並且如果該引數設定不夠大，經常會出現ora-04031錯誤，表示為了解析新的 SQL，沒有可用的足夠大的連續空閒空間，這樣自然我們期望該引數能大一些。但是該引數的增大，卻也有負面的影響，因為需要維護共享的結構，記憶體的增大也會使得SQL 的老化的代價更高，帶來大量的管理的開銷，所有這些可能會導致CPU 的嚴重問題。

在一個充分使用繫結變數的比較大的系統中，shared_pool_size 的開銷通常應該維持在300M 以內。除非系統使用了大量的儲存過程、函式、包，比如oracle erp 這樣的應用，可能會達到500M甚至更高。於是我們假定一個1G記憶體的系統，可能考慮設定該引數為100M，2G 的系統考慮設定為150M,8G 的系統可以考慮設定為200—300M。
對於一個沒有充分使用或者沒有使用繫結變數系統，這可能給我們帶來一個嚴重的問題。所謂沒有使用bind var 的SQL，我們稱為Literal SQL。也就是比如這樣的兩句SQL我們認為是不同的SQL,需要進行2 次硬解析：

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。但是通常我們不主張這部分記憶體超過800M（特殊情況下可以更大）。
事實上，可能的話我們甚至要想辦法避免軟分析，這在不同的程式語言中實現方式有差異。我們也可能透過設定session_cached_cursors 引數來獲得幫助（這將增大PGA）
關於使用繫結變數的話題，在下面的應用最佳化中繼續討論。

Data buffer
現在我們來談資料緩衝區，在確定了SGA 的大小並分配完了前面部分的記憶體後，其餘的，都分配給這部分記憶體。通常，在允許的情況下，我們都嘗試使得這部分記憶體更大。這部分記憶體的作用主要是快取 DB BLOCK，減少甚至避免從磁碟上獲取資料，在8i中通常是由db_block_buffers*db_block_size 來決定大小的。如果我們設定了buffer_pool_keep 和buffer_pool_recycle，則應該加上後面這兩部分記憶體的大小。

可以看出，設定SGA時基本上應該掌握的原則是：
data buffer 一般可以儘可能的大
shared_pool_size 應該適度
log buffer 在 1MB 以內就可以了

假定oracle是 32 bit ,伺服器RAM大於2G ，注意你的PGA的情況，,則建議
shared_pool_size + data buffer +large_pool_size + java_pool_size < 1.6G

再具體化，如果512M RAM
建議 shared_pool_size = 50M, data buffer = 200M

如果1G RAM
shared_pool_size = 100M , data buffer = 500M

如果2G RAM
shared_pool_size = 150M ,data buffer = 1.2G

實體記憶體再大已經跟引數沒有關係了

假定64 bit ORACLE
記憶體4G
shared_pool_size = 200M , data buffer = 2.5G

記憶體8G
shared_pool_size = 300M , data buffer = 5G

記憶體 12G
shared_pool_size = 300M-----800M , data buffer = 8G

1.3 32bit 與 64bit 對SGA的影響
為什麼在上面SGA大小設定的經驗規則中要分 32bit Oracle 和 64bit Oracle 呢，是因為這關係到SGA大小的上限問題。在32bit的資料庫下，通常oracle只能使用不超過1.7G的記憶體，即使我們擁有12G的記憶體，但是我們卻只能使用1.7G，這是一個莫大的遺憾。假如我們安裝64bit的資料庫,我們就可以使用很大的記憶體，幾乎不可能達到上限。但是64bit 的資料庫必須安裝在64bit 的作業系統上，可惜目前windows上只能安裝32bit的資料庫，我們透過下面的方式可以檢視資料庫是 32bit 還是 64bit ：

SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production PL/SQL Release 8.1.7.0.0 - Production CORE 8.1.7.0.0 Production TNS for 32-bit Windows: Version 8.1.7.0.0 - Production NLSRTL Version 3.4.1.0.0 – Production

在UNIX平臺下的顯示有所不同，明顯可以看出是 64bit Oracle ，比如在HP-UX平臺上：

SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production PL/SQL Release 8.1.7.4.0 - Production CORE 8.1.7.0.0 Production TNS for HPUX: Version 8.1.7.4.0 - Production NLSRTL Version 3.4.1.0.0 – Production

32bit的oracle無論跑在32bit或者64bit的平臺都有SGA的限制的，而對於32bit的平臺只能跑32bit的oracle，但是在特定的作業系統下，可能提供了一定的手段，使得我們可以使用超過1.7G 的記憶體，達到2G 以上甚至更多。由於我們現在一般都使用64bit Oracle，因此關於如何在32bit平臺上擴充套件SGA大小的問題不再贅述。


1.4 9i中相關引數的變化
oracle的版本的更新，總是伴隨著引數的變化，並且越來越趨向於使得引數的設定更簡單，因為複雜的引數設定使得DBA們經常焦頭爛額。關於記憶體這部分的變化，我們可以考察下面的引數。事實上在9i中資料庫本身可以給出一組適合當前執行系統的SGA相關部分的引數調整值（參考V$ DB_CACHE_ADVICE、V$SHARED_POOL_ADVICE），關於PGA也有相關檢視V$PGA_TARGET_ADVICE 等。

Data buffer
9i 中保留了8i中的引數，如設定了新的引數，則忽略舊的引數。9i中用db_cache_size來取代db_block_buffers ， 用db_keep_cache_size 取代buffer_pool_keep, 用db_recycle_cache_size 取代buffer_pool_recycle；這裡要注意9i 中設定的是實際的快取大小而不再是塊的數量。另外9i新增加了db_nk_cache_size，這是為了支援在同一個資料庫中使用不同的塊大小而設定的。對於不同的表空間，可以定義不同的資料塊的大小，而緩衝區的定義則依靠該引數的支援。其中n 可以為2、4、6、8、16 等不同的值。在這裡順便提及的一個引數就是db_block_lru_latches，該引數在9i中已經成為了保留引數，不推薦手工設定。

PGA
在9i 裡面這部分也有了很大的變化。在獨立模式下，9i已經不再主張使用原來的UGA相關的引數設定，而代之以新的引數。假如 workarea_size_policy=AUTO（預設），則所有的會話的UGA 共用一大塊記憶體，該記憶體由 pga_aggregate_target 設定。在我們根據前面介紹的方法評估了所有程式可能使用的最大PGA 記憶體之後，我們可以透過在初始化引數中設定這個引數，從而不再關心其他 ”*_area_size” 引數。

SGA_MAX_SIZE
在9i中若設定了SGA_MAX_SIZE，則在總和小於等於這個值內，可以動態的調整資料緩衝區和共享池的大小

SQL> show parameters sga_max_size NAME TYPE VALUE ---------------- -------------------- ------- ------------- sga_max_size unknown 193752940 SQL> SQL> alter system set db_cache_size = 30000000; System altered. SQL> alter system set shared_pool_size = 20480000; System altered.

1.5 lock_sga = true 的問題
由於幾乎所有的作業系統都支援虛擬記憶體，所以即使我們使用的記憶體小於實體記憶體，也不能避免作業系統將SGA 換到虛擬記憶體（SWAP）。所以我們可以嘗試使得SGA 鎖定在實體記憶體中不被換到虛擬記憶體中，這樣減少頁面的換入和換出，從而提高效能。但在這裡遺憾的是，windows 是無法避免這種情況的。下面我們來參考在不同的幾個系統下怎麼實現lock_sga
AIX 5L（AIX 4.3.3 以上）
logon aix as root
cd /usr/samples/kernel
./vmtune (資訊如下) v_pingshm已經是1
./vmtune -S 1
然後oracle使用者修改initSID.ora 中 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使用者修改initSID.ora中lock_sga=true
重新啟動資料庫

SOLARIS (solaris2.6以上)
8i版本以上資料庫預設使用隱藏引數 use_ism = true ，自動鎖定SGA於記憶體中,不用設定lock_sga, 如果設定 lock_sga =true 使用非 root 使用者啟動資料庫將返回錯誤。

WINDOWS
不能設定lock_sga=true,可以透過設定pre_page_sga=true,使得資料庫啟動的時候就把所有記憶體頁裝載，這樣可能起到一定的作用。

2. 應用最佳化
下面我們從技術的角度入手，來探討資料庫最佳化方面的問題。通常作為最佳化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）入手的方法。

2.1 使用statspack
statspack是一個效能診斷工具，首先釋出於Oracle8.1.6版本，在8.1.7版本中功能得到加強。Statspack除了查詢例項中的效能問題外，還可以查詢應用程式中高負荷的SQL語句，很容易確定Oracle 資料庫的瓶頸所在，並且記錄資料庫效能狀態。
在資料庫中Statspack 的指令碼位於$ORACLE_HOME/RDBMS/ADMIN 目錄下，對於ORACLE8.1.6,是一組以stat 開頭的檔案；對於ORACLE8.1.7,是一組以sp 開頭的檔案。
在Statspack 釋出之前，我們通常能夠使用診斷資料庫的工具是兩個指令碼UTLBSTAT.SQL 和UTLESTAT.SQL，BSTAT/ESTAT 是一個非常簡單的效能診斷工具。UTLBSTAT 獲得開始時很多V$檢視的快照，UTLESTAT 透過先前的快照和當前檢視生成一個報表。
該報表實際上相當於statspack 中的兩個取樣點。
Statspack 透過連續的取樣，能夠給我們提供至關重要的趨勢分析資料。這是一個巨大的進步。能夠使用Statspack 的環境我們就儘量不要使用BSTAT/ESTAT 的方式來診斷資料庫問題。

2.1.1 安裝statapack
§ 步驟一：
為了能夠順利安裝和執行Statspack ，首先需要設定以下兩個系統引數：
1. job_queue_processes
為了能夠建立自動任務，執行資料收集，該引數需要大於0。你可以在初試化引數檔案中修改該引數(使該引數在重起後以然有效)。
該引數可以在系統級動態修改(重起後失效)。

SQL> alter system set job_queue_processes = 6; System altered

在Oracle9i 當中，可以指定範圍，如 both,這樣該修改在當前及之後保持有效(僅當你使用spfile 時，如果在9i 中仍然使用pfile，那麼更改方法同8i 相同):

SQL> alter system set job_queue_processes = 6 scope=both; System altered

2. timed_statistics
收集作業系統的計時資訊，這些資訊可被用來顯示時間等統計資訊、最佳化資料庫和 SQL 語句。要防止因從作業系統請求時間而引起的開銷，請將該值設定為False。
使用statspack 收集統計資訊時建議將該值設定為 TRUE，否則收集的統計資訊大約只能起到10%的作用，將timed_statistics 設定為True 所帶來的效能影響與好處相比是微不足道的。
該引數使收集的時間資訊儲存在在V$SESSTATS 和V$SYSSTATS 等動態效能檢視中。
timed_statistics 引數也可以在例項級進行更改

SQL> alter system set timed_statistics = true; System altered

如果你擔心一直啟用timed_statistics 對於效能的影響，你可以在使用statspack 之前在system 更改，取樣過後把該引數動態修改成false。

§ 步驟二：
需要單獨為statspack建立一個儲存資料的表空間，如果取樣間隔較短，週期較長，打算長期使用，那麼可能需要一個大一點的表空間，如果每個半個小時取樣一次，連續取樣一週，資料量是很大的。下面的例子中建立了一個500M 的測試表空間。
注意: 這裡建立的表空間不能太小，如果太小的話建立物件會失敗，建議至少建立100M 表空間。

SQL> create tablespace perfstat 2 datafile '/oracle/oradata/oradata/res/perfstat.dbf' 3 size 500M; Tablespace created。

§ 步驟三：
在 sqlplus 中用internal 身份登陸，或者擁有SYSDBA(connect / as sysdba)許可權的使用者登陸。
注: 在Oracle9i 中，不存在internal 使用者，可以使用sys 使用者以sysdba 身份連線。
先轉到$ORACLE_HOME/RDBMS/ADMIN 目錄，檢查安裝指令碼是否存在，同時我們執行指令碼也可以方便些。

$ cd $ORACLE_HOME/rdbms/admin $ ls -l sp*.sql -rw-r--r-- 1 oracle other 1774 Feb 18 2000 spauto.sql -rw-r--r-- 1 oracle other 62545 Jun 15 2000 spcpkg.sql -rw-r--r-- 1 oracle other 877 Feb 18 2000 spcreate.sql -rw-r--r-- 1 oracle other 31193 Jun 15 2000 spctab.sql -rw-r--r-- 1 oracle other 6414 Jun 15 2000 spcusr.sql -rw-r--r-- 1 oracle other 758 Jun 15 2000 spdrop.sql -rw-r--r-- 1 oracle other 3615 Jun 15 2000 spdtab.sql -rw-r--r-- 1 oracle other 1274 Jun 15 2000 spdusr.sql -rw-r--r-- 1 oracle other 6760 Jun 15 2000 sppurge.sql -rw-r--r-- 1 oracle other 71034 Jul 12 2000 spreport.sql -rw-r--r-- 1 oracle other 2191 Jun 15 2000 sptrunc.sql -rw-r--r-- 1 oracle other 30133 Jun 15 2000 spup816.sql $

接下來我們就可以開始安裝Statspack 了。在Oracle8.1.6 版本中執行statscre.sql; 在Oracle8.1.7 版本中執行spcreate.sql。
這期間會提示你輸入預設表空間和臨時表空間的位置,輸入我們為 perfstat 使用者建立的表空間和你的臨時表空間。安裝指令碼會自動建立perfstat 使用者。

$ sqlplus SQL*Plus: Release 8.1.7.0.0 - Production on Sat Jul 26 16:27:31 2003 (c) Copyright 2000 Oracle Corporation. All rights reserved. Enter user-name: internal Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production SQL> SQL> @spcreate ... Installing Required Packages Package created. Grant succeeded. View created. Package body created. Package created. Synonym dropped. Synonym created. …… Specify PERFSTAT user's default tablespace Enter value for default_tablespace: perfstat Using perfstat for the default tablespace User altered. User altered. Specify PERFSTAT user's temporary tablespace Enter value for temporary_tablespace: temp Using temp for the temporary tablespace User altered. NOTE: SPCUSR complete. Please check spcusr.lis for any errors. ……

如果安裝成功，你可以接著看到如下的輸出資訊：

…. Creating Package STATSPACK... Package created. No errors. Creating Package Body STATSPACK... Package body created. No errors. NOTE: SPCPKG complete. Please check spcpkg.lis for any errors.

可以檢視.lis 檔案檢視安裝時的錯誤資訊。

§ 步驟四：
如果安裝過程中出現錯誤，那麼可以執行spdrop.sql 指令碼來刪除這些安裝指令碼建立的物件。然後重新執行spcreate.sql來建立這些物件。

SQL> @spdrop Dropping old versions (if any) Synonym dropped. Sequence dropped. Synonym dropped. Table dropped. Synonym dropped. View dropped. …… NOTE: SPDUSR complete. Please check spdusr.lis for any errors.

（以上的安裝過程描述是在 HP 11.11 + Oracle 8.1.7 平臺上得到的）

2.1.2 測試statspack
執行statspack.snap 可以產生系統快照，執行兩次，然後執行spreport.sql 就可以生成一個基於兩個時間點的報告。
如果一切正常，說明安裝成功。

SQL>execute statspack.snap PL/SQL procedure successfully completed. SQL>execute statspack.snap PL/SQL procedure successfully completed. SQL>@spreport.sql

可是有可能你會得到以下錯誤：

SQL> exec statspack.snap; BEGIN statspack.snap; END; * ERROR at line 1: ORA-01401: inserted value too large for column ORA-06512: at "PERFSTAT.STATSPACK", line 978 ORA-06512: at "PERFSTAT.STATSPACK", line 1612 ORA-06512: at "PERFSTAT.STATSPACK", line 71 ORA-06512: at line 1

這是Oracle 的一個Bug，Bug 號1940915。
該Bug 自8.1.7.3 後修正。
這個問題只會出現在多位的字符集, 需要修改spcpkg.sql 指令碼，$ORACLE_HOME/rdbms/admin/spcpkg.sql，將"substr" 修改為 "substrb"，然後重新執行該指令碼。
該指令碼錯誤部分：

select l_snap_id , p_dbid , p_instance_number , substr(sql_text,1,31) ．．．．．．．．．．．

substr 會將多位的字元, 當作一個byte.substrb 則會當作多個byte。在收集資料時， statpack 會將 top10 的 sql 前 31 個位元組 存入資料表中,若在SQL 的前31 個字有中文，就會出現此錯誤。
注意：執行 spcpkg.sql 也需要以 internal 使用者登入 sqlplus

2.1.3 生成statspack報告
呼叫spreport.sql 可以生成分析報告：
當呼叫spreprot.sql 時，系統首先會查詢快照列表，然後要求你選擇生成報告的開始快照ID(begin_snap)和結束快照ID(end_snap),生成一個報告.
為了生成一個report,我們至少需要兩次取樣:

SQL> @spreport   DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------  2749170756 RES 1 res  Completed Snapshots   Snap Snap Instance DB Name Id Snap Started Level Comment ------------ ------------ ----- ----------------- ----- ---------------------- res RES 1 26 Jul 2003 16:36 5  2 26 Jul 2003 16:37 5  3 26 Jul 2003 17:03 5   Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap:2 Begin Snapshot Id specified: 2  Enter value for end_snap: 3 End Snapshot Id specified: 3   Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is sp_2_3. To use this name, press to continue, otherwise enter an alternative. Enter value for report_name: rep0726.txt  ……  End of Report

在執行 spreport.sql 生成 statspack 報告的過程中，會有三個地方提示使用者輸入：
1、 開始快照ID；
2、 結束快照ID；
3、 輸出報告檔案的檔名，預設的檔名是sp__
上面輸入的開始快照ID是2，開始快照ID是3，輸出報告檔案的檔名是rep0726.txt
成功執行一次 statspack.snap 就會產生一個 snapshot ，在生成 statspack 報告的時候就可以看到這個 snap id 和 snap 執行的時間。執行 statspack.snap ，就是上面所說的取樣，statspack 報告是分析兩個取樣點之間各種情況。

2.1.4 刪除歷史快照資料
前面講過，成功執行一次 statspack.snap 就會產生一個 snapshot ，這個 snapshot 的基本資訊是存放在 PERFSTAT.stats$snapshot 表中的，生成 statspack報告時會查詢該表的資料，供使用者選擇準備分析的 snapshot 。如果執行 statspack.snap 次數多了以後，該表的資料也會增加，歷史資料會影響正常執行的效果，因此需要定時清理一下歷史快照資料。
刪除stats$snapshot 資料表中的相應資料，其他表中的資料會相應的級連刪除：

SQL> select max(snap_id) from stats$snapshot; MAX(SNAP_ID) ------------ 166 SQL> delete from stats$snapshot where snap_id < = 166; 143 rows deleted

你可以更改snap_id 的範圍以保留你需要的資料。
在以上刪除過程中，你可以看到所有相關的表都被鎖定。

SQL> select a.object_id,a.oracle_username ,b.object_name from v$locked_object a,dba_objects b where a.object_id = b.object_id / OBJECT_ID ORACLE_USERNAME OBJECT_NAME ------------------------------------- --------------------------------------------------- 156 PERFSTAT SNAP$ 39700 PERFSTAT STATS$LIBRARYCACHE 39706 PERFSTAT STATS$ROLLSTAT 39712 PERFSTAT STATS$SGA 39754 PERFSTAT STATS$PARAMETER 39745 PERFSTAT STATS$SQL_STATISTICS 39739 PERFSTAT STATS$SQL_SUMMARY 39736 PERFSTAT STATS$ENQUEUESTAT 39733 PERFSTAT STATS$WAITSTAT 39730 PERFSTAT STATS$BG_EVENT_SUMMARY 39724 PERFSTAT STATS$SYSTEM_EVENT 39718 PERFSTAT STATS$SYSSTAT 39715 PERFSTAT STATS$SGASTAT 39709 PERFSTAT STATS$ROWCACHE_SUMMARY 39703 PERFSTAT STATS$BUFFER_POOL_STATISTICS 39697 PERFSTAT STATS$LATCH_MISSES_SUMMARY 39679 PERFSTAT STATS$SNAPSHOT 39682 PERFSTAT STATS$FILESTATXS 39688 PERFSTAT STATS$LATCH 174 PERFSTAT JOB$ 20 rows selected

Oracle 還提供了系統指令碼用於Truncate 這些統計資訊表，這個指令碼名字是: sptrunc.sql (8i、9i 都相同)
該指令碼主要內容如下，裡面看到的就是statspack 相關的所有系統表：

                    

                                                    來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16396910/viewspace-1029640/，如需轉載，請註明出處，否則將追究法律責任。