数据缓冲 池争用
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表多不少,因为数据分散放在不同数据块上