数据库突然变得剧慢无比．有个oracle 进程居然用了9.66g的内存 - 操作系统, 数据库

绝世坏男人

question:

昨天下午，一个测试库的数据库突然变得剧慢无比．内存包括虚拟内存都用光了．pmon进程报错，是不是oracle 的but,下面是当时记下的一些操作系统的性能数据．数据库因为已经作不了任何操作，所以没有作监控．

os : HP-UX domea B.11.11
db: 9205

top:
System: domea Tue Mar 29 16:49:02 2005
Load averages: 0.12, 0.13, 0.14
420 processes: 404 sleeping, 16 running
Cpu states:
CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
0 0.11 0.0% 0.0% 0.4% 99.6% 0.0% 0.0% 0.0% 0.0%
1 0.08 1.9% 0.0% 1.9% 96.1% 0.0% 0.0% 0.0% 0.0%
2 0.09 0.0% 0.0% 1.2% 98.8% 0.0% 0.0% 0.0% 0.0%
3 0.08 1.9% 0.0% 6.0% 92.1% 0.0% 0.0% 0.0% 0.0%
4 0.17 2.8% 0.0% 3.2% 94.0% 0.0% 0.0% 0.0% 0.0%
5 0.18 5.3% 0.0% 7.6% 87.2% 0.0% 0.0% 0.0% 0.0%
6 0.35 2.5% 0.0% 1.9% 95.6% 0.0% 0.0% 0.0% 0.0%
7 0.20 5.8% 0.0% 4.7% 89.5% 0.0% 0.0% 0.0% 0.0%
8 0.00 0.0% 0.0% 0.7% 99.3% 0.0% 0.0% 0.0% 0.0%
9 0.14 0.0% 0.0% 1.1% 98.9% 0.0% 0.0% 0.0% 0.0%
10 0.01 1.9% 0.0% 1.4% 96.7% 0.0% 0.0% 0.0% 0.0%
11 0.04 0.7% 0.0% 1.2% 98.1% 0.0% 0.0% 0.0% 0.0%
12 0.03 0.7% 0.0% 6.3% 93.0% 0.0% 0.0% 0.0% 0.0%
13 0.00 4.9% 0.0% 1.2% 93.8% 0.0% 0.0% 0.0% 0.0%
14 0.25 0.2% 0.0% 1.4% 98.4% 0.0% 0.0% 0.0% 0.0%
15 0.14 1.1% 0.0% 6.0% 93.0% 0.0% 0.0% 0.0% 0.0%
--- ---- ----- ----- ----- ----- ----- ----- ----- -----
avg 0.12 1.8% 0.0% 2.8% 95.4% 0.0% 0.0% 0.0% 0.0%

Memory: 15334200K (13523288K) real, 18027580K (14831784K) virtual, 48964K free Page# 1/20

CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
5 ? 14719 oracle8i 154 20 3135M 2508K run 7249:36 36.26 36.20 ora_lmd0_gz97
15 ? 27102 oracle 130 20 22220M 17344K sleep 1:14 12.62 12.60 oracleibss1
13 ? 25853 oracle8i 154 20 3135M 2512K sleep 0:52 12.05 12.03 oraclegz97
15 ? 2 root 128 20 32K 32K sleep 1:30 10.46 10.45 vhand
12 ? 8631 root -16 10 56900K 35180K run 204:52 3.08 3.08 midaemon
2 ? 14744 oracle8i 154 20 3135M 2484K sleep 649:13 2.94 2.93 ora_lck0_gz97
8 ? 49 root 152 20 16064K 16064K run 577:06 1.56 1.56 vxfsd
11 ? 27309 oracle8i 154 20 3134M 1984K sleep 0:00 1.48 1.29 oraclegz97
14 ? 0 root 127 20 32K 0K sleep 4:47 1.22 1.21 swapper
8 ? 20 root 147 20 32K 32K sleep 2:31 1.13 1.12 lvmkd
3 ? 24 root 147 20 32K 32K sleep 2:33 1.11 1.10 lvmkd
6 ? 19 root 147 20 32K 32K sleep 2:33 1.09 1.09 lvmkd
5 ? 23 root 147 20 32K 32K sleep 2:33 1.09 1.09 lvmkd
7 ? 22 root 147 20 32K 32K sleep 2:33 1.07 1.06 lvmkd
1 ? 21 root 147 20 32K 32K sleep 2:35 1.05 1.05 lvmkd
7 ? 17747 i3 168 20 11168K 4540K sleep 3:59 0.82 0.81 psi_os
6 ? 14717 oracle8i 154 20 3137M 2524K sleep 202:19 0.78 0.78 ora_lmon_gz97
14 ? 4075 root -27 20 10964K 7136K run 210:14 0.76 0.76 cmcld
12 ? 12425 i3 152 20 301M 36256K run 0:31 0.37 0.37 java
0 ? 1163 root 152 20 3336K 580K run 27:53 0.34 0.34 syncer
4 ? 27320 oracle8i 154 20 3134M 1776K sleep 0:00 0.55 0.31 oraclegz97
7 ? 8012 oracle71 154 20 2096M 2504K sleep 207:03 0.23 0.22 ora_diag_ibss71


