RE: db file sequential read - again

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Tue, 13 Mar 2007 17:14:29 -0500
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E3602F6E36F@CWYMSX04.Corp.Acxiom.net>

Aren't 'physical reads direct' included in 'physical reads'? If so, I think you'd want to subtract out that value since direct reads won't be affected by any pool definition. Maybe that's not a big deal for most folks, but with a DW and consequently lots of parallelism, direct reads are the most frequent physical read, so non-direct physical reads are important to spot for us.

> The query I came up with is:
> select vss.owner,
> vss.object_name,
> vss.statistic_name,
> vss.value,
> ds.bytes segsize,
> ds.buffer_pool
> from v$segment_statistics vss,
> dba_segments ds
> where vss.statistic_name ='physical reads'
> and vss.value > 5000000 ---You may need to play with this
threshold
> value for your environment
> and ds.segment_type = vss.object_type
> and ds.segment_name = vss.object_name
> and ds.owner=vss.owner
> and ds.buffer_pool = 'DEFAULT'
> order by value
> /

Dave

---

David C. Herring, DBA | A c x i o m Delivery Center Organization

630-944-4762 office | 630-430-5988 wireless | 630-944-4989 fax

---

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.

If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.

If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.

Thank you.

---

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 13 2007 - 17:14:29 CDT