행위

DBA 모니터링 sqlplus용

DB CAFE

Dbcafe (토론 | 기여)님의 2023년 12월 28일 (목) 11:19 판 (→‎현재 사용중인 SQL별 그룹핑 조회 (병렬처리확인))
thumb_up 추천메뉴 바로가기

1 DBA Sqlplus용 모니터링 SQL[편집]

  1. 서버 터미널에서 sqlplus를 이용하여 모니터링
  2. sqplus 에서 @xx 로 수행 (예시 xx.sql 로 저장한경우)

1.1 현재 사용중인 SQL 조회[편집]

-- s.sql
set linesize 1000
set pagesize 1200
set recsep off
set verify off
set feedback on
clear column

col username1 heading "UserName" format a7
col sid1 heading "SID/Ser#" format a10
col status1 heading "S" format a10
col blocking1 heading "Blocking" format a11
col wait_event1 heading "Wait_Event" format a20
col pga1 heading "PGA" format 9999
col lce1 heading "LCET" format 99999
col module heading "MODULE" format a10
col action heading "ACTION" format a20
col sqlid heading "SQL_ID" format a15
col sql_text1 heading "SQL" format a51
col seconds_in_wait heading "W/sec" format 9999 
col osuser1 heading "OSUSER" format a10 
col user_info1 heading "USER_INFO" format a15 
col spid1 heading "SPID" format a9 
col cpid1 heading "CPID" format a9 
col logon1 heading "LOGON" format a18
col machine1 heading "MACHINE" format a12
col kill1 heading "Kill_OS" format a20
col kill2 heading "Kill_DB" format a50

select substr(s.username,1,8) as username1
     , to_char(s.sid)||','||to_char(s.serial#) as sid1
     , substr(status,1,1) as status1
     , s.module
--     , s.action as action
     , s.seconds_in_wait as seconds_in_wait
     , substr(s.event,1,25) as wait_event1
     , last_call_et as lce1
     , s.sql_id as sqlid
     , trim((select substr(sql_text,1,71) form v$sql sq where sq.sql_id=s.sql_id and rownum<=1)) as sql_text1
     , s.osuser as osuser1
     , s.machine as machine1
     , to_char(logon_time,'yyyymmdd HH24:MI:SS') as logon1
     , s.process as cpid1
     , p.spid as spid1
     , s.sql_hash_value
     , 'kill -9 ' as kill1
     , 'alter system kill session '||''''||s.sid||','||s.erial#||''''||';' as kill2
--   , s.terminal as user_info1
  from v$session s
     , v$process p
 where s.paddr = p.addr
   and s.status = 'ACTIVE'
   and s.useranem is not null
   and s.event not in ('SQL*Net message from client','rdbms ipc message','ges remote message')
   and (machine,port) <> (select machine,port from v$session where sid=sys_context('USERENV','SID')) -- myself session 
 order by status1 desc,username1 desc,substr(s.action,1,3) desc ,module,lce1;

1.2 현재 사용중인 SQL별 그룹핑 조회 (병렬처리확인)[편집]

-- sg.sql
set linesize 350
set pagesize 1200
set recsep off
set verify off
set feedback on
clear column

col username1 heading "UsrName" format a7
col cnt heading "CNT" format 999
col status heading "S" format a3
col blocking1 heading "Blocking" format a11
col wait_event1 heading "Wait_Event" format a20
#col pga1 heading "PGA" format 9999
col lce1 heading "LCET" format 99999
col module heading "MODULE" format a10
col action heading "ACTION" format a20
col sqlid heading "SQL_ID" format a15
col sql_text1 heading "SQL" format a51
col seconds_in_wait heading "W/sec" format 9999 
col osuser1 heading "OSUSER" format a10 
col terminal1 heading "Terminal" format a15 
#col spid1 heading "SPID" format a9 
#col cpid1 heading "CPID" format a9 
col logon1 heading "LOGON" format a18
col machine1 heading "MACHINE" format a12
#col kill1 heading "Kill_OS" format a20
#col kill2 heading "Kill_DB" format a50

select substr(s.username,1,8) as username1
--     , to_char(s.sid)||','||to_char(s.serial#) as sid1
     , substr(status,1,1) as status
     , count(*) cnt
     , s.module
--     , s.action as action
     , max(s.seconds_in_wait) as seconds_in_wait
--     , substr(s.event,1,25) as wait_event1
--     , last_call_et as lce1
     , s.sql_id as sqlid
     , trim((select substr(sql_text,1,71) form v$sql sq where sq.sql_id=s.sql_id and rownum<=1)) as sql_text1
     , s.osuser as osuser1
     , s.machine as machine1
     , to_char(logon_time,'yyyymmdd HH24:MI:SS') as logon1
     , s.terminal as terminal1   
  from v$session s
     , v$process p
 where s.paddr = p.addr
   and s.status = 'ACTIVE'
   and s.useranem is not null
   and s.event not in ('SQL*Net message from client','rdbms ipc message','ges remote message')
   and (machine,port) <> (select machine,port from v$session where sid=sys_context('USERENV','SID')) -- myself session 
 group by substr(s.username,1,8) ,status,s.module,s.osuser,s.machine,s.sql_id,s.terminal;
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=DBA_모니터링_sqlplus용&oldid=10554"