Parallel Query problem

I got a wierd problem with parallel query on 8.1.7

Scenario goes as follows: There is a schema with quite a number of smaller tables (less than 1 Meg) all with PARALLEL (DEGREE DEFAULT INSTANCES DEFAULT). There is connection pool on that schema with about 20 connections doing various and frequent simple queries on these tables. All statistics for these tables are regulary computed, and CBO is used. There are proper indexes in place for queried columns. So far so good. Since all these tables are small oracle almost allways chooses FULL TABLE SCAN as access path. This sounds reasonable because tables are small and it's more efficient to do FTS than to go through indexes first.

But when database chooses FTS it executes it through Parallel Execution. And then I got database doing way to many parallel queries on less than 1 Meg tables. And there are many slave processes and many queries got their degree of parallelism downgraded because there are not enough slaves. And 'PX%' Waits are allways on the Top 5 Wait events by statspack report along with 'direct path read' (standard access path for PQ)

My question is how to 'tune' optimizer not to use PQ on these smaller tables because I assume there are many downsides in that. For example each PQ forces Tablespace level checkpoint, PQ uses direct path reads so it's bounding IO and there are of course overhead of parallelizing queries insted of doing then serially.

My options are:

Do the 'alter table x parallel (degree 1 instances 1)' on these tables. But when some of these tables got bigger PQ might be the right choice and i would want my database to be 'selftunable' as much as possible (that's why I uses 'degree default' for start). And there are other type of queries on the same tables that would benefit from the PQ (more complex and less frequent).

I don't like using hints for the same reasons.

I could force use of indexes either by hints or by adjusting optimizer_index_cost_adj. Nice, but I really think that FTS is right choice but not when FTS is executed in parallel.

Is there anything else....

thnx,
a. Received on Fri Jun 06 2003 - 08:49:01 CDT