ORA-04031: unable to allocate 4096 bytes of shared memory (解决方案)
来源:互联网 发布:中小企业网络拓扑图 编辑:程序博客网 时间:2024/05/29 16:08
查阅了一些Oracle的资料,发现ORA-04031错误一般可能由于两个原因引起的:
1.内存中存在大量碎片,导致在分配内存的时候,没有连续的内存可存放,此问题一般是需要在开发的角度上入手,比如增加绑定变量,减少应解析来改善和避免;
2.内存容量不足,需要扩大内存。
1、首先看shared_pool的两个参数设置
SQL> show parameter shared_pool;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 77175193
shared_pool_size big integer 320M
共享池:320M
保留区的大小为:77175193
2、查看保留区的使用情况
SQL> select sum(free_space) from v$shared_pool_reserved;
SUM(FREE_SPACE)
---------------
77246328
大家可以看到保留区基本没有使用
那么如何提高保留区的使用效率呢,接着如下查询
3、查看保留区使用情况
SQL> SELECT FREE_SPACE, FREE_COUNT, REQUEST_FAILURES, REQUEST_MISSES, LAST_FAILURE_SIZE FROM V$SHARED_POOL_RESERVED;
FREE_SPACE FREE_COUNT REQUEST_FAILURES REQUEST_MISSES LAST_FAILURE_SIZE
---------- ---------- ---------------- -------------- -----------------
77246328 23 6 0 4192
请求失败次数为6,。最后一次请求为4192
4、查看门值
SQL> select KSPPINM,KSPPSTVL from x$ksppi, x$ksppcv where x$ksppi.indx = x$ksppcv.indx and KSPPINM = '_shared_pool_reserved_min_alloc';
KSPPINM KSPPSTVL
---------------------------------------- ----------
_shared_pool_reserved_min_alloc 4400
可以看出只有>=4400的申请才会使用保留区,为提供保留区的使用率。必须降低门值。建议降低为4100
//下次数据库启动时才能生效
SQL> alter system set "_shared_pool_reserved_min_alloc" = 4100 scope = spfile;
System altered.
由于共享池过小,导致数据库性能降低,现在把更改方法总结一下,以便以后查阅
A.sga(指定数据库启动时分配的内存大小)空间已满
1.以DBA身份进入控制台
2.在管理->例程->内存参数中,更改SGA大小
3.重启数据库后再次以DBA进入控制台,更改共享池大小
4.再次重启数据库
B.sga空间足够:直接更改共享池大小就可以了
可以用命令更改:进入sqlplus执行alter system set shared_pool_size=10M。(设置成需要的大小)
更改后要重启数据库哦!
查询数据库的SGA:select * from v$sga;
- ORA-04031: unable to allocate 4096 bytes of shared memory (解决方案)
- ORA-04031: unable to allocate 4096 bytes of shared memory
- ORA-04031:unable to allocate N bytes of shared memory
- ORA-04031: unable to allocate 2048024 bytes of shared memory
- ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","BEGIN :EXEC_STR := SYS.DBMS...","PL/SQL MPCODE","BAMIM
- ECC 6.0 SR3 安装import abap出错ORA-04031: unable to allocate 116 bytes of shared memory和DbSl Trace: ORA-1403 when accessing table SA
- OERR: ORA-4031 "unable to allocate %s bytes of shared memory ("%s","%s","%s")" (文档 ID 4031.1)
- ORA-04030: out of process memory when trying to allocate 64544 bytes (sort subheap,sort key)
- Unable to allocate 61257852 elements of size 4 bytes
- Oracle错误:ORA-27121: unable to determine size of shared memory segment
- :"ORA-27121: unable to determine size of shared memory segment" 错误
- Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 775920 bytes) 问题解决
- Allowed memory size of 33554432 bytes exhausted (tried to allocate 43148176 bytes) in php
- Allowed memory size of 33554432 bytes exhausted (tried to allocate 16 bytes)
- Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 775920 bytes)
- Fatal error: Allowed memory size of 12582912 bytes exhausted (tried to allocate 62 bytes) in
- Allowed memory size of aaaaa bytes exhausted (tried to allocate bbbbb bytes)
- 问题解决Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 38218371 bytes)
- oled+w25x16+vs1003编程经验
- Elastalert-基于Elasticsearch层面的监控告警框架
- Android okhttp3 进行socket connect&poll的底层实现跟踪
- ALSA声卡驱动中的DAPM详解之一:kcontrol
- Swift3 GCD全局队列(global)的优先级
- ORA-04031: unable to allocate 4096 bytes of shared memory (解决方案)
- 流水号变换算法(9位)
- 从原理角度解析Android (Java) http 文件上传
- chrome视频教程-02 设置
- ALSA声卡驱动中的DAPM详解之二:widget-具备路径和电源管理信息的kcontrol
- CentOS6如何开启FTP及telnet服务
- C语言中字符串赋值处理方式
- JS匿名函数
- Android图片加载神器之Fresco-加载图片基础[详细图解Fresco的使用]