Re: parallel read and buffer cache

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