Re: Parallel execution strategy

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.

Have you tried monitoring the system wide wait events as a starting point for addressing the performance problems? When was the last time table and index statistics were gathered with DBMS_STATS? What is the Oracle version?

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Mon Mar 17 2008 - 07:32:29 CDT