通过案例学调优之--RECORDS_PER_BLOCK参数

通过案例学调优之--RECORDS_PER_BLOCK参数

     RECORDS_PER_BLOCK参数用于设定每个BLOCK中记录数的最大值，其先找到当前表所有BLOCK中容纳的最大行数，并会把这个数字记录到数据字典，以后任何导致BLOCK行数超过这个数字的插入都会被拒绝。

    RECORDS_PER_BLOCK参数是为位图索引而生的，能够改善位图索引的存储，减小位图索引的长度。这样，利用该位图索引的时候，就能获得比较好的效率了。

    测试案例：

1、表默认的存储分析

15:45:46 SCOTT@ prod >create table t3 (x int,y int);
Table created.

15:46:03 SCOTT@ prod >insert into t3 values (1,1);
1 row created.

15:46:12 SCOTT@ prod >insert into t3 values (2,1);
1 row created.

15:46:27 SCOTT@ prod >commit;
Commit complete.

15:48:01 SCOTT@ prod >insert into t3 select rownum+2,1 from all_objects where rownum <=254;
254 rows created.

15:48:37 SCOTT@ prod >create index t3_indx on t3(x);
Index created.

15:48:57 SCOTT@ prod >exec dbms_stats.gather_table_stats(user,'T3',cascade=>true);
PL/sql procedure successfully completed.

15:49:54 SCOTT@ prod >select count(distinct dbms_rowid.rowid_block_number(rowid)) from t3;
COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
                                                  1

15:53:09 SCOTT@ prod >col segment_name for a20
15:53:21 SCOTT@ prod >select segment_name,EXTENTS,BLOCKS,BYTES from user_segments where segment_name='T3';
SEGMENT_NAME            EXTENTS     BLOCKS      BYTES
-------------------- ---------- ---------- ----------
T3                            1          8      65536

默认值，T3表中的数据存储在一个数据块上。

2、通过RECORDS_PER_BLOCK参数分散数据块的存储

15:57:47 SCOTT@ prod >drop table t3 purge;
Table dropped.
15:59:59 SCOTT@ prod >create table t3 (x int,y int);
Table created.

16:00:08 SCOTT@ prod >insert into t3 values (1,1);
1 row created.

16:00:16 SCOTT@ prod >insert into t3 values (2,1);
1 row created.

16:00:25 SCOTT@ prod >commit;
Commit complete.

16:00:37 SCOTT@ prod >alter table t3 minimize records_per_block;
Table altered.

16:00:54 SCOTT@ prod >insert into t3 select rownum+2,1 from all_objects where rownum <=254;
254 rows created.

16:01:09 SCOTT@ prod >commit;
Commit complete.

17:15:14 SCOTT@ prod >create index t3_indx on t3(x);

Index created.

16:01:12 SCOTT@ prod >exec dbms_stats.gather_table_stats(user,'T3');
PL/SQL procedure successfully completed.

16:01:58 SCOTT@ prod >select count(distinct dbms_rowid.rowid_block_number(rowid)) from t3;
COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
                                                128

16:21:29 SCOTT@ prod >select dbms_rowid.rowid_block_number(rowid),count(0)   from t3
   group by dbms_rowid.rowid_block_number(rowid);

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(0)
------------------------------------ ----------
                                 198          2
                                 138          2
                                 151          2
                                 153          2
                                 167          2
                                 244          2
                                 245          2
                                 247          2
                                 537          2
                                 544          2
                                 134          2
                                 194          2
                                 207          2
                                 147          2
                                 209          2
                                 213          2
                                 155          2
......
                              

128 rows selected.

可以看出，T3表占用了128个数据块！

        测试发现：执行alter table test minimize records_per_block;之后，目前BLOCK中的记录数（的最大值）会应用到以后的新增数据中，也就是，当以后再往表中INSERT数据时，每个BLOCK中可以包含的记录数将与设定records_per_block之前的最大值保持一致。

     需要注意的是：

• 不能对空表设定此参数。

• 每个BLOCK中可以包含的记录数的最低下限是2。

