Re: parallel read and buffer cache

Hi Jonathan ,

Thanks for your mail .

You are right that the output belongs to gv$system_event not session and i forgot to add direct reads . The dbserver has been up since 01.sep.2004 .

This is the output of gv$system_event with direct path reads:

INST_ID	EVENT	TOTAL_WAITS	TOTAL_TIMEOUTS	TIME_WAITED	AVERAGE_WAIT	TIME_WAITED_MICRO
1	db file parallel read	81267	0	352028	4	3520276629
1	db file scattered read	654639	0	1270063	2	12700628138
1	db file sequential read	11524145	0	6419610	1	64196100180
1	db file single write	16086	0	2239	0	22393203
1	direct path read	12838275	0	4553	0	45529459
1	direct path read (lob) 	170	0	0	0	841
1	direct path write	3635299	0	1122	0	11219581
1	direct path write (lob)	28	0	0	0	87
2	db file parallel read	27553	0	110379	4	1103788244
2	db file parallel write	99654	0	83	0	831104
2	db file scattered read	860725	0	4065017	5	40650170952
2	db file sequential read	40433756	0	18878430	0	188784298739
2	db file single write	64	0	9	0	87765
2	direct path read	32650754	0	12463	0	124633617
2	direct path read (lob) 	52	0	0	0	379
2	direct path write	10582783	0	3785	0	37850109

The biggest numbers belong to db file sequential read and direct path read waits. But time waited columns a significant difference between these two events . Direct path reads time waited value is very low comparing to db seq.scan values. What can be the reason for this?

You said : "Clearly the
> multiblock I/Os are slower than the single block
> I/Os. Your single block reads seem to be
> buffered by something (outside Oracle) on
> instance 2 rather better than they are on instance 1."

How did you understand that ?

Kind Regards,
hope

Kind Regards,
hope

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<ch9o3k$6qt$1_at_hercules.btinternet.com>...
> Is this really SESSION event, and not SYSTEM event ?
>
> There is a problem with parallel execution and
> wait events - the results for parallel slaves are
> not summed back to the query coordinator,
> so checking numbers later can be misleading.
>
> Notice that you have no 'direct path read' figures
> here, i.e. no time recorded against PX slaves.
>
> The 'db file parallel read' is not a read by a PX
> slave, by the way, it is a non-contiguous multiblock
> read by a single process.
>
> There is no way I can tell from this set of figures
> whether you should increase the buffer. Clearly
> you are losing a lot of time to I/O. Clearly the
> multiblock I/Os are slower than the single block
> I/Os. Your single block reads seem to be
> buffered by something (outside Oracle) on
> instance 2 rather better than they are on instance 1.
>
>
> It is possible that a larger Oracle buffer would help,
> you are doing a lot of reading, and it's hard to believe
> that you aren't re-reading a lot of blocks. On the other
> hand, you haven't mentioned the time period, so this
> could be a few weeks worth of reads.
>
> It's possible that you need to check the SQL for
> inefficient access paths - possibly ensure that
> suitable indexes are available, or that partition
> elimination can occur.
>
> It would also be a good idea to cross check
> with v$system_event to see how much time
> and contention you are overlooking due to
> the missing 'direct path read'.
>
>
> --
> Regards
>
> Jonathan Lewis
>
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ
>
> http://www.jlcomp.demon.co.uk/seminar.html
> Optimising Oracle Seminar - schedule updated Sept 2nd
Received on Mon Sep 06 2004 - 05:59:21 CDT