I had a similar situation. I had Oracle 817 on sun Solaris 8. The database started running slow when I analyzed the schemas. I returned to normal when I deleted the stats. I find that rule optimization works better on Solaris.
If that isn't it it would be helpful to know how long it has been running slow and what has changed recently.
Is it all of a sudden or We had the same performance but now we want to improve the performance ??
what optimizer_mode u r using ??
when was the last time you had analyzed all tables and indexes
did you see the sql statements which has high disk read and buffer_gets ?? are they using the indexes??
any of the setting has been changed lately
sort_area_size/db blocks /log buffer
How long(days) has the database running so far
The database start behaving slow from time to time, this has happened quite a few times now,
I am more interested in finding out is their sequence of steps to be taken to findout what is currently going on in the Database.
Something like, get all the SQL stmt. currently executing
How much disk I/O etc.
Is their any article/white papers on this topic.
The sequence of steps that Oracle recommends for performance tuning are as follows:
1. Database Design (probably a little too late for this since the app is up and running)
2. Check Application SQL Stmts
3. Memory Tuning
4. Disk I/O
5. Internal Memory Structure Contention
Inside each of these is a lot of area to cover. I would suggest you start first with app SQL stmts, if possible to make chgs there. You'll get the most "bang for your buck" at that level. After that, proceed through the list. Anymore detailed questions about each, just ask.