看到内存用光了．

虚拟内存也用完了.
$ vmstat 5 5
procs memory page faults cpu
r b w avm free re at pi po fr de sr in sy cs us sy id
3 10 26 3823880 12986 32 0 0 0 0 0 1 12796 68427 5927 16 2 82
3 10 26 3823880 12482 3 0 18 2271 561 0 16787 18971 50970 6420 2 2 96
1 12 31 3823837 11723 10 0 19 2326 466 0 14136 19236 50822 6393 2 3 95
1 12 31 3823837 13289 3 0 18 2231 372 0 18978 20242 51652 6522 2 3 95
3 6 37 4229496 11354 9 0 26 3188 495 0 16523 20979 52421 6041 2 2 95

glance 的数据:
ProcList CPU Rpt Mem Rpt Disk Rpt NextKeys SlctProc Help Exit
B3692A GlancePlus C.03.72.00 16:56:44 domea 9000/800 Current Avg High
--------------
CPU Util S SU | 12% 12% 12%
Disk Util F F |100% 100% 100%
Mem Util S SU UBB |100% 100% 100%
Networkil U UR R | 83% 83% 83%
--------------
PROCESS LIST Users= 16
User CPU Util Cum Disk Thd
Process Name PID PPID Pri Name ( 1600% max) CPU IO Rate RSS Cnt
---------------------
vhand 2 0 128 root 99.1/ 0.0 43.3 34.5/ 0.3 32kb 1
ora_lmd0_gz 14719 1 154 oracle8i 45.5/14.6 78242.6 0.0/ 0.0 101.1mb 1
glance 27713 6188 168 ibss 20.9/10.8 0.3 24.5/64.8 9.4mb 1
midaemon 8631 1 -16 root 2.7/ 2.3 12256.0 0.0/ 0.0 45.8mb 3
swapper 0 0 127 root 0.0/ 0.0 14.3 3.6/ 0.9 32kb 1
oraclegz97 25853 1 154 oracle8i 0.0/13.7 142.8 0.0/ 124 101.2mb 1
oraclegz97 22584 1 156 oracle8i 0.0/ 0.1 3.5 0.0/ 0.0 101.0mb 1
oraclegz97 25800 1 154 oracle8i 0.0/ 0.0 0.3 0.0/ 0.0 99.9mb 1
ora_ckpt_gz 14725 1 156 oracle8i 0.0/ 0.0 221.1 0.0/ 1.0 101.1mb 1
ora_smon_gz 14727 1 156 oracle8i 0.0/ 0.0 61.6 0.0/ 0.0 103.2mb 1
oraclegz97 23966 1 156 oracle8i 0.0/ 0.3 6.9 0.0/ 5.3 101.0mb 1
tftpd 27714 1688 149 root 0.0/ 0.0 0.0 6.3/ 6.3 36kb 1
oraclegz97 17954 1 154 oracle8i 0.0/ 3.1 298.7 0.0/71.4 119.4mb 1
oraclegz97 27320 1 154 oracle8i 0.0/ 0.0 0.1 0.0/ 0.2 100.9mb 1
oraclegz97 27354 1 154 oracle8i 0.0/ 0.2 0.7 0.0/ 0.8 102.1mb 1
oracleibss1 27526 27521 130 oracle 0.0/31.7 69.9 0.0/ 0.1 9.66gb 1 这个进程占用了大量的内存.
java 12425 1 154 i3 0.0/ 0.2 28.7 0.0/ 0.0 33.9mb 20
oraclegz97 11651 11650 154 oracle8i 0.0/ 0.0 0.7 0.0/ 0.0 280.2mb 1
oraclegz97 5580 1 154 oracle8i 0.0/ 0.0 0.3 0.0/ 0.0 148.4mb 1
oraclegz97 22895 1 154 oracle8i 0.0/ 0.0 0.2 0.0/ 0.0 116.3mb 1
ora_bsp0_gz 14746 1 156 oracle8i 0.0/ 0.1 646.1 0.0/ 0.0 100.0mb 1
ora_reco_gz 14729 1 156 oracle8i 0.0/ 0.0 1.9 0.0/ 0.0 91.7mb 1
ora_lgwr_gz 14723 1 156 oracle8i 0.0/ 0.1 624.1 0.0/ 3.8 100.1mb 1
ora_reco_ib 8034 1 156 oracle71 0.0/ 0.0 3.9 0.0/ 0.0 169.2mb 1
ora_smon_ib 8032 1 156 oracle71 0.0/ 0.0 56.4 0.0/ 0.0 205.9mb 1
ora_dbw0_gz 14721 1 156 oracle8i 0.0/ 0.1 624.6 0.0/ 7.2 107.0mb 1
ora_diag_ib 8012 1 154 oracle71 0.0/ 0.3 1536.3 0.0/ 0.0 171.3mb 1
ora_pmon_ib 8010 1 154 oracle71 0.0/ 0.0 57.4 0.0/ 0.0 172.6mb 1
ora_ckpt_ib 8030 1 156 oracle71 0.0/ 0.0 180.7 2.7/ 1.1 177.6mb 1
vxfsd 49 0 138 root 0.0/ 0.8 4379.2 3.6/ 3.0 15.7mb 51
ora_lmon_ib 8014 1 154 oracle71 0.0/ 0.1 623.7 0.0/ 0.0 172.3mb 1
ora_qmn0_ib 8038 1 156 oracle71 0.0/ 0.0 92.5 0.0/ 0.0 172.4mb 1
ora_lmon_gz 14717 1 154 oracle8i 0.0/ 0.4 2257.3 0.0/ 0.0 101.1mb 1
ora_lck0_gz 14744 1 154 oracle8i 0.0/ 1.6 8421.3 0.0/ 0.0 101.0mb 1
ora_pmon_gz 14715 1 156 oracle8i 0.0/ 0.0 104.0 0.0/ 0.0 101.1mb 1
ora_lgwr_ib 8028 1 156 oracle71 0.0/ 0.0 52.7 0.0/ 0.2 183.6mb 1

