오라클 과도한 리두, 아카이브 로그를 생성하는 세션을 찾는 방법

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.10.0.0

 

방법 : 오라클 과도한 리두,  아카이브 로그를 생성하는 세션을 찾는 방법

v$sess_io 뷰에서 BLOCK_CHANGES 컬럼은 세션에 의해 변경된 블록의 양을 나타냄

이 값이 높은 경우 리두 로그를 많이 생성 하는 세션으로 볼수 있음

SQL>
set lines 200 pages 1000
col username for a20
col program for a40
select s.sid, s.serial#, s.username, s.program,
i.block_changes
from v$session s, v$sess_io i
where s.sid = i.sid
order by 5 desc, 1, 2, 3, 4;
 
       SID    SERIAL# USERNAME           PROGRAM                    BLOCK_CHANGES
---------- ---------- -------------------- ---------------------------------------- -------------
    80    12920               oracle@ORACLE19 (M001)                93663
    78     7242               oracle@ORACLE19 (M003)                89802
    21    57717               oracle@ORACLE19 (SMON)                74235
    77    44761               oracle@ORACLE19 (M005)                41157
    74    17985               oracle@ORACLE19 (W007)                10116
    76    19078               oracle@ORACLE19 (M002)                 9979
    33    39339               oracle@ORACLE19 (W002)                 9808
    24     9581               oracle@ORACLE19 (W000)                 9761
    54    59461               oracle@ORACLE19 (W003)                 9668
    72    27985               oracle@ORACLE19 (W005)                 9553
    56    24304               oracle@ORACLE19 (W004)                 9218
.
.
 
54 rows selected.

 

 

v$sesstat에서 현재 redo size 가 많은 세션 확인

SQL>
set lines 200 pages 1000
col sid for 99999
col username for a10
col program for a30
col module for a30
select st.sid, s.serial#, s.username, round(st.value/1024/1024, 2) redo_mb,
s.status, substr(s.program,1,22) "program", s.type,
s.module, s.sql_id
from v$session s, v$sesstat st, v$statname sn
where 1=1
and sn.statistic# = st.statistic#
and s.sid = st.sid
and sn.name = 'redo size'
and st.value != 0
order by redo_mb desc;
 
   SID      SERIAL# USERNAME          REDO_MB STATUS   program                  TYPE     MODULE             SQL_ID
------ ---------- ---------------- ---------- -------- ------------------------------ ---------- ------------------------------ -------------
    29           25 SYSTEM        83.26 ACTIVE   sqlplus@oracle50 (TNS          USER     SQL*Plus            8xt2s6pfbxc2u
    10        1              .05 ACTIVE   oracle@oracle50 (DBW0)          BACKGROUND
    15        1              .03 ACTIVE   oracle@oracle50 (MMON)          BACKGROUND
     1        5 SYS              .02 ACTIVE   sqlplus@oracle50 (TNS          USER     sqlplus@oracle50 (TNS V1-V3)    gf6pa48ah2hrv
    25        5              .01 ACTIVE   oracle@oracle50 (CJQ0)          BACKGROUND
    13        1              .01 ACTIVE   oracle@oracle50 (SMON)          BACKGROUND
 
6 rows selected.

 

 

sql text 확인

SQL> 
select sql_id, sql_text 
from v$sql 
where sql_id = '8xt2s6pfbxc2u';
 
SQL_ID         SQL_TEXT
-------------  ---------------
8xt2s6pfbxc2u   insert into test select * from test

 

 

v$transaction 뷰에서 used_ublk, used_urec 컬럼은

트랜잭션에서 액세스 한 UNDO 블록 및 UNDO 레코드의 양에 대한 정보가 포함됨

used_ublk, used_urec 컬럼값이 높은 경우 리두 로그를 많이 생성 하는 세션으로 볼수 있음

SQL>
set lines 200 pages 1000
col username for a20
col program for a40
select s.sid, s.serial#, s.username, s.program, 
t.used_ublk, t.used_urec
from v$session s, v$transaction t
where s.taddr = t.addr
order by 5 desc, 6 desc, 1, 2, 3, 4;
 
       SID    SERIAL# USERNAME           PROGRAM                     USED_UBLK    USED_UREC
---------- ---------- -------------------- ---------------------------------------- ---------- ----------
    87    20179 IMSI           sqlplus@ORACLE19 (TNS V1-V3)            427        38347

 

 

조금더 자세한 내용은 아래 참조 링크를 확인하면됨

 

 

참조 : 167492.1

positivemh.tistory.com/637 로그스위치 과다 발생 시 확인 사항