结论
1,可以通过x$ksmsp分析共享池chunk的分配回收情况，深入分析ora-4031错误的原因2,x$ksmsp的列ksmchcls为chunk的类型，共计6个值，具体如下：
KSMCHCLS
--------
R-free
R-freea
free
freeabl
perm
recr
6 rows selected.
3，关于上述ksmchcls列的不同含义，见下：
free：这种类型的chunk不包含有效的对象，可以不受限制的被分配。
recr：意味着recreatable，这种类型的chunks里包含的对象可以在需要的时候被临时移走，并且在需要的时候重新创建。比如对于很多有关共享SQL语句的chunks就是recreatable的。
freeabl：这种类型的chunks包含的对象都是曾经被session使用过的，并且随后会被完全或部分释放的。这种类型的chunks不能临时从内存移走，因为它们是在处理过程中间产生的，如果移走的话就无法被重建。
perm：意味着permanent，这种类型的chunks包含永久的对象，大型的permanent类型的chunks也可能含有可用空间，这部分可用空间可以在需要的时候释放回shared pool里。
当chunk属于free类型的时候，它既不属于library cache，也不属于dictionary cache.如果该chunk被用于存放SQL游标时，则该chunk进入library cache；同样，如果该chunk被用于存放数据字典的信息时，则该chunk进入dictionary cache.
仍有部分值没有解释的含义，还需要再查查资料
4，通过如下方法，模拟ora-4031错误
alter system set open_cursors=65535 scope=spfile;
create or replace procedure proc_4031(v_in int)
as
n number;
v_cycle number;
v_number number;
begin
v_cycle:=v_in;
dbms_random.seed(123456);
for i in 1..v_cycle loop
n:=dbms_random.value;
select n into v_number from dual;
end loop;
end;
/
5,ora-4031错误信息的含义：
ERROR at line 1:
ORA-04031: unable to allocate 4080 bytes of shared memory ("shared
pool","unknown object","sga heap(4,0)","library cache")
每一个参数为共享池
第二个参数要在共享池中分配的对象
第三个参数即要在共享池哪个heap中分配内存空间
第四个参数，即要在共享池哪个组件中分配内存空间，到底是library cache,还是row cache或是其它的组件
知道这个含义，在分析ORA-4031的TRC文件，就查看对应的heap即可进行针对性分析
6，经过本文测试，一般情况从共享池分配内存只会从FREE LIST中的BUCKET中的CHUNK进行分配内存,而不会从reserved free list或者unpinned recreatable chunks以及permanent chunks分配内存
7, 既然上述ora-4031是基于library cache,学习了dump library cache的方法
即oradebug setmypid
oradebug dump library_cache 3;--3是概要DUMP,而6则把每个BUCKET中具体内容也DUMP出来
oradebug tracefile_name
也就是说6级产生的信息比3级要全面丰富得多
8，bucket里面包括很多library object handle,我估计如果一个bucket里面的library object handle多了，这些很多个连接到同一个bucket的library object handle就是chain构，好像我查了下资料，仅在buffer cache
有chain的概念结构
9, x$kglob可以理解对应每个library object handle, 可以把library object handle理解不同一的内存对象，可能是表，也可能是依整表的SQL或存储过程等等
10,library cache dump产生的TRC文件结构为：
10.1,LIBRARY CACHE STATISTICS:
namespace gets hit ratio pins hit ratio reloads invalids
-------------- --------- --------- --------- --------- ---------- ----------
CRSR 119123 0.411 535527 0.801 1117 568
TABL 10367 0.711 262568 0.976 665 0
BODY 309 0.838 16219 0.996 7 0
TRGR 97 0.907 101 0.911 0 0
INDX 352 0.267 1783 0.781 0 0
CLST 259 0.961 666 0.982 2 0
KGLT 0 0.000 0 0.000 0 0
PIPE 0 0.000 0 0.000 0 0
中间略
NSCPD 0 0.000 0 0.000 0 0
JSLV 0 0.000 0 0.000 0 0
MODL 0 0.000 0 0.000 0 0
CUMULATIVE 164614 0.354 872633 0.833 1801 568
10.2,第二部分，好像存储各种锁，PIN,以及KGL相关的HANDLE之类的对象占用的PERMANENT空间相关信息，包括对应的LATCH个数，消耗内存空间大小，管理LIBRARY CACHE内存分配的FREE LIST中CHUNK的个数以及对象个数（这个对象不知为何义）
再罗列下：存储在LIBRARY CACHE中的包括：加载lock,kgl pin,kgl lock,kgl lock,kgl s handle,kgl m handle,kgl l handle,kgl handle,kgl object,kgl handle dependents
Permanent space allocated for Load Locks
LATCH:0 TOTAL SPACE: 4104
FREELIST CHUNK COUNT:57 OBJECT SIZE:72
Permanent space allocated for KGL pins
LATCH:0 TOTAL SPACE: 28560
FREELIST CHUNK COUNT:66 OBJECT SIZE:136
Permanent space allocated for KGL locks
LATCH:0 TOTAL SPACE: 23676752
FREELIST CHUNK COUNT:64064 OBJECT SIZE:184
10.3，library cache hash table的大小，以及个数，个数我估计是bucket个数，且列出包括20个chains以上的bucket个数，基于分组列出
LIBRARY CACHE HASH TABLE: size=131072 count=64903
Buckets with more than 20 objects:
NONE
Hash Chain Size Number of Buckets
--------------- -----------------
0 79861 --如下可见大多bucket没有对应的chain
1 39579
2 9799 --有2个chain的bucket共计9799个
3 1635
4 173
5 21
6 4
7 0
8 0
9 0
10 0
11 0
12 0
13 0
14 0
15 0
16 0
17 0
18 0
19 0
20 0
>20 0
11，library cache buckets的个数是固定的值
131072
12，x$kglob的重列的相关含义：
好，再列举下x$kglob的重要列的含义
X$kglob 表说明如下：
Kglhdadr (children address) LIBRARY OBJECT HANDLE: handle
Kglhdpar (parent address) LIBRARY OBJECT HANDLE:handle
Kglnaown 依赖object的owner name
Kglnahsh sql的hash value （v$sql 中的hash_value）
Kglnahsv sql 的hash value （长串格式：9a5fb5d584eb42ce5f55b9c92930cf55）
Kglhdobj LIBRARY OBJECT: object
Kglobhd0 data#=0 的 heap 地址
Kglobhd1
Kglobhd2
Kglobhd3..7
引发的问题：
1，为何在共享池中分配内存只会特定的从heap 进行分配chunk呢，这是什么算法或机制呢？
2，何时会从reserved free list及unpinned recreatable chunks以及permanent chunks分配内存呢？
3, 同一个bucket里面可能会包括2个以上的library object handle吗？
测试
---oracle version
SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
---ora-4031错误
SQL> host oerr ora 4031
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause: More shared memory is needed than was allocated in the shared
// pool.
// *Action: If the shared pool is out of memory, either use the
// dbms_shared_pool package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// INIT.ORA parameters "shared_pool_reserved_size" and
// "shared_pool_size".
// If the large pool is out of memory, increase the INIT.ORA
// parameter "large_pool_size".
解决方法：
1，使用dbms_shared_pool.keep过程pin掉大对象在共享池，防止老化
2,增加共享池大小，即调整参数shared_pool_reserved_size及shared_pool_size
3,如果提示大池不够用，增大large_pool_size大小
---查看共享池中不同类型的chunk,可知共计6种类型
SQL> select distinct ksmchcls from x$ksmsp order by 1;
KSMCHCLS
--------
R-free
R-freea
free
freeabl
perm
recr
6 rows selected.
上述不同类型chunk的分组大小
SQL> select ksmchcls,sum(ksmchsiz) from x$ksmsp group by ksmchcls order by 2;
KSMCHCLS SUM(KSMCHSIZ)
-------- -------------
R-freea 1536
R-free 13431936
recr 14197824
freeabl 18410408
perm 79687680
free 142704664
6 rows selected.
可见可用共享池空间为136M左右
SQL> select 142704664/1024/1024 from dual;
142704664/1024/1024
-------------------
136.093773
生成一个新SQL
SQL> show user
USER is "SCOTT"
SQL> create table t_pool(a int,b int);
Table created.
SQL> insert into t_pool values(1,1);
1 row created.
SQL> commit;
Commit complete.
SQL> select ksmchcls,sum(ksmchsiz) from x$ksmsp group by ksmchcls order by 2;
KSMCHCLS SUM(KSMCHSIZ)
-------- -------------
R-freea 1536 --未变化
R-free 13431936 --未变化
recr 14295608 --变化，变大
freeabl 18593744 --变化，变大
perm 79687680 --未变化
free 142423544 --变化，变小
6 rows selected.
SQL> select * from t_pool;
A B
---------- ----------
1 1
同上理，共享池CHUNK继续变化，综上可见仅recr,freeabl,free类型的CHUNK会发生变化，而R-FREEA，R-FREE,PERM不会发生变化
SQL> select ksmchcls,sum(ksmchsiz) from x$ksmsp group by ksmchcls order by 2;
KSMCHCLS SUM(KSMCHSIZ)
-------- -------------
R-freea 1536
R-free 13431936
recr 14317360
freeabl 18649352
perm 79687680
free 142346184
6 rows selected.
上述不同类型CHUNK的含义：
free：这种类型的chunk不包含有效的对象，可以不受限制的被分配。
recr：意味着recreatable，这种类型的chunks里包含的对象可以在需要的时候被临时移走，并且在需要的时候重新创建。比如对于很多有关共享SQL语句的chunks就是recreatable的。
freeabl：这种类型的chunks包含的对象都是曾经被session使用过的，并且随后会被完全或部分释放的。这种类型的chunks不能临时从内存移走，因为它们是在处理过程中间产生的，如果移走的话就无法被重建。
perm：意味着permanent，这种类型的chunks包含永久的对象，大型的permanent类型的chunks也可能含有可用空间，这部分可用空间可以在需要的时候释放回shared pool里。
当chunk属于free类型的时候，它既不属于library cache，也不属于dictionary cache.如果该chunk被用于存放SQL游标时，则该chunk进入library cache；同样，如果该chunk被用于存放数据字典的信息时，则该chunk进入dictionary cache.
在shared pool里，可用的chunk（free类型）会被串起来成为可用链表（free lists）或者也可以叫做buckets（一个可用链表也就是一个bucket）。我们可以使用下面的命令将shared pool的内容转储出来看看这些bucket.
SQL> select 'abcccccccccccccccccccccccccc' v_str from dual;
V_STR
----------------------------
abcccccccccccccccccccccccccc
SQL> select 'abcccccccccccccccccccccccccc'||'abc' v_str from dual;
V_STR
-------------------------------
abccccccccccccccccccccccccccabc
SQL> select ksmchcls,sum(ksmchsiz) from x$ksmsp group by ksmchcls order by 2;
KSMCHCLS SUM(KSMCHSIZ)
-------- -------------
R-freea 1536
R-free 13431936
recr 14332392
freeabl 18653768
perm 79687680
free 142326736
6 rows selected.
创建一个存储过程
create or replace procedure proc_4031(v_in int)
as
n number;
v_cycle number;
v_number number;
begin
v_cycle:=v_in;
dbms_random.seed(123456);
for i in 1..v_cycle loop
n:=dbms_random.value;
select n into v_number from dual;
end loop;
end;
/
通过这种方式，很难模拟出ORA-4031错误
SQL> exec proc_4031(10000000);
SQL> select count(*) from x$ksmsp;
COUNT(*)
----------
38633
SQL>
SQL>
SQL>
SQL> select count(*) from x$ksmsp;
COUNT(*)
----------
38634
SQL> select sid from v$mystat where rownum=1;
SID
----------
122
SQL> select 'abc' from dual;
'AB
---
abc
SQL> select sid,serial#,prev_sql_id,sql_id from v$session where sid=122;
SID SERIAL# PREV_SQL_ID SQL_ID
---------- ---------- ------------- -------------
122 90 3y2rwdgbrygbz chsyqm9x25nwa
SQL> col sql_text for a100
SQL> select sql_id,sql_text from v$sql where sql_id='3y2rwdgbrygbz';
SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
3y2rwdgbrygbz select 'abc' from dual
用网上如下脚本未模拟出ORA-4031错误
declare
msql varchar2(100);
mcur number;
mstat number;
jg varchar2(2000);
cg number;
begin for i in 1..1000000000 loop
mcur:=dbms_sql.open_cursor;
msql:='select deptno from dept where deptno='||i;
dbms_sql.parse(mcur,msql,dbms_sql.native);
mstat:=dbms_sql.execute(mcur);
end loop;
end;
/
加大open_cursors=65535后，模拟出ora-4031错误,open_cursos最大值为65535
SQL> show parameter cursor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 50000
session_cached_cursors integer 20
SQL> alter system set open_cursors=65536;
alter system set open_cursors=65536
*
ERROR at line 1:
ORA-00068: invalid value 65536 for parameter open_cursors, must be between 0
and 65535
SQL> alter system set open_cursors=65535;
System altered.
SQL> show parameter open_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 65535
SQL> declare
msql varchar2(100);
mcur number;
mstat number;
2 3 4 5 jg varchar2(2000);
6 cg number;
7 begin for i in 1..1000000000 loop
8 mcur:=dbms_sql.open_cursor;
9 msql:='select deptno from dept where deptno='||i;
10 dbms_sql.parse(mcur,msql,dbms_sql.native);
11 mstat:=dbms_sql.execute(mcur);
12 end loop;
13 end;
14 /
declare
*
ERROR at line 1:
ORA-04031: unable to allocate 4080 bytes of shared memory ("shared
pool","unknown object","sga heap(4,0)","library cache")
ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at line 10
SQL> select count(*) from dba_objects;
select count(*) from dba_objects
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 4080 bytes of shared memory ("shared
pool","VIEW$","sga heap(4,0)","library cache")
同时在alert也报一系列的ORA-4031错误
Fri Nov 20 05:56:05 EST 2015
Errors in file /home/ora10g/admin/ora10g/bdump/ora10g_smon_3217.trc:
ORA-04031: unable to allocate 4080 bytes of shared memory ("shared pool","COL_USAGE$","sga heap(4,0)","library cache")
Fri Nov 20 05:56:06 EST 2015
Errors in file /home/ora10g/admin/ora10g/bdump/ora10g_smon_3217.trc:
ORA-04031: unable to allocate 4080 bytes of shared memory ("shared pool","COL_USAGE$","sga heap(4,0)","library cache")
Fri Nov 20 05:56:07 EST 2015
Errors in file /home/ora10g/admin/ora10g/bdump/ora10g_smon_3217.trc:
ORA-04031: unable to allocate 4080 bytes of shared memory ("shared pool","COL_USAGE$","sga heap(4,0)","library cache")
Fri Nov 20 05:56:08 EST 2015
Errors in file /home/ora10g/admin/ora10g/bdump/ora10g_smon_3217.trc:
ORA-04031: unable to allocate 4080 bytes of shared memory ("shared pool","COL_USAGE$","sga heap(4,0)","library cache")
Fri Nov 20 05:56:09 EST 2015
Errors in file /home/ora10g/admin/ora10g/bdump/ora10g_smon_3217.trc:
ORA-04031: unable to allocate 4080 bytes of shared memory ("shared pool","COL_USAGE$","sga heap(4,0)","library cache")
Fri Nov 20 05:56:19 EST 2015
Errors in file /home/ora10g/admin/ora10g/bdump/ora10g_smon_3217.trc:
ORA-04031: unable to allocate 4080 bytes of shared memory ("shared pool","COL_USAGE$","sga heap(4,0)","library cache
好像有空闲的共享池空间，为何报ORA-4031错误
SQL> select ksmchcls,count(*) from x$ksmsp where ksmchsiz>4080 group by ksmchcls;
KSMCHCLS COUNT(*)
-------- ----------
freeabl 37714
recr 32116
perm 6
R-free 36
free 737
分析下共享池的DUMP,从上述ORA-4031报错是在ORA-04031: unable to allocate 4080 bytes of shared memory ("shared
pool","VIEW$","sga heap(4,0)","library cache") ，可知是在heap 4不能分配到空间，下面分析这个HEAP即可
我们就来分析如下的ORA-4031错误
ERROR at line 1:
ORA-04031: unable to allocate 4080 bytes of shared memory ("shared
pool","unknown object","sga heap(4,0)","library cache")
ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at line 10
HEAP DUMP heap name="sga heap(4,0)" desc=0x60051858
extent sz=0xfe0 alt=216 het=32767 rec=9 flg=-126 opc=0
parent=(nil) owner=(nil) nex=(nil) xsz=0x1000000
latch set 4 of 5
durations enabled for this heap
reserved granules for root 0 (granule size 16777216)
EXTENT 0 addr=0x7a000000
Chunk 07a000058 sz= 48 R-freeable "reserved stoppe"
Chunk 07a000088 sz= 839496 R-free " "
Chunk 07a0ccfd0 sz= 48 R-freeable "reserved stoppe"
Chunk 07a0cd000 sz= 15934392 perm "perm " alo=15934392
Chunk 07afff3b8 sz= 3144 free " "
EXTENT 1 addr=0x9c000000
Chunk 09c000058 sz= 48 R-freeable "reserved stoppe"
Chunk 09c000088 sz= 839496 R-free " "
Chunk 09c0ccfd0 sz= 48 R-freeable "reserved stoppe"
Chunk 09c0cd000 sz= 15935336 perm "perm " alo=15935336
Chunk 09cfff768 sz= 2200 free " "
Total heap size = 33554256
共享池分配会优先从free list分配chunk,但里面的2个chunk皆不到4080byte
FREE LISTS:
Bucket 0 size=32
Bucket 1 size=40
Bucket 2 size=48
Bucket 3 size=56
Bucket 4 size=64
中间略
Bucket 197 size=2008
Bucket 198 size=2056
Bucket 199 size=2104
Bucket 200 size=2152
Bucket 201 size=2200
Chunk 09cfff768 sz= 2200 free " "
Bucket 202 size=2248
Bucket 203 size=2296
中间略
Bucket 220 size=3112
Chunk 07afff3b8 sz= 3144 free " "
Bucket 221 size=3160
中间略
Bucket 254 size=65560
Total free space = 5344
可见不会从预备的保留自由列表分配chunk
RESERVED FREE LISTS:
Reserved bucket 0 size=32
Reserved bucket 1 size=4400
Reserved bucket 2 size=8216
Reserved bucket 3 size=8696
Reserved bucket 4 size=8704
Reserved bucket 5 size=8712
Reserved bucket 6 size=8720
Reserved bucket 7 size=9368
Reserved bucket 8 size=9376
Reserved bucket 9 size=12352
Reserved bucket 10 size=12360
Reserved bucket 11 size=16408
Reserved bucket 12 size=32792
Reserved bucket 13 size=65560
Chunk 07a000088 sz= 839496 R-free " " --839496bytes大小的chunk
Chunk 09c000088 sz= 839496 R-free " "
Total reserved free space = 1678992
也没有从未PIN住的可重建的CHUNK(LRU优先)要分配CHUNK
UNPINNED RECREATABLE CHUNKS (lru first):
Chunk 0a1fd04b8 sz= 56 recreate "fixed allocatio" latch=0x60013968
Chunk 0a1fc1010 sz= 560 recreate "KQR PO " latch=0x9b56d7f8
略
Chunk 0a1fb4470 sz= 560 recreate "KQR PO " latch=0x9b56d7f8
Chunk 0a1fb4240 sz= 560 recreate "KQR PO " latch=0x9b56d7f8
Chunk 0a1faed30 sz= 560 recreate "KQR PO " latch=0x9b56d7f8
也没有从永久性的CHUNKS列表中分配CHUNK
PERMANENT CHUNKS:
Chunk 07a0cd000 sz= 15934392 perm "perm " alo=15934392
Chunk 09c0cd000 sz= 15935336 perm "perm " alo=15935336
Permanent space = 31869728
引申一下，可见一般情况从共享池分配内存只会从FREE LIST中的BUCKET中的CHUNK进行分配内存,而不会从reserved free list或者unpinned recreatable chunks以及permanent chunks分配内存
这里就出现几个个问题，
1，为何只会从heap 4进行分配chunk呢，这是什么算法或机制呢？
2，何时会从reserved free list及unpinned recreatable chunks以及permanent chunks分配内存呢？
既然上述ORA-4031报错全与LIBRARY CACHE有关，我们DUMP下它的内容分析下，看可否的到相关的线索
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump library_cache 3
Statement processed.
SQL> oradebug tracefile_name
/home/ora10g/admin/ora10g/udump/ora10g_ora_23356.trc
第一部分，不过好像内容要比v$librarycache记录要多，不知为何
SQL> select count(*) from v$librarycache;
COUNT(*)
----------
11
--namespace要远远大于11个哟，暂且搁置此问题
LIBRARY CACHE STATISTICS:
namespace gets hit ratio pins hit ratio reloads invalids
-------------- --------- --------- --------- --------- ---------- ----------
CRSR 119123 0.411 535527 0.801 1117 568
TABL 10367 0.711 262568 0.976 665 0
BODY 309 0.838 16219 0.996 7 0
TRGR 97 0.907 101 0.911 0 0
INDX 352 0.267 1783 0.781 0 0
CLST 259 0.961 666 0.982 2 0
KGLT 0 0.000 0 0.000 0 0
PIPE 0 0.000 0 0.000 0 0
中间略
NSCPD 0 0.000 0 0.000 0 0
JSLV 0 0.000 0 0.000 0 0
MODL 0 0.000 0 0.000 0 0
CUMULATIVE 164614 0.354 872633 0.833 1801 568
第二部分，好像存储各种锁，PIN,以及KGL相关的HANDLE之类的对象占用的PERMANENT空间相关信息，包括对应的LATCH个数，消耗内存空间大小，管理LIBRARY CACHE内存分配的FREE LIST中CHUNK的个数以及对象个数（这个对象不知为何义）
再罗列下：存储在LIBRARY CACHE中的包括：加载lock,kgl pin,kgl lock,kgl lock,kgl s handle,kgl m handle,kgl l handle,kgl handle,kgl object,kgl handle dependents
Permanent space allocated for Load Locks
LATCH:0 TOTAL SPACE: 4104
FREELIST CHUNK COUNT:57 OBJECT SIZE:72
Permanent space allocated for KGL pins
LATCH:0 TOTAL SPACE: 28560
FREELIST CHUNK COUNT:66 OBJECT SIZE:136
Permanent space allocated for KGL locks
LATCH:0 TOTAL SPACE: 23676752
FREELIST CHUNK COUNT:64064 OBJECT SIZE:184
Permanent space allocated for KGL S handles
LATCH:0 TOTAL SPACE: 31474536
FREELIST CHUNK COUNT:0 OBJECT SIZE:488
Permanent space allocated for KGL M handles
LATCH:0 TOTAL SPACE: 233920
FREELIST CHUNK COUNT:5 OBJECT SIZE:680
Permanent space allocated for KGL L handles
LATCH:0 TOTAL SPACE: 87048
FREELIST CHUNK COUNT:46 OBJECT SIZE:936
Permanent space allocated for KGL A handles
LATCH:0 TOTAL SPACE: 11121176
FREELIST CHUNK COUNT:53 OBJECT SIZE:344
Permanent space allocated for KGL objects
LATCH:0 TOTAL SPACE: 9297216
FREELIST CHUNK COUNT:17 OBJECT SIZE:144
Permanent space allocated for KGL handle dependents
LATCH:0 TOTAL SPACE: 784728
FREELIST CHUNK COUNT:49 OBJECT SIZE:24
第三部分，library cache hash table的大小，以及个数，个数我估计是bucket个数，且列出包括20个chains以上的bucket个数，基于分组列出
LIBRARY CACHE HASH TABLE: size=131072 count=64903
Buckets with more than 20 objects:
NONE
Hash Chain Size Number of Buckets
--------------- -----------------
0 79861 --如下可见大多bucket没有对应的chain
1 39579
2 9799 --有2个chain的bucket共计9799个
3 1635
4 173
5 21
6 4
7 0
8 0
9 0
10 0
11 0
12 0
13 0
14 0
15 0
16 0
17 0
18 0
19 0
20 0
>20 0
SQL> create table t_libcache(a int);
Table created.
SQL> insert into t_libcache values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t_libcache;
A
----------
1
再次dump library cache,与上述进行对比
可见
Buckets with more than 20 objects:
NONE
Hash Chain Size Number of Buckets
--------------- -----------------
0 79796
1 39617
2 9820
3 1641
4 173
5 21
6 4
7 0
8 0
9 0
10 0
11 0
12 0
13 0
14 0
15 0
16 0
17 0
18 0
19 0
20 0
>20 0
数据库有个参数与bucket个数有关，共计32768个bucket
_db_block_hash_buckets 32768 Number of database block hash buckets
但上述的bucket个数明显要大于32768，可见不是一回事
经计算可见上述library cache中用于空间分配的hash bucket个数共计131072，并且是固定的大小
SQL> select 79796+39617+9820+1641+173+21+4 from dual;
79796+39617+9820+1641+173+21+4
------------------------------
131072
SQL> select 79861+39579+9799+1635+173+21+4 from dual;
79861+39579+9799+1635+173+21+4
------------------------------
131072
但又想到一个参数，hash bucket个数与下述参数刚好一致，这是一回事呢，经测试，hash_area_size不是控制library cache hash bucket个数的参数
并且没有找到控制library cache hash bucket个数的参数，或者由ORACLE内部算法控制，具体原因暂时未知
hash_area_size 131072 size of in-memory hash work area
很简单，我调整下hash_area_size,再dump library cache,即知
SQL> alter system set hash_area_size=131999;
alter system set hash_area_size=131999
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option
hash_area_size必须重启库方可生效
SQL> alter system set hash_area_size=131999 scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 1157627904 bytes
Fixed Size 2095800 bytes
Variable Size 1090520392 bytes
Database Buffers 33554432 bytes
Redo Buffers 31457280 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter hash_area_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hash_area_size integer 131999
Hash Chain Size Number of Buckets
--------------- -----------------
0 130101
1 967
2 4
3 0
4 0
5 0
6 0
7 0
8 0
9 0
10 0
11 0
12 0
13 0
14 0
15 0
16 0
17 0
18 0
19 0
20 0
>20 0
可见library cache hash bucket个数还是131072，而非由hash_area_size控制
SQL> select 130101+967+4 from dual;
130101+967+4
------------
131072
后来也没有相到相关的参数，不过改造了一个SQL脚本，以后分析可以用脚本进行反推到某个数据库参数，列举于此，我认为此脚本的价值很大
set linesize 300
col name_1 for a50
col value_1 for a50
col desc1 for a50
select
ksppinm as name_1,
ksppstvl as value_1,
ksppdesc as desc1
from x$ksppi x, x$ksppcv y
where (x.indx = y.indx) and lower(ksppstvl) like '%&val%';
再以oradebug dump library_cache 6进行DUMP，分析更为详细 的LIBRARY CACHE内容
仅列出与oradebug dump library_cache 3不同的内容进行讲解
列出library cache 中所有bucket的信息，bucket里面是library object handle可以理解为不同的内存对象，有SQL，有存储过程，也有其它的对象，其handle对应x$kglob中的kglhdadr
可以理解为library cache中的内容是存储在bucket中，而每个bucket包括不同的library object handle
BUCKET 64970:
LIBRARY OBJECT HANDLE: handle=9ff11880 mtx=0x9ff119b0(0) lct=0 pct=0 cdp=0
name=SYS.UTL_RECOMP
hash=d5f7067038c28d08af944250a59afdca timestamp=04-20-2010 08:25:21
namespace=TABL flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0000-0000 lock=0 pin=0 latch#=1 hpc=0000 hlc=0000
lwt=0x9ff11928[0x9ff11928,0x9ff11928] ltm=0x9ff11938[0x9ff11938,0x9ff11938]
pwt=0x9ff118f0[0x9ff118f0,0x9ff118f0] ptm=0x9ff11900[0x9ff11900,0x9ff11900]
ref=0x9ff11958[0x9ff11958,0x9ff11958] lnd=0x9ff11970[0x9ff11758,0x9ff25d98]
BUCKET 64970 total object count=1
SQL> select addr,KGLHDADR from x$kglob where lower(kglhdadr)='000000009fe4a098';
ADDR KGLHDADR
---------------- ----------------
00002B934B552128 000000009FE4A098
除了上述的bucket，还有若名列表，也是包括多个不同的library object handle,具体为何ORACLE不会这些LIBRARY OBJECT HANDLE放到对应的bucket中，暂时原因不知
ANONYMOUS LIST:
LIBRARY OBJECT HANDLE: handle=9fe356f0 mtx=0x9fe35820(0) lct=2 pct=6 cdp=0
namespace=CRSR flags=RON/KGHP/PN0/EXP/[10010100]
kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch#=1 hpc=fffe hlc=fffe
lwt=0x9fe35798[0x9fe35798,0x9fe35798] ltm=0x9fe357a8[0x9fe357a8,0x9fe357a8]
pwt=0x9fe35760[0x9fe35760,0x9fe35760] ptm=0x9fe35770[0x9fe35770,0x9fe35770]
ref=0x9fe357c8[0x9ace88c0,0x9ace88c0] lnd=0x9fe357e0[0x9fe357e0,0x9fe357e0]
中间内容略
kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch#=1 hpc=0000 hlc=0000
lwt=0x9fe36b18[0x9fe36b18,0x9fe36b18] ltm=0x9fe36b28[0x9fe36b28,0x9fe36b28]
pwt=0x9fe36ae0[0x9fe36ae0,0x9fe36ae0] ptm=0x9fe36af0[0x9fe36af0,0x9fe36af0]
ref=0x9fe36b48[0x9aced298,0x9aced298] lnd=0x9fe36b60[0x9fe36b60,0x9fe36b60]
LIBRARY OBJECT: object=9acecb30
最后一部分是具有依赖对象的handle列，其含义暂时未知
LIST OF HANDLES WITH DEPENDENTS:
LATCH=0 unpin_call_count=5 hd_count=776 hd_unpinned_count=4
9ff9a9d0 -> 9ff96730 -> 9ff940b8 -> 9ff931d0 -> 9ff922e8 -> 9ff891e8 -
中间内容略
-> 9fe41c30 -> 9fe41958 -> 9fe41680 -> 9fe413a8 -> 9fe410d0 -> 9fe40df8 9fe3d9f8 -> 9fe3d720 -> 9fe3d230 -> 9fe38480 ->
9fe379e0 -> 9fe367b0
为了加深理解，我们现在创建一个新的SQL,然后DUMP下进行对比
SQL> create table t_libcache2(a int);
Table created.
SQL> select count(*) from t_libcache2;
COUNT(*)
----------
0
SQL> select sql_id,sql_text,address,hash_value from v$sql where sql_text like '%select count(*) from t_libcache2%';
SQL_ID SQL_TEXT ADDRESS HASH_VALUE
------------- -------------------------------------------------- ---------------- ----------
2dqgyd70ag5p7 select count(*) from t_libcache2 000000009FD7D038 3232208551
但好像没有在x$kglob中记录
SQL> select addr,KGLHDADR from x$kglob where kglhdadr='000000009FD7D038';
ADDR KGLHDADR
---------------- ----------------
00002B521C377E80 000000009FD7D038
可见已经上述的sql已存储到bucket 104103的library object handle中，而且library cache dump的handle addr仅为低8位，且为小写，而在x$kglob的列kghhdadr却为16列的大写，一定要注意
BUCKET 104103:
LIBRARY OBJECT HANDLE: handle=9fd7d038 mtx=0x9fd7d168(1) lct=3 pct=1 cdp=1
name=select count(*) from t_libcache2
hash=a1dca2c74e5cd69526d9fe69c0a796a7 timestamp=11-20-2015 10:07:13
namespace=CRSR flags=RON/KGHP/TIM/PN0/SML/KST/DBN/MTX/[120100d0]
kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=1 hpc=0002 hlc=0002
lwt=0x9fd7d0e0[0x9fd7d0e0,0x9fd7d0e0] ltm=0x9fd7d0f0[0x9fd7d0f0,0x9fd7d0f0]
pwt=0x9fd7d0a8[0x9fd7d0a8,0x9fd7d0a8] ptm=0x9fd7d0b8[0x9fd7d0b8,0x9fd7d0b8]
ref=0x9fd7d110[0x9fd7d110,0x9fd7d110] lnd=0x9fd7d128[0x9fd7d128,0x9fd7d128]
LIBRARY OBJECT: object=9ab0ba30
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
CHILDREN: size=16
child# table reference handle
------ -------- --------- --------
0 9ab0b930 9aaf34c0 9fd7ceb0
BUCKET 104103 total object count=1
好，再列举下x$kglob的重要列的含义
X$kglob 表说明如下：
Kglhdadr (children address) LIBRARY OBJECT HANDLE: handle
Kglhdpar (parent address) LIBRARY OBJECT HANDLE:handle
Kglnaown 依赖object的owner name
Kglnahsh sql的hash value （v$sql 中的hash_value）
Kglnahsv sql 的hash value （长串格式：9a5fb5d584eb42ce5f55b9c92930cf55）
Kglhdobj LIBRARY OBJECT: object
Kglobhd0 data#=0 的 heap 地址
Kglobhd1
Kglobhd2
Kglobhd3..7
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1843316/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-1843316/
463
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