• 不能在已经有 bitmap 的表中使用records_per_block参数，也就是说，如果要使用records_per_block参数，必须先alter table xxx minimize records_per_block，然后才能在表上建立索引。

         如果字段的类型、大小、个数发生了改变，那么就会导致一个比较差的结果，这就说明了，这项功能只在于使用在静态的环境中，比如数据仓库。

主要用途：

• 通过减少同一个block中的记录数，使记录分布于更多的数据块中，可以优化等待块类型为data block的Buffer Busy Wait事件。

• 其主要用途是提高BITMAP INDEX的存储性能

3、对table访问分析

15:44:39 SYS@ prod >alter system flush buffer_cache;
System altered.

16:07:01 SYS@ prod >show parameter mult
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
db_file_multiblock_read_count        integer                          28
parallel_adaptive_multi_user         boolean                          TRUE

17:32:42 SCOTT@ prod >col object_name for a20
17:32:49 SCOTT@ prod >select object_name,object_id from user_objects where object_name='T3';

OBJECT_NAME           OBJECT_ID
-------------------- ----------
T3                        76505

16:22:19 SCOTT@ prod >alter session set db_file_multiblock_read_count=64;

Session altered.
将数据块以间隔的方式读入内存
16:09:03 SCOTT@ prod >declare
16:09:20   2  num number;
16:09:25   3  begin
16:09:29   4  for i in 1..64
16:09:34   5  loop
16:09:37   6  select y into num from t3 where x=i*4;
16:09:42   7  end loop;
16:09:48   8  end;
16:09:50   9  /
PL/SQL procedure successfully completed.

17:25:29 SYS@ prod >select file#,block#,status,objd from v$bh where file#=4;

     FILE#     BLOCK# STATUS           OBJD
---------- ---------- ---------- ----------
         4        521 free            76505
         4        521 free            76505
         4        521 free            76505
         4        165 free            76505
         4        165 free            76505
         4        165 free            76505
         4        542 free            76505
         4        542 free            76505
         4        542 free            76505
         4        131 free            76505
         4        131 free            76505
         4        131 free            76505
         4        131 xcur            76505
         4        529 free            76505
         4        529 free            76505
         4        529 free            76505
         4        529 xcur            76505

     FILE#     BLOCK# STATUS           OBJD
---------- ---------- ---------- ----------
         4        550 free            76505
         4        550 free            76505
         4        550 free            76505
         4        139 free            76505
         4        139 free            76505
         4        139 free            76505
         4        139 xcur            76505
         4        537 free            76505
         4        537 free            76505
         4        537 free            76505
         4          3 free       4294967295
         4          3 free       4294967295
         4        147 free            76505
         4        147 free            76505
         4        147 free            76505
         4        524 free            76505
         4        524 free            76505

     FILE#     BLOCK# STATUS           OBJD
---------- ---------- ---------- ----------
         4        524 free            76505
         4        545 free            76505
         4        545 free            76505
         4        545 free            76505
         4        545 xcur            76505
         4        134 free            76505
         4        134 free            76505
         4        134 free            76505
         4        134 xcur            76505
         4        155 free            76505
         4        155 free            76505
         4        155 free            76505
         4        155 xcur            76505
         4        532 free            76505
         4        532 free            76505
         4        532 free            76505
         4        532 xcur            76505

     FILE#     BLOCK# STATUS           OBJD
---------- ---------- ---------- ----------
         4        553 free            76506
         4        142 free            76505
         4        142 free            76505
         4        142 free            76505
         4        163 free            76505
         4        163 free            76505
         4        163 free            76505
         4        540 free            76505
         4        540 free            76505
         4        540 free            76505
         4        129 free            76505
         4        129 free            76505
         4        129 free            76505
         4        150 free            76505
         4        150 free            76505
         4        150 free            76505
         4        150 xcur            76505

     FILE#     BLOCK# STATUS           OBJD
