ORA-4031问题的诊断 - Oracle数据库管理 - ITPUB论坛

Kamus
版主

精华贴数 52
个人空间 400
技术积分 47987 (18)
社区积分 3611 (504)
注册日期 2002-5-26
论坛徽章:49

#1

使用道具

发表于 2007-12-18 22:06

ORA-4031问题的诊断

这次在客户出处理ORA-4031的故障，但是发现在系统正常期间却没有什么值得怀疑的痕迹。跟大家讨论一下。

操作系统：IBM AIX 5.3
数据库：Oracle 9.2.0.6
Shared Pool：2G

问题现象：
Weblogic中间件收到ORA-4031报错，然后SQLPLUS无法登陆，重新启动数据库，系统恢复正常。同样的错误在最近2个月内发生了3次。在第一次发生问题前的几天内刚实施了Change Data Capture，通过ODI实施的。

告警日志内容：

[Copy to clipboard] [ - ]

CODE:

Tue Dec 11 17:14:49 2007
Errors in file /oracle/app/admin/ctais2/udump/ctais2_ora_778732.trc:
ORA-04031: unable to allocate 4216 bytes of shared memory ("shared pool","IDX_DJ_NSRXX_P_NSRMCCTAIS2","sga heap(2,0)","library cache")
ORA-04031: unable to allocate 4216 bytes of shared memory ("shared pool","IDX_DJ_NSRXX_P_NSRMCCTAIS2","sga heap(2,0)","library cache")
Tue Dec 11 17:14:51 2007
Errors in file /oracle/app/admin/ctais2/bdump/ctais2_pmon_393248.trc:
ORA-04031: unable to allocate 4216 bytes of shared memory ("shared pool","unknown object","sga heap(2,0)","library cache")
。。。。。。。。。
Tue Dec 11 17:16:40 2007
Completed checkpoint up to RBA [0x2225.2.10], SCN: 0x07ba.62a2620f
Tue Dec 11 17:20:56 2007
Beginning log switch checkpoint up to RBA [0x2226.2.10], SCN: 0x07ba.62a2b207
Thread 1 advanced to log sequence 8742
  Current log# 1 seq# 8742 mem# 0: /oracle/app/oradata/ctais2/redo01a.log
  Current log# 1 seq# 8742 mem# 1: /oracle/app/oradata/ctais2/redo01b.log
Tue Dec 11 17:20:56 2007
ARC0: Evaluating archive log 3 thread 1 sequence 8741
ARC0: Beginning to archive log 3 thread 1 sequence 8741
Creating archive destination LOG_ARCHIVE_DEST_1: '/oralog/1_8741.dbf'
ARC0: Completed archiving  log 3 thread 1 sequence 8741
。。。。。。。。。
Tue Dec 11 17:46:13 2007
Errors in file /oracle/app/admin/ctais2/udump/ctais2_ora_1413316.trc:
ORA-00600: internal error code, arguments: [729], [864], [space leak], [], [], [], [], []
Tue Dec 11 17:46:14 2007
Errors in file /oracle/app/admin/ctais2/udump/ctais2_ora_1921394.trc:
ORA-00600: internal error code, arguments: [729], [240], [space leak], [], [], [], [], []

因为报错是在sga heap(2,0)，所以在ctais2_ora_778732.trc中查看Subpool 2的dump，但是free memory还有 148200480，报错的library cache区域也只有173949024而已。

[Copy to clipboard] [ - ]

CODE:

===============================
Memory Utilization of Subpool 2
===============================
   Allocation Name       Size
_________________________  __________
"free memory             " 148200480
"miscellaneous          "    8271560
"trace buf hdr xtend    "    182160
"errors                "          0
"partitioning d          "          0
"PL/SQL SOURCE          "          0
"transaction             "    641040
"parameters             "          0
"sim memory hea          "    2656472
"table definiti          "          0
"trigger inform          "          0
"MTTR advisory          "    1015088
"PL/SQL PPCODE          "          0
"transaction co          "          0
"PL/SQL DIANA          "          0
"joxlod: in ehe          "    70808
"db_block_hash_buckets "    1572768
"UNDO INFO HASH          "    77600
"kglsim hash table bkts "    700416
"Temporary Tables State Ob"    517080
"session param values    "    3020160
"trace buffer          "    3325952
"dictionary cache       "    1065472
"Checkpoint queue       "    1283200
"kglsim heap             "    8424000
"temporary tabl          "    287456
"PL/SQL MPCODE          "    37304
"library cache          " 173949024
"kglsim object batch    "    7608384
"sql area                "    163320
"sessions                "    995072
"PLS non-lib hp          "       2088
"event statistics per sess"    4151504
"fixed allocation callback"       288
"KGLS heap             "    61320
"trigger defini          "          0
"obj htab chunk          "    655872
"KQR M PO                "    162864

