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