Avoid Unnecessary Parallel Query

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. Received on Mon Jul 28 2008 - 10:18:33 CDT