关键字：
性能
作者：
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、现场观察应用程序的运行，发现不良语句，及时优化，因为部分语句如果不执行也很难发现。