OraFAQ Forum: Server Administration » Tablespace Read Stats

Home » RDBMS Server » Server Administration » Tablespace Read Stats (10.2/11.2)

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

Tablespace Read Stats [message #577315]

Thu, 14 February 2013 03:35

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Hi all,

Just a quick one.

On looking at AWR you'll all be familiar with the tablespace section and the read statistics associated with this.

My question is simple - do these read values increment in line with physical read requests, or in line with any calls to the tablespace - even if the block(s) is(are) already resident within the SGA?

From examining the dba_hist_sysstat (and similar) views they seem to correlate to physical reads - but I'm not 100% sure.

Does anyone know for sure? My google searches are coming up worthless (possible terms used issue) and I couldn't see anything in the documentation.

Thanks in advance as always Smile

Edit: Mods if this is more apt in perf. tuning please move - I couldnt decide

[Updated on: Thu, 14 February 2013 03:36]

Report message to a moderator

Re: Tablespace Read Stats [message #577319 is a reply to message #577315]

Thu, 14 February 2013 03:38

hkchital
Messages: 128
Registered: September 2008
Location: Singapore

Senior Member

They should relate to physical I/O.

Check the tablespace stats with the file stats. These would be from v$FILESTAT which shows physical i/o.

Hemant K Chitale

Report message to a moderator

Re: Tablespace Read Stats [message #577322 is a reply to message #577319]

Thu, 14 February 2013 03:43

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Ideal, thank you.

Report message to a moderator

Re: Tablespace Read Stats [message #577699 is a reply to message #577322]

Tue, 19 February 2013 13:11

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

Senior Member

I like to look at the objects that are doing the most physical reads per day because it is more significant (Wipe out the top offenders and the system does not slow down during some of the smaller I/O spikes).

DATE                 TOTAL_PHYSICAL_READS_TODAY OBJECT_NAME
-------------------- -------------------------- ---------------------------
2013-02-19 Tuesday                       101674 PK_LMETADATA
2013-02-19 Tuesday                       104963 SEARCHABLESTRINGS
2013-02-19 Tuesday                       109453 SS_COMBO
2013-02-19 Tuesday                       120738 METADATA_DATAFILE
2013-02-19 Tuesday                       124802 AUDITLOG
2013-02-19 Tuesday                       124826 JHL_USERID
2013-02-19 Tuesday                       145231 PK_SEARCHL
2013-02-19 Tuesday                       149512 SL_COMBO
2013-02-19 Tuesday                       315118 AP_ARTICLE
2013-02-19 Tuesday                       426390 SEARCHABLELONGS
2013-02-19 Tuesday                       775605 METADATA_REVISIONID
2013-02-19 Tuesday                       776048 METADATA_FACTORYID
2013-02-19 Tuesday                       912172 PK_METADATA
2013-02-19 Tuesday                      1313523 LAYERMETADATA
2013-02-19 Tuesday                      1864546 DIDS
2013-02-19 Tuesday                      2358756 JOBHISTORYLOG
2013-02-19 Tuesday                      5325400 METADATA
                     --------------------------
sum                                    16512369

ECSESBQ > list
  1  select
  2  to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DATE",
  3  sum(b.PHYSICAL_READS_DELTA) total_physical_reads_today,
  4  a.object_name
  5  from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
  6  where  a.object_id=b.OBJ#
  7  and b.SNAP_ID >=
  8  (select min(snap_id)
  9  from sys.wRM$_SNAPSHOT
 10  where BEGIN_INTERVAL_TIME >= trunc(sysdate))
 11  and upper(a.object_name) like upper('%') and b.PHYSICAL_READS_DELTA>0
 12  and c.instance_number=(select instance_number from v$instance)
 13  and c.snap_id=b.snap_id
 14  group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day'),a.object_name
 15* order by 2

[Updated on: Tue, 19 February 2013 13:12]

Report message to a moderator

Previous Topic:	Migration of Oracle from AIX to Solaris
Next Topic:	space manage

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 12 23:31:38 CST 2025