[Oracle] Slow queries: what you should check!

Hi everyone,

If you’re an Oracle DBA, one main task of this job is to do Performance And Tuning, expecially when someone warns you about some slow queries that may slow down the entire database.
In this article I want to explain you my top 5 actions you can follow if you encounter this kind of problems:

1 – Statistics

Oracle RDMS uses statistics to let its optimizer choose the best execution plan for each SQL statement.
Statistics are a collection of data stored in proprietary Oracle tables.
That’s why it’s important to have always updated statistics.
You can use the following query to check if table statistics are invalid:

col owner for a20
col table_name for a30
col partition_name for a30
set lines 222
set pages 2222
select owner, table_name, partition_name, stale_stats, last_analyzed from 
dba_tab_statistics where 
table_name in ('TABLE_NAME') and stale_stats='YES';

If you find some stale statistics, you can use the following query to recalculate them:

exec dbms_stats.gather_table_stats(OWNNAME=>'OWNER', TABNAME=>'TABLE', DEGREE=> 8, CASCADE=> TRUE);

2 – Oracle SQL Tuning

Oracle RDBMS gives you a very powerful tool to analyze a query and let you know if there are some things you can change in order to improve the execution time of a query.
You can launch a SQL Tuning advisor task following the article published by my friend and colleague Stefano:

Managing Oracle Database tuning tasks

3 – ASH Analytics / AWR Reports

ASH and AWR reports are two kind of tools that Oracle offers you, in order to diagnose problems or check health of your Oracle Database.
ASH report contains recent informations about sessions running in your database (data stored in V$ACTIVE_SESSION_HISTORY table), scanned every second, while AWR reports are taken usually every one hour and contain a lot of information such as long CPU/IO/WAIT queries and usage of different types of memory. That is why ASH is very useful if you have to diagnose a sudden poor performance problem, while AWR is useful if you have to investigate what caused slowness or any kind of problem in the past.

Both of them can be generated via command-line (sqlplus) but if you have installed Enterprise Manager in your environment, I suggest you to use that, because it’s a simpe an more efficient method, plus, informations are more readable.

4 – Older execution plans

Each query you execute in Oracle RDBMS, identified by a SQL_ID, uses an execution plan, which is a group of directives that the software chooses in order to manipulate in the most efficent way (time, I/O count request ecc..) all the data inside your database. As I said in the point 1, Oracle uses statistics to choose the best execution plan, so it’s very important to keep them updated.

Sometimes it can happens, for a lot of reasons, that Oracle software chooses to change the plan of a same query, and the new plan performs worse than the old one, so you want to say to Oracle to use the old one again and ever, fixing that plan statically. You can check the history of plans correlated with SQL_ID showing the endtime and force an execution plan the Carlos Sierra Script coe_xfr_sql_profile.sql.
You can find the guide following the official oracle notes: Doc ID 1955195.1

5 – Hints

Hints are suggestions that you can add to your SQL code in order to change the behavior of Oracle when it processes the query.
For example, if you print your SQL Plan and notice that some index takes too much time to retrieve data, you can bypass the use of the index adding the following hint in order to load all the table in memory:

SELECT /*+ FULL(TABLE_NAME) */ FROM TABLE_NAME;