Re: Parallel execution strategy

On Mar 17, 1:32 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Mar 17, 6:14 am, vitalis..._at_gmail.com wrote:
>
>
>
> > Hi all,
>
> > A customer of mine is facing performance problems caused by bad single
> > physical read rates. I'm working on the root cause. In the meanwhile
> > I'm going to implement parallel execution since all resources on these
> > servers are underused and there are just a few concurrent users all
> > day long. With parallel execution enabled, the physical read rate for
> > most queries with lots of gets is dramatically increased.
>
> > Any advice regarding the strategy to get the best results? Forcing
> > parallel query/dml/ddl at the session level is no option (I've tried
> > this out on a test plateform with very bad results such as application
> > errors and, as expected, very long execution times for queries not
> > benefiting from parallel execution.)
> > I'm currently identifying every queries with a bad read rate and a
> > long execution time and I'm implementing parallel execution on the
> > underlying objects (by testing each query on a test plateform). But
> > this is somewhat time-consuming. ;-)
>
> > TIA,
> > Jerome
>
> I could be wrong, but I believe that parallel execution will force all
> dirty buffers in the buffer cache for the objects referenced in the
> query to be written to disk (object checkpoint) prior to the query
> performing the direct reads of the objects from disk for the parallel
> execution. This might explain why the problem becomes worse when
> parallel execution is used. There are quite a few articles on the
> Internet that describe potential performance problems caused by
> parallel execution.

Thanks for your time Charles.

> Have you tried monitoring the system wide wait events as a starting
> point for addressing the performance problems?

Yes. And the root cause is the bad read rate for single processes. This point is being addressed with the suppliers involved.

> When was the last time
> table and index statistics were gathered with DBMS_STATS?

They are up to date.

> What is the
> Oracle version?

My bad. I've forgotten to give the versions (2 environments with the same read rate problem): 10.2.0.3.0 RAC and 9.2.0.8.0 RAC.

Please bear in mind that my question is about switching to parallel execution, not about resolving the root cause of the problem (hopefully our suppliers will take care of that.)

Cheers,
Jerome Received on Mon Mar 17 2008 - 08:28:45 CDT