Overview

This describes how to view Plan and Stat using tbsql without an SQL Tool.

Method

Target Query

Select * from v$session;

Preparation Before Testing

GATHER_SQL_EXEC_TIME=Y

GATHER_SQL_PLAN_STAT=Y

To properly retrieve STAT information, the following two parameters must be set before running the query.

In Tibero, these parameters are set to N by default.

Set Session Properties After Connecting to Tbsql

SQL> set lines 200

SQL> col name for a30

SQL> col value for a50

SQL> col dflt_value for a50

These settings improve readability in the terminal environment.

Using the set autotrace Feature

How to Use

You can verify the usage by the command set autotrace exp plans stat.

After setting this command, running the query will display as shown in the above screen.

Using the SQL_TRACE Feature

Set the parameter SQL_TRACE=Y before executing the SQL.

The SQL_TRACE file is created in the directory specified by the parameter TRACE_LOG_DEST.

The generated file can be converted and checked using the tbprof command.

Execute the target query (the query result data is not important, so readability was not considered)

Check the location of Trace_log_dest.

Convert using Tbprof into a text file.

You can verify PLAN and STAT in the generated text file.

Using the DBMS_XPLAN Package

Provides functionality to output plan information and execution details in various formats.

The output method uses a pipelined function and outputs the information as query results using the TABLE() syntax. Also, since the output information is obtained through the V$SQL_PLAN and V$SQL_PLAN_STATISTICS views, the GATHER_SQL_PLAN_STAT parameter must be enabled to retrieve execution information.

DISPLAY_CURSOR

This function queries the plan registered in the Physical Plan Cache using the SQL_ID value. Since it is a pipelined function, it is used with the TABLE() function.

Details of the DISPLAY_CURSOR function are as follows.

Prototype

DBMS_XPLAN.DISPLAY_CURSOR (

in_sql_id VARCHAR2 default NULL, in_child_number NUMBER default NULL,

format   VARCHAR2 default 'BASIC LAST SQL')

RETURN dbms_xplan_type_table pipelined;

Parameters

Individual Output Items

Group Output Items

Execution Result