Re: Perf Tuning : Analyze table BACKFIRED!!

your sample size is way too small. standard is 20% sample. you should be using dbms_stats and make sure you gather histograms.

Are you using an off the shelf product? Ive found that those often use a layered series of complex join views built on top of each other and are optimized for the rule based optimizer(they are antiquated and not updated).
Also, if you use the CBO you need all tables to be analyzed or else you will use the CBO even though some of your tables are not analyzed and get bad query plans.

have you looked at the individual query plans? you havent dug deep enough to see what is really going on. Also, tuning for the cache hit ratio is incorrect. Your cache hit ratio can drop and performance can increase.

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
Received on Mon Aug 18 2003 - 05:50:09 CDT