ORACLE 使用records_per_block降低表資料塊熱塊機率

一、建立測試表
SQL> create table t_obj as select * from dba_objects;
 
Table created
二、建立索引
SQL> create index t_obj_ind on t_obj(object_id);
 
Index created
 三、收集統計資訊
SQL> exec dbms_stats.gather_table_stats(user,'t_obj',cascade=>true);
 
PL/SQL procedure successfully completed
四、檢視塊數，每塊記錄數
SQL> select 'T_OBJ' tbl_name, rows_per_block,count(*) number_of_such_blocks from
  2  (
  3   select dbms_rowid.rowid_block_number(rowid),count(*) rows_per_block from t_obj group by dbms_rowid.rowid_block_number(rowid)
  4  ) group by 'T_OBJ' ,rows_per_block;
 
TBL_NAME ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS
-------- -------------- ---------------------
T_OBJ                80                    18
T_OBJ                67                   156
T_OBJ                89                     2
T_OBJ                84                     1
T_OBJ                85                     1
T_OBJ                55                     1
T_OBJ                74                    27
T_OBJ                77                    28
T_OBJ                69                   152
T_OBJ                72                    27
T_OBJ                75                    28
T_OBJ                66                    71
T_OBJ                83                     2
T_OBJ                73                    37
T_OBJ                70                    74
T_OBJ                65                    19
T_OBJ                64                     2
T_OBJ                76                    35
T_OBJ                90                     2
T_OBJ                71                    56
 
TBL_NAME ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS
-------- -------------- ---------------------
T_OBJ                81                    12
T_OBJ                82                     2
T_OBJ                79                    16
T_OBJ                68                   225
T_OBJ                63                     4
T_OBJ                24                     1
T_OBJ                78                    33
T_OBJ                88                     1
 
28 rows selected

五、建立另一測試表t_obj_1

SQL> create table t_obj_1 as select * from dba_objects where rownum < 5;
 
Table created
六檢視t_obj_1的塊數，每塊記錄數
SQL> select 'T_OBJ_1' tbl_name, rows_per_block,count(*) number_of_such_blocks from
  2  (
  3   select dbms_rowid.rowid_block_number(rowid),count(*) rows_per_block from t_obj_1 group by dbms_rowid.rowid_block_number(rowid)
  4  ) group by 'T_OBJ_1' ,rows_per_block;
 
TBL_NAME ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS
-------- -------------- ---------------------
T_OBJ_1               4                     1

七、使用records_per_block，固定每塊記錄數。

SQL> alter table t_obj_1 minimize records_per_block;
 
Table altered

八、插入資料

SQL> truncate table t_obj_1;
 
Table truncated
 
SQL> 
SQL> select 'T_OBJ_1' tbl_name, rows_per_block,count(*) number_of_such_blocks from
  2      (
  3       select dbms_rowid.rowid_block_number(rowid),count(*) rows_per_block from t_obj_1 group by dbms_rowid.rowid_block_number(rowid)
  4      ) group by 'T_OBJ_1' ,rows_per_block;
 
TBL_NAME ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS
-------- -------------- ---------------------


SQL> insert into t_obj_1 select * from dba_objects;
 
72532 rows inserted
 
SQL> commit;
 
Commit complete

九建立索引

SQL>  create index t_obj_ind_1 on t_obj_1(object_id);
 
Index created

十、檢視塊數，每塊記錄數

SQL> select 'T_OBJ_1' tbl_name, rows_per_block,count(*) number_of_such_blocks from
  2      (
  3       select dbms_rowid.rowid_block_number(rowid),count(*) rows_per_block from t_obj_1 group by dbms_rowid.rowid_block_number(rowid)
  4      ) group by 'T_OBJ_1' ,rows_per_block;
 
TBL_NAME ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS
-------- -------------- ---------------------
T_OBJ_1               4                 18133

 T_OBJ_1每個塊存放的記錄數為4，因此T_OBJ_1比T_OBJ表使用的資料塊要多的多。

 十一、檢視t_obj執行計劃

SQL> set autotrace traceonly; 
SQL> set linesize 800
SQL> select * from t_obj where object_id < 1000;


已選擇942行。

執行計劃
----------------------------------------------------------
Plan hash value: 1458571288


-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |   964 | 93508 |    19   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJ     |   964 | 93508 |    19   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_OBJ_IND |   964 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"<1000)

統計資訊
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        141  consistent gets
          0  physical reads
          0  redo size
      94571  bytes sent via SQL*Net to client
       1098  bytes received via SQL*Net from client
         64  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        942  rows processed
 十一、檢視t_obj_1執行計劃
SQL> select * from t_obj_1 where object_id < 1000;

已選擇942行。

執行計劃
----------------------------------------------------------
Plan hash value: 3240268517

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   942 |   190K|   278   (0)| 00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJ_1     |   942 |   190K|   278   (0)| 00:00:04 |
|*  2 |   INDEX RANGE SCAN          | T_OBJ_IND_1 |   942 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("OBJECT_ID"<1000)


Note
-----
   - dynamic sampling used for this statement (level=2)

統計資訊
----------------------------------------------------------
          9  recursive calls
          0  db block gets
        474  consistent gets
          4  physical reads
          0  redo size
      94571  bytes sent via SQL*Net to client
       1098  bytes received via SQL*Net from client
         64  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)

當分別選擇1000資料時，執行計劃相同，返回的結果集相同，但T_OBJ_1一致性讀的次數為474，遠多於T_OBJ的141次，原因為相同的記錄，T_OBJ_1分佈在更大的資料塊上。降低了一個塊被重複讀取的機率。缺點是效能下降。當然，如果多個使用者併發修改不同的記錄，那麼這些使用者讀取同一個塊的機率就會降低，也就是熱塊出現的機率降低。