ksandbergfl
Programmer
I am the de facto DBA of a smaller database (200 tables, 12GB total, data plus indexes). We are running 8.1.7.4 on a Sun Ultrasparc 450. We moved to this platform back in September, 2001, and not had any problems until the last 30 days.
Twice in the past month now, the CBO seems to lose its marbles... EXPLAIN PLAN's start returning huge numbers... indexes aren't being used... queries start running very slow. No indexes are "unusable", nor is there any other indication that anything is wrong (no alerts, no error messages). We use TOAD and Oracle DBAStudio, and neither app illuminated any serious problems. There is no warning -- all of the sudden our normally speedy database is as slow as a snail.
What seems to work is this -- we switch user sessions to use RULE based optmization... that buys us some time. Then,
overnight I run a script to rebuild all indexes and ANALYZE STATISTICS..COMPUTE on all tables. The next day, the optimization mode is back to "CHOOSE" and things are OK.
I also run a script that does an "ANALYZE STATISTICS...ESTIMATE" every night, with the idea to keep the stats up to date.
Do I need to rebuild my indexes every week? Every night? Are my indexes becoming too fragmented - do I need to drop the bigger ones and build them from scratch, maybe into different tablespaces? I have tablespaces that are 95% full (with auto-extend on) -- am I having extent problems?
Any ideas you could share would be much appreciated. If this problem occurs a third time, it could mean my job!
Keith
Orlando, FL
Twice in the past month now, the CBO seems to lose its marbles... EXPLAIN PLAN's start returning huge numbers... indexes aren't being used... queries start running very slow. No indexes are "unusable", nor is there any other indication that anything is wrong (no alerts, no error messages). We use TOAD and Oracle DBAStudio, and neither app illuminated any serious problems. There is no warning -- all of the sudden our normally speedy database is as slow as a snail.
What seems to work is this -- we switch user sessions to use RULE based optmization... that buys us some time. Then,
overnight I run a script to rebuild all indexes and ANALYZE STATISTICS..COMPUTE on all tables. The next day, the optimization mode is back to "CHOOSE" and things are OK.
I also run a script that does an "ANALYZE STATISTICS...ESTIMATE" every night, with the idea to keep the stats up to date.
Do I need to rebuild my indexes every week? Every night? Are my indexes becoming too fragmented - do I need to drop the bigger ones and build them from scratch, maybe into different tablespaces? I have tablespaces that are 95% full (with auto-extend on) -- am I having extent problems?
Any ideas you could share would be much appreciated. If this problem occurs a third time, it could mean my job!
Keith
Orlando, FL