Even if you're right, the CD is safe.
"Richard Foote" <richard.foote_at_bigpond.com> wrote in message
news:ug50b.42372$bo1.17903_at_news-server.bigpond.net.au...
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
> news:3f40d606$0$3135$ed9e5944_at_reading.news.pipex.net...
> > "JK_Yahoo" <JKMalpani_at_Yahoo.com> wrote in message
> > news:7a58b2f6.0308172028.7e2960b3_at_posting.google.com...
> > > Little long chain of events, but little lost on what went wrong...
> > > Thanks!!
> > >
> > > Problem statement :
> > > * Production Oracle 8.1.7.4 DB of 90+ GB in size, 300+ tables
> > > * Overall performance is "survivable" to users, current DB health is
> > > OK, can be much better.
> > > * On closer review from tuning perspective, from Oracle Statspack
> > > and lots of queries across various tables with existing suitable
> > > indices are observed to be not utilizing indices.
> > > * Buffer Cache hit ratio was around 90%, but still select routine
> > > queries were using full table scans
> > > * Users were NOT complaining
> > > * A being monitored program had a throughput of 300 units per hour
> > > during business day
> > >
> > > * ACTION 1 : Suggested some table specific index and analyze of
> > > select tables,
> > > * monitored program throughput increased to 1000 units per hour
> > > during business day
> > > * Still many other queries continued to have problem, hence thought
> > > all tables may needed anlayze
> > >
> > > * ACTION 2 : for all 300 tables
> > > * "ANALYZE table_name FOR TABLE ESTIMATE STATISTICS 5 percent;"
> > > * oops..
> > > * few hours down into night and weekend, despite very less user
> > > and transaction activity on DB the monitored program throughput came
> > > down to 500 units per hour.
> > > * Data buffer cache hit ratio became volatile down FROM 90% TO
> > > range of 15-60.
> > > * Overall DB slowed down a LOT
> > >
> > > * Action 3 : : for all 60 medium to heavy transaction tables ( rows
> > > > 1000 ) executed
> > > * "ANALYZE table_name FOR ALL INDEXED COLUMNS ESTIMATE STATISTICS
> > > 5000 rows ;"
> > > * Some relief, but still open concerns
> > > * Many queries which after Action 2 were doing much better, as
> > > expected.
> > > * But Buffer cache still remained volatile between 20-70%, below
> > > earlier 90%
> > > * Even after ACTION3, some queries on those tables were still
> > > doing Table Scans
> > > * I have a fear, come Monday full business load, things are going
> > > to get worse.
> > >
> > > What went wrong? In past I have seen improvements in performance with
> > > other customer databases with similar to above actions, but it
> > > BACKFIRED on me this time. Any clues / suggestions / analysis is
> > > appreciated.
> >
> > What went wrong? Well action 1 seems to have been documented and thought
> > out, that is you started with a known slow process and some suggested
> > indexes. You carried out the action, updated stats and then measured
> > performance which was improved (3 times a good figure to quote to mgmt).
> > Action 2 seems to have been carried out on the basis that it seemed like
a
> > good idea at the time, action 3 was a similar attempt to sort out action
> 2.
> > So the big lesson to learn is not to make changes at random and without
> > testing.
> >
> > Where to go from here?
> > well if you had *never* previously collected stats then you can remove
all
> > stats and return to square one by deleting the stats. otherwise you
should
> > 1. Gather decent stats - I'd suggest dbms_stats.gather_schema_stats with
a
> > sample size as Ryan suggests of at least 20%. If you have skewed data
and
> > 2. Monitor what queries take a long time and look at optimizing those
> > queries first.
> > 3. Ignore ratios and FTS per se and concentrate on what takes the time.
> >
>
> Hi Niall,
>
> It's interesting that three people have now suggested a sample size of 20%
> with 5% being considered too small. Cause I like being different and in
the
> interests of starting a bit of a discussion I kinda disagree. I have found
> that in the vast majority of cases, 5% is actually more than a sufficient
> sample size. I would recommend to the OP to save the stats as they
currently
> exist, try the 20% sample (or bugger it and compute the lot) and then
> compare both the stats and the execution plans. Has the problem gone away
?
> I would wager my limited edition Aladdin Sane CD that the stats remain
> around the same or thereabouts and the plans likewise.
>
> That said, there are sometimes exceptions, although somewhat rare in my
> experience. Histograms, when needed could be the missing link, but that
> requires further investigation.
>
> If the OP is familiar with statspack, I would recommend suzing out what
are
> the most "expensive" statements listed and see if they provide any clues.
I
> would also strongly suggest looking at the worst wait events listed and
see
> what clues they provided. I would then trace a session that is
experiencing
> these performance issues (10046) and suz out what are the wait events and
> execution plans causing the grief here. The cause to the problems are
found
> here and hence so are the solutions ...
>
> It could well be that the problem with the database has nothing to do with
> the so-called slow statements (I'm not entirely sure what is meant by
> "units") but with an entirely different activity in the database that is
> sucking up all the resources, causing contention, etc. and indirectly
> causing other statements to slow down. This could have been caused by the
> indiscriminate introduction of stats.
>
> Once the problem has been narrowed down, then you can determine whether a
> larger stats sample, histograms, SQL tuning/hints, data striping, Bowie in
> the CD drive, etc. etc. might help.
>
> In summary to the OP, determine *what* is causing the waits, then *why*
it's
> causing the waits before you get into the *how* the hell you fix it. It
> could well be the sample size, let us know if my Bowie CD is safe ;)
>
> Cheers
>
> Richard
>
>
Received on Mon Aug 18 2003 - 17:00:15 CDT
