While running a query is slow compare as yesterday

 

DBA career mostly face the issue “Yesterday this database Query is running fine. But today we have observed database query response time is very slow compare as yesterday" question raise from Development Team / Client.

Today morning I went to my office through bike at 8.00 AM from my home. I have reached my office at 9.00 AM.It took one hour. When I was started from my office to home at 6.00 PM, it took 2 hours. Why the time different is varying?


DBA point of view first action


1. Take explain plan for database query.

Ø If you have any old (yesterday) explain plan for database query, Compare the both.
Ø Query used which type of optimizer?
Ø If table’s 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 night inserted 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…)