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 기반 중복 방지