Resolving ORA-04031-problem
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
Re: Resolving ORA-04031-problem
Quote:
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...
Code:
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...
Re: Re: Resolving ORA-04031-problem
Thanks for your reply, Tarry
Quote:
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?