Database running very slow

[Sharma]

Oracle 817 on Sun Solaris

Today morning my users told about Database performing very slow, I querried few tables/views but not much accomplished.
Can you explain me

What steps should I take? and in what sequence to deal with recurring issue.

Thanks!

[gandolf989]

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.

[omegamark]

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

[Sharma]

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.

[mallard]

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.

[Sharma]

Lets be specific, since application is already in production so first 2 options are more or less ruledout. Infact second option is workable.

How to go ahead with memory tuning? What steps / querries can be peroformed for memory tuning.
Similary for disk IO and contention.

Thanks!

[tamilselvan]

1 Run top in unix to check which process consumes more memory.

2 Run vmstat to check paging is taking place at the OS level.

3 Kill unwanted sessions at unix and oracle level.

4 ALTER SYSTEM FLUSH SHARED_POOL;

5 Run utlbstat and utlestat and verify all the statistics.

6 If you can't figure it out the reason, shutdown oracle and unix and restart the box. If you experience slowness in the system, run sql trace at session level and take tkprof report.

[mallard]

I would check all that tamilsevan suggests, and possibly these others too:

* readhitratios
* check total system waits with v$system_event, v$session_event, and v$session_wait
* check v$latch for latch contention

[thushara]

FIRST OF ALL CHECK WHETHER YOU DO TABLE INDEXING