How to determine slowness in I/O from AWR? [message #563127] |
Thu, 09 August 2012 00:01 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/f71d1fec42c5929468e37dbc03ea9e46?s=64&d=mm&r=g) |
orapratap
Messages: 134 Registered: November 2011 Location: Canada
|
Senior Member |
|
|
Hello
We are investigating performance of SQL executions on a database server and we suspect I/O on the server is an issue
For example one particular statement accesses one row during execution (index access) and still takes 2.4 seconds out of which it does I/O for 1.9 seconds
which of the following sections in the AWR will give us the correct information about the I/O, it is slow or not?
1)
Load Profile
Logical reads per second
Physical reads per second
2)
Top 5 Timed Foreground Events
waits / time(s) for events like "db file sequential/scattered read"
average wait(ms) for events like "db file sequential/scattered read"
3)
Foreground Wait Events
db file sequential read
db file scattered read
4)
Wait Event Histogram
%of waits <1ms <2ms
Disk file operations I/O
db file sequential read
db file scattered read
5)
Wait Event Histogram Detail (64 msec to 2 sec)
Wait Event Histogram Detail (4 sec to 2 min)
6)
IOStat by Function summary
Buffer Cache Reads reads per sec
7)
File IO Stats
Thanks and Regards
Orapratap
[Updated on: Thu, 09 August 2012 00:17] Report message to a moderator
|
|
|
|
|
Re: How to determine slowness in I/O from AWR? [message #563150 is a reply to message #563145] |
Thu, 09 August 2012 01:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
- "Av Reads/s", "Av Writes/s" give you the load.
- "Av Rd(ms)", "Av Buf Wt(ms)" give you the performances of IO subsystem seen by Oracle (this is what you have to compare with your benchmark on the IO subsystem - the hardware vendor gives ideal figures you never reach in real world and they are meaningless if you have disk arrays and several layers between the physical disk and the Oracle files)
- "Buffer Waits" gives you the consequence of the previous 2 on your client.
Regards
Michel
[Updated on: Thu, 09 August 2012 01:39] Report message to a moderator
|
|
|
|
Re: How to determine slowness in I/O from AWR? [message #563214 is a reply to message #563213] |
Thu, 09 August 2012 13:24 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/da93b8d9f57f4be8145750e5748ac9cd?s=64&d=mm&r=g) |
alan.kendall@nfl.com
Messages: 163 Registered: June 2012 Location: Culver City, California
|
Senior Member |
|
|
I also want to be sure that I am focusing on the actual objects that are being read with the following.
@dba_hist_seg_stat_PHYSICAL_READS_BY_DAILY_TOTALS.sql
DATE OBJECT_NAME TOTAL_DAILY_PHYSICAL_READS
---------- -------------------- --------------------------
2012-08-08 D_1F000D5D80000902 33283
2012-08-08 D_1F000D5D80000161 37879
2012-08-08 DM_SYSOBJECT_S_COMP3 39922
2012-08-08 NFL_VIDEO_S 40468
2012-08-08 DM_WEBC_800129A9_R 43485
2012-08-08 WRH$_ACTIVE_SESSION_ 51748
2012-08-08 DM_SYSOBJECT_S_IX_00 53581
2012-08-08 DMI_OBJECT_TYPE_UNIQ 57399
2012-08-08 TAB$ 61651
2012-08-08 IDX_DM_SYSOBJECT_R1 96717
2012-08-08 D_1F000D5D80000160 108714
2012-08-08 DM_WEBC_800129A9_L 132862
2012-08-08 D_1F000D5D80000910 165702
2012-08-08 DM_WEBC_800129A9_S 169636
2012-08-08 DMI_OBJECT_TYPE 179898
2012-08-08 NFL_ARTICLE_S 192771
2012-08-08 DM_SYSOBJECT_R 238555
2012-08-08 D_1F000D5D80000005 697674
2012-08-08 DM_SYSOBJECT_S 748792
2012-08-08 DM_SYSOBJECT_S_COMP2 766891
2012-08-08 D_1F000D5D80000901 1010850
2012-08-08 DM_RELATION_S 1711991
2012-08-08 DM_SYSOBJECT_S_COMP1 1738940
2012-08-08 NFL_CONTENT_R_COMP1 1876666
2012-08-08 DMR_CONTENT_R 2376400
2012-08-08 DMI_QUEUE_ITEM_S 3441227
2012-08-08 NFL_CONTENT_R 4475234
2012-08-08 DM_SYSOBJECT_R_COMP1 5548337
2012-08-08 DMR_CONTENT_S 6780831
2012-08-08 NFL_CONTENT_S 11382506
2012-08-08 D_1F000D5D8000010A 15529501
2012-08-08 DM_SYSOBJECT_R_COMP2 32885582
ENDOCP1P > list
1 select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DATE",a.object_name,
2 sum(b.PHYSICAL_READS_DELTA) total_daily_physical_reads
3 from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
4 where a.object_id=b.OBJ#
5 -- and b.SNAP_ID >(select max(SNAP_ID)-24*8 from sys.wRM$_SNAPSHOT)
6 and upper(a.object_name) like upper('%') and b.PHYSICAL_READS_DELTA>0
7 and c.instance_number=(select instance_number from v$instance)
8 and c.snap_id=b.snap_id
9 group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day'),a.object_name
10* order by 1,3
|
|
|