Document Type | Technical Information
Category | Monitoring/Inspection
Applicable Product Version | Tibero 7.2.3
Document Number | TMOTI041
Overview
This document explains how to query for queries whose plans and other performance metrics have changed before and after snapshots using the snapshot ID, SQL_ID, SQL_HASH_VALUE, and PLAN_HASH_VALUE in the TPR table.
Test Environment
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}
Plan Change Query Test Procedure
1. Execute the query and create a TPR snapshot
To collect baseline information for later execution plan comparison, ensure the query's execution plan is stored in the TPR snapshot table before performing any work that causes execution plan changes.
To collect baseline information for later execution plan comparison, ensure the query's execution plan is stored in the TPR snapshot table before performing any work that causes execution plan changes.
2. Perform operations that cause execution plan changes
Perform operations such as adding or dropping indexes that definitely cause execution plan changes.
3. Execute the query and create a TPR snapshot
After performing the operations that cause execution plan changes, ensure the query's execution plan is stored in the TPR table.
4. Query for plan-changed queries in the SYS schema's TPR table
Compare query/plan data between snapshots to identify and query for queries with changed plans.
Method
1. Query to Check Plan Changes
You can query for queries whose plans have changed using the snapshot IDs and SQL_IDs that identify the snapshots and monitored queries.
<Sample Data Creation>
- Create sample table/data
drop table tibero.test100 cascade;
create table tibero.test100(col1 varchar(10));
insert into tibero.test100 values ('aaaaa');
commit;
- Query the table
select * from tibero.test100 where col1 = 'aaaaa'; -- TABLE FULL SCAN
- Create snapshot
exec dbms_tpr.CREATE_SNAPSHOT();
- Create index (to cause plan change)
create index tibero.i_test100_02 on tibero.test100(col1);
- Query the table
select * from tibero.test100 where col1 = 'aaaaa'; -- INDEX SCAN (i_test100_02)
- Create snapshot
exec dbms_tpr.CREATE_SNAPSHOT();
- Query 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
- Query snapshot IDs
select * from sys._tpr_snapshot
order by 2
=> In this example, the example query was executed in snapshots 10 and 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; -- Query plan changes between snapshot 10 and 11
EXEC :AFTER_SNAP_ID := 11;
EXEC :V_SQL_ID := '62vs31agqmstm'; -- Identifier for query. Enter the sql_id of the query to check.
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. Execution Results and Explanation of Columns
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'
Explanation: By comparing two snapshots (BEFORE SNAPSHOT, AFTER SNAPSHOT) this query outputs whether the plan changed, execution time, and the SQL_ID identifying the plan-changed query, plan hash values, and the query text.
B_THREAD: THREAD# of BEFORE SNAPSHOT
A_THREAD: THREAD# of AFTER SNAPSHOT
(Before)SQL_ID/PLAN_HASH_VALUE: SQL_ID and PLAN_HASH_VALUE of BEFORE SNAPSHOT
(After)SQL_ID/PLAN_HASH_VALUE: SQL_ID and PLAN_HASH_VALUE of AFTER SNAPSHOT
PLAN_CHANGED: Indicates whether the plan has changed
IMPROVEMENT: Indicates whether the average execution time improved (IMPROVED) or did not improve (NOT IMPROVED)
BEFORE_ELAPS_EXEC: Execution time before the change
AFTER_ELAPS_EXEC: Execution time after the change
ELAPS_GAP(S): Difference between AFTER and BEFORE execution times in seconds
SQL_TEXT: Executed query text