행위

DBA 모니터링 sqlplus용

DB CAFE

Dbcafe (토론 | 기여)님의 2023년 12월 28일 (목) 11:50 판 (→‎롤백 세그먼트 조회)
thumb_up 추천메뉴 바로가기

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

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

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

-- s.sql
-- created by dbcafe.co.kr

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 
-- created by dbcafe.co.kr
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;

1.3 XPLAN 플랜 조회[편집]

-- xp.sql
-- created by dbcafe.co.kr
variable sql_id varchar2(100)
accept sql_id prompt 'Input SQL ID : '

set verifyy off
set long 1000000
set longchunksize 2000000
set linesize 30000
set pagesize 0
set trim on
set trimspool on
set echo off
set feedback off

select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST -ROWS REMOTE PARALLEL OUTLINE PREDICATE'));

set verify on

1.4 병렬/실시간 플랜 조회 REAL MONITOR[편집]

-- rp.sql
-- created by dbcafe.co.kr
variable sql_id varchar2(100)
accept sql_id prompt 'Input SQL ID : '

set verifyy off
set long 1000000
set longchunksize 2000000
set linesize 30000
set pagesize 0
set trim on
set trimspool on
set echo off
set feedback off

select dbms_sqltune.report_sql_monitor(sql_id => '&sql_id',report_level => 'ALL',type => 'TEXT')
  from dual;

set verify on

1.5 테이블스페이스 모니터링[편집]

1.5.1 테이블스페이스 사이즈 조회[편집]

-- ts.sql
-- created by dbcafe.co.kr

set timing off
set feedback off
set lines 1000
set pages 1000
col session_info heading "SID,Serial#" format a20
col "Alloc(M)" format 99,999,999
col "Free(M)" format 99,999,999
col "Used(M)" format 99,999,999
col tablespace_name format a15

select d.tablespace_name
     , round(sum(d.bytes)/1024/1024) "Alloc(M)"  
     , round(sum(f.bytes)/1024/1024) "Free(M)"
     , round((sum(d.bytes)-sum(f.bytes))/1024/1024) "Used(M)"
     , round(nvl(sum(f.bytes),0)/sum(d.bytes) * 100,2) "Free(%)"
     , 100 - round(nvl(sum(f.bytes),0)/sum(d.bytes) * 100,2) "Used(%)"
  from (select d.tablespace_name,d.file_id,sum(d.byte) bytes
          from dba_data_files d
         group by d.tablespace_name,d.file_id) d
     , (select d.tablespace_name ,d.file_id,sum(d.bytes) bytes 
          from dba_free_space d
         group by d.tablespace_name,d.file_id) f
 where d.tablespace_name = f.tablespace_name(+)
   and d.file_id = f.file_id(+)
   and regexp_like(d.tablespace_name,'SYSTEM|UNDO|DBA')
 group by d.tablespace_name
 order by 1

1.5.2 템프 테이블스페이스 조회[편집]

1.5.3 롤백 세그먼트 조회[편집]

-- rb.sql
-- created by dbcafe.co.kr

set linesize 200
set pagesize 25
variable v_interval_1 varchar2(100)
variable v_interval_2 varchar2(100)

col no format 999
col undo_blocks format 9,999,999 heading "Blocks"
col undo_size format 99,999 heading "Size(M)"
col undo_record format 999,999,999 heading "Records"
col no format 999
col no format 999
col no format 999
col no format 999
col no format 999
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=DBA_모니터링_sqlplus용&oldid=10559"