행위

"DBA 모니터링 sqlplus용"의 두 판 사이의 차이

DB CAFE
(→‎DBA Sqlplus용 모니터링 SQL)
60번째 줄: 60번째 줄:
 
   and (machine,port) <> (select machine,port from v$session where sid=sys_context('USERENV','SID')) -- myself session  
 
   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;
 
  order by status1 desc,username1 desc,substr(s.action,1,3) desc ,module,lce1;
 +
</source>
 +
 +
=== 현재 사용중인 SQL별 그룹핑 조회 (병렬처리확인) ===
 +
<source lang=sql>
 +
-- s.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;
 
</source>
 
</source>

2023년 12월 28일 (목) 11:18 판

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별 그룹핑 조회 (병렬처리확인)[편집]

-- s.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=10553"