In the DBA career, the most prominent issue we will face is “Yesterday this database query was running fine. But today we have observed database query response time is very slow compared to yesterday” raised from Development Team / Client.

Here are a few things you can do, to find the root cause of this issue and solve it.

Note: This article is for someone with basic to intermediate experience and serves as a checklist for finding a solution to the SQL “Query Slowness” issue.

From the DBA point of view, the first action should be

1. Take explain plan for database query.

• If you have any old (yesterday) explain plan for database query, compare both.
• Which type of optimizer is used in the query?
• If tables go to full table scan check the index available or not.
• If an index is not available, create the index & compare the explain plan.
• If index available Check why my index is not being used?
• Check the Joins Method
• Using HINT to force the optimizer.

2. Compare the table growth today & old one.

3. Check the statistics gathered for the table or not.

• Yesterday the table contained only 1,00,000 records. Last night inserted numerous records. (Depending on business).
• Large number of deletions occur in the table. (So fragmentation is there)
• Now the table contains 10,00,000 records. So it takes more time to fetch the data generally.
• When was the last time the table was analyzed?
• Suppose the tables were analyzed yesterday morning. But last night numerous records were inserted. So we need to analyze the corresponding objects.
• If statistics are outdated, gather the new statistics using the Analyze/DBMS package.

4. Check the connected sessions

• Yesterday, only 25 Active Users connected to the database. Today, 100 Active users connected. (Due to this, PGA, SGA & I/O resulted in using many resources)
• Check the wait event.

5. Check the Physical reads / Logical reads.

6. Check the hit ratio. (Library cache, Data dictionary & Db Cache)

7. Ensure if any init parameter or memory structure changed.

8. Generated trace file for the particular query. (Using TKPROF)

9. OS level, any background process taking more CPU utilization belongs to the database. (RMAN backup, EXP/IMP, DB Analyze job, Materialized view refresh job etc.)

By performing the above actions one can find the root cause and solution for “Query Slowness” issue.

I hope this article helped to you. Suggestions are welcome.