Re: 8.1.7.3 and the shared pool

Sounds like your database is a candidate for using the (new) init.ora parameter CURSOR_SHARING=force.
Literal strings are replaced by bind variables before the statement is loaded in the shared pool. Later the literal values are binded. This could significantly reduce the number of different statements in the shared pool in your case.

Read Metalink note 94036.1 " Init.ora Parameter "CURSOR_SHARING" Reference Note".
Bugs are reported too! (It's a new feature you know).

Scott Gamble <zifnab_at_reddragon.org> schreef in berichtnieuws R1KP8.5617$Bz4.31080_at_petpeeve.ziplink.net...
| 8.1.7.3 + 4 one off patches
| Tru64 5.1
|
| Has anyone else had any experiences after upgrading to 8.1.7.3 (from 8.0.6
| in our case) that the shared pool needed to be tripled in size?
|
| The database in question is running a third party application that is
| murder on the pool, lack of bind variables lots of parsing etc., has been
| for 3 years so no change there. The concurrent usage has not changed
| either still about 600 concurrent users.
|
| Since the upgrade to 8.1.7.3 we have seen the number of 4031's rise
| significantly (8.0.6 was 0 ) started at 100 a week (we reboot the database
| once a week for backups and have been making shared pool adjustments) from
| about 5 weeks ago, to 4 this week. Over that same 5 week period we have
| tripled the size of the shared pool from 110m to 330m.
|
| Oracle support has been of little assistance in resolving this, other than
| telling us to continue increasing the pool, and that this is 'normal' for
| the upgrade (which I am having a hard time believing).
|
| It seems there have been a few reports of 4031 problems against 8.1.7.3
but
| none that seem to match what we have happening. The unfortunate thing
| about this is that the application is horrible at reporting errors,
| everything is a 'database error' or it just retries the statement which
| then suceeds so I don't have specific error messages to supply. We are
| basing our count of 4031's off the column request_failures in
| v$shared_pool_reserved which Oracle confirmed for me is the number
| of 4031's received by the application.
|
| Following a document on metalink and using v$shared_pool_reserved, the
last
| failure size is always less than _shared_pool_reserved_min_alloc which
| Oracle has not yet asked us to change, so we do have plenty of space in
| the reserved part of the pool just getting really fragmentted in the
normal
| part.
|
| I am just curious whether anyone else has seen this kind of issue as well,
| doesn't seem to fit into any known bugs but also doesn't quite feel right.
|
| Scott Gamble
Received on Tue Jun 18 2002 - 13:16:02 CDT