首页 > Linux操作系统 > Linux操作系统 > 数据库默认安装配置导致的问题
协助地方公司诊断数据库性能问题。
刚登陆数据库服务器,发现系统响应很慢,从操作系统的监测工具上看,系统的idle不到10,数据库服务器处于非常繁忙的状态。
p570a# iostat 2 10
System configuration: lcpu=16 drives=11 paths=6 vdisks=0
tty: tin tout avg-cpu: % user % sys % idle % iowait
0.0 30.0 90.9 1.7 5.0 2.3
Disks: % tm_act Kbps tps Kb_read Kb_wrtn
hdisk2 0.0 24208.0 1101.0 11664 36752
hdisk3 0.0 0.0 0.0 0 0
hdisk5 0.0 0.0 0.0 0 0
hdisk6 0.0 0.0 0.0 0 0
hdisk8 0.0 0.0 0.0 0 0
hdisk7 0.0 0.0 0.0 0 0
hdisk4 0.0 0.0 0.0 0 0
hdisk9 0.0 0.0 0.0 0 0
hdisk0 0.0 0.0 0.0 0 0
hdisk1 0.0 0.0 0.0 0 0
cd0 0.0 0.0 0.0 0 0
Disk history flag turned off. The current iteration disk utilization values may be inaccurate
tty: tin tout avg-cpu: % user % sys % idle % iowait
0.0 498.0 89.1 2.1 6.2 2.6
Disks: % tm_act Kbps tps Kb_read Kb_wrtn
hdisk2 0.0 24524.0 1034.5 8836 40212
hdisk3 0.0 0.0 0.0 0 0
hdisk5 0.0 0.0 0.0 0 0
hdisk6 0.0 0.0 0.0 0 0
hdisk8 0.0 0.0 0.0 0 0
hdisk7 0.0 0.0 0.0 0 0
hdisk4 0.0 0.0 0.0 0 0
hdisk9 0.0 0.0 0.0 0 0
hdisk0 0.0 30.0 7.5 0 60
hdisk1 0.0 30.0 7.5 0 60
cd0 0.0 0.0 0.0 0 0
Disk history flag turned off. The current iteration disk utilization values may be inaccurate
.
.
.
tty: tin tout avg-cpu: % user % sys % idle % iowait
0.0 497.5 80.4 2.7 13.3 3.6
Disks: % tm_act Kbps tps Kb_read Kb_wrtn
hdisk2 0.0 30470.0 680.5 18288 42652
hdisk3 0.0 0.0 0.0 0 0
hdisk5 0.0 0.0 0.0 0 0
hdisk6 0.0 0.0 0.0 0 0
hdisk8 0.0 0.0 0.0 0 0
hdisk7 0.0 0.0 0.0 0 0
hdisk4 0.0 0.0 0.0 0 0
hdisk9 0.0 0.0 0.0 0 0
hdisk0 0.0 0.0 0.0 0 0
hdisk1 0.0 0.0 0.0 0 0
cd0 0.0 0.0 0.0 0 0
从占用资源较大的后台进行找到一个sql语句,这是一个只取前10条记录的分页语句,在实际运行中没有记录返回,即使是这样,运行也超过了50秒。而检查对应的执行计划并没有发现什么问题。简单分析了一下,找不到问题所在,继续检查整个系统的情况。
SQL> SELECT SID, SERIAL#, USERNAME, EVENT, SECONDS_IN_WAIT FROM V$SESSION WHERE LOGON_TIME < SYSDATE - 5 AND USERNAME != 'SYS';
SID SERIAL# USERNAME EVENT SECONDS_IN_WAIT
------ ---------- -------------------- ----------------------------------- ---------------
81 41590 JIANGSREP SQL*Net message from client 1734662
97 3228 JIANGSREP SQL*Net message from client 245416
103 31706 JSSQ_TRADE kksfbc child completion 9047122
134 11042 JIANGSREP SQL*Net message from client 245505
140 40963 JIANGSREP SQL*Net message from client 2508546
158 49273 JIANGSREP SQL*Net message from client 3540123
237 10017 JIANGSREP SQL*Net message from client 850642
246 14033 JSSQ_TRADE SQL*Net message from client 522290
253 37697 JSSQ_TRADE SQL*Net message from client 854678
267 22150 JIANGSREP SQL*Net message from client 1734675
287 33919 JSSQ_TRADE kksfbc child completion 5440100
320 49947 JSSQ_TRADE SQL*Net message from client 3111
338 34648 JSSQ_TRADE kksfbc child completion 6477167
414 31881 JSSQ_TRADE SQL*Net message from client 87
447 21579 JSSQ_TRADE SQL*Net message from client 220
479 34580 JSSQ_TRADE kksfbc child completion 8647549
16 rows selected.
系统中存在很多登陆时间超过5条的会话,这对于当前系统而言,可能性不大,且观察SECONDS_IN_WAIT列的等待时间,显然这些会话的状态并不正常。虽然SQL*Net message from client是空闲状态,但是如果出现了网络闪断,会话也可能处于这种状态,且会一直占用大量的CPU资源,而当前就是这种症状。
在操作系统中找到这些长时间等待的进程,确认这些进程不属于Oracle的后台进行,将这些进程用kill -9命令结束掉:
SQL> SELECT SPID FROM V$PROCESS
2 WHERE ADDR IN (SELECT PADDR FROM V$SESSION WHERE SID IN (81, 97, 103, 134, 140, 158, 237, 246, 253, 267, 287, 320, 338, 479));
SPID
------------
1597770
1786238
1306960
786582
1015972
376952
1212566
356998
770692
1421972
693036
762560
1377278
1609872
14 rows selected.
$ ps -ef|grep 1597770
oracle 1355892 672288 0 11:05:43 pts/5 0:00 grep 1597770
oracle 1597770 1 0 Jul 30 - 0:03 oracletrade (LOCAL=NO)
$ kill -9 1597770
$ ps -ef|grep 1786238
oracle 1630318 672288 0 11:06:31 pts/5 0:00 grep 1786238
oracle 1786238 1 0 Jul 30 - 0:08 oracletrade (LOCAL=NO)
$ kill -9 1786238
$ ps -ef |grep 1306960
oracle 504028 672288 0 11:08:08 pts/5 0:00 grep 1306960
oracle 1306960 1 85 May 27 - 148536:45 oracletrade (LOCAL=NO)
$ kill -9 1306960
.
.
.
$ ps -ef|grep 762560
oracle 1307044 672288 0 11:15:20 pts/5 0:00 grep 762560
oracle 762560 1 0 Sep 02 - 3:40 oracletrade (LOCAL=NO)
$ kill -9 762560
清除掉处于长时间等待的这些进程,再次检查系统状态:
$ iostat 2 10
System configuration: lcpu=16 drives=11 paths=6 vdisks=0
tty: tin tout avg-cpu: % user % sys % idle % iowait
0.0 30.0 57.3 1.6 23.6 17.5
Disks: % tm_act Kbps tps Kb_read Kb_wrtn
hdisk2 0.0 12638.0 651.0 3372 21904
hdisk3 0.0 0.0 0.0 0 0
hdisk5 0.0 0.0 0.0 0 0
hdisk6 0.0 0.0 0.0 0 0
hdisk8 0.0 0.0 0.0 0 0
hdisk7 0.0 0.0 0.0 0 0
hdisk4 0.0 0.0 0.0 0 0
hdisk9 0.0 0.0 0.0 0 0
hdisk0 0.0 0.0 0.0 0 0
hdisk1 0.0 0.0 0.0 0 0
cd0 0.0 0.0 0.0 0 0
Disk history flag turned off. The current iteration disk utilization values may be inaccurate
tty: tin tout avg-cpu: % user % sys % idle % iowait
0.0 497.7 59.0 2.3 19.3 19.5
Disks: % tm_act Kbps tps Kb_read Kb_wrtn
hdisk2 0.0 19218.0 953.8 5736 32688
hdisk3 0.0 0.0 0.0 0 0
hdisk5 0.0 0.0 0.0 0 0
hdisk6 0.0 0.0 0.0 0 0
hdisk8 0.0 0.0 0.0 0 0
hdisk7 0.0 0.0 0.0 0 0
hdisk4 0.0 0.0 0.0 0 0
hdisk9 0.0 0.0 0.0 0 0
hdisk0 0.0 0.0 0.0 0 0
hdisk1 0.0 0.0 0.0 0 0
cd0 0.0 0.0 0.0 0 0
Disk history flag turned off. The current iteration disk utilization values may be inaccurate
tty: tin tout avg-cpu: % user % sys % idle % iowait
0.0 497.5 59.3 2.2 23.5 15.0
Disks: % tm_act Kbps tps Kb_read Kb_wrtn
hdisk2 0.0 19272.0 536.0 580 37964
hdisk3 0.0 0.0 0.0 0 0
hdisk5 0.0 0.0 0.0 0 0
hdisk6 0.0 0.0 0.0 0 0
hdisk8 0.0 0.0 0.0 0 0
hdisk7 0.0 0.0 0.0 0 0
hdisk4 0.0 0.0 0.0 0 0
hdisk9 0.0 0.0 0.0 0 0
hdisk0 0.0 0.0 0.0 0 0
hdisk1 0.0 0.0 0.0 0 0
cd0 0.0 0.0 0.0 0 0
Disk history flag turned off. The current iteration disk utilization values may be inaccurate
.
.
.
tty: tin tout avg-cpu: % user % sys % idle % iowait
0.0 497.5 61.1 2.5 20.6 15.9
Disks: % tm_act Kbps tps Kb_read Kb_wrtn
hdisk2 0.0 21254.0 820.5 804 41704
hdisk3 0.0 0.0 0.0 0 0
hdisk5 0.0 0.0 0.0 0 0
hdisk6 0.0 0.0 0.0 0 0
hdisk8 0.0 0.0 0.0 0 0
hdisk7 0.0 0.0 0.0 0 0
hdisk4 0.0 0.0 0.0 0 0
hdisk9 0.0 0.0 0.0 0 0
hdisk0 0.0 0.0 0.0 0 0
hdisk1 0.0 0.0 0.0 0 0
cd0 0.0 0.0 0.0 0 0
Disk history flag turned off. The current iteration disk utilization values may be inaccurate
显然,杀掉了长时间运行的会话后,系统的idle从原来的不到10%,上升到20%以上,说明系统的压力有了缓解,等待一段时间后,idle的值进一步上升。
这时在检查刚才运行超过50秒的SQL,这次发现SQL运行时间不到0.1秒,看来刚才的SQL本身没有什么问题,只不过刚才整个系统都处于极度繁忙的状态,SQL运行所需要的资源、LATCH、锁等都被占用,因此出现了长时间的等待。这从片面也说明一个问题,系统的网络肯定是发生过闪断,导致了大量的会话处于等待的状态,消耗的大量的系统资源,直至系统资源被耗尽。
解决这个问题后,系统的压力开始慢慢降低,但是观察发现,虽然现在数据库中的会话并不多,但是数据库并没有处于比较空闲的状态。这说明系统还存在其他的问题。
检查数据库的alert文件时,发现一个问题。
首先,日志切换过于频繁:
$ tail -200 alert_trade.log
Thu Sep 9 08:10:41 2010
Thread 1 advanced to log sequence 4566 (LGWR switch)
Current log# 2 seq# 4566 mem# 0: /oracle01/app/oracle/oradata/trade/trade/redo02.log
Thu Sep 9 08:15:47 2010
Thread 1 advanced to log sequence 4567 (LGWR switch)
Current log# 3 seq# 4567 mem# 0: /oracle01/app/oracle/oradata/trade/trade/redo03.log
Thu Sep 9 08:20:57 2010
Thread 1 advanced to log sequence 4568 (LGWR switch)
Current log# 1 seq# 4568 mem# 0: /oracle01/app/oracle/oradata/trade/trade/redo01.log
Thu Sep 9 08:24:39 2010
Thread 1 advanced to log sequence 4569 (LGWR switch)
Current log# 2 seq# 4569 mem# 0: /oracle01/app/oracle/oradata/trade/trade/redo02.log
Thu Sep 9 08:28:28 2010
Thread 1 advanced to log sequence 4570 (LGWR switch)
Current log# 3 seq# 4570 mem# 0: /oracle01/app/oracle/oradata/trade/trade/redo03.log
Thu Sep 9 08:33:15 2010
Thread 1 advanced to log sequence 4571 (LGWR switch)
Current log# 1 seq# 4571 mem# 0: /oracle01/app/oracle/oradata/trade/trade/redo01.log
Thu Sep 9 08:37:12 2010
Thread 1 advanced to log sequence 4572 (LGWR switch)
Current log# 2 seq# 4572 mem# 0: /oracle01/app/oracle/oradata/trade/trade/redo02.log
Thu Sep 9 08:41:19 2010
Thread 1 advanced to log sequence 4573 (LGWR switch)
Current log# 3 seq# 4573 mem# 0: /oracle01/app/oracle/oradata/trade/trade/redo03.log
Thu Sep 9 08:45:17 2010
Thread 1 advanced to log sequence 4574 (LGWR switch)
Current log# 1 seq# 4574 mem# 0: /oracle01/app/oracle/oradata/trade/trade/redo01.log
Thu Sep 9 08:48:44 2010
Thread 1 advanced to log sequence 4575 (LGWR switch)
Current log# 2 seq# 4575 mem# 0: /oracle01/app/oracle/oradata/trade/trade/redo02.log
Thu Sep 9 08:51:45 2010
Thread 1 advanced to log sequence 4576 (LGWR switch)
Current log# 3 seq# 4576 mem# 0: /oracle01/app/oracle/oradata/trade/trade/redo03.log
Thu Sep 9 08:55:19 2010
Thread 1 advanced to log sequence 4577 (LGWR switch)
Current log# 1 seq# 4577 mem# 0: /oracle01/app/oracle/oradata/trade/trade/redo01.log
Thu Sep 9 08:58:15 2010
Thread 1 advanced to log sequence 4578 (LGWR switch)
Current log# 2 seq# 4578 mem# 0: /oracle01/app/oracle/oradata/trade/trade/redo02.log
Thu Sep 9 09:00:50 2010
Thread 1 advanced to log sequence 4579 (LGWR switch)
Current log# 3 seq# 4579 mem# 0: /oracle01/app/oracle/oradata/trade/trade/redo03.log
Thu Sep 9 09:03:40 2010
Thread 1 advanced to log sequence 4580 (LGWR switch)
Current log# 1 seq# 4580 mem# 0: /oracle01/app/oracle/oradata/trade/trade/redo01.log
Thu Sep 9 09:06:43 2010
Thread 1 advanced to log sequence 4581 (LGWR switch)
.
.
.
Thu Sep 9 10:00:04 2010
Thread 1 advanced to log sequence 4593 (LGWR switch)
Current log# 2 seq# 4593 mem# 0: /oracle01/app/oracle/oradata/trade/trade/redo02.log
Thu Sep 9 10:08:42 2010
Thread 1 advanced to log sequence 4594 (LGWR switch)
Current log# 3 seq# 4594 mem# 0: /oracle01/app/oracle/oradata/trade/trade/redo03.log
Thu Sep 9 10:18:18 2010
Thread 1 advanced to log sequence 4595 (LGWR switch)
可以看到,在系统忙时,每2、3分钟就需要切换一次redo文件,检查数据库中redo的配置:
SQL> SELECT * FROM V$LOG;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- -------- -------- --- --------- ------------- ----------
1 1 4616 52428800 1 NO CURRENT 609502223 09-SEP-10
2 1 4614 52428800 1 YES INACTIVE 609466117 09-SEP-10
3 1 4615 52428800 1 YES INACTIVE 609475846 09-SEP-10
每个日志文件才分配了50M,难怪系统频繁的切换日志。怀疑安装数据库的时候采用了缺省的配置。
既然日志文件大小缺省配置,不会系统中的内存参数也是缺省配置大小吧,检查数据库中SGA配置:
SQL> show sga
Total System Global Area 8589934592 bytes
Fixed Size 2097760 bytes
Variable Size 5268049312 bytes
Database Buffers 3305111552 bytes
Redo Buffers 14675968 bytes
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 1G
SGA分配8G,PGA分配1G,操作系统有32G内存,这个分配显然不合理。
进一步检查系统的内存分配情况:
SQL> show parameter cache
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_advice string ON
db_cache_size big integer 3104M
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
session_cached_cursors integer 20
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 6560M
用户仅指定了6G的内存给SGA,Oracle在自动分配的时候还将大部分给了共享池,只给了DB_CACHE不到3G的空间,难怪系统性能很差。
调整了SGA以及redo文件的大小,并重启数据库后,性能问题解决。
来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/4227/viewspace-680841/ ,如需转载,请注明出处,否则将追究法律责任。