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_at_freelists.org <oracle-l-bounce_at_freelists.org> 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