Document Type | Technical Information
Category | Monitoring/Inspection
Applicable Product Version | -
Document Number | TMOTI068
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=YTo 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 a50These 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
| in_sql_id | The SQL_ID value of the plan to query. If omitted, the SQL_ID of the last executed query in the session is used. |
|---|---|
| in_child_number | The CHILD_NUMBER value of the plan to query. If omitted, the CHILD_NUMBER of the last executed query in the session is used. |
| Format | Specifies the items to output. There are individual items and group items that set multiple individual items. Prefixing an item name with a hyphen (-) excludes that item. |
Individual Output Items
| CARDS | The estimated number of rows for the plan node predicted by the optimizer. |
|---|---|
| COST | The cost of the plan node predicted by the optimizer. |
| PARTITION | Partition-related information. |
| PARALLEL | Information related to parallel execution. |
| PREDICATE | Predicate information for each plan node. |
| REMOTE | Query details executed via database link for each plan node. |
| ROWS | The actual number of rows processed by the plan node. |
| ELAPTIME | The actual time spent executing the plan node. |
| USEDMEM | The actual amount of memory used by the plan node. |
| TEMPREAD | The number of temp read operations performed by the plan node. |
| TEMPWRITE | The number of temp write operations performed by the plan node. |
| BUFGETS | The number of buffer get requests made by the plan node. |
| STARTS | The number of times the plan node was restarted. |
| LAST | Outputs only the last execution's statistics. If not specified, cumulative statistics for all executions are shown. |
| PRECISE | Shows exact values for CARDS and ROWS without rounding. |
| HEADER | Displays basic plan information (sql id, hash value, total executions, total fetches, plan execution time). |
| SQL | Displays the query used to generate the plan. |
Group Output Items
| IOSTATS | Shows all IO-related execution statistics: TEMPREAD + TEMPWRITE + BUFGETS. |
|---|---|
| MEMSTATS | Shows all memory-related execution statistics: USEDMEM. |
| ALLSTATS | Shows all IO and memory-related execution statistics: IOSTATS + MEMSTATS. |
| BASIC | The default output format showing optimizer estimated cardinality, cost, and node execution time for the last execution: CARDS + COST + PART + ELAPTIME + LAST.
|
| TYPICAL | Adds node-level processed row counts, predicate information, and remote SQL info for the last execution to BASIC: BASIC + PE + ROWS + STARTS + PRED + REMOTE + PRECISE. |
| ALL | Includes ALLSTATS in the TYPICAL format: TYPICAL + ALLSTATS.
|
Execution Result