2025년 6월 16일 (월) 13:31에 Oracle님의 편집

2025-06-16T13:31:47Z

← 이전 판		2025년 6월 16일 (월) 22:31 판
15번째 줄:		15번째 줄:

	✅ 2. AWR에서 TOP SQL 추출 쿼리 (Elapsed Time 기준)		✅ 2. AWR에서 TOP SQL 추출 쿼리 (Elapsed Time 기준)
			<source lang=sql>
	SELECT		SELECT
	s.snap_id,		s.snap_id,
40번째 줄:		40번째 줄:
	FETCH FIRST 50 ROWS ONLY;		FETCH FIRST 50 ROWS ONLY;

			</source>


45번째 줄:		46번째 줄:

	✅ 3. 저장 테이블 생성		✅ 3. 저장 테이블 생성
			<source lang=sql>
	CREATE TABLE perf_sql_history (		CREATE TABLE perf_sql_history (
	id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,		id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
63번째 줄:		64번째 줄:
	inserted_at DATE DEFAULT SYSDATE		inserted_at DATE DEFAULT SYSDATE
	);		);
			</source>


69번째 줄:		71번째 줄:

	✅ 4. 중복 방지 및 INSERT 프로시저		✅ 4. 중복 방지 및 INSERT 프로시저
			<source lang=sql>
	CREATE OR REPLACE PROCEDURE collect_top_sql_from_awr IS		CREATE OR REPLACE PROCEDURE collect_top_sql_from_awr IS
	BEGIN		BEGIN
110번째 줄:		112번째 줄:


			</source>

	⸻		⸻
116번째 줄:		119번째 줄:

	최근 일주일간 가장 느렸던 SQL 조회:		최근 일주일간 가장 느렸던 SQL 조회:
			<source lang=sql>

	SELECT *		SELECT *
122번째 줄:		126번째 줄:
	ORDER BY elapsed_time DESC;		ORDER BY elapsed_time DESC;

			</source>

	⸻		⸻

	✅ 6. 자동 실행 (옵션: DBMS_SCHEDULER)		✅ 6. 자동 실행 (옵션: DBMS_SCHEDULER)
			<source lang=sql>

	BEGIN		BEGIN
140번째 줄:		145번째 줄:
	/		/

			</source>


163번째 줄:		169번째 줄:

	✅ 0. 저장 테이블 구조 확장		✅ 0. 저장 테이블 구조 확장
			<source lang=sql>

	CREATE TABLE perf_sql_history (		CREATE TABLE perf_sql_history (
182번째 줄:		189번째 줄:
	inserted_at DATE DEFAULT SYSDATE		inserted_at DATE DEFAULT SYSDATE
	);		);
			</source>

	✅ sql_text 컬럼을 CLOB으로 지정		✅ sql_text 컬럼을 CLOB으로 지정
193번째 줄:		201번째 줄:

	▶️ 프로시저 수정		▶️ 프로시저 수정
			<source lang=sql>

	CREATE OR REPLACE PROCEDURE collect_top_sql_from_awr IS		CREATE OR REPLACE PROCEDURE collect_top_sql_from_awr IS
236번째 줄:		245번째 줄:
	/		/

			</source>


243번째 줄:		253번째 줄:

	▶️ PLAN 저장 테이블		▶️ PLAN 저장 테이블
			<source lang=sql>
	CREATE TABLE perf_sql_plan (		CREATE TABLE perf_sql_plan (
	id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,		id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
259번째 줄:		269번째 줄:
	depth NUMBER		depth NUMBER
	);		);
			</source>

	▶️ PLAN 저장 프로시저 (간단 예시)		▶️ PLAN 저장 프로시저 (간단 예시)
			<source lang=sql>
	CREATE OR REPLACE PROCEDURE collect_sql_plan(p_sql_id VARCHAR2, p_plan_hash NUMBER, p_history_id NUMBER) IS		CREATE OR REPLACE PROCEDURE collect_sql_plan(p_sql_id VARCHAR2, p_plan_hash NUMBER, p_history_id NUMBER) IS
	BEGIN		BEGIN
291번째 줄:		302번째 줄:
	END;		END;
	/		/
			</source>

	▶️ 메인 프로시저에서 호출		▶️ 메인 프로시저에서 호출
303번째 줄:		315번째 줄:

	▶️ 바인드 값 테이블		▶️ 바인드 값 테이블
			<source lang=sql>
	CREATE TABLE perf_sql_bind (		CREATE TABLE perf_sql_bind (
	id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,		id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
313번째 줄:		325번째 줄:
	captured_at DATE DEFAULT SYSDATE		captured_at DATE DEFAULT SYSDATE
	);		);
			</source>

	▶️ 바인드 저장 프로시저		▶️ 바인드 저장 프로시저
			<source lang=sql>

	CREATE OR REPLACE PROCEDURE collect_bind_values(p_sql_id VARCHAR2) IS		CREATE OR REPLACE PROCEDURE collect_bind_values(p_sql_id VARCHAR2) IS
333번째 줄:		347번째 줄:
	END;		END;
	/		/
			</source>

	⚠️ 해당 값은 현재 SGA에 남아있는 바인드 값에 한정됩니다.		⚠️ 해당 값은 현재 SGA에 남아있는 바인드 값에 한정됩니다.

Oracle: 새 문서: Oracle 19c에서 성능이 좋지 않은 SQL을 AWR에서 추출하고, 그 이력을 관리하는 테이블에 저장한 후 SQL로 조회할 수 있는 간단한 프로그램을 아래와 같이 구성해드릴게요. ⸻ ✅ 1. 개요 • 대상: AWR에서 수집된 SQL 중 Elapsed Time 또는 Buffer Gets가 높은 SQL • 저장처: 사용자 정의 이력 테이블 • 주기: 수동 실행 혹은 스케줄러로 주기적 실행 가능 • 기능: • AWR에서 TOP...

2025-06-16T13:29:14Z

새 문서: Oracle 19c에서 성능이 좋지 않은 SQL을 AWR에서 추출하고, 그 이력을 관리하는 테이블에 저장한 후 SQL로 조회할 수 있는 간단한 프로그램을 아래와 같이 구성해드릴게요. ⸻ ✅ 1. 개요 • 대상: AWR에서 수집된 SQL 중 Elapsed Time 또는 Buffer Gets가 높은 SQL • 저장처: 사용자 정의 이력 테이블 • 주기: 수동 실행 혹은 스케줄러로 주기적 실행 가능 • 기능: • AWR에서 TOP...

새 문서

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

[[분류:awr]]
[[분류:튜닝]]

Awr을 이용한 sql 튜닝 대상 찾기 - 편집 역사

2025년 6월 16일 (월) 13:31에 Oracle님의 편집