故障现象
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
1
2db_block_buffers: 180000 log_buffer:
3
48192000
5
6db_block_size: 8192 shared_pool_size:
7
8314572800
9
10Load Profile
11
12~~~~~~~~~~~~ Per Second Per Transaction
13
14\--------------- \---------------
15
16Redo size: 11,005.01 2,280.39
17
18Logical reads: 65,704.21 13,614.83
19
20Block changes: 67.96 14.08
21
22Physical reads: 1,392.89 288.63
23
24Physical writes: 11.61 2.40
25
26User calls: 172.63 35.77
27
28Parses: 29.11 6.03
29
30Hard parses: 0.01 0.00
31
32Sorts: 7.81 1.62
33
34Logons: 0.14 0.03
35
36Executes: 101.44 21.02
37
38Transactions: 4.83
39
40% Blocks changed per Read: 0.10 Recursive Call %: 41.29
41
42Rollback per transaction %: 0.28 Rows per Sort: 25.55
43
44Instance 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
1
2Event Waits Time (cs) Wt Time
3
4\-------------------------------------------- ------------ ------------ -------
5
6db file sequential read 5,030,075 389,071 86.37
7
8log file sync 17,470 21,187 4.70
9
10log file parallel write 17,640 18,611 4.13
11
12db file parallel write 1,853 14,930 3.31
13
14db file scattered read 3,149 2,297 .51
15
16对报告分析后发现有一些不合理的初始化参数需要调整,建议如下调整:
17
18
19
20** 1. ** ** 报告中发现全表扫描的语句特别多 , 因此建议程序中尽量避免使用全表扫描 , **
21
22** 减少 IO等待,从而加快语句的执行速度。 **
23
24类似如下语句需要优化:
25
26SQL>select count(*) as totalcount from LOCALUSAGE where se
27
28rviceid=:"SYS_B_0" and starttime>=to_date(:"SYS_B_1",:"SYS_B_2")
29
30and starttime <=to_date(:"SYS_B_3",:"SYS_B_4") and ( LOCALROAMI
31
32NGCHARGE >:"SYS_B_5" or LocalCharge >:"SYS_B_6" or UrbanCharge
33
34>:"SYS_B_7" or ruralcharge >:"SYS_B_8");
35
362. **调整db_file_multiblock_read_count=16**
37
38这个参数指定一个完全连续扫描的一次 I/O操作过程中读取的块的最大数量。它的增加对IO是有改善的,特别是在做full table scan的时候,可以减少IO的次数。
39
403. **调整db_block_lru_latches=2**
41
42这个参数指定 LRU 闩锁集数量的上限。LRU锁的数量是在Oracle数据库内部用来管理数据库缓冲的,它严重依赖于服务器上CPU的数量,这个值通常设置为服务器上cpu_count的一半,增大这个值有利于提高磁盘的I/O性能。
43
444. **调整session_cached_cursors=200**
45
46这个参数指定要高速缓存的会话游标的数量,对同一 SQL语句进行多次语法分析后,它的会话游标将被移到该会话的游标高速缓存中。增大这个值可以缩短语法分析的时间,因为游标被高速缓存,无需被重新打开。
47
485. **调整log_buffer=1048576**
49
50参数 log_buffer指定在 LGWR 将重做日志缓冲区里的内容写入重做日志文件之前,用于缓存这些条目的内存量。这个参数以字节为单位,同时受cpu_count的影响, log_buffer如果被设置得太高(例如,大于1MB),这会引起性能问题,因为大容量的结果会使得写入同步进行(例如,日志同步等待事件非常高)。
51
526\. **调整db_block_buffers = 200000 shared_pool_size= ** ** 262144000 **
53
54按照杭州的规划, Oracle最终运行起来占用近1/2的物理内存。其中最主要的两个参数为:
55
56db_block_buffers:它的配置原则是,最终数据块缓存占据1/3的内存。
57
58Shared_pool_size:它的配置原则是,基本控制在200-500M左右。
59
607. **从报告中发现系统等待最严重的五个事件为:db file sequential read,log file sync,log file parallel write,db file parallel write和db file scattered read.**
61
62(1)对于db file sequential read等待事件,一般问题出现在读索引上,建议将wacos表空间和wacos索引表空间分开存储在不同的物理卷下,以提高磁盘的I/O性能。
63
64(2)对于db file scattered read等待事件,建议程序中尽量避免使用全表扫描的语句,或者可以增大db_file_multiblock_read_count的值,提高全表扫描一次读取数据块的速度,减少磁盘I/O。
65
66(3) 对于 db file parallel write 等待事件,说明 DBWR 进程正等待把缓冲区的内容并行写入数据文件中去 , 等待将一直持续到所有的 I/O 全部完成。建议增大初始化参数中的 db_writer_processes 的值,可以增大到 4 。
67(4) 对于 log file sync 等待事件,说明任何时候一个事物提交时,它将通知 LGWR 将 LOG _ BUFFER 写入日志文件,如果此部分占用时间较长,应减少 COMMIT 的次数,建议将重做日志放到较快的磁盘上进行存储。
68(5) 对于 log file parallel write 等待事件,和上面一样建议将重做日志放到较快的磁盘上进行存储。 **
69
70**
71
72** 故障处理 **
73调整 initORCL.ora里不合