ITPub博客

首页 > 数据库 > Oracle > ORA-04031内存不能分配

ORA-04031内存不能分配

原创 Oracle 作者:dbs101 时间:2017-01-11 10:57:36 0 删除 编辑

问题描述:

数据库无法连接,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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-12-18

  • 博文量
    92
  • 访问量
    439157