다른 명령
DBA Sqlplus용 모니터링 SQL
- 서버 터미널에서 sqlplus를 이용하여 모니터링
- sqplus 에서 @xx 로 수행 (예시 xx.sql 로 저장한경우)
현재 세션 모니터링
현재 실행중인 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;
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;
현재 실행중인 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;
SQL 실행 계획 확인
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
병렬/실시간 플랜 조회 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
테이블스페이스 모니터링
테이블스페이스 사이즈 조회
-- 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.bytes) 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
템프 테이블스페이스 조회
-- 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.tablespace_name = so.tablespace(+) and b.contents = 'TEMPORARY' group by b.tablespace_name , inst_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.tablespace_name = so.tablespace(+) and b.contents = 'TEMPORARY' group by b.tablespace_name , inst_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 dual; set heading on set timing on set feedback on
롤백 세그먼트 조회
-- 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 99 col sid_serial format a10 col orauser format a20 col program format a30 col osuser format a30 col machine format a30 col terminal format a30 col TABLESPACE_NAME format a40 col undoseg format a30 col Undo format a40 col start_time a40 SELECT TO_CHAR (s.sid) || ',' || TO_CHAR (s.serial#) sid_serial , NVL (s.username, 'None') orauser , s.program, s.osuser, s.machine , s.terminal,RS.TABLESPACE_NAME , r.name undoseg , t.used_ublk * TO_NUMBER (x.VALUE) /1024 || 'K' "Undo" , t.start_time FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x, sys.dba_rollback_segs rs WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) AND x.name = 'db_block_size' AND rs.segment_name = r.name; -- and r.name = '원하는 세그먼트 이름' -- and ts.tablespace_name='언두이름'
모니터링용 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
sqlplus 쉘스크립트에서 한글 깨질때
- 원인) 오라클의 NLS_LANG 이 OS(리눅스,유닉스) 와 맞지 않기 때문
- 조치 방법
1.대상 DB의 NLS_LANG 정보 조회
SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER IN ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');
2. 쉘스크립트 파일에 적용(.bashrc_profile 에 적용하면 글로벌 하게 적용)
vi xxx.sh export NLS_LANG=KOREAN_KOREA.AL32UTF8