single block read (二)

/***************************************************************************************/
同时运行四个session，每个session读取40000记录，这样运行更长时间来做awr的snapshot：

修改iotest.sh文件
#!/bin/bash
n=1
while (( $n <= 4 ))
do
        sqlplus " soe/soe@dbs101" @iotest.sql $n 40000 &
        n=$(( n+1 ))
done

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          0.00   4.61 95.19  1.60 1526.25   43.69   763.13    21.84    16.22     0.85    8.78   6.27  60.64
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   4.61  0.00  0.40    0.00   40.08     0.00    20.04   100.00     0.00   12.00  12.00   0.48
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         0.00   0.00 95.19  1.00 1526.25    2.00   763.13     1.00    15.89     0.84    8.76   6.26  60.24
sda6         0.00   0.00  0.00  0.20    0.00    1.60     0.00     0.80     8.00     0.00   10.00  10.00   0.20
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          0.00   3.81 100.40  2.00 1606.41   51.30   803.21    25.65    16.19     1.21   11.86   7.51  76.89
sdb1         0.00   0.00 100.40  0.40 1606.41    8.02   803.21     4.01    16.02     1.19   11.85   7.55  76.15
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   3.81  0.00  1.60    0.00   43.29     0.00    21.64    27.00     0.02   12.62  12.62   2.02
sdc          0.00   1.40 89.78  2.00 1430.06   33.67   715.03    16.83    15.95     0.99   10.78   7.21  66.17
sdc1         0.00   0.00 89.78  0.40 1430.06   12.83   715.03     6.41    16.00     0.95   10.55   7.34  66.15
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   1.40  0.00  1.60    0.00   20.84     0.00    10.42    13.00     0.04   23.88  11.13   1.78
sdd          0.00   0.00 100.80  1.60 1622.44   21.24   811.22    10.62    16.05     1.00    9.61   6.20  63.47
sdd1         0.00   0.00 100.80  1.60 1622.44   21.24   811.22    10.62    16.05     1.00    9.61   6.20  63.47
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

利用率是60~66%，响应时间6~12ms。IOPS每个磁盘是95左右。

SQL> select * from v$sysstat where name like '%physical%read%' and statistic# = 46;

STATISTIC# NAME                                                               CLASS      VALUE    STAT_ID
---------- ---------------------------------------------------------------- ------- ---------- ----------
        46 physical read total IO requests                                        8    4615270 3343375620

SQL> select * from v$sysstat where name like '%physical%read%' and statistic# = 46;

STATISTIC# NAME                                                               CLASS      VALUE    STAT_ID
---------- ---------------------------------------------------------------- ------- ---------- ----------
        46 physical read total IO requests                                        8    8607375 3343375620

8,607,375 - 4,615,270 = 3,992,105

运行时间为2h56m50s左右，

3,992,105 / ( (2h * 60 + 56) m * 60 s + 50 s) = 3,992,105 / (10,610s) = 376 iops。这和iostat的结果接近(95*4 = 380)。

ARW report的数据：

采样时间1 hour
              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:       579 12-Jul-11 23:00:35        34       1.6
  End Snap:       580 13-Jul-11 00:00:43        34       1.4
   Elapsed:               60.14 (mins)
   DB Time:              240.82 (mins)

Load Profile              Per Second    Per Transaction   Per Exec   Per Call
~~~~~~~~~~~~         ---------------    --------------- ---------- ----------
      DB Time(s):                4.0              437.9       0.01      32.11
       DB CPU(s):                0.1                7.5       0.00       0.55
       Redo size:              542.3           59,297.6
   Logical reads:            1,197.9          130,985.5
   Block changes:                1.6              173.6
  Physical reads:              298.4           32,629.3               ------平均每秒298.4
 Physical writes:                0.2               26.2
      User calls:                0.1               13.6
          Parses:                0.5               53.3
     Hard parses:                0.0                2.7
W/A MB processed:                0.0                1.1
          Logons:                0.0                4.6
        Executes:              298.5           32,644.6

                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
