[Oracle] DBA에게 유용한 스크립트 활용법

dba로 일하다 보면 자주 발생하는 문제들이 2가지가 있는데 이를 빨리 해결해줘야합니다.

1. 락(lock) 문제릋 찾아서 Kill 시키는 작업

2. 악성 SQL을 찾아서 Kill 시키는 작업

위의 작업을 빨리 수행할 수 있도록 스크립트로 저장하겠습니다.

---

실습1. 아래의 락 웨이팅 상태를 만듭니다.

아래의 스크립트를 sqldeveloper 에서 수행합니다.

col holder for a15
col waiter for a15
select decode(status,'INACTIVE',username || ' ' || sid || ',' || serial#,'lock') as Holder,
       decode(status,'ACTIVE',  username || ' ' || sid || ',' || serial#,'lock') as waiter, sid, serial#, status
    from( select level as le, NVL(s.username,'(oracle)') AS username,
    s.osuser,
    s.sid,
    s.serial#,
    s.lockwait,
    s.module,
    s.machine,
    s.status,
    s.program,
    to_char(s.logon_TIME, 'DD-MON-YYYY HH24:MI:SS') as logon_time
       from v$session s
      where level>1
                or EXISTS( select 1
    from v$session
    where blocking_session = s.sid)
      CONNECT by PRIOR s.sid = s.blocking_session
  START WITH s.blocking_session is null );

!echo "락 홀더 세션을 죽이고 싶으면 아래의 SQL을 참고하세요"
!echo " alter system kill session '136,61515' immediate; "

---

실습2. 악성 SQL을 사용하는 유져의 SID와 Serial# 를 확인하는 쿼리를 저장하시오

-- 높은 리소스를 사용하는 SQL 세션
WITH resource_intensive AS (
    SELECT 
        sql_id,
        sql_text,
        executions,
        disk_reads,
        buffer_gets,
        cpu_time/1000000 AS cpu_seconds,
        elapsed_time/1000000 AS elapsed_seconds,
        CASE 
            WHEN executions > 0 THEN ROUND(disk_reads/executions, 2)
            ELSE disk_reads
        END AS disk_reads_per_exec,
        CASE 
            WHEN executions > 0 THEN ROUND(buffer_gets/executions, 2) 
            ELSE buffer_gets
        END AS buffer_gets_per_exec
    FROM v$sqlarea
    WHERE (disk_reads > 100000 OR buffer_gets > 1000000 OR cpu_time > 10000000)
)
SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.program,
    s.machine,
    s.status,
    ri.sql_id,
    ri.executions,
    ri.disk_reads,
    ri.buffer_gets,
    ri.cpu_seconds,
    ri.disk_reads_per_exec,
    ri.buffer_gets_per_exec,
    SUBSTR(ri.sql_text, 1, 100) AS sql_text,
    'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE;' AS kill_command
FROM 
    v$session s,
    resource_intensive ri,
    v$sqlarea sa
WHERE 
    s.sql_address = sa.address
    AND s.sql_hash_value = sa.hash_value  
    AND sa.sql_id = ri.sql_id
    AND s.username IS NOT NULL
ORDER BY ri.cpu_seconds DESC, ri.disk_reads DESC;

---

문제. 아래의 악성 SQL을 수행하고 이 악성 SQL을 수행하는 세션을 찾는 위의 쿼리문을 SYS 유져로 편하게 찾을 수 있게 bad.sql로 저장하시오

select count(*)
     from emp, emp, emp, emp, emp, emp, emp, emp, emp, emp, emp, emp;

ㄴ sql developer에서 돌리기

[oracle@ora19c ~]$
[oracle@ora19c ~]$ sys

SQL*Plus: Release 19.0.0.0.0 - Production on 화 8월 19 01:12:40 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


다음에 접속됨:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> @lock

HOLDER          WAITER                 SID    SERIAL# STATUS
--------------- --------------- ---------- ---------- --------
SCOTT 136,24778 lock                   136      24778 INACTIVE
lock            SCOTT 261,56853        261      56853 ACTIVE

락 홀더 세션을 죽이고 싶으면 아래의 SQL을 참고하세요

 alter system kill session '136,61515' immediate;

SQL>
SQL> @bad

       SID    SERIAL# USERNAME
---------- ---------- ----------
SQL_TEXT
--------------------------------------------------------------------------------
KILL_COMMAND
--------------------------------------------------------------------------------
        18      27401 SCOTT
select count(*)  from emp, emp, emp, emp, emp, emp, emp, emp, emp, emp, emp, emp
ALTER SYSTEM KILL SESSION '18,27401' IMMEDIATE;