multiple blocks read

multiple blocks read:系统执行并行查询，大表全表扫描，备份恢复时进行multiple
blocks reads。一般DSS，数据仓库中发出的大多是multiple blocks reads。
统计信息physical read total multi block requests记录的multiple block reads的次数。
环境：
SQL> select * from v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
 
SQL>
mbpsiotest.sql脚本文件：
set timing on
set echo on
alter session set events '10046 trace name context forever, level 12';
select /*+ NO_MERGE(t1) */ count(*) from
   (select /*+ FULL(s) PARALLEL (s,4) */ * from test s) t1
;
select 1 from dual;
这个脚本全表扫描4,000,000记录，每个记录占用一个块。这样全表扫描4,000,000个
块。

SQL> select * from user_segments where segment_name = 'TEST';
 
SEGMENT_NAME     SEGMENT_TYPE       SEGMENT_SUBTYPE TABLESPACE_NAME                     BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS   MAX_SIZE RETENTION MINRETENTION PCT_INCREASE  FREELISTS FREELIST_GROUPS BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE
---------------- ------------------ --------------- ------------------------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ---------- --------- ------------ ------------ ---------- --------------- ----------- ----------- ----------------
TEST             TABLE              ASSM            SOEDATA                        3280470016    4004480      31285        1048576     1048576           1  2147483645 2147483645                                   0                            DEFAULT     DEFAULT     DEFAULT
 
SQL>
表test有4,004,480个块。
iostat的数据结果：
avg-cpu:  %user   %nice    %sys %iowait   %idle
           2.99    0.00   10.67   15.27   71.06
Device:    rrqm/s wrqm/s   r/s   w/s  rsec/s  wsec/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
sda         31.93   0.20 31.53  0.80 32668.27    8.03 16334.14     4.02  1010.73     1.75   53.76  25.29  81.75
sda1         0.00   0.00  0.00  0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2         0.00   0.20  0.00  0.40    0.00    4.82     0.00     2.41    12.00     0.01   26.50  26.50   1.06
sda3         0.00   0.00  0.00  0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda4         0.00   0.00  0.00  0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda5        32.13   0.00 31.53  0.00 32771.08    0.00 16385.54     0.00  1039.49     1.72   54.23  25.61  80.74
sda6         0.00   0.00  0.00  0.40    0.00    3.21     0.00     1.61     8.00     0.02   44.50  44.50   1.79
sda7         0.00   0.00  0.00  0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdb         31.53   2.01 31.93  3.82 32302.01   44.18 16151.00    22.09   904.97     3.12   86.66  22.96  82.07
sdb1        31.53   0.00 31.93  2.81 32302.01   20.08 16151.00    10.04   930.43     2.99   85.39  23.46  81.49
sdb2         0.00   0.00  0.00  0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdb3         0.00   2.01  0.00  1.00    0.00   24.10     0.00    12.05    24.00     0.13  130.60 130.60  13.11
sdc         31.53   3.01 33.33  9.84 32327.71   81.93 16163.86    40.96   750.70     7.83  299.70  21.97  94.84
sdc1        31.53   0.00 33.33  0.40 32327.71   12.85 16163.86     6.43   958.67     3.84  115.58  27.73  93.55
sdc2         0.00   0.00  0.00  0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdc3         0.00   3.01  0.00  9.44    0.00   69.08     0.00    34.54     7.32     3.99  957.83  54.60  51.53
sdd         31.53   0.00 32.13  0.80 32308.43   25.70 16154.22    12.85   981.85     1.59   47.98  25.00  82.33
sdd1        31.53   0.00 32.13  0.80 32308.43   25.70 16154.22    12.85   981.85     1.59   47.98  25.00  82.33
sdd2         0.00   0.00  0.00  0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdd3         0.00   0.00  0.00  0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdd4         0.00   0.00  0.00  0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
从iostat的数据看，每个磁盘每秒可读16m。四个asm磁盘每秒可读64m
SQL> select * from v$sysstat where name like '%physical%read%' and statistic# in (46,47,50);
 
STATISTIC# NAME                                                                  CLASS      VALUE    STAT_ID
---------- ---------------------------------------------------------------- ---------- ---------- ----------
        46 physical read total IO requests                                           8       7075 3343375620
        47 physical read total multi block requests                                  8         62 2007302071
        50 physical read total bytes                                                 8  110039552 2572010804
 
SQL> select * from v$sysstat where name like '%physical%read%' and statistic# in (46,47,50);
 
