SQL TRACE

[Manuel G]

Clay:

- We have reindexed database and review programming. We tkprof all the sqlcode structures to minimize CPU usage.

- We notice certain improve but it wasn't enough for resolve the whole problem.

- When database is performing poorly we can see with top all Oracle processes (ora"sid") on the top of the list. Top load averages grows up very fast but idle time doesn't goes down with same speed. We can have 5 or 6 load points and 80% idle. That?s make as thing to have an I/O problem but we are not sure. We are not either sure if we are suffering any problem with kernel parameters.

- We continue reviewing code to minimize accesing to database but the experience is we are not going to improve enough performance to resolve the problem.

Thanks a lot Clay.

[Magdi KAMAL]

Hi Manuel,

Please APPEND your comments and responses to your ORIGINAL question to allow to everyone to follow the discussion. Thanks alot.
Magdi

[Volker Borowski]

Manuel,

keep in mind to calculate statistics for tables and indexes after your created new indexes.

Check for buffer-qualities using utlbstat.sql and utlestat.sql.
Check for io and swaps with sar and/or vmstat.
Check for lock activity in V$LOCK.

Volker

[Steve Slade]

Manuel

What are the oracle processes that you see, (or saw) at the top of top? If they were something like

ora_lgwr_SID then it could be that you are generating a lot of redo, and could benefit from multiple redo log writer processes.

ora_dbwr_SID this it could be that you are making a large number of changes on the database, and you could benefit from multiple database writer processes - and maybe a larger SGA. (Depending on what is going on at the time.)

If the database is at fault, then the real answers will be in the utlbstat reports - although they take alot of deceiphering.

rgds

Steve