문서유형ㅣ기술정보
분야ㅣ모니터링/점검
적용제품버전ㅣTibero7.2.3
문서번호ㅣTMOTI041
개요
TPR 테이블에 있는 스냅샷 ID 와 SQL_ID, SQL_HASH_VALUE, PLAN_HASH_VALUE 를 사용하여 스냅샷 전/후로 플랜 및 기타 성능이 변경된 쿼리를 조회하는 방법에 대해 설명하였습니다.
테스트 환경
OS : Linux 64bit (Rocky Linux release 8.10)
Tibero Version : Tibero 7 (DB 7.2.3)
[t723@localhost ~]$ tbboot -version
Tibero 7 (DB 7.2.3) Build 292332
Linux bistro-build-centos_8.4 5.15.0-134-generic #145~20.04.1-Ubuntu SMP Mon Feb 17 13:27:16 UTC 2025 x86_64 x86_64 x86_64 GNU/Linux version (little-endian)
Patch files (FS02PS_312674d FS02PS_317432a FS02PS_323395a FS02PS_321003c FS02PS_323395b FS02PS_340129a FS02PS_336275b FS02PS_338263b FS02PS_251232e FS02PS_339369a FS02PS_340601a FS02PS_340005a FS02PS_340625a FS02PS_340395a FS02PS_337717b FS02PS_339708a FS02PS_193265n FS02PS_340107a FS02PS_291276c FS02PS_340123a FS02PS_339887a FS02PS_338392a FS02PS_340371a FS02PS_329634b FS02PS_340181a FS02PS_321003d FS02PS_328082d FS02PS_340204a FS02PS_248493m FS02PS_340337b FS02PS_340539a FS02PS_326877e FS02PS_339694a FS02PS_339199a FS02PS_339369c FS02PS_339466a FS02PS_335301a FS02PS_340204b FS02PS_340775a FS02PS_335301b FS02PS_340654a FS02PS_333569b FS02PS_340850a FS02PS_338183a FS02PS_340139a FS02PS_326781b FS02PS_339909a FS02PS_339174a FS02PS_340605a FS02PS_335301c FS02PS_340115a)
Compiled with "cc -Wl,--no-as-needed -D_TAC -ggdb -fstack-protector-all -O3 -fno-strict-aliasing -fno-omit-frame-pointer -pipe -D_OPT_COMPILED -D_USE_VIP -Wall -W -Werror-implicit-function-declaration -Wno-unused-parameter -Wpointer-arith -Wdeclaration-after-statement -DHAVE_CONFIG_H -DFD_SETSIZE=8192 -D_WORDSIZE=64 -D_OS_LINUX -D_LINUX_X86_64 -D_X86 -DUSE_ASSERT -DTSAM -DTSAM_NO_ESDS_SEQUENCE -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -D_USE_FSYNC -D_CHECK_LICENSE -D_STBLOG -D_CM_OBSERVER -D_USE_FALLOCATE -D_GNU_SOURCE -D_GNU_SOURCE -D_GNU_SOURCE -D_GNU_SOURCE -DHAVE_EPOLL"
Compiled at Apr 21 2025 06:25:49 build seq 292332 init rev {2023-11-24}
플랜 변경 조회 테스트 수행 과정
1. 쿼리 수행 후 TPR 스냅샷 생성
추후에 실행 계획 비교 시 기준이 되는 정보를 수집하기 위해, 실행 계획의 변경을 유발하는 작업을 수행하기 전에 쿼리의 실행 계획이 TPR 스냅샷 테이블에 저장되도록 합니다.
추후에 실행 계획 비교 시 기준이 되는 정보를 수집하기 위해, 실행 계획의 변경을 유발하는 작업을 수행하기 전에 쿼리의 실행 계획이 TPR 스냅샷 테이블에 저장되도록 합니다.
2. 실행 계획의 변경을 유발하는 작업 수행
인덱스 추가/삭제 등 실행 계획이 확실하게 변경될만한 작업을 수행합니다.
3. 쿼리 수행 후 TPR 스냅샷 생성
실행 계획의 변경이 유발하는 작업을 수행한 후 쿼리의 실행 계확이 TPR 테이블에 저장되도록 합니다.
4. SYS 스키마의 TPR 테이블에서 플랜 변경 쿼리 조회
스냅샷 간의 쿼리/플랜 데이터를 비교하여 플랜이 변경된 쿼리를 식별하여 조회합니다.
방법
1. 플랜 변경 확인 쿼리문 조회
스냅 아이디와 모니터링 대상 쿼리를 식별하기 위한 스냅샷 ID 와 SQL_ID 를 사용하여 플랜이 변경된 쿼리를 조회할 수 있습니다.
<샘플 데이터 생성>
- 샘플 테이블/데이터 생성
drop table tibero.test100 cascade;
create table tibero.test100(col1 varchar(10));
insert into tibero.test100 values ('aaaaa');
commit;
- 테이블 조회
select * from tibero.test100 where col1 = 'aaaaa'; -- TABLE FULL SCAN
- 스냅샷 생성
exec dbms_tpr.CREATE_SNAPSHOT();
- 인덱스 생성(플랜 변경 위함)
create index tibero.i_test100_02 on tibero.test100(col1);
- 테이블 조회
select * from tibero.test100 where col1 = 'aaaaa'; -- INDEX SCAN (i_test100_02)
- 스냅샷 생성
exec dbms_tpr.CREATE_SNAPSHOT();
- sql_id 조회
select sql_id, sql_text from v$sql
where sql_text like '%select * from tibero.test100 where col1 =%aaaaa%';
SQL_ID SQL_TEXT
62vs31agqmstm select * from tibero.test100 where col1 = 'aaaaa'
62vs31agqmstm select * from tibero.test100 where col1 = 'aaaaa'
8h5fg5k0y1b9y select sql_id, sql_text from v$sql where sql_tex
=> sql_id = 62vs31agqmstm
- 스냅샷 ID 조회
select * from sys._tpr_snapshot
order by 2
=> 해당 예제에서는 스냅샷 10, 11 에서 예제 쿼리문 수행
SET ECHO ON
VAR BEFORE_SNAP_ID NUMBER;
VAR AFTER_SNAP_ID NUMBER;
VAR V_SQL_ID VARCHAR(65);
EXEC :BEFORE_SNAP_ID := 10; -- 스냅 아이디 10, 11 간의 플랜 변경건을 조회하도록 함.
EXEC :AFTER_SNAP_ID := 11;
EXEC :V_SQL_ID := '62vs31agqmstm'; -- 쿼리 식별용. 확인하고자 하는 쿼리문의 sql_id 를 작성.
COL (Before)SQL_ID/PLAN_HASH_VALUE FOR A32
COL (After)SQL_ID/PLAN_HASH_VALUE FOR A32
COL HASH_PLAN FOR A32
COL HASH_SQL FOR A32
COL BEFORE_HASH_PLAN FOR A32
COL AFTER_HASH_PLAN FOR A32
COL SQL_TEXT FOR A40
SET LINESIZE 220
SELECT
B.THREAD# B_THREAD, B.SQL_ID || '/' || B.PLAN_HASH_VALUE "(Before)SQL_ID/PLAN_HASH_VALUE"
,A.THREAD# A_THREAD, A.SQL_ID || '/' || A.PLAN_HASH_VALUE "(After)SQL_ID/PLAN_HASH_VALUE"
,CASE WHEN (B.PLAN_HASH_VALUE=A.PLAN_HASH_VALUE) THEN 'NOT CHANGED' ELSE 'CHANGED' END PLAN_CHANGED
,CASE WHEN (A.ELAPS_EXEC-B.ELAPS_EXEC) < 0 THEN 'IMPROVED' ELSE 'NOT IMPROVED' END IMPROVEMENT
,B.ELAPS_EXEC/1000000 "BEFORE_ELAPS_EXEC(S)", A.ELAPS_EXEC/1000000 "AFTER_ELAPS_EXEC(S)"
,(A.ELAPS_EXEC-B.ELAPS_EXEC)/1000000 "ELAPS_GAP(S)"
,SUBSTR(B.SQL_TEXT,1,50) SQL_TEXT
FROM (
SELECT S.SQL_HASH_VALUE as HASH_SQL
,S.PLAN_HASH_VALUE as HASH_PLAN
,S.THREAD# ,S.SQL_HASH_VALUE, S.PLAN_HASH_VALUE, S.PARSE_CALLS, S.FETCHES, S.EXECUTIONS
,S.BUFFER_GETS, S.ELAPSED_TIME, S.SQL_ID
,(CASE WHEN (S.EXECUTIONS > 0 )
THEN (S.ELAPSED_TIME/S.EXECUTIONS)
ELSE 0
END) ELAPS_EXEC
,S.ROWS_PROCESSED
,S.DISK_READ_TIME
,S.DISK_READS
,S.TEMP_SGMT_READ_TIME
,S.TEMP_SGMT_WRITE_TIME
,T.MODULE
,T.SQL_TEXT
FROM SYS._TPR_SQLTEXT T,
SYS._TPR_SQLSTATS S
WHERE 1 = 1
AND S.THREAD# = T.THREAD#
AND S.SQL_HASH_VALUE = T.SQL_HASH_VALUE
AND S.PLAN_HASH_VALUE = T.PLAN_HASH_VALUE
AND S.EXECUTIONS > 0
AND S.PLAN_HASH_VALUE > 0
AND T.SQL_TEXT NOT LIKE 'INSERT /*+ default_stat%'
AND T.SQL_TEXT NOT LIKE 'DELETE /*+ default_stat%'
AND T.SQL_TEXT NOT LIKE 'SELECT /*+ default_stat%'
AND S.SNAP_ID = :BEFORE_SNAP_ID
AND T.MODULE NOT IN ('DATAFILE OPEN','DML STAT FLUSH','JOB SCHD MAIN','JOB_SCHEDULER'
,'SECU LOGGING TO SYS TBL','TPM COLLECTOR','TPM SENDER','TPR SESSION'
,'TRIGGER','TX RECOVERY','UPDATE SESS LTIME','UPDATE USER STATUS')
AND T.SQL_ID = :V_SQL_ID
) B,
(
SELECT S.SQL_HASH_VALUE as HASH_SQL
,S.PLAN_HASH_VALUE as HASH_PLAN
,S.THREAD# ,S.SQL_HASH_VALUE, S.PLAN_HASH_VALUE, S.PARSE_CALLS, S.FETCHES, S.EXECUTIONS
,S.BUFFER_GETS, S.ELAPSED_TIME, S.SQL_ID
,(CASE WHEN (S.EXECUTIONS > 0 )
THEN (S.ELAPSED_TIME/S.EXECUTIONS)
ELSE 0
END) ELAPS_EXEC
,S.ROWS_PROCESSED
,S.DISK_READ_TIME
,S.DISK_READS
,S.TEMP_SGMT_READ_TIME
,S.TEMP_SGMT_WRITE_TIME
,T.MODULE
,T.SQL_TEXT
FROM SYS._TPR_SQLTEXT T,
SYS._TPR_SQLSTATS S
WHERE 1 = 1
AND S.THREAD# = T.THREAD#
AND S.SQL_HASH_VALUE = T.SQL_HASH_VALUE
AND S.PLAN_HASH_VALUE = T.PLAN_HASH_VALUE
AND S.EXECUTIONS > 0
AND S.PLAN_HASH_VALUE > 0
AND T.SQL_TEXT NOT LIKE 'INSERT /*+ default_stat%'
AND T.SQL_TEXT NOT LIKE 'DELETE /*+ default_stat%'
AND T.SQL_TEXT NOT LIKE 'SELECT /*+ default_stat%'
AND S.SNAP_ID = :AFTER_SNAP_ID
AND T.MODULE NOT IN ('DATAFILE OPEN','DML STAT FLUSH','JOB SCHD MAIN','JOB_SCHEDULER'
,'SECU LOGGING TO SYS TBL','TPM COLLECTOR','TPM SENDER','TPR SESSION'
,'TRIGGER','TX RECOVERY','UPDATE SESS LTIME','UPDATE USER STATUS')
AND T.SQL_ID = :V_SQL_ID
) A
WHERE B.HASH_SQL = A.HASH_SQL
ORDER BY "ELAPS_GAP(S)" DESC
/
2. 수행 결과 및 항목 설명
B_THREAD (Before)SQL_ID/PLAN_HASH_VALUE A_THREAD (After)SQL_ID/PLAN_HASH_VALUE PLAN_CHANGED IMPROVEMENT BEFORE_ELAPS_EXEC(S) AFTER_ELAPS_EXEC(S) ELAPS_GAP(S) SQL_TEXT
---------- -------------------------------- ---------- -------------------------------- ------------ ------------ -------------------- ------------------- ------------ ----------------------------------------
0 62vs31agqmstm/717645934 0 62vs31agqmstm/417980975 CHANGED IMPROVED .007158 .002135 -.005023 select * from tibero.test100 where col1
= 'aaaaa'
설명 : 두개의 스냅샷 (BEFORE SNAPSHOT, AFTER SNAPSHOT) 을 비교하여, 플랜 변경 여부 및 수행시간과 플랜 변경 쿼리를 식별할 수 있는 쿼리의 SQL_ID, 플랜의 해시값, 쿼리 문장 등을 출력합니다.
B_THREAD : BEFORE SNAPSHOT 의 THREAD#
A_THREAD : AFTER SNAPSHOT 의 THREAD#
(Before)SQL_ID/PLAN_HASH_VALUE : BEFORE SNAPSHOT 의 SQL_ID, PLAN_HASH_VALUE
(After)SQL_ID/PLAN_HASH_VALUE : AFTER SNAPSHOT 의 SQL_ID, PLAN_HASH_VALUE
PLAN_CHANGED : 플랜의 변경 여부를 출력함
IMPROVEMENT : 평균 수행시간에 대한 시간 단축으로, 향상(IMPROVED) 혹은 향상되지 않음(NOT IMPROVED) 여부를 출력함.
BEFORE_ELAPS_EXEC : BEFORE 쿼리문 소요시간
AFTER_ELAPS_EXEC : AFTER 쿼리문 소요시간
ELAPS_GAP(S) : AFTER SNAPSHOT 수행시간에서 BEFORE SNAPSHOT 수행시간을 뺀 값을 출력함(초단위)
SQL_TEXT : 수행 쿼리문