Avoid Unnecessary Parallel Query
- From: swjkeen@xxxxxxxxxxx
- Date: Mon, 28 Jul 2008 08:18:33 -0700 (PDT)
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.
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?
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.
.
- Follow-Ups:
- Re: Avoid Unnecessary Parallel Query
- From: Jack
- Re: Avoid Unnecessary Parallel Query
- From: fitzjarrell@xxxxxxx
- Re: Avoid Unnecessary Parallel Query
- Prev by Date: Re: Help, my developers are killing me with varchar2(4000)
- Next by Date: Re: Newbie Oracle DBA question - Why CREATE ANY VIEW not shown
- Previous by thread: Help, my developers are killing me with varchar2(4000)
- Next by thread: Re: Avoid Unnecessary Parallel Query
- Index(es):
Relevant Pages
|
