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