walkerdba
asked on
how to run script in oracle real time
In real time how we will run this script
Script to find out the top processes, sql information,
lock and Wait information
########################## ########## ########## ########## ########## ########## ###
# show_top
# Owner : Don Dohm , Neeraj
# Copyright orcl-experts.info
# Make sure to setup the right Oracle Home. We are assuming that the oracle home is /data01/home/oracle and .bash_profile is present
# Displays information about oracle processes appearing in the "top" connamd"
# Question or Comments : livedba@orcl-experts.info
########################## ########## ########## ########## ########## ########## ####
# set -x
. ~oracle/.bash_profile
echo "TOP ORACLE PROCESSES:"
echo "---------------------"
echo ""
# for pid in `top -n1 | grep " oracle " | grep -v " top" | grep -v ^$ | cut -f1 -d' '|sed 's/^ //'`
for pid in `ps -u oracle -o "%p %u %C %t %a"|sed 's/^ //'|sort -nr +2 | head | cut -f1 -d' '|sed '/^$/d'`
do
echo "************************* ********** ********** ********** ********** ********** ***"
echo "Processing $pid ..."
echo "************************* ********** ********** ********** ********** ********** ***"
echo ""
# cmd=`ps -flp $pid`
echo "COMMAND:"
echo ""
ps -flp $pid
echo ""
export ORACLE_SID="UNKNOWN"
for sid in `ps -ef|grep pmon|grep -v grep|cut -c58-65`
do
ps -flp $pid | grep $sid | grep -v "ora_" | grep -v grep 2>&1>> /dev/null
if [ $? = 0 ]; then
export ORACLE_SID=$sid
fi
done
echo ""
if [ "$ORACLE_SID" = "UNKNOWN" ]; then
echo "This is NOT an Oracle database USER process"
else
sqlplus -s / as sysdba << EOF
rem -------------------------- ---------- ---------- ---------- ---------- -----
rem Lookup database details for a given Unix process id
rem -------------------------- ---------- ---------- ---------- ---------- -----
set echo off;
set feed off;
set verify off;
set trimspool on;
set pages 0;
set serveroutput on size 50000
-- accept 1 prompt 'Enter Unix process id: '
DECLARE
v_sid number;
s sys.v_\$session%ROWTYPE;
p sys.v_\$process%ROWTYPE;
BEGIN
begin
select sid into v_sid
from sys.v_\$process p, sys.v_\$session s
where p.addr = s.paddr
and (p.spid = $pid
or s.process = '$pid');
exception
when no_data_found then
dbms_output.put_line('Unab le to find process id $pid!!!');
return;
when others then
dbms_output.put_line(sqler rm);
return;
end;
select * into s from sys.v_\$session where sid = v_sid;
select * into p from sys.v_\$process where addr = s.paddr;
dbms_output.put_line('==== ========== ========== ========== ========== ========== ========== =====');
dbms_output.put_line('SID/ Serial : '|| s.sid||','||s.serial#);
dbms_output.put_line('Fore ground : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Shad ow : '|| 'PID: '||p.spid||' - '||p.program);
dbms_output.put_line('Term inal : '|| s.terminal || '/ ' || p.terminal);
dbms_output.put_line('OS User : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User : '|| s.username);
dbms_output.put_line('Stat us Flags: '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Logi n Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_ca ll_et/60/6 0/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '990.0') || ' min');
dbms_output.put_line('Lock / Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
dbms_output.put_line('Latc h Spin : '|| nvl(p.latchspin, 'NONE'));
dbms_output.put_line('Curr ent SQL statement:');
for c1 in ( select * from sys.v_\$sqltext
where HASH_VALUE = s.sql_hash_value order by piece) loop
dbms_output.put_line(chr(9 )||c1.sql_ text);
end loop;
dbms_output.put_line('Prev ious SQL statement:');
for c1 in ( select * from sys.v_\$sqltext
where HASH_VALUE = s.prev_hash_value order by piece) loop
dbms_output.put_line(chr(9 )||c1.sql_ text);
end loop;
dbms_output.put_line('Sess ion Waits:');
for c1 in ( select * from sys.v_\$session_wait where sid = s.sid) loop
dbms_output.put_line(chr(9 )||c1.stat e||': '||c1.event);
end loop;
-- dbms_output.put_line('Conn ect Info:');
-- for c1 in ( select * from sys.v_\$session_connect_in fo where sid = s.sid) loop
-- dbms_output.put_line(chr(9 )||': '||c1.network_service_bann er);
-- end loop;
dbms_output.put_line('Lock s:');
for c1 in ( select /*+ ordered */
decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest,
decode(l.type, 'MR', o.name,
'TD', o.name,
'TM', o.name,
'RW', 'FILE#='||substr(l.id1,1,3 )||
' BLOCK#='||substr(l.id1,4,5 )||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) objname
from sys.v_\$lock l, sys.obj\$ o
where sid = s.sid
and l.id1 = o.obj#(+) ) loop
dbms_output.put_line(chr(9 )||c1.type ||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
end loop;
dbms_output.put_line('==== ========== ========== ========== ========== ========== ========== =====');
END;
/
exit;
EOF
fi
echo ""
echo ""
done
set +x
Script to find out the top processes, sql information,
lock and Wait information
##########################
# show_top
# Owner : Don Dohm , Neeraj
# Copyright orcl-experts.info
# Make sure to setup the right Oracle Home. We are assuming that the oracle home is /data01/home/oracle and .bash_profile is present
# Displays information about oracle processes appearing in the "top" connamd"
# Question or Comments : livedba@orcl-experts.info
##########################
# set -x
. ~oracle/.bash_profile
echo "TOP ORACLE PROCESSES:"
echo "---------------------"
echo ""
# for pid in `top -n1 | grep " oracle " | grep -v " top" | grep -v ^$ | cut -f1 -d' '|sed 's/^ //'`
for pid in `ps -u oracle -o "%p %u %C %t %a"|sed 's/^ //'|sort -nr +2 | head | cut -f1 -d' '|sed '/^$/d'`
do
echo "*************************
echo "Processing $pid ..."
echo "*************************
echo ""
# cmd=`ps -flp $pid`
echo "COMMAND:"
echo ""
ps -flp $pid
echo ""
export ORACLE_SID="UNKNOWN"
for sid in `ps -ef|grep pmon|grep -v grep|cut -c58-65`
do
ps -flp $pid | grep $sid | grep -v "ora_" | grep -v grep 2>&1>> /dev/null
if [ $? = 0 ]; then
export ORACLE_SID=$sid
fi
done
echo ""
if [ "$ORACLE_SID" = "UNKNOWN" ]; then
echo "This is NOT an Oracle database USER process"
else
sqlplus -s / as sysdba << EOF
rem --------------------------
rem Lookup database details for a given Unix process id
rem --------------------------
set echo off;
set feed off;
set verify off;
set trimspool on;
set pages 0;
set serveroutput on size 50000
-- accept 1 prompt 'Enter Unix process id: '
DECLARE
v_sid number;
s sys.v_\$session%ROWTYPE;
p sys.v_\$process%ROWTYPE;
BEGIN
begin
select sid into v_sid
from sys.v_\$process p, sys.v_\$session s
where p.addr = s.paddr
and (p.spid = $pid
or s.process = '$pid');
exception
when no_data_found then
dbms_output.put_line('Unab
return;
when others then
dbms_output.put_line(sqler
return;
end;
select * into s from sys.v_\$session where sid = v_sid;
select * into p from sys.v_\$process where addr = s.paddr;
dbms_output.put_line('====
dbms_output.put_line('SID/
dbms_output.put_line('Fore
dbms_output.put_line('Shad
dbms_output.put_line('Term
dbms_output.put_line('OS User : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User : '|| s.username);
dbms_output.put_line('Stat
dbms_output.put_line('Tran
dbms_output.put_line('Logi
dbms_output.put_line('Last
dbms_output.put_line('Lock
dbms_output.put_line('Latc
dbms_output.put_line('Curr
for c1 in ( select * from sys.v_\$sqltext
where HASH_VALUE = s.sql_hash_value order by piece) loop
dbms_output.put_line(chr(9
end loop;
dbms_output.put_line('Prev
for c1 in ( select * from sys.v_\$sqltext
where HASH_VALUE = s.prev_hash_value order by piece) loop
dbms_output.put_line(chr(9
end loop;
dbms_output.put_line('Sess
for c1 in ( select * from sys.v_\$session_wait where sid = s.sid) loop
dbms_output.put_line(chr(9
end loop;
-- dbms_output.put_line('Conn
-- for c1 in ( select * from sys.v_\$session_connect_in
-- dbms_output.put_line(chr(9
-- end loop;
dbms_output.put_line('Lock
for c1 in ( select /*+ ordered */
decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest,
decode(l.type, 'MR', o.name,
'TD', o.name,
'TM', o.name,
'RW', 'FILE#='||substr(l.id1,1,3
' BLOCK#='||substr(l.id1,4,5
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) objname
from sys.v_\$lock l, sys.obj\$ o
where sid = s.sid
and l.id1 = o.obj#(+) ) loop
dbms_output.put_line(chr(9
end loop;
dbms_output.put_line('====
END;
/
exit;
EOF
fi
echo ""
echo ""
done
set +x
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok
ASKER