Question

You got a mail from developer saying that server is very slow. What action will you check ?

Answer 1

When users/developers have performance problem most of the time it’s not likely that database performance is down but it’s likely that its session fault. We have to identify the bump and remove it.

Oracle Database sessions are always in three states, Idle, Processing or Waiting.

Have a look at V$SESSION, everything we need for our analysis releated to users session is in this view.

Most common issue we find row locking, disk I/O contention, infinite looping, poorly written SQL statement, etc.

Answer 2

User facing his Query running slow - Run Explan Plan tool with Autotrace on and find out the CPU Usage and Cost on each statement which is causing high CPU with high cost and report that to Developer to fix.
Find Hit ratios from AWR which are showing less than 90% and increase the Size of SGA or that individual buffers.
Find Memory usage and Swap Usage - If required recommend to add RAM extra.

Using TOP Command find out the pid which is causing more CPU and find out the SQL from v$Sql and v$sqlarea , V$process,V$session.
Report that SQL to Developer.
Find out locks Using following Scripts

#Finding Blocking sessions in Database

select    'SID ' || l1.sid ||' is blocking  ' || l2.sid blocking
from    v$lock l1, v$lock l2
where    l1.block =1 and l2.request > 0
and    l1.id1=l2.id1
and    l1.id2=l2.id2

If any locks kill with alter system kill session 'sid,serial#';
Find out any dead locks , Usually deadlocks will release automatically can find out in alertlog.