分享
 
 
 

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

王朝other·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

故障现象

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以上的内存。

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有