Dion Cho Scripts

EXEM Knowledge Base

Jump to: navigation, 찾기

목차

[편집] 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%';