Query Slowness

Edward Ramamoorthy
13 years ago
Categories: Tech

In the DBA career the most prominent issue we will face is the issueYesterday this database Query is running fine. But today we have observed database query response time is very slow compare as yesterday” raised from Development Team / Client.

Here are 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 intermediateexperience and serves as a checklist for finding solution to 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 query?
• If tables go to full table scan check the index available or not.
• If 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 contains only 1,00,000 records.Yesterday nightinserted large number of records. (Depend on business).
• Large number of deletion occur in the table.( So fragmentation is there)
• Now the table contains 10,00,000 records.So its takes more time to fetch the data generally.
• Check when did the table is analyzed?
• Suppose the tables were analyzed yesterday morning. But yesterday night large number records are inserted.So we need to analyze the corresponding objects.
• If statistics is outdated, gather the new statistics using Analyze/DBMS package.

4. Check the connected sessions

• Yesterday 25 Active Users only connected to database.Today 100 Active users connected. (Due to this PGA, SGA & I/O used large amount of resource are consumed)
• 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.