Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Suddenly slow performance - 8.1.7 on Solaris

Status
Not open for further replies.
ksandbergfl

ksandbergfl

Programmer
Jun 20, 2001
9
0
0
US
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
 
Sort by date Sort by votes
recommend reading an excellent book called :

'the art and science of oracle performance tuning'
christopher lawson,
curlingstone,
isbn 1-904347-01-0

i am half way through it and it is full of practical, down-to-earth advice which is more art than science ! it has changed my views on db tuning

not an immediate solution to your problem, i agree, but investment for (near ?) future and it may make you look like the boffin to the boss if you follow advice in book ...

good luck ...


Best of Irish Luck, David.

 
Upvote 0 Downvote
The answer to all your "index rebuilding" questions, can obtained only after you have some db statistics to review. You can see your index fragmentation level and then determine when to rebuild them.

The rule on tumb is that is the deleted index entries are 20% or more of the current entries in the index, then the index performance will benefit when rebuilt.

Here are two SQL(PL/SQL) scripts to help you out with the queries:


Seems you already know how to rebuild indexes.


Hope that helps,

clio_usa - OCP DBA
------------------
 
Upvote 0 Downvote
Sudden performance issues - could be server (O/S) related ?
 
Upvote 0 Downvote
We're having a similar problem running 8.1.7.3 on a Sun Cluster...

Suddenly things will just slow down and almost stop, normally the only way we've been able to sort it is to fail over to the other node of the cluster!
 
Upvote 0 Downvote
Spikmeister, the only thing I may suggest is to move to 9i ASAP. I beleive that your problem has nothing similar with Keith's one and is in fact "pure OPS issue" . We have the similar configuration as your and suffer from it during last 4 years. We can not change version due to the third-party requirements.

We had memory leak problem about 2 years ago after more or less stable work during 2 years (that in turne followed 3-month torture of initial deployment). Nobody could explain its origin but it was resolved by applying a patch not known even for our local Oracle support :)



Regards, Dima
 
Upvote 0 Downvote
I have found no hard-and-fast answer for our problem. We narrowed it down to one or two tables in the database, and we deleted the statistics for these tables (so queries against these tables always run using RULE-based optimization). I also created a schedule to rebuild all my indexes at least once per week.

We seem to have the problem under control now. Soon, though, we will be moving to Oracle 9i - I hope the cost-based optimizer works better in that version.

Good luck.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

George L
  • Locked
  • Question
web app not saving "all" records suddenly
Replies
3
Views
171
carp
carp
Server Guy
  • Locked
  • Question
Solaris 10 update 9 iso file
Replies
0
Views
58
Server Guy
Server Guy
Chopstik
  • Locked
  • Question
How to identify a package based on a code snippet 1
Replies
1
Views
147
Andrzejek
Andrzejek
Livia8
  • Locked
  • Question
Generate report/output on the basis of "enquiry" responsibilities only 1
Replies
16
Views
96
Livia8
Livia8
DTGMI
  • Locked
  • Question
Solaris 10 .. 9-10.iso FIle
Replies
2
Views
72
DTGMI
DTGMI

Part and Inventory Search

Sponsor

Back
Top