db file sequential read           1,075,613      14,232     13   98.5 User I/O  
----读了4,000,000，等待了1,075,613，平均13ms
DB CPU                                              246           1.7
log file sync                             8           0     19     .0 Commit
Disk file operations I/O                 13           0      0     .0 User I/O
cursor: mutex S                           2           0      0     .0 Concurrenc

                                                                  Avg
                                      %Time       Total Wait     wait
Wait Class                      Waits -outs         Time (s)     (ms)  %DB time
-------------------- ---------------- ----- ---------------- -------- ---------
User I/O                    1,075,626     0           14,232       13      98.5
DB CPU                                                   246                1.7

SQL ordered by Elapsed Time            DB/Inst: dbs101/dbs101  Snaps: 579-580
-> Resources reported for PL/SQL code includes the resources used by all SQL
   statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
   into the Total Database Time multiplied by 100
-> %Total - Elapsed Time  as a percentage of Total DB time
-> %CPU   - CPU Time      as a percentage of Elapsed Time
-> %IO    - User I/O Time as a percentage of Elapsed Time
-> Captured SQL account for   99.4% of Total DB Time (s):          14,449
-> Captured PL/SQL account for   99.9% of Total DB Time (s):          14,449

        Elapsed                  Elapsed Time
        Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
        14,356.3      1,074,114          0.01   99.4    1.2   99.1 b986t72dmt42q

Tablespace IO Stats                    DB/Inst: dbs101/dbs101  Snaps: 579-580
-> ordered by IOs (Reads + Writes) desc

Tablespace
------------------------------
                 Av       Av     Av                       Av     Buffer  Av Buf
         Reads Reads/s  Rd(ms) Blks/Rd       Writes Writes/s      Waits  Wt(ms)
-------------- ------- ------- ------- ------------ -------- ---------- -------
SOEDATA
     1,075,635     298    13.2     1.0            0        0          0     0.0
SYSAUX
           557       0    12.5     1.1          381        0          0     0.0
UNDOTBS1
           515       0     9.2     1.0          106        0          0     0.0
SYSTEM
            40       0    14.0     1.1           46        0          0     0.0
TEMP
             6       0    16.7     1.0            0        0          0     0.0
          -------------------------------------------------------------

File IO Stats                          DB/Inst: dbs101/dbs101  Snaps: 579-580
-> ordered by Tablespace, File

Tablespace               Filename
------------------------ ----------------------------------------------------
                 Av       Av     Av                       Av     Buffer  Av Buf
         Reads Reads/s  Rd(ms) Blks/Rd       Writes Writes/s      Waits  Wt(ms)
-------------- ------- ------- ------- ------------ -------- ---------- -------
SOEDATA                  +DATA/dbs101/datafile/soedata.294.751909089
     1,075,635     298    13.2     1.0            0        0          0     0.0

ASH report的数据：

Top User Events                DB/Inst: dbs101/dbs101  (Jul 12 23:00 to 00:00)

                                                               Avg Active
Event                               Event Class        % Event   Sessions
----------------------------------- --------------- ---------- ----------
db file sequential read             User I/O             97.82       3.93
CPU + Wait for CPU                  CPU                   1.47       0.06
          -------------------------------------------------------------
Top Event P1/P2/P3 Values      DB/Inst: dbs101/dbs101  (Jul 12 23:00 to 00:00)

Event                          % Event  P1 Value, P2 Value, P3 Value % Activity
------------------------------ ------- ----------------------------- ----------
Parameter 1                Parameter 2                Parameter 3
-------------------------- -------------------------- --------------------------
db file sequential read          97.89                "3","2344","1"       0.01
file#                      block#                     blocks

Top SQL with Top Events       DB/Inst: dbs101/dbs101  (Jul 12 23:00 to 00:00)

                                                        Sampled #
                 SQL ID             Planhash        of Executions     % Activity
----------------------- -------------------- -------------------- --------------
Event                          % Event Top Row Source                    % RwSrc
------------------------------ ------- --------------------------------- -------
          b986t72dmt42q           1408842701                14313          99.03
