Resolving ORA-04031-problem

[efrijters]

Hello,

On a production-server there's a db (Oracle 8.1.7.1.1) that has ORA-04031 errors when you connect to it.

Flushing the shared pool didn't do the job. Installing package DBMS_SHARED_POOL (to pin objects in memory) failed also because of the ORA-04031 error.

The application running on the db is the accounting software "Exact".

The db is also in MTS-mode and I don't think it needs it (maximum number of concurrent users: less than 100!)
Should I remove MTS?

I want to resolve the ORA-04031 error by increasing SHARED_POOL_SIZE.

Here are the current values:

SHARED POOL : 50 MB
BUFFER CACHE : 82 MB
LARGE POOL : 15 MB
JAVA POOL : 20 MB

TOTAL SGA : 167 MB

MAX CONCURRENT USERS : 700
SORT AREA SIZE : 64 KB

CURSOR_SHARING : EXACT
CURSOR_SPACE_FOR_TIME : FALSE
OPEN_CURSORS : 300
SESSION_CACHED_CURSORS : 0

I think of altering these values into:
SHARED POOL : 80 MB
LARGE POOL : 25 MB
JAVA POOL : 10 MB
MAX CONCURRENT USERS : 200

I have some 290MB free physical memory, but there's a second (more important db on that server)

I'll remove 10MB of memory from the Java pool and add this to the large pool. Java isn't in use here, I guess...(see the V$VGASTAT below)

I read many articles about CURSOR_SHARING (Yes, I Read The Fantastic Manual!), but I can't find any conclusive evidence to alter it into 'FORCE'. Doing so could introduce new problems and being it a production database, I don't want any extra problems...
Do you agree on this?

And one last thing: I entered the on line learning course on 'tuning the shared pool'. The mentor said that any production database starts with a shared pool with the size of at least 150 MB! I'm not anything near of that size.
Is the Oracle mentor right about this?

I hope you guys can reflect on my questions. (Sunday morning I'll have the alter the system on my own, without the help of any experienced dba. So some confirmation of things would be great!)

Thanks in advance,
Erik
very junior ocp-wannabe



V$VGASTAT:
POOL NAME BYTES
----------- -------------------------- ----------
fixed_sga 75804
db_block_buffers 86294528
log_buffer 66560
shared pool free memory 15173612
shared pool miscellaneous 30766424
shared pool VIRTUAL CIRCUITS 275752
shared pool PL/SQL DIANA 312460
shared pool fixed allocation callback 640
shared pool PX msg pool 88508
shared pool PLS non-lib hp 2096
shared pool table columns 16860
shared pool PX subheap 6176
shared pool sessions 366520
shared pool enqueue_resources 129024
shared pool State objects 247360
shared pool joxs heap init 4248
shared pool db_files 370988
shared pool KQLS heap 92532
shared pool dictionary cache 168484
shared pool KGFF heap 6552
shared pool KGK heap 17556
shared pool trigger inform 460
shared pool message pool freequeue 124552
shared pool library cache 5387548
shared pool db_block_buffers 1432624
shared pool sql area 1261708
shared pool db_block_hash_buckets 299784
shared pool PL/SQL MPCODE 19744
shared pool transactions 166804
shared pool event statistics per sess 584800
large pool free memory 13230040
large pool session heap 2498600
java pool free memory 20606976
java pool memory in use 364544

[Tarry]

Originally posted by efrijters
Hello,


I think of altering these values into:
SHARED POOL : 80 MB
LARGE POOL : 25 MB
JAVA POOL : 10 MB
MAX CONCURRENT USERS : 200

How did you decide on those figures?

Try just increasing the large pool and then see...
execute this as well...

SELECT NAME, SUM(BYTES) 
FROM V$SGASTAT 
WHERE POOL='LARGE POOL' 
GROUP BY ROLLUP (NAME);

That'll show free space...

That 150mb shared pool is new to me, it all depends...

[efrijters]

Thanks for your reply, Tarry

Originally posted by Tarry
How did you decide on those figures?

Try just increasing the large pool and then see...
execute this as well...

Well, the size of the shared pool (80 MB) came from the other production db. Going from 50MB to 80MB is a great step for this db. It runned very well up to now... It's been up since november 2002.

Running the statement results in:

1 SELECT NAME, SUM(BYTES)
2 FROM V$SGASTAT
3 WHERE upper(POOL)='LARGE POOL'
4* GROUP BY ROLLUP (NAME)
SQL> /

NAME SUM(BYTES)
-------------------------- ----------
free memory 13181680
session heap 2546960
15728640

Okay, I have 15MB free memory, but I'm afraid that it's fragmented. Why would I otherwise receive those ORA-04031 errors?

Any suggestions?

[Tarry]

[1] What follows after the ora-04031 error? Any erroneous message etc.
Post all of it here?
[2] It's most porbably the "exact" application which is doing a lot of work(in hard parsing etc) that's causing the mem problem.
[3] Your idea is apparently to pin those large queries or blocks in memory but you might as well take a look into using bind variables.

Post the error code with details then we can see what might/can be done here.

[gbrabham]

You might also want to include this undocumented init.ora setting
... You can find this on Metalink ... It made a world of difference
for 1 of my clients:

#### Work around for BAMIMA Buffer (4031) errors ####
_db_handles_cached = 0

Gregg

[Tarry]

And BTW that MTS option for users less than 100 is a weird decision IMHO. Put it back to dedicated.

[efrijters]

Originally posted by Tarry
[1] What follows after the ora-04031 error? Any erroneous message etc.
Post all of it here?
[2] It's most porbably the "exact" application which is doing a lot of work(in hard parsing etc) that's causing the mem problem.
[3] Your idea is apparently to pin those large queries or blocks in memory but you might as well take a look into using bind variables.

Post the error code with details then we can see what might/can be done here.

The error message directly after connecting to SQLPLUSW:
ORA-04031: unable to allocate 4200 bytes of shared memory ("shared pool","unknown object","sga heap","state objects")

There's no message after this. The connection succeeds though. I think the error is the result of SQLPLUSW selecting the version information that's being displayed as SQLPLUSW starts. This also happens when starting with "SQLPLUSW /NOLOG".

The "Exact" software is a well known accounting solution in The Nehterlands. I have no way of altering the programm or the sql-statements.

I just logged in and ... the error is gone! Probably because everyone's gone home... So, the higher the amount of concurrent users, the higher risk of running into the ORA-04031...

To Gregg: I saw this undocumented init.ora setting in some documents. I too had the BAMIMA error a couple of times. I'll read the articles on Metalink and decide on what to do.

Tarry, Gregg: Thanks sofar!!

I'll post the result as soon as I bounced the db and when I get some feedback from the users!!

[efrijters]

Last night I had to stop the NT Service of the database, because there was no way to log in: ORA-00604 and ORA-04031's kept me from starting svrmgrl or sqlplus.

After altering the shared_pool_size, shared_pool_reserved_size, large_pool_size and some other parameters that were due for altering, I restarted the database via svrmgrl and it works!!

No more ORA-04031's!!

I want to thank everyone for their support!

I just hope I can return the favour someday.

Erik