Wednesday, January 15, 2014

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;

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;


4.Run this bash level.

How look psmon