資料庫效能分析及調整一例(zt)

故障現象

2004年6月8日上午10:00,內蒙古巴盟網通使用者反映在OSS系統介面“話單查詢”裡查詢單個使用者五天的話單特別慢，查詢很長時間無結果。

例如：在OSS系統介面“綜合查詢”內點選“收費”-〉“話單查詢”，鍵入“使用者號碼，起始時間：2004-01-01 00：00：00，結束時間：2004-06-01 23：00：00”，點選查詢後，IE進度條緩慢，很長時間不返回結果。

故障分析

經過分析，此現象和資料庫的效能有關，主要是資料庫初始化引數調整不合理造成的效能低下。具體分析步驟如下：

1．首先查詢話單表的索引是否失效，因為失效的索引會帶來差的SQL查詢效率。

SQL>select INDEX_NAME,status from USER_IND_PARTITIONS where status!='USABLE';

no rows selected.

結果說明沒有失效的話單表索引。

2．用top命令看到可用實體記憶體很低，只剩下100M，有大量的SWAP區記憶體正在使用，ORACLE單個會話佔用的記憶體很多，經檢視ORACLE初始化引數shared_pool_size的值設定的過高，應重新調整。

top的結果:
last pid: 4565; load averages: 0.15, 0.20, 0.20 10:09:56

170 processes: 169 sleeping, 1 on cpu

CPU states: 84.9% idle, 1.6% user, 1.1% kernel, 12.4% iowait, 0.0% swap

Memory: 4096M real, 100M free, 1343M swap in use, 6851M swap free

PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND

10459 oracle 1 59 0 1978M 1953M sleep 0:53 0.79% oracle

2258 oracle 1 10 0 1976M 1951M sleep 116:57 0.65% oracle

25639 oracle 1 58 0 1975M 1949M sleep 1:56 0.27% oracle

1948 oracle 1 58 0 1976M 1948M sleep 3:34 0.18% oracle

4002 wacos 6 47 4 9616K 2344K sleep 27:26 0.18% cdr_backup

2271 oracle 1 59 0 1975M 1947M sleep 15:13 0.16% oracle

1958 oracle 1 48 0 1976M 1949M sleep 2:26 0.13% oracle

1928 oracle 1 58 0 1976M 1951M sleep 4:28 0.12% oracle

1926 oracle 1 58 0 1976M 1949M sleep 2:06 0.12% oracle

1956 oracle 1 58 0 1976M 1949M sleep 2:23 0.11% oracle

1952 oracle 1 59 0 1976M 1949M sleep 2:19 0.10% oracle

403 root 10 21 0 4896K 4608K sleep 16:32 0.09% picld

1954 oracle 1 48 0 1976M 1949M sleep 2:04 0.08% oracle

2189 oracle 1 58 0 1976M 1949M sleep 15:51 0.08% oracle

3．為了進一步分析ORACLE的效能，用ORACLE自帶的診斷工具statspack做效能快照分析，統計時段為1小時，時間從下午17：00-18：00之間。這段時間業務比較繁忙，選擇在此時段內對整個系統進行效能分析，能夠得到更加準確的資訊。

安裝statspack效能分析工具:

SQL>connect internal

SQL>alter system set timed_statistics=true;(收集作業系統的計時資訊)

SQL>@?/rdbms/admin/spcreate.sql

SQL>execute statspack.snap (17:00的時候執行一次)

SQL>execute statspack.snap (18:00的時候執行一次)

SQL>@?/rdbms/admin/spreport (產生效能分析報告)

擷取報告的部分內容如下:

STATSPACK report for

DB Name DB Id Instance Inst Num Release OPS Host

------------ ----------- ------------ -------- ----------- --- ------------

ORCL 1000277484 ORCL 1 8.1.7.3.0 NO bm_db1

Snap Id Snap Time Sessions

------- ------------------ --------

Begin Snap: 1 08-Jun-04 17:00:15 116

End Snap: 2 08-Jun-04 18:00:40 116

Elapsed: 60.42 (mins)

Cache Sizes

~~~~~~~~~~~

db_block_buffers: 180000 log_buffer:

8192000

db_block_size: 8192 shared_pool_size:

314572800

Load Profile

~~~~~~~~~~~~ Per Second Per Transaction

--------------- ---------------

Redo size: 11,005.01 2,280.39

Logical reads: 65,704.21 13,614.83

Block changes: 67.96 14.08

Physical reads: 1,392.89 288.63

Physical writes: 11.61 2.40

User calls: 172.63 35.77

Parses: 29.11 6.03

Hard parses: 0.01 0.00

Sorts: 7.81 1.62

Logons: 0.14 0.03

Executes: 101.44 21.02

Transactions: 4.83

% Blocks changed per Read: 0.10 Recursive Call %: 41.29

Rollback per transaction %: 0.28 Rows per Sort: 25.55

Instance Efficiency Percentages (Target 100%)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Buffer Nowait %: 100.00 Redo NoWait %: 100.00

Buffer Hit %: 97.88 In-memory Sort %: 100.00

Library Hit %: 99.98 Soft Parse %: 99.96

