某客户Oracle数据库业务系统性能问题处理过程

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:33

关键字:
性能
作者:
jlandzpa
一、问题描述:
用户反映,几年前在新系统上线时,系统的性能是正常的。但随着业务数据增加了数倍,系统的性能开始变慢。尤其是2010年4月份以来,出现多次系统很慢无法登陆的故障,严重影响了业务系统的使用。最近一次是2010-5-24日,系统上的表现是hdisk0,hdisk1长时间100%繁忙,数据库似乎停顿了,业务系统不能登录和使用。故障的频率没有规律,有时一个星期都正常,有时一个星期出现1,2次。
二、环境配置:
操作系统:AIX p550 (2个双线程的cpu)、16G内存。
存储设备:IBM FastT600(IO速度一般)。
数据库:oracle9.2.0.7(双机热备)。
数据量:数据库扩展110GB,exp出来的文件60GB。
[@more@]三、处理分析过程:
1、检查网络:
分析与结论:发现有停顿现象,用户近期将检查网络。

2、检查系统参数:
mailto:node1@root]node1@root#
cpu_scale_memp = 8
data_stagger_interval = 161
defps = 1
force_relalias_lite = 0
framesets = 2
htabscale = n/a
kernel_heap_psize = 4096
large_page_heap_size = 0
lgpg_regions = 0
lgpg_size = 0
low_ps_handling = 1
lru_file_repage = 1
lru_poll_interval = 0
lrubucket = 131072
maxclient% = 80
maxfree = 128
maxperm = 3120515
maxperm% = 80
maxpin = 3237479
maxpin% = 80
mbuf_heap_psize = 4096
memory_affinity = 1
memory_frames = 4046848
mempools = 2
minfree = 120
minperm = 780128
minperm% = 20
nokilluid = 0
npskill = 32768
npswarn = 131072
num_spec_dataseg = 0
numpsblks = 4194304
pagecoloring = n/a
pinnable_frames = 3836604
pta_balance_threshold = n/a
relalias_percentage = 0
soft_min_lgpgs_vmpool = 0
spec_dataseg_int = 512
strict_maxclient = 1
strict_maxperm = 0
v_pinshm = 0
vmm_fork_policy = 0
aio 是【10,80】
分析与结论:检查了aio 和vmtune参数,设置都正常。

3、检查系统日志:
分析与结论:有一个预防性的主机主板微码升级的提示,但不影响性能。

4、检查数据库日志:
分析与结论:出故障时,没有多少错误信息需要关注,日志切换也不多。

5、检查数据库表空间:
分析与结论:对几个数据和索引表空间进行了扩展。

6、检查数据库备份情况:
逻辑备份每天晚上19:00开始,最新一次是21:48成功结束。
逻辑备份文件每天早上7:00开始向带库里备,一般8点左右都可以结束。
全库备份每天早上1:00开始,最新一次是70分钟成功结束。
归档备份中午做,时间很短。
分析与结论:根据以上情况,基本可以排除备份的影响。

7、检查表的数据量:
表的增长很快,比如talbe1已经超过1亿条记录。
用户也反映,涉及到这些大表的操作都比较慢。

8、检查归档情况:
分析与结论:归档文件是放在本地盘的,但正常做业务一天产生归档日志不多,因此归档对本地盘的压力也不大。

9、故障重现:
2010-5-25、26、27;在用户现场三天,故障没有出现。
2010-5-25日下班后,对大表talbe1做了一个group by操作,语句半天执行不完。
此时监控系统,可以看到:
hdisk0、hdisk1磁盘100%繁忙,系统响应变慢。
vmstat可以看到内存被耗光,故障重现。
分析与结论:我们分析故障是由于多个长时间的排序操作同时运行导致的。在硬件环境不能升级的情况下,只能从软件优化的方面去着手解决。后面的部分尝试,可以看到,简单的优化技巧可以使语句的执行时间缩短数倍,甚至数十倍。

10、Statspack报告分析:
从25日下午的监控报告可以看到:
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
latch free 104,247 1,106 34.80
db file sequential read 686,463 932 29.32
SQL*Net message from dblink 15,555 854 26.85
db file scattered read 719,947 180 5.68
log file sync 6,194 42 1.31
分析与结论:latch free的等待事件较大,说明应用程序的变量绑定方面做的不好,这样会导致cpu消耗较大。