统计部分的命中率也没什么大问题。

[Copy to clipboard] [ - ]

CODE:

LIBRARY CACHE STATISTICS:
namespace          gets hit ratio    pins hit ratio reloads invalids
-------------- --------- --------- --------- --------- ---------- ----------
CRSR          498593069    0.986 1235442503    0.842 38042363    575091
TABL/PRCD/TYPE 787258490    0.998 1413907949    0.987 6769881       0
BODY/TYBD       465948    0.993 467940    0.986    3152       0
TRGR          27415575    0.999  27417188    0.993    172702       0
INDX          141777026    0.999  99729644    0.998       690       0
CLST             524459    0.989 706762    0.983       1       0

后面那个ORA-600报UGA内存泄露的错误，估计应该是4031错误的后遗症，而不是导致4031的原因，因为：
1. 泄露的内存并不大，最大的10480而已。
2. 查看Call Stack Trace特征，有opilof，表示是session logout的时候出现的内存泄露，而因为是dedicated server模式，所以即使是UGA内存泄露也不会影响到SGA

关于ORA-600 [729] 的问题可以参考Metalink Note:403584.1

今天在系统正常期间，检查共享池sharedpool各chunck的情况，很正常，大小size的free chunck都不少。

[Copy to clipboard] [ - ]

CODE:

SQL> select KSMCHIDX "SubPool", 'sga heap('||KSMCHIDX||',0)'sga_heap,ksmchcom ChunkComment,
  2  decode(round(ksmchsiz/1000),0,'0-1K', 1,'1-2K', 2,'2-3K',3,'3-4K',
  3  4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,
  4  '8-9k', 9,'9-10k','> 10K') "size",
  5  count(*),ksmchcls Status, sum(ksmchsiz) Bytes
  6  from x$ksmsp
  7  where KSMCHCOM = 'free memory'
  8  group by ksmchidx, ksmchcls,
  9  'sga heap('||KSMCHIDX||',0)',ksmchcom, ksmchcls,decode(round(ksmchsiz/1000),0,'0-1K',
10  1,'1-2K', 2,'2-3K', 3,'3-4K',4,'4-5K',5,'5-6k',6,
11  '6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K');

         SubPool SGA_HEAP       CHUNKCOMMENT    size                COUNT(*) STATUS                BYTES