---------- ---------- ---------- ----------
         4        527 free            76505
         4        527 free            76505
         4        527 free            76505
         4        527 xcur            76505
         4        548 free            76505
         4        548 free            76505
         4        548 free            76505
         4        137 free            76505
         4        137 free            76505
         4        158 free            76505
         4        158 free            76505
         4        535 free            76505
         4        535 free            76505
         4        145 free            76505
         4        145 free            76505
         4        145 xcur            76505
         4        522 free            76505

     FILE#     BLOCK# STATUS           OBJD
---------- ---------- ---------- ----------
         4        522 free            76505
         4        522 xcur            76505
         4        166 free            76505
         4        166 free            76505
         4        166 xcur            76505
         4        543 free            76505
         4        543 free            76505
         4        543 xcur            76505
         4        132 free            76505
         4        132 free            76505
         4        153 free            76505
         4        153 free            76505
         4        530 free            76505
         4        530 free            76505
         4        551 free            76505
         4        551 free            76505
         4        551 xcur            76505

     FILE#     BLOCK# STATUS           OBJD
---------- ---------- ---------- ----------
         4        140 free            76505
         4        140 free            76505
         4        161 free            76505
         4        161 free            76505
         4        161 xcur            76505
         4        538 free            76505
         4        538 free            76505
         4        538 xcur            76505
         4        148 free            76505
         4        148 free            76505
         4        148 xcur            76505
         4        525 free            76505
         4        525 free            76505
         4        525 xcur            76505
         4        546 free            76505
         4        546 free            76505
         4        135 free            76505

     FILE#     BLOCK# STATUS           OBJD
---------- ---------- ---------- ----------
         4        135 free            76505
         4        156 free            76505
         4        156 free            76505
         4        533 free            76505
         4        533 free            76505
         4        554 free            76506
         4        143 free            76505
         4        143 free            76505
         4        143 xcur            76505
         4        164 free            76505
         4        164 free            76505
         4        164 xcur            76505
         4        541 free            76505
         4        541 free            76505
         4        541 xcur            76505
         4        130 free            76505
         4        130 free            76505

     FILE#     BLOCK# STATUS           OBJD
---------- ---------- ---------- ----------
         4        151 free            76505
         4        151 free            76505
         4        549 free            76505
         4        549 free            76505
         4        549 xcur            76505
         4        138 free            76505
         4        138 free            76505
         4        138 xcur            76505
         4        159 free            76505
         4        159 free            76505
         4        159 xcur            76505
         4          2 free       4294967295
         4        146 free            76505
         4        146 free            76505
         4        523 free            76505
         4        523 free            76505
         4        523 xcur            76505

     FILE#     BLOCK# STATUS           OBJD
---------- ---------- ---------- ----------
         4        167 free            76505
         4        167 free            76505
         4        544 free            76505
         4        544 free            76505
         4        133 free            76505
         4        133 free            76505
         4        154 free            76505
         4        154 free            76505
         4        154 xcur            76505
         4        531 free            76505
         4        531 free            76505
         4        552 free            76506
         4        141 free            76505
         4        141 free            76505
         4        141 xcur            76505
         4        162 free            76505
         4        162 free            76505

     FILE#     BLOCK# STATUS           OBJD
---------- ---------- ---------- ----------
         4        539 free            76505
         4        539 free            76505
         4        539 xcur            76505
         4        149 free            76505
         4        149 free            76505
         4        526 free            76505
         4        526 free            76505
         4        547 free            76505
         4        547 free            76505
         4        547 xcur            76505
         4        157 free            76505
         4        157 free            76505
         4        157 xcur            76505
         4        534 free            76505
         4        534 free            76505
         4        534 xcur            76505
         4        555 free            76506

     FILE#     BLOCK# STATUS           OBJD
---------- ---------- ---------- ----------
         4        555 xcur            76506
188 rows selected.
16:14:20 SYS@ prod >grant alter session to scott;
Grant succeeded.

16:14:39 SYS@ prod >conn scott/tiger
Connected.
16:14:42 SCOTT@ prod >alter session set events '10046 trace name context forever,level 12';
Session altered.

