Please give me your systematic approach as an oracle/computer expert in this issue :
If you are working in a company and all of the sudden, one or many of the end-users come up to you and say
my computer is too slow what steps normaly you follow to find the bottleneck.
I know there is bunch of different things can involve for instances :
Network traffic in LAN
Application sql out of tune
Operating system memory shortage / swapping / lock
Oracle server intensive operation by other users
Oracle server fragmentation issues
and many ..many more
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.
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?
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?
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.
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.