ORA-04031: unable to allocate 2097152 bytes of shared memory ("shared pool","unknown object","hash-join subh","QERHJ Hash Table Entries")
puroh...@gmail.com
ORA-04031: unable to allocate 2097152 bytes of shared memory ("shared
pool","unknown object","hash-join subh","QERHJ Hash Table Entries")"
We have already tried the following:
1. Flushing shared pool
2. Bouncing db
Bouncing does resolve the problem temporarily but then it comes back
again?
Size of the shared pool has been same throughtout but problem has
started to appear recently.
Any clues anyone?
sybr...@hccnet.nl
Posts without version should be avoided.
You are using bind variables?
--
Sybrand Bakker
Senior Oracle DBA
fitzj...@cox.net
You've run out of shared pool, apparently. Possibly Sybrand has hit
upon an area to address, however to get past this in the interim
you'll need to increase the shared_pool_size parameter, then restart,
unless you have sga_max_size (in 9i and later releases) set at which
point you can dynamically alter the shared_pool_size within the
confines of that maximum. You may need to change that parameter as
well, which will require a restart of the database.
David Fitzjarrell
Krish
You may need to increase the setting for SGA_TARGET to allow MMAN more
memory to manage behind the scenes if you experience ORA-04031 errors
in the Large Pool, Streams Pool, or Java Pool.
The Shared Pool is little more complicated to tune. Please refer
metalink article 396940.1 for more information to resovle this issue.
Krish
puroh...@gmail.com
Sorry...missed putting the version..its 9.2.0.8.
Also, we have recently implemented shared server configuration.
I was't a part of the implementation but are there any parameters that
can to be looked to verify that alls configured correctly and memory
allocation is proper.
-Atul
puroh...@gmail.com
Sorry...missed putting the version..its 9.2.0.8.
Also, we have recently implemented shared server configuration.
I was't a part of the implementation but are there any parameters that
can to be looked to verify that alls configured correctly and memory
allocation is proper.
On bind variables, we don't really have a buzy system this one, its
have very few user and running mostly stand alone queries.
Thanks for your inputs,
Atul
Brian Peasland
when you configured your shared servers, did you adequately size the
Large Pool at the same time?
HTH,
Brian
--
===================================================================
Brian Peasland
d...@nospam.peasland.net
http://www.peasland.net
Remove the "nospam." from the email address to email me.
"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown
--
Posted via a free Usenet account from http://www.teranews.com
sybr...@hccnet.nl
>Sorry...missed putting the version..its 9.2.0.8.
>Also, we have recently implemented shared server configuration.
>I was't a part of the implementation but are there any parameters that
>can to be looked to verify that alls configured correctly and memory
>allocation is proper.
>
>-Atul
As already noted, shared server requires the large pool to be
configured. If you don't configure the large pool, the shared pool
will be used for shared server purposes.
Please be aware any crystal balls required are usually out of order,
shattered, broken or all of them.
Jonathan Lewis
news:elied399l73aj422h...@4ax.com...
Sybrand,
When the error message tells you that you can't allocate memory
in the shared pool for a hash join, it's fairly obvious that the system
is 9i or earlier with shared servers configured but no large pool.
So when the OP offers an apology for failing to supply a version number
there really isn't any need to make snide comments about crystal balls.
--
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
puroh...@gmail.com
wrote:
> <sybra...@hccnet.nl> wrote in message
>
> news:elied399l73aj422h...@4ax.com...
>
>
>
>
>
> > On Thu, 30 Aug 2007 11:00:49 -0700, purohita...@gmail.com wrote:
>
> >>Sorry...missed putting the version..its 9.2.0.8.
> >>Also, we have recently implemented shared server configuration.
> >>I was't a part of the implementation but are there any parameters that
> >>can to be looked to verify that alls configured correctly and memory
> >>allocation is proper.
>
> >>-Atul
>
> > As already noted, shared server requires the large pool to be
> > configured. If you don't configure the large pool, the shared pool
> > will be used for shared server purposes.
> > Please be aware any crystal balls required are usually out of order,
> > shattered, broken or all of them.
>
> > --
> > Sybrand Bakker
> > Senior Oracle DBA
>
> Sybrand,
>
> When the error message tells you that you can't allocate memory
> in the shared pool for a hash join, it's fairly obvious that the system
> is 9i or earlier with shared servers configured but no large pool.
>
> So when the OP offers an apology for failing to supply a version number
> there really isn't any need to make snide comments about crystal balls.
>
> --
> Regards
>
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html- Hide quoted text -
>
> - Show quoted text -
Thank you all.
As you guys said, we did not have a large pool configured.
Now we have a large pool of 500mb and shared pool to 1 gig..that seems
to have solved the problem. For the time being at least. :)
-Atul