Overview

The shell script provides an auto-completion feature for three functions when tuning using the tbSQL utility: trace activation, DBMS_XPLAN checking, and tbprof analysis. This minimizes tedious tasks, allowing you to focus on tuning. This document introduces the tbProfiler.sh script.

tbProfiler.sh Overview

The tbProfiler.sh shell script runs only on LINUX environments and is recommended for use with TIBERO version 6 or higher.

• LINUX: RHEL7.9 or higher, CentOS 7.9 or higher
• TIBERO: 6 or higher

The tbProfiler.sh version covered in this document is 230217, and continuous feature improvements and bug fixes are made in the Github Repositories.

Note

For a brief explanation, please refer to the readme at the following github URL.

https://github.com/younjunsu/tbProfiler

Method

Main Features of tbProfiler.sh 

• Provides an automatic trace option application list
• Automatically outputs DBMS_XPLAN execution results
• Automatically outputs SQL TRACE file tbprof utility analysis results

tbProfiler.sh Configuration

1. Prepare tbprofiler.sh and tbsql.login files

- Download tbProfiler.tar.gz file > Upload to server > Extract > Check tbProfiler.sh and tbsql.login files

- Download tbProfiler.sh and tbsql.login files from Github > Upload to server > Check tbProfiler.sh and tbsql.login files

- The working directory location does not matter, but tbsql execution permission is required.

- Both tbProfiler.sh and tbsql.login files must be in the same directory.

total 24
-rw-r--r--.  1 ps1 psall   526 Feb 17  2023 tbsql.login
-rwxr--r--.  1 ps1 psall 15165 Feb 17  2023 tbProfiler.sh

2. tbprofiler.sh Configuration

# user configuration
#--------------------------------------------------------------------------------
TBSQL_USER=""
TBSQL_PASSWORD=""
TB_SQLPATH=""
SQL_TRACE_FILE_PATH=""
#--------------------------------------------------------------------------------
#TB_NLS_LANG=UTF8
#LANG=ko_KR.utf8
#stty erase ^H
#stty erase ^?
#--------------------------------------------------------------------------------

• Edit the tbProfiler.sh file with vi to set several parameters.

Required Settings

• TBSQL_USER = tbSQL user
• TBSQL_PASSWORD = password for tbSQL user
• TB_SQLPATH = path to tbsql.login file and working directory
• SQL_TRACE_FILE_PATH = directory path where files generated by SQL_TRACE=Y option are stored

Optional Settings

• TB_NLS_LANG = client character set used by tbSQL
• LANG = LANG setting displayed in the terminal emulator
• stty erase ^H or ^? = character deletion signal when backspace is pressed

Example

# user configuration
#--------------------------------------------------------------------------------
TBSQL_USER="tibero"
TBSQL_PASSWORD="tmax"
TB_SQLPATH="/sdiske/ps1/tbProfiler230217"
SQL_TRACE_FILE_PATH="/sdiske/ps1/tibero7/instance/tb7/log/sqltrace"
#--------------------------------------------------------------------------------
#TB_NLS_LANG=UTF8
#LANG=ko_KR.utf8
#stty erase ^H
#stty erase ^?
#--------------------------------------------------------------------------------

Using tbProfiler.sh

tbProfiler.sh Execution Description

[ps1@psdblcbp:/sdiske/ps1/tbProfiler230217]$ sh tbProfiler.sh

###############################
 tbProfiler mode help message
###############################
 usage: sh tbProfiler.sh [option]
-----------------------------
  run  : start tbsql Profiler
  help : help message
-----------------------------

When executed without options or with the help command, the help message is displayed.

tbProfiler.sh Execution

###############################
# tbProfiler mode options
###############################
  - TIBERO VERSION             : Tibero 7 (DB 7.2.4) Build 303704
  - TIBERO USER                : tibero
  - TB_SQLPATH                 : /sdiske/ps1/soi/tbProfiler230217
  - SQL_TRACE_FILE_PATH        : /sdiske/ps1/soi/tibero7/instance/tb7/log/sqltrace
  - TB_NLS_LANG                :
  - DB CHARACTERSET_NAME       : UTF8
  - DB NCHAR_CHARACTERSET_NAME : UTF16
-----------------------------
  sql tbprof file count : 0
    - /sdiske/ps1/soi/tbProfiler230217
  sql trace file count  : 0
    - /sdiske/ps1/soi/tibero7/instance/tb7/log/sqltrace
-----------------------------


