Re: Avoid Unnecessary Parallel Query

Comments embedded.

On Jul 28, 10:18 am, swjk..._at_yahoo.co.uk wrote:
> I am using Oracle 10.2.0.3 RAC on 2 x Sun Solaris box with 32 CPUs. In
> general I wish to allow parallel execution, so parallel_server is set
> to TRUE.
>

Interesting since the parallel_server init.ora parameter was used in Oracle 8.1.x to indicate that the instance was part of an OPS implementation, and has no effect in 10.2.0.3.

> Our database is running, amongst other things, the Oracle Internet
> Directory schema. The application using this was experiencing a poor
> response time to a number of its queries. A quick investigation
> identified that a high percentage of time was spent on waits for
> events related to parallel execution.
>
> I ran a "select count(*)..." against one of the commonly used tables
> (ODS.CT_DN) to establish what volume of data was being queried, which
> took ~1s to count ~1,500 rows. This table has some indexes on it which
> have parallel enabled, and a trace of the query showed that parallel
> query was being used.
>
> If I disable parallel execution on the indexes in question, and re-run
> the query, as expected it completes far quicker (~10ms). It seems
> therefore that with parallel enabled on the indexes, with such a low
> number of rows, the query is spending most of its time spawning and
> coordinating slave processes.
>
> The table and all the indexes have statistics gathered (by
> GATHER_STATS_JOB ), so my question is, why doesn't the optimizer reach
> the same conclusion as me, that based on the low number of rows in the
> table it would be far quicker to execute the query in serial rather
> than in parallel?
>

That may depend upon whether the statistics are estimated or calculated, and I expect since you're running the automatic job they're estimates. Such estimates may be fine on some tables and inadequate on others. Also, it may be necessary to generate histograms for the tables in question to allow the optimizer to realize the actual data distribution. I would check to see if there are broad gaps between key values (columns or groups of columns which are indexed) as this could mislead the optimizer into presuming there is far more data than you actually have in the table or tables in question.

> The table in question, and others experiencing a similar problem,
> belong to Oracle Internet Directory, hence I would prefer not to
> resolve the problem using schema changes, i.e. disabling parallel
> execution on the schema objects. Furthermore, though the tables may
> have few rows now, in the future they may reach a point where it makes
> sense to execute these queries in parallel, so I guess I would like
> the optimizer to do its job and adjust the plan accordingly as the
> statistics change.
>
> Any ideas?
>
> Thanks,
>
> Simon.

David Fitzjarrell Received on Mon Jul 28 2008 - 12:55:30 CDT