缓解ora-04031的一种方法

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 提供oracle技术支持服务

本文链接地址: 缓解ora-04031的一种方法

关于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                '&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";


   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/

    分享到:

No related posts.

  • http://no yangjiawei

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