缓解ora-04031的一种方法

本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

关于ora-04031错误，已经是老生常谈的问题了。我这里主要描述一种另外一种方式
来说缓解该错误，不过通常不建议这么做，这是一种无奈之举！

SQL> select * from v$version where rownum > 2;

1	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




1

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);

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                '&gt; 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                   '&gt; 10K')
 58   order by "size";

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  /

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> !

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

[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> !

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

[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/

Tags: ora-04031 _enable_shared_pool_durations
Posted in mangement, Oracle rdbms on 8月 31, 2011

One Response to “缓解ora-04031的一种方法”

yangjiawei Says:

9月 1, 2011 at 上午 8:55

兄弟，shared_pool_freelist.ksh这个脚本在http://orainternals.wordpress.com/2009/08/06/ora-4031-and-shared-pool-duration/里没找到啊，能不能发给小弟一下？多谢。
邮箱地址：yangjiawei@cpic.com.cn

You must be logged in to post a comment.

love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

Categories

Archives

最新评论

国内圈子

oracle security

缓解ora-04031的一种方法

One Response to “缓解ora-04031的一种方法”

Leave a Reply