Re: Perf Tuning : Analyze table BACKFIRED!!

JKMalpani_at_Yahoo.com (JK_Yahoo) 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.
>
> Thanks,
> JK

JK, I agree with Ryan's two main observations: 1- Your sample size is way too small.
2- The hit ratio is not a reliable indicator of overall performance; poorly performing queries can drive the ratio up while driving overall performance down

HTH -- Mark D Powell -- Received on Mon Aug 18 2003 - 07:45:42 CDT