Hi Christopher, thanks for answering
>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.
I'm doing exactly this, mostly of my waits are data
block waits and p3=0, so there's no much I can do,
anyway I'll try increasing buffer cache size.
>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.
This is the first thing I'm trying to do.
>3. What is the block size of this database?
8KB Oracle 7.3.4
>4. I think possibly problem is not further sizing
>the sp but avoiding
>fragmentation by pinning large procedures and
>packages at startup.
I'll do that. Althought I'm pinning everything that
moves (with some Steve Adams scripts), I'll identify
"large procedures" to pin.
>5. Do the statements use literals or bind
>variables. Are objects being
>reloaded because of different syntax or because of
>invalidations and aging?
I really don't know if the application uses bind
variables (I think it does). I think that this high
parsing might be due to a lot of ad hoc queries. I'm
running a script right now to collect data from v$sql,
to see if these reloads are due to different syntax
for the same SQLs.
Is there another way to check this ?
TIA
> --- Pablo ksksksk <p_rodri99_at_yahoo.es> escribió: >
> Fecha: Tue, 29 May 2001 22:19:09 +0200 (CEST)
> > De: Pablo ksksksk <p_rodri99_at_yahoo.es>
> > Asunto: RE: Would you increase the shared pool?
> > --URGENT
> > Para: Steve Adams <steve.adams_at_ixora.com.au>
> > CC: "ORACLE-L @ Fatcity" <oracle-l_at_fatcity.com>
> >
> > 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 <steve.adams_at_ixora.com.au>
> 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:p_rodri99_at_yahoo.es]
> > > 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 <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
> > >
> > === message truncated ===
> >
> >
> >
>
> > Do You Yahoo!?
> > Yahoo! Messenger: Comunicación instantánea gratis
> > con tu gente -
> > http://messenger.yahoo.es
> >
>
>
>
> Do You Yahoo!?
> Yahoo! Messenger: Comunicación instantánea gratis
> con tu gente -
> http://messenger.yahoo.es
>
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: p_rodri99_at_yahoo.es
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 Wed May 30 2001 - 14:14:43 CDT
