Skip to main content

Search

Welcome to Tibero GTS!

Using SQL TRACE and Checking Result Values

Document Type | Technical Information 

Field | Tuning

Applicable Product Versions | 7FS02, 7FS02PS

Document Number | TTUTI002

 

Overview

SQL_TRACE is a feature that records trace information during SQL execution, enabling confirmation of execution statistics. 
This document explains how to record trace information during SQL execution using this feature and how to check the results.

 

Method

Caution
Enabling Trace at the instance level may cause overall system performance degradation.
It is recommended to run trace at the session level.

 

Related Parameters

Parameter Name
Description
SQL_TRACE
Whether the SQL trace feature is enabled (Y: enabled, N: disabled)
 

SQL_TRACE Usage Procedure

1.Change the session parameter value (SQL_TRACE=Y) and run the query

$ tbsql sys/tibero
SQL> set timing on;
SQL> set lines 250;
SQL> set rows off;

-- Enable SQL_TRACE
SQL> alter session set sql_trace = y;


-- Execute query
SQL> @query.sql


-- Disable SQL_TRACE
SQL> alter session set sql_trace = n;
 

2.Convert the .trc file to an out file

The trace file (.trc) of the executed query is generated in the $TB_HOME/instance/$TB_SID/sqltrace directory.

-. Move to the trace file location
$ cd $TB_HOME/instance/$TB_SID/sqltrace

-. Convert the trace file to a readable Out file
$ tbprof tb_sqltrc_13795_186_2104490.trc sqltrace.out
 

3.Analyze the sqltrace.out results

  • parse: Time and resource usage for SQL statement parsing
  • exec: Disk reads, block accesses, and number of rows processed during SQL execution
  • fetch: Resources consumed during data fetching
 
[Key Statistical Items]
Item
Description
parse
Statistics about the stage when the SQL statement is parsed (syntax analysis)
exec
Statistics about the execution stage of the SQL statement (DML: Update, Insert, Delete)
fetch
Statistics on data fetching (Fetch) during SQL execution
count
Number of times SQL parsing/execution/fetching was performed
cpu
Actual CPU time used
elapsed
Actual elapsed time from start to finish of the operation
current
Number of blocks accessed that are valid only to this session because the work was not committed 
(Increases during changes such as Update, Insert, Delete)
query
Number of Consistent Read blocks read during read operations such as Select  
(Blocks read that are unchanged in memory or snapshot blocks copied because changes made by other sessions are not yet committed)
disk
Number of blocks read from disk
rows
Final number of rows processed as a result of SQL execution
 
[Checking Execution Information]

 
 
 
 
 
 
 
 

Related to