Dion Cho Scripts
EXEM Knowledge Base
목차 |
[편집] My Scripts
아래 스크립트들은 (주)엑셈의 조동욱 수석 컨설턴트가 테스트나 트러블 슈팅시에 쓰는 것들을 모은 것이다. 수정이 되는 대로 주기적으로 업데이트된다.
[편집] Basic
[편집] glogin.sql
$ORACLE_HOME/sqlplus/admin/glogin.sql 파일에 다음과 같은 기존 설정을 사용
--define _editor="gvim" define _editor="c:\Progra~1\acrosoft\acroedit\acroedit.exe" set sqlprompt '&_USER.@&_CONNECT_IDENTIFIER.> ' set timing on --set echo on set linesize 80 set pagesize 100 col table_name format a20 col index_name format a20 col tablespace_name format a20 col column_name format a20 col low_value format a20 col high_value format a20 col owner format a20 col username format a20 col name format a30 col value format a20 col tname format a30 col segment_name format a20 col partition_name format a20 col histogram format a20 col pid format a10 select sid, serial#, (select spid from v$process where addr = paddr) as pid from v$session where sid = (select sid from v$mystat where rownum = 1);
[편집] Session Info
[편집] me.sql
현재 세션의 아주 간략한 정보를 조회한다.
------------------------------------------------- -- @name: me -- @author: Dion Cho -- @description: basic current session info ------------------------------------------------- select saddr, sid, serial#, (select spid from v$process where addr = paddr) as pid, paddr from v$session where sid = (select sid from v$mystat where rownum = 1);
[편집] session.sql
특정 세션의 기본적인 정보를 조회한다.
---------------------------------------------
-- @name: session
-- @author: Dion Cho
-- @description: detailed snapshot info for given session
---------------------------------------------
define __SID = &1;
col process format a15
col machine format a15
col program format a15
col event format a30
col spid format 99999
col operation_type format a20
prompt '01. basic session info'
select s.sid, s.serial#, p.spid, s.machine, s.program,
(select value from v$sesstat where sid = s.sid and
statistic# = n1.statistic#) as pga,
(select value from v$sesstat where sid = s.sid and
statistic# = n2.statistic#) as uga,
last_call_et,
logon_time
from v$session s, v$statname n1, v$statname n2, v$process p
where sid = &__SID and
n1.name = 'session pga memory' and
n2.name = 'session uga memory' and
s.paddr = p.addr
;
prompt '02. session wait'
select sid, event, p1,p2,p3, seconds_in_wait, state
from v$session_wait
where sid = &__SID
;
prompt '03. sql info'
select &__SID, sharable_mem, persistent_mem, runtime_mem, executions,
fetches, buffer_gets,
sql_text
from v$sql
where address = (select sql_address from v$session where sid = &__SID)
;
prompt '04. workarea info'
select sid, operation_type, active_time, work_area_size, actual_mem_used,
max_mem_used
from v$sql_workarea_active
where sid = &__SID
;
실제 출력 예는 다음과 같다.
UKJA@ukja11> @session 140
UKJA@ukja11> @session 140
'01. basic session info'
old 9: where sid = &__SID and
new 9: where sid = 140 and
SID SERIAL#
---------- ----------
SPID
------------------------------------------------------------------------
MACHINE PROGRAM PGA UGA LAST_CALL_ET
--------------- --------------- ---------- ---------- ------------
LOGON_TIME
---------------
140 172
3100
EX-EM\UKJA sqlplus.exe 3450452 2920684 489
26-MAR-08
Elapsed: 00:00:00.03
'02. session wait'
old 3: where sid = &__SID
new 3: where sid = 140
SID EVENT P1 P2 P3
---------- ------------------------------ ---------- ---------- ----------
SECONDS_IN_WAIT STATE
--------------- ---------------------------------------------------------
140 SQL*Net message to client 1111838976 1 0
476 WAITED SHORT TIME
Elapsed: 00:00:00.00
'03. sql info'
old 1: select &__SID, sharable_mem, persistent_mem, runtime_mem, executions,
new 1: select 140, sharable_mem, persistent_mem, runtime_mem, executions,
old 5: where address = (select sql_address from v$session where sid = &__SID)
new 5: where address = (select sql_address from v$session where sid = 140)
140 SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM EXECUTIONS FETCHES
---------- ------------ -------------- ----------- ---------- ----------
BUFFER_GETS
-----------
SQL_TEXT
--------------------------------------------------------------------------------
140 791373 182308 181760 1 0
4710
SELECT COUNT(*) FROM V$MAP_LIBRARY ,V$MAP_FILE ,V$MAP_FILE_EXTENT ,
V$MAP_ELEMENT ,V$MAP_EXT_ELEMENT ,V$MAP_COMP_LIST ,V$MAP_SUBELEMENT
,V$MAP_FILE_IO_STACK ,V$SQL_REDIRECTION ,V$SQL_PLAN ,V$SQL_PLAN_STATIST
ICS ,V$SQL_PLAN_STATISTICS_ALL ,V$SQL_WORKAREA ,V$SQL_WORKAREA_ACTIVE
,V$SQL_WORKAREA_HISTOGRAM ,V$PGA_TARGET_ADVICE ,V$PGA_TARGET_ADVICE_HIST
OGRAM ,V$PGASTAT ,V$SYS_OPTIMIZER_ENV ,V$SES_OPTIMIZER_ENV ,V$SQL_OP
TIMIZER_ENV ,V$DLM_MISC ,V$DLM_LATCH ,V$DLM_CONVERT_LOCAL ,V$DLM_CON
VERT_REMOTE ,V$DLM_ALL_LOCKS ,V$DLM_LOCKS ,V$DLM_RESS ,V$HVMASTER_IN
FO ,V$GCSHVMASTER_INFO ,V$GCSPFMASTER_INFO ,GV$DLM_TRAFFIC_CONTROLLER
,V$DLM_TRAFFIC_CONTROLLER ,V$GES_ENQUEUE ,V$GES_BLOCKING_ENQUEUE ,V$G
C_ELEMENT ,V$CR_BLOCK_SERVER ,V$CURRENT_BLOCK_SERVER ,V$FILE_CACHE_TRAN
SFER ,V$TEMP_CACHE_TRANSFER ,V$CLASS_CACHE_TRANSFER ,V$BH ,V$LOCK_EL
EMENT ,V$LOCKS_WITH_COLLISIONS ,V$
Elapsed: 00:00:00.04
'04. workarea info'
old 4: where sid = &__SID
new 4: where sid = 140
SID OPERATION_TYPE ACTIVE_TIME WORK_AREA_SIZE ACTUAL_MEM_USED
---------- -------------------- ----------- -------------- ---------------
MAX_MEM_USED
------------
140 HASH-JOIN 475738662 2991104 196608
196608
140 BUFFER 475648920 194560 194560
194560
140 HASH-JOIN 475738845 2983936 196608
196608
140 HASH-JOIN 475739471 2975744 174080
174080
140 HASH-JOIN 475739073 2977792 196608
196608
140 HASH-JOIN 475739268 2975744 198656
198656
6 rows selected.
[편집] Session Monitoring
Session Stats, Latch 등을 이용해 특정 Query의 수행 이전과 수행 이후를 비교할 때 사용한다.
[편집] mon_init.sql
수행 결과를 저장할 테이블을 만든다. 한번만 수행하면 된다.
--------------------------------------------- -- @name: mon_init -- @author: Dion Cho -- @initialize workload monitoring --------------------------------------------- drop table t_mon_temp purge; drop table t_mon_latch_temp purge; -- stat monitoring create global temporary table t_mon_temp ( step char(1), sid number, statistic# number, name varchar2(64), value number ) on commit preserve rows; -- latch monitoring create global temporary table t_mon_latch_temp ( step char(1), latch# number, name varchar2(50), gets number, misses number, sleeps number, immediate_gets number, immediate_misses number, spin_gets number, wait_time number ) on commit preserve rows;
[편집] mon_on.sql
Session Monitoring을 시작한다. 특정 작업(SQL)을 수행하기 전에 수행한다.
------------------------------------- -- @name: mon_on -- @author: Dion Cho -- @description: monitor on ------------------------------------- define __SID = &1 delete from t_mon_temp; delete from t_mon_latch_temp; -- gather stat insert into t_mon_temp select '1' as step, s.sid, s.statistic#, n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = (&__SID) ; -- gather latch insert into t_mon_latch_temp select '1' as step, latch#, name, gets, misses, sleeps, immediate_gets, immediate_misses, spin_gets, wait_time from v$latch ;
[편집] mon_off.sql
Session Monitoring을 끝낸다. 특정 작업(SQL)의 수행이 종료된 후 수행한다.
-------------------------------- -- @name: mon_off -- @author: Dion Cho -- @description: monitor off -------------------------------- define __SID = &1 -- gather stat insert into t_mon_temp select '2' as step, s.sid, s.statistic#, n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = (&__SID) ; -- gather latch -- gather latch insert into t_mon_latch_temp select '2' as step, latch#, name, gets, misses, sleeps, immediate_gets, immediate_misses, spin_gets, wait_time from v$latch ; commit;
[편집] mon_show.sql
mon_on.sql과 mon_off.sql에 의해 생성된 Session Monitoring 정보를 출력한다.
---------------------------------------------- -- @name: mon_show -- @author: Dion Cho -- @description: show result of session monitor ----------------------------------------------- define __SID = &1 col name format a40 col value1 format 999,999,999 col value2 format 999,999,999 col diff format 999,999,999 -- get difference of stat select t1.name as name, t1.value as value1, t2.value as value2, t2.value - t1.value as diff from t_mon_temp t1, t_mon_temp t2 where t1.step = '1' and t2.step = '2' and t1.statistic# = t2.statistic# and (t2.value - t1.value) > 0 and t1.sid in (&__SID) and t2.sid in (&__SID) order by 4 desc ; col latch_name format a30 -- get difference of latch select t1.name as latch_name, (t2.gets - t1.gets) as d_gets, (t2.misses - t1.misses) as d_misses, (t2.sleeps - t1.sleeps) as d_sleeps, (t2.immediate_gets - t1.immediate_gets) as d_im_gets /*, (t2.immediate_misses - t1.immediate_misses) as d_im_misses, (t2.spin_gets - t1.spin_gets) as d_spins, (t2.wait_time - t1.wait_time) as wait */ from t_mon_latch_temp t1, t_mon_latch_temp t2 where t1.step = '1' and t2.step = '2' and t1.latch# = t2.latch# and ((t2.gets - t1.gets) > 0 or (t2.immediate_gets - t1.immediate_gets) > 0) order by 2 desc ;
[편집] 사용 예
실제 사용 예는 다음과 같다.
SQL> @mon_on 140 -- 140번 세션 Monitoring
SQL> ... -- 수행이 끝날 때까지 Wait
SQL> @mon_off
SQL> @mon_show
UKJA@ukja11> @mon_show
old 14: t1.sid in (&__SID) and
new 14: t1.sid in (140) and
old 15: t2.sid in (&__SID)
new 15: t2.sid in (140)
NAME VALUE1 VALUE2 DIFF
---------------------------------------- ------------ ------------ ------------
session pga memory 1,165,000 11,249,236 10,084,236
session pga memory max 1,165,000 11,249,236 10,084,236
bytes received via SQL*Net from client 3,578 5,096 1,518
recursive calls 2,287 2,729 442
buffer is not pinned count 469 721 252
session logical reads 618 744 126
consistent gets from cache (fastpath) 344 470 126
consistent gets from cache 613 739 126
no work - consistent read gets 331 457 126
table fetch by rowid 139 265 126
consistent gets 613 739 126
parse count (total) 126 190 64
opened cursors cumulative 169 233 64
calls to get snapshot scn: kcmgss 207 270 63
execute count 190 253 63
session cursor cache hits 112 175 63
CPU used by this session 5 11 6
user calls 23 24 1
parse count (hard) 14 15 1
opened cursors current 3 4 1
enqueue requests 17 18 1
21 rows selected.
Elapsed: 00:00:00.01
LATCH_NAME D_GETS D_MISSES D_SLEEPS D_IM_GETS
------------------------------ ---------- ---------- ---------- ----------
DML lock allocation 53165 0 0 0
session allocation 49055 0 0 0
row cache objects 19626 0 0 0
cache buffers chains 16921 0 0 1
shared pool 12482 0 0 0
transaction allocation 11955 0 0 0
SQL memory manager workarea li 6616 0 0 0
st latch
enqueue hash chains 5426 0 0 0
enqueues 4942 1 0 0
shared pool simulator 3633 0 0 0
messages 2433 1 0 0
checkpoint queue latch 2052 0 0 37
JS queue state obj latch 2052 0 0 0
channel operations parent latc 1563 0 0 0
...
[편집] Lock Info
[편집] lock_tree.sql
간단한 형태의 Lock Tree를 보여준다.
---------------------------------------------------
-- @name: lock_tree
-- @author: Dion Cho
-- @description: show lock tree
---------------------------------------------------
col id1 format a20
col id2 format a10
col lmode format a5
col request format a5
col h_mode format a5
col w_mode format a5
with holder as (
select
sid,
type,
(case when (type = 'TM') then
(select object_name||'('||id1||')' from all_objects where object_id = id1)
else id1|| end) as id1,
id2|| as id2,
--lmode,
lmode,
request,
ctime
from
v$lock
where
block = 1
),
waiter as (
select
sid,
type,
(case when (type = 'TM') then
(select object_name||'('||id1||')' from all_objects where object_id = id1)
else id1|| end) as id1,
id2|| as id2,
lmode,
request,
ctime
from
v$lock
where
block = 0 and
request > 0
order by ctime desc
)
select
h.sid as h_sid,
decode(h.lmode,6,'X(6)',5,'SRX(5)',4,'S(4)',3,'RX(3)',2,'RS(2)',1,'N(1)',h.lmode) as h_mode,
w.sid as w_sid,
decode(w.request,6,'X(6)',5,'SRX(5)',4,'S(4)',3,'RX(3)',2,'RS(2)',1,'N(1)',w.request) as w_mode,
h.id1,
h.id2,
w.ctime as "w_time(cs)"
from
waiter w, holder h
where
w.id1 = h.id1 and
w.id2 = h.id2
order by
1
;
[편집] CBO Info
[편집] tab_stat.sql
특정 테이블의 통계 정보를 보여준다.
-----------------------------------------------
-- @name: tab_stat
-- @author: Dion Cho
-- @description: show CBO table stat
-----------------------------------------------
define T_NAME = &1
prompt 01. table stats
-- table
select
table_name, num_rows, blocks, empty_blocks, sample_size
from
dba_tab_statistics
where
table_name = upper('&T_NAME')
;
prompt 02. column stats
-- column
select
c.table_name, c.column_name, c.num_distinct, c.density,
s.low_value, s.high_value, c.histogram
from
dba_tab_col_statistics s, dba_tab_cols c
where
c.table_name = upper('&T_NAME') and
c.table_name = s.table_name(+) and
c.column_name = s.column_name(+)
;
prompt 03. histogram stats
-- histogram
select
table_name, column_name, endpoint_number, endpoint_value
from
dba_tab_histograms
where
table_name = upper('&T_NAME')
order by column_name, endpoint_number
;
[편집] ind_stat.sql
특정 테이블 혹은 인덱스의 통계 정보를 보여준다.
----------------------------------------------
-- @name: ind_stat
-- @author: Dion Cho
-- @description: show index CBO stats
----------------------------------------------
define I_NAME = &1
prompt 01. index stats
-- table
select
table_name, index_name, blevel, leaf_blocks, clustering_factor, sample_size
from
dba_ind_statistics
where
index_name = upper('&I_NAME') or
table_name = upper('&I_NAME')
;
[편집] sys_stat.sql
System 통계 정보를 보여준다.
--------------------------------- -- @name: sys_stat -- @author: Dion Cho -- @description: get system stats --------------------------------- col sname format a30 col pname format a20 col pval2 format a20 select * from sys.aux_stats$;
[편집] 사용 예
UKJA@ukja11> @tab_stat t_spm
01. table stats
old 6: table_name = upper('&T_NAME')
new 6: table_name = upper('t_spm')
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS SAMPLE_SIZE
-------------------- ---------- ---------- ------------ -----------
T_SPM 10000 20 0 10000
Elapsed: 00:00:01.75
02. column stats
old 7: c.table_name = upper('&T_NAME') and
new 7: c.table_name = upper('t_spm') and
TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY
-------------------- -------------------- ------------ ----------
LOW_VALUE HIGH_VALUE HISTOGRAM
-------------------- -------------------- --------------------
T_SPM C2 10 .1
80 C10A NONE
T_SPM C1 10000 .0001
C102 C302 NONE
Elapsed: 00:00:01.42
03. histogram stats
old 6: table_name = upper('&T_NAME')
new 6: table_name = upper('t_spm')
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- -------------------- --------------- --------------
T_SPM C1 0 1
T_SPM C1 1 10000
T_SPM C2 0 0
T_SPM C2 1 9
Elapsed: 00:00:00.34
[편집] Dump
트러블슈팅에 필요한 Dump를 생성하는 것을 도와주는 스크립트들이다.
[편집] dba.sql
십진수 형태의 DBA를 입력받아 File 번호, Block 번호로 변환다.
---------------------------------------------------- -- @author: Dion Cho -- @description: convert dba to file# and block# ---------------------------------------------------- define __DBA = &dba; select dbms_utility.data_block_address_file(&__DBA) as f#, dbms_utility.data_block_address_block(&__DBA) as b# from dual ;
[편집] dump_dba.sql
십진수 형태의 DBA를 입력받아 해당 블록을 덤프한다.
----------------------------------
-- @name: dump_dba
-- @author: Dion Cho
-- @description: dump block based on dba
----------------------------------
define __DBA = &dba;
declare
v_f number;
v_b number;
begin
select
dbms_utility.data_block_address_file(&__DBA),
dbms_utility.data_block_address_block(&__DBA)
into v_f, v_b
from dual;
dbms_output.put_line('file# = ' || v_f || ', block# = ' || v_b);
execute immediate 'alter system dump datafile ' || v_f || ' block ' || v_b;
end;
/
[편집] Misc
[편집] para.sql
Hidden Parameter를 포함한 Parameter 정보를 보여준다.
select ksppinm name,
ksppstvl value,
decode(bitand(ksppiflg/256,1),1,'true','false') ses_modifiable,
decode(bitand(ksppiflg/65536,3),1,'immediate',2,'deferred',3,'immediate','false') sys_modifiable,
ksppdesc description
from sys.x$ksppi i, sys.x$ksppcv v
where i.indx = v.indx
and i.ksppinm like '%&1%';