/***************************************************************************************/
同时运行四个session,每个session读取40000记录,这样运行更长时间来做awr的snapshot:
同时运行四个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
#!/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
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
---------- ---------------------------------------------------------------- ------- ---------- ----------
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
---------- ---------------------------------------------------------------- ------- ---------- ----------
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)
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
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
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
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
%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
-> 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
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
-> 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
-------------------------------------------------------------
------------------------------
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
-> 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
------------------------ ----------------------------------------------------
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 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
------------------------------ ------- ----------------------------- ----------
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
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
--------------- ---------- ------------------------------ ----------
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
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
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
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
-------------------------------------------------------------
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/