ORA-04031内存不能分配

问题描述：

数据库无法连接，ORA-04031: unable to allocate 32 bytes of shared memory

问题分析：

库shared pool报ORA-04031的错误是由于SQL 类似语句导致。这个语句关联了500个表，文本有192k。在数据库中一直在进行解析，并且没有执行解析成功。从ash视图中看到有5个会话并发执行类似语句，一直在进行解析。这些会话一直在不停地分配数据库共享池。直到共享池出现碎片，导致ORA-04031错误。在测试库运行该语句时，语句一直在解析状态，对执行该语句的进程进行pstack，发现pstack的堆栈调用和生产库ORA-04031报错一致。对该语句的进程进行truss，发现该进程调用mmap函数，进行内存分配。

生产库ORA-04031错误stack信息和测试库stack信息一致：

从pstack信息中在调用函数mmap，一直在分配内存

点击(此处)折叠或打开

1. machine1 > pstack 20725
   
2. 20725:  oraclet1csms (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
   
3. ffffffff7a7dc5e4 mmap (ffffffff7fff5038, ffffffff03a00000, 7, 10000, 0, 1) + 10
   
4. 000000010834194c skgmrf_alloc (1, 0, 1, ffffffff78c00000, ffffffff78c000a8, 153) + 1cc
   
5. 0000000100965c60 ksmarfg (10d03d, 10d043, 10000, 10000, 10d043b38, 10d043b30) + e0
   
6. 0000000107e1fcdc kghgex (10d03e190, 0, 10d043af0, 10b507a68, 2000, 10d043b30) + 57c
   
7. 0000000107e23408 kghfnd (0, 0, fc78, 10d043c98, 10d043c80, 1800) + ac8
   
8. 0000000107e26724 kghalo (10d03e190, 0, fc78, fc78, 10d0428bc, 0) + d24
   
9. 0000000107e1fae4 kghgex (10d03e190, 0, 10d042870, fc60, 2000, 10d0428b0) + 384
   
10. 0000000107e23408 kghfnd (0, 0, fc50, 10d042910, 10d042910, 1800) + ac8
    
11. 0000000107e26724 kghalo (10d03e190, 0, fc50, fc50, ffffffff78c1ff94, 0) + d24
    
12. 0000000107e1fae4 kghgex (10d03e190, 0, ffffffff78c1ff48, fc38, 2000, ffffffff78c1ff88) + 384
    
13. …..

测试库的truss日志进程一直在分配内存

点击(此处)折叠或打开

1. machine1 > truss -fea -p 20725
   
2. 20725:  psargs: oraclet1csms (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
   
3. 20725:  mmap(0xFFFFFFFF03D00000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xFFFFFFFF03D00000
   
4. 20725:  mmap(0xFFFFFFFF03D10000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xFFFFFFFF03D10000
   
5. 20725:  mmap(0xFFFFFFFF03D20000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xFFFFFFFF03D20000
   
6. 20725:  mmap(0xFFFFFFFF03D30000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xFFFFFFFF03D30000
   
7. 20725:  mmap(0xFFFFFFFF03D40000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xFFFFFFFF03D40000
   
8. 20725:  mmap(0xFFFFFFFF03D50000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xFFFFFFFF03D50000
   
9. 20725:  mmap(0xFFFFFFFF03D60000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xFFFFFFFF03D60000
   
10. 20725:  mmap(0xFFFFFFFF03D70000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xFFFFFFFF03D70000
    
11. 20725:  mmap(0xFFFFFFFF03D80000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xFFFFFFFF03D80000
    
12. 20725:  mmap(0xFFFFFFFF03D90000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xFFFFFFFF03D90000
    
13. 20725:  mmap(0xFFFFFFFF03DA0000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xFFFFFFFF03DA0000
    
14. 20725:  mmap(0xFFFFFFFF03DB0000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xFFFFFFFF03DB0000
    
15. 20725:  mmap(0xFFFFFFFF03DC0000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xFFFFFFFF03DC0000
    
16. 20725:  mmap(0xFFFFFFFF03DD0000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xFFFFFFFF03DD0000
    
17. 20725:  mmap(0xFFFFFFFF03DE0000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xFFFFFFFF03DE0000
    
18. 20725:  mmap(0xFFFFFFFF03DF0000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xFFFFFFFF03DF0000
    
19. 20725:  mmap(0xFFFFFFFF03E00000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xFFFFFFFF03E00000
    
20. 20725:  mmap(0xFFFFFFFF03E10000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xFFFFFFFF03E10000
    
21. 20725:  mmap(0xFFFFFFFF03E20000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xFFFFFFFF03E20000
    
22. 20725:  mmap(0xFFFFFFFF03E30000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xFFFFFFFF03E30000
    
23. 20725:  mmap(0xFFFFFFFF03E40000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 7, 0) = 0xFFFFFFFF03E40000

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25105315/viewspace-2132229/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25105315/viewspace-2132229/