这个是找到的那个用掉剧多内存的trc文件:
$ more ibss1_ora_27526.trc
/oracle/admin/ibss/udump/ibss1_ora_27526.trc
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
ORACLE_HOME = /oracle/product/9.2.0
System name: HP-UX
Node name: domea
Release: B.11.11
Version: U
Machine: 9000/800
Instance name: ibss1
Redo thread mounted by this instance: 0
Oracle process number: 0
27526

*** 2005-03-29 16:57:18.458
Ioctl ASYNC_ADDSEG error, errno = 11


数据库的日志:
Thread 1 advanced to log sequence 55716
Current log# 3 seq# 55716 mem# 0: /dev/vgdata1/rrdfl_3
Tue Mar 29 16:03:34 2005
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
Tue Mar 29 16:21:13 2005
ORA-01555 caused by SQL statement below (Query Duration=101414 sec, SCN: 0x0603.efc35c06):
Tue Mar 29 16:21:13 2005
select * from CONV_REC_KHFW where prog='p_conv_gz97_gh_ask' and rec_date>trunc(sysdate)-3
Tue Mar 29 16:23:02 2005
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/oracle/arch1/arch1' SCOPE=BOTH SID='ibss1';
Tue Mar 29 16:23:39 2005
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/oracle/arch2/arch2' SCOPE=BOTH SID='ibss2';
Tue Mar 29 16:32:10 2005
ALTER SYSTEM SET sort_area_size=1048576000 SCOPE=SPFILE;
Tue Mar 29 16:34:47 2005
ALTER SYSTEM SET log_buffer=1048576000 SCOPE=SPFILE;
Tue Mar 29 16:35:28 2005
ALTER SYSTEM SET sga_max_size='22608268080' SCOPE=SPFILE;
Tue Mar 29 16:35:32 2005
Errors in file /oracle/admin/ibss/udump/ibss1_ora_24209.trc:
ORA-00031: session marked for kill
Tue Mar 29 16:40:52 2005
ALTER SYSTEM SET timed_statistics=TRUE SCOPE=BOTH;
Tue Mar 29 16:46:06 2005
Shutting down instance: further logons disabled
Shutting down instance (immediate)


answer1:


__________________
人生开启新的篇章


answer2:

再打开看看啊
select * from CONV_REC_KHFW where prog='p_conv_gz97_gh_ask' and rec_date>trunc(sysdate)-3 为什么会这么厉害的


answer3:


__________________
有环境就有可能!
Oracle Oracle ......
渴望成为达者
MSN: rogerhuang75@hotmail.com
==我在北京==
不让疑问陪我过夜
I LIKE ORACLE 10G

请比较资深DBA，口语好比较重要，有意者请MSN


answer4:

SQL> select count(*) from CONV_REC_KHFW;

