Oracle의 DBMS_SQL 또는 V$SQL 뷰를 사용해 런타임에 실제 실행된 SQL을 추적하는 방법

 

오라클의 DBMS_SQL 패키지와 V$SQL 뷰는 런타임에 실행되는 SQL을 추적하거나 실행된 SQL의 세부 정보를 제공하는 데 유용합니다. 각 방법의 작동 방식과 사용 사례를 자세히 설명하겠습니다.

---

1. DBMS_SQL 패키지

기능

DBMS_SQL 패키지는 동적 SQL을 처리하기 위해 제공되는 PL/SQL 패키지입니다. 특히, 동적 SQL 실행 시 실행된 SQL 문장을 캡처하고 추적하는 데 유용합니다.

작업 과정

1. SQL 구문 열기: DBMS_SQL.OPEN_CURSOR를 호출해 커서를 열고, 동적 SQL 문장을 준비합니다.
2. SQL 실행 추적: SQL 실행 전후로 SQL 문장을 로그하거나 저장합니다.
3. 커서 닫기: 실행이 완료되면 DBMS_SQL.CLOSE_CURSOR로 커서를 닫습니다.

사용 예제

DECLARE
    v_cursor  INTEGER;
    v_sql     VARCHAR2(1000);
    v_result  NUMBER;
BEGIN
    -- 동적 SQL 정의
    v_sql := 'SELECT COUNT(*) FROM employees WHERE department_id = :dept_id';

    -- 커서 열기
    v_cursor := DBMS_SQL.OPEN_CURSOR;

    -- SQL 문 준비
    DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);

    -- 바인드 변수 설정
    DBMS_SQL.BIND_VARIABLE(v_cursor, ':dept_id', 10);

    -- SQL 실행
    v_result := DBMS_SQL.EXECUTE_AND_FETCH(v_cursor);

    -- 실행된 SQL 로그
    DBMS_OUTPUT.PUT_LINE('Executed SQL: ' || v_sql);

    -- 결과 출력
    DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);

    -- 커서 닫기
    DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
    WHEN OTHERS THEN
        -- 예외 처리 및 커서 닫기
        IF DBMS_SQL.IS_OPEN(v_cursor) THEN
            DBMS_SQL.CLOSE_CURSOR(v_cursor);
        END IF;
        RAISE;
END;
/

장점

• 런타임에서 동적으로 실행되는 SQL을 제어하고 추적 가능.
• 커서와 바인드 변수 등 동적 SQL의 모든 구성 요소를 캡처 가능.

한계

• 정적 SQL은 추적할 수 없음.
• 수동으로 관리해야 하므로 구현의 복잡도가 높아질 수 있음.

---

728x90

2. V$SQL 뷰

기능

V$SQL 뷰는 공유 SQL 영역에 저장된 모든 SQL 문장을 보여줍니다. 현재 데이터베이스 인스턴스에서 실행된 SQL을 추적하는 데 사용됩니다.

사용 방법

V$SQL 뷰를 조회하면 다음 정보를 얻을 수 있습니다:

• SQL_TEXT: 실행된 SQL 문장.
• PARSE_CALLS: SQL이 파싱된 횟수.
• EXECUTIONS: SQL이 실행된 횟수.
• DISK_READS, BUFFER_GETS: SQL 실행 시 사용된 리소스.
• FIRST_LOAD_TIME: SQL이 처음 로드된 시간.

기본 조회 예제

SELECT SQL_ID, SQL_TEXT, PARSE_CALLS, EXECUTIONS, DISK_READS, BUFFER_GETS, FIRST_LOAD_TIME
FROM V$SQL
WHERE SQL_TEXT LIKE '%employees%'
ORDER BY LAST_ACTIVE_TIME DESC;

필터 조건

• 특정 사용자만 추적:

  SELECT SQL_TEXT, EXECUTIONS
  FROM V$SQL
  WHERE PARSING_USER_ID = (SELECT USER_ID FROM DBA_USERS WHERE USERNAME = 'HR');
  

• 최신 실행된 SQL만 확인:

  SELECT SQL_TEXT, LAST_ACTIVE_TIME
  FROM V$SQL
  WHERE LAST_ACTIVE_TIME > SYSDATE - INTERVAL '1' HOUR;
  

실제 동작 중인 SQL 확인

V$SQL_MONITOR 뷰를 사용하면 현재 실행 중인 SQL의 성능 통계를 실시간으로 확인할 수 있습니다:

SELECT SQL_TEXT, STATUS, ELAPSED_TIME, CPU_TIME, BUFFER_GETS
FROM V$SQL_MONITOR
WHERE STATUS = 'EXECUTING';

---

3. 실시간 추적 및 감사

런타임에서 실제 실행된 SQL을 실시간으로 추적하거나 감사(Auditing)하려면 오라클의 Fine-Grained Auditing (FGA) 기능을 활용할 수 있습니다.

FGA 설정 예제

BEGIN
    DBMS_FGA.ADD_POLICY(
        object_schema   => 'HR',
        object_name     => 'EMPLOYEES',
        policy_name     => 'EMPLOYEE_SELECT_AUDIT',
        audit_condition => NULL,  -- 모든 조건을 감사
        audit_column    => NULL,  -- 모든 열을 감사
        handler_schema  => NULL,
        handler_module  => NULL,
        enable          => TRUE
    );
END;
/

감사 결과 확인

DBA_FGA_AUDIT_TRAIL 뷰에서 감사 결과를 확인할 수 있습니다:

SELECT DB_USER, OS_USER, OBJECT_NAME, SQL_TEXT
FROM DBA_FGA_AUDIT_TRAIL;

---

728x90

4. 활용 사례 비교

방법	적용 대상	특징	장점	한계
DBMS_SQL	동적 SQL	런타임에 직접 SQL 실행과 추적 가능	커스터마이징 가능, SQL 세부 추적 가능	구현 복잡성 높음, 정적 SQL은 처리 불가
V$SQL	모든 실행된 SQL	공유 SQL 영역에서 SQL 문장과 리소스 사용량 추적	시스템 전체 SQL 파악, 리소스 소비 정보 제공	동적 SQL에 대한 세부 추적 어려움
V$SQL_MONITOR	실행 중인 SQL	현재 실행 중인 SQL의 성능 정보 제공	실시간 SQL 모니터링 가능	정적 SQL 외에는 제한적, 오래된 SQL은 확인 불가
Fine-Grained Auditing	특정 테이블 및 조건에 따라	테이블, 조건, 컬럼별로 세분화된 SQL 감사를 수행	감사 로그 자동 생성	추가 설정 필요, 모든 SQL을 자동으로 추적할 수 없음

---

결론

1. 동적 SQL이 많다면: DBMS_SQL을 사용하여 런타임 SQL을 명시적으로 추적.
2. 시스템 전반에서 실행된 SQL 추적: V$SQL 또는 V$SQL_MONITOR 뷰 활용.
3. 정교한 감사: Fine-Grained Auditing으로 특정 테이블/조건 감사를 설정.

사용 목적에 따라 적절한 방법을 조합하여 SQL 추적 및 분석 작업을 진행하면 됩니다. 😊