Question On enq: PS - contention

From: "Desai, Bhavik \(MLITS\)" <Bhavik_Desai@xxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 13 Jan 2009 15:11:17 +0530
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



--------------------------------------------------------------------------
This message w/attachments (message) may be privileged, confidential or 
proprietary, and if you are not an intended recipient, please notify the 
sender, do not use or share it and delete it. Unless specifically indicated, 
this message is not an offer to sell or a solicitation of any investment 
products or other financial product or service, an official confirmation of any 
transaction, or an official statement of Merrill Lynch. Subject to applicable 
law, Merrill Lynch may monitor, review and retain e-communications (EC) 
traveling through its networks/systems. The laws of the country of each 
sender/recipient may impact the handling of EC, and EC may be archived, 
supervised and produced in countries other than the country in which you are 
located. This message cannot be guaranteed to be secure or error-free. 
References to "Merrill Lynch" are references to any company in the Merrill 
Lynch & Co., Inc. group of companies, which are wholly-owned by Bank of America 
Corporation. Securities and Insurance Products: * Are Not FDIC Insured * Are 
Not Bank Guaranteed * May Lose Value * Are Not a Bank Deposit * Are Not a 
Condition to Any Banking Service or Activity * Are Not Insured by Any Federal 
Government Agency. Attachments that are part of this E-communication may have 
additional important disclosures and disclaimers, which you should read. This 
message is subject to terms available at the following link: 
http://www.ml.com/e-communications_terms/. By messaging with Merrill Lynch you 
consent to the foregoing.
--------------------------------------------------------------------------
Question On enq: PS - contention

Other related posts:
