Oracle Performance Issues - Basic Checks

What are the basic checks for a junior DBA when slowness reports in the system?

Taking user inputs:

This is the very first step for troubleshooting any performance issues, get user inputs.You may use the below tips for this.

• Is application is slow or any particular batch processing is slow?
• Slowness is observed through out the system or only few or one user
• Is it happening in some particular timing ?
• Is it slow right now?

By collecting these information we will get an outline of what needs to be checked.

Now login to system and start investigation.

Check the resource utilization:

You can check the CPU,Load,Memory utilization, use top or topas command in unix.
Check any single process is holding the CPU for long time -- note the process ID.
Press 'c' in top command, it will give you the time and process which is consuming more CPU.

Check the alert log:

First check the alert log for any error and note the error or abnormalities if any. You can check how many log switches are happening in one hour. If you have more tan 5 archives per hour we can say you may need to increase the redo log size.Trouble shoot the errors if it s critical or related to performance.

Check the Database:

Loggin to database and see any lock contention in database.You can use the below query for this.

SQL> select count(*) from v$lock where block=1;

If count is greater than one, lock is there in database.Check with application team and release the blocking sessions (Refer my Blocking Sessions in Oracle post)

CPU Intensive Queries

You can find out the sql query details using the below by taking the process id from top command.

select sql_text,a.sid,a.serial# from v$sqlarea c,v$session a,v$process b where a.paddr=b.addr and a.sql_address=c.address and b.spid=&pid;

Check with application team whether these are ad-hock queries or regular, disconnect the high CPU queries if possible.

Send these query details to application team for tuning.

Below query is an another way to find out high CPU quries:

select ss.username,se.SID,VALUE/100 cpu_usage_seconds
from v$session ss, v$sesstat se,v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and NAME like '%CPU used by this session%'
and se.SID = ss.SID and ss.status='ACTIVE'
and ss.username is not null
order by VALUE desc;

Resource intensive Queries 

Find out the resource intensive queries and share the details with application team.

col usr for a10
set lines 500
select
s.username usr,s.module,logon_time,status,
m.session_id sid,
m.session_serial_num ssn,
round(m.cpu) cpu100, --- CPU usage 100th sec
m.physical_reads prds, --- Number of physical read
m.logical_reads, --- Number of logical reads
m.pga_memory, --- PGA size at the end of the intervel
m.physical_read_pct prp,
m.logical_read_pct lrp,
s.sql_id
from v$sessmetric m,v$session s
where (m.physical_reads >100
or m.cpu>100
or m.logical_reads>10000)
and m.session_id=s.sid
and m.session_serial_num=s.serial#
and s.type='USER'
order by m.physical_reads DESC,m.cpu desc,m.logical_reads desc;

Stale stats tables check 

Check any critical table statistics became stale

select count(*),owner from dba_tab_statistics where stale_stats='YES';

Make sure that no highly accessed tables are in stale stats and gather the stats if any.

Sample script for stats gathering:

execute dbms_stats.gather_table_stats(ownname => '<OWNER>', tabname  =>'<TABLE NAME>', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', degree => 2, cascade => TRUE);

Single user session or batch slowness:

Find out the sessions for the user and enable the trace

Enabling

alter session set tracefile_identifier=ARUN_NEW;

EXECUTE dbms_system.set_sql_trace_in_session (109,18512,TRUE);

This will generate the trace in udump

Disabling

EXECUTE dbms_system.set_sql_trace_in_session (707,49158,FALSE);

Go to trace location:(udump)

tkprof trace_file.ora trace_out.txt 

Analyse the report or share with application team

Server side checks

Check for the memory,paging, IO utilization from server side.

Paging and memory can be checked by top command and iostat will do the io statistics.

Contact the concern team for any abnormality if you see in this.

Advance performance tuning is not the scope of this blog, this is for junior DBA. Hope it helps.