RE: db file sequential read

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Wed, 14 Mar 2007 09:58:51 -0400
Message-ID: <AA29A27627F842409E1D18FB19CDCF270B9B92F0@AABO-EXCHANGE02.bos.il.pqe>

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_at_gmail.com] Sent: Tuesday, March 13, 2007 11:12 PM
To: Bobak, Mark
Cc: oracle-l_at_freelists.org
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_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 Received on Wed Mar 14 2007 - 08:58:51 CDT

RE: db file sequential read - again