Un-wanted parallel queries in 9i

[chrisrlong]

Okay, so I'm on a roll today

Many of my test statements are deciding in 9i that they should be parallel. In 8i, I had optimizer_percent_parallel set to 0 and the degree on every table was 1, so unless I had a parallel hint, there were no parallel queries.

Now in 9i, they appear to have dropped that initialization parameter and I suddenly have several statements that have gone parallel. I want to find a way to turn this back off at the database level. I want the optimizer to not choose to use parallel operations unless I ask for it.

I even came across ALTER SESSION DISABLE PARALLEL DML, which Oracle appears to completely ignore.

Any suggestions would be appreciated.

Thanks,

- Chris

[pando]

try set parallel_max_servers to 0

[chrisrlong]

Wouldn't that eliminate all possible parallel operations?

I do have specific statements that have parallel hints that I still want to be parallel. I simply don't want Oracle to ever choose to make a plan parallel on its own.

Thanks,

- Chris

[slimdave]

Do you have parallel_automatic_tuning turned on, by any chance?

[chrisrlong]

Nope.

Here are the parallel-specific settings on both boxes.

PARAMETER				9i		8i
-----------------------------	-----------	--------------
parallel_adaptive_multi_user	FALSE		FALSE
parallel_automatic_tuning		FALSE		FALSE
parallel_broadcast_enabled			FALSE
parallel_execution_message_size	2148		2148
parallel_instance_group		NULL		NULL
parallel_max_servers		5		16
parallel_min_percent		0		0
parallel_min_servers		0		1
parallel_server			FALSE		FALSE
parallel_server_instances		1		1
parallel_threads_per_cpu		2		2

Here are the non-version, non-file/directory differences in parameters between the two databases.

PARAMETER				9i		8i
-----------------------------	-----------	--------------
db_block_checksum			TRUE		FALSE
dml_locks				988		2440
enqueue_resources			2030		3482
fast_start_io_target		0		5000
java_pool_size			33554432	20000K
job_queue_processes			0		9
log_archive_max_processes		2		1
log_buffer				10485760	15360000
max_rollback_segments		49		122
O7_DICTIONARY_ACCESSIBILITY	FALSE		TRUE
optimizer_max_permutations	2000		80000
parallel_max_servers		5		16
parallel_min_servers		0		1
processes				200		500
query_rewrite_enabled		TRUE		FALSE
query_rewrite_integrity		trusted	enforced
sessions				225		555
shared_pool_reserved_size		10905190	5120000
shared_pool_size			218103808	102400000
transactions				247		610

I've asked the DBA about the differences in the parallel_max_servers and parallel_min_servers settings. I can't see why they would have been changed for or during the upgrade, but we'll see.

Any thoughts?

- Chris

[DaPi]

(This is gut speaking, not brain - but why not use both?) Could query_rewrite_enabled be giving you grief?

[chrisrlong]

Okay - had the DBA make changes to the init params. The only remaining differences are:

PARAMETER				9i		8i
-----------------------------	-----------	--------------
db_block_checksum			TRUE		FALSE
fast_start_io_target		2000		5000
java_pool_size			33554432	20000K
log_archive_max_processes		2		1
O7_DICTIONARY_ACCESSIBILITY	FALSE		TRUE
shared_pool_reserved_size		10905190	5120000
shared_pool_size			218103808	102400000

Any more ideas?

- Chris

[chrisrlong]

I just noticed that the command I was trying to use was wrong, it's:

ALTER SESSION DISABLE PARALLEL QUERY (not DML)

This indeed makes the optimizer choose a non-parallel plan for all the SELECT statements in the session.

*But this is still not what I want!* It is a suitable work-around for the moment, but not for the long term.

I very much want to mimic my 8i functionality: The optimizer should not choose a parallel plan unless I ask for one.

Any thoughts are appreciated,

- Chris

[abhaysk]

Since OPTIMIZER_PERCENT_PARALLEL is obselete and
The degree of parallelism is determined by any of the following:

A PARALLEL clause in a statement
For objects referred to in a query, the PARALLEL clause that was used when the object was created or altered
A parallel hint inserted into the statement
A default determined by Oracle


If you dont want Oracle to choose prallel execution, then dont create objects with Parallel Clause or Alter it to NOPARALLEL ( I beleive the underlying objects in your query are created with parallel clause? )..

Abhay.

[chrisrlong]

Well, I could *swear* I checked every table and index involved in the query and they all had a degree of 1 (noparallel), but to be safe, I altered every table and every index in the schema and set them all to noparallel and the problem went away. I must have missed one when I was checking before, although I still don't see how. Especially since this happened in multiple statements and there were only 4-5 tables and indexes with parallel settings and none of them were involved in any of the statements.

Regarless, re-setting all the tables and indexes to noparallel seems to have solved the problem.

Thanks to everyone for the help.

- Chris