Steps to Check if an Oracle database is performing slow

As a DBA it is very common in our daily basis scenarios if a database is performing slow. You should be a performance expert too along with a database administrator. So as a beginner, here are the steps we can follow to troubleshoot to check if an Oracle database is performing slow. Performance issues are very common in our daily DBA tasks. There may be multiple reasons like database hung, FRA issue, blocking session, long-running query, OS level issue etc. So here are the steps we can follow to check if an Oracle database is performing slowly.

1. Do a switch log in the database


alter system switch logfile

If the switch file is not happening, we can directly check the alert log if the database went hung or what went wrong if any suspicious message is coming. That could be one of the possible reasons why an Oracle database is performing slowly.

2. Check if there are any blocking sessions present in the database

select
(select username from v$session where sid=a.sid) blocker,
a.sid,' is blocking ',(select username from v$session where sid=b.sid) blockee,b
.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;

If any blocking session is found, we can check the username, machine, module, and sql details. If these are related to application users, we can take a confirmation if we can kill the session

3. Check if there is any long-running query

set lines 300
col TARGET for a40
col SQL_ID for a20
select SID,TARGET||OPNAME TARGET, TOTALWORK, SOFAR,TIME_REMAINING/60 Mins_Remaining,ELAPSED_SECONDS from v$session_longops where TIME_REMAINING>0 order by TIME_REMAINING
/

4. Check the FRA(Flash Recovery Area) usage :

select file_type
, percent_space_used
, percent_space_reclaimable
, number_of_files
, con_id
from v$recovery_area_usage
order by 1
/

If FRA gets full, we have to clear some space from FRA

5. Check the alert log if any error is coming or if there is any message to get a hint

tail -500f  $BDUMP/alert_$ORACLE_SID.log

6. Check the server performance with the OS Team about the CPU usage, load average, disk space, the network connectivity, or if any message we can get from /var/log/messages

basic OS commands to check :

i)Disk space check : df -kh

ii)OS log : tail -300f /var/log/messages

Steps to Check if an Oracle Database is Performing Slow:

iii)CPU Usage (top, sar1 etc)

So here are some simple and easy steps that you can check if an Oracle database is performing slow. Hope that helps!! Also you can follow the Oracle Doc for the same.