ORA-04031: unable to allocate 2097152 bytes of shared memory ("shared pool","unknown object","hash-join subh","QERHJ Hash Table Entries")

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

purohitatul@gmail.com - 30 Aug 2007 06:55 GMT

We getting the following error in our database:
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?

Reply to this Message

sybrandb@hccnet.nl - 30 Aug 2007 07:25 GMT

>We getting the following error in our database:
>ORA-04031: unable to allocate 2097152 bytes of shared memory ("shared
[quoted text clipped - 9 lines]
>started to appear recently.
>Any clues anyone?

Posts without version should be avoided.
You are using bind variables?

Signature

Sybrand Bakker
Senior Oracle DBA

Reply to this Message

purohitatul@gmail.com - 30 Aug 2007 19:02 GMT

On Aug 29, 11:25 pm, sybra...@hccnet.nl wrote:

> >We getting the following error in our database:
> >ORA-04031: unable to allocate 2097152 bytes of shared memory ("shared
[quoted text clipped - 16 lines]
> Sybrand Bakker
> Senior Oracle DBA

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

Reply to this Message

Brian Peasland - 30 Aug 2007 20:59 GMT

> On Aug 29, 11:25 pm, sybra...@hccnet.nl wrote:
>>> We getting the following error in our database:
[quoted text clipped - 25 lines]
> Thanks for your inputs,
> Atul

when you configured your shared servers, did you adequately size the
Large Pool at the same time?

HTH,
Brian

Signature

===================================================================

Brian Peasland
dba@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

Reply to this Message

fitzjarrell@cox.net - 30 Aug 2007 14:24 GMT

On Aug 30, 12:55 am, purohita...@gmail.com wrote:

> We getting the following error in our database:
> ORA-04031: unable to allocate 2097152 bytes of shared memory ("shared
[quoted text clipped - 9 lines]
> started to appear recently.
> Any clues anyone?

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

Reply to this Message

Krish - 30 Aug 2007 16:35 GMT

On Aug 30, 12:55 am, purohita...@gmail.com wrote:

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

Reply to this Message

purohitatul@gmail.com - 30 Aug 2007 19:00 GMT

> On Aug 30, 12:55 am, purohita...@gmail.com wrote:
>
[quoted text clipped - 20 lines]
>
> Krish

Reply to this Message

sybrandb@hccnet.nl - 30 Aug 2007 23:56 GMT

>Sorry...missed putting the version..its 9.2.0.8.
>Also, we have recently implemented shared server configuration.
[quoted text clipped - 3 lines]
>
>-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.

Signature

Sybrand Bakker
Senior Oracle DBA

Reply to this Message

Jonathan Lewis - 31 Aug 2007 17:25 GMT

>>Sorry...missed putting the version..its 9.2.0.8.
>>Also, we have recently implemented shared server configuration.
[quoted text clipped - 9 lines]
> Please be aware any crystal balls required are usually out of order,
> shattered, broken or all of them.

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.

Signature

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

Reply to this Message

purohitatul@gmail.com - 31 Aug 2007 19:09 GMT

On Aug 31, 9:25 am, "Jonathan Lewis" <jonat...@jlcomp.demon.co.uk>
wrote:

> <sybra...@hccnet.nl> wrote in message
>
[quoted text clipped - 37 lines]
>
> - 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

Reply to this Message