[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错误。
