love wife & love life —Roger 的Oracle技术博客

Phone:18180207355 提供专业Oracle数据恢复、性能优化、迁移升级、紧急救援等服务

缓解ora-04031的一种方法

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客

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

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


BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

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

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.


   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.


  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方式来查看更加确切的信息,如下:


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



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

18180207355
加Q咨询