Document Type | Technical Information
Field | Tuning
Applicable Product Version | 7FS02PS
Document Number |
Overview
AUTOTRACE is a feature that automatically collects the execution plan and SQL execution statistics when running a query and provides the results. It allows you to automatically collect and check the execution plan and SQL execution statistics during query execution, which can be usefully utilized for query tuning and performance diagnosis.
Method
Related Parameters
Parameter Name | Description |
|---|---|
gather_sql_plan_stat | Sets whether to collect execution plan (Plan) and related statistics information during SQL execution Y: Collect, N: Do not collect |
Related View Information
Parameter Name | Description |
|---|---|
v$sql_plan | Execution plan information of SQL statements |
v$sysstat | System statistics |
v$sql_plan_statistics | Execution statistics information of SQL statements |
[v$sql_plan]
SQL> desc v$sql_plan COLUMN_NAME TYPE CONSTRAINT ----------------------- ----------------- --------------- HASH_VALUE NUMBER PLAN_HASH_VALUE NUMBER SQL_ID VARCHAR(13) CHILD_NUMBER NUMBER OPERATION VARCHAR(128) OBJECT# NUMBER OBJECT_OWNER VARCHAR(128) OBJECT_NAME VARCHAR(128) OBJECT_TYPE VARCHAR(20) ID NUMBER PARENT_ID NUMBER DEPTH NUMBER POSITION NUMBER SEARCH_COLUMNS NUMBER COST NUMBER CPU_COST NUMBER IO_COST NUMBER CARDINALITY NUMBER PSTART VARCHAR(38) PEND VARCHAR(38) OTHERS VARCHAR(65532) ACCESS_PREDICATES VARCHAR(65532) FILTER_PREDICATES VARCHAR(65532) LPN VARCHAR(128) ADVISED_COLNO VARCHAR(4000)
[v$sysstat]
SQL> desc v$sysstat COLUMN_NAME TYPE CONSTRAINT ----------------------- ----------------- --------------- STAT# NUMBER NAME VARCHAR(60) CLASS NUMBER
[v$sql_plan_statistics]
SQL> desc v$sql_plan_statistics COLUMN_NAME TYPE CONSTRAINT ----------------------- ----------------- --------------- HASH_VALUE NUMBER PLAN_HASH_VALUE NUMBER SQL_ID VARCHAR(13) CHILD_NUMBER NUMBER ID NUMBER EXECUTIONS NUMBER LAST_STARTS NUMBER STARTS NUMBER LAST_OUTPUT_ROWS NUMBER OUTPUT_ROWS NUMBER LAST_LEFT_INPUT_ROWS NUMBER LEFT_INPUT_ROWS NUMBER LAST_RIGHT_INPUT_ROWS NUMBER RIGHT_INPUT_ROWS NUMBER LAST_CR_BUFFER_GETS NUMBER CR_BUFFER_GETS NUMBER LAST_ELAPSED_TIME NUMBER ELAPSED_TIME NUMBER LAST_MEM_USAGE NUMBER MEM_USAGE NUMBER LAST_TEMP_SEGMENT_READ_CNT NUMBER TEMP_SEGMENT_READ_CNT NUMBER LAST_TEMP_SEGMENT_WRITE_CNT NUMBER TEMP_SEGMENT_WRITE_CNT NUMBER LAST_CU_BUFFER_GETS NUMBER CU_BUFFER_GETS NUMBER LAST_DISK_READS NUMBER DISK_READS NUMBER
Procedure to Use AUTOTRACE
Execute the query after setting the execution plan collection parameter.
1. Set Server Encoding (if the DB encoding is UTF8)
$ export LANG=ko_KR.utf8 $ echo $LANG ko_KR.utf8
2. Connect to TBSQL and Set AUTOTRACE
The result is saved to the
autotrace.txt file.- Enable AUTOTRACE:
set autot on exp stat plans - Set execution plan collection:
alter session set gather_sql_plan_stat=y
$ tbsql sys/tibero SQL> spool autotrace.txt SQL> alter session set gather_sql_plan_stat=y; SQL> set rows off SQL> set autot on exp stat plans SQL> set timing on SQL> set lines 250 SQL> @query.sql SQL> alter session set gather_sql_plan_stat=n; SQL> spool off
3. AUTOTRACE Result Check Items
3.1. Query Execution Time, SQL_ID, Child number, Plan hash value
3.2. Execution Plan (Execution Plan)
Cost | The COST column value of V$SQL_PLAN, which is the cumulative cost value of the lower plan nodes |
|---|---|
%%CPU | The percentage of CPU Cost in the Cost |
Rows | The number of rows the optimizer predicts as the result of executing the plan node |
3.3. Predicate Information
- access : Join predicate, Index access predicate
- filter : filter predicate
3.4. Execution Stat (Execution Statistics)
[SQL Execution Information]
Time (ms) | Execution time of the plan node |
|---|---|
Rows | Number of rows returned after executing the plan node |
Starts | Number of times the plan node was repeatedly executed during the entire SQL execution |
