When your query takes too long to execute ...

What to do when your query is too slow? Why it is slow. What is the real cause of your problem?

If the reason why is not known, suggesting to rewrite the query, or hinting the query, suggesting parallellization et cetera is not very productive. Once in a while you may get lucky. But even then you have to realize that if your problem seems "solved", but you don't know why, nothing guarantees you that the problem won't come back tomorrow. So the first step should always be to investigate the root cause.

The tools at your disposal are, among more:
- dbms_profiler
- explain plan
- SQL*Trace / tkprof
- AWR
- SQLT report

dbms_profiler:

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.

Click here to read more about dbms_profiler and its issues...

Explain Plan:

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to run the statement.

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.

Click here to read more about explain plan...

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> q_out.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.

Click here to read more about SQL*Trace and tkprof...

Analyze AWR report:

As you have Generated AWR Report in Oracle, Next task is to Analyze AWR Report in Oracle. By Reading AWR Report  you can easily solve issues like Slow database, high wait events, Slow query and many more. Though It's a lengthy report but Analyzing or Reading relevant part of AWR Report can help to troubleshoot issues in easy and fast manner.

AWR stands for Automatically workload repository, Though there could be many types of database performance issues, but when whole database is slow, then there are two possibilities.

Click here to read more about how to analyze AWR reports...

Note: You can follow so many blogs and other oracle documents also.

SQLT Report:

SQLT, also known as SQLTXPLAIN is a tool provided by Oracle Server Technologies Center of Expertise that can be used to diagnose why a particular SQL statement is performing poorly.  It is not like AWR or Statspack which provide a system-wide view of performance.  SQLT is very focused and works on one SQL statement at a time.  SQLT inputs one SQL statement and outputs a set of diagnostics files. These files can be used to diagnose SQL statements performing poorly.  In this blog, I will give an overview of SQLT and how to get started with it.

Click here to read more about sqlt reports...

Query runs fine in pre-prod databases but too slow in production

A SQL query runs fine in pre-prod/ test databases but too slow in production database. What could be the root cause?

Before answer to the above question, you may go though below question and answers to find your answers.

Q.1) Do you have the same number of extents in both databases ?
-- Maybe the segments in the "test" database were reorganized during import/export for example.

Q.2) How busy is your production? Do you have a lot of read waits on prod compare to test?
-- Then you can proceed for further investigation.

Q.3) Do you have more Contentions? 
-- Then check the undo header. if this value to the total request is more than 1% then try to increase the number of rbs.

Q.4) Is your queries sort intensive?
-- Then increasing the parameter SORT_AREA_SIZE will help.
Eventually, i've solved the problem by increasing the parameter SORT_AREA_SIZE from 65536 (default) to 1048576.
After the increasing,the query ran fast. But, you may ask, How can it happen? May the environments are the same.
Then probably there were other users using the system and also there were a lot of disk i/o happening to your temp tablespace. You can specify more temp tablespaces/ tempfiles based on RAC nodes in RAC environments.

Note: no. of active nodes = no. of tempfiles in a temp tablespace

Q.5) Have you verified "Selectivity", "Clustering", and "Histograms" of the used tables in both PROD and TEST?
-- Use below links to get clues..
Click here : Selectivity, Clustering, and Histograms