행위

DBA 모니터링 sqlplus용

DB CAFE

thumb_up 추천메뉴 바로가기

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

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

1.1 현재 세션 모니터링[편집]

1.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.1.2 SQL_ID로 FULL SQL 문장 보기[편집]

set verify off
set long 4000
col sql_fulltext for a400

--select inst_id,sql_text from gv$sqltext where sql_id='&1' order by inst_id,piece;
select inst_id,sql_fulltext from gv$sqlstats where sql_id='&1' order by inst_id;

1.1.3 현재 실행중인 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.2 SQL 실행 계획 확인[편집]

1.2.1 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.2.2 병렬/실시간 플랜 조회 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.3 테이블스페이스 모니터링[편집]

1.3.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.3.2 템프 테이블스페이스 조회[편집]

-- temp.sql
set timing off
set feedback off
set lines 1000
set pages 1000
col session_info heading "SID,Serial#" format a20
col "SIZE" format 99,999,999
col tablespace_name format a15
col module format a50

set heading off
select '------[[ 각 User Session별 사용량 ]] --------' from dual;
set heading on

select tablespace tablespace_name
     , to_char(s.sid) || ',' || to_char(s.serial#) session_info
     , s.module module
     , sum(so.blocks * 8 /1024) as "SIZE"
     , 'M' Unit
  from gv$session s, gv$sort_usage so
 where s.inst_id = so.inst_id
   and s.saddr = so.session_addr
   and s.serial# = so.session_num
   and s.status = 'ACTIVE'
 group by tablespace ,s.sid,s.serial#,module
 order by "SIZE"; 

set heading off
select '---- [[ 각 인스턴스별 Temp Tablespace 총 사용량 ]] --------' from dual;
set heading on

select inst_id
     , b.tablespace_name
     , 'Used Total Size' sessin_info
     , nvl(sum(so.blocks * 8/1024),0) as "SIZE"
     , 'M' Unit
  from gv$sort_usage so , dba_tablespaces b
 where b.tablesapce_name = so.tablespace(+)
   and b.contents = 'TEMPORARY'
 group by b.tablespace_name , init_id
   order by 1,2;

set heading off
select '---- [[ 각 Temp Tablespace별 총 사용량 ]] --------' from dual;
set heading on

select b.tablespace_name
     , 'Used Total Size' sessin_info
     , nvl(sum(so.blocks * 8/1024),0) as "SIZE"
     , 'M' Unit
  from gv$sort_usage so , dba_tablespaces b
 where b.tablesapce_name = so.tablespace(+)
   and b.contents = 'TEMPORARY'
 group by b.tablespace_name , init_id
   order by 1,2;

set heading off
select '---- [[ 각 Temp Tablespace별 총 할당량 ]] --------' from dual;
set heading on

select b.tablespace_name
     , 'Temp Total Size' sessin_info
     , trunc(sum(bytes)/1024/1024,2) as "SIZE"
     , 'M' Unit
  from dba_temp_files , dba_tablespaces b
 where b.contents = 'TEMPORARY'
   and a.tablespace_name = b.tablespace_name 
group by b.tablespace_name
   order by 1;

select '' from daul;
set heading on
set timing on
set feedback on

1.3.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

1.4 모니터링용 sql 반복 실행 쉘스크립트[편집]

-- aix 용 스크립트
#!/bin/sh
interval=10 # sleep interval in seconds
reps=100000 # ntimes
j=1         # loop variable

while [ $j -le ${reps} ] 
do
echo " ------ cycle ${j} / ${reps} ------"
#@sg.sql is user execute sql file for monitor 
sqlplus -s '/as sysdba'<<EOF
@sg.sql ${j}
exit
EOF
sleep ${interval}
j=`expr ${j} + 1`
done
exit 0
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=DBA_모니터링_sqlplus용&oldid=10595"