[20160203]ora-04031错误.txt

[20160203]ora-04031错误.txt

--生产系统10g的数据库，修改为手工内存管理后出现ora-04031错误，自己手工模拟看看：

1.环境:

SYS@test> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

$ cat inittest.ora
*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.background_dump_dest='/u01/app/oracle/admin/test/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/mnt/ramdisk/test/control01.ctl','/mnt/ramdisk/test/control02.ctl','/mnt/ramdisk/test/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/test/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='test'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=64424509440
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.filesystemio_options='ASYNCH'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=160432128
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=483393536
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/test/udump'

*.db_cache_size=142606336
*.shared_pool_size=335544320
*.sga_max_size=499122176
*.sga_target=499122176

--说明一下，后面的内存配置从生产系统拷贝过来：
--db_cache_size=136M,shared_pool_size=320M,sga_max_size=476M,sga_target=476M
--shared_pool_reserved_size=16M(保留池在shared_pool里面吗?)

SYS@test> startup pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/inittest.ora
ORACLE instance started.
Total System Global Area  499122176 bytes
Fixed Size                  2085032 bytes
Variable Size             343936856 bytes
Database Buffers          142606336 bytes
Redo Buffers               10493952 bytes
Database mounted.
Database opened.

2.加入测试脚本：
create table t as select rownum id ,rpad(rownum,100,'test') pad,'Y' flag from dual connect by level<=20000;
create unique index pk_t on t(id);
alter table t add constraint pk_t  primary key (id);
exec dbms_stats.gather_table_stats(user, 't', method_opt=>'for all columns size 1');
--分析表T，没有建立直方图。

select * from dba_tab_cols where table_name='T' and owner=user;

2.测试使用bind的情况：
select /*+ full(t) */ count(*) from t;

set timing on
--alter system flush shared_pool;
declare
v_pad varchar2(200);
begin
for i in 1 .. 20000 loop
execute immediate 'select pad from t where id = ' || i into v_pad;
end loop;
end;
/

3.测试前看看共享池情况：
SYS@test> @ &r/tpt/sgastatx.sql "free memory"

-- All allocations:
SUBPOOL                             BYTES         MB
------------------------------ ---------- ----------
shared pool (0 - Unused):       218103808        208
shared pool (1):                117445304        112
shared pool (Total):            335549112        320

-- Allocations matching "free memory":
old  15:     AND LOWER(ksmssnam) LIKE LOWER('%&1%')
new  15:     AND LOWER(ksmssnam) LIKE LOWER('%free memory%')
SUBPOOL                        NAME                           SUM(BYTES)         MB
------------------------------ ------------------------------ ---------- ----------
shared pool (0 - Unused):      free memory                     218103808        208
shared pool (1):               free memory                      27747432      26.46

--奇怪以前没有注意子池shared pool (0 - Unused)全部是free memory。
-- 运行后测试：

SYS@test> @ &r/tpt/sgastatx.sql "free memory"

-- All allocations:
SUBPOOL                             BYTES         MB
------------------------------ ---------- ----------
shared pool (1):                335555608     320.01
shared pool (Total):            335555608     320.01

-- Allocations matching "free memory":
old  15:     AND LOWER(ksmssnam) LIKE LOWER('%&1%')
new  15:     AND LOWER(ksmssnam) LIKE LOWER('%free memory%')
SUBPOOL                        NAME                           SUM(BYTES)         MB
------------------------------ ------------------------------ ---------- ----------
shared pool (1):               free memory                     109438480     104.37

--在修改上面的测试脚本比如select换成Select，继续运行。
--运行多次后我仅仅运行上面的脚本3次就出现

SELECT
*
ERROR at line 1:
ORA-04031: unable to allocate 4064 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","work area tab")

SYS@test> @ &r/tpt/sgastatx.sql "free memory"

-- All allocations:
SUBPOOL                             BYTES         MB
------------------------------ ---------- ----------
shared pool (1):                335551912     320.01
shared pool (Total):            335551912     320.01

-- Allocations matching "free memory":
old  15:     AND LOWER(ksmssnam) LIKE LOWER('%&1%')
new  15:     AND LOWER(ksmssnam) LIKE LOWER('%free memory%')
SUBPOOL                        NAME                           SUM(BYTES)         MB
------------------------------ ------------------------------ ---------- ----------
shared pool (1):               free memory                     111519328     106.35

--实际上这个时候还有106.35MB free memory。

SYS@test> alter system flush shared_pool;
System altered.

SYS@test> @ &r/tpt/sgastatx.sql "free memory"

-- All allocations:
SUBPOOL                             BYTES         MB
------------------------------ ---------- ----------
shared pool (1):                335551912     320.01
shared pool (Total):            335551912     320.01