-------------------- --------------- ---------------- ---------- -------------------- -------- --------------------
               1 sga heap(1,0) free memory    0-1K                      4842 free                909400
               1 sga heap(1,0) free memory    1-2K                      297 free                369896
               1 sga heap(1,0) free memory    2-3K                      822 free                1903264
               1 sga heap(1,0) free memory    3-4K                      4616 free                13839336
               1 sga heap(1,0) free memory    4-5K                      3634 free                13827256
               1 sga heap(1,0) free memory    8-9k                         3 free                   24808
               1 sga heap(1,0) free memory    1-2K                         1 R-free                1416
               1 sga heap(1,0) free memory    6-7k                         1 R-free                5696
               1 sga heap(1,0) free memory    9-10k                      1 R-free                8768
               1 sga heap(1,0) free memory    > 10K                      77 R-free             75113896
               2 sga heap(2,0) free memory    0-1K                      5948 free                729904
               2 sga heap(2,0) free memory    1-2K                      55 free                   52056
               2 sga heap(2,0) free memory    2-3K                      225 free                453936
               2 sga heap(2,0) free memory    3-4K                      6799 free                21146640
               2 sga heap(2,0) free memory    4-5K                      4900 free                18657216
               2 sga heap(2,0) free memory    5-6k                         1 free                   4624
               2 sga heap(2,0) free memory    7-8k                         1 free                   7168
               2 sga heap(2,0) free memory    8-9k                         4 free                   32000
               2 sga heap(2,0) free memory    1-2K                         1 R-free                   648
               2 sga heap(2,0) free memory    8-9k                         2 R-free                16304
               2 sga heap(2,0) free memory    > 10K                      80 R-free             78728920
               3 sga heap(3,0) free memory    0-1K                      5572 free                914096
               3 sga heap(3,0) free memory    1-2K                      204 free                153120
               3 sga heap(3,0) free memory    2-3K                      2818 free                6129920
               3 sga heap(3,0) free memory    3-4K                      5323 free                15764400
               3 sga heap(3,0) free memory    4-5K                      3710 free                14163560
               3 sga heap(3,0) free memory    2-3K                         1 R-free                1712
               3 sga heap(3,0) free memory    4-5K                         1 R-free                4496
               3 sga heap(3,0) free memory    8-9k                         3 R-free                24456
               3 sga heap(3,0) free memory    9-10k                      2 R-free                17656
               3 sga heap(3,0) free memory    > 10K                      64 R-free             75655456
               4 sga heap(4,0) free memory    0-1K                      6566 free                1192232
               4 sga heap(4,0) free memory    1-2K                      405 free                289456
               4 sga heap(4,0) free memory    2-3K                      1300 free                2728536
               4 sga heap(4,0) free memory    3-4K                      5989 free                17931464
               4 sga heap(4,0) free memory    4-5K                      3683 free                14001176
               4 sga heap(4,0) free memory    9-10k                      1 free                   8736
               4 sga heap(4,0) free memory    1-2K                         1 R-free                   616
               4 sga heap(4,0) free memory    3-4K                         1 R-free                2624
               4 sga heap(4,0) free memory    5-6k                         1 R-free                5424
               4 sga heap(4,0) free memory    6-7k                         1 R-free                5504
               4 sga heap(4,0) free memory    8-9k                         1 R-free                8152
               4 sga heap(4,0) free memory    > 10K                      58 R-free             75088136
               5 sga heap(5,0) free memory    0-1K                      5695 free                689448
               5 sga heap(5,0) free memory    1-2K                      771 free                847032
               5 sga heap(5,0) free memory    2-3K                      1160 free                2422184
               5 sga heap(5,0) free memory    3-4K                      4939 free                15128240
               5 sga heap(5,0) free memory    4-5K                      3140 free                12045192
               5 sga heap(5,0) free memory    4-5K                         1 R-free                3592
               5 sga heap(5,0) free memory    8-9k                         1 R-free                8152
               5 sga heap(5,0) free memory    > 10K                      73 R-free             79633288
               6 sga heap(6,0) free memory    0-1K                      5840 free                1088488
               6 sga heap(6,0) free memory    1-2K                      309 free                412944
               6 sga heap(6,0) free memory    2-3K                      2362 free                5076752
               6 sga heap(6,0) free memory    3-4K                      5250 free                15702056
               6 sga heap(6,0) free memory    4-5K                      3878 free                14726920
               6 sga heap(6,0) free memory    5-6k                         1 free                   4648
               6 sga heap(6,0) free memory    4-5K                         1 R-free                3912
               6 sga heap(6,0) free memory    6-7k                         3 R-free                18840
               6 sga heap(6,0) free memory    7-8k                         1 R-free                7080
               6 sga heap(6,0) free memory    > 10K                      94 R-free             73405856

61 rows selected.

[ 本帖最后由 Kamus 于 2007-12-18 22:44 编辑 ]

Kamus 上传了这个附件：

2007-12-18 22:44
下载次数: 27

20071217.ctais.4031.issue.zip (2.31 MB)

__________________

有事情请发Gmail邮箱，站内IM可能不能及时回复。

***Chanel [K]***

从明天起, 做一个幸福的人
喂马, 劈柴, 周游世界
从明天起, 关心粮食和蔬菜
我有一所房子面朝大海, 春暖花开

只看该作者

Kamus
版主

精华贴数 52
个人空间 400
技术积分 47987 (18)
社区积分 3611 (504)
注册日期 2002-5-26
论坛徽章:49

#2

使用道具

发表于 2007-12-18 22:16

在正常期间，共享池占用1.4G左右。。。恩，是比较大，因为系统没有绑定变量，cursor_sharing=similar，v$sqlarea中version_count大于50的很多，最多的前面几个SQL都超过1000的version count。

[Copy to clipboard] [ - ]

CODE:

