結論

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

深入理解shared pool共享池空間及library cache分配之ora-4031 系列一

結論

引發的問題：

測試

相關文章