While running a query is slow compare as yesterday in Oracle

 

While running a query is slow compare as yesterday

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.

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.

Find the top 10 Segment or object for High Physical read in Oracle

set line 200 page 200
col segment_name format a20
col owner format a10
select segment_name,object_type,total_physical_reads
from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in ('physical reads')
order by total_physical_reads desc)
where rownum <=10;

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…)