/*
|| Oracle 11gR2: I/O Performance Tuning Series, Listing 2
||
|| Contains code samples demonstrating how to use:
|| -- Linux operating system command-line utilities
|| -- Oracle 11gR2 ASMCMD utilities
|| -- Oracle 11gR2 SQL*Plus queries
|| as effective tools for I/O performance tuning.
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various Oracle 11gR2 I/O performance
|| tuning concepts and features, and they should be carefully proofread before
|| being executed against any existing Oracle database to avoid potential damage!
*/
/*
|| Listing 2.1.1:
|| Using iostat to monitor I/O performance
*/
>>> Using iostat:
[oracle@11gR2Base ~]$ iostat
Linux 2.6.18-92.el5 (11gR2Base) 07/11/2010
avg-cpu: %user %nice %system %iowait %steal %idle
0.64 0.01 1.03 0.36 0.00 97.95
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 1.34 7.85 24.94 2342647 7442335
sdb 1.41 2.99 22.33 892399 6664240
sdc 0.01 0.08 0.00 24307 8
sdd 0.00 0.01 0.01 2125 2920
sde 5.26 51.69 76.94 15426812 22961680
sdf 0.01 0.02 0.00 6224 0
sdg 0.00 0.00 0.00 1225 0
sdh 0.00 0.01 0.00 2482 8
sdi 0.01 0.02 2.49 6504 742216
sdj 0.00 0.02 0.00 6382 0
dm-0 0.31 0.00 2.49 690 742208
sdk 0.00 0.00 0.00 302 60
[oracle@11gR2Base ~]$ iostat -x
Linux 2.6.18-92.el5 (11gR2Base) 07/11/2010
avg-cpu: %user %nice %system %iowait %steal %idle
0.64 0.01 1.03 0.36 0.00 97.95
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.09 2.15 0.38 0.96 7.85 24.94 24.38 0.01 5.22 1.89 0.25
sdb 0.01 1.45 0.07 1.35 2.99 22.33 17.90 0.00 1.57 0.71 0.10
sdc 0.00 0.00 0.01 0.00 0.08 0.00 7.12 0.00 12.39 12.38 0.01
sdd 0.00 0.00 0.00 0.00 0.01 0.01 6.81 0.00 3.65 3.57 0.00
sde 0.01 0.00 1.53 3.73 51.70 76.94 24.43 0.01 1.06 0.41 0.21
sdf 0.00 0.00 0.01 0.00 0.02 0.00 2.42 0.00 0.17 0.14 0.00
sdg 0.00 0.00 0.00 0.00 0.00 0.00 9.07 0.00 0.17 0.17 0.00
sdh 0.00 0.00 0.00 0.00 0.01 0.00 5.63 0.00 0.67 0.66 0.00
sdi 0.00 0.31 0.01 0.00 0.02 2.49 215.15 0.00 102.96 1.88 0.00
sdj 0.00 0.00 0.00 0.00 0.02 0.00 6.45 0.00 0.10 0.10 0.00
dm-0 0.00 0.00 0.00 0.31 0.00 2.49 8.00 0.14 459.21 0.07 0.00
sdk 0.00 0.00 0.00 0.00 0.00 0.00 8.23 0.00 5.48 3.16 0.00
/*
|| Listing 2.1.2:
|| Using vmstat to monitor I/O performance
*/
[oracle@11gR2Base ~]$ vmstat -d
disk- ------------reads------------ ------------writes----------- -----IO------
total merged sectors ms total merged sectors ms cur sec
ram0 0 0 0 0 0 0 0 0 0 0
ram1 0 0 0 0 0 0 0 0 0 0
ram2 0 0 0 0 0 0 0 0 0 0
ram3 0 0 0 0 0 0 0 0 0 0
ram4 0 0 0 0 0 0 0 0 0 0
ram5 0 0 0 0 0 0 0 0 0 0
ram6 0 0 0 0 0 0 0 0 0 0
ram7 0 0 0 0 0 0 0 0 0 0
ram8 0 0 0 0 0 0 0 0 0 0
ram9 0 0 0 0 0 0 0 0 0 0
ram10 0 0 0 0 0 0 0 0 0 0
ram11 0 0 0 0 0 0 0 0 0 0
ram12 0 0 0 0 0 0 0 0 0 0
ram13 0 0 0 0 0 0 0 0 0 0
ram14 0 0 0 0 0 0 0 0 0 0
ram15 0 0 0 0 0 0 0 0 0 0
sda 114051 25993 2343039 918971 287839 644183 7457471 1178376 0 760
sdb 20725 2863 892399 238407 401987 432178 6673320 424851 0 298
sdc 3413 170 24307 42291 1 0 8 0 0 42
sdd 469 339 2125 373 272 93 2920 2331 0 2
sde 458324 3230 15445916 604581 1114802 9 22989475 1068785 0 637
sdf 2567 604 6224 446 0 0 0 0 0 0
sdg 135 19 1225 23 0 0 0 0 0 0
sdh 441 101 2482 298 1 0 8 0 0 0
sdi 2497 873 6504 982 983 91794 742216 357317 0 6
sdj 990 232 6382 98 0 0 0 0 0 0
hdc 0 0 0 0 0 0 0 0 0 0
fd0 0 0 0 0 0 0 0 0 0 0
md0 0 0 0 0 0 0 0 0 0 0
loop0 0 0 0 0 0 0 0 0 0 0
loop1 0 0 0 0 0 0 0 0 0 0
loop2 0 0 0 0 0 0 0 0 0 0
loop3 0 0 0 0 0 0 0 0 0 0
loop4 0 0 0 0 0 0 0 0 0 0
loop5 0 0 0 0 0 0 0 0 0 0
loop6 0 0 0 0 0 0 0 0 0 0
loop7 0 0 0 0 0 0 0 0 0 0
loop8 0 0 0 0 0 0 0 0 0 0
loop9 0 0 0 0 0 0 0 0 0 0
loop10 0 0 0 0 0 0 0 0 0 0
loop11 0 0 0 0 0 0 0 0 0 0
loop12 0 0 0 0 0 0 0 0 0 0
loop13 0 0 0 0 0 0 0 0 0 0
loop14 0 0 0 0 0 0 0 0 0 0
loop15 0 0 0 0 0 0 0 0 0 0
dm-0 87 0 690 632 92776 0 742208 42643381 0 6
asm/.asm_ctl_sp 0 0 0 0 0 0 129905 0 0 0
asm/.asm_ctl_vm 0 0 129841 0 0 0 0 0 0 0
asm/.asm_ctl_vd 0 0 0 0 0 0 129761 0 0 0
asm/.asm_ctl_vb 0 0 129697 0 0 0 0 0 0 0
asm/.asm_ctl_vb 0 0 0 0 0 0 129617 0 0 0
asm/.asm_ctl_vb 0 0 129553 0 0 0 0 0 0 0
asm/.asm_ctl_vb 0 0 0 0 0 0 129473 0 0 0
asm/.asm_ctl_vb 0 0 129409 0 0 0 0 0 0 0
asm/.asm_ctl_vb 0 0 0 0 0 0 129329 0 0 0
asm/.asm_ctl_vb 0 0 129265 0 0 0 0 0 0 0
asm/.asm_ctl_vb 0 0 0 0 0 0 129185 0 0 0
asm/.asm_ctl_vb 0 0 129121 0 0 0 0 0 0 0
asm/.asm_ctl_vb 0 0 0 0 0 0 129041 0 0 0
ofsctl 0 0 0 0 0 0 0 0 0 0
sdk 31 15 302 98 13 0 60 143 0 0
[oracle@11gR2Base ~]$ vmstat -p sdb1
sdb1 reads read sectors writes requested writes
23462 891374 834331 6674648
/*
|| Listing 2.1.3:
|| Using sar to monitor I/O performance
*/
[oracle@11gR2Base ~]$ sar -b
Linux 2.6.18-92.el5 (11gR2Base) 07/11/2010
12:00:01 AM tps rtps wtps bread/s bwrtn/s
12:10:01 AM 10.33 3.01 7.32 80.20 159.99
12:20:01 AM 7.36 1.56 5.81 49.62 106.73
12:30:01 AM 6.94 1.31 5.64 41.83 103.05
12:40:01 AM 7.11 1.44 5.67 46.01 103.38
12:50:01 AM 7.56 1.43 6.12 45.68 120.55
01:00:01 AM 7.08 1.41 5.68 45.09 105.29
01:10:01 AM 8.91 1.66 7.24 60.04 160.48
01:20:01 AM 7.69 1.57 6.12 49.95 110.44
01:30:01 AM 7.04 1.30 5.75 41.62 106.36
01:40:02 AM 7.15 1.40 5.75 44.97 105.48
01:50:01 AM 7.53 1.54 5.99 48.87 116.74
{ . . . removed for sake of brevity . . .
10:00:02 PM 6.95 1.31 5.65 42.01 104.19
10:10:01 PM 8.84 1.76 7.08 63.03 164.83
10:20:01 PM 7.24 1.40 5.84 44.87 107.70
10:30:01 PM 12.46 5.98 6.47 157.31 124.39
10:40:01 PM 7.45 1.64 5.81 50.80 109.26
10:50:01 PM 12.99 6.42 6.58 86.43 143.32
11:00:02 PM 7.61 1.66 5.95 50.35 110.50
11:10:01 PM 8.97 1.81 7.16 61.23 163.96
11:20:01 PM 8.94 3.10 5.84 61.69 109.20
11:30:01 PM 9.01 2.87 6.14 62.54 117.55
Average: 8.07 2.00 6.07 54.46 121.62
/*
|| Listing 2.2:
|| Using ASMCMD’s new iostat command to query ASM file system performance
*/
ASMCMD> iostat 5
Group_Name Dsk_Name Reads Writes
DATA DATA_0000 7200768 1099345920
DATA DATA_0001 3795456 1101507072
DATA DATA_0002 10399744 1096343552
DATA DATA_0003 10543104 629115392
FRA FRA_0000 1363968 9367552
FRA FRA_0001 303104 6004736
FRA FRA_0002 81920 4689920
FRA FRA_0003 86016 4612096
Group_Name Dsk_Name Reads Writes
DATA DATA_0000 0.00 7344128.00
DATA DATA_0001 0.00 7134412.80
DATA DATA_0002 3276.80 7344128.00
DATA DATA_0003 0.00 7340032.00
FRA FRA_0000 0.00 4096.00
FRA FRA_0001 0.00 0.00
FRA FRA_0002 0.00 0.00
FRA FRA_0003 0.00 0.00
Group_Name Dsk_Name Reads Writes
DATA DATA_0000 0.00 5666406.40
DATA DATA_0001 0.00 5666406.40
DATA DATA_0002 3276.80 5876121.60
DATA DATA_0003 0.00 5662310.40
FRA FRA_0000 0.00 4096.00
FRA FRA_0001 0.00 0.00
FRA FRA_0002 0.00 0.00
FRA FRA_0003 0.00 0.00
Group_Name Dsk_Name Reads Writes
DATA DATA_0000 0.00 7138508.80
DATA DATA_0001 0.00 7348224.00
DATA DATA_0002 6553.60 6928793.60
DATA DATA_0003 0.00 7130316.80
FRA FRA_0000 0.00 8192.00
FRA FRA_0001 0.00 0.00
FRA FRA_0002 0.00 0.00
FRA FRA_0003 0.00 0.00
Group_Name Dsk_Name Reads Writes
DATA DATA_0000 0.00 5037260.80
DATA DATA_0001 0.00 5037260.80
DATA DATA_0002 3276.80 5037260.80
DATA DATA_0003 0.00 5033164.80
FRA FRA_0000 0.00 4096.00
FRA FRA_0001 0.00 0.00
FRA FRA_0002 0.00 0.00
FRA FRA_0003 0.00 0.00
/*
|| Listing 2.3.1:
|| Querying ASM-specific dynamic views via SQL*Plus to monitor I/O performance
*/
-----
-- View: V$ASM_DISK_STAT
-- Purpose: Lists statistics for ASM Disks
-----
TTITLE 'ASM Disk Statistics|(From V$ASM_DISK_STAT)'
COL group_number FORMAT 9999 HEADING 'ASM|Disk|Grp#'
COL disk_number FORMAT 9999 HEADING 'ASM|Disk|#'
COL name FORMAT A12 HEADING 'Disk Name' WRAP
COL library FORMAT A10 HEADING 'Library' WRAP
COL mount_status FORMAT A07 HEADING 'Mount|Status'
COL header_status FORMAT A10 HEADING 'Header|Status'
COL mode_status FORMAT A08 HEADING 'Mode|Status'
COL state FORMAT A07 HEADING 'Disk|State'
COL redundancy FORMAT A07 HEADING 'Redun-|dancy'
COL failgroup FORMAT A10 HEADING 'Failure|Group'
COL label FORMAT A12 HEADING 'Label' WRAP
SELECT
group_number
,disk_number
,name
,library
,mount_status
,header_status
,mode_status
,state
,redundancy
,failgroup
,label
FROM v$asm_disk_stat
ORDER BY group_number, disk_number
;
TTITLE OFF
TTITLE 'ASM Disk Statistics|(From V$ASM_DISK_STAT)'
COL group_number FORMAT 9999 HEADING 'ASM|Disk|Grp#'
COL group_name FORMAT A08 HEADING 'Disk|Group|Name'
COL disk_number FORMAT 9999 HEADING 'ASM|Disk|#'
COL total_mb FORMAT 999999 HEADING 'Total|Disk|Space|(MB)'
COL free_mb FORMAT 999999 HEADING 'Free|Disk|Space|(MB)'
COL mount_date FORMAT A11 HEADING 'Mounted On' WRAP
COL repair_timer FORMAT 99999999 HEADING 'Repair|Timer'
COL reads FORMAT 99999999 HEADING 'Disk|Reads'
COL read_time FORMAT 999999 HEADING 'Read|Time'
COL read_errs FORMAT 999999 HEADING 'Read|Errors'
COL mb_read FORMAT 999999.9 HEADING 'Bytes|Read|(MB)'
COL writes FORMAT 99999999 HEADING 'Disk|Writes'
COL write_errs FORMAT 999999 HEADING 'Write|Errors'
COL write_time FORMAT 999999 HEADING 'Write|Time'
COL mb_wrtn FORMAT 999999.9 HEADING 'Bytes|Written|(MB)'
SELECT
ADG.name group_name
,AD.disk_number
,AD.total_mb
,AD.free_mb
,AD.repair_timer
,AD.reads
,AD.read_errs
,AD.read_time
,(AD.bytes_read / (1024*1024)) mb_read
,AD.writes
,AD.write_errs
,AD.write_time
,(AD.bytes_written / (1024*1024)) mb_wrtn
FROM
v$asm_disk_stat AD
,v$asm_diskgroup ADG
WHERE AD.group_number = ADG.group_number
ORDER BY ADG.name, AD.disk_number
;
TTITLE OFF
-----
-- View: V$ASM_DISK_IOSTAT
-- Purpose: Describes additional I/O statistics for any ASM disk that's currently
-- mounted within an ASM disk group by the ASM instance
-----
COL instname FORMAT A08 HEADING 'Inst|Name'
COL dbname FORMAT A08 HEADING 'DB Name'
COL group_name FORMAT A08 HEADING 'Disk|Group|Name'
COL disk_number FORMAT 9999 HEADING 'ASM|Disk|#'
COL reads FORMAT 99999999 HEADING 'Disk|Reads'
COL read_time FORMAT 999999 HEADING 'Read|Time|(s)'
COL read_errs FORMAT 999999 HEADING 'Read|Errors'
COL mb_read FORMAT 999999.9 HEADING 'Bytes|Read|(MB)'
COL writes FORMAT 99999999 HEADING 'Disk|Writes'
COL write_errs FORMAT 999999 HEADING 'Write|Errors'
COL write_time FORMAT 999999 HEADING 'Write|Time|(s)'
COL mb_wrtn FORMAT 999999.9 HEADING 'Bytes|Written|(MB)'
TTITLE 'ASM Disk I/O Statistics|(From V$ASM_DISK_IOSTAT)'
SELECT
IO.dbname
,IO.instname
,ADG.name group_name
,IO.disk_number
,IO.reads
,IO.read_errs
,IO.read_time
,(IO.bytes_read / (1024*1024)) mb_read
,IO.writes
,IO.write_errs
,IO.write_time
,(IO.bytes_written / (1024*1024)) mb_wrtn
/*
-- New in 11gR2
,IO.hot_reads
,IO.cold_reads
,(IO.hot_bytes_read / (1024*1024)) hot_mb_read
,(IO.cold_bytes_read / (1024*1024)) cold_mb_read
,IO.hot_writes
,IO.cold_writes
,(IO.hot_bytes_written / (1024*1024)) hot_mb_wrtn
,(IO.cold_bytes_written / (1024*1024)) cold_mb_wrtn
*/
FROM
v$asm_disk_iostat IO
,v$asm_diskgroup ADG
WHERE IO.group_number = ADG.group_number
ORDER BY IO.dbname, IO.instname, ADG.name
;
TTITLE OFF
-----
-- View: V$ASM_DISKGROUP
-- Purpose: Describes information about ASM disk groups mounted
-- by the ASM instance
-----
TTITLE 'ASM Disk Groups|(From V$ASM_DISKGROUP)'
COL group_number FORMAT 99999 HEADING 'ASM|Disk|Grp #'
COL name FORMAT A12 HEADING 'ASM Disk|Group Name' WRAP
COL sector_size FORMAT 99999999 HEADING 'Sector|Size'
COL block_size FORMAT 999999 HEADING 'Block|Size'
COL au_size FORMAT 99999999 HEADING 'Alloc|Unit|Size'
COL state FORMAT A11 HEADING 'Disk|Group|State'
COL type FORMAT A06 HEADING 'Disk|Group|Type'
COL total_mb FORMAT 999999 HEADING 'Total|Space(MB)'
COL free_mb FORMAT 999999 HEADING 'Free|Space(MB)'
SELECT
group_number
,name
,sector_size
,block_size
,allocation_unit_size au_size
,state
,type
,total_mb
,free_mb
FROM v$asm_diskgroup
;
TTITLE OFF
-----
-- View: V$ASM_DISKGROUP_STAT
-- Purpose: Lists statistics about ASM Disk Groups
-----
TTITLE 'ASM Disk Group Statistics|(From V$ASM_DISKGROUP_STAT)'
COL group_number FORMAT 9999 HEADING 'Grp|#'
COL name FORMAT A08 HEADING 'Disk|Group|Name'
COL sector_size FORMAT 999999 HEADING 'Sector|Size'
COL block_size FORMAT 999999 HEADING 'Block|Size'
COL allocation_unit_size FORMAT 9999999 HEADING 'Alloc|Unit|Size'
COL state FORMAT A09 HEADING 'Disk|State'
COL type FORMAT A07 HEADING 'Disk|Type'
COL total_mb FORMAT 999999 HEADING 'Total|Disk|Space|(MB)'
COL free_mb FORMAT 999999 HEADING 'Free|Disk|Space|(MB)'
COL required_mirror_free_mb FORMAT 999999 HEADING 'Rqrd|Mirror|Space|(MB)'
COL usable_file_mb FORMAT 999999 HEADING 'Usable|File|Space|(MB)'
COL offline_disks FORMAT 99999 HEADING '# Of|Disks|Off-|line'
COL compatibility FORMAT A10 HEADING 'Compat-|ibility'
COL database_compatibility FORMAT A10 HEADING 'Database|Compat.'
SELECT
group_number
,name
,sector_size
,block_size
,allocation_unit_size
,state
,type
,total_mb
,free_mb
,required_mirror_free_mb
,usable_file_mb
,offline_disks
,compatibility
FROM v$asm_diskgroup_stat
;
TTITLE OFF
-----
-- View: V$ASM_FILES
-- Purpose: Lists statistics about ASM Files
-----
TTITLE 'ASM Files|(From V$ASM Views)'
COL group_number FORMAT 999 HEADING 'ASM|Grp|#'
COL group_name FORMAT A12 HEADING 'ASM|Disk|Group'
COL file_number FORMAT 9999 HEADING 'ASM|File|#'
COL compound_index FORMAT 99999999 HEADING 'Compound|Index #'
COL incarnation FORMAT 9999999999 HEADING 'Incarn #'
COL file_name FORMAT A30 HEADING 'File Name' WRAP
COL block_size FORMAT 99999 HEADING 'Block|Size'
COL blocks FORMAT 99999999 HEADING 'Blocks'
COL bytes_mb FORMAT 99999 HEADING 'Size|(MB)'
COL space_alloc_mb FORMAT 999999 HEADING 'Space|Alloc|(MB)'
COL type FORMAT A18 HEADING 'ASM File Type' WRAP
COL redundancy FORMAT A06 HEADING 'Redun-|dancy'
COL striped FORMAT A07 HEADING 'Striped'
COL created_on FORMAT A21 HEADING 'Created On'
COL tot_reads FORMAT 9999999 HEADING 'Total|Reads'
COL tot_writes FORMAT 9999999 HEADING 'Space|Writes'
COL tot_mb_read FORMAT 9999999 HEADING 'Total|Read|(MB)'
COL tot_mb_wrtn FORMAT 9999999 HEADING 'Total|Wrtn|(MB)'
SELECT
AF.type
,ADG.name group_name
,AF.file_number
,AA.name file_name
,AF.block_size
,AF.blocks
,(AF.bytes / (1024*1024)) bytes_mb
,(AF.space / (1024*1024)) space_alloc_mb
,AF.redundancy
,AF.striped
-- ,TO_CHAR(AF.creation_date, 'mm-dd-yyyy hh24:mi:ss') created_on
,(AF.cold_reads + AF.hot_reads) tot_reads
,(AF.cold_writes + AF.hot_writes) tot_writes
,((AF.cold_bytes_read + AF.hot_bytes_read) / (1024*1024)) tot_mb_read
,((AF.cold_bytes_written + AF.hot_bytes_written) / (1024*1024)) tot_mb_wrtn
FROM
v$asm_file AF
,v$asm_alias AA
,v$asm_diskgroup ADG
WHERE AF.file_number = AA.file_number
AND AF.group_number = ADG.group_number
AND AA.group_number = ADG.group_number
AND AF.type IN ('CONTROLFILE','DATAFILE','ONLINELOG','TEMPFILE')
ORDER BY AF.type, ADG.name, AF.file_number
;
TTITLE OFF
/*
|| Listing 2.3.2:
|| Results from querying ASM-specific dynamic views via SQL*Plus
*/
SQL> @ASM_IO_Monitoring;
ASM Disk Statistics
(From V$ASM_DISK_STAT)
ASM ASM
Disk Disk Mount Header Mode Disk Redun- Failure
Grp# # Disk Name Library Status Status Status State dancy Group Label
----- ----- ------------ ---------- ------- ---------- -------- ------- ------- ---------- ------------
1 0 DATA_0000 System CACHED MEMBER ONLINE NORMAL UNKNOWN DATA_0000
1 1 DATA_0001 System CACHED MEMBER ONLINE NORMAL UNKNOWN DATA_0001
1 2 DATA_0002 System CACHED MEMBER ONLINE NORMAL UNKNOWN DATA_0002
1 3 DATA_0003 System CACHED MEMBER ONLINE NORMAL UNKNOWN DATA_0003
2 0 FRA_0000 System CACHED MEMBER ONLINE NORMAL UNKNOWN FRA_0000
2 1 FRA_0001 System CACHED MEMBER ONLINE NORMAL UNKNOWN FRA_0001
2 2 FRA_0002 System CACHED MEMBER ONLINE NORMAL UNKNOWN FRA_0002
2 3 FRA_0003 System CACHED MEMBER ONLINE NORMAL UNKNOWN FRA_0003
8 rows selected.
Sun Jul 11 page 1
ASM Disk Statistics
(From V$ASM_DISK_STAT)
Total Free
Disk ASM Disk Disk Bytes Bytes
Group Disk Space Space Repair Disk Read Read Read Disk Write Write Written
Name # (MB) (MB) Timer Reads Errors Time (MB) Writes Errors Time (MB)
-------- ----- ------- ------- --------- --------- ------- ------- --------- --------- ------- ------- ---------
DATA 0 2204 364 0 48578 0 299 884.5 263026 0 376 2696.4
DATA 1 2204 352 0 22915 0 257 464.8 264663 0 349 2668.1
DATA 2 2204 364 0 190238 0 222 3081.1 258141 0 362 2651.9
DATA 3 2204 365 0 175101 0 264 2833.7 72569 0 200 766.0
FRA 0 2204 1714 0 15896 0 2 243.1 211697 0 167 2054.3
FRA 1 2204 1706 0 527 0 1 3.1 13277 0 74 107.3
FRA 2 2204 1713 0 509 0 0 2.0 13182 0 56 119.2
FRA 3 2204 1712 0 499 0 1 1.9 14675 0 54 133.2
8 rows selected.
Sun Jul 11 page 1
ASM Disk I/O Statistics
(From V$ASM_DISK_IOSTAT)
Disk ASM Read Bytes Write Bytes
Inst Group Disk Disk Read Time Read Disk Write Time Written
DB Name Name Name # Reads Errors (s) (MB) Writes Errors (s) (MB)
-------- -------- -------- ----- --------- ------- ------- --------- --------- ------- ------- ---------
orcl orcl DATA 0 48046 0 298 881.5 163757 0 285 2308.7
orcl orcl DATA 1 22381 0 256 461.8 165376 0 255 2280.2
orcl orcl DATA 2 189721 0 221 3079.1 158862 0 268 2264.1
orcl orcl DATA 3 174607 0 264 2831.7 72540 0 196 765.9
orcl orcl FRA 0 15371 0 2 240.1 112796 0 139 1668.0
orcl orcl FRA 1 2 0 0 .1 13259 0 60 107.2
orcl orcl FRA 2 6 0 0 .0 13180 0 56 119.2
orcl orcl FRA 3 0 0 0 .0 14665 0 53 133.2
8 rows selected.
Sun Jul 11 page 1
ASM Disk Groups
(From V$ASM_DISKGROUP)
ASM Alloc Disk Disk
Disk ASM Disk Sector Block Unit Group Group Total Free
Grp # Group Name Size Size Size State Type Space(MB) Space(MB)
------ ------------ --------- ------- --------- ----------- ------ --------- ---------
1 DATA 512 4096 1048576 CONNECTED NORMAL 8816 1445
0 SSD 512 4096 0 DISMOUNTED 0 0
2 FRA 512 4096 1048576 CONNECTED EXTERN 8816 6845
Sun Jul 11 page 1
ASM Disk Group Statistics
(From V$ASM_DISKGROUP_STAT)
Total Free Rqrd Usable # Of
Disk Alloc Disk Disk Mirror File Disks
Grp Group Sector Block Unit Disk Disk Space Space Space Space Off- Compat-
# Name Size Size Size State Type (MB) (MB) (MB) (MB) line ibility
----- -------- ------- ------- -------- --------- ------- ------- ------- ------- ------- ------ ----------
1 DATA 512 4096 1048576 CONNECTED NORMAL 8816 1445 1852 -203 0 11.2.0.0.0
2 FRA 512 4096 1048576 CONNECTED EXTERN 8816 6845 0 6845 0 11.2.0.0.0
Sun Jul 11 page 1
ASM Files
(From V$ASM Views)
ASM ASM Space Total Total
Disk File Block Size Alloc Redun- Total Space Read Wrtn
ASM File Type Group # File Name Size Blocks (MB) (MB) dancy Striped Reads Writes (MB) (MB)
------------------ ------------ ----- ------------------------------ ------ --------- ------ ------- ------ ------- -------- -------- -------- --------
CONTROLFILE DATA 256 Backup.256.716327307 16384 621 10 48 HIGH FINE 0 0 0 0
CONTROLFILE DATA 257 current.257.716404861 16384 621 10 48 HIGH FINE 416274 317103 6531 4955
CONTROLFILE FRA 256 current.256.716404861 16384 621 10 16 UNPROT FINE 15360 105690 240 1651
CONTROLFILE FRA 265 Current.265.719253907 16384 595 9 16 UNPROT FINE 0 0 0 0
DATAFILE DATA 258 SYSAUX.258.716405081 8192 134161 1048 2101 MIRROR COARSE 5312 102656 103 981
DATAFILE DATA 259 SYSTEM.259.716405187 8192 115201 900 1805 MIRROR COARSE 10657 8112 115 68
DATAFILE DATA 260 UNDOTBS1.260.716405283 8192 60801 475 955 MIRROR COARSE 112 33636 1 583
DATAFILE DATA 261 SECUREFILES.261.716405339 8192 32769 256 517 MIRROR COARSE 91 10 1 0
DATAFILE DATA 262 BASICFILES.262.716405363 8192 16385 128 261 MIRROR COARSE 91 10 1 0
DATAFILE DATA 263 USERS.263.716405381 8192 16313 127 259 MIRROR COARSE 91 10 1 0
DATAFILE DATA 264 EXAMPLE.264.716405395 8192 12801 100 205 MIRROR COARSE 95 10 1 0
DATAFILE FRA 261 SYSTEM.261.719253761 8192 88321 690 692 UNPROT COARSE 0 0 0 0
DATAFILE FRA 262 SYSAUX.262.719253763 8192 62721 490 492 UNPROT COARSE 0 0 0 0
DATAFILE FRA 263 UNDOTBS1.263.719253763 8192 9601 75 77 UNPROT COARSE 0 0 0 0
DATAFILE FRA 264 USERS.264.719253763 8192 641 5 6 UNPROT COARSE 0 0 0 0
DATAFILE FRA 271 rcat_111.dbf 8192 4097 32 33 UNPROT COARSE 0 0 0 0
DATAFILE FRA 271 RCAT_111.271.719258885 8192 4097 32 33 UNPROT COARSE 0 0 0 0
ONLINELOG DATA 266 group_4.266.716406451 512 262145 128 261 MIRROR COARSE 3 32474 0 221
ONLINELOG DATA 267 group_5.267.716406463 512 262145 128 261 MIRROR COARSE 3 29074 0 249
ONLINELOG DATA 268 group_6.268.716406467 512 262145 128 261 MIRROR COARSE 4 34872 0 282
ONLINELOG FRA 257 group_4.257.716408773 512 262145 128 130 UNPROT COARSE 3 16237 0 111
ONLINELOG FRA 258 group_5.258.716408781 512 262145 128 130 UNPROT COARSE 3 14537 0 125
ONLINELOG FRA 259 group_6.259.716408791 512 262145 128 130 UNPROT COARSE 4 17436 0 141
ONLINELOG FRA 266 group_1.266.719253909 512 102401 50 51 UNPROT COARSE 0 0 0 0
ONLINELOG FRA 267 group_2.267.719253909 512 102401 50 51 UNPROT COARSE 0 0 0 0
ONLINELOG FRA 268 group_3.268.719253909 512 102401 50 51 UNPROT COARSE 0 0 0 0
TEMPFILE DATA 265 TEMP.265.716405987 8192 12801 100 205 MIRROR COARSE 1289 2568 140 279
TEMPFILE FRA 269 TEMP.269.719253921 8192 3713 29 30 UNPROT COARSE 0 0 0 0
28 rows selected.