关于ora-04031错误，已经是老生常谈的问题了。我这里主要描述一种另外一种方式
来说缓解该错误，不过通常不建议这么做，这是一种无奈之举！
SQL> select * from v$version where rownum > 2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
SQL> show parameter shared_pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
__shared_pool_size big integer 92M
_dm_max_shared_pool_pct integer 1
_enable_shared_pool_durations boolean TRUE
_io_shared_pool_size big integer 4M
_shared_pool_max_size big integer 0
_shared_pool_minsize_on boolean FALSE
_shared_pool_reserved_min_alloc big integer 4400
_shared_pool_reserved_pct integer 5
shared_pool_reserved_size big integer 4823449
shared_pool_size big integer 0
SQL> SET pagesize 100
SQL> SELECT '0 (<140)' bucket, ksmchcls, 10 * TRUNC (ksmchsiz / 10) "From",
2 COUNT (*) "Count", MAX (ksmchsiz) "Biggest",
3 TRUNC (AVG (ksmchsiz)) "AvgSize", TRUNC (SUM (ksmchsiz)) "Total"
4 FROM x$ksmsp
5 WHERE ksmchsiz < 140 AND ksmchcls = 'free'
6 GROUP BY ksmchcls, 10 * TRUNC (ksmchsiz / 10)
7 UNION ALL
8 SELECT '1 (140-267)' bucket, ksmchcls, 20 * TRUNC (ksmchsiz / 20),
9 COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",
10 TRUNC (SUM (ksmchsiz)) "Total"
11 FROM x$ksmsp
12 WHERE ksmchsiz BETWEEN 140 AND 267 AND ksmchcls = 'free'
13 GROUP BY ksmchcls, 20 * TRUNC (ksmchsiz / 20)
14 UNION ALL
15 SELECT '2 (268-523)' bucket, ksmchcls, 50 * TRUNC (ksmchsiz / 50),
16 COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",
17 TRUNC (SUM (ksmchsiz)) "Total"
18 FROM x$ksmsp
19 WHERE ksmchsiz BETWEEN 268 AND 523 AND ksmchcls = 'free'
20 GROUP BY ksmchcls, 50 * TRUNC (ksmchsiz / 50)
21 UNION ALL
22 SELECT '3-5 (524-4107)' bucket, ksmchcls, 500 * TRUNC (ksmchsiz / 500),
23 COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",
24 TRUNC (SUM (ksmchsiz)) "Total"
25 FROM x$ksmsp
26 WHERE ksmchsiz BETWEEN 524 AND 4107 AND ksmchcls = 'free'
27 GROUP BY ksmchcls, 500 * TRUNC (ksmchsiz / 500)
28 UNION ALL
29 SELECT '6+ (4108+)' bucket, ksmchcls, 1000 * TRUNC (ksmchsiz / 1000),
30 COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",
31 TRUNC (SUM (ksmchsiz)) "Total"
32 FROM x$ksmsp
33 WHERE ksmchsiz <= 4108 AND ksmchcls = 'free'
34 GROUP BY ksmchcls, 1000 * TRUNC (ksmchsiz / 1000);
BUCKET KSMCHCLS From Count Biggest AvgSize Total
-------------- -------- ---------- ---------- ---------- ---------- ----------
0 (<140) free 70 9 76 75 676
0 (<140) free 40 15 48 43 648
0 (<140) free 20 64 28 24 1572
0 (<140) free 100 73 108 104 7600
0 (<140) free 90 8 92 92 736
0 (<140) free 80 37 88 83 3100
0 (<140) free 110 1 112 112 112
0 (<140) free 50 72 56 52 3760
0 (<140) free 30 10 36 34 344
0 (<140) free 130 3 136 136 408
0 (<140) free 60 18 68 64 1152
0 (<140) free 120 3 128 125 376
1 (140-267) free 180 12 192 187 2252
1 (140-267) free 220 2 232 226 452
1 (140-267) free 200 17 216 205 3492
1 (140-267) free 140 9 156 156 1404
1 (140-267) free 160 4 164 161 644
2 (268-523) free 500 1 520 520 520
3-5 (524-4107) free 1000 5 1060 1020 5104
3-5 (524-4107) free 500 25 996 904 22600
3-5 (524-4107) free 3000 1 3404 3404 3404
3-5 (524-4107) free 1500 1 1736 1736 1736
6+ (4108+) free 20000 1 20584 20584 20584
6+ (4108+) free 574000 1 574780 574780 574780
6+ (4108+) free 2917000 1 2917616 2917616 2917616
6+ (4108+) free 2362000 1 2362844 2362844 2362844
6+ (4108+) free 1085000 1 1085032 1085032 1085032
27 rows selected.
SQL> set lines 120
SQL> col sga_heap for a30
SQL> select KSMCHIDX "SubPool",
2 'sga heap(' || KSMCHIDX || ',0)' sga_heap,
3 ksmchcom ChunkComment,
4 decode(round(ksmchsiz / 1000),
5 0,
6 '0-1K',
7 1,
8 '1-2K',
9 2,
10 '2-3K',
11 3,
12 '3-4K',
13 4,
14 '4-5K',
15 5,
16 '5-6k',
17 6,
18 '6-7k',
19 7,
20 '7-8k',
21 8,
22 '8-9k',
23 9,
24 '9-10k',
25 '> 10K') "size",
26 count(*),
27 ksmchcls Status,
28 sum(ksmchsiz) Bytes
29 from x$ksmsp
30 where KSMCHCOM = 'free memory'
31 group by ksmchidx,
32 ksmchcls,
33 'sga heap(' || KSMCHIDX || ',0)',
34 ksmchcom,
35 ksmchcls,
36 decode(round(ksmchsiz / 1000),
37 0,
38 '0-1K',
39 1,
40 '1-2K',
41 2,
42 '2-3K',
43 3,
44 '3-4K',
45 4,
46 '4-5K',
47 5,
48 '5-6k',
49 6,
50 '6-7k',
51 7,
52 '7-8k',
53 8,
54 '8-9k',
55 9,
56 '9-10k',
57 '> 10K')
58 order by "size";
SubPool SGA_HEAP CHUNKCOMMENT size COUNT(*) STATUS BYTES
---------- ------------------------------ ---------------- ----- ---------- -------- ----------
1 sga heap(1,0) free memory 0-1K 357 free 28728
1 sga heap(1,0) free memory 1-2K 31 free 28224
1 sga heap(1,0) free memory 2-3K 1 free 1736
1 sga heap(1,0) free memory 3-4K 1 free 3404
1 sga heap(1,0) free memory > 10K 22 R-free 4683536
1 sga heap(1,0) free memory > 10K 5 free 6944176
6 rows selected.
SQL> break on ksmchidx on ksmchdur
SQL> SELECT ksmchidx, ksmchdur,
2 CASE
3 WHEN ksmchsiz < 1672
4 THEN TRUNC ((ksmchsiz - 32) / 8)
5 WHEN ksmchsiz < 4120
6 THEN TRUNC ((ksmchsiz + 7928) / 48)
7 WHEN ksmchsiz < 8216
8 THEN 250
9 WHEN ksmchsiz < 16408
10 THEN 251
11 WHEN ksmchsiz < 32792
12 THEN 252
13 WHEN ksmchsiz < 65560
14 THEN 253
15 WHEN ksmchsiz <= 65560
16 THEN 253
17 END bucket,
18 SUM (ksmchsiz) free_space, COUNT (*) free_chunks,
19 TRUNC (AVG (ksmchsiz)) average_size, MAX (ksmchsiz) biggest
20 FROM SYS.x$ksmsp
21 WHERE inst_id = USERENV ('Instance') AND ksmchcls = 'free'
22 GROUP BY CASE
23 WHEN ksmchsiz < 1672
24 THEN TRUNC ((ksmchsiz - 32) / 8)
25 WHEN ksmchsiz < 4120
26 THEN TRUNC ((ksmchsiz + 7928) / 48)
27 WHEN ksmchsiz < 8216
28 THEN 250
29 WHEN ksmchsiz < 16408
30 THEN 251
31 WHEN ksmchsiz < 32792
32 THEN 252
33 WHEN ksmchsiz < 65560
34 THEN 253
35 WHEN ksmchsiz <= 65560
36 THEN 253
37 END,
38 ksmchidx,
39 ksmchdur
40 ORDER BY ksmchidx, ksmchdur
41 /
KSMCHIDX KSMCHDUR BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZE BIGGEST
---------- ---------- ---------- ---------- ----------- ------------ ----------
1 1 4 68 1 68 68
61 520 1 520 520
83 696 1 696 696
98 820 1 820 820
121 1000 1 1000 1000
201 1736 1 1736 1736
236 3404 1 3404 3404
253 574780 1 574780 574780
2 -1 1108 47 23 24
0 596 20 29 36
1 504 12 42 44
2 256 5 51 52
3 348 6 58 60
4 256 4 64 64
5 452 6 75 76
6 1500 18 83 84
7 912 10 91 92
253 1079128 1 1079128 1079128
3 3 56 1 56 56
16 160 1 160 160
20 192 1 192 192
90 756 1 756 756
94 784 1 784 784
106 1764 2 882 884
110 916 1 916 916
253 2906896 1 2906896 2906896
4 -1 44 2 22 24
0 168 5 33 36
2 3424 66 51 52
3 240 4 60 60
4 408 6 68 68
5 224 3 74 76
6 1248 15 83 84
7 176 2 88 88
8 100 1 100 100
9 7500 72 104 108
10 112 1 112 112
11 248 2 124 124
12 128 1 128 128
13 408 3 136 136
15 1404 9 156 156
16 484 3 161 164
18 720 4 180 180
19 188 1 188 188
20 1152 6 192 192
21 2012 10 201 204
22 1264 6 210 212
23 436 2 218 220
25 232 1 232 232
106 6160 7 880 880
110 912 1 912 912
113 940 1 940 940
114 948 1 948 948
119 5912 6 985 988
120 1992 2 996 996
122 2016 2 1008 1008
124 1028 1 1028 1028
128 1060 1 1060 1060
250 4200 1 4200 4200
253 2362844 1 2362844 2362844
60 rows selected.
从上面我们可以发现，目前shared pool有一个subpool(_kghdsidx_count为1)，
该subpool又划分为4个subheap(也有人成为min heap)。
在10g和11g中，使用上面的几个sql查询结果已经不准确了（当然仍然可以参考），
通过下面的heapdump方式来查看更加确切的信息，如下：
SQL> alter system set events 'immediate trace name heapdump level 2';
System altered.
SQL> !
[oracle@roger ~]$ ./shared_pool_freelist.ksh roger_ora_5551.trc
-- Heapdump freelist v1.00 by Riyaj Shamsudeen @OraInternals
This script prints shared pool freelists details
1. /tmp/shared_pool_freelist.lst - Freelist information for various heaps
[oracle@roger ~]$ ls -ltr /tmp/shared_pool_freelist.lst
-rw-r--r-- 1 oracle dba 6376 Aug 31 14:19 /tmp/shared_pool_freelist.lst
[oracle@roger ~]$ cat /tmp/shared_pool_freelist.lst
------------------------------------------
sga heap(1,0)
------------------------------------------
Bucket 13 size=68 Count= 1 Sum= 68
Bucket 126 size=520 Count= 1 Sum= 520
Bucket 170 size=696 Count= 1 Sum= 696
Bucket 187 size=812 Count= 1 Sum= 820
Bucket 189 size=940 Count= 1 Sum= 1000
Bucket 203 size=1708 Count= 1 Sum= 1736
Bucket 229 size=3372 Count= 1 Sum= 3404
Bucket 254 size=65548 Count= 1 Sum= 574780
------------------------------------------
sga heap(1,1)
------------------------------------------
Bucket 1 size=20 Count= 5 Sum= 100
Bucket 2 size=24 Count= 42 Sum= 1008
Bucket 3 size=28 Count= 15 Sum= 420
Bucket 4 size=32 Count= 1 Sum= 32
Bucket 5 size=36 Count= 4 Sum= 144
Bucket 6 size=40 Count= 6 Sum= 240
Bucket 7 size=44 Count= 6 Sum= 264
Bucket 8 size=48 Count= 1 Sum= 48
Bucket 9 size=52 Count= 4 Sum= 208
Bucket 10 size=56 Count= 3 Sum= 168
Bucket 11 size=60 Count= 3 Sum= 180
Bucket 12 size=64 Count= 4 Sum= 256
Bucket 14 size=72 Count= 1 Sum= 72
Bucket 15 size=76 Count= 5 Sum= 380
Bucket 16 size=80 Count= 3 Sum= 240
Bucket 17 size=84 Count= 15 Sum= 1260
Bucket 18 size=88 Count= 2 Sum= 176
Bucket 19 size=92 Count= 8 Sum= 736
Bucket 254 size=65548 Count= 1 Sum= 1078492
------------------------------------------
sga heap(1,2)
------------------------------------------
Bucket 10 size=56 Count= 1 Sum= 56
Bucket 36 size=160 Count= 1 Sum= 160
Bucket 44 size=192 Count= 1 Sum= 192
Bucket 180 size=756 Count= 1 Sum= 756
Bucket 183 size=780 Count= 1 Sum= 784
Bucket 188 size=876 Count= 3 Sum= 2680
Bucket 254 size=65548 Count= 1 Sum= 2904752
------------------------------------------
sga heap(1,3)
------------------------------------------
Bucket 1 size=20 Count= 1 Sum= 20
Bucket 2 size=24 Count= 1 Sum= 24
Bucket 4 size=32 Count= 3 Sum= 96
Bucket 5 size=36 Count= 2 Sum= 72
Bucket 8 size=48 Count= 2 Sum= 96
Bucket 9 size=52 Count= 64 Sum= 3328
Bucket 11 size=60 Count= 4 Sum= 240
Bucket 13 size=68 Count= 6 Sum= 408
Bucket 14 size=72 Count= 1 Sum= 72
Bucket 15 size=76 Count= 2 Sum= 152
Bucket 16 size=80 Count= 3 Sum= 240
Bucket 17 size=84 Count= 12 Sum= 1008
Bucket 18 size=88 Count= 2 Sum= 176
Bucket 21 size=100 Count= 1 Sum= 100
Bucket 22 size=104 Count= 69 Sum= 7176
Bucket 23 size=108 Count= 3 Sum= 324
Bucket 24 size=112 Count= 1 Sum= 112
Bucket 27 size=124 Count= 2 Sum= 248
Bucket 28 size=128 Count= 1 Sum= 128
Bucket 30 size=136 Count= 3 Sum= 408
Bucket 35 size=156 Count= 9 Sum= 1404
Bucket 36 size=160 Count= 2 Sum= 320
Bucket 37 size=164 Count= 1 Sum= 164
Bucket 41 size=180 Count= 4 Sum= 720
Bucket 43 size=188 Count= 1 Sum= 188
Bucket 44 size=192 Count= 6 Sum= 1152
Bucket 46 size=200 Count= 7 Sum= 1400
Bucket 47 size=204 Count= 3 Sum= 612
Bucket 48 size=208 Count= 2 Sum= 416
Bucket 49 size=212 Count= 4 Sum= 848
Bucket 50 size=216 Count= 1 Sum= 216
Bucket 51 size=220 Count= 1 Sum= 220
Bucket 54 size=232 Count= 1 Sum= 232
Bucket 188 size=876 Count= 8 Sum= 7072
Bucket 189 size=940 Count= 10 Sum= 9792
Bucket 190 size=1004 Count= 4 Sum= 4104
Bucket 242 size=4108 Count= 1 Sum= 4200
Bucket 254 size=65548 Count= 1 Sum= 2362844
------------------------------------------
我们可以清楚的看到一共有4个subheap，每个subheap有254个bucket，这里需要说明一下的是，
其实每个subheap的管理方式完全一样，也是通过freelist的方式，上面的ksh脚本就是print一个
完整详细的freelist chunk信息。 下面我们将参数_enable_shared_pool_durations修改为false。
SQL> alter system set "_enable_shared_pool_durations"=false scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 104860968 bytes
Database Buffers 58720256 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump heapdump 2
Statement processed.
SQL> oradebug tracefile_name
/oracle/product/admin/roger/udump/roger_ora_6086.trc
SQL> !
[oracle@roger ~]$ ls -ltr *.ksh
-rwxrwxrwx 1 oracle dba 2529 Aug 31 14:18 shared_pool_freelist.ksh
[oracle@roger ~]$ ./shared_pool_freelist.ksh /oracle/product/admin/roger/udump/roger_ora_6086.trc
-- Heapdump freelist v1.00 by Riyaj Shamsudeen @OraInternals
This script prints shared pool freelists details
1. /tmp/shared_pool_freelist.lst - Freelist information for various heaps
[oracle@roger ~]$ cat /tmp/shared_pool_freelist.lst
------------------------------------------
sga heap(1,0)
------------------------------------------
Bucket 1 size=20 Count= 14 Sum= 280
Bucket 2 size=24 Count= 8 Sum= 192
Bucket 3 size=28 Count= 13 Sum= 364
Bucket 5 size=36 Count= 4 Sum= 144
Bucket 6 size=40 Count= 4 Sum= 160
Bucket 7 size=44 Count= 11 Sum= 484
Bucket 8 size=48 Count= 19 Sum= 912
Bucket 9 size=52 Count= 24 Sum= 1248
Bucket 10 size=56 Count= 13 Sum= 728
Bucket 11 size=60 Count= 10 Sum= 600
Bucket 12 size=64 Count= 6 Sum= 384
Bucket 13 size=68 Count= 18 Sum= 1224
Bucket 14 size=72 Count= 6 Sum= 432
Bucket 15 size=76 Count= 7 Sum= 532
Bucket 16 size=80 Count= 1 Sum= 80
Bucket 17 size=84 Count= 8 Sum= 672
Bucket 18 size=88 Count= 9 Sum= 792
Bucket 19 size=92 Count= 21 Sum= 1932
Bucket 253 size=32780 Count= 1 Sum= 53844
Bucket 254 size=65548 Count= 0 Sum= 0
------------------------------------------
我们可以看到，将该隐含参数修改为false以后，subheap 消失了，每个subpool只有一个subheap了。
虽然说这样可以在一定程度上消除shared pool 碎片，但是存在另外的隐患，那就是增加了latch的
争用。
关于文中提到的脚本，大家可以去这里下载http://orainternals.wordpress.com/2009/08/06/ora-4031-and-shared-pool-duration/
发表回复