關于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 bytesFixed 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 163840SQL> select * from v$sgastat where pool is null;POOL NAME BYTES ----------- -------------------------- ---------- fixed_sga 104936 db_block_buffers 1073741824 log_buffer 163840SQL> select * from v$sga;NAME VALUE -------------------- ---------- Fixed Size 104936 Variable Size 823164928 Database Buffers 1073741824 Redo Buffers 172032172032 – 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就有調大的機會。
注: 在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 用戶。$ sqlplusSQL*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: internalConnected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - ProductionSQL> SQL> @spcreate ... Installing Required PackagesPackage 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 tablespaceUser altered.User altered.Specify PERFSTAT user's temporary tablespace Enter value for temporary_tablespace: temp Using temp for the temporary tablespaceUser 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 resCompleted 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: 2Enter 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) ------------ 166SQL> 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 selectedOracle 還提供了系統腳本用于Truncate 這些統計信息表，這個腳本名字是: sptrunc.sql (8i、9i 都相同) 該腳本主要內容如下，里面看到的就是statspack 相關的所有系統表： truncate table STATS$FILESTATXS; truncate table STATS$LATCH;
下面接合一個實際的statspack報告，大致分析一下。2.2.1 基本信息分析 DB Name DB Id Instance Inst Num Release OPS Host ------------ ----------- ------------ -------- ----------- --- --------- --- RES 2749170756 res 1 8.1.7.0.0 NO res Snap Id Snap Time Sessions ------- ------------------ -------- Begin Snap: 2 26-Jul-03 16:37:08 38 End Snap: 3 26-Jul-03 17:03:23 38 Elapsed: 26.25 (mins)Statspack報告首先描述了數據庫的基本情況，比如數據庫名、實例名、實例個數、oracle版本號等等；然后是該報告的開始快照和結束快照的信息，包括 snap id , snap time 等等；最后是該報告經過的時間跨度，單位是分鐘(mins)。Cache Sizes ~~~~~~~~~~~ db_block_buffers: 61440 log_buffer: 163840 db_block_size: 8192 shared_pool_size: 52428800然后描述了Oracle內存結構中幾個重要的參數。2.2.2 內存信息分析 Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 4,834.87 11,116.67 Logical reads: 405.53 932.43 Block changes: 60.03 138.02
Physical reads: 138.63 318.75 Physical writes: 54.27 124.79 User calls: 62.69 144.13 Parses: 19.14 44.00 Hard parses: 2.26 5.20 Sorts: 1.83 4.20 Logons: 0.21 0.47 Executes: 21.10 48.50 Transactions: 0.43 % Blocks changed per Read: 14.80 Recursive Call %: 34.45 Rollback per transaction %: 0.00 Rows per Sort: 20.57Redo size: 是日志的生成量，分為每秒和每事務所產生的，通常在很繁忙的系統中日志生成量可能達到上百k，甚至幾百k；Logical reads: 邏輯讀實際上就是logical IO=buffer gets表示的含義，我們可以這樣認為，block在內存中，我們每一次讀一塊內存，就相當于一次邏輯讀；Parses 和 Hard parses: Parse 和 hard parse通常是很輕易出問題的部分，80%的系統的慢都是由于這個原因所導致的。
所謂parse分soft parse 和hard parse，soft 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，需要使用latch，latch是一種Oracle低級結構,用于保護內存資源，是一種內部生命周期很短的lock，大量使用latch將消耗大量的cpu資源。Sorts: 表示排序的數量；Executes: 表示執行次數；Transactions: 表示事務數量；Rollback per transaction %: 表示數據庫中事務的回退率。假如不是因為業務本身的原因，通常應該小于10%為好，回退是一個很消耗資源的操作。 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 99.98 Buffer Hit %: 65.82 In-memory Sort %: 99.65 Library Hit %: 91.32 Soft Parse %: 88.18 Execute to Parse %: 9.28 Latch Hit %: 99.99 Parse CPU to Parse ElaPSD %: 94.61 % Non-Parse CPU: 99.90Buffer Hit %: 數據緩沖區命中率，通常應該大于90%；Library Hit %: libaray cache的命中率，通常應該大于98%；In-memory Sort %: 排序在內存的比例，假如這個比例過小，可以考慮增大sort_area_size，使得排序在內存中進行而不是在temp表空間中進行；Soft Parse %: 軟解析的百分比，這個百分比也應該很大才好，因為我們要盡量減少hard parse。 soft parse 百分比=soft/(soft+hard)；Execute to Parse %: 這個數字也應該是越大越好，接近100%最好。有些報告中這個值是負的，看上去很希奇。事實上這表示一個問題，sql假如被age out的話就可能出現這種情況，也就是sql老化，或執行alter system flush shared_pool等。 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 90.63 87.19 % SQL with executions>1: 71.53 75.39 % Memory for SQL w/exec>1: 59.45 65.17% SQL with executions>1: 這個表示SQL被執行次數多于一次的比率，也應該大為好，小則表示很多sql只被執行了一次，說明沒有使用bind var；2.2.3 等待事件分析 接下來，statspack報告中描述的是等待事件（Wait Events），這是Oracle中比較復雜難懂的概念。 Oracle 的等待事件是衡量Oracle 運行狀況的重要依據及指標。 等待事件的概念是在Oracle7.0.1.2 中引入的，大致有100 個等待事件。在Oracle 8.0 中這個數目增加到了大約150 個，在Oracle8i 中大約有200 個事件,在Oracle9i 中大約有360 個等待事件。 主要有兩種類別的等待事件，即空閑（idle）等待事件和非空閑（non-idle）等待事件。 空閑事件指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 5 Wait Events ~~~~~~~~~~~~~~~~~ Wait % Total Event Waits Time (cs) Wt Time -------------------------------------------- ------------ ------------ ------- db file scattered read 26,877 12,850 52.94 db file parallel write 472 3,674 15.13 log file parallel write 975 1,560 6.43 direct path write 1,571 1,543 6.36 control file parallel write 652 1,290 5.31 -------------------------------------------------------------db file scattered read: DB文件分散讀取。這個等待事件很常見，經常在top5中出現，這表示，一次從磁盤讀數據進來的時候讀了多于一個block的數據，而這些數據又被分散的放在不連續的內存塊中，因為一次讀進來的是多于一個block的。 通常來說我們可以認為是全表掃描類型的讀，因為根據索引讀表數據的話一次只讀一個block，假如這個數字過大，就表明該表找不到索引，或者只能找到有限的索引，可能是全表掃描過多，需要檢查sql是否合理的利用了索引，或者是否需要建立合理的索引。 當全表掃描被限制在內存時，它們很少會進入連續的緩沖區內，而是分散于整個緩沖存儲器中。盡管在特定條件下執行全表掃描可能比索引掃描更有效，但假如出現這種等待時，最好檢查一下這些全表掃描是否必要,是否可以通過建立合適的索引來減少對于大表全表掃描所產生的大規模數據讀取。