11、SQL的优化:
以下语句,每个月都要执行一次,但每次执行都很痛苦;每次执行需要45分钟:
SELECT b.table2_col2, b.table2_col3, c.table3_col1, c.table3_col3
FROM table1 a,table2 b,table3 c
WHERE a.table1_col1=b.table2_col1 AND a.table1_col2=c.table3_col2 AND
a.table1_col3=1500 AND a.table1_col4=53 AND a.table1_col5=1 AND a.table1_col6=200904
ORDER BY b.table2_col2, b.table2_col3
优化过程:
创建一个索引(下班后执行,需要10分钟):
CREATE INDEX user1.IND_table1_col6 ON user1.table1
(table1_col6)
LOGGING
TABLESPACE tablespace1_INDX02
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
计算记录数(如果记录很多,后面的临时表可考虑建索引):
select count(*) from user1.table1 a
where a.table1_col3=1500 AND a.table1_col4=53 AND a.table1_col5=1 AND a.table1_col6=200904;
创建临时表:
create table test
tablespace tablespace_name
as select * from user1.table1 a
where a.table1_col3=1500 AND a.table1_col4=53 AND a.table1_col5=1 AND a.table1_col6=200904;
原始语句的改写(这一步执行只需要37秒):
SELECT b.table2_col2, b.table2_col3, c.table3_col1, c.table3_col3
FROM test a,table2 b,table3 c
WHERE a.col1=b.table2_col1 AND a.col2=c.table3_col2
ORDER BY b.table2_col2, b.table2_col3;
26日对系统做了监控,大表table1的table1_col6索引的创建改变了部分语句的执行计划,某些语句的执行时间从不到1秒变长到3秒。因此,这个索引用完后清删除。
分析与结论:经过优化,SQL所需要的结果都可以控制在2,3分钟内完成,大大缩短了用户的时间。

12、一个分页SQL的优化:
下面的语句似乎经常出现,每次执行需要30秒左右,消耗cpu约20%。
经过观察,如果几个类似的语句同时执行,cpu几乎消耗完了。
/* Formatted on 2010/05/25 16:00 (Formatter Plus v4.7.0) */
SELECT *
FROM (SELECT t.*, ROWNUM AS rowno
FROM (SELECT acol1, acol2, acol3, acol4, acol5, ..., bcol1, bcol2
FROM user2.view1
WHERE acol2 = '1100' AND acol5 = '1') t)
WHERE rowno >= 1 AND rowno < 301; 其中的视图定义如下: CREATE OR REPLACE FORCE VIEW user2.view1 (acol1, acol2, acol3, acol4, acol5, ..., bcol1, bcol2) AS SELECT a."table4_col1",a."table4_col2",a."table4_col3",a."table4_col4", a."table4_col5", ...,b.table5_col1,b.table5_col2 FROM table4 a,table5 b WHERE a.table4_col3 = b.table5_col3 and a.table4_col4 != 0; 记录数的统计如下: select count(*) from user2.table4; 10671134 select count(*) from user2.table5; 69969 select count(*) from user2.view1; 10620169 经过分析,需要创建一个复合索引: CREATE INDEX user2.IND_table4_col2_col5 ON user2.table4 (col2,col5) LOGGING TABLESPACE tablespace1_INDX02 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL; 优化后的执行时间: /* Formatted on 2010/05/25 16:00 (Formatter Plus v4.7.0) */ SELECT * FROM (SELECT t.*, ROWNUM AS rowno FROM (SELECT acol1, acol2, acol3, acol4, acol5, ..., bcol1, bcol2 FROM user2.view1 WHERE acol2 = '1100' AND acol5 = '1') t) WHERE rowno >= 1 AND rowno < 301; no rows selected Elapsed: 00:00:00.02 分析和总结:经过优化,可以缩短到0.02秒。 13、一个和VIEW1有关的优化: 下面两个语句似乎也经常出现,每次执行需要58.98秒左右,消耗cpu约20%。经过观察,如果几个类似的语句同时执行,cpu几乎消耗完了。 /* Formatted on 2010/05/25 15:24 (Formatter Plus v4.7.0) */ SELECT * FROM (SELECT t.*, ROWNUM AS rowno FROM (SELECT ... FROM VIEW1 WHERE ...; SELECT COUNT (*) FROM VIEW1 WHERE ...; 其中视图如下: CREATE OR REPLACE FORCE VIEW USER1.VIEW1 (...) AS SELECT ... FROM table1 a,table2 b,table4 d, (SELECT * FROM table5 E WHERE E.table5_col1= ( SELECT MAX(table6_col1) FROM table6 F WHERE F.table6_col1=E.table5_col1)) c WHERE ... GROUP BY ...; 通过加hint进行优化: SQL> select /*+index(table2 IND_col1_col2)*/ count(*) from
USER1.VIEW1 where table2_col2='36121321' and ...;
COUNT(*)
----------
1
Elapsed: 00:00:10.25
结论与分析:经过优化,可以缩短到10秒左右;但需要开发人员修改程序。

14、一个和VIEW2有关的优化:
CREATE OR REPLACE FORCE VIEW USER1.VIEW2
(..., table1_col1, ...)
AS
SELECT table1_col1, ... FROM table1 A;
SQL> SELECT COUNT (*) FROM USER1.VIEW2 WHERE table1_col1 = '1774856';
COUNT(*)
----------
9
Elapsed: 00:00:24.90
CREATE INDEX ind_table1_col1 ON USER1.TABLE1
(table1_col1)
LOGGING
TABLESPACE TABLESPACE1_INDX01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 128K
NEXT 128K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
SQL> SELECT COUNT (*) FROM USER1.VIEW2 WHERE table1_col1 = '1774856';
COUNT(*)
----------
9
Elapsed: 00:00:01.10
结论与分析:部分表上建了复合主键或者复合索引,但由于顺序的关系,导致某些语句不上索引,执行很慢。
四、总结:
1、绑定变量不好而导致latch free较高的问题。
2、重点关注和大表有关的语句。
3、现场观察应用程序的运行,发现不良语句,及时优化,因为部分语句如果不执行也很难发现。

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】