16:15:31 SCOTT@ prod >set autotrace trace
16:15:37 SCOTT@ prod >select * from t3 ;
256 rows selected.
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   256 |  1792 |    68   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T3   |   256 |  1792 |    68   (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        196  consistent gets
          0  physical reads
          0  redo size
       4829  bytes sent via SQL*Net to client
        606  bytes received via SQL*Net from client
         19  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        256  rows processed
        
[oracle@RH6 ~]$ ls -lt /u01/app/oracle/diag/rdbms/prod/prod/trace/|more
total 12056
-rw-r----- 1 oracle oinstall  51244 Nov 19 17:28 prod_ora_3681.trc
-rw-r----- 1 oracle oinstall    199 Nov 19 17:28 prod_ora_3681.trm
-rw-r--r-- 1 oracle oinstall 430401 Nov 19 17:22 alert_prod.log
-rw-r----- 1 oracle oinstall   8230 Nov 19 17:18 prod_ora_3629.trc

[oracle@RH6 ~]$ grep sequen /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_3681.trc
WAIT #10: nam='db file sequential read' ela= 13 file#=4 block#=130 blocks=1 obj#=76505 tim=1416389324098217
WAIT #10: nam='db file sequential read' ela= 7 file#=4 block#=135 blocks=1 obj#=76505 tim=1416389324098716
WAIT #10: nam='db file sequential read' ela= 7 file#=6 block#=193 blocks=1 obj#=76505 tim=1416389324098758
WAIT #10: nam='db file sequential read' ela= 0 file#=6 block#=195 blocks=1 obj#=76505 tim=1416389324098837
WAIT #10: nam='db file sequential read' ela= 0 file#=6 block#=197 blocks=1 obj#=76505 tim=1416389324098837
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=199 blocks=1 obj#=76505 tim=1416389324098874
WAIT #10: nam='db file sequential read' ela= 9 file#=4 block#=137 blocks=1 obj#=76505 tim=1416389324098917
WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=140 blocks=1 obj#=76505 tim=1416389324099100
WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=142 blocks=1 obj#=76505 tim=1416389324099144
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=200 blocks=1 obj#=76505 tim=1416389324099188
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=202 blocks=1 obj#=76505 tim=1416389324099230
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=204 blocks=1 obj#=76505 tim=1416389324099395
WAIT #10: nam='db file sequential read' ela= 7 file#=6 block#=206 blocks=1 obj#=76505 tim=1416389324099439
WAIT #10: nam='db file sequential read' ela= 223 file#=4 block#=149 blocks=1 obj#=76505 tim=1416389324100699
WAIT #10: nam='db file sequential read' ela= 13 file#=4 block#=151 blocks=1 obj#=76505 tim=1416389324100962
WAIT #10: nam='db file sequential read' ela= 9 file#=6 block#=209 blocks=1 obj#=76505 tim=1416389324101019
WAIT #10: nam='db file sequential read' ela= 9 file#=6 block#=211 blocks=1 obj#=76505 tim=1416389324101319
WAIT #10: nam='db file sequential read' ela= 7 file#=6 block#=213 blocks=1 obj#=76505 tim=1416389324101384
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=215 blocks=1 obj#=76505 tim=1416389324101418
WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=153 blocks=1 obj#=76505 tim=1416389324101459
WAIT #10: nam='db file sequential read' ela= 10 file#=4 block#=156 blocks=1 obj#=76505 tim=1416389324101664
WAIT #10: nam='db file sequential read' ela= 9 file#=4 block#=158 blocks=1 obj#=76505 tim=1416389324101716
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=216 blocks=1 obj#=76505 tim=1416389324101770
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=218 blocks=1 obj#=76505 tim=1416389324101813
WAIT #10: nam='db file sequential read' ela= 9 file#=6 block#=220 blocks=1 obj#=76505 tim=1416389324101992
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=222 blocks=1 obj#=76505 tim=1416389324102036
WAIT #10: nam='db file sequential read' ela= 9 file#=4 block#=165 blocks=1 obj#=76505 tim=1416389324102276
WAIT #10: nam='db file sequential read' ela= 7 file#=4 block#=167 blocks=1 obj#=76505 tim=1416389324102309
WAIT #10: nam='db file sequential read' ela= 9 file#=6 block#=233 blocks=1 obj#=76505 tim=1416389324102355
WAIT #10: nam='db file sequential read' ela= 32 file#=6 block#=235 blocks=1 obj#=76505 tim=1416389324102705
WAIT #10: nam='db file sequential read' ela= 9 file#=6 block#=237 blocks=1 obj#=76505 tim=1416389324102931
WAIT #10: nam='db file sequential read' ela= 27 file#=6 block#=239 blocks=1 obj#=76505 tim=1416389324103182
WAIT #10: nam='db file sequential read' ela= 10 file#=6 block#=256 blocks=1 obj#=76505 tim=1416389324103344
WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=129 blocks=1 obj#=76505 tim=1416389324103389
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=257 blocks=1 obj#=76505 tim=1416389324103423
WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=521 blocks=1 obj#=76505 tim=1416389324103466
WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=524 blocks=1 obj#=76505 tim=1416389324103678
WAIT #10: nam='db file sequential read' ela= 7 file#=4 block#=526 blocks=1 obj#=76505 tim=1416389324103722
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=240 blocks=1 obj#=76505 tim=1416389324103766
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=242 blocks=1 obj#=76505 tim=1416389324103808
WAIT #10: nam='db file sequential read' ela= 9 file#=6 block#=244 blocks=1 obj#=76505 tim=1416389324103872
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=246 blocks=1 obj#=76505 tim=1416389324103918
WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=533 blocks=1 obj#=76505 tim=1416389324104170
WAIT #10: nam='db file sequential read' ela= 7 file#=4 block#=535 blocks=1 obj#=76505 tim=1416389324104206
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=249 blocks=1 obj#=76505 tim=1416389324104250
WAIT #10: nam='db file sequential read' ela= 9 file#=6 block#=251 blocks=1 obj#=76505 tim=1416389324104449
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=253 blocks=1 obj#=76505 tim=1416389324104512
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=255 blocks=1 obj#=76505 tim=1416389324104544
WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=537 blocks=1 obj#=76505 tim=1416389324104584
WAIT #10: nam='db file sequential read' ela= 9 file#=4 block#=540 blocks=1 obj#=76505 tim=1416389324104759
WAIT #10: nam='db file sequential read' ela= 7 file#=4 block#=542 blocks=1 obj#=76505 tim=1416389324104802
WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=544 blocks=1 obj#=76505 tim=1416389324104845
WAIT #10: nam='db file sequential read' ela= 76 file#=4 block#=546 blocks=1 obj#=76505 tim=1416389324105604
WAIT #10: nam='db file sequential read' ela= 7 file#=4 block#=548 blocks=1 obj#=76505 tim=1416389324105805
WAIT #10: nam='db file sequential read' ela= 6 file#=4 block#=550 blocks=1 obj#=76505 tim=1416389324105834
......

     以上向我们展示了Oracle多个数据块读取的工作机制，当内存中已经有了某个数据块时，Oracle将不再从磁盘中读取它。这里使用一个循环来通过索引块访问的方式（每次读取一个数据块），将间隔的数据块读入到内存中。这样，当我们对T3表执行全表扫描时，尽管设置了参数：

16:22:19 SCOTT@ prod >alter session set db_file_multiblock_read_count=64;

     但是由于没有连续的数据块可以读取了，所以Oracle每次也只能将一个数据块读取到内存。在等待事件中每一个WAIT#中 blocks=1说明每次I/O读取的数据块都为1，而且数据块的序号正好间隔为1，说明她们之间的那个数据块已经读取到内存中了。因为需要读取的数据块不再连续，所以此时不能一次读取多个数据块。

    多数据块读取一般发生在：

      FTS(FULL TABLE SCAN)

      INDEX_FFS(INDEX FAST FULL SCAN)