latch -buffer busy wait 之表数据块争用导致热块

数据缓冲 池争用

buffer busy wait

1张表有很多数据行，分布在不同的数据块中

并发用户访问表中相同数据会产生争用，访问相同的数据块也会产生争用（热块）

避免访问相同数据块访问不同表数据引起争用的方法是

将某个表分散放在不同的数据库块上

alter table t1 minimize records_per_block;

示例：

SQL> create table t as select * from dba_objects;

Table created.

SQL> create index t_inx on t(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);

PL/SQL procedure successfully completed.

-

SQL> set pagesize 120

SQL> select 'T' tbl_name,rows_per_block,count(*) number_of_such_blocks from

  2   (select dbms_rowid.ROWID_BLOCK_NUMBER(rowid),count(*) rows_per_block from

  3   t group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid)) group by 'T',rows_per_block;

T ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS

- -------------- ---------------------

T             87                     2

T             82                    15

T             74                    28

T             77                    16

T             76                    15

T             65                     1

T             84                     8

T             69                    73

T             91                     1

T             93                     2

T             75                    21

T             72                    55

T             68                    49

T             94                     1

T             88                     1

T             73                    28

T             81                    19

T             79                    26

T              9                     1

T             92                     1

T             83                    13

T             70                   153

T             71                   106

T             67                    10

T             86                     2

T             66                     1

T             80                    18

T             78                    14

T             85                     4

29 rows selected.

SQL> create table t1 as select * from dba_objects where rownum<3;

Table created.

SQL> select 'T' tbl_name,rows_per_block,count(*) number_of_such_blocks from

  2   (select dbms_rowid.ROWID_BLOCK_NUMBER(rowid),count(*) rows_per_block from

  3   t group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid)) group by 'T',rows_per_block;

T ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS

- -------------- ---------------------

T             87                     2

T             82                    15

T             74                    28

T             77                    16

T             76                    15

T             65                     1

T             84                     8

T             69                    73

T             91                     1

T             93                     2

T             75                    21

T             72                    55

T             68                    49

T             94                     1

T             88                     1

T             73                    28

T             81                    19

T             79                    26

T              9                     1

T             92                     1

T             83                    13

T             70                   153

T             71                   106

T             67                    10

T             86                     2

T             66                     1

T             80                    18

T             78                    14

T             85                     4

29 rows selected.

SQL>  select 'T' tbl_name,rows_per_block,count(*) number_of_such_blocks from

  2   (select dbms_rowid.ROWID_BLOCK_NUMBER(rowid),count(*) rows_per_block from

  3   t1 group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid)) group by 'T',rows_per_block;

T ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS

- -------------- ---------------------

T              2                     1

SQL> alter table t1 minimize records_per_block;

Table altered.

SQL> insert into t1 select * from dba_objects;

49783 rows created.

SQL> commit;

Commit complete.

SQL> create index t1_inx on t1(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true);

PL/SQL procedure successfully completed.

SQL>  select 'T' tbl_name,rows_per_block,count(*) number_of_such_blocks from

  2   (select dbms_rowid.ROWID_BLOCK_NUMBER(rowid),count(*) rows_per_block from

  3   t1 group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid)) group by 'T',rows_per_block;

T ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS

- -------------- ---------------------

T              1                     1

T              2                 24892

SQL> set autotrace traceonly

SQL> select * from t where object_id<1000;

953 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1579008347

--------------------------------------------------------------------------------

-----

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time

    |

--------------------------------------------------------------------------------

-----

|   0 | SELECT STATEMENT            |       |   966 | 89838 |    18   (0)| 00:00

:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T     |   966 | 89838 |    18   (0)| 00:00

:01 |

|*  2 |   INDEX RANGE SCAN          | T_INX |   966 |       |     4   (0)| 00:00

:01 |

--------------------------------------------------------------------------------

-----

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID"<1000)

Statistics

----------------------------------------------------------

        244  recursive calls

          0  db block gets

        180  consistent gets

          0  physical reads

          0  redo size

      95291  bytes sent via SQL*Net to client

       1162  bytes received via SQL*Net from client

         65  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        953  rows processed

SQL> select * from t1 where object_id<1000;

955 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1311207630

--------------------------------------------------------------------------------

------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time

     |

--------------------------------------------------------------------------------

------

|   0 | SELECT STATEMENT            |        |   947 | 88071 |   489   (0)| 00:0

0:06 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |   947 | 88071 |   489   (0)| 00:0

0:06 |

|*  2 |   INDEX RANGE SCAN          | T1_INX |   947 |       |     4   (0)| 00:0

0:01 |

--------------------------------------------------------------------------------

------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID"<1000)

Statistics

----------------------------------------------------------

        244  recursive calls

          0  db block gets

        656  consistent gets

          0  physical reads

          0  redo size

      95445  bytes sent via SQL*Net to client

       1162  bytes received via SQL*Net from client

         65  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        955  rows processed

t1的consistent gets 比t表多不少，因为数据分散放在不同数据块上