Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Possible to monitor shared pool in Oracle?

SOLVED
Go to solution
Gino Castoldi_2
Honored Contributor

Possible to monitor shared pool in Oracle?

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

5 REPLIES
Nicolas Dumeige
Esteemed Contributor
Solution

Re: Possible to monitor shared pool in Oracle?

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
All different, all Unix
Jean-Luc Oudart
Honored Contributor

Re: Possible to monitor shared pool in Oracle?

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
fiat lux
Jean-Luc Oudart
Honored Contributor

Re: Possible to monitor shared pool in Oracle?

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
fiat lux
Printaporn_1
Esteemed Contributor

Re: Possible to monitor shared pool in Oracle?

Hi TIA,

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

HTH
enjoy any little thing in my life
Yogeeraj_1
Honored Contributor

Re: Possible to monitor shared pool in Oracle?

hi Gino,

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

Did you check your statspack report?

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)