Skip to main content

Search

Welcome to Tibero GTS!

How to Check SQL TRACE for Parallel Execution Queries

Document Type | Technical Information

Category | Utility

Applicable Product Versions | 6FS06, 6FS07, 6FS07PS, 7FS01, 7FS02, 7FS02PS

Document Number | TUTTI007

 

Overview

When the SQL Trace feature is enabled, SQL execution information is recorded in one file (with the extension trc) per session.
However, SQL queries using Parallel execution store information distributed across multiple files.
If you convert each file separately, inaccurate information may remain, so this document introduces an option to merge the files into a single result file.

 

 

Method

Example of SQL Execution and Trace File Generation

ALTER SESSION SET SQL_TRACE=Y or ALTER SESSION SET SQL_TRACE=Y;

~~PARALLEL SQL execution


ALTER SESSION SET SQL_TRACE=Y or ALTER SESSION SET SQL_TRACE=N;

 

Example of Return Command

tbprof directory_name output_file_name dir=y
Note
Use the tbprof option dir=yes.
 
 

1. After executing a parallel query, multiple .trc files are generated as shown below

-rw-r--r--. 1 tibero tibero 1799 Apr 29 08:37 tb_sqltrc_934101_88_977946.trc
-rw-r--r--. 1 tibero tibero 1441 Apr 29 08:37 tb_sqltrc_934103_108_980302.trc
-rw-r--r--. 1 tibero tibero 1442 Apr 29 08:37 tb_sqltrc_934103_109_980301.trc
-rw-r--r--. 1 tibero tibero 1438 Apr 29 08:37 tb_sqltrc_934103_110_980299.trc
-rw-r--r--. 1 tibero tibero 1438 Apr 29 08:37 tb_sqltrc_934103_111_980304.trc
-rw-r--r--. 1 tibero tibero 1332 Apr 29 08:37 tb_sqltrc_934103_112_980305.trc
-rw-r--r--. 1 tibero tibero 1333 Apr 29 08:37 tb_sqltrc_934103_113_980303.trc
-rw-r--r--. 1 tibero tibero 1335 Apr 29 08:37 tb_sqltrc_934103_114_980300.trc
-rw-r--r--. 1 tibero tibero 1332 Apr 29 08:37 tb_sqltrc_934103_115_980306.trc
Note
How to Check the Default Path and Configuration Parameter for SQL Trace .trc Files
  • default: $TB_HOME/instance/$TB_SID/log/sqltrace/
  • Specify in the tip file: Check the SQL_TRACE_DEST parameter.
  • Check using tbsql.
    SQL> show param SQL_TRACE_DEST
 

2.  Move the .trc files and Execute the Command

After executing the parallel SQL, move the generated .trc files to a directory, then execute the command below. 

$ tbprof tracedir test.out dir=yes  (tbprof directory_name output_file_name dir=y)

[tibero@ENT-ROCKY8 sqltrace]$  tbprof tracedir test.out dir=yes

TBPROF 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Note
  • If SQL Trace is already in use, other queries' .trc files may exist in the directory where trace dump files are stored. In this case, unrelated .trc files might be mixed in, so caution is necessary.
  • Therefore, create a separate directory to store the .trc files for parallel queries.

 

 

 

Related to