一张记录用户登录退出的表,每天9点会突然慢一下,等待事件为buffer busy wait并发插入量为230左右。oracle使用assm(如图)也就是L3,L2,L1架构理论上100个L1每个L1管理64个块就支持6400并发,实际也是如此吗?

进行插入实验发现,插入都是插入到1个分区里面,我们知道分区是一个接一个分配的,因为还没分配到分区所以才导致插到1个分区里,手动分配好多个分区。不幸的是结果还是一样。

插入实验一:

SQL> create table ye (id int,name char(10)) tablespace ye;

Table created.

[oracle]$cat   insert.sh                                                

sqlplus / as sysdba<<EOF
insert into ye values($1,'qqqq');
commit;
exec dbms_lock.sleep(100000)                 #防止pid不变导致插入同一个数据块
EOF

[oracle]$./insert.sh  1&

[oracle]$./insert.sh  2&

[oracle]$./insert.sh  3&

[oracle]$./insert.sh  4&

SQL> /

ID    FNO    BLOCKID ROW_ID
---------- ---------- ---------- ----------
1    8    161    0
2    8    171    0
1    8    172    0
3    8    172    1
3    8    178    0
4    8    181    0
5    8    184    0
2    8    185    0
6    8    186    0
7    8    187    0
8    8    188    0
9    8    189    0
10    8    190    0

 

 插入实验二:

 

SQL> /

    ID      FNO     BLOCKID     ROW_ID
---------- ---------- ---------- ----------
    11        8         132      0
    66        8         132      1
    12        8         133      0
    67        8         133      1
    12        8         134      0
    68        8         134      1
    12        8         135      0
    69        8         135      1
    12        8         136      0
    70        8         136      1
    13        8         137      0
    14        8         138      0
    15        8         139      0
    16        8         140      0
    17        8         141      0
    18        8         142      0
    19        8         143      0
    20        8         144      0
    21        8         145      0
    22        8         146      0
    23        8         147      0
    24        8         148      0
    25        8         149      0
    25        8         150      0
    26        8         151      0
    27        8         152      0
    28        8         153      0
    29        8         154      0
    30        8         155      0
    31        8         156      0
    32        8         157      0
    33        8         158      0
    34        8         159      0
    35        8         160      0
     1        8         161      0
    36        8         161      1
    37        8         162      0
    38        8         163      0
    39        8         164      0
    40        8         165      0
     4        8         166      0
    41        8         167      0
    42        8         168      0
    43        8         169      0
    44        8         170      0
     2        8         171      0
    45        8         171      1
     1        8         172      0
     3        8         172      1
    46        8         172      2
    47        8         173      0
    48        8         174      0
    49        8         175      0
    50        8         176      0
    51        8         177      0
     3        8         178      0
    52        8         178      1
    53        8         179      0
    54        8         180      0
     4        8         181      0
    55        8         181      1
    56        8         182      0
    57        8         183      0
     5        8         184      0
    58        8         184      1
     2        8         185      0
    59        8         185      1
     6        8         186      0
    60        8         186      1
     7        8         187      0
    61        8         187      1
     8        8         188      0
    62        8         188      1
     9        8         189      0
    63        8         189      1
    10        8         190      0
    63        8         190      1
    11        8         191      0
    65        8         191      1

79 rows selected.

 

 在进行70次插入发现,block到191之后开始重新向132号块插入了,这也就能解释128,129号块为L1,130是L2,131是L3,128-191,64个块的同时插入不够230并发量所以有些块在插入过程中又有新的会话要插入,所以导致了buffer busy wait,那么为什么只插入前64个块呢?此时可以dump下131号段头块,可以看到前10位为文件号,后面为块地址,正好看到是192号块。插入的时候都是插入到高水位线以下,因此都插到了前64个块。解决方法就是拉高水位线,每天先使用append方式插入数据再删除,结果等待消失。

 

在总结下,如果块是8K,区大小是1M,高水位就是一64个块为单位依次往后挪,但是每次并发插入其实都只是往64个块插入。

这里还有个问题L1管理64个块是固定的么?

SQL> create table tbs(id int ,name char(10)) tablespace ye1;

Table created.

SQL>  alter table tbs allocate extent(size 200m); 

Table altered.

SQL> select extent_id,block_id,blocks,file_id from dba_extents where segment_name='TBS' order by 1;

 EXTENT_ID   BLOCK_ID      BLOCKS    FILE_ID
---------- ---------- ---------- ----------
     0      128           8      9
     1      256         128      9
     2      384         128      9
     3      512         128      9
     4      640         128      9
     5      768         128      9
     6      896         128      9
     7     1024         128      9
     8     1152         128      9
     9     1280         128      9
    10     1408         128      9
    11     1536         128      9
    12     1664         128      9
    13     1792         128      9
    14     1920         128      9
    15     2048         128      9
    16     2176         128      9
    17     2304         128      9
    18     2432         128      9
    19     2560         128      9
    20     2688         128      9
    21     2816         128      9
    22     2944         128      9
    23     3072         128      9
    24     3200         128      9
    25     3328         128      9
    26     3456         128      9
    27     3584         128      9
    28     3712         128      9
    29     3840         128      9
    30     3968         128      9
    31     4096         128      9
    32     4224         128      9
    33     4352         128      9
    34     4480         128      9
    35     4608         128      9
    36     4736         128      9
    37     4864         128      9
    38     4992         128      9
    39     5120         128      9
    40     5248         128      9
    41     5376         128      9
    42     5504         128      9
    43     5632         128      9
    44     5760         128      9
    45     5888         128      9
    46     6016         128      9
    47     6144         128      9
    48     6272         128      9
    49     6400         128      9
    50     6528         128      9
    51     6656         128      9
    52     6784         128      9
    53     6912         128      9
    54     7040         128      9
    55     7168         128      9
    56     7296         128      9
    57     7424        1024      9
    58     8448        1024      9
    59     9472        1024      9
    60    10496        1024      9
    61    11520        1024      9
    62    12544        1024      9
    63    13568        1024      9
    64    14592        1024      9
    65    15616        1024      9
    66    16640        1024      9
    67    17664        1024      9
    68    18688        1024      9
    69    19712        1024      9
    70    20736        1024      9
    71    21760        1024      9
    72    22784        1024      9
    73    23808        1024      9
    74    24832        1024      9

75 rows selected.

dump256和21760号块,发现第一个块的L1是管理64个块第二个是有256个块。经测试8M区大小的分区第8个分区以后L1中数据块的数量就会达到256个块。因此只要建立8m区大小的表空间插入一些数据将高水位抬到第8个分区以后就可以了。