Re: Avoid Unnecessary Parallel Query
- From: "fitzjarrell@xxxxxxx" <oratune@xxxxxxx>
- Date: Mon, 28 Jul 2008 10:55:30 -0700 (PDT)
Comments embedded.
On Jul 28, 10:18 am, swjk...@xxxxxxxxxxx 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
.
- Follow-Ups:
- Re: Avoid Unnecessary Parallel Query
- From: swjkeen
- Re: Avoid Unnecessary Parallel Query
- References:
- Avoid Unnecessary Parallel Query
- From: swjkeen
- Avoid Unnecessary Parallel Query
- Prev by Date: Re: Help, my developers are killing me with varchar2(4000)
- Next by Date: Re: Comments?
- Previous by thread: Avoid Unnecessary Parallel Query
- Next by thread: Re: Avoid Unnecessary Parallel Query
- Index(es):
Relevant Pages
|
