Basic Oracle DBA Task List

Jephe Wu - http://linuxtechres.blogspot.com

Objective: some basic Oracle DBA daily task scripts and performance checking


Performance Issue: Most of (more 90%) performance issues are caused by Application. Few (less than 10%) of issues are caused by resource limitation, OS/RDBMS configuration and other reasons.

  • CPU bottleneck 
According to page http://www.dba-oracle.com/t_high_cpu.htm

It is not a cause for concern when the user + system CPU values approach 100 percent. This just means that the CPUs are working to their full potential. The only metric that identifies a CPU bottleneck is when the run queue (r value) exceeds the number of CPUs on the server.
  • IO bottleneck
The Wa value of vmstat command indicates how many percent of the CPU time is being used waiting for database I/O. But it does not always indicate I/O bottleneck.
  • Memory bottleneck 
According to page http://www.dba-oracle.com/t_tuning_cpu_usage_vmstat.htm

Oracle parameters such as sort_area_size can greatly increase the amount of RAM allocated to each connected user's PGA.

a UNIX process may page-in when the UNIX program is starting or is accessing parts of its code that it had not used before.

With UNIX virtual memory we are always anticipating running out of RAM memory, and a page-out is a method for being ready for a subsequent page-in

In sum, page-out (po) operations are a normal part of virtual memory operation, but page-in (pi) operations indicate that the server has excessive RAM demands. Because Linux will put least used part of memory back to swap

  • Cronjob scripts 


  1. use vmstat 10 to get data and the following is the script:



-bash-3.2# cat /home/oracle/bin/vmstat.sh 
#!/bin/sh
DATE=`date +%Y%m%d`
function vm {
  while read line
  do
    printf "$line"
    date '+ %m-%d-%Y %H:%M:%S'
  done
}
find /var/adm/logs/vmstat.log* -type f -mtime +30 -exec rm -f {} \;
vmstat 10 | vm >> /var/log/vmstat/vmstat.log.$DATE

then put cronjob as follows to run vmstat 
0 0 * * * sleep 1;kill `ps -ef | grep -v  grep  |grep vmstat.sh | awk '{print $2}'`;sleep 3;nohup /home/oracle/bin/vmstat.sh &

 2.  most used cpu and wait time


#!/bin/sh
export ORACLE_SID=PROD
export ORACLE_BASE=/db01/oracle
export ORACLE_HOME=/db01/oracle/product/11.2.0
PATH=/usr/local/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:/home/oracle/bin:/usr/bin/X11:/sbin:/db01/oracle/product/11.2.0/bin
LD_LIBRARY_PATH=/db01/oracle/product/11.2.0/lib
export PATH LD_LIBRARY_PATH

DATE=`date +%Y%m%d`
LOGFILE=/var/adm/logs/oracle_perf.log.$DATE

find /var/adm/logs/oracle_perf.log.* -type f -mtime +30 -exec rm -f {} \;
sqlplus -S / as sysdba @/home/oracle/bin/oracle_perf.sql >> $LOGFILE


oracle@prod1 (PROD)> cat /home/oracle/bin/oracle_perf.sql 
set line 32000
select to_char(sysdate, 'YYYYMMDD hh24:mi:ss') tm from dual;

# logon within 4 hours, last half hour, cpu and db time .
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time (sec)"
, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"
FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p
WHERE sn.name = 'DB time' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND  sncpu.name = 'CPU used by this session' -- CPU
AND stcpu.statistic# = sncpu.statistic#
AND stcpu.sid = st.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND st.value/100 > 30 order by st.value;

# time waited sort for logon within 4 hours, last half an hour, for db file sequential read time waited , sorting
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
WHERE se.event = 'db file sequential read
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited;
exit;

3. long queries

# long query , run more than 60s, active, not background type.

select s.username,s.type,s.sid,s.serial#,s.last_call_et seconds_running,q.sql_text from v$session s 
join v$sql q
on s.sql_id = q.sql_id
 where status='ACTIVE'
 and type <> 'BACKGROUND'
 and last_call_et> 60
order by sid,serial#




  • 4. checking blocking session.





SQL> select process,sid, blocking_session from v$session where blocking_session is not null;


select
  object_name, 
  object_type, 
  session_id, 
  type,                 -- Type or system/user lock
  lmode,        -- lock mode in which session holds lock
  request, 
  block, 
  ctime                 -- Time since current mode was granted
from
  v$locked_object, all_objects, v$lockwhere
  v$locked_object.object_id = all_objects.object_id AND
  v$lock.id1 = all_objects.object_id AND
  v$lock.sid = v$locked_object.session_idorder by
  session_id, ctime desc, object_name/
SQL> select SERIAL# from v$session where SID=123;
SERIAL#
———-
380

SQL> alter system kill session ’380,123′;

  • 5. long transaction more than 6 seconds



sqlplus / as sysdba
sql> select *from v$session_longops;


select * from (
  select opname, target, sofar, totalwork,
         units, elapsed_seconds, message
  from v$session_longops order by start_time desc)
where rownum <=1;
http://www.gplivna.eu/papers/v$session_longops.htm

# long transaction and still running ones, based on http://stackoverflow.com/questions/622289/how-to-check-oracle-database-for-long-running-queries?answertab=votes#tab-top

COLUMN percent FORMAT 999.99 
SELECT sid, to_char(start_time,'hh24:mi:ss') stime, 
message,( sofar/totalwork)* 100 percent FROM v$session_longopsWHERE sofar/totalwork < 1
/
6.  oracle performance tuning To find sessions generating lots of redo


methods:  refer to: How to Find Sessions Generating Lots of Redo or Archive logs [ID 167492.1]

1. Query V$SESS_IO
This view contains the column BLOCK_CHANGES which indicates
   how much blocks have been changed by the session. High values indicate a 
   session generating lots of redo.


SELECT s.sid, s.serial#, s.username, s.program,
 i.block_changes
  FROM v$session s, v$sess_io i
  WHERE s.sid = i.sid [and block_changes > 1000 ]
  ORDER BY 5 desc, 1,2,3,4;


# Run the query multiple times and examine the delta between each occurrence
   of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.


2) Query V$TRANSACTION. This view contains information about the amount of
   undo blocks and undo records accessed by the transaction (as found in the 
   USED_UBLK and USED_UREC columns).
  The query you can use is:

SELECT s.sid, s.serial#, s.username, s.program, 
 t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
 WHERE s.taddr = t.addr
ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

   Run the query multiple times and examine the delta between each occurrence
   of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by 
   the session.


Basic Oracle names: