数据库性能分析及调整一例

故障现象

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里不合
Published At
Categories with 数据库类
Tagged with
comments powered by Disqus