OraFAQ Forum: Performance Tuning » How to determine slowness in I/O from AWR?

Home » RDBMS Server » Performance Tuning » How to determine slowness in I/O from AWR? (Oracle 11.2.0.3.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to determine slowness in I/O from AWR? [message #563127]

Thu, 09 August 2012 00:01

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 #563138 is a reply to message #563127]

Thu, 09 August 2012 01:04

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

How to determine slowness in I/O from AWR?

In AWR report you have a section "IO Stats" which will give the io performances; check if they match with what is expected with your hardware and workload.

Regards
Michel

Report message to a moderator

Re: How to determine slowness in I/O from AWR? [message #563145 is a reply to message #563138]

Thu, 09 August 2012 01:28

orapratap
Messages: 134
Registered: November 2011
Location: Canada

Senior Member

Hello Michel

Thanks for the quick reply

I understand the I/O speed when calculated as "blocks read/second" would give us the reference

In the "Tablespace IO Stats" section there are 3 columns - Av Reads/s, Av Rd(ms), Av Blks/Rd

From these how can I determine the above I/O speed so that we can compare with what hardware vendor has committed

Shall I multiply Av Reads/s with Av Blks/Rd and get "blks read/second"? or then "seconds to read a block(8k)"?

Thanks and Regards
Orapratap

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

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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 #563213 is a reply to message #563150]

Thu, 09 August 2012 13:17

alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California

Senior Member

I look at the megabytes of objects in memory because it is these objects that cause others to be LRU and cause all the I/O.
In a DataWarehouse this might be hard to control but in an OLTP you are in control.

ENDOCP1P > @v$bh
OBJECT_TYPE DB       MEG_IN_MEMORY OBJECT_IN_MEMORY
----------- -------- ------------- -----------------------------------------
INDEX       NDOCP1             132 NFLPROD.D_1F000D5D80000109
INDEX       NDOCP3             134 NFLPROD.D_1F000D5D80000109
INDEX       NDOCP3             136 NFLPROD.D_1F000D5D80000146
INDEX       NDOCP4             138 NFLPROD.D_1F000D5D80000146
INDEX       NDOCP2             139 NFLPROD.DM_SYSOBJECT_S_COMP1
INDEX       NDOCP2             141 NFLPROD.D_1F000D5D80000146
INDEX       NDOCP4             141 NFLPROD.DM_SYSOBJECT_S_COMP1
INDEX       NDOCP4             143 NFLPROD.D_1F000D5D80000109
INDEX       NDOCP2             146 NFLPROD.D_1F000D5D80000109
INDEX       NDOCP1             162 NFLPROD.DM_SYSOBJECT_S_COMP2
INDEX       NDOCP1             181 NFLPROD.DM_SYSOBJECT_S_COMP1
TABLE       NDOCP4             215 NFLPROD.DM_WEBC_800129A9_L
INDEX       NDOCP3             223 NFLPROD.D_1F000D5D80000902
INDEX       NDOCP3             260 NFLPROD.D_1F000D5D8000000F
INDEX       NDOCP1             260 NFLPROD.D_1F000D5D8000000F
INDEX       NDOCP1             267 NFLPROD.D_1F000D5D80000902
INDEX       NDOCP2             268 NFLPROD.D_1F000D5D8000000F
INDEX       NDOCP4             269 NFLPROD.D_1F000D5D8000000F
INDEX       NDOCP2             284 NFLPROD.DM_SYSOBJECT_R_COMP2
INDEX       NDOCP3             289 NFLPROD.DM_SYSOBJECT_R_COMP2
INDEX       NDOCP4             297 NFLPROD.D_1F000D5D80000902
TABLE       NDOCP4             758 NFLPROD.NFL_CONTENT_S
TABLE       NDOCP3             863 NFLPROD.NFL_CONTENT_S
TABLE       NDOCP1             916 NFLPROD.NFL_CONTENT_S
TABLE       NDOCP3             922 NFLPROD.DM_SYSOBJECT_R
TABLE       NDOCP1             929 NFLPROD.DM_SYSOBJECT_R
TABLE       NDOCP2             939 NFLPROD.DM_SYSOBJECT_R
TABLE       NDOCP4             961 NFLPROD.DM_SYSOBJECT_R
TABLE       NDOCP2            1042 NFLPROD.NFL_CONTENT_S
TABLE       NDOCP1            2976 NFLPROD.DM_SYSOBJECT_S
TABLE       NDOCP3            3172 NFLPROD.DM_SYSOBJECT_S
TABLE       NDOCP4            3260 NFLPROD.DM_SYSOBJECT_S
TABLE       NDOCP2            3775 NFLPROD.DM_SYSOBJECT_S

ENDOCP1P > list
  1  SELECT
  2  o.object_type,i.instance_name db,COUNT(*)*8192/1024/1024 meg_in_memory,
  3  o.owner||'.'||o.OBJECT_NAME Object_in_Memory
  4       FROM DBA_OBJECTS o, gV$BH bh, gv$instance i
  5      WHERE o.DATA_OBJECT_ID = bh.OBJD
  6      and bh.status<>'free'
  7      and bh.inst_id = i.inst_id
  8  and o.object_name like upper('%%')
  9      GROUP BY o.owner||'.'||o.OBJECT_NAME,o.object_type,i.instance_name
 10      having count(*)>0
 11*     ORDER BY COUNT(*)

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

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

Report message to a moderator

Previous Topic:	Table actual size
Next Topic:	Analyzed table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 15:42:59 CST 2024