Re: db file sequential read - again

Mark,

you forgot to include v$segment_statistics.subobject_name =
dba_segments.partition_name in join conditions. You should account for
partitioned/subpartitioned tables too.

btw - that query is a "little" slow when you have a DB with large
number of segments (more than 350 000 in my case),

Here is something that I found to be more performance-friendly:

with segstat as
(
        select  object_type,
                        object_name,
                        owner,
                        subobject_name,
                        max(case statistic_name when 'physical reads' then 
value end) pr,
                        max(case statistic_name when 'physical reads direct' 
then value end) prd
                from v$segment_statistics vss
                where statistic_name in ('physical reads', 'physical reads 
direct')
                group by object_type, object_name, owner, subobject_name
) select * from (
        select st.owner,
                st.object_name,
                st.subobject_name,
                (st.pr-st.prd) pr_prd_diff,
                ds.bytes/power(1024,2) mb
        from dba_segments ds, segstat st
        where ds.owner=st.owner
                and ds.partition_name=st.subobject_name
                and ds.segment_name=st.object_name
                and ds.segment_type=st.object_type
                and ds.buffer_pool='DEFAULT'
        order by st.pr-st.prd desc
        ) where rownum <= 20;

On 3/14/07, oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> wrote:

Dave,

You're absolutely right!  Good point.  I come from a very OLTP-centric
view of the world....

Here's a modified version of the query which will account for physical
reads direct:
column owner format a20
column statistic_name format a15
select * from(
  select vss1.owner,
         vss1.object_name,
         vss1.statistic_name,
         vss1.value-vss2.value value,
         ds.bytes/1048576 "SEGSIZE (MB)",
         ds.buffer_pool
    from v$segment_statistics vss1,
         v$segment_statistics vss2,
         dba_segments ds
   where vss1.statistic_name ='physical reads'
     and vss2.statistic_name = 'physical reads direct'
     and ds.segment_type = vss1.object_type
     and ds.segment_name = vss1.object_name
     and ds.owner=vss1.owner
     and ds.segment_type = vss2.object_type
     and ds.segment_name = vss2.object_name
     and ds.owner=vss2.owner
     and ds.buffer_pool = 'DEFAULT'
order by (vss1.value-vss2.value) desc
)
where rownum <=20
/

Note that, while I was tinkering, I made a few other changes too:
 - It no longer needs the threshold value, 5,000,000, or whatever.  It
will automatically return the top 20 segments w/ the most non-direct
physical I/O.

 - The order of the output is reversed, worst is on top.

 - Segment size is displayed in MB.

 - Added some reasonable column formatting, for a wide terminal (at
least 132 columns)

-Mark

--
Mark J. Bobak
Senior Oracle Architect
ProQuest/CSA



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

Other related posts: