cancel
Showing results for 
Search instead for 
Did you mean: 

SQL TRACE

Manuel G
Frequent Advisor

SQL TRACE

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.
3 REPLIES
Magdi KAMAL
Respected Contributor

Re: SQL TRACE

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
Honored Contributor

Re: SQL TRACE

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
Frequent Advisor

Re: SQL TRACE

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
If at first you do not succeed. Destroy all evidence that you even attempted.