Hi Pablo,
A modest reduction in the shared pool will help because it will reduce some of
your latching problems. A severe reduction would of course be harmful as you
would age out important SQL. The real solution to this problem is to get rid of
your literal SQL. But that can be difficult, so an interim modest reduction in
the shared pool size is a good idea.
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-----Original Message-----
Sent: Saturday, 19 May 2001 1:06
To: Steve Adams
Yes Steve you're right, I didn't noticed a cron entry
that flushed the shared pool on wednesday's night.
This report is from thursday morning.
So this is why the shared pool statistics are not
representative of what's really going on. Thanks
I will remove this flush and recheck the statistics
Thanks!
But there's still something I don't understand, the LC
get hit ratio is poor, if I reduce the shared pool it
should get worse, shouldn't it?, The LC would be
smaller.
(althought this would of course improve shared pool
latch hit ratio)
thanks again
- Steve Adams <steve.adams_at_ixora.com.au> escribió: >
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-----
> From: Pablo ksksksk [mailto:p_rodri99_at_yahoo.es]
> Sent: Saturday, 19 May 2001 0:16
> To: Steve Adams; Multiple recipients of list
> ORACLE-L
> Subject: RE: Would you increase the shared pool?
> --URGENT
>
>
> 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
> > --------- ---------- ----------- ------------
> > ---------
>
=== message truncated ===
Do You Yahoo!?
Yahoo! Messenger: Comunicación instantánea gratis con tu gente -
http://messenger.yahoo.es
--
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).
Received on Fri May 18 2001 - 11:00:12 CDT
