Awr을 이용한 sql 튜닝 대상 찾기

Oracle 19c에서 성능이 좋지 않은 SQL을 AWR에서 추출하고, 그 이력을 관리하는 테이블에 저장한 후 SQL로 조회할 수 있는 간단한 프로그램을 아래와 같이 구성해드릴게요.

⸻

✅ 1. 개요 • 대상: AWR에서 수집된 SQL 중 Elapsed Time 또는 Buffer Gets가 높은 SQL • 저장처: 사용자 정의 이력 테이블 • 주기: 수동 실행 혹은 스케줄러로 주기적 실행 가능 • 기능: • AWR에서 TOP SQL 추출 • 사용자 테이블에 INSERT (중복 방지) • 조회용 SELECT 제공

⸻

✅ 2. AWR에서 TOP SQL 추출 쿼리 (Elapsed Time 기준)

SELECT 
    s.snap_id,
    TO_CHAR(s.begin_interval_time, 'YYYY-MM-DD HH24:MI') AS begin_time,
    TO_CHAR(s.end_interval_time, 'YYYY-MM-DD HH24:MI') AS end_time,
    sq.sql_id,
    sq.plan_hash_value,
    sq.module,
    sq.parsing_schema_name,
    sq.executions_delta,
    sq.elapsed_time_delta,
    ROUND(sq.elapsed_time_delta / GREATEST(sq.executions_delta, 1)) AS avg_elapsed,
    sq.buffer_gets_delta,
    sq.disk_reads_delta,
    sq.rows_processed_delta
FROM
    dba_hist_snapshot s
JOIN
    dba_hist_sqlstat sq ON sq.snap_id = s.snap_id AND sq.dbid = s.dbid AND sq.instance_number = s.instance_number
WHERE
    s.begin_interval_time >= SYSDATE - 1
ORDER BY
    sq.elapsed_time_delta DESC
FETCH FIRST 50 ROWS ONLY;

⸻

✅ 3. 저장 테이블 생성

CREATE TABLE perf_sql_history (
    id                 NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    snap_id            NUMBER,
    begin_time         DATE,
    end_time           DATE,
    sql_id             VARCHAR2(20),
    plan_hash_value    NUMBER,
    module             VARCHAR2(64),
    parsing_schema     VARCHAR2(30),
    executions         NUMBER,
    elapsed_time       NUMBER,
    avg_elapsed        NUMBER,
    buffer_gets        NUMBER,
    disk_reads         NUMBER,
    rows_processed     NUMBER,
    inserted_at        DATE DEFAULT SYSDATE
);

⸻

✅ 4. 중복 방지 및 INSERT 프로시저

CREATE OR REPLACE PROCEDURE collect_top_sql_from_awr IS
BEGIN
    INSERT INTO perf_sql_history (
        snap_id, begin_time, end_time, sql_id, plan_hash_value,
        module, parsing_schema, executions, elapsed_time, avg_elapsed,
        buffer_gets, disk_reads, rows_processed
    )
    SELECT 
        s.snap_id,
        s.begin_interval_time,
        s.end_interval_time,
        sq.sql_id,
        sq.plan_hash_value,
        sq.module,
        sq.parsing_schema_name,
        sq.executions_delta,
        sq.elapsed_time_delta,
        ROUND(sq.elapsed_time_delta / GREATEST(sq.executions_delta, 1)),
        sq.buffer_gets_delta,
        sq.disk_reads_delta,
        sq.rows_processed_delta
    FROM
        dba_hist_snapshot s
    JOIN
        dba_hist_sqlstat sq ON sq.snap_id = s.snap_id AND sq.dbid = s.dbid AND sq.instance_number = s.instance_number
    WHERE
        s.begin_interval_time >= SYSDATE - 1
        AND NOT EXISTS (
            SELECT 1 FROM perf_sql_history h
            WHERE h.snap_id = s.snap_id AND h.sql_id = sq.sql_id
        )
    ORDER BY
        sq.elapsed_time_delta DESC
    FETCH FIRST 50 ROWS ONLY;
    
    COMMIT;
END;
/

⸻

✅ 5. 조회 SQL 예제

최근 일주일간 가장 느렸던 SQL 조회:

SELECT *
FROM perf_sql_history
WHERE inserted_at >= SYSDATE - 7
ORDER BY elapsed_time DESC;

⸻

✅ 6. 자동 실행 (옵션: DBMS_SCHEDULER)

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'JOB_COLLECT_TOP_SQL',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN collect_top_sql_from_awr; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=HOURLY;INTERVAL=2',  -- 2시간마다 실행
    enabled         => TRUE,
    comments        => 'Collect top SQL from AWR every 2 hours');
END;
/

⸻

✅ 7. 확장 아이디어 • SQL Text까지 저장하고 싶다면 dba_hist_sqltext 조인 • PLAN 상세 추적: dba_hist_sql_plan 이용 • 특정 모듈/사용자 필터링

---

좋습니다! 요청하신 다음 3가지 기능을 추가하여 AWR 성능저하 SQL 추적 시스템을 확장하겠습니다:

⸻

✅ 1. dba_hist_sqltext 조인하여 SQL Text 저장

✅ 2. dba_hist_sql_plan 이용하여 PLAN 상세 추적

✅ 3. Bind 변수 값 저장 (via v$sql_bind_capture)

⸻

✅ 0. 저장 테이블 구조 확장

CREATE TABLE perf_sql_history (
    id                 NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    snap_id            NUMBER,
    begin_time         DATE,
    end_time           DATE,
    sql_id             VARCHAR2(20),
    plan_hash_value    NUMBER,
    module             VARCHAR2(64),
    parsing_schema     VARCHAR2(30),
    executions         NUMBER,
    elapsed_time       NUMBER,
    avg_elapsed        NUMBER,
    buffer_gets        NUMBER,
    disk_reads         NUMBER,
    rows_processed     NUMBER,
    sql_text           CLOB,
    inserted_at        DATE DEFAULT SYSDATE
);

