모니터링 스크립트

사용법 : ./mon.sh mon.sql 3 (수행간격)

===============================

if [ $# -eq 1 ]

then

 arg2=2

elif [ $# -eq 2 ]

then

 arg2=$2

else

    echo "Usage : mon.sh sql term";

    exit;

fi

arg1=$1

while true

do

sqlplus -s '/ as sysdba' <<EOF

set linesize 200

set pause off

set pagesize 120

@$arg1

EOF

sleep $arg2

done

- mon.sql 화일내용

prompt ##################################################################################################################

!date

prompt ========================================================

prompt = Session Wait

prompt ========================================================

col DB      format 99

col sid     format 999999

col serial# format 99999

col spid    format a7

col cli_spid format a10

col process format a11

col event   format a30

col p       format a20

col W       format 999

col program format a15

col C       format a2

col blkSid  format 99999

col object_name format a20

set linesize 170 pagesize 100 trimspool on

select 

       a.inst_id "DB",

       a.sid, a.serial#, 

       (select p.spid from gv$process p where (a.inst_id = p.inst_id) and (a.paddr = p.addr))  spid, a.status,a.event,a.process as CLI_SPID,

       decode(a.event,'enqueue',

              decode(chr(bitand(a.p1,-16777216)/16777215)||chr(bitand(a.p1, 16711680)/65535),'TX',

              chr(bitand(a.p1,-16777216)/16777215)||chr(bitand(a.p1, 16711680)/65535)||to_char( bitand(a.p1, 65535) )||','||

              'usn.slot:'||bitand(a.p2, 4294901760)/65536||'.'||bitand(a.p2, 65535)||','||'wrap:'||a.p3,

              a.p1||','||a.p2||','||a.p3),a.p1||','||a.p2||','||a.p3) P,

       a.seconds_in_wait W, 

       substr(a.program,1,15) program, 

       decode(a.command,3,'S',2,'I',6,'U',7,'D',command) C,

       a.sql_id,

       substr((SELECT OBJECT_NAME FROM DBA_OBJECTS C WHERE C.OBJECT_ID = ROW_WAIT_OBJ# ),1,20) object_name,

       a.blocking_session blkSid, -- comment out for 10.2.0.2

       substr((SELECT NAME FROM V$LATCH WHERE LATCH# = p2 and event = 'latch free' ),1,10) LATCH_NAME

  from gv$session a

 where a.event not like 'SQL*Net message from client%'

   and a.event not like 'rdbms ipc%'

   and a.event not like 'smon timer'

   and a.event not like 'pmon timer'

   and a.event not like '%slave wait%'

   and a.event not like 'lock manager wait for remote message'

   and a.event not like 'PX Deq%'

   and a.event not like 'null event'

   and a.event not like 'gcs remote message'

   and a.event not like 'ges remote message'

   and a.event not like 'wakeup time manager'

   and a.event not like 'Queue Monitor%Wait'

   and a.event not like 'DIAG idle wait'

   and a.event not like 'Streams AQ:%'

   and a.sid <> (select sid from v$mystat where rownum = 1)

   and a.wait_class <> 'Idle'

order by a.inst_id, a.event, a.sid

/

prompt ========================================================

prompt =  v$latch_holder

prompt ========================================================

set linesize 150 pagesize 100 trimspool on

col name format a40

select 

'[latch]'||to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') dt,

INST_ID,PID,SID,LADDR,NAME,GETS

from gv$latchholder

/

prompt ========================================================

prompt = 'Event summary'  having count(*) >= 3 

prompt ========================================================

col DB      format 99

col event   format a30

col count1   format 999,999

set linesize 150 pagesize 100 trimspool on

select '[event]'||to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') dt,

        a.inst_id "DB", a.event,count(*) as count1

  from gv$session a

 where a.event not like 'SQL*Net message from client%'

   and a.event not like 'rdbms ipc%'

   and a.event not like 'smon timer'

   and a.event not like 'pmon timer'

   and a.event not like '%slave wait%'

   and a.event not like 'lock manager wait for remote message'

   and a.event not like 'PX Deq%'

   and a.event not like 'null event'

   and a.event not like 'gcs remote message'

   and a.event not like 'ges remote message'

   and a.event not like 'wakeup time manager'

   and a.event not like 'Queue Monitor%Wait'

   and a.event not like 'DIAG idle wait'

   and a.event not like 'Streams AQ:%'

   and a.sid <> (select sid from v$mystat where rownum = 1)

 group by a.inst_id, a.event

 having count(*) >= 3

 order by count(*) desc

/

--prompt ========================================================

--prompt = Program Info. having count(*) > 1

--prompt ========================================================

--col program format a40

--select '[program]'||to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') dt,

--         program, count(*) as count1

--  from gv$session a

-- where a.event not like 'SQL*Net message from client%'

--   and a.event not like 'rdbms ipc%'

--   and a.event not like 'smon timer'

--   and a.event not like 'pmon timer'

--   and a.event not like '%slave wait%'

--   and a.event not like 'lock manager wait for remote message'

--   and a.event not like 'PX Deq%'

--   and a.event not like 'null event'

--   and a.event not like 'gcs remote message'

--   and a.event not like 'ges remote message'

--   and a.event not like 'wakeup time manager'

--   and a.event not like 'Queue Monitor%Wait'

--   and a.event not like 'DIAG idle wait'

--   and a.event not like 'Streams AQ:%'

--   and a.sid <> (select sid from v$mystat where rownum = 1)

-- group by program

-- having count(*) > 1

-- order by count(*) desc

--/

----

----prompt ========================================================

----prompt = 'gc buffer busy' - p1, p2 , count 

----prompt ========================================================

----select p1, p2, count(*) cnt

----  from gv$session

---- where event = 'gc buffer busy'

---- group by p1, p2

---- order by cnt desc

----/

--prompt ========================================================

--prompt = 'gv$resource ' - MAX_UTILIZATION > 0.8 * INITIAL_ALLOCATION

--prompt ========================================================

--set linesize 150 pagesize 100 trimspool on

--col RESOURCE_NAME format a17

--select 

--'[resource]'||to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') dt, INST_ID,RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,INITIAL_ALLOCATION,LIMIT_VALUE

--from gv$resource_limit  

--where   MAX_UTILIZATION > 

--0.8*to_number( decode(INITIAL_ALLOCATION,' UNLIMITED','99999999999',INITIAL_ALLOCATION),'999999999999') 

--and RESOURCE_NAME not in ('ges_cache_ress','ges_big_msgs','gcs_shadows','gcs_resources')

--order by INST_ID asc 

--/

prompt ========================================================

prompt = Session Count.

prompt ========================================================

set linesize 150 pagesize 100 trimspool on

col dt format a29

select '[ses_cnt]'||to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') dt,

       inst_id,

       sum(decode(status,'ACTIVE',1))-1 "Active",

       count(*) "Total"

  from gv$session

 where type <> 'BACKGROUND'

    or username is not null

 group by inst_id

 order by inst_id asc

/

--

--prompt ========================================================

--prompt = Insert Session Wait

--prompt ========================================================

--col DB      format 99

--col sid     format 999999

--col serial# format 99999

--col process format a11

--col event   format a30

--col p       format a20

--col W       format 999

--col program format a15

--col C       format a2

--col blkSid  format 99999

--col object_name format a20

--col spid format a6

--set linesize 170 pagesize 100 trimspool on

--

--select 

--       a.inst_id "DB",

--       a.sid, a.serial#, 

--       (select p.spid from gv$process p where (a.inst_id = p.inst_id) and (a.paddr = p.addr))  spid, a.status,a.event,a.process as CLI_SPID,

----       decode(a.event,'enqueue',

----              decode(chr(bitand(a.p1,-16777216)/16777215)||chr(bitand(a.p1, 16711680)/65535),'TX',

----              chr(bitand(a.p1,-16777216)/16777215)||chr(bitand(a.p1, 16711680)/65535)||to_char( bitand(a.p1, 65535) )||','||

----              'usn.slot:'||bitand(a.p2, 4294901760)/65536||'.'||bitand(a.p2, 65535)||','||'wrap:'||a.p3,

----              a.p1||','||a.p2||','||a.p3),a.p1||','||a.p2||','||a.p3) P,

--       a.seconds_in_wait W, 

--       substr(a.program,1,15) program, 

--       decode(a.command,3,'S',2,'I',6,'U',7,'D',command) C,

--       a.sql_id,

--       substr((SELECT OBJECT_NAME FROM DBA_OBJECTS C WHERE C.OBJECT_ID = ROW_WAIT_OBJ# ),1,20) object_name,

--       a.blocking_session blkSid,

--       substr((SELECT NAME FROM V$LATCH WHERE LATCH# = p2 and event = 'latch free' ),1,10) LATCH_NAME

--  from gv$session a

-- where a.event not like 'SQL*Net message from client%'

----   and a.event not like 'rdbms ipc%'

----   and a.event not like 'smon timer'

----   and a.event not like 'pmon timer'

----   and a.event not like '%slave wait%'

----   and a.event not like 'lock manager wait for remote message'

----   and a.event not like 'PX Deq%'

----   and a.event not like 'null event'

----   and a.event not like 'gcs remote message'

----   and a.event not like 'ges remote message'

----   and a.event not like 'wakeup time manager'

----   and a.event not like 'Queue Monitor%Wait'

----   and a.event not like 'DIAG idle wait'

----   and a.event not like 'Streams AQ:%'

--   and a.sid <> (select sid from v$mystat where rownum = 1)

--   and a.COMMAND = 2

--   and a.WAIT_TIME = 0 and a.SECONDS_IN_WAIT > 5

---- order by a.inst_id, a.event, a.sid

--/

--

--prompt ========================================================

--prompt =  DFS lock handle

--prompt ========================================================

--col DB      format 99

--col inst format 99

--col b_inst format 99

--col sid     format 999999

--col blk_sid     format 999999

--col serial# format 99999

--col blk_seri format 99999

--col process format a11

--col Mode   format a4

--col P2TEXT format a6

--col P3TEXT format a6

--col username format a9

--col W       format 999

--col program format a15

--col machine format a10

--col blkSid  format 99999

--col object_name format a20

--col spid format a6

--col dt format a29

--set linesize 170 pagesize 100 trimspool on

--

--select '[DFSlock]'||to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') dt, inst_id as inst,chr(to_char(bitand(p1,-16777216))/16777215)||

--      chr(to_char(bitand(p1, 16711680))/65535) "Lock",

--      to_char( bitand(p1, 65535) )    "Mode",  P2TEXT,        P2,     P3TEXT, P3,

--sid,serial#,username,program,sql_id

--,BLOCKING_INSTANCE as b_inst,BLOCKING_SESSION as blk_sid

----,BLOCKING_SESSION_STATUS,PREV_SQL_ID, ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#

--FROM gv$session

--WHERE event = 'DFS lock handle'

--/

--

--prompt ========================================================

--prompt = Global Enqueue 

--prompt ========================================================

--set numwidth 5

--col inst format 99

--column state format a8 tru;

--column event format a25 tru;

--select dl.inst_id as inst, s.sid, s.serial#,p.spid,s.username,s.program, dl.resource_name1, 

--decode(substr(dl.grant_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',

--'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',

--'KJUSEREX','Exclusive',request_level) as grant_level,

--decode(substr(dl.request_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',

--'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',

--'KJUSEREX','Exclusive',request_level) as request_level, 

--decode(substr(dl.state,1,8),'KJUSERGR','Granted','KJUSEROP','Opening',

--'KJUSERCA','Canceling','KJUSERCV','Converting') as state,

-- sw.event, sw.seconds_in_wait W

--from gv$ges_enqueue dl, gv$process p, gv$session s, gv$session_wait sw

--where blocker = 1

--and (dl.inst_id = p.inst_id and dl.pid = p.spid)

--and (p.inst_id = s.inst_id and p.addr = s.paddr)

--and (s.inst_id = sw.inst_id and s.sid = sw.sid)

--order by sw.seconds_in_wait desc

--/

--prompt ========================================================

--prompt = LOCK Blocking

--prompt ========================================================

--set verify off linesize 100

--col username format a10

--col sid format 999

--col lock_type format a18

--col mode_held format a11

--col mode_requested format a10

--col lock_id1 format a8

--col lock_id2 format a8

--select /*+ rule */ a.sid,

--decode(a.type,

--'MR', 'Media Recovery',

--'RT', 'Redo Thread',

--'UN', 'User Name',

--'TX', 'Transaction',

--'TM', 'DML',

--'UL', 'PL/SQL User Lock',

--'DX', 'Distributed Xact',

--'CF', 'Control File',

--'IS', 'Instance State',

--'FS', 'File Set',

--'IR', 'Instance Recovery',

--'ST', 'Disk Space TranX',

--'IR', 'Instance Recovery',

--'ST', 'Disk Space TranX',

--'TS', 'Temp Segment',

--'IV', 'Lib Cache Invalid',

--'LS', 'Log Start/Switch',

--'RW', 'Row Wait',

--'SQ', 'Sequence Number',

--'TE', 'Extend Table',

--'TT', 'Temp Table',

--a.type) lock_type,

--decode(a.lmode,

--0, 'None', /* Mon Lock equivalent */

--1, 'Null', /* N */

--2, 'Row-S (SS)', /* L */

--3, 'Row-X (SX)', /* R */

--4, 'Share', /* S */

--5, 'S/Row-X (SSX)', /* C */

--6, 'Exclusive', /* X */

--to_char(a.lmode)) mode_held,

--decode(a.request,

--0, 'None', /* Mon Lock equivalent */

--1, 'Null', /* N */

--2, 'Row-S (SS)', /* L */

--3, 'Row-X (SX)', /* R */

--4, 'Share', /* S */

--5, 'S/Row-X (SSX)', /* C */

--6, 'Exclusive', /* X */

--to_char(a.request)) mode_requested,

--to_char(a.id1) lock_id1, to_char(a.id2) lock_id2, BLOCK

--from v$lock a

--where a.type NOT IN ( 'MR','RT' )

--and (id1,id2) in

--( select b.id1, b.id2 from v$lock b

--where b.id1=a.id1

--and b.id2=a.id2

--and b.request > 0 )

--/