sundar

Tuning DB - Clarification

Discussion created by sundar on Mar 29, 2012
Latest reply on Apr 4, 2012 by Chris_Hackett
Hi,

we are in Clarity 12.1.1.1 and Oracle 10.2.0.4 as our DB.we are working on tuning our database server.CA had advised that to set the Optimizer mode CHOOSE and CURSOR_SHARING=FORCE. (Installation Guide -->DB Performance).
our DBA is not agreeing as there are oracle bugs related to it.so i want to know other users experience and of course CA experts answers too?.


1. Optimizer mode CHOOSE>>

Oracle does not support this settings in 10G.RBO was obsolete since oracle10.1. From Oracle10.2 the default optimizer mode is ALL_ROWS. It is cost base optimization. Please find below Meta link Doc
ID for the same.Rule Based Optimizer is to be Desupported in Oracle10g [ID 189702.1].
In CHOOSE based on query and statistics available for tables the it will pickup either ALL_ROWS(CBO) or RBO.

If that is the case then why It is recommended to set Optimizer mode CHOOSE.Does CA still have the Ruled based Optimizer in clarity?.
is there any reason behind it.


2. CURSOR_SHARING=FORCE >>
we have CURSOR_SHARING=EXACT.

we find an oracle bug related to it.
**********************************************************************************************************************************************************************************************************************************************
Might occur some other issues/slow responses if set the cursor_sharing=force. Please find the below Oracle Doc ID related to that.

Health Check Alert: Set CURSOR_SHARING to EXACT [ID 957431.1]

Bug 12345980 high parse time with cursor_sharing=force when session_cached_cursors set

Will increase the shared pool as suggested then monitor the system for a while then take a call is necessary to change the parameter to force in prod

Before implement this in prod need to implement in test followed by complete functional and performance testing.

**********************************************************************************************************************************************************************************************************************************************

Do any body have set the above 2 parameters and find it fruitful in Oracle 10G?.


cheers,
sundar

Outcomes