db_file_multiblock_read_count那些事(一)

  测试学习下参数的作用与原理db_file_multiblock_read_count；
经查官方手册：
  1，此参数与操作系统有关
  2，即每次io读取的数据块个数
  3，如增大此参数，io次数会减少
  4，此参数用于表扫描，
  5，此参数与cbo选择表的访问路径有关，即如何选择全表扫描或索引扫描
 
--查询测试表所属的文件号
22:42:46 SQL> select dbms_rowid.rowid_to_absolute_fno(rowid,'SCOTT','T_STORAGE') from t_storage where rownum=1;
 
DBMS_ROWID.ROWID_TO_ABSOLUTE_F
------------------------------
                            10 
                           
--如下视图可知文件相关的io信息如何
22:40:02 SQL> desc v$iostat_file;
Name                    Type         Nullable Default Comments
----------------------- ------------ -------- ------- --------
FILE_NO                 NUMBER       Y                        
FILETYPE_ID             NUMBER       Y   --文件类型：数据文件或是临时文件还是归档文件                     
FILETYPE_NAME           VARCHAR2(28) Y                        
SMALL_READ_MEGABYTES    NUMBER       Y   --单块读产生的多少m字节的数据                     
SMALL_WRITE_MEGABYTES   NUMBER       Y   --单块写产生的多少m字节的数据                   
LARGE_READ_MEGABYTES    NUMBER       Y   --多块读产生的多少m字节数据                     
LARGE_WRITE_MEGABYTES   NUMBER       Y   --多块写产生的多少m字节数据                     
SMALL_READ_REQS         NUMBER       Y   --单块读的次数                    
SMALL_WRITE_REQS        NUMBER       Y   --单块写的次数                   
SMALL_SYNC_READ_REQS    NUMBER       Y   --同步单块读的次数                    
LARGE_READ_REQS         NUMBER       Y   --多块读的次数                  
LARGE_WRITE_REQS        NUMBER       Y   --多块写的次数                    
SMALL_READ_SERVICETIME  NUMBER       Y   --单块读总共花费的时间以毫秒计                   
SMALL_WRITE_SERVICETIME NUMBER       Y   --单块写总共花费的时间以毫秒计                     
SMALL_SYNC_READ_LATENCY NUMBER       Y   --单块同步读的延迟以毫秒计                     
LARGE_READ_SERVICETIME  NUMBER       Y   -- 多块读总共花费的时间以毫秒计                   
LARGE_WRITE_SERVICETIME NUMBER       Y    --多块写总共花费的时间以毫秒计                    
ASYNCH_IO               VARCHAR2(9)  Y    --是否开启异步io                   
ACCESS_METHOD           VARCHAR2(11) Y    --访问文件的io library;其值可为：OS_LIB ODM_LIB ASM_MANAGED DNFS_LIB                    
RETRIES_ON_ERROR        NUMBER       Y    --发生问题产生读重启的次数         

--未变更参数前视图的值
23:01:15 SQL> select * from v$iostat_file where file_no=10;
 
   FILE_NO FILETYPE_ID FILETYPE_NAME                SMALL_READ_MEGABYTES SMALL_WRITE_MEGABYTES LARGE_READ_MEGABYTES LARGE_WRITE_MEGABYTES SMALL_READ_REQS SMALL_WRITE_REQS SMALL_SYNC_READ_REQS LARGE_READ_REQS LARGE_WRITE_REQS SMALL_READ_SERVICETIME SMALL_WRITE_SERVICETIME SMALL_SYNC_READ_LATENCY LARGE_READ_SERVICETIME LARGE_WRITE_SERVICETIME ASYNCH_IO ACCESS_METHOD RETRIES_ON_ERROR
---------- ----------- ---------------------------- -------------------- --------------------- -------------------- --------------------- --------------- ---------------- -------------------- --------------- ---------------- ---------------------- ----------------------- ----------------------- ---------------------- ----------------------- --------- ------------- ----------------
        10           2 Data File                                       1                   171                    0                   101             141            18479                  141               0              811                   6833               102116488                    6833                      0                   63410 ASYNC_ON  OS_LIB                       0
 
Executed in 0.031 seconds    

--扫描表，发现io未变化啊
23:02:31 SQL> select count(a) from t_storage;
 
  COUNT(A)
----------
   1000000
 
Executed in 0.047 seconds
 
23:02:47 SQL> select * from v$iostat_file where file_no=10;
 
   FILE_NO FILETYPE_ID FILETYPE_NAME                SMALL_READ_MEGABYTES SMALL_WRITE_MEGABYTES LARGE_READ_MEGABYTES LARGE_WRITE_MEGABYTES SMALL_READ_REQS SMALL_WRITE_REQS SMALL_SYNC_READ_REQS LARGE_READ_REQS LARGE_WRITE_REQS SMALL_READ_SERVICETIME SMALL_WRITE_SERVICETIME SMALL_SYNC_READ_LATENCY LARGE_READ_SERVICETIME LARGE_WRITE_SERVICETIME ASYNCH_IO ACCESS_METHOD RETRIES_ON_ERROR
