Oracle 10g/11g:Database monitoring Tools/Utility
Oracle 10g/11g:Database monitoring Tools/Utility
This shell script mainly developed for monitoring database basic things in regular interval.
This is home made utilities that inside set of sql queries that give required output.
This utility basically useful for getting info of active sessions,inactive session,running jobs,top wait events,block and locking sessions and current load of server.
For this utility implement,Need to create set of queries and shell script which execute anywhere.
Follow this steps for create home made monitoring tool.
1.create directory on /usr/bin.
mkdir psmon
2.Create shell script for execute set of sql queries.
#!/bin/ksh
clear
echo
uptime
prc=`ps -e -o pid,pcpu|awk '$2>0 {print $1}'`
while :
do
echo "#############################################################################################"
echo "|r=Refresh|e=End|S=Query_By_SID|L=LongOP_QUERY|Q=Remaining_Query|t=Topas|j=Jobs|z=View_Query|"
echo "|l=List_Users|k=Lock_Session|c=cpu|w=wait|a=active_user|i=kill_session|b=lock_with_sqlid|p=explain_plan|"
echo "|y=Query_By_SQLID|d=Session_Count|m=Time_Consuming_Query|q=Quit|"
echo
opt=`/usr/bin/askopt 'r/e/S/L/Q/t/j/z/l/k/c/w/a/i/b/p/y/d/m/q' refresh `
[ "$opt" = "e" ] && break
[ "$opt" = "r" ] && prc=`ps -e -o pid,pcpu|awk '$2>0 {print $1}'`
[ "$opt" = "t" ] && topas
[ "$opt" = "q" ] && exit
[ "$opt" = "j" ] && {
date
sqlplus -s username/password @/usr/bin/psmon/dbajobs.sql
continue
}
[ "$opt" = "S" ] && {
date
sqlplus -s username/password @/usr/bin/psmon/Q_SID.sql
continue
}
[ "$opt" = "L" ] && {
date
sqlplus -s username/password @/usr/bin/psmon/longop.sql
continue
}
[ "$opt" = "Q" ] && {
date
sqlplus -s username/password @/usr/bin/psmon/remainingquery.sql
continue
}
[ "$opt" = "l" ] && {
date
sqlplus -s username/password @/usr/bin/psmon/login_cnt.sql
continue
}
[ "$opt" = "k" ] && {
date
sqlplus -s username/password @/usr/bin/psmon/lock_check.sql
continue
}
[ "$opt" = "z" ] && {
sqlplus -s username/password @/usr/bin/unixusr.sql
continue
}
[ "$opt" = "c" ] && {
sqlplus -s username/password @/usr/bin/psmon/cpu.sql
continue
}
[ "$opt" = "w" ] && {
sqlplus -s username/password @/usr/bin/psmon/wait.sql
continue
}
[ "$opt" = "a" ] && {
sqlplus -s username/password @/usr/bin/psmon/au.sql
continue
}
[ "$opt" = "i" ] && {
sqlplus -s username/password @/usr/bin/psmon/killsession.sql
continue
}
[ "$opt" = "b" ] && {
sqlplus -s username/password @/usr/bin/psmon/lock.sql
continue
}
[ "$opt" = "p" ] && {
sqlplus -s username/password @/usr/bin/psmon/explain.sql
continue
}
[ "$opt" = "y" ] && {
sqlplus -s username/password @/usr/bin/psmon/sql_id.sql
continue
}
[ "$opt" = "d" ] && {
sqlplus -s username/password @/usr/bin/psmon/sessionstats.sql
continue
}
[ "$opt" = "m" ] && {
sqlplus -s username/password @/usr/bin/psmon/time.sql
continue
}
ps -fp "$prc"|awk '$4>0'|sort +3 -4n
uptime
done
ans=`/usr/bin/askopt 'Y/N/' Do you want to kill the session '`
[ "$ans" = "Y" ] && /usr/bin/killproc.ksh
ans1=`/usr/bin/askopt 'Y/N/' Do you want to see the INDEXES ON THE TABELE'`
[ "$ans1" = "Y" ] && /usr/bin/ind_col_list.ksh
exit;
Save this file named psmon into /usr/bin/
This willl execute every location.
3.Create .sql files that store the queries for respective options on psmon.
1./usr/bin/psmon/dbajobs.sql
col what format a45;
select sid,a.job,what from dba_jobs_running a,dba_jobs b
where a.job = b.job;
exit;
1./usr/bin/psmon/dbajobs.sql
col what format a45;
select sid,a.job,what from dba_jobs_running a,dba_jobs b
where a.job = b.job;
exit;
2./usr/bin/psmon/Q_SID.sql
column sql_text format a10000
SELECT a.sql_text
FROM v$sqltext a,
v$session b
WHERE a.address = b.sql_address
AND a.hash_value = b.sql_hash_value
AND b.sid = &sid
ORDER BY a.piece;
set pages 999 lines 999 long 999
select a.sid, a.serial#, b.spid, a.username, a.osuser, a.machine, a.terminal, a.program, a.sql_id, s.hash_value, b.background, s.sql_fulltext
from v$session a, v$process b, v$sqlarea s where a.paddr = b.addr and a.sql_id = s.sql_id and a.sql_hash_value = s.hash_value and a.sid = &sid ;
exit;
3./usr/bin/psmon/longop.sql
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A25
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.sid,
s.serial#,
s.machine,
ROUND(sl.elapsed_seconds/60) || ':' ||
MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' ||
MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM v$session s,
v$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial# order by elapsed desc;
exit;
4./usr/bin/bafl/remainingquery.sql
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A25
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.sid,
s.serial#,
s.machine,
ROUND(sl.elapsed_seconds/60) || ':' ||
MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' ||
MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM v$session s,
v$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial# and MOD(sl.time_remaining,60) > 0 order by elapsed desc;
exit;
5./usr/bin/bafl/login_cnt.sql
set pages 50
set feedback off
break on report
compute sum of cnt on report
select substr(USER_ID,1,1) usrs,count(*) cnt from finnsso_bfprod.sec_login_users a, finnsso_bfprod.SEC_APPL_USERS b
where a.APPL_USER_ID = b.APPL_USER_ID
and a.login_status = 'Y'
group by substr(USER_ID,1,1)
order by 1;
exit;
6./usr/bin/bafl/lock_check.sql
column LOCK_TYPE format a25
column MODE_HELD format a25
column MODE_REQUESTED format a25
select WAITING_SESSION,HOLDING_SESSION,LOCK_TYPE,LOCK_ID1,LOCK_ID2 from dba_waiters;
exit;
7./usr/bin/unixusr.sql
rem -----------------------------------------------------------------------
rem Filename: unixusr.sql
rem Purpose: Lookup database details for a given Unix process id
rem Date: 20-Dec-2004
rem Author: Anuranjan Gupta
rem -----------------------------------------------------------------------
set serveroutput on size 50000
set echo off feed off veri off
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 = &&1
or s.process = '&&1');
exception
when no_data_found then
dbms_output.put_line('Unable to find process id &&1!!!');
return;
when others then
dbms_output.put_line(sqlerrm);
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('Foreground : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Shadow : '|| 'PID: '||p.spid||' - '||p.program);
dbms_output.put_line('Terminal : '|| 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('Status Flags: '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_call_et/60/60/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('Latch Spin : '|| nvl(p.latchspin, 'NONE'));
dbms_output.put_line('Current 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('Previous 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('Session Waits:');
for c1 in ( select * from sys.v_$session_wait where sid = s.sid) loop
dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
end loop;
-- dbms_output.put_line('Connect Info:');
-- for c1 in ( select * from sys.v_$session_connect_info where sid = s.sid) loop
-- dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
-- end loop;
dbms_output.put_line('Locks:');
for c1 in ( select
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;
8./usr/bin/bafl/cpu.sql
col what format a45;
select sid,a.job,what from dba_jobs_running a,dba_jobs b
where a.job = b.job;
exit;
you have mail in /usr/spool/mail/oracle
[oracle@bcfprddb|BCFPRD /oracle/products/apps]$cat /usr/bin/bafl/cpu.sql
column sql_text format a40 word_wrapped heading 'SQL|Text'
column cpu_time heading 'CPU|Time'
column elapsed_time heading 'Elapsed|Time'
column disk_reads heading 'Disk|Reads'
column buffer_gets heading 'Buffer|Gets'
column rows_processed heading 'Rows|Processed'
set pages 55 lines 132 long 9999
ttitle 'SQL By CPU Usage'
select * from
(select sql_id, sql_text,
cpu_time/1000000000 cpu_time,
elapsed_time/1000000000 elapsed_time,
disk_reads,
buffer_gets,
rows_processed
from v$sqlarea
order by cpu_time desc, disk_reads desc
)
where rownum < 7 ;
exit;
9./usr/bin/bafl/wait.sql
select event "Wait_Event" , count(*) from v$session_wait group by event order by 2 desc;
exit;
10./usr/bin/bafl/au.sql
set echo off
set pagesize 1000
set lines 140
set verify off
set heading on
set feedback on
col SESS format a12
col status format a10
col program format a30
col terminal format a12
col "Machine Name" format a15
col "Machine Name" format a15
col "DB User" format a14
col "Logon Time" format a14
col "OS User" format a10
select rpad(s.username,14,' ') as "DB User",
to_char(logon_time,'hh24:mi Mon/dd') as "Logon Time",
s.sid||','||s.serial# SESS,
rpad(upper(substr(s.program,instr(s.program,'\',-1)+1)),30,' ') as "Program",
rpad(lower(osuser),10,' ') as "OS User", rpad(s.terminal,12,' ') "Terminal",
rpad(initcap(machine),15,' ') as "Machine Name",round(LAST_CALL_ET/60) MINS_ACTIVE
from v$session s
where upper(s.username) like upper('%&Username%') and s.status='ACTIVE'
order by LAST_CALL_ET;
exit;
11./usr/bin/bafl/killsession.sql
select 'alter system kill session '||''''||sid||','||serial#||''';' from v$session
where sid in (SELECT a.sid
from v$session b,
(select distinct b.*
from v$lock a,
v$lock b
where a.id1 = b.id1
and a.id2 = b.id2
and a.request > 0) a
where a.sid = b.sid
and b.status='INACTIVE')
and username is not null;
exit;
12./usr/bin/bafl/lock.sql
set line 200
set pages 2000
col sess for a15
col username for a15
SELECT substr(DECODE(request,0,'Holder: ','Waiter: ')||a.sid,1,12) sess, a.type, a.lmode, a.request,a.block, a.ctime, b.username, b.status, b.sql_id, b.prev_sql_id, b.ROW_WAIT_OBJ#
from gv$session b,
(select distinct b.*
from gv$lock a,
gv$lock b
where a.id1 = b.id1
and a.id2 = b.id2
and a.request > 0) a
where a.sid = b.sid
and a.inst_id = b.inst_id
order by 8 desc;
exit;
13./usr/bin/bafl/explain.sql
set pages 999 lines 999 long 999
select 'Explain Plan from Historical AWR' from dual;
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));
select 'Explain Plan from Current Cursor' from dual;
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));
exit;
14./usr/bin/bafl/sql_id.sql
set pages 999 lines 999 long 999
column sql_fulltext format a10000
select a.sid, a.serial#, b.spid, a.username, a.osuser, a.machine, a.terminal, a.program, a.sql_id, s.hash_value, b.background, s.sql_fulltext
from v$session a, v$process b, v$sqlarea s where a.paddr = b.addr and a.sql_id = s.sql_id
and a.sql_hash_value = s.hash_value and a.sql_id = '&SQLID' ;
exit;
15./usr/bin/bafl/sessionstats.sql
set pages 9999 lines 9999 long 999999
col username format a30
col status format a30
col machine format a30
col terminal format a40
col program format a40
col sql_id format a20
col count(1) format 99999.999
select a.username,a.status, a.machine, a.terminal, a.program, a.sql_id , count(1)
from v$session a, v$process b, v$sqlarea s where a.paddr = b.addr and a.sql_id = s.sql_id
and a.sql_hash_value = s.hash_value group by a.username,a.status, a.machine, a.terminal, a.program, a.sql_id
order by count(1) desc, a.username asc ;
exit;
16./usr/bin/bafl/time.sql
set pagesize 9999 linesize 9999 long 9999
select * from
(
select sql_id,PLAN_HASH_VALUE,executions, DISK_READS/executions, BUFFER_GETS/executions, (elapsed_time/nvl(executions,1))/1000000 "Query Time per execution"
from v$sqlstats where executions > 0 order by 6 desc
)
where rownum < 15 ;
exit;
How look psmon
column sql_text format a10000
SELECT a.sql_text
FROM v$sqltext a,
v$session b
WHERE a.address = b.sql_address
AND a.hash_value = b.sql_hash_value
AND b.sid = &sid
ORDER BY a.piece;
set pages 999 lines 999 long 999
select a.sid, a.serial#, b.spid, a.username, a.osuser, a.machine, a.terminal, a.program, a.sql_id, s.hash_value, b.background, s.sql_fulltext
from v$session a, v$process b, v$sqlarea s where a.paddr = b.addr and a.sql_id = s.sql_id and a.sql_hash_value = s.hash_value and a.sid = &sid ;
exit;
3./usr/bin/psmon/longop.sql
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A25
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.sid,
s.serial#,
s.machine,
ROUND(sl.elapsed_seconds/60) || ':' ||
MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' ||
MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM v$session s,
v$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial# order by elapsed desc;
exit;
4./usr/bin/bafl/remainingquery.sql
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A25
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.sid,
s.serial#,
s.machine,
ROUND(sl.elapsed_seconds/60) || ':' ||
MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' ||
MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM v$session s,
v$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial# and MOD(sl.time_remaining,60) > 0 order by elapsed desc;
exit;
5./usr/bin/bafl/login_cnt.sql
set pages 50
set feedback off
break on report
compute sum of cnt on report
select substr(USER_ID,1,1) usrs,count(*) cnt from finnsso_bfprod.sec_login_users a, finnsso_bfprod.SEC_APPL_USERS b
where a.APPL_USER_ID = b.APPL_USER_ID
and a.login_status = 'Y'
group by substr(USER_ID,1,1)
order by 1;
exit;
6./usr/bin/bafl/lock_check.sql
column LOCK_TYPE format a25
column MODE_HELD format a25
column MODE_REQUESTED format a25
select WAITING_SESSION,HOLDING_SESSION,LOCK_TYPE,LOCK_ID1,LOCK_ID2 from dba_waiters;
exit;
7./usr/bin/unixusr.sql
rem -----------------------------------------------------------------------
rem Filename: unixusr.sql
rem Purpose: Lookup database details for a given Unix process id
rem Date: 20-Dec-2004
rem Author: Anuranjan Gupta
rem -----------------------------------------------------------------------
set serveroutput on size 50000
set echo off feed off veri off
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 = &&1
or s.process = '&&1');
exception
when no_data_found then
dbms_output.put_line('Unable to find process id &&1!!!');
return;
when others then
dbms_output.put_line(sqlerrm);
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('Foreground : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Shadow : '|| 'PID: '||p.spid||' - '||p.program);
dbms_output.put_line('Terminal : '|| 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('Status Flags: '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_call_et/60/60/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('Latch Spin : '|| nvl(p.latchspin, 'NONE'));
dbms_output.put_line('Current 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('Previous 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('Session Waits:');
for c1 in ( select * from sys.v_$session_wait where sid = s.sid) loop
dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
end loop;
-- dbms_output.put_line('Connect Info:');
-- for c1 in ( select * from sys.v_$session_connect_info where sid = s.sid) loop
-- dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
-- end loop;
dbms_output.put_line('Locks:');
for c1 in ( select
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;
8./usr/bin/bafl/cpu.sql
col what format a45;
select sid,a.job,what from dba_jobs_running a,dba_jobs b
where a.job = b.job;
exit;
you have mail in /usr/spool/mail/oracle
[oracle@bcfprddb|BCFPRD /oracle/products/apps]$cat /usr/bin/bafl/cpu.sql
column sql_text format a40 word_wrapped heading 'SQL|Text'
column cpu_time heading 'CPU|Time'
column elapsed_time heading 'Elapsed|Time'
column disk_reads heading 'Disk|Reads'
column buffer_gets heading 'Buffer|Gets'
column rows_processed heading 'Rows|Processed'
set pages 55 lines 132 long 9999
ttitle 'SQL By CPU Usage'
select * from
(select sql_id, sql_text,
cpu_time/1000000000 cpu_time,
elapsed_time/1000000000 elapsed_time,
disk_reads,
buffer_gets,
rows_processed
from v$sqlarea
order by cpu_time desc, disk_reads desc
)
where rownum < 7 ;
exit;
9./usr/bin/bafl/wait.sql
select event "Wait_Event" , count(*) from v$session_wait group by event order by 2 desc;
exit;
10./usr/bin/bafl/au.sql
set echo off
set pagesize 1000
set lines 140
set verify off
set heading on
set feedback on
col SESS format a12
col status format a10
col program format a30
col terminal format a12
col "Machine Name" format a15
col "Machine Name" format a15
col "DB User" format a14
col "Logon Time" format a14
col "OS User" format a10
select rpad(s.username,14,' ') as "DB User",
to_char(logon_time,'hh24:mi Mon/dd') as "Logon Time",
s.sid||','||s.serial# SESS,
rpad(upper(substr(s.program,instr(s.program,'\',-1)+1)),30,' ') as "Program",
rpad(lower(osuser),10,' ') as "OS User", rpad(s.terminal,12,' ') "Terminal",
rpad(initcap(machine),15,' ') as "Machine Name",round(LAST_CALL_ET/60) MINS_ACTIVE
from v$session s
where upper(s.username) like upper('%&Username%') and s.status='ACTIVE'
order by LAST_CALL_ET;
exit;
11./usr/bin/bafl/killsession.sql
select 'alter system kill session '||''''||sid||','||serial#||''';' from v$session
where sid in (SELECT a.sid
from v$session b,
(select distinct b.*
from v$lock a,
v$lock b
where a.id1 = b.id1
and a.id2 = b.id2
and a.request > 0) a
where a.sid = b.sid
and b.status='INACTIVE')
and username is not null;
exit;
12./usr/bin/bafl/lock.sql
set line 200
set pages 2000
col sess for a15
col username for a15
SELECT substr(DECODE(request,0,'Holder: ','Waiter: ')||a.sid,1,12) sess, a.type, a.lmode, a.request,a.block, a.ctime, b.username, b.status, b.sql_id, b.prev_sql_id, b.ROW_WAIT_OBJ#
from gv$session b,
(select distinct b.*
from gv$lock a,
gv$lock b
where a.id1 = b.id1
and a.id2 = b.id2
and a.request > 0) a
where a.sid = b.sid
and a.inst_id = b.inst_id
order by 8 desc;
exit;
13./usr/bin/bafl/explain.sql
set pages 999 lines 999 long 999
select 'Explain Plan from Historical AWR' from dual;
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));
select 'Explain Plan from Current Cursor' from dual;
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));
exit;
14./usr/bin/bafl/sql_id.sql
set pages 999 lines 999 long 999
column sql_fulltext format a10000
select a.sid, a.serial#, b.spid, a.username, a.osuser, a.machine, a.terminal, a.program, a.sql_id, s.hash_value, b.background, s.sql_fulltext
from v$session a, v$process b, v$sqlarea s where a.paddr = b.addr and a.sql_id = s.sql_id
and a.sql_hash_value = s.hash_value and a.sql_id = '&SQLID' ;
exit;
15./usr/bin/bafl/sessionstats.sql
set pages 9999 lines 9999 long 999999
col username format a30
col status format a30
col machine format a30
col terminal format a40
col program format a40
col sql_id format a20
col count(1) format 99999.999
select a.username,a.status, a.machine, a.terminal, a.program, a.sql_id , count(1)
from v$session a, v$process b, v$sqlarea s where a.paddr = b.addr and a.sql_id = s.sql_id
and a.sql_hash_value = s.hash_value group by a.username,a.status, a.machine, a.terminal, a.program, a.sql_id
order by count(1) desc, a.username asc ;
exit;
16./usr/bin/bafl/time.sql
set pagesize 9999 linesize 9999 long 9999
select * from
(
select sql_id,PLAN_HASH_VALUE,executions, DISK_READS/executions, BUFFER_GETS/executions, (elapsed_time/nvl(executions,1))/1000000 "Query Time per execution"
from v$sqlstats where executions > 0 order by 6 desc
)
where rownum < 15 ;
exit;
4.Run this bash level.
How look psmon
After reading this post I got an idea about on this note.Really something grate in this article ,Thanks for sharing this. We are providing ORACLE courses training online. After reading this slightly am changed my way of introduction about my training to people. To know more Visit Us ORACLEOnline Training Course
ReplyDelete