1G的share_pool_size 还报ora-04031错误

oracle-plus · 发表于 2004-12-24 14:40

先用alter system flush shared_pool吧，
然後再慢慢對現有的sql進行trace.

blue_prince10 · 发表于 2004-12-24 14:43

shared pool没有必要设置这么大吧

chenfeng · 发表于 2004-12-24 17:37

执行alter system flush shared_pool命令后现在有什么结果吗？

ghost_zy1979 · 发表于 2004-12-25 08:57

这么多老大关心,怎么就没有下文了呢! 急呀

chenfeng · 发表于 2004-12-28 15:25

Fenng有一篇关于如何解决ORA-04031 错误的文章，写的很好，希望对你有帮助！

http://www.dbanotes.net/Oracle/Ora-04031.htm

eygle · 发表于 2004-12-28 16:38

使用以下代码查询一下shared pool的使用情况:

[php]

SELECT ksmchcom, COUNT (ksmchcom) CHUNK,
      DECODE (ksmchcls, 'recr', SUM (ksmchsiz), NULL) recr,
      DECODE (ksmchcls, 'freeabl', SUM (ksmchsiz), NULL) freeabl,
      SUM (ksmchsiz) SUM
FROM x$ksmsp
GROUP BY ksmchcom, ksmchcls
/

.[/php]

增大shared_pool不是解决问题的方法。

你可以看看v$sqlarea视图
是否SQL代码存在异常，比如应该共享的没有得到充分共享?

如果这个问题是和你的03113错误同时出现的，我倾向于认为是Bug。

关于shared_pool的内容你参考一下:
http://www.eygle.com/internal/shared_pool-2.htm

foxmile · 发表于 2004-12-28 21:59

share_pool_size是影响性能的语句，它的高低只是会影响数据库的快慢。不会导致错误。

dx6340 · 发表于 2004-12-29 09:51

如果是9I 和配合多个CPU ，那么使用较大的shared_pool也是可以的。
从9I 开始，shared_pool中可以划分为subpool,每个subpool有自己的free list,latch资源.
在9I以前，所有的shared_pool只有一套资源使用，所以增加shared_pool反而会影响。
当然在没有优化SQL语句之前，无谓的增加shared_pool也是不合适的。
至于如何配置shared_pool使用多个subpool, 看下文：
-----------------------------------------
Prior to Oracle9i Database, the shared pool memory structures were protected by a solitary shared pool latch. Beginning with Oracle9i Database, up to seven child shared pool latches can be used to protect the shared pool structures. This is possible because Oracle9i Database can break the shared pool into multiple subpools if the server has at least four CPUs and the SHARED_POOL_SIZE is greater than 250MB. The number of subpools can be manually adjusted by the _KGHDSIDX_COUNT initialization parameter, which also supplies the appropriate number of child shared pool latches. If you manually increase the number of subpools, you should also increase the SHARED_POOL_SIZE because each subpool has its own structure, LRU list, and shared pool latch. Otherwise, the instance may not start due to the error ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","sga heap(5,0)","fixed allocation callback"

.