ORA-04031 - Whats happening.

[JMac]

I've created several 8.1.5 databases (on WIn NT4) using the Creation Wizard but on two of them I occasionally get this message:


Error accessing package DBMS_APPLICATION_INFO
ERROR:
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","BEGIN
DBMS_APPLICATION_INFO....","PL/SQL MPCODE","BAMIMA: Bam Buffer")

What could be different in these databases to cause this?

[jmodic]

Probably your shared pool became too fragmented to find enough contiguous space to load DBMS_APPLICATION_INFO package. Few options that you have to avoid this:

- Use bind variables in your applications (if they are not used allready)
- Pin larger packages (the ones you actaully use) immediately uppon database startup
- Increase your shared pool (if it's not unreasonably large allready)
- ALTER SYSTEM FLUSH SHARED_POOL; when you encounter ORA-4031 (this is more of a temporary workaround than a real sollution)

[padmam]

Hi,

I faced simillar error in the Past....Bamima Buffer...

Its related to Shared Pool Size....

We had a Package of file size 385 KB.We spilt that in to several Smaller Packages(7 or 8 rather as one Package to compile) of the file size as per oracle standard not exceeding 50 KB.

There is Solution 2 ways either increasing the Shared Pool Size or Properly reorganizing the Package affecting the Shared Pool taking the total resource to compile as one Package.

Cheers

Padmam

[pando]

how about increase shared pool reserved size?

[marist89]

There's also bugs in versions before 8.1.7.2 that can cause this error...

[JMac]

But I got these errors when starting up SQL*Plus. No application or packages were executing.

Is it a SHARED_POOL issue then? Which begs me to ask: How do you properly estimate the size of the shared pool?

(And how can I tell if an application uses bind variables?)

[padmam]

Hi,

Since you say some of the other db created simillarly didnt have problem...

1)Can you kindly have check whether all have Same Patch Sets Installed

Since as you have told this Problem is arising when you login this issue may not be related with Increasing shared pool Size at all.

Also read this Detailed document in Metalink with seacrh word 146599.1 ....Throws more high light on the same error...

Cheers

Padmam

[nagarjuna]

Originally posted by JMac
But I got these errors when starting up SQL*Plus. No application or packages were executing.

Is it a SHARED_POOL issue then? Which begs me to ask: How do you properly estimate the size of the shared pool?

(And how can I tell if an application uses bind variables?)

Your database configured for MTS???

[JMac]

Nope - all are at same patchset.
And Nope - No MTS.

Will check out the metalink doc, Pando, thanks.

Can I repeat the question about checking if SQL uses bind variables?

[nagarjuna]

Originally posted by JMac
Nope - all are at same patchset.
And Nope - No MTS.

Will check out the metalink doc, Pando, thanks.

Can I repeat the question about checking if SQL uses bind variables?

Then it is a shared_pool issue. Your shared pool is fragmented very much. Try reducing shared_pool as much as possible, but make sure that your library cache hit ratio does not fall below the required level (It depends on your SLA). It means, you need to have down time to change shared_pool parameter.

About bind variables.. Using bind variables and cursor_sharing=force will increase the probability of shared SQLs. That is, SQLs are not reloaded again. They are found in memory.