故障現象
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以上的內存。
