Oracle Database Discussions

Hi All,

I am on 10.2.0.3 and since last couple of days, i am getting high waits on 'enq: PS - contention' for a query which is using parallel slaves to retrieve records from tables.Px are invloked through table.DEGREE and not through any hint.

After 3 hrs of wait on this, query terminted with ORA-44203: timeout waiting for lock on cursor error msg.
There were there concurrent executions of same query from three different machines located in different locations.
I have varified that there is no change in query's execution plan.and each time px were used.
This is for the first time where Oracle terminated query with ORA-44203.In past it used to retrieve records within 2 hrs.

FYI ...
object_cache_optimal_size integer 102400
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.3
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE

parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_execution_message_size integer 2152
parallel_instance_group string
parallel_max_servers integer 8
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_threads_per_cpu integer 2
recovery_parallelism integer 5


As per Oracle support suggestion, i am waiting for next ocurrance of this err so that i can put 10046 trace.

I would appreciate if you can share such experiance if you have gone through.

Regards,
Bhavik Desai