✅ sql_text 컬럼을 CLOB으로 지정 🛠️ 테이블 생성 후 CLOB 저장을 위해 LOB 저장 방식 (SecureFiles) 설정 확인 필요할 수 있음

⸻

✅ 1. SQL Text 저장 (from dba_hist_sqltext)

AWR에서는 dba_hist_sqltext 에 SQL Text가 저장되어 있으며, sql_id 기준으로 조인합니다.

▶️ 프로시저 수정

CREATE OR REPLACE PROCEDURE collect_top_sql_from_awr IS
BEGIN
    INSERT INTO perf_sql_history (
        snap_id, begin_time, end_time, sql_id, plan_hash_value,
        module, parsing_schema, executions, elapsed_time, avg_elapsed,
        buffer_gets, disk_reads, rows_processed, sql_text
    )
    SELECT 
        s.snap_id,
        s.begin_interval_time,
        s.end_interval_time,
        sq.sql_id,
        sq.plan_hash_value,
        sq.module,
        sq.parsing_schema_name,
        sq.executions_delta,
        sq.elapsed_time_delta,
        ROUND(sq.elapsed_time_delta / GREATEST(sq.executions_delta, 1)),
        sq.buffer_gets_delta,
        sq.disk_reads_delta,
        sq.rows_processed_delta,
        st.sql_text
    FROM
        dba_hist_snapshot s
    JOIN
        dba_hist_sqlstat sq ON sq.snap_id = s.snap_id AND sq.dbid = s.dbid AND sq.instance_number = s.instance_number
    JOIN
        dba_hist_sqltext st ON st.sql_id = sq.sql_id
    WHERE
        s.begin_interval_time >= SYSDATE - 1
        AND NOT EXISTS (
            SELECT 1 FROM perf_sql_history h
            WHERE h.snap_id = s.snap_id AND h.sql_id = sq.sql_id
        )
    ORDER BY
        sq.elapsed_time_delta DESC
    FETCH FIRST 50 ROWS ONLY;

    COMMIT;
END;
/

⸻

✅ 2. PLAN 상세 추적 저장용 테이블

▶️ PLAN 저장 테이블

CREATE TABLE perf_sql_plan (
    id              NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    history_id      NUMBER, -- perf_sql_history.id 참조
    operation_id    NUMBER,
    operation       VARCHAR2(50),
    options         VARCHAR2(50),
    object_owner    VARCHAR2(30),
    object_name     VARCHAR2(128),
    object_type     VARCHAR2(30),
    access_predicates  VARCHAR2(4000),
    filter_predicates  VARCHAR2(4000),
    cardinality     NUMBER,
    cost            NUMBER,
    depth           NUMBER
);

▶️ PLAN 저장 프로시저 (간단 예시)

CREATE OR REPLACE PROCEDURE collect_sql_plan(p_sql_id VARCHAR2, p_plan_hash NUMBER, p_history_id NUMBER) IS
BEGIN
    INSERT INTO perf_sql_plan (
        history_id, operation_id, operation, options,
        object_owner, object_name, object_type,
        access_predicates, filter_predicates, cardinality, cost, depth
    )
    SELECT
        p_history_id,
        id,
        operation,
        options,
        object_owner,
        object_name,
        object_type,
        access_predicates,
        filter_predicates,
        cardinality,
        cost,
        depth
    FROM
        dba_hist_sql_plan
    WHERE
        sql_id = p_sql_id
        AND plan_hash_value = p_plan_hash;

    COMMIT;
END;
/

▶️ 메인 프로시저에서 호출

perf_sql_history 저장 후 해당 id를 이용해 collect_sql_plan 호출하는 방식으로 확장 가능합니다. (PL/SQL 내에서 RETURNING id INTO 구문 활용)

⸻

✅ 3. Bind 변수 저장

AWR은 bind 값을 저장하지 않지만, GV$SQL_BIND_CAPTURE 를 통해 현재 캐시된 바인드 값을 조회할 수 있습니다.

▶️ 바인드 값 테이블

CREATE TABLE perf_sql_bind (
    id            NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sql_id        VARCHAR2(20),
    name          VARCHAR2(30),
    position      NUMBER,
    datatype      VARCHAR2(30),
    value_string  VARCHAR2(4000),
    captured_at   DATE DEFAULT SYSDATE
);

▶️ 바인드 저장 프로시저

CREATE OR REPLACE PROCEDURE collect_bind_values(p_sql_id VARCHAR2) IS
BEGIN
    INSERT INTO perf_sql_bind (sql_id, name, position, datatype, value_string)
    SELECT
        sql_id,
        name,
        position,
        datatype_string,
        value_string
    FROM
        gv$sql_bind_capture
    WHERE
        sql_id = p_sql_id;

    COMMIT;
END;
/

⚠️ 해당 값은 현재 SGA에 남아있는 바인드 값에 한정됩니다. AWR에는 바인드 값이 저장되지 않기 때문에 이 부분은 실시간 수집 시 병행 해야 합니다.

⸻

✅ 향후 확장 아이디어

기능 설명 SQL Text 길이 초과 방지 4000자 이상인 SQL은 별도 처리 필요 자동 수집 스케줄링 DBMS_SCHEDULER 주기적 실행 가능 대시보드 APEX, Python Flask 등으로 Web View 구성 가능 SQL Text 중 중복 제거 Hash나 SQL_ID 기반 중복 방지