RE: Would you increase the shared pool? --URGENT

Hi Pablo,

Your shared pool looks like it's been flushed recently, if not repeatedly, so the shared pool stats are deceptive. The latching stats suggest that the shared pool is indeed too big, the library cache get hit ratio is poor.

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/
@   http://www.christianity.net.au/

-----Original Message-----
Sent: Saturday, 19 May 2001 0:16
To: Steve Adams; Multiple recipients of list ORACLE-L

Hi Steve,

How can you say that the shared pool is too big in this case?

According to your book ,I've done this:

If this ratio (Flushed Chrunks/Pins and releases ) is more than 1 in 20 then the shared pool is probably too small. And if transient chunks is more than 3 times recurrent chunks it's probably too big.

655389/5351916=0,12 1/20=0.05
0.12 > 0.05 then the shared pool is too small !!!

Did you look at free lists 0 and 1 ?
I think that they are not too big, am I right? If they were big this would indicate that tha shared pool is fragmented, and that would indicate that the shared pool is too big, right?

So, according to this thea shared pool is small. Please help me with this. thanks

And how can you say that it is parsing to frequently? What did you look at?

did you look at these ratios?
> library cache get hit ratio 91
> SQL AREA .73288057 .97035171 47011 13891
And at last, what can I do about synonyms (from a database point of view).

Thanks for you help, Steve

• Steve Adams <steve.adams_at_ixora.com.au> escribió: > Hi Pablo,
  >
  > Your shared pool is too big, not too small, and you
  > are parsing too frequently
  > (probably some literal SQL). Your use of synonyms is
  > an exacerbating factor.
  > Your reloads are due to the invalidations. They do
  > not indicate that your shared
  > pool is too small.
  >
  > Of course, you should worry about the major issues
  > first.
  >
  > @ Regards,
  > @ Steve Adams
  > @ http://www.ixora.com.au/
  > @ http://www.christianity.net.au/
  >
  >
  > -----Original Message-----
  > From: Pablo ksksksk [mailto:p_rodri99_at_yahoo.es]
  > Sent: Friday, 18 May 2001 2:01
  > To: Multiple recipients of list ORACLE-L
  > Subject: Would you increase the shared pool?
  > --URGENT
  >
  >
  > Hello Gurus,
  >
  > I need help in this performance issue.
  >
  > Oracle 7.3.4
  > HPUX 10.20
  >
  > I 've got this ratios:
  >
  > BC hit ratio 98
  > dictionary cache hit rate 100
  > library cache get hit ratio 91
  > library cache pin hit ratio 98
  >
  > Mayor waits are enqueue (57%), buffer busy
  > waits(34%),
  > especially p3=0 for some big tables (buffer being
  > read
  > from disk), and latch free (6%).
  >
  > I'm dealing with enqueue and bbw, but what would you
  > do with the low LC ratio and the shared pool in this
  > case.
  >
  > Latch free waits details
  >
  > LATCH TYPE IMPACT SLEEP RATE LATCH
  > ---------
  > shared pool 123285 1.05% 27097
  > library cache 58841 0.06% 103
  > cache buffers chains 18108 0.00% 0
  >
  >
  > This is the library cache stat:
  >
  > NAMESPACE GETHITRATIO PINHITRATIO RELOADS INVALIDAT
  > ----------------------------------------------------
  > SQL AREA .73288057 .97035171 47011 13891
  > TABLE/PROCEDURE .99150783 .99813894 11758 0
  > BODY .9975282 .9971979 32 0
  > TRIGGER .99816161 .99591602 61 0
  > INDEX .03401361 .02267574 5 0
  > CLUSTER .44444444 .33333333 0 0
  > OBJECT 1 1 0 0
  > PIPE .99999414 .99999415 0 0
  >
  >
  > The keep object summary
  >
  > TYPE OBJECTS KEPT RELOADS
  > OWNERS
  > -------------- --------- --------- ---------
  > ---------
  > TABLE 389 0 1661
  > 12
  > SYNONYM 348 0 2574
  > 4
  > PACKAGE 294 293 131
  > 4
  > PACKAGE BODY 292 291 23
  > 4
  > TRIGGER 220 220 52
  > 9
  > VIEW 170 0 373
  > 3
  > SEQUENCE 108 108 131
  > 11
  > NON-EXISTENT 68 2 153
  > 8
  > PROCEDURE 60 60 47
  > 2
  > FUNCTION 16 16 4
  > 1
  >
  > PARAMETER VALUE USAGE
  > ---------------------- ----- -----
  > session_cached_cursors 100 81%
  > open_cursors 500 85%
  >
  >
  >
  > Shared Pool
  >
  > RECURRENT TRANSIENT FLUSHED PINS AND ORA-4031
  > CHUNKS CHUNKS CHUNKS RELEASES ERRORS
  > --------- --------- --------- --------- ---------
  > 3051 5374 655389 5351916 0
  >
  >
  >
  > Shared pool free lists
  >
  > BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZE
  > BIGGEST
  > --------- ---------- ----------- ------------
  > ---------
  > 0 79552 1707 46
  > 72
  > 1 45760 516 88
  > 96
  > 8 9480 1 9480
  > 9480
  > 9 41165488 1797 22907
  > 32776
  > 10 48335568 1093 44222
  > 65424
  > 11 20518744 242 84788
  > 130072
  > 12 4383200 26 168584
  > 246496
  > 13 807072 2 403536
  > 447040
  >
  >
  > TIA
  

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steve Adams
  INET: steve.adams_at_ixora.com.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).