-- Allocations matching "free memory":
old  15:     AND LOWER(ksmssnam) LIKE LOWER('%&1%')
new  15:     AND LOWER(ksmssnam) LIKE LOWER('%free memory%')
SUBPOOL                        NAME                           SUM(BYTES)         MB
------------------------------ ------------------------------ ---------- ----------
shared pool (1):               free memory                     233532840     222.71

SCOTT@test> /
declare
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 4064 bytes of shared memory ("shared pool","PROCEDURE$SYS","sga heap(1,0)","work area tab")
Elapsed: 00:00:00.01

--甚至无法关机。
SYS@test> shutdown immediate ;
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 4080 bytes of shared memory ("shared pool","TRIGGER$SYS","sga heap(1,0)","library cache")

4.重启后分析:
SYS@test> @ &r/hide large_pool_size
NAME               DESCRIPTION                         DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
------------------ ----------------------------------- ------------- ------------- -------------
__large_pool_size  Actual size in bytes of large pool  TRUE          4194304       4194304
large_pool_size    size in bytes of large pool         TRUE          0             0

--会不会large_pool_size设置太小。 修改如下两行。
*.sga_max_size=488M
*.sga_target=488M

SYS@test> show parameter shared_pool
NAME                       TYPE         VALUE
-------------------------- ------------ --------
shared_pool_reserved_size  big integer  16M
shared_pool_size           big integer  320M

SYS@test> @ &r/hide large_pool_size
NAME               DESCRIPTION                         DEFAULT_VALUE          SESSION_VALUE          SYSTEM_VALUE
------------------ ----------------------------------- ---------------------- ---------------------- ----------------------
__large_pool_size  Actual size in bytes of large pool  TRUE                   4194304                4194304
large_pool_size    size in bytes of large pool         TRUE                   0                      0

--依旧是4M。
-- 320+136+4+16=476

SYS@test> @ &r/tpt/sgastatx.sql "free memory"
-- All allocations:
SUBPOOL                             BYTES         MB
------------------------------ ---------- ----------
shared pool (0 - Unused):       218103808        208
shared pool (1):                117445304        112
shared pool (Total):            335549112        320

-- Allocations matching "free memory":
old  15:     AND LOWER(ksmssnam) LIKE LOWER('%&1%')
new  15:     AND LOWER(ksmssnam) LIKE LOWER('%free memory%')
SUBPOOL                        NAME                                     SUM(BYTES)         MB
------------------------------ ---------------------------------------- ---------- ----------
shared pool (0 - Unused):      free memory                               218103808        208
shared pool (1):               free memory                                28559200      27.24

-- 多次运行脚本。

SYS@test> @ &r/tpt/sgastatx.sql "free memory"

-- All allocations:
SUBPOOL                             BYTES         MB
------------------------------ ---------- ----------
shared pool (1):                343943320     328.01
shared pool (Total):            343943320     328.01

-- Allocations matching "free memory":
old  15:     AND LOWER(ksmssnam) LIKE LOWER('%&1%')
new  15:     AND LOWER(ksmssnam) LIKE LOWER('%free memory%')
SUBPOOL                        NAME                                     SUM(BYTES)         MB
------------------------------ ---------------------------------------- ---------- ----------
shared pool (1):               free memory                               151774448     144.74

--可以发现shared pool会达到328M。
--看来以后改成手工不能设定太死，保留一定的余量。

4.修改*.large_pool_size=16M，继续测试：
SCOTT@test> /
declare
*
ERROR at line 1:
ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","SElect pad from t where id =...","sga heap(1,0)","kglsim object batch")
ORA-06512: at line 5

总结：
--看来以后改成手工不能设定太死，保留一定的余量。

 

--后记：
--下午想想不对，还有许多free memory内存呢？

SYS@test> select ksmchsiz ,count(*) from x$ksmsp where ksmchcls='free'  and ksmchsiz>=4096 group by ksmchsiz order by ksmchsiz;
  KSMCHSIZ   COUNT(*)
---------- ----------
      4448        524
      4800          1
      5552          1
      5560        422
      5912          1
      6664          2
      6672        433
      6768          1
      6776          1
      7024          1
      7360          1
      7784        400
      8896        373
      8992          1
      9248          1
      9936          1
     10008        280
     10360          1
     11120        195
     12232        131
     13344        125
     14456        108

--不应该这个时候出现ora-04031错误。
--google 发现如下链接：https://dfitzjarrell.wordpress.com/2016/01/26/heap-of-trouble/
--非常巧合他也使用Tanel Poder的sgastatx看共享池使用情况，好像跟他的情况很相似。

When subpool 0 is exhausted it will be eliminated from the displayed output, which will indicate that Oracle can make no
more adjustments to the existing subpools and either a dynamic shared pool increase needs to be executed or, failing
that, the spfile needs to be changed, the database stopped and then restarted. [If an spfile is not in use then the
pfile needs to be modified to reflect the memory increase, followed by a shutdown and startup of the database. For ease
of administration it is recommended that an spfile, rather than a pfile, be used.]

