Top Useful scripts for Oracle Database for Monitoring purpose



Last updated on August 19th, 2016 at 06:40 pm

We often need to monitor the database session for performance reason.Here are Top Useful Scripts  for Oracle  Database for monitoring purpose

Script to find the sid of the session you are logged in as

select distinct(sid) from v$mystat;

 

Script to see all active session

select username,osuser,sid,serial#, program,sql_hash_value,module from v$session where username is not null
and status =’ACTIVE’ and module is not null;

 

Script to see waiters

set linesize 1000
column waiting_session heading ‘WAITING|SESSION’
column holding_session heading ‘HOLDING|SESSION’
column lock_type format a15
column mode_held format a15
column mode_requested format a15select
waiting_session,
holding_session,
lock_type,
mode_held,
mode_requested,
lock_id1,
lock_id2
from
dba_waiters
/

Script to how active transaction in the database

 

col RBS format a15 trunc
col SID format 9999
col USER format a15 trunc
col COMMAND format a60 trunc
col status format a8 trunc
select r.name “RBS”, s.sid, s.serial#, s.username “USER”, t.status,
t.cr_get, t.phy_io, t.used_ublk, t.noundo,
substr(s.program, 1, 78) “COMMAND”
from v$session s, v$transaction t, v$rollname r
where t.addr = s.taddr
and t.xidusn = r.usn
order by t.cr_get, t.phy_io
/

 

Script to monitor the long running queries

set linesize 1000

select
OPNAME,
sid,SOFAR/TOTALWORK*100,
to_char(start_time,’dd-mon-yy hh:mi’) started,
elapsed_seconds/60,time_remaining/60
from
v$session_longops
where
sid =&sid

Script to see all lock objects

set term on;
set lines 130;
column sid_ser format a12 heading ‘session,|serial#’;
column username format a12 heading ‘os user/|db user’;
column process format a9 heading ‘os|process’;
column spid format a7 heading ‘trace|number’;
column owner_object format a35 heading ‘owner.object’;
column locked_mode format a13 heading ‘locked|mode’;
column status format a8 heading ‘status’;
select
substr(to_char(l.session_id)||’,’||to_char(s.serial#),1,12) sid_ser,
substr(l.os_user_name||’/’||l.oracle_username,1,12) username,
l.process,
p.spid,
substr(o.owner||’.’||o.object_name,1,35) owner_object,
decode(l.locked_mode,
1,’No Lock’,
2,’Row Share’,
3,’Row Exclusive’,
4,’Share’,
5,’Share Row Excl’,
6,’Exclusive’,null) locked_mode,
substr(s.status,1,8) status
from
v$locked_object l,
all_objects o,
v$session s,
v$process p
where
l.object_id = o.object_id
and l.session_id = s.sid
and s.paddr = p.addr
and s.status != ‘KILLED’
/

 

Script to see waits events

set linesize 1000
column sid format 999
column username format a15 wrapped
column spid format a8
column event format a30 wrapped
column osuser format a12 wrapped
column machine format a25 wrapped
column program format a30 wrapped
select sw.sid sid
, p.spid spid
, s.username username
, s.osuser osuser
, sw.event event
, s.machine machine
, s.program program
from v$session_wait sw
, v$session s
, v$process p
where s.paddr = p.addr
and event not in (‘pipe get’,’client message’)
and sw.sid = s.sid
/

Script to see particular session waits

select sid,seq#,wait_time,event,seconds_in_wait,state from v$session_wait where sid in (&sid)

 

Script to see all user accessing that objects

column object format a30
column owner format a10
select * from v$access where object=’&object_name’
/

Script gives information about the user sessions locking a particular object

set linesize 1000
column program format a15
column object format a15
select substr(username||'(‘|| se0.sid||’)’,1,5) “User Session”,
substr(owner,1,5) “Object Owner”,
substr(object,1,15) “Object”,
se0.sid,
substr(serial#,1,6) “Serial#”,
substr(program,1,15) “Program”,
logon_time “Logon Time”,
process “Unix Process”
from v$access ac, v$session se0
where ac.sid = se0.sid
and Object = ‘&PACKAGE’
order by logon_time,”Object Owner”,”Object”
/

Script to see the explain plan for the statement in the library cache

set linesize 9999
column QUERY format a999
set pages 250
set head off
set verify off
select id,lpad(‘ ‘,2*(depth-1)) || depth ||’.’ || nvl(position,0) || ‘ ‘|| operation || ‘ ‘|| options || ‘ ‘|| object_name ||’ ‘
||’cost= ‘|| to_char(cost)||’ ‘|| optimizer “QUERY”
from v$sql_plan
where hash_value = &sql_hash_value
order by child_number,id
/

Script to find server location

select nvl(username,’ORACLE SHADOW PROCESS’),
machine from
v$session where username is null
and rownum < 2

Script to monitor any active sql or done sql in the database

 

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFFSELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => ‘&1’, type => ‘TEXT’)
AS report FROM dual;

Script to see the top sort segment usage

col sid format 999999

col spid format a6

col tablespace format a10

col username format a25

col noexts format 9999 head EXTS

col proginfo format a25 trunc

col mbused format 999,999.90

col status format a1 trunc

set verify off

select * from (

select s.sid,

s.status,

b.spid,

s.sql_hash_value sesshash,

u.SQLHASH sorthash,

s.username,

u.tablespace,

sum(u.blocks*p.value/1024/1024) mbused ,

sum(u.extents) noexts,

u.segtype,

s.module || ‘ – ‘ || s.program proginfo

from v$sort_usage u, v$session s, v$parameter p, v$process b

where u.session_addr = s.saddr

and p.name = ‘db_block_size’

and b.addr = s.paddr

group by s.sid,s.status,b.spid,s.sql_hash_value,u.sqlhash,s.username,u.tablespace,

u.segtype,

s.module || ‘ – ‘ || s.program

order by 8 desc,4)

where rownum < 11;

 

Script to check the last analyzed for the tables in sql statement

set lin 1000

set verify off

col owner format a15

col object_name format a25

col object_type format a12

col “LAST ANALYZED” format a13

 

select do.OWNER,do.OBJECT_NAME,OBJECT_TYPE,

decode (OBJECT_TYPE,’TABLE’  , (Select LAST_ANALYZED from dba_tables where owner=do.owner and TABLE_NAME=do.object_name)  ,

‘INDEX’  , (Select LAST_ANALYZED from dba_indexes where owner=do.owner and INDEX_NAME=do.object_name) ,

‘UNKNOWN’) “LAST ANALYZED”,STATUS

from   DBA_OBJECTS do

where  OBJECT_TYPE in (‘TABLE’,’INDEX’)

and    (OWNER,OBJECT_NAME) in (select OBJECT_OWNER,OBJECT_NAME from V$SQL_PLAN where HASH_VALUE=&1)

/

This list of Useful scripts for oracle database for monitoring purpose is not complete. There are many more scripts for monitoring.I will be presenting them in subsequent posts


Leave a Reply