RE: db file sequential read - again

Nice improvement/correction, Alexander.  Thanks! 


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

"There are 10 types of people in the world:  Those who understand
binary, and those who don't."

-----Original Message-----
From: Alexander Fatkulin [mailto:afatkulin@xxxxxxxxx] 
Sent: Tuesday, March 13, 2007 11:12 PM
To: Bobak, Mark
Cc: oracle-l@xxxxxxxxxxxxx
Subject: 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: