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
- IO bottleneck
- Memory bottleneck
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
-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#
6. oracle performance tuning - To
find sessions generating lots of redo
- Cronjob scripts
- 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;
or according to http://stackoverflow.com/questions/622289/how-to-check-oracle-database-for-long-running-queries?answertab=votes#tab-top
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
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
/
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:
Posts
