But I need to know how do you start your investgation, what questions you ask and what you are more suspect for the cause of the problem. If you can give me one real life example that happens to you, I'll appreciate it.

Thanks..

12-22-2000, 11:00 AM

marist89

When they say their computer is slow, does that mean one application is slow or all their applications are slow?
Is one particular piece of the application slow?
What has changed on the database?
Are indexes missing?
Are all objects valid?
Trace the session, see what the Oracle can tell you about the trace output (tkprof).
Are other applications on the server performing OK?

12-22-2000, 11:30 AM

farrokhp

case

We had two different scenarios :

1. All applications slow from end-user point of view.
2. One of the application terribly slow not part of it.

What's your perscription ?

Also how can I trace the session and then use TKPROF?

12-22-2000, 11:38 AM

DBAtrix

Check to see if your stats have been collected if you're running cost-based optimizer--or if someone generated stats and you're *not* normally running CBO. Explain plan or TKPROF would likely reveal this as well.

if the app is suddenly slower, the problem is probably not the application code(unless it just changed) nor "fragmentation issues"--unless you had a recent veryveryvery heavy insert or update activities that could have caused freelist contention or row migration.

d.

12-22-2000, 11:44 AM

marist89

Once the user is logged in, get the serial# and sid from v$session. Then, from sqlplus as system, issue:
execute dbms_system.set_sql_trace_in_session(sid,serial#, true);

The trace information will then dump to where you have user_dump_dest defined. Then, from the OS, run:

tkprof tracefilename.trc output.txt

look at output.txt for information on all the queries that were running.

If all the users apps (MS Word, email, etc.) are slow, I would look at the client or the network. If all their db apps are slow and they were fine before, I would look at what changed on the server.