Document Type | Technical Information
Category | Administration
Applicable Product Version | Tibero 7.2.2
Document Number | TADTI197
Overview
This document contains information for users new to Tibero on how to analyze basic database performance using the features provided by Tibero after installation. For detailed information on features, please refer to the manual.
Method
TPR Report
TPR (Tibero Performance Repository) is a feature that periodically and automatically collects statistical information and provides a built-in analysis report function for the collected statistics to assist in system load analysis. Periodically, data is stored in a specific table at intervals (default 1 hour) and this stored information is called a Snapshot. For various parameters and options, please refer to the manual.
| Step | Command |
| Select Snapshot | SQL> alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss'; SQL> select * from _tpr_snapshot order by BEGIN_INTERVAL_TIME; |
| Create Snapshot | SQL> exec dbms_tpr.create_snapshot; SQL> exec dbms_tpr.create_snapshot_all; |
| Report Snapshot | SQL> exec dbms_tpr.report_text_id(<SNAP_ID>); SQL> exec dbms_tpr.report_text_id(<BEGIN_SNAP_ID>,<END_SNAP_ID>); SQL> exec dbms_tpr.report_text_id(<BEGIN_SNAP_ID>,<END_SNAP_ID>,<INSTANCE_NUMBER>); |
| Snapshot Location | $TB_HOME/instance/$TB_SID/tpr_report.{mthr_pid}.{current_time} |
Example
set lines 200
col BEGIN_INTERVAL_TIME for a20
col END_INTERVAL_TIME for a20
alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss';
select * from _tpr_snapshot order by BEGIN_INTERVAL_TIME;
BEGIN_INTERVAL_TIME SNAP_ID THREAD# INSTANCE_NUMBER END_INTERVAL_TIME SNAP_GID MARKED_FOR_REPORT
-------------------- ---------- ---------- --------------- -------------------- ---------- -----------------
2025/10/20 16:22:36 201 0 0 2025/10/20 16:23:04 1 N
2025/10/20 16:22:38 221 1 1 2025/10/20 16:23:04 1 N
2025/10/20 16:23:04 202 0 0 2025/10/20 16:23:14 N
SQL> conn sys/tibero
Connected to Tibero.
SQL> exec dbms_tpr.report_text_id(201);
PSM completed.
SQL> exec dbms_tpr.report_text_id(201,202,0);
PSM completed.
SQL> exec dbms_tpr.report_text_id(201,221);
PSM completed.
tibero@edu1[tac1]:/home/tibero> ls -tlr $TB_HOME/instance/$TB_SID/tpr*
-rw-r--r--. 1 tibero dba 186022 Oct 20 16:24 tpr_report.tac.20251020_162450.txt
-rw-r--r--. 1 tibero dba 203410 Oct 20 16:25 tpr_report.tac.20251020_162530.txt
-rw-r--r--. 1 tibero dba 202323 Oct 20 16:25 tpr_report.tac.20251020_162542.txt
SQL Plan
| Command | Description |
| SQL> help set | AUTOT[RACE] {OFF|ON|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] [PLANS[TAT]] |
Example
SQL> set autot on
SQL> select * from TBL_PB_SYNC where c1<2;
C1
----------
C2
--------------------------------------------------------------------------------
C3
------------------------------
C4
--------------------------------------------------------------------------------
C5
-----------------------------------------------------------------
C6 C7 C8
------------------------------- --------------- --------------------------------
C9
------------------------------------------------------------
C10
----------------------------------------
C11
--------------------------------------------------------------------------------
C12
--------------------------------------------------------------------------------
CREATE_DATE
-----------------------------------------------------------------
UPDATE_DATE
-----------------------------------------------------------------
1
2065/11/10
2018/05/06
2025/09/10
2025/09/10 10:35:18.000000
+00 00:30:00.000000 +01-02 00000A4760FE70C07EFB73130677A5B3
test
test
2000
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
2025/09/10 10:35:18.858860
2025/09/10 10:35:18.858860
1 row selected.
SQL ID: 65gsxbh3c639a
Child number: 1682
Plan hash value: 4096446177
Execution Plan
--------------------------------------------------------------------------------
1 PARTITION RANGE (ALL PART) (Cost:31, %%CPU:0, Rows:1) (PS:1, PE:8)
2 TABLE ACCESS (ROWID): TBL_PB_SYNC (Cost:31, %%CPU:0, Rows:1)
3 PARTITION RANGE (ALL PART) (Cost:30, %%CPU:0, Rows:1) (PS:0, PE:0)
4 INDEX (RANGE SCAN): TBL_PB_SYNC_I01_UK (Cost:30, %%CPU:0, Rows:1)
Predicate Information
--------------------------------------------------------------------------------
4 - access: ("TBL_PB_SYNC"."C1" < 2) (0.000)
NAME VALUE
------------------------------ ----------
db block gets 0
consistent gets 103
physical reads 80
redo size 0
sorts (disk) 0
sorts (memory) 3
rows processed 1
tbprof
When the sqltrace parameter is enabled and SQL is executed, a file is generated at the SQL_TRACE_DEST location. The generated file can be analyzed using the tbprof utility to create a Report file. Performing this at the alter system level will cause all queries occurring in the DB to be written to the sqltrace file, so it is recommended to perform it at the session level.
| Step | Command |
| Command | SQL> alter session set sql_trace=y; SQL> < sql execute > SQL> alter session set sql_trace=n; |
| Location | SQL> show parame SQL_TRACE_DEST
NAME TYPE VALUE ------------- -------- ------------------------------------------------ SQL_TRACE_DES DIRNAME /home/tibero/tibero7/instance/tac1/log/sqltrace/ |
| tbprof | $ tbprof < Generated SQLTRACE File > < File name for creating > |
Example
SQL> alter session set sql_trace=y;
Session altered.
SQL> select * from TBL_PB_SYNC where c1<2;
C1
----------
C2
--------------------------------------------------------------------------------
C3
------------------------------
C4
--------------------------------------------------------------------------------
C5
-----------------------------------------------------------------
C6 C7 C8
------------------------------- --------------- --------------------------------
C9
------------------------------------------------------------
C10
----------------------------------------
C11
--------------------------------------------------------------------------------
C12
--------------------------------------------------------------------------------
CREATE_DATE
-----------------------------------------------------------------
UPDATE_DATE
-----------------------------------------------------------------
1
2065/11/10
2018/05/06
2025/09/10
2025/09/10 10:35:18.000000
+00 00:30:00.000000 +01-02 00000A4760FE70C07EFB73130677A5B3
test
test
2000
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
2025/09/10 10:35:18.858860
2025/09/10 10:35:18.858860
SQL> alter session set sql_trace=n;
Session altered.
tibero@edu1[tac1]:/home/tibero/tibero7/instance/tac1/log/sqltrace> ls -tlr
total 4
-rw-r--r--. 1 tibero dba 2491 Oct 20 16:30 tb_sqltrc_364363_175_4447.trc
tibero@edu1[tac1]:/home/tibero/tibero7/instance/tac1/log/sqltrace> tbprof tb_sqltrc_364363_175_4447.trc a.out
TBPROF 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
tibero@edu1[tac1]:/home/tibero/tibero7/instance/tac1/log/sqltrace> cat a.out
TBPROF 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
input file name : tb_sqltrc_364363_175_4447.trc
output file name : a.out
sort option : default
aggregate : yes
sys : yes
print : all
=================================================================
count: number of times the procedure was executed
cpu: cpu time(seconds)
this is not quite accurate due to threaded architecture
elapsed: elapsed time(seconds)
disk: number of physical reads from disk
query: number of blocks for consistent read
current: number of blocks in current mode
rows: number of rows processed
u_rows: original number of rows before filter was applied.
=================================================================
*******************************************************************************
UserID: 22
select * from TBL_PB_SYNC where c1<2
stage count cpu elapsed current query disk rows
-----------------------------------------------------------------------------
parse 1 0.00 0.00 0 16 0 0
exec 1 0.00 0.00 0 0 0 0
fetch 2 0.00 0.00 0 81 0 1
-----------------------------------------------------------------------------
sum 4 0.00 0.01 0 97 0 1
===============================================================================
PPID: 1684 UserID: 22
stage count cpu elapsed current query disk rows
-----------------------------------------------------------------------------
parse 1 0.00 0.00 0 16 0 0
exec 1 0.00 0.00 0 0 0 0
fetch 2 0.00 0.00 0 81 0 1
-----------------------------------------------------------------------------
sum 4 0.00 0.01 0 97 0 1
rows u_rows execution plan
----------------------------------------------------------
1 - partition RANGE (ALL PART) (et=16, cr=0, cu=0, co=31, cpu=0, ro=1)
1 - table access (rowid) TBL_PB_SYNC(3762) (et=25, cr=2, cu=0, co=31, cpu=0, ro=1)
1 - partition RANGE (ALL PART) (et=479, cr=0, cu=0, co=30, cpu=0, ro=1)
1 - index (range scan) TBL_PB_SYNC_I01_UK(3939) (et=405, cr=79, cu=0, co=30, cpu=0, ro=1)
*******************************************************************************
UserID: 22
alter session set sql_trace=n
stage count cpu elapsed current query disk rows
-----------------------------------------------------------------------------
parse 0 0.00 0.00 0 0 0 0
exec 0 0.00 0.00 0 0 0 0
fetch 0 0.00 0.00 0 0 0 0
-----------------------------------------------------------------------------
sum 0 0.00 0.00 0 0 0 0
*******************************************************************************
stage obj_id line_no count cpu elapsed
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
*******************************************************************************
OVERALL TOTAL
stage count cpu elapsed current query disk rows
-----------------------------------------------------------------------------
parse 1 0.00 0.00 0 16 0 0
exec 1 0.00 0.00 0 0 0 0
fetch 2 0.00 0.00 0 81 0 1
-----------------------------------------------------------------------------
sum 4 0.00 0.01 0 97 0 1
*******************************************************************************
2 SQL statements in trace file.
2 unique SQL statements in trace file.
1 unique SQL plans in trace file.
32 lines in trace file.