db file sequential read          97.82 TABLE ACCESS - BY INDEX ROWID       97.71
SELECT NAME FROM TEST WHERE TID = :B1

Top Sessions                  DB/Inst: dbs101/dbs101  (Jul 12 23:00 to 00:00)
-> '# Samples Active' shows the number of ASH samples in which the session
      was found waiting for that particular event. The percentage shown
      in this column is calculated with respect to wall clock time
      and not total database activity.
-> 'XIDs' shows the number of distinct transaction IDs sampled in ASH
      when the session was waiting for that particular event
-> For sessions running Parallel Queries, this section will NOT aggregate
      the PQ slave activity into the session issuing the PQ. Refer to
      the 'Top Sessions running PQs' section for such statistics.

   Sid, Serial# % Activity Event                             % Event
--------------- ---------- ------------------------------ ----------
User                 Program                          # Samples Active     XIDs
-------------------- ------------------------------ ------------------ --------
        6,   15      24.81 db file sequential read             24.45
SOE                  sqlplus@test.ac...m (TNS V1-V3) 3,540/3,600 [ 98%]        0

      964,  105      24.81 db file sequential read             24.43
SOE                  sqlplus@test.ac...m (TNS V1-V3) 3,537/3,600 [ 98%]        0

     1151,  233      24.81 db file sequential read             24.47
SOE                  sqlplus@test.ac...m (TNS V1-V3) 3,543/3,600 [ 98%]        0

     1345,   11      24.81 db file sequential read             24.42
SOE                  sqlplus@test.ac...m (TNS V1-V3) 3,535/3,600 [ 98%]        0

Activity Over Time            DB/Inst: dbs101/dbs101  (Jul 12 23:00 to 00:00)
-> Analysis period is divided into smaller time slots
-> Top 3 events are reported in each of those slots
-> 'Slot Count' shows the number of ASH samples in that slot
-> 'Event Count' shows the number of ASH samples waiting for
   that event in that slot
-> '% Event' is 'Event Count' over all ASH samples in the analysis period

                         Slot                                   Event
Slot Time (Duration)    Count Event                             Count % Event
-------------------- -------- ------------------------------ -------- -------
23:00:00    (0 secs)        6 db file sequential read               5    0.03
                              read by other session                 1    0.01
23:00:00   (6.0 min)    1,456 db file sequential read           1,425    9.84
                              CPU + Wait for CPU                   23    0.16
                              control file sequential read          4    0.03
23:06:00   (6.0 min)    1,444 db file sequential read           1,406    9.71
                              CPU + Wait for CPU                   32    0.22
                              control file sequential read          3    0.02
23:12:00   (6.0 min)    1,453 db file sequential read           1,431    9.89
                              CPU + Wait for CPU                   16    0.11
                              control file parallel write           5    0.03
23:18:00   (6.0 min)    1,442 db file sequential read           1,416    9.78
                              CPU + Wait for CPU                   19    0.13
                              control file parallel write           5    0.03
23:24:00   (6.0 min)    1,443 db file sequential read           1,415    9.77
                              CPU + Wait for CPU                   23    0.16
                              control file parallel write           3    0.02
23:30:00   (6.0 min)    1,451 db file sequential read           1,419    9.80
                              CPU + Wait for CPU                   26    0.18
                              control file parallel write           3    0.02
23:36:00   (6.0 min)    1,445 db file sequential read           1,401    9.68
                              CPU + Wait for CPU                   38    0.26
                              control file sequential read          3    0.02
23:42:00   (6.0 min)    1,445 db file sequential read           1,414    9.77
                              CPU + Wait for CPU                   25    0.17
                              control file sequential read          4    0.03
23:48:00   (6.0 min)    1,449 db file sequential read           1,427    9.86
                              CPU + Wait for CPU                   16    0.11
                              control file parallel write           4    0.03
23:54:00   (6.0 min)    1,442 db file sequential read           1,412    9.75
                              CPU + Wait for CPU                   23    0.16
                              control file sequential read          3    0.02
          -------------------------------------------------------------

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

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