SQL & PL/SQL : When Your Query Takes Too Long

When your query takes too long and too slow, first of all you have to investigate the root caused of the problem, you have to know why it is slow

The tools at your disposal are, among more:
1. dbms_profiler
2. explain plan
3. SQL*Trace / tkprof
4. statspack

Use dbms_profiler if you want to know where time is being spent in PL/SQL code.
Statspack is a must if you are a dba and want to know what is going on in your entire database. For a single query or a small process, explain plan and SQL*Trace and tkprof are your tools.

explain plan in SQL*Plus you have to type:

explain plan for [your query];
select * from table(dbms_xplan.display);

When you get error messages or a message complaining about an old version of plan_table, make sure you run the script utlxplan.sql.

The output you get here basically shows you what the cost based optimizer expects. It gives you an idea on why the cost based optimizer chooses an access path.

SQL*Trace/tkprof

For this you have to type in SQL*Plus:
- alter session set sql_trace true;
- run your query
- disconnect (this step is important, because it ensures all cursors get closed, and "row source operation" is generated)
- identify your trace file in the server directory as specified in the parameter user_dump_dest
- on your operating system: "tkprof [trace file] a.txt sys=no sort=prsela exeela fchela"

The file a.txt will now give you valuable information on what has actually happened. No predictions but the truth.

By comparing the output from explain plan with the output from tkprof, you are able to identify the possible problem areas.

So before rushing into possible solutions, always post the output of explain plan and tkprof with your question and don't forget to post them between the tags [pre] and [/pre] for readability.