RE: SHARED_POOL_SIZE and CURSOR_SHARING

I'd imagine it depends on your SQL set. If your SQL statements are already using bind variables, then they are already shared and then I would think it doesn't make any difference what you set cursor_sharing too, but if you're running SQL with literals I'd think it could make a significant difference on the sharing and thus your shared pool requirements. Sorry, no real life experience testing it, but I'd think this would be a pretty specialized thing where you can't really apply a rule-of-thumb, it will depend on the size, complexity, and variations of your SQL statements, e.g. if you have 1000 versions of the same statement that are all the same except for a different literal value in the WHERE clause, then with cursor_sharing, those 1000 statements could be consolidated into one if I understand correctly (not sure, like I've said I haven't really tested it), so in this case you'd get a 99.9% reduction in size of your shared pool (if this were the only SQL in your pool, just for arguments sake), but if there were only 4 variations of the statement, then it would only be a 75% reduction. Someone please correct me if my line of thought is off here.  

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Denys van Kempen Sent: Thursday, September 15, 2005 9:18 AM To: Oracle-L_at_freelists.org
Subject: SHARED_POOL_SIZE and CURSOR_SHARING

List,

I was asked if the shared pool could be configured smaller with cursor sharing on. The argument is that there are less execution plans to store. Makes sense, however I've never seen this relation documented anywhere. Has anyone has come across some doc that explains the relation between the actual size of the shared pool and the cursor sharing facility?

Thanks

Denys

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

--

http://www.freelists.org/webpage/oracle-l Received on Thu Sep 15 2005 - 11:32:03 CDT