COUNT(*)
----------
5379847

SQL> select * from CONV_REC_KHFW where prog='p_conv_gz97_gh_ask' and rec_date>trunc(sysdate)-3 ;

未选定行

已用时间: 00: 00: 00.00


answer5:


__________________
人生开启新的篇章


answer6:

从glance的结果来看，disk utility已经达到了100%，同时，Ioctl ASYNC_ADDSEG error, errno = 11，我觉得瓶颈是由IO方面引起来的，一方面，你的数据库异步IO应该没有开启，另外，可能需要检查应用程序为什么会产生如此大的IO。
另外，机器配置那么高，应该有带外部存储设备，也应该检查一下外部存储的write cache是否有打开，这个参数也相当重要。


answer7:

当时我接到报障后，就看了这些资料，然后数据库就被另外的人停掉了，内存使用，io 都下来了．也没办法再查看了．现在要分析原因．
Ioctl ASYNC_ADDSEG error, errno = 11　
我看了，是这个原因.
查看 /usr/conf/sys/errno.h
发现 #define EAGAIN 11 /* No more processes */


answer8:


__________________
人生开启新的篇章


answer9:

这可能与操作系统设置有点关系
If the application accessing async driver is not owned by superuser or by a user who is a member of a group that has MLOCK privilege, ASYNC_CONFIG and ASYNC_ADDSEG ioctl()will fail and errno will be set to EPERM.


answer10:

quote:

---

最初由 qish69 发布
SQL> select count(*) from CONV_REC_KHFW;

COUNT(*)
----------
5379847

SQL> select * from CONV_REC_KHFW where prog='p_conv_gz97_gh_ask' and rec_date>trunc(sysdate)-3 ;

未选定行

已用时间: 00: 00: 00.00

---

00: 00: 00.00???? 我不太相信
能列一下执行计划吗?


answer11:


__________________
有环境就有可能!
Oracle Oracle ......
渴望成为达者
MSN: rogerhuang75@hotmail.com
==我在北京==
不让疑问陪我过夜
I LIKE ORACLE 10G

请比较资深DBA，口语好比较重要，有意者请MSN


answer12:

把异步写的开发先开了再说


answer13:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CONV_REC_KHFW'
2 1 INDEX (RANGE SCAN) OF 'IND2_CONV_REC_KHFW' (NON-UNIQUE)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
435 bytes sent via SQL*Net to client
235 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed


answer14:


__________________
人生开启新的篇章


answer15:

#define EAGAIN 11 /* No more processes */ 什么意思?建立不了新的进程了?

感谢您读完这篇文章，如果您对consistent gets 突然变小问题感兴趣，请继续阅读下面的文章。
consistent gets 突然变小	consistent gets 突然变小

这个错误是什么意思，怎么导致的呢 - ...
question:-Win2000 + Oracle8.16 启动数据库的时候出了这个错误 ORA-00600: internal error code, arguments: [3051], [82], [73], [], [], [], [], [] ORA-04030: out of process memory when trying to allocate 8389132 bytes (pga heap,THREAD RECOVERY) 如何解决呢-
大家来谈谈latch是个什么东西？ - ...
question:-最近在看performance tuning，里面有latch，不是很理解 谁能讲将啊-answer1:这种概念性的问题一般在concept中讲得最清楚了！ 9i的在<<Database Concepts>> Part VII Data Protection---How Oracle Locks Data---Latches and Internal Locks-answer2: 毋以善小而不为,毋以恶小而为之-answer3:
孫偉琴: 無業務意義代理主鍵設計 - ...
question:在关系数据库表中为什么提倡使用代理主键？ 选自<<精通Hibernate：Java对象持久化技术详解>> 作者：孙卫琴 来源:www.javathinker.org 如果转载，请标明出处，谢谢 在关系数据库表中，用主键来识别记录并保证每条记录的惟一性。作为主键的字段必须满足以下条件： (1) 不允许为null。 (2)每条记录具有惟一的主键值，不允许主键值重复。 (3) 每条记录的主键值永远不会改变。 在CUSTOMERS表中，如果把NAME字段作为主键，前提条件是： (1) 每条记录的客户姓名不允许为null。 ...
如何查看删除job的记录？ -
question:今天突然发现某个job被用户删除了，请问大家有什么办法可以查看删除这个job的记录吗？answer1:logminer工具answer2: 我懒但想学习 过了大半个月才发现的bloganswer3:
用loader装文本文件，记录数有2万多条， ...
question:-用loader装文本文件，记录数有2万多条，老是报错误 :sqlldr.exe - 应用程序错误 "0x7800ffe7"指令引用的"0x018e0200"内存，该内存不能为"written". ，该如何处理调整数据库？-