Re: Avoid Unnecessary Parallel Query

<swjkeen_at_yahoo.co.uk> kirjoitti
viestissä:af1a0bc7-4813-43ab-bf95-a9ef74acbfc0_at_y38g2000hsy.googlegroups.com...
>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.

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