Execute to Parse %: 71.30 Latch Hit %: 99.99

Parse CPU to Parse Elapsd %: 62.24 % Non-Parse CPU: 99.99

Shared Pool Statistics Begin End

------ ------

Memory Usage %: 24.15 24.44

% SQL with executions>1: 75.04 76.95

% Memory for SQL w/exec>1: 75.49 79.90

Top 5 Wait Events

~~~~~~~~~~~~~~~~~ Wait % Total

Event Waits Time (cs) Wt Time

-------------------------------------------- ------------ ------------ -------

db file sequential read 5,030,075 389,071 86.37

log file sync 17,470 21,187 4.70

log file parallel write 17,640 18,611 4.13

db file parallel write 1,853 14,930 3.31

db file scattered read 3,149 2,297 .51

對報告分析後發現有一些不合理的初始化引數需要調整，建議如下調整：

1． 報告中發現全表掃描的語句特別多，因此建議程式中儘量避免使用全表掃描，

減少IO等待，從而加快語句的執行速度。

類似如下語句需要最佳化：

SQL>select count(*) as totalcount from LOCALUSAGE where se

rviceid=:"SYS_B_0" and starttime>=to_date(:"SYS_B_1",:"SYS_B_2")

and starttime <=to_date(:"SYS_B_3",:"SYS_B_4") and ( LOCALROAMI

NGCHARGE >:"SYS_B_5" or LocalCharge >:"SYS_B_6" or UrbanCharge

>:"SYS_B_7" or ruralcharge >:"SYS_B_8")；

2．調整db_file_multiblock_read_count=16

這個引數指定一個完全連續掃描的一次I/O操作過程中讀取的塊的最大數量。它的增加對IO是有改善的，特別是在做full table scan的時候，可以減少IO的次數。

3．調整db_block_lru_latches=2

這個引數指定LRU 閂鎖集數量的上限。LRU鎖的數量是在Oracle資料庫內部用來管理資料庫緩衝的，它嚴重依賴於伺服器上CPU的數量,這個值通常設定為伺服器上cpu_count的一半，增大這個值有利於提高磁碟的I/O效能。

4．調整session_cached_cursors=200

這個引數指定要快取記憶體的會話遊標的數量，對同一SQL語句進行多次語法分析後，它的會話遊標將被移到該會話的遊標快取記憶體中。增大這個值可以縮短語法分析的時間，因為遊標被快取記憶體，無需被重新開啟。

5．調整log_buffer=1048576

引數log_buffer指定在 LGWR 將重做日誌緩衝區裡的內容寫入重做日誌檔案之前，用於快取這些條目的記憶體量。這個引數以位元組為單位，同時受cpu_count的影響, log_buffer如果被設定得太高（例如，大於1MB），這會引起效能問題，因為大容量的結果會使得寫入同步進行（例如，日誌同步等待事件非常高）。

6.調整db_block_buffers = 200000 shared_pool_size= 262144000

按照杭州的規劃，Oracle最終執行起來佔用近1/2的實體記憶體。其中最主要的兩個引數為：

db_block_buffers：它的配置原則是，最終資料塊快取佔據1/3的記憶體。

Shared_pool_size：它的配置原則是，基本控制在200-500M左右。

7．從報告中發現系統等待最嚴重的五個事件為:db file sequential read，log file sync，log file parallel write，db file parallel write和db file scattered read.

(1)對於db file sequential read等待事件，一般問題出現在讀索引上，建議將wacos表空間和wacos索引表空間分開儲存在不同的物理卷下，以提高磁碟的I/O效能。

(2)對於db file scattered read等待事件，建議程式中儘量避免使用全表掃描的語句，或者可以增大db_file_multiblock_read_count的值，提高全表掃描一次讀取資料塊的速度，減少磁碟I/O。

(3)對於db file parallel write等待事件，說明DBWR程式正等待把緩衝區的內容並行寫入資料檔案中去,等待將一直持續到所有的I/O全部完成。建議增大初始化引數中的db_writer_processes的值，可以增大到4。
(4)對於log file sync等待事件，說明任何時候一個事物提交時，它將通知LGWR將LOG_BUFFER寫入日誌檔案，如果此部分佔用時間較長，應減少COMMIT的次數，建議將重做日誌放到較快的磁碟上進行儲存。
(5)對於log file parallel write等待事件，和上面一樣建議將重做日誌放到較快的磁碟上進行儲存。

故障處理調整initORCL.ora裡不合理的引數，具體調整為：

process=200

log_buffer=1048576

session_cached_cursors=200

db_block_lru_latches=2

shared_pool_size= 262144000

db_block_buffers = 200000

sort_area_size = 6553600

sort_area_retained_size = 6553600

db_file_multiblock_read_count = 16

處理結果調整完重啟DB後，發現查詢一切正常，很快就返回了結果。

總結

資料庫裡初始化引數設定不合理, 記憶體富餘太少, 導致資料庫執行使用大量的swap空間，資料庫效能很差，導致透過OSS介面查詢話單很慢。這時需要透過調整資料庫初始化引數解決該問題。從效能方面考慮，資料庫伺服器最好能富餘300－500M以上的記憶體。