RE: CURSOR_SHARING survey
Date: Thu, 24 Jan 2013 06:46:56 -0600
Message-ID: <F05D8DF1FB25F44085DB74CB916678E8856C2C8F13_at_NADCWPMSGCMS10.hca.corpad.net>
Sriram,
We're probably getting a bit off-thread here, but something to consider:
Bind Variables aren't always a good thing. Did you know that SQL's that use literals can get better plans (at the cost of more parsing) because each plan is specific to the SQL that is running?
With bind variables you can get bad plans when Query1 needs 10% of rows, and Query2 needs 90% of rows (as a high level example). The plan generated for Query1 will be the plan used by Query2 and if that is an index access->table lookup operation and you need 90% of the rows - well, that could be a very bad thing. The tradeoff here is you save space in the shared pool and you skip the parsing for the 2nd query.
(Anyone feel free to correct me on that - I could be way off base but I don't think so)
Also - when you say you like to keep environments the same, do you mean for different applications as well?
For example - let's say you had PeopleSoft on 10.2.0.4 and you had some home-grown .Net application with PL/SQL packages in another 10.2.0.4 database? Would you (in your environment) want those 2 databases setup the same for the sake of change management? I'm just curious - not casting stones.
Regards,
Chris
From: Sriram Kumar [mailto:k.sriramkumar_at_gmail.com]
Sent: Thursday, January 24, 2013 3:35 AM
To: Taylor Christopher - Nashville
Cc: oracle-l_at_freelists.org
Subject: Re: CURSOR_SHARING survey
Hi Chris,
cursor_sharing in 11gr2 is not recommended/supported any more. cursor sharing is a bad workaround that allows developers to write SQL's without bind variables. performance is then dba's responsibility.
from a change management perspective, I would like to keep all the environments same. have faced some bugs, cursor_sharing=force gave wrong results (in 10gr2). sure you would see them in metalink now also.
best regards
sriram kumar
On Wed, Jan 23, 2013 at 12:13 AM, <Christopher.Taylor2_at_parallon.net<mailto:Christopher.Taylor2_at_parallon.net>> wrote: I'm curious how many of you guys have set CURSOR_SHARING=FORCE from EXACT in a development or test database (or even production) and what led you to set it? My only experience with CURSOR_SHARING comes from my PeopleSoft days when SIMILAR was thought to be a "good" thing and then found out it wasn't (at least I believe that's the way it went).
I've never played around with FORCE.
The reason I'm asking is that we have 4 QA instances that share the same server and one of these QA boxes has the requirement to match production as far as memory pools, parameters etc.
This has the adverse effect of forcing the other 3 into smaller RAM buckets. I'm getting 4031 errors intermittently and [of course] it's related to non-shared sql blowing my sql area and library cache sizes all to he**.
(Keeping in mind this is a very immature application - much work going on in redesign - while that is happening though I really would like to keep the 4031 errors down to a minimum)
Regards,
Chris Taylor
Oracle DBA
Parallon IT&S
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu Jan 24 2013 - 13:46:56 CET