Re: SHARED_POOL_SIZE and CURSOR_SHARING

On 9/15/05, Allen, Brandon <Brandon.Allen_at_oneneck.com> wrote:
>
> 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.
>

The line of thought seems about right. I have a couple of observations though.

1. You can certainly get different cursors with cursor_sharing set to SIMILAR for essentially the same sql statement if Oracle decides that it isn't safe to share the cursor for the condition that the literal is on. (The one statement for the thousand describes c_s=force better) Thus the reduction in size may not be quite as large as anticipated.
2. If in fact your existing app suffers from thousands and thousands of non-shareable statements of the kind you describe, it probably makes sense to keep the shared pool fairly small anyway - there is no sense trudging through hundreds of mb of ram looking for a statement that isn't going to exist anyway.

These two factors together suggest to me that *for the same application* the shared pool size with CURSOR_SHARING=SIMILAR and that with CURSOR_SHARING=EXACT probably ought to be fairly similar in size.

I'd not be looking for a huge reduction the the shared pool - but hopefully at least some benefit in increased CPU capacity to do useful work.

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com

--
http://www.freelists.org/webpage/oracle-l