문서유형ㅣ기술정보
분야ㅣ관리/환경설정
적용제품버전ㅣTibero 7.2.2
문서번호ㅣTADTI197
개요
티베로를 처음 접하는 사용자가 티베로 설치 후 티베로에서 제공하는 기능을 이용하여 기본적으로 DB 성능을 분석하는 내용을 담고 있습니다. 기능에 대한 자세한 내용은 매뉴얼을 참조합니다.
방법
TPR Report
TPR(Tibero Performance Repository)은 통계 정보를 주기적으로 자동 수집하여 수집한 통계 자료에 대한 자체적인 분석 리포트 출력 기능을 제공하여 시스템 부하 분석에 도움을 줄 수 있는 기능입니다. 주기적으로 (default 1시간) 특정 테이블에 데이터를 저장하고 이렇게 저장된 정보를 Snapshot 이라고 부릅니다. 다양한 파라미터 및 옵션들은 매뉴얼을 참고합니다.
| 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
sqltrace 파라미터를 키고 sql을 수행하면 SQL_TRACE_DEST 위치에 파일이 생성됩니다. 생성된 파일로 tbprof 유틸리티를 사용해 분석 가능한 형태로 Report 파일을 생성 해줍니다. alter system 단위로 수행하게 되면 DB에 발생되고 있는 모든 쿼리가 sqltrace 파일로 떨어지므로 session 단위로 수행 할 것을 권장합니다.
| 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.