Thursday, November 21, 2024

Query taking more time? 

1. DML Query (Insert, Update,)

Cause: locks / deadlocks 
Fix/Solution: kill / Ask user to do commit/rollback  


2. Select Query 
- OS side - OS analysis 
- top, vmstat, iosts, memory, sar (OEM, OS watcher, Exa watcher, Nagios)

- DB Side - DB analysis 
- Query dynamic perf view (v$session, v$longops, v$sql etc...)
- AWR report (ASH report, ADDM report, SQL advisory report)


To identify:
- SQL text (select * from emp;) - We can identify what all tables involved in the query:

Recommendations
- Latest Patch (n-1) (Jan, Apr, Jul, Oct) 
- Gather stats (Table/Index/Schema) 
- Validation / rebuild Index 
- Table move 
- Table shrink 

- SQL ID (1a1a1a1) - What is the execution plan associated to this SQL ID 
- Check execution plan (Plan change)
- SQL Profiling 

Query taking more time?  1. DML Query (Insert, Update,) Cause: locks / deadlocks  Fix/Solution: kill / Ask user to do commit/rollback   2. S...