8.1.7.3 and the shared pool

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 - 12:25:05 CDT