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 Sun Aug 17 2003 - 23:28:38 CDT
