Possible to monitor shared pool in Oracle?

[Gino Castoldi_2]

Hi,

Mgmt Svr:
Oracle 8.1.7.4 OVO 7.19 NNM 6.2 HPUX 11.0
MC/SG 11.09

We have had several ORA-04031 issues and we would like to somehow monitor the Oracle "shared pool" so we can be alerted before we have any problems. Can this be done in a script?

ORA-04031: unable to allocate 4200 bytes of shared memory ("shared
pool","DATABASE","sga heap","state objects")


10 points to any good answer.
TIA, Gino

[Nicolas Dumeige · Accepted solution]

Hello,

Take a look at metalink.oracle.com Note:174555.1
UNIX: Determining the Size of an Oracle Process

From the OS point of view :
ipcs -a
glance ...

From the Oracle point of view :
rem -----------------------------------------------------------------------
rem Filename: sga_free.sql
rem Purpose: Reports free memory available in the SGA
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

select name,
trunc(sgasize/(1024*1024)) "Allocated (M)",
trunc(bytes/1024) "Free (K)",
round(bytes/sgasize*100, 2) "% Free"
from (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f
where f.name = 'free memory'
/

If you question was about the automation of the ORA error in the alert.log, check attached script.

Nicolas

[Jean-Luc Oudart]

Hi Gino,

got the pb myself a few month ago.
I replied in a previous thread on the subject.
cf . thread :
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=479267

Usually, the 2 main possibilities are :
- shared memory is fragmented or
- shared meory is too small
(or a combination of these 2 !)

The SQL script has been a good help for us


Regards,
Jean-Luc

[Jean-Luc Oudart]

Sorry Gino,
did not realise that thread was yours !

if you cron the SQL script and check for a limit on "Percentage Utilized" before you send an automatic email.

just a suggestion.

Regards,
Jean-Luc

[Printaporn_1]

Hi TIA,

Aside from increase sharepool.
Pining large package and using MTS (shared server) will help a lot on this issue.

HTH

[Yogeeraj_1]

hi Gino,

Maybe that your sql are not using bind variable statements at all.

Did you check your statspack report?

regards
Yogeeraj