--他提到subpool 0消耗殆尽后无法在分配内存，出现错误，我的测试即使shared pool (0) 也可以继续很长一段事件。
--继续测试：
SCOTT@test> /
declare
*
ERROR at line 1:
ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","SELECT PAD FROm t where id = 16","sga heap(1,0)","kglsim object batch")
ORA-06512: at line 5
Elapsed: 00:00:00.54

--可以发现我一致修改SQL语句大写到SELECT PAD FRO才再次出现ora-04031错误。按照这个提示如果绑定做的好应该可以延缓这个问题的出现，
--如果应用绑定做的不好可能很快就出现ora-04031错误。

时间： 2024-08-19 01:45:12

[20160203]ora-04031错误.txt的相关文章

[20160910]低级错误.txt

[20160910]低级错误.txt --记录一个低级错误,要在表ms_yyhy中增加一个字段fyxh,并且要与同步ms_ysks.fyxh相关记录保持一直. --我同事在测试环境下,执行如下: UPDATE ms_yyhy    SET fyxh =           (SELECT fyxh              FROM ms_ysks             WHERE     ms_yyhy.ysdm = ms_ysks.ysdm                   AND ms

[20170315]ORA-19656错误.txt

[20170315]ORA-19656错误.txt --//上午删除测试数据库的归档日志,遇到ORA-19656,做一个记录. 1.环境: SYS@book> @ &r/ver BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Produ

[20140429]ORA-01665 错误.txt

[20140429]ORA-01665 错误.txt $ oerr ora 1665 01665, 00000, "control file is not a standby control file" // *Cause:  Attempting to mount, recover or activate a standby database //          without a standby control file. // *Action: Create a standb

[20120929]ora-55605错误.txt

[20120929]ora-55605错误.txt 昨天在测试机器查看进程,发现一个进程. $ ps -ef | grep fbda 503      13583     1  0 09:27 ?        00:00:00 ora_fbda_test 503      14502 14465  0 09:43 pts/2    00:00:00 grep fbda 查看alert.log也能发现这个进程的启动,我查看以前的日志,发现并没有启动. 我记得当时测试时使用的表空间我前几天已经删除

Oracle数据库ORA 54013错误的解决办法_oracle

ORA-54013: 不允许对虚拟列执行 INSERT 操作 这是Oracle 11 的新特性 -- 虚拟列. 在以前的Oracle 版本,当我们需要使用表达式或者一些计算公式时,我们会创建数据库视图,如果我们需要在这个视图上使用索引,我们会创建基于函数的索引.现在Oracle 11允许我们直接在表上使用虚拟列来存储表达式.虚拟列的值是不存储在磁盘的,它们是在查询时根据定义的表达式临时计算的.我们不能往虚拟列中插入数据,我们也不能隐式的添加数据到虚拟列,我们只能使用物理列来插入数据.然后可以查询

plsql连接oracle数据库报ora 12154错误解决方法_oracle

plsql连接oracle数据库报ora 12154错误 今天遇到一个问题,使用sqlplus能够连接到远程的数据库,但是使用plsql却连接不上,报错"ORA-12154: TNS: 无法解析指定的连接标识符" 解决方法如下: 1.先检查服务器端的监听服务是否打开,如果没有打开请启动其监听 客户端:tnsping <tns_name> 服务器Linux下: #>lsnrctl status 查看监听状态 #>lsnrctl start 启动监听 2.通过Sql

[20170914]tnsnames.ora的管理.txt

[20170914]tnsnames.ora的管理.txt --//昨天朋友讲tnsnams.ora的内容太长了,而且许多不需要的.管理不方便.我记得以前写[20150409]tnsnames.ora与IFILE.txt.链接 --//http://blog.itpub.net/267265/viewspace-1561107/ --//这样你可以按照某种分类管理.实际上这个我也是以前看别人的机器学来的,很简单就是建立多个tnsnames配置文件. --//使用参数IFILE=/path/xxx

[20171205]bash for例子错误.txt

[20171205]bash for例子错误.txt --//今天写bash for循环,遇到问题.通过例子说明: $ cat tt1.sh #! /bin/bash for i in { 1 .. 5 } do   echo $i done --//本想输出1,2,3,4,5的.而实际上执行输出是: $ . tt1.sh { 1 .. 5 } --//使用这样的方式{ 1 .. 5 }之间不能有任何空格.正确的写法如下: $ cat tt1.sh #! /bin/bash for i in {

[20150601]模拟ora-00600[2608]错误.txt

[20150601]模拟ora-00600[2608]错误.txt --前几天在测试使用bbed解决丢失的归档,链接: http://blog.itpub.net/267265/viewspace-1676438/ [20150529]使用bbed解决丢失的归档.txt --出现如下错误: ORA-00600: internal error code, arguments: [2608], [1], [2], [4101877000], [2], [4101877009], [], [] --不