SQL> select cr_shared_pool_size,
  2  sum_obj_size, sum_sql_size,
  3  sum_user_size,
  4  (sum_obj_size + sum_sql_size+sum_user_size)* 1.3 min_shared_pool
  5  from (select sum(sharable_mem) sum_obj_size
  6  from v$db_object_cache where type<> 'CURSOR'),
  7  (select sum(sharable_mem) sum_sql_size from v$sqlarea),
  8  (select sum(250*users_opening) sum_user_size from v$sqlarea),
  9  (select to_Number(b.ksppstvl) cr_shared_pool_size
10  from x$ksppi a, x$ksppcv b, x$ksppsv c
11  where a.indx = b.indx and a.indx = c.indx
12  and a.ksppinm = 'shared_pool_size' );

CR_SHARED_POOL_SIZE    SUM_OBJ_SIZE    SUM_SQL_SIZE SUM_USER_SIZE  MIN_SHARED_POOL
------------------- ---------------- ---------------- ---------------- ----------------
   2,013,265,920    23,472,765 1,048,293,638    28,898,000 1,430,863,724

SQL>
SQL> /

CR_SHARED_POOL_SIZE    SUM_OBJ_SIZE    SUM_SQL_SIZE SUM_USER_SIZE  MIN_SHARED_POOL
------------------- ---------------- ---------------- ---------------- ----------------
   2,013,265,920    23,451,349 1,050,169,800    28,908,125 1,433,288,056

SQL>
SQL> /

CR_SHARED_POOL_SIZE    SUM_OBJ_SIZE    SUM_SQL_SIZE SUM_USER_SIZE  MIN_SHARED_POOL
------------------- ---------------- ---------------- ---------------- ----------------
   2,013,265,920    23,393,677 1,051,046,611    28,919,125 1,434,367,237

SQL> /

CR_SHARED_POOL_SIZE    SUM_OBJ_SIZE    SUM_SQL_SIZE SUM_USER_SIZE  MIN_SHARED_POOL
------------------- ---------------- ---------------- ---------------- ----------------
   2,013,265,920    23,301,214 1,051,491,020    28,942,625 1,434,855,317

SQL> /

CR_SHARED_POOL_SIZE    SUM_OBJ_SIZE    SUM_SQL_SIZE SUM_USER_SIZE  MIN_SHARED_POOL
------------------- ---------------- ---------------- ---------------- ----------------
   2,013,265,920    23,180,604 1,039,327,136    29,829,875 1,420,038,900

SQL>
SQL>
SQL> /

CR_SHARED_POOL_SIZE    SUM_OBJ_SIZE    SUM_SQL_SIZE SUM_USER_SIZE  MIN_SHARED_POOL
------------------- ---------------- ---------------- ---------------- ----------------
   2,013,265,920    23,057,520 1,038,933,452    29,851,500 1,419,395,214

SQL>
SQL>
SQL> /

CR_SHARED_POOL_SIZE    SUM_OBJ_SIZE    SUM_SQL_SIZE SUM_USER_SIZE  MIN_SHARED_POOL
------------------- ---------------- ---------------- ---------------- ----------------
   2,013,265,920    23,001,142 1,039,057,979    29,848,500 1,419,479,907

SQL>
SQL> /

CR_SHARED_POOL_SIZE    SUM_OBJ_SIZE    SUM_SQL_SIZE SUM_USER_SIZE  MIN_SHARED_POOL
------------------- ---------------- ---------------- ---------------- ----------------
   2,013,265,920    23,063,769 1,040,401,225    29,864,250 1,421,328,017

SQL>
SQL>
SQL> /

CR_SHARED_POOL_SIZE    SUM_OBJ_SIZE    SUM_SQL_SIZE SUM_USER_SIZE  MIN_SHARED_POOL
------------------- ---------------- ---------------- ---------------- ----------------
   2,013,265,920    23,062,497 1,041,170,031    29,950,000 1,422,437,286

SQL>

__________________

有事情请发Gmail邮箱，站内IM可能不能及时回复。

***Chanel [K]***

从明天起, 做一个幸福的人
喂马, 劈柴, 周游世界
从明天起, 关心粮食和蔬菜
我有一所房子面朝大海, 春暖花开

只看该作者

wenaini
高级会员

精华贴数 0
个人空间 0
技术积分 3526 (506)
社区积分 392 (2021)
注册日期 2004-5-9
论坛徽章:9