###############################
# Please select the trace option.
###############################
 - set autot on exp stat plans    : 1
 - set autot on                   : 2
 - set autot on exp               : 3
 - set autot on stat              : 4
 - set autot on plans             : 5
 - set autot trace exp stat plans : 6
 - set autot trace                : 7
 - set autot trace exp            : 8
 - set autot trace stat           : 9
 - set autot trace plans          : 10
-----------------------------
 - quit : q
-----------------------------
 - other key no trace
-----------------------------

  press key : 3

tbProfiler Mode Options

• TIBERO VERSION: Tibero version
• TIBERO USER: User logged into tbsql
• TB_SQLPATH: Path to tbsql.login file and working directory
• SQL_TRACE_FILE_PATH: Path of trace files dumped due to SQL_TRACE=Y parameter
• TB_NLS_LANG: tbSQL client CHARSET
• DB_CHARACTERSET_NAME: Server CHARSET
• DB NCHAR_CHARACTERSET_NAME: Server NCHARSET
• sql tbprof file count: number of tbprof output files
• sql trace file count: number of sql trace files

Please select the trace option

• set autot on exp stat plans: optimizer execution plan, execution statistics, execution results / SQL results output
• set autot on: optimizer execution plan, execution statistics / SQL results output
• set autot on exp: optimizer execution plan / SQL results output
• set autot on stat: optimizer execution statistics / SQL results output
• set autot on plans: optimizer execution results / SQL results output
• set autot trace exp stat plans: optimizer execution plan, execution statistics, execution results / SQL results not output
• set autot trace: optimizer execution plan, execution statistics / SQL results not output
• set autot trace exp: optimizer execution plan / SQL results not output
• set autot trace stat: optimizer execution statistics / SQL results not output
• set autot trace plans: optimizer execution results / SQL results not output

tbProfiler.sh SQL Execution

###############################
# tbsql.login options apply
###############################

Spooling is started.


TBPROF            SID    SERIAL#        PID
---------- ---------- ---------- ----------
tbprofinfo        103    1379426      29659



#############################
# Please execute the query.
#############################


select * from dual;

DUMMY
-----
X

1 row selected.

Total elapsed time 00:00:00.015651

SQL ID: a5ks9fhw2v9s1
Child number: 6966
Plan hash value: 3480736023

Execution Plan
----------------------------------------------------------------------------------------------------
   1  DPV: _VT_DUAL (Cost:1, %%CPU:0, Rows:1)
###############################
# TIBERO XPLAN
###############################


SQL ID         : a5ks9fhw2v9s1
CHILD NUMBER   : 6966
HASH VALUE     : 942515969
PLAN HASH VALUE: 3480736023
EXECUTIONS     : 1
FETCHES        : 1
LOADED AT      : 2025/11/18 10:25:53
TOT ELAPSED TIME: 00:00:00.0068
AVG ELAPSED TIME: 00:00:00.0068
TOT BUFFER GETS: 0
AVG BUFFER GETS: 0
SQL            :
select * from dual

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| ID  | Operation            | Name     | Cost (%CPU)  | Cards   | Rows    | Elaps. Time  | BUFGETS  | Starts   | Used Mem | Temp. Read | Temp. Write|  Reads   |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 | DPV                  | _VT_DUAL |    1      (0)|       1 |       1 |00:00:00.0000 |       0  |       1  |       0K |         0  |         0  |       0  |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------

LAST ELAPSED TIME: 00:00:00.0000

tbsql.login options apply

• Automatically applies tbSQL options specified in the tbsql.login file

Please execute the query.

• Enter query
• Re-execute query by entering again without pressing q
• Check query XPLAN by pressing q then Enter (XPLAN of the last executed query)

tbProfiler mode menu

###############################
# tbProfiler mode menu
###############################

  - tbProfiler  : re
  - tbprof      : tr
  - quit        : q
-----------------------------
    other key retry.
-----------------------------

  press key :

tbProfiler mode menu

• re: moves to the initial tbProfiler execution menu when selected
• tr: performs tbprof
• q: exits tbProfiler

tbProfiler mode menu "re"

###############################
# tbProfiler mode menu
###############################

  - tbProfiler  : re
  - tbprof      : tr
  - quit        : q
-----------------------------
    other key retry.
-----------------------------

  press key : re
###############################
# tbProfiler mode options
###############################
  - TIBERO VERSION             : Tibero 7 (DB 7.2.4) Build 303704
  - TIBERO USER                : tibero
  - TB_SQLPATH                 : /sdiske/ps1/tbProfiler230217
  - SQL_TRACE_FILE_PATH        : /sdiske/ps1/tibero7/instance/tb7/log/sqltrace
  - TB_NLS_LANG                :
  - DB CHARACTERSET_NAME       : UTF8
  - DB NCHAR_CHARACTERSET_NAME : UTF16
