1. I would check to see if the buffer busy waits are on the same
file/block. Perhaps the same object is being hit constantly. I would also
base my action on the type of block being waited on.
2. Tuning the redo generation would be a good thing, specially one of that
size. I would recommend two raid 0 arrays of 3-6 disks each with quick io /
raw devices. 3gb is fine, I have never had to work with a monstrocity of
that, but I have heard 2gb+ redo logs a few times. That is a constant
800Kb/s second sustained.
3. What is the block size of this database?
4. I think possibly problem is not further sizing the sp but avoiding
fragmentation by pinning large procedures and packages at startup.
5. Do the statements use literals or bind variables. Are objects being
reloaded because of different syntax or because of invalidations and aging?
"Walking on water and developing software from a specification are easy if
both are frozen."
Christopher R. Spence
Oracle DBA
Fuelspot
-----Original Message-----
Sent: Tuesday, May 29, 2001 5:24 PM
To: Multiple recipients of list ORACLE-L
Hi Steve and List,
I'm still having some problem to understand this data.
I've taken Steve Adams advise to reduce Shared Pool. I
reduced it from 200MB to 180MB. And off course, this
reduction has impacted in SP latch hit ratio. It
sleeps rate has improved from 0.88% to 0.55%.
But I still have some opposed data.
Here I past my wait events:
EVENT TIME_WAITED
----------------------------------------------------
enqueue 1780156
buffer busy waits 511713
log buffer space 103542
latch free 71181
write complete waits 35970
free buffer waits 5246
library cache pin 4797
log file switch completion 1678
library cache load lock 392
row cache lock 35
Shared Pool Info
RECURR TRANSIENT FLUSHED PINS ORA-4031 LAST ERROR S.
------------------------------------------------------
6531 11538 260744 3555435 0 0
BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZE BIGGEST
------------------------------------------------------
0 100528 2098 47 72
1 70904 759 93 136
2 223576 1252 178 224
3 384 1 384 384
4 6456 10 645 808
5 218000 151 1443 2048
6 185536 64 2899 3968
As far as I can see, transient list is not bigger than
tree times recurrent list (so SP is not too big) and
flushed chunks/pins and releases is more than 1/20
(0.07). This posible indicates that the shared Pool is
too small.
Besides, "library cache load lock" event indicates
that a lot of SQLs (or other objects) are being loaded
to the LC, this may also indicate that the SP is
small.
But on the other hand, We've got that list 0 (of the
shared pool) is probably too big and that would
indicate that the shared pool is too big.
what am I missing here?
I know I've got more serious trouble with enqueue and
bbw events, I'm trying to resolve them. That's no
problem.
I'm also aware that "log file switch completion" event
is causing a lot of trouble here and I'm trying to get
some disks to place redo logs and make them bigger.
By the way, I'm going to create redo logs of 3GB each
in order to get a switch every hour. Is 3GB some
reasonable size for redo?
TIA
--- Steve Adams <[EMAIL PROTECTED]> escribió: >
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-----
> From: Pablo ksksksk [mailto:[EMAIL PROTECTED]]
> Sent: Saturday, 19 May 2001 1:06
> To: Steve Adams
> Subject: RE: Would you increase the shared pool?
> --URGENT
>
>
>
> 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 <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]]
> > 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 <[EMAIL PROTECTED]>
> 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:[EMAIL PROTECTED]]
> > > 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
>
=== 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: =?iso-8859-1?q?Pablo=20ksksksk?=
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christopher Spence
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