#3

使用道具

发表于 2007-12-18 22:19

oh,it's living.sofa

只看该作者

花好月不圆
初级会员

来自 shanghai
精华贴数 5
个人空间 20
技术积分 12318 (124)
社区积分 3187 (561)
注册日期 2003-8-11
论坛徽章:62

#4

使用道具

发表于 2007-12-18 22:20

关注
也有遇到4031 的,还没解决

只看该作者

happig
happig

精华贴数 0
个人空间 0
技术积分 50 (32448)
社区积分 2 (31726)
注册日期 2004-10-20
论坛徽章:1

#5

使用道具

发表于 2007-12-18 22:29

今天也处理了一个ORA-04031的问题，后来做了一个statspack,发现是由于同事当时的错误配置，使得db_cache_size,shared_pool_size参数设置太小，导致后来的客户端不能建立与服务器的连接，修改后还没有出现问题。

严重关注此问题！

只看该作者

happig
happig

精华贴数 0
个人空间 0
技术积分 50 (32448)
社区积分 2 (31726)
注册日期 2004-10-20
论坛徽章:1

#6

使用道具

发表于 2007-12-18 22:30

ORA-04031，又见ORA-04031

只看该作者

Kamus
版主

精华贴数 52
个人空间 400
技术积分 47987 (18)
社区积分 3611 (504)
注册日期 2002-5-26
论坛徽章:49

#7

使用道具

发表于 2007-12-18 22:32

欢迎大家讨论，包括任何对于shared pool的构造，机制的疑问，以及ora-4031诊断的经验等等。

当然，讨论之前，先去eygle的blog上看看《关于shared pool的深入探讨》那一系列文章则更好。

http://www.eygle.com/archives/2004/10/shared_pool-1.html

__________________

有事情请发Gmail邮箱，站内IM可能不能及时回复。

***Chanel [K]***

从明天起, 做一个幸福的人
喂马, 劈柴, 周游世界
从明天起, 关心粮食和蔬菜
我有一所房子面朝大海, 春暖花开

只看该作者

棉花糖ONE

精华贴数 0
个人空间 48
技术积分 22061 (54)
社区积分 1623 (883)
注册日期 2007-2-21
论坛徽章:83

#8

使用道具

发表于 2007-12-18 22:32

今天在系统正常期间，检查共享池sharedpool各chunck的情况，很正常，大小size的free chunck都不少。

ps: 正常期间的结果有意义吗,ora-4031类似于突发事件吧

__________________

只看该作者

棉花糖ONE

精华贴数 0
个人空间 48
技术积分 22061 (54)
社区积分 1623 (883)
注册日期 2007-2-21
论坛徽章:83

#9

使用道具

发表于 2007-12-18 22:34

我想问下cursor_space_for_time 这参数的值是不是设置true了

__________________

只看该作者

Kamus
版主

精华贴数 52
个人空间 400
技术积分 47987 (18)
社区积分 3611 (504)
注册日期 2002-5-26
论坛徽章:49

#10

使用道具

发表于 2007-12-18 22:40

再次仔细看

在最后部分：
Permanent space allocted for Load Locks
LATCH:0  TOTAL SPACE: 25488
FREELIST CHUNK COUNT:354  OBJECT SIZE:72
Permanent space allocted for KGL pins
LATCH:0  TOTAL SPACE: 2356992
FREELIST CHUNK COUNT:223  OBJECT SIZE:128
LATCH:1  TOTAL SPACE: 2310528
FREELIST CHUNK COUNT:314  OBJECT SIZE:128

我不确认这是不是真个shared pool的dump还仅仅是报错的subpool的dump，也不确认是不是表示报错时候所有free chunk的情况。因为“Permanent space allocted for”这样的字样让我怀疑这段trace的意义。

如果是的话，那么确实所有object size都小于要求的4216，也就是说即使总共的free memory还看上去很大，但是其实已经全部都是碎片，没有一个连续的空间可以足够容纳4216的申请，因此报错。

[ 本帖最后由 Kamus 于 2007-12-18 22:46 编辑 ]

__________________

有事情请发Gmail邮箱，站内IM可能不能及时回复。

***Chanel [K]***

从明天起, 做一个幸福的人
喂马, 劈柴, 周游世界
从明天起, 关心粮食和蔬菜
我有一所房子面朝大海, 春暖花开

只看该作者