STATISTIC# NAME                                                                  CLASS      VALUE    STAT_ID
---------- ---------------------------------------------------------------- ---------- ---------- ----------
        46 physical read total IO requests                                           8      71242 3343375620
        47 physical read total multi block requests                                  8      62657 2007302071
        50 physical read total bytes                                                 8 3290101811 2572010804
(62,657 - 62) * 64 = 4,006,080 blocks
运行时间是8m3s：
(4,006,080 * 8k)/1024 = 31,297.5m
31,297.5m / (8m * 60s + 3s) = 31,297.5m / 483s = 64.789m/s 这和iosta的输出结果接近。
下面是10046的trace文件：
more dbs101_p000_3781.trc

PARSING IN CURSOR #182925470344 len=100 dep=1 uid=91 ct=3 lid=91 tim=1310787297282899 hv=1540677790 ad='cee6f018' sqlid='9
yxs865dx9s4y'
select /*+ NO_MERGE(t1) */ count(*) from
   (select /*+ FULL(s) PARALLEL (s,4) */ * from test s) t1
END OF STMT
PARSE #182925470344:c=999,e=666,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1005399955,tim=1310787297282895
WAIT #182925470344: nam='PX Deq: Execution Msg' ela= 4138 sleeptime/senderid=268566527 passes=1 p3=3514998296 obj#=-1 tim=1
310787297287554
WAIT #182925470344: nam='Disk file operations I/O' ela= 643 FileOperation=2 fileno=5 filetype=2 obj#=-1 tim=131078729728854
7
WAIT #182925470344: nam='direct path read' ela= 46666 file number=5 first dba=2464623 block cnt=17 obj#=78253 tim=131078729
7340525
WAIT #182925470344: nam='direct path read' ela= 44021 file number=5 first dba=2464704 block cnt=64 obj#=78253 tim=131078729
7385952
WAIT #182925470344: nam='direct path read' ela= 12454 file number=5 first dba=2464769 block cnt=63 obj#=78253 tim=131078729
7398855
WAIT #182925470344: nam='direct path read' ela= 42348 file number=5 first dba=2464832 block cnt=64 obj#=78253 tim=131078729
7441629
WAIT #182925470344: nam='direct path read' ela= 1283 file number=5 first dba=2464896 block cnt=64 obj#=78253 tim=1310787297
443357
WAIT #182925470344: nam='direct path read' ela= 59024 file number=5 first dba=2464960 block cnt=64 obj#=78253 tim=131078729
7502797
WAIT #182925470344: nam='direct path read' ela= 32570 file number=5 first dba=2465024 block cnt=64 obj#=78253 tim=131078729
7535815
WAIT #182925470344: nam='direct path read' ela= 27589 file number=5 first dba=2465088 block cnt=64 obj#=78253 tim=131078729

more dbs101_p003_3787.trc:
PARSING IN CURSOR #182925470344 len=100 dep=1 uid=91 ct=3 lid=91 tim=1310787297278707 hv=1540677790 ad='cee6f018' sqlid='9
yxs865dx9s4y'
select /*+ NO_MERGE(t1) */ count(*) from
   (select /*+ FULL(s) PARALLEL (s,4) */ * from test s) t1
END OF STMT
PARSE #182925470344:c=0,e=334,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1005399955,tim=1310787297278703
WAIT #182925470344: nam='PX Deq: Execution Msg' ela= 8408 sleeptime/senderid=268566527 passes=1 p3=3514996936 obj#=-1 tim=1
310787297287608
WAIT #182925470344: nam='Disk file operations I/O' ela= 1185 FileOperation=2 fileno=5 filetype=2 obj#=-1 tim=13107872972894
15
WAIT #182925470344: nam='direct path read' ela= 38387 file number=5 first dba=3542629 block cnt=27 obj#=78253 tim=131078729
7338357
WAIT #182925470344: nam='direct path read' ela= 813 file number=5 first dba=3542656 block cnt=64 obj#=78253 tim=13107872973
40150
WAIT #182925470344: nam='direct path read' ela= 31497 file number=5 first dba=3542720 block cnt=64 obj#=78253 tim=131078729
7372134
WAIT #182925470344: nam='direct path read' ela= 55923 file number=5 first dba=3542784 block cnt=64 obj#=78253 tim=131078729
7428509
WAIT #182925470344: nam='direct path read' ela= 44135 file number=5 first dba=3542848 block cnt=64 obj#=78253 tim=131078729
747311
从trace文件中看到每次读的块数是64。

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

转载于:http://blog.itpub.net/25105315/viewspace-702371/