Re: Avoid Unnecessary Parallel Query

From: Jack <>
Date: Mon, 28 Jul 2008 22:19:03 +0300
Message-ID: <488e1bab$0$23588$>

<> kirjoitti
>I am using Oracle 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.

Yes, I do have.
Just give contact details (later with invoising details) and I will analyze this issue.
Joo, myäs selviä savvox kuvvaus auttais assia ;) Same in London dialect... Received on Mon Jul 28 2008 - 14:19:03 CDT

Original text of this message