| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: 8.1.7.3 and the shared pool
I wouldn't rely on CURSOR_SHARING as there is a number of bugs known,
and some caveats to know, with one major that it will rewrite queries you
normally wouldn't want it to. Also, there is a bug fixed in 8.1.7.4, namely
2104071, which may, or may not be relevant to your situation. Bug description
is as follows:
Bug 2104071 Fixed: 8174
a.. Resource Leaks (eg: Memory Leak) b.. Shared Pool c.. XML / XDK
Although "this is known to affect the XML parser', it may wellaffect other areas as well...
-- Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications. All opinions are mine and do not necessarily go in line with those of my employer. "Anton Buijs" <aammbuijs_at_xs4all.nl> wrote in message news:aentb1$hm1$1_at_news1.xs4all.nl...Received on Tue Jun 18 2002 - 13:36:56 CDT
> 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
>
 |
 |