Home » RDBMS Server » Performance Tuning » Oracle 9i Performance and Tuning question
Oracle 9i Performance and Tuning question [message #243469] Thu, 07 June 2007 06:53 Go to next message
sk003
Messages: 16
Registered: November 2006
Location: atlanta,ga
Junior Member
1. If an application is currently running slowly, what is the best approach for resolving the problem assuming it is a database issue ?

1. If an application was running slowly last night, what is the best approach for resolving the problem assuming it is a database issue ?

Thanks.
Re: Oracle 9i Performance and Tuning question [message #243583 is a reply to message #243469] Thu, 07 June 2007 16:04 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. If an application is currently running slowly, what is the best approach for resolving the problem assuming it is a database issue ?
-- Look at V$SQL / V$SQLAREA for statements having large number of disk_reads/buffer_gets, join to V$SQL_PLAN to see the real execution plan anf tune these statements.
-- Check for WAITS/LOCKs as well.

1. If an application was running slowly last night, what is the best approach for resolving the problem assuming it is a database issue ?
-- Statspack must be already installed and executed at least once an hour (IMHO).

Michael
Re: Oracle 9i Performance and Tuning question [message #243585 is a reply to message #243583] Thu, 07 June 2007 16:26 Go to previous messageGo to next message
sk003
Messages: 16
Registered: November 2006
Location: atlanta,ga
Junior Member
Thanks.

Do you also recommend looking at v$session, v$session_wait, v$session_event etc for waits on resources?

From your experience, will you use Trace 10046 if a user is complaining about an application running slowly?
Re: Oracle 9i Performance and Tuning question [message #243590 is a reply to message #243583] Thu, 07 June 2007 17:34 Go to previous messageGo to next message
sk003
Messages: 16
Registered: November 2006
Location: atlanta,ga
Junior Member
Thanks for your help.
Re: Oracle 9i Performance and Tuning question [message #243626 is a reply to message #243469] Fri, 08 June 2007 02:08 Go to previous messageGo to next message
kamkan
Messages: 27
Registered: April 2007
Location: Chennai, INDIA
Junior Member
Hi,
There are some possibilities where u shall look at:
1. Jobs running at night
2. Backup
3. dba_waiters

Do you have your STATISTICS updated? else try to generate statistics using DBMS_STATS and check the performance. Also generate STATSPACK during the suspected hours for the period of 15mins.
Re: Oracle 9i Performance and Tuning question [message #243694 is a reply to message #243585] Fri, 08 June 2007 08:27 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member

Quote:
Do you also recommend looking at v$session, v$session_wait, v$session_event etc for waits on resources?

Yes> I advice you to do it, but AFTER you tuned your TOP 10/20 SQL statements.


Quote:
From your experience, will you use Trace 10046 if a user is complaining about an application running slowly?

Yes (if you can isolate/identify user's session).

HTH.
Michael
Previous Topic: 2 tables same rowcount different size
Next Topic: How do you determine db_cache_size
Goto Forum:
  


Current Time: Fri Dec 02 18:48:14 CST 2016

Total time taken to generate the page: 0.14438 seconds