---------- ----------- ---------------------------- -------------------- --------------------- -------------------- --------------------- --------------- ---------------- -------------------- --------------- ---------------- ---------------------- ----------------------- ----------------------- ---------------------- ----------------------- --------- ------------- ----------------
        10           2 Data File                                       1                   171                    0                   101             141            18479                  141               0              811                   6833               102116488                    6833                      0                   63410 ASYNC_ON  OS_LIB                       0
 
Executed in 0.047 seconds
          
--清空数据缓冲池
23:03:59 SQL> alter system flush buffer_cache;
 
System altered
 
Executed in 3.978 seconds         

--这下有变化了io
23:04:32 SQL> select count(a) from t_storage;
 
  COUNT(A)
----------
   1000000
 
Executed in 0.421 seconds
 
23:04:56 SQL> select * from v$iostat_file where file_no=10;
 
   FILE_NO FILETYPE_ID FILETYPE_NAME                SMALL_READ_MEGABYTES SMALL_WRITE_MEGABYTES LARGE_READ_MEGABYTES LARGE_WRITE_MEGABYTES SMALL_READ_REQS SMALL_WRITE_REQS SMALL_SYNC_READ_REQS LARGE_READ_REQS LARGE_WRITE_REQS SMALL_READ_SERVICETIME SMALL_WRITE_SERVICETIME SMALL_SYNC_READ_LATENCY LARGE_READ_SERVICETIME LARGE_WRITE_SERVICETIME ASYNCH_IO ACCESS_METHOD RETRIES_ON_ERROR
---------- ----------- ---------------------------- -------------------- --------------------- -------------------- --------------------- --------------- ---------------- -------------------- --------------- ---------------- ---------------------- ----------------------- ----------------------- ---------------------- ----------------------- --------- ------------- ----------------
        10           2 Data File                                       3                   180                   10                   105             172            19108                  144              42              836                   6973               103190058                    6865                    187                   63969 ASYNC_ON  OS_LIB                       0
 
Executed in 0.062 seconds

23:05:03 SQL> select bytes/1024/1024 mb from user_segments where segment_name='T_STORAGE';
 
        MB
----------
        12
 
Executed in 0.609 seconds

--发现上述的单块读及多块读共计＝(3-1)+(10-0)=12mb,刚好与测试表大小相符，即读表刚好读取了此表；
  且单块读很少，多块读很多；我猜测每个数据块仅块头要单块读，而其它块可以多块读；
 
  而且单块写与多块写也与上述差不多；
 
  发生单块读共计31次；
  发生单块写共计629次
 
  发生多块读共计42次
  发生多块写共计25次
 
  发生单块读共用时140毫秒
  发生单块写共用时1073570毫秒

  发生多块读共用时187毫秒
  发生多块写共用时589毫秒

--上述测试结果为此参数为30时的表现 
23:22:52 SQL> show parameter db_file_multiblock_read_count
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     30 
 
 
--我猜测加大此参数后，io次数会减少；至少多次读的次数会明显减少；
23:22:58 SQL> alter system set db_file_multiblock_read_count=150;
 
System altered
 
Executed in 0.047 seconds 
 
23:24:55 SQL> select count(a) from t_storage;
 
  COUNT(A)
----------
   1000000
 
Executed in 0.047 seconds

23:25:28 SQL> alter system checkpoint;
 
System altered
 
23:31:02 SQL> select * from v$iostat_file where file_no=10;
 
   FILE_NO FILETYPE_ID FILETYPE_NAME                SMALL_READ_MEGABYTES SMALL_WRITE_MEGABYTES LARGE_READ_MEGABYTES LARGE_WRITE_MEGABYTES SMALL_READ_REQS SMALL_WRITE_REQS SMALL_SYNC_READ_REQS LARGE_READ_REQS LARGE_WRITE_REQS SMALL_READ_SERVICETIME SMALL_WRITE_SERVICETIME SMALL_SYNC_READ_LATENCY LARGE_READ_SERVICETIME LARGE_WRITE_SERVICETIME ASYNCH_IO ACCESS_METHOD RETRIES_ON_ERROR
---------- ----------- ---------------------------- -------------------- --------------------- -------------------- --------------------- --------------- ---------------- -------------------- --------------- ---------------- ---------------------- ----------------------- ----------------------- ---------------------- ----------------------- --------- ------------- ----------------
        10           2 Data File                                       4                   180                   20                   105             193            19109                  148              53              836                   7270               103190058                    7053                    982                   63969 ASYNC_ON  OS_LIB                       0
 
Executed in 0.062 seconds

结论：单块读产生的数据与上述差不多
      单块读次数20
     
      多块读次数11，与之前的42，明显减少好多；
     
      单块读用时170多毫秒
     
      多块读用时795毫秒 ，而之前为187毫秒，明显用时更多了
     
小结：加大此参数后，io次数明显变少了
      当然触发io还有其它的因素；     

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-755077/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-755077/