Question On enq: PS - contention
Date: Tue, 13 Jan 2009 15:11:17 +0530
Message-ID: <DFD964C24D30A74EB7C0438CFB7CA14005A8AA_at_mlmmb20mb051.amrs.win.ml.com>
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 13 2009 - 03:41:17 CST