-----------------------------
  sql tbprof file count : 0
    - /sdiske/ps1/tbProfiler230217
  sql trace file count  : 1
    - /sdiske/ps1/tibero7/instance/tb7/log/sqltrace
-----------------------------

###############################
# Please select the trace option.
###############################
 - set autot on exp stat plans    : 1
 - set autot on                   : 2
 - set autot on exp               : 3
 - set autot on stat              : 4
 - set autot on plans             : 5
 - set autot trace exp stat plans : 6
 - set autot trace                : 7
 - set autot trace exp            : 8
 - set autot trace stat           : 9
 - set autot trace plans          : 10
-----------------------------
 - quit : q
-----------------------------
 - other key no trace
-----------------------------

  press key :

• When "re" is selected, it moves to the initial tbProfiler execution menu

tbProfiler mode menu "tr"

###############################
# tbProfiler mode menu
###############################

  - tbProfiler  : re
  - tbprof      : tr
  - quit        : q
-----------------------------
    other key retry.
-----------------------------

  press key : tr


###############################
# tbprof extract
###############################

progress : -(90%)

TBPROF 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

 input file name : /sdiske/ps1/tibero7/instance/tb7/log/sqltrace/tb_sqltrc_29659_103_1383332.trc
 output file name : /sdiske/ps1/tbProfiler230217/log/1763430794_tbprof.outfile
 sort option : default
 aggregate : yes
 sys : no
 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.
 =================================================================

.... omitted ....

vi exit

-----------------------------
- file name : /sdiske/ps1/tbProfiler230217/log/1763430794_tbprof.outfile
-----------------------------
  tbprof out file remove (y or n) ?  y

• When "tr" is selected, the trc file generated by the executed query is analyzed using the tbprof utility
• progress (100%): after 1 second, the tbprof outfile is opened with vi
• After completing tbprof outfile analysis, exit vi command mode by typing :q
• Prompts whether to delete the tbprof outfile

tbProfiler mode menu "q"

###############################
# tbProfiler mode menu
###############################

  - tbProfiler  : re
  - tbprof      : tr
  - quit        : q
-----------------------------
    other key retry.
-----------------------------

  press key : q

###############################
# tbProfiler mode stop
###############################
 ....exit

• Use "q" to exit when query usage via tbProfiler.sh shell script or tbProf outfile analysis is complete

tbProfiler.sh Errors

This section provides error messages related to the tbProfiler.sh shell script.

tbProfiler.sh Shell Script Error Messages

# If the tbsvr corresponding to TB_SID is not running
ERROR : Please check the tbsvr process.

# Occurs when the TB_SID environment variable is not set
ERROR : TB_SID variable is empty.

# Occurs when TBSQL_USER parameter in tbProfiler.sh is not set
ERROR : TBSQL_USER variable is empty.

# Occurs when TBSQL_PASSWORD parameter in tbProfiler.sh is not set
ERROR : TBSQL_PASSWORD variable is empty.

# Occurs when TB_SQLPATH parameter in tbProfiler.sh is not set
ERROR : TB_SQLPATH variable is empty.

# Occurs when TB_SQLPATH path does not exist
ERROR : TB_SQLPATH path dose not exist.

# Occurs when SQL_TRACE_FILE_PATH parameter in tbProfiler.sh is not set
ERROR : SQL_TRACE_FILE_PATH variable is empty.

# Occurs when SQL_TRACE_FILE_PATH path does not exist
ERROR : SQL_TRACE_FILE_PATH path dose not exist.

tbProfiler.sh Plugin

If you want to execute previously run queries using the up arrow key in tbSQL, installing the rlwrap utility makes query execution more convenient.

rlwrap Plugin

Installation method using yum
root# yum install -y epel-release 
root# yum install -y rlwrap 

If installation does not proceed using the above method, you can install it using the following commands.


Download rpm and install with yum
root# wget https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/r/rlwrap-0.45.2-2.el7.x86_64.rpm 
root# rpm -Uvh rlwrap-0.45.2-2.el7.x86_64.rpm 
root# yum install -y perl perl-Data-Dumper perl-File-Slurp python3 

If rlwrap is not installed properly and tbProfiler.sh errors occur,
please change rlwrap_vaild_check=Y to N inside the tbProfiler.sh shell script and use it.

• Once installed properly, tbProfiler.sh will automatically run as rlwrap tbsql, allowing you to retrieve previously executed queries using the arrow keys.