Google Git
Sign in
apache/trafodion/master/./docs/sql_reference/src/asciidoc/_chapters/runtime_stats.adoc
blob: bbde7cd82badb31a6e8764050b0f4a812f3101e9 [file] [log] [blame]
////
/**
* @@@ START COPYRIGHT @@@
*
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership. The ASF licenses this file
* to you under the Apache License, Version 2.0 (the
* "License"); you may not use this file except in compliance
* with the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing,
* software distributed under the License is distributed on an
* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
* KIND, either express or implied. See the License for the
* specific language governing permissions and limitations
* under the License.
*
* @@@ END COPYRIGHT @@@
*/
////
[[sql_runtime_statistics]]
= SQL Runtime Statistics
The Runtime Management System (RMS) shows the status of queries while
they are running. RMS can service on-demand requests from the {project-name}
Command Interface (TrafCI) to get statistics for a given query ID or for
active queries in a given process. RMS also provides information about
itself to determine the health of the RMS infrastructure.
RMS provides the summary statistics for each fragment instance and
detailed statistics for each operator (TDB_ID) of a given active query.
A query is considered active if either the compilation or execution is
in progress. The variable_input column output is returned as a multiple
value pair of the form _token=value_. For more information, see
<<considerations_obtaining_stats_fragment,
Considerations For Obtaining Statistics For Each Fragment-Instance of an Active Query>>.
RMS is enabled and available all the time.
== PERTABLE and OPERATOR Statistics
The SQL database engine determines which type of statistics collection
is appropriate for the query. The RMS infrastructure provides the
runtime metrics about a query while a query is executing. You can
identify queries that are using excessive resources, suspend a query to
determine its impact on resources, and cancel a query, when necessary.
PERTABLE statistics count rows and report rows estimated in the
operators in the disk processes and time spent in the ESP processes.
Although PERTABLE statistics can deduce when all the rows have been read
from the disks, it is impossible to correctly assess the current state
of the query.
Complex queries such as joins, sorts, and group result sets are often
too large to fit into memory, so intermediate results must overflow to
scratch files. These operators are called Big Memory Operators (BMOs).
Because of the BMOs, RMS provides OPERATOR statistics, which provide a
richer set of statistics so that the current state of a query can be
determined at any time.
With OPERATOR statistics, all SQL operators are instrumented and the
following statistics are collected:
* Node time spent in the operator
* Actual number of rows flowing to the parent operator
* Estimated number of rows flowing to the parent operator (estimated by the optimizer)
* Virtual memory used in the BMO
* Amount of data overflowed to scratch files and read back to the query
For more information,
see <<displaying_sql_runtimestatistics,Displaying SQL Runtime Statistics>>.
[[adaptive_statistics_collection]]
== Adaptive Statistics Collection
The SQL database engine chooses the appropriate statistics collection
type based on the type of query. By default, the SQL database engine
statistics collection is OPERATOR statistics. You can view the
statistics in different formats: PERTABLE, ACCUMULATED, PROGRESS, and
DEFAULT. Statistics Collection is adaptive to ensure that sufficient
statistics information is available without
causing any performance impact to the query's execution. For some
queries, either no statistics or PERTABLE statistics are collected.
[cols="50%,50%l",options="header"]
|===
| Query Type | Statistics Collection Type
| OLT optimized queries | PERTABLE
| Unique queries | PERTABLE
| CQD | No statistics
| SET commands | No statistics
| EXPLAIN | No statistics
| GET STATISTICS | No statistics
| All other queries | DEFAULT
|===
<<<
[[retrieving_sql_runtime_statistics]]
== Retrieving SQL Runtime Statistics
[[using_the_get_statistics_command]]
=== Using the GET STATISTICS Command
The GET STATISTICS command shows statistical information for:
* A single query ID (QID)
* Active queries for a process ID (PID)
* RMS itself
A query is considered active if either compilation or execution is in
progress. In the case of a SELECT statement, a query is in execution
until the statement or result set is closed. Logically, a query is
considered to be active when the compile end time is -1 and the compile
start time is not -1, or when the execute end time is -1 and the execute
start time is not -1.
[[syntax_of_get_statistics]]
=== Syntax of GET STATISTICS
```
GET STATISTICS FOR QID { query-id | CURRENT } [stats-view-type] }
| PID { process-name | [ nodeid, pid ] } [ ACTIVE n ][ stats-view-type ]
| RMS node-num | ALL [ RESET ]
stats-view-type is:
ACCUMULATED | PERTABLE | PROGRESS | DEFAULT
```
* `QID`
+
Required keyword if requesting statistics for a specific query.
* `_query-id_`
+
is the query ID. You must put the _query-id_ in double quotes if the
user name in the query ID contains lower case letters or if the user
name contains a period.
+
NOTE: The _query-id_ is a unique identifier for the SQL statement
generated when the query is compiled (prepared). The _query-id_ is
visible for queries executed through certain TrafCI commands.
* `CURRENT`
+
provides statistics for the most recently prepared or executed statement
in the same session where you run the GET STATISTICS FOR QID CURRENT
command. You must issue the GET STATISTICS FOR QID CURRENT command
immediately after the PREPARE or EXECUTE statement.
* `PID`
+
Required keyword if requesting statistics for an active query in a given
process.
* `_process-name_`
+
is the name of the process ID (PID) in the format: $Z_nnn_. The
process name can be for the master (MXOSRVR) or executor server process
(ESP). If the process name corresponds to the ESP, the ACTIVE _n_ query
is just the _n_th query in that ESP and might not be the currently
active query in the ESP.
* `ACTIVE _n_`
+
describes which of the active queries for which RMS returns statistics.
ACTIVE 1 is the default. ACTIVE 1 returns statistics for the first
active query. ACTIVE 2 returns statistics for the second active query.
* `_stats-view-type_`
+
sets the statistics view type to a different format. Statistics are
collected at the operator level by default. For exceptions, see
<<adaptive_statistics_collection,Adaptive Statistics Collection>>.
* `ACCUMULATED`
+
causes the statistics to be displayed in an aggregated summary across
all tables in the query.
* `PERTABLE`
+
displays statistics for each table in the query. This is the default
_stats-view-type_ although statistics are collected at the operator
level. If the collection occurs at a lower level due to Adaptive
Statistics, the default is the lowered collection level. For more
information,
see <<adaptive_statistics_collection,Adaptive Statistics Collection>>.
* `progress`
+
displays rows of information corresponding to each of the big memory
operators (BMO) operators involved in the query, in addition to pertable
_stats-view-type_. For more information about BMOs,
see <<pertable_and_operator_statistics,Pertable and Operator Statistics>>.
* `PROGRESS`
+
displays rows of information corresponding to each of the big memory
operators (BMO) operators involved in the query, in addition to pertable
_stats-view-type_. For more information about BMOs,
see <<pertable_and_operator_statistics,Pertable and Operator Statistics>>.
* `default`
+
displays statistics in the same way as it is collected.
* `RMS`
+
required keyword if requesting statistics about RMS itself.
* `_node-num_`
+
returns the statistics about the RMS infrastructure for a given node.
* `ALL`
+
returns the statistics about the RMS infrastructure for every node in the cluster.
* `RESET`
+
resets the cumulative RMS statistics counters.
[[examples_of_get_statistics]]
=== Examples of GET STATISTICS
These examples show the runtime statistics that various get statistics
commands return. for more information about the runtime statistics and
RMS counters,
see <<displaying_sql_runtime_statistics,Displaying SQL Runtime Statistics>>.
* This GET STATISTICS command returns PERTABLE statistics for the most
recently executed statement in the same session:
+
```
SQL> GET STATISTICS FOR QID CURRENT;
Qid MXID1100801837021216821167247667200000000030000_59_SQL_CUR_6
Compile Start Time 2011/03/30 07:29:15.332216
Compile End Time 2011/03/30 07:29:15.339467
Compile Elapsed Time 0:00:00.007251
Execute Start Time 2011/03/30 07:29:15.383077
Execute End Time 2011/03/30 07:29:15.470222
Execute Elapsed Time 0:00:00.087145
State CLOSE
Rows Affected 0
SQL Error Code 100
Stats Error Code 0
Query Type SQL_SELECT_NON_UNIQUE Estimated Accessed Rows 0
Estimated Used Rows 0
Parent Qid NONE
Child Qid NONE
Number of SQL Processes 1
Number of Cpus 1
Execution Priority -1
Transaction Id -1
Source String SELECT
CUR_SERVICE,PLAN,TEXT,CUR_SCHEMA,RULE_NAME,APPL_NAME,SESSION_NAME,DSN_NAME,ROLE_NAME,DEFAULT_SCHEMA_ACCESS_ONLY
FROM(VALUES(CAST('HP_DEFAULT_SERVICE' as VARCHAR(50)),CAST(0 AS INT),CAST(0 AS INT),CAST('NEO.USR' as
VARCHAR(260)),CAST('' as VARCHAR(
SQL Source Length 548
Rows Returned 1
First Row Returned Time 2011/03/30 07:29:15.469778
Last Error before AQR 0
Number of AQR retries 0
Delay before AQR 0
No. of times reclaimed 0
Stats Collection Type OPERATOR_STATS
SQL Process Busy Time 0
UDR Process Busy Time 0
SQL Space Allocated 32 KB
SQL Space Used 3 KB
SQL Heap Allocated 7 KB
SQL Heap Used 1 KB
EID Space Allocated 0 KB
EID Space Used 0 KB
EID Heap Allocated 0 KB
EID Heap Used 0 KB
Processes Created 0
Process Create Time 0
Request Message Count 0
Request Message Bytes 0
Reply Message Count 0
Reply Message Bytes 0
Scr. Overflow Mode DISK
Scr File Count 0
Scr. Buffer Blk Size 0
Scr. Buffer Blks Read 0
Scr. Buffer Blks Written 0
Scr. Read Count 0
Scr. Write Count 0
--- SQL operation complete.
```
<<<
* This GET STATISTICS command returns PERTABLE statistics for the
specified query ID (note that this command should be issued in the same
session):
+
```
SQL> GET STATISTICS FOR QID
+> "MXID1100800517921216818752807267200000000030000_48_SQL_CUR_2"
+> ;
Qid MXID1100800517921216818752807267200000000030000_48_SQL_CUR_2
Compile Start Time 2011/03/30 00:53:21.382211
Compile End Time 2011/03/30 00:53:22.980201
Compile Elapsed Time 0:00:01.597990
Execute Start Time 2011/03/30 00:53:23.079979
Execute End Time -1
Execute Elapsed Time 7:16:13.494563
State OPEN
Rows Affected -1
SQL Error Code 0
Stats Error Code 0
Query Type SQL_SELECT_NON_UNIQUE
Estimated Accessed Rows 2,487,984
Estimated Used Rows 2,487,984
Parent Qid NONE
Child Qid NONE
Number of SQL Processes 129
Number of Cpus 9
Execution Priority -1
Transaction Id 34359956800
Source String select count(*) from
MANAGEABILITY.INSTANCE_REPOSITORY.EVENTS_TEXT K,
MANAGEABILITY.INSTANCE_REPOSITORY.EVENTS_TEXT J,
MANAGEABILITY.INSTANCE_REPOSITORY.EVENTS_TEXT H,
MANAGEABILITY.INSTANCE_REPOSITORY.EVENTS_TEXT G
SQL Source Length 220
Rows Returned 0
First Row Returned Time -1
Last Error before AQR 0
Number of AQR retries 0
Delay before AQR 0
No. of times reclaimed 0
Stats Collection Type OPERATOR_STATS
SQL Process Busy Time 830,910,830,000
UDR Process Busy Time 0
SQL Space Allocated 179,049 KB
SQL Space Used 171,746 KB
SQL Heap Allocated 1,140,503 KB
SQL Heap Used 1,138,033 KB
EID Space Allocated 46,080 KB
EID Space Used 42,816 KB
EID Heap Allocated 18,624 KB
EID Heap Used 192 KB
Processes Created 32
Process Create Time 799,702
Request Message Count 202,214
Request Message Bytes 27,091,104
Reply Message Count 197,563
Reply Message Bytes 1,008,451,688
Scr. Overflow Mode DISK
Scr File Count 0
Scr. Buffer Blk Size 0
Scr. Buffer Blks Read 0
Scr. Buffer Blks Written 0
Scr. Read Count 0
Scr. Write Count 0
Table Name
Records Accessed Records Used Disk Message Message Lock Lock Disk Process Open Open
Estimated/Actual Estimated/Actual I/Os Count Bytes Escl wait Busy Time Count Time
MANAGEABILITY.INSTANCE_REPOSITORY.EVENTS_TEXT(H)
621,996 621,996
621,998 621,998 0 441 10,666,384 0 0 303,955 32 15,967
MANAGEABILITY.INSTANCE_REPOSITORY.EVENTS_TEXT(J) 621,996 621,996
621,996 621,996
621,998 621,998 0 439 10,666,384 0 0 289,949 32 19,680
MANAGEABILITY.INSTANCE_REPOSITORY.EVENTS_TEXT(K) 621,996 621,996
621,996 621,996
621,998 621,998 0 439 10,666,384 0 0 301,956 32 14,419
MANAGEABILITY.INSTANCE_REPOSITORY.EVENTS_TEXT(G)
0 621,996
0 0 0 192 4,548,048 0 0 0 32 40,019
--- SQL operation complete.
```
<<<
* This GET STATISTICS command returns ACCUMULATED statistics for the
most recently executed statement in the same session:
+
```
SQL> GET STATISTICS FOR QID CURRENT ACCUMULATED;
Qid MXID1100802517321216821277534304000000000340000_957_SQL_CUR_6
Compile Start Time 2011/03/30 08:05:07.646667
Compile End Time 2011/03/30 08:05:07.647622
Compile Elapsed Time 0:00:00.000955
Execute Start Time 2011/03/30 08:05:07.652710
Execute End Time 2011/03/30 08:05:07.740461
Execute Elapsed Time 0:00:00.087751
State CLOSE
Rows Affected 0
SQL Error Code 100
Stats Error Code 0
Query Type SQL_SELECT_NON_UNIQUE
Estimated Accessed Rows 0
Estimated Used Rows 0
Parent Qid NONE
Child Qid NONE
Number of SQL Processes 0
Number of Cpus 0
Execution Priority -1
Transaction Id -1
Source String SELECT
CUR_SERVICE,PLAN,TEXT,CUR_SCHEMA,RULE_NAME,APPL_NAME,SESSION_NAME,DSN_NAME,ROLE_NAME,DEFAULT_SCHEMA_ACCESS_ONLY
FROM(VALUES(CAST('HP_DEFAULT_SERVICE' as VARCHAR(50)),CAST(0 AS INT),CAST(0 AS INT),CAST('NEO.SCH' as
VARCHAR(260)),CAST('' as VARCHAR(
SQL Source Length 548
Rows Returned 1
First Row Returned Time 2011/03/30 08:05:07.739827
Last Error before AQR 0
Number of AQR retries 0
Delay before AQR 0
No. of times reclaimed 0
Stats Collection Type OPERATOR_STATS
Accessed Rows 0
Used Rows 0
Message Count 0
Message Bytes 0
Stats Bytes 0
Disk IOs 0
Lock Waits 0
Lock Escalations 0
Disk Process Busy Time 0
SQL Process Busy Time 0
UDR Process Busy Time 0
SQL Space Allocated 32 KB
SQL Space Used 3 KB
SQL Heap Allocated 7 KB
SQL Heap Used 1 KB
EID Space Allocated 0 KB
EID Space Used 0 KB
EID Heap Allocated 0 KB
EID Heap Used 0 KB
Opens 0
Open Time 0
Processes Created 0
Process Create Time 0
Request Message Count 0
Request Message Bytes 0
Reply Message Count 0
Reply Message Bytes 0
Scr. Overflow Mode UNKNOWN
Scr. File Count 0
Scr. Buffer Blk Size 0
Scr. Buffer Blks Read 0
Scr. Buffer Blks Written 0
Scr. Read Count 0
Scr. Write Count 0
--- SQL operation complete.
```
<<<
* These GET STATISTICS commands return PERTABLE statistics for the first
active query in the specified process ID:
+
```
SQL> GET STATISTICS FOR PID 0,27195;
SQL> GET STATISTICS FOR PID $Z000F3R;
```
[[displaying_sql_runtime_statistics]]
== Displaying SQL Runtime Statistics
By default, GET STATISTICS displays table-wise statistics (PERTABLE). If
you want to view the statistics in a different format, use the
appropriate view option of the GET STATISTICS command.
RMS provides abbreviated statistics information for prepared statements
and full runtime statistics for executed statements.
The following table shows the RMS counters that are returned by GET
STATISTICS, tokens from the STATISTICS table-valued function that relate
to the RMS counters, and descriptions of the counters and tokens.
[cols="25%l,25%l,50%",options="header"]
|===
| Counter Name | Tokens in STATISTICS Table-Valued Function | Description
| Qid | Qid | A unique ID generated for each query. Each time a SQL statement is prepared, a new query ID is generated.
| Compile Start Time | CompStartTime | Time when the query compilation started or time when PREPARE for this query started.
| Compile End Time | CompEndTime | Time when the query compilation ended or time when PREPARE for this query ended.
| Compile Elapsed Time | CompElapsedTime | Amount of actual time to prepare the query.
| Execute Start Time | ExeStartTime | Time when query execution started.
| Execute End Time | ExeEndTime | Time when query execution ended. When a query is executing, Execute End Time is -1.
| Execute Elapsed Time | ExeElapsedTime | Amount of actual time used by the SQL executor to execute the query.
| State | State | Internally used.
| Rows Affected | RowsAffected | Represents the number of rows affected by the INSERT, UPDATE, or DELETE (IUD) SQL statements.
Value of -1 for SELECT statements or non-IUD SQL statements.
| SQL Error Code | SQLErrorCode | Top-level error code returned by the query, indicating whether the query completed with warnings, errors,
or successfully. A positive number indicates a warning. A negative number indicates an error. The value returned may not be accurate up to the point GET STATISTICS was executed.
| Stats Error Code | StatsErrorCode | Error code returned to the statistics collector while obtaining statistics from RMS. If an error code,
counter values may be incorrect. Reissue the GET STATISTICS command.
| Query Type | Estimated Accessed Rows | Type of DML statement and enum value: +
+
- SQL_SELECT_UNIQUE=1 +
- SQL_SELECT_NON_UNIQUE=2 +
- SQL_INSERT_UNIQUE=3 +
- SQL_INSERT_NON_UNIQUE=4 +
- SQL_UPDATE_UNIQUE=5 +
- SQL_UPDATE_NON_UNIQUE=6 +
- SQL_DELETE_UNIQUE=7 +
- SQL_DELETE_NON_UNIQUE=8 +
- SQL_CONTROL=9 +
- SQL_SET_TRANSACTION=10 +
- SQL_SET_CATALOG=11 +
- SQL_SET_SCHEMA=12 +
- SQL_CALL_NO_RESULT_SETS=13 +
- SQL_CALL_WITH_RESULT_SETS=14 +
- SQL_SP_RESULT_SET=15 +
- SQL_INSERT_ROWSET_SIDETREE=16 +
- SQL_CAT_UTIL=17 +
- SQL_EXE_UTIL=18 +
- SQL_OTHER=1 +
- SQL_UNKNOWN=0
| QueryType | EstRowsAccessed | Compiler's estimated number of rows accessed by the executor in TSE.
| Estimated Used Rows | EstRowsUsed | Compiler's estimated number of rows returned by the executor in TSE after applying the predicates.
| Parent Qid | parentQid | A unique ID for the parent query. If there is no parent query ID associated with the query, RMS returns NONE.
For more information, see <<using_the_parent_query_id,Using the Parent Query ID>>.
| Child Qid | childQid | A unique ID for the child query. If there is no child query, then there will be no child query ID and
RMS returns NONE. For more information, see <<child_query_id,Child Query ID>>.
| Number of SQL Processes | numSqlProcs | Represents the number of SQL processes (excluding TSE processes) involved in executing the query.
| Number of CPUs | numCpus | Represents the number of nodes that SQL is processing the query.
| Transaction ID | transId | Represents the transaction ID of the transaction involved in executing the query. When no transaction exists,
the Transaction ID is -1.
| Source String | sqlSrc | Contains the first 254 bytes of source string.
| SQL Source Length | sqlSrcLen | The actual length of the SQL source string.
| Rows Returned | rowsReturned | Represents the number of rows returned from the root operator at the master executor process.
| First Row Returned Time | firstRowReturnTime | Represents the actual time that the first row is returned by the master root operator.
| Last Error Before AQR | LastErrorBeforeAQR | The error code that triggered Automatic Query Retry (AQR) for the most recent retry. If the value is not 0,
this is the error code that triggered the most recent AQR.
| Number of AQR retries | AQRNumRetries | The number of retries for the current query until now.
| Delay before AQR | DelayBeforeAQR | Delay in seconds that SQL waited before initiating AQR.
| No. of times reclaimed | reclaimSpaceCnt | When a process is under virtual memory pressure, the execution space occupied by the queries executed much
earlier will be reclaimed to free up space for the upcoming queries. This counter represents how many times this particular query is reclaimed.
| | statsRowType | statsRowType can be one of the following: +
+
- SQLSTATS_DESC_OPER_STATS=0 +
- SQLSTATS_DESC_ROOT_OPER_STATS=1 +
- SQLSTATS_DESC_PERTABLE_STATS=11 +
- SQLSTATS_DESC_UDR_STATS=13 +
- SQLSTATS_DESC_MASTER_STATS=15 +
- SQLSTATS_DESC_RMS_STATS=16 +
- SQLSTATS_DESC_BMO_STATS=17
| Stats Collection Type | StatsType | Collection type, which is OPERATOR_STATS by default. StatsType can be one of the following: +
+
- SQLCLI_NO_STATS=0 +
- SQLCLI_ACCUMULATED_STATS=2 +
- SQLCLI_PERTABLE_STATS=3 +
- SQLCLI_OPERATOR_STATS=5
| Accessed Rows (Rows Accessed) | AccessedRows | Actual number of rows accessed by the executor in TSE.
| Used Rows (Rows Used) | UsedRows | Number of rows returned by TSE after applying the predicates. In a push down plan, TSE may not return all the used rows.
| Message Count | NumMessages | Count of the number of messages sent to TSE.
| Message Bytes | MessageBytes | Count of the message bytes exchanged with TSE.
| Stats Bytes | StatsBytes | Number of bytes returned for statistics counters from TSE.
| Disk IOs | DiskIOs | Number of physical disk reads for accessing the tables.
| Lock Waits | LockWaits | Number of times this statement had to wait on a conflicting lock.
| Lock Escalations | Escalations | Number of times row locks escalated to a file lock during the execution of this statement.
| Disk Process Busy Time | ProcessBusyTime | An approximation of the total node time in microseconds spent by TSE for executing the query.
| SQL Process Busy Time | CpuTime | An approximation of the total node time in microseconds spent in the master and ESPs involved in the query.
| UDR Process Busy Time (same as UDR CPU Time) | udrCpuTime | An approximation of the total node time in microseconds spent in the UDR server process.
| UDR Server ID | UDRServerId | MXUDR process ID.
| Recent Request Timestamp | | Actual timestamp of the recent request sent to MXUDR.
| Recent Reply Timestamp | | Actual timestamp of the recent request received by MXUDR.
| SQL Space Allocated^1^ | SpaceTotal^1^ | The amount of "space" type of memory in KB allocated in the master and ESPs involved in the query.
| SQL Space Used^1^ | SpaceUsed^1^ | Amount of "space" type of memory in KB used in master and ESPs involved in the query.
| SQL Heap Allocated^2^ | HeapTotal^2^ | Amount of "heap" type of memory in KB allocated in master and ESPs involved in the query.
| SQL Heap Used^2^ | HeapUsed^2^ | Amount of "heap" type of memory in KB used in master and ESPs involved in the query.
| EID Space Allocated^1^ | Dp2SpaceTotal | Amount of "space" type of memory in KB allocated in the executor in TSEs involved in the query.
| EID Space Used^1^ | Dp2SpaceUsed | Amount of "space" type of memory in KB used in the executor in TSEs involved in the query.
| EID Heap Allocated^2^ | Dp2HeapTotal | Amount of "heap" memory in KB allocated in the executor in TSEs involved in the query.
| EID Heap Used2 | Dp2HeapUsed | Amount of "heap" memory in KB used in the executor in TSEs involved in the query.
| Opens | Opens | Number of OPEN calls performed by the SQL executor on behalf of this statement.
| Open Time | OpenTime | Time (in microseconds) this process spent doing opens on behalf of this statement.
| Processes Created | Newprocess | The number of processes (ESPs and MXCMPs) created by the master executor for this statement.
| Process Create Time | NewprocessTime | The elapsed time taken to create these processes.
| Table Name | AnsiName | Name of a table in the query.
| Request Message Count | reqMsgCnt | Number of messages initiated from the master to ESPs or from the ESP to ESPs.
| Request Message Bytes | regMsgBytes | Number of message bytes that are sent from the master to ESPs or from the ESP to ESPs as part of the request messages.
| Reply Message Count | replyMsgCnt | Number of reply messages from the ESPs for the message requests.
| Reply Message Bytes | replyMsgBytes | Number of bytes sent as part of the reply messages.
| Scr. Overflow Mode | scrOverFlowMode | Represents the scratch overflow mode. Modes are DISK_TYPE or SSD_TYPE.
| Scr. File Count | scrFileCount | Number of scratch files created to execute the query. Default file size is 2 GB.
| Scr. Buffer Blk Size | scrBufferBlockSize | Size of buffer block that is used to read from/write to the scratch file.
| Scr. Buffer Blks Read | scrBufferRead | Number of scratch buffer blocks read from the scratch file.
| Scr. Buffer Blks Written | scrBufferWritten | Number of scratch buffer blocks written to the scratch file. Exact size of scratch file can be obtained
by multiplying Scr. Buffer Blk Size by this counter.
| Scr. Read Count | scrReadCount | Number of file-system calls involved in reading buffer blocks from scratch files. One call reads multiple
buffer blocks at once.
| Scr. Write Count | scrWriteCount | Number of file-system calls involved in writing buffer blocks to scratch files. One call writes multiple
buffer blocks at once.
| BMO Heap Used | bmoHeapUsed | Amount of "heap" type of memory in KB used in the BMO operator(s). The BMO operators are HASH_JOIN (and
all varieties of HASH_JOIN), HASH_GROUPBY (and all varieties of HASH_GROUPBY), and SORT (and all varieties of SORT).
| BMO Heap Total | bmoHeapTotal | Amount of "heap" type of memory in KB allocated in the BMO operator(s).
| BMO Heap High Watermark | bmoHeapWM | Maximum amount of memory used in the BMO operator.
| BMO Space Buffer Size | bmoSpaceBufferSize | Size in KB for space buffers allocated for the type of memory.
| BMO Space Buffer Count | bmoSpaceBufferCount | Count of space buffers allocated for the type of memory.
| Records Accessed (Estimated / Actual) | | Actual number of rows accessed by the executor in TSE.
| Records Used (Estimated / Actual) | | Number of rows returned by TSE after applying the predicates. In a push-down plan, TSE may not return all the used rows.
| ID | | TDB ID of the operator at the time of execution of the query.
| LCID | | Left child operator ID.
| RCID | | Right child operator ID.
| PaID | | Parent operator ID (TDB-ID).
| ExID | | Explain plan operator ID.
| Frag | | Fragment ID to which this operator belongs.
| Dispatches | | Number of times the operator is scheduled in SQL executor.
| Oper CPU Time | OperCpuTime | Approximation of the node time spent by the operator to execute the query.
| Est. Records Used | | Approximation of the number of tuples that would flow up to the parent operator.
| Act. Records Used | | Actual number of tuples that flowed up to the parent operator.
| | ProcessId | Name of the process ID (PID) in the format: $Znnn. The process name can be for the master (MXOSRVR) or executor
server process (ESP).
|===
1. Space is memory allocated from a pool owned by the executor. The executor
operators requesting the memory are not expected to return the memory until
the statement is deallocated.
2. Heap memory is used for temporary allocations. Operators may return heap memory before the statement is deallocated.
This allows the memory to be reused as needed.
<<<
[[examples_of_displaying_sql_runtime_statistics]]
=== Examples of Displaying SQL Runtime Statistics
NOTE: Some of the output has been reformatted for better document readability.
[[statistics_of_a_prepared_statement]]
==== Statistics of a Prepared Statement
* This example shows the output of the currently prepared statement:
+
```
SQL> GET STATISTICS FOR QID CURRENT;
Qid MXID1100000649721215837305997952000000001930000_4200_Q1
Compile Start Time 2010/12/06 10:55:40.931000
Compile End Time 2010/12/06 10:55:42.131845
Compile Elapsed Time 0:00:01.200845
ExecuteStart Time -1
Execute End Time -1
Execute Elapsed Time 0:00:00.000000
State CLOSE
Rows Affected -1
SQL Error Code 0
Stats Error Code 0
Query Type SQL_SELECT_NON_UNIQUE
Estimated Accessed Rows 100,010
Estimated Used Rows 100,010
Parent Qid NONE
Child Qid NONE
Number of SQL Processes 0
Number of Cpus 0
Execution Priority -1
Transaction Id -1
Source String select * from t100k where b in (select b from t10)
SQL Source Length 50
Rows Returned 0
First Row Returned Time -1
Last Error before AQR 0
Number of AQR retries 0
Delay before AQR 0
No. of times reclaimed 0
Stats Collection Type OPERATOR_STATS
--- SQL operation complete.
```
<<<
[[pertable_statistics_of_an_executing_statement]]
=== PERTABLE Statistics of an Executing Statement
* This example shows the PERTABLE statistics of an executing statement:
+
```
SQL> GET STATISTICS FOR QID CURRENT;
Qid MXID1100000649721215837305997952000000001930000_4200_Q1
Compile Start Time 2010/12/06 10:55:40.931000
Compile End Time 2010/12/06 10:55:42.131845
Compile Elapsed Time 0:00:01.200845
Execute Start Time 2010/12/06 10:56:16.254686
Execute End Time 2010/12/06 10:56:18.434873
Execute Elapsed Time 0:00:02.180187
State CLOSE
Rows Affected 0
SQL Error Code 100
Stats Error Code 0
Query Type SQL_SELECT_NON_UNIQUE
Estimated Accessed Rows 100,010
Estimated Used Rows 100,010
Parent Qid NONE
Child Qid NONE
Number of SQL Processes 7
Number of Cpus 1
Execution Priority -1
Transaction Id 18121
Source String select * from t100k where b in (select b from t10)
SQL Source Length 50
Rows Returned 100
First Row Returned Time 2010/12/06 10:56:18.150977
Last Error before AQR 0
Number of AQR retries 0
Delay before AQR 0
No. of times reclaimed 0
Stats Collection Type OPERATOR_STATS
SQL Process Busy Time 600,000
UDR Process Busy Time 0
SQL Space Allocated 1,576 KB
SQL Space Used 1,450 KB
SQL Heap Allocated 199 KB
SQL Heap Used 30 KB
EID Space Allocated 704 KB
EID Space Used 549 KB
EID Heap Allocated 582 KB
EID Heap Used 6 KB
Processes Created 4
Process Create Time 750,762
Request Message Count 701
Request Message Bytes 135,088
Reply Message Count 667
Reply Message Bytes 3,427,664
Scr. Overflow Mode DISK
Scr File Count 0
Scr. Buffer Blk Size 0
Scr. Buffer Blks Read 0
Scr. Buffer Blks Written 0
Table Name
Records Accessed Records Used Disk Message Message Lock Lock Disk Process Open Open
Estimated/Actual Estimated/Actual I/Os Count Bytes Escl wait Busy Time Count Time
NEO.SCTEST.T10
10 10
10 10 0 2 5,280 0 0 2,000 32 15,967
NEO.SCTEST.T100K
100,000 100,000
100,000 100,000 0 110 3,235,720 0 0 351,941 4 48,747
--- SQL operation complete.
```
<<<
[[accumulated_statistics_of_an_executing_statement]]
=== ACCUMULATED Statistics of an Executing Statement
* This example shows the ACCUMULATED statistics of an executing statement:
+
```
SQL> GET STATISTICS FOR QID CURRENT ACCUMULATED;
Qid MXID1100000649721215837305997952000000001930000_4200_Q1
Compile Start Time 2010/12/06 10:55:40.931000
Compile End Time 2010/12/06 10:55:42.131845
Compile Elapsed Time 0:00:01.200845
Execute Start Time 2010/12/06 10:56:16.254686
Execute End Time 2010/12/06 10:56:18.434873
Execute Elapsed Time 0:00:02.180187
State CLOSE
Rows Affected 0
SQL Error Code 100
Stats Error Code 0
Query Type SQL_SELECT_NON_UNIQUE
Estimated Accessed Rows 100,010
Estimated Used Rows 100,010
Parent Qid NONE
Child Qid NONE
Number of SQL Processes 7
Number of Cpus 1
Execution Priority -1
Transaction Id 18121
Source String select * from t100k where b in (select b from t10)
SQL Source Length 50
Rows Returned 100
First Row Returned Time 2010/12/06 10:56:18.150977
Last Error before AQR 0
Number of AQR retries 0
Delay before AQR 0
No. of times reclaimed 0
Stats Collection Type OPERATOR_STATS
Accessed Rows 100,010
Used Rows 100,010
Message Count 112
Message Bytes 3,241,000
Stats Bytes 2,904
Disk IOs 0
Lock Waits 0
Lock Escalations 0
Disk Process Busy Time 353,941
SQL Process Busy Time 600,000
UDR Process Busy Time 0
SQL Space Allocated 1,576 KB
SQL Space Used 1,450 KB
SQL Heap Allocated 199 KB
SQL Heap Used 30 KB
EID Space Allocated 704 KB
EID Space Used 549 KB
EID Heap Allocated 582 KB
EID Heap Used 6 KB
Opens 4
Open Time 48,747
Processes Created 4
Process Create Time 750,762
Request Message Count 701
Request Message Bytes 135,088
Reply Message Count 667
Reply Message Bytes 3,427,664
Scr. Overflow Mode DISK
Scr. File Count 0
Scr. Buffer Blk Size 0
Scr. Buffer Blks Read 0
Scr. Buffer Blks Written 0
--- SQL operation complete.
```
<<<
[[progress-statistics-of-an-executing-statement]]
=== PROGRESS Statistics of an Executing Statement
* This example shows the PROGRESS statistics of an executing statement:
+
```
SQL> GET STATISTICS FOR QID CURRENT PROGRESS;
Qid MXID1100000649721215837305997952000000001930000_4200_Q1
Compile Start Time 2010/12/06 10:55:40.931000
Compile End Time 2010/12/06 10:55:42.131845
Compile Elapsed Time 0:00:01.200845
Execute Start Time 2010/12/06 10:56:16.254686
Execute End Time 2010/12/06 10:56:18.434873
Execute Elapsed Time 0:00:02.180187
State CLOSE
Rows Affected 0
SQL Error Code 100
Stats Error Code 0
Query Type SQL_SELECT_NON_UNIQUE
Estimated Accessed Rows 100,010
Estimated Used Rows 100,010
Parent Qid NONE
Child Qid NONE
Number of SQL Processes 7
Number of Cpus 1
Execution Priority -1
Transaction Id 18121
Source String select * from t100k where b in (select b from t10)
SQL Source Length 50
Rows Returned 100
First Row Returned Time 2010/12/06 10:56:18.150977
Last Error before AQR 0
Number of AQR retries 0
Delay before AQR 0
No. of times reclaimed 0
Stats Collection Type OPERATOR_STATS
SQL Process Busy Time 600,000
SQL Space Allocated 1,576 KB
SQL Space Used 1,450 KB
SQL Heap Allocated 199 KB
SQL Heap Used 30 KB
EID Space Allocated 704 KB
EID Space Used 549 KB
EID Heap Allocated 582 KB
EID Heap Used 6 KB
Processes Created 4
Process Create Time 750,762
Request Message Count 701
Request Message Bytes 135,088
Reply Message Count 667
Reply Message Bytes 3,427,664
Table Name
Records Accessed Records Used Disk Message Message Lock Lock Disk Process Open Open
Estimated/Actual Estimated/Actual I/Os Count Bytes Escl wait Busy Time Count Time
NEO.SCTEST.T10
10 10
10 10 0 2 5,280 0 0 2,000 0 0
NEO.SCTEST.T100K
100,000 100,000
100,000 100,000 0 110 3,235,720 0 0 351,941 4 48,747
Id TDB Mode Phase Phase BMO BMO BMO BMO BMO File Scratch Buffer Cpu
Name Phase Start Heap Heap Heap Space Spacez Count Size/Read/Written Time
Time Used Total WM BufSz BufCnt
16 EX_HASHJ DISK 0 0 56 0 0 -1 0 0 60,000
```
<<<
[[default_statistics_of_an_executing_statement]]
=== DEFAULT Statistics of an Executing Statement
* This example shows the DEFAULT statistics of an executing statement:
+
```
SQL> GET STATISTICS FOR QID CURRENT DEFAULT;
Qid MXID1100000649721215837305997952000000001930000_4200_Q1
Compile Start Time 2010/12/06 10:55:40.931000
Compile End Time 2010/12/06 10:55:42.131845
Compile Elapsed Time 0:00:01.200845
Execute Start Time 2010/12/06 10:56:16.254686
Execute End Time 2010/12/06 10:56:18.434873
Execute Elapsed Time 0:00:02.180187
State CLOSE
Rows Affected 0
SQL Error Code 100
Stats Error Code 0
Query Type SQL_SELECT_NON_UNIQUE
Estimated Accessed Rows 100,010
Estimated Used Rows 100,010
Parent Qid NONE
Child Qid NONE
Number of SQL Processes 7
Number of Cpus 1
Execution Priority -1
Transaction Id 18121
Source String select * from t100k where b in (select b from t10)
SQL Source Length 50
Rows Returned 100
First Row Returned Time 2010/12/06 10:56:18.150977
Last Error before AQR 0
Number of AQR retries 0
Delay before AQR 0
No. of times reclaimed 0
Stats Collection Type OPERATOR_STATS
Id LCId RCId PaId ExId Frag TDB Name Dispatches Oper CPU Records Records
Time Est. Used Act. Used Details
21 20 . . 10 0 EX_ROOT 15 0 0 100
20 19 . 21 9 0 EX_SPLIT_TOP 13 0 100 100
19 18 . 20 9 0 EX_SEND_TOP 20 0 100 100
18 17 . 19 9 2 EX_SEND_BOTTOM 72 0 100 100
17 16 . 18 9 2 EX_SPLIT_BOTTOM 88 0 100 100
16 15 . 17 8 2 EX_HASHJ 1,314 60,000 100 100
15 14 . 16 7 2 EX_SPLIT_TOP 1,343 20,000 100,000 100,000
14 13 . 15 7 2 EX_SEND_TOP 1,342 120,000 100,000 100,000
13 12 . 14 7 5 EX_SEND_BOTTOM 1,534 200,000 100,000 100,000
12 11 . 13 7 5 EX_SPLIT_BOTTOM 493 70,000 100,000 100,000
11 10 . 12 6 5 EX_SPLIT_TOP 486 70,000 100,000 100,000
10 9 . 11 5 5 EX_PARTN_ACCESS 1,634 60,000 100,000 0
9 8 . 10 5 6 EX_EID_ROOT 12 0 100,000 100,000
8 7 . 9 4 6 EX_DP2_SUBS_OPER 160 170,000 100,000 10
7 6 . 8 3 2 EX_SPLIT_TOP 16 0 10 10
6 5 . 7 3 2 EX_SEND_TOP 17 0 10 10
5 4 . 6 3 3 EX_SEND_BOTTOM 17 0 10 10
4 3 . 5 3 3 EX_SPLIT_BOTTOM 9 0 10 10
3 2 . 4 2 3 EX_PARTN_ACCESS 6 0 10 10
2 1 . 3 2 4 EX_EID_ROOT 3 0 10 0
1 . . 1 1 4 EX_DP2_SUBS_OPER 3 100,000 10 10
--- SQL operation complete.
```
<<<
[[using_the_parent_query_id]]
=== Using the Parent Query ID
When executed, some SQL statements execute additional SQL statements,
resulting in a parent-child relationship. For example, when executed,
the UPDATE STATISTICS, MAINTAIN, and CALL statements execute other SQL
statements called child queries. The child queries might execute even
more child queries, thus introducing a hierarchy of SQL statements with
parent-child relationships. The parent query ID maps the child query to
the immediate parent SQL statement, helping you to trace the child SQL
statement back to the user-issued SQL statement.
The parent query ID is available as a counter, Parent Qid, in the
runtime statistics output. See Table 1-1 . A query directly
issued by a user will not have a parent query ID and the counter will
indicate "None."
[[child_query_id]]
=== Child Query ID
In many cases, a child query will execute in the same node as its
parent. In such cases, the GET STATISTICS report on the parent query ID
will contain a query ID value for the child query which executed most
recently. Conversely, if no child query exists, or the child query is
executing in a different node, no child query ID will be reported.
The following examples shows GET STATISTICS output for both the parent
and one child query which are executed when the user issues a CREATE
TABLE AS command:
<<<
```
SQL> -- get statistics for the parent query
SQL> GET STATISTICS FOR QID
+> MXID01001091200212164828759544076000000000217DEFAULT_MXCI_USER00_34SQLCI_DML_LAST
+> ;
Qid MXID11001091200212164828759544076000000000217DEFAULT_MXCI_USER00_34SQLCI_DML_LAST
Compile Start Time 2011/02/18 14:49:04.606513
Compile End Time 2011/02/18 14:49:04.631802
Compile Elapsed Time 0:00:00.025289
Execute Start Time 2011/02/18 14:49:04.632142
Execute End Time -1
Execute Elapsed Time 0:03:29.473604
State CLOSE
Rows Affected -1
SQL Error Code 0
Stats Error Code 0
Query Type SQL_INSERT_NON_UNIQUE
Estimated Accessed Rows 0
Estimated Used Rows 0
Parent Qid NONE
Child Qid MXID11001091200212164828759544076000000000217DEFAULT_MXCI_USER00_37_86
Number of SQL Processes 1
Number of Cpus 1
Execution Priority 148
Transaction Id -1
Source String create table odetail hash partition by (ordernum, partnum)
as select * from SALES.ODETAIL;
SQL Source Length 91
Rows Returned 0
First Row Returned Time -1
Last Error before AQR 0
Number of AQR retries 0
Delay before AQR 0
No. of times reclaimed 0
Stats Collection Type OPERATOR_STATS
Id LCId RCId PaId ExId Frag TDB Name Dispatches Oper CPU Records Records
Time Est. Used Act. Used Details
2 1 . . 2 0 EX_ROOT 0 0 0 0
1 . . 2 1 0 CREATE_TABLE_AS 0 0 0 0
--- SQL operation complete.
```
<<<
```
SQL> -- get statistics for the child query
SQL> GET STATISTICS FOR QID
+> MXID11001091200212164828759544076000000000217DEFAULT_MXCI_USER00_37_86
+> ;
Qid MXID01001091200212164828759544076000000000217DEFAULT_MXCI_USER00_37_86
Compile Start Time 2011/02/18 14:49:07.632898
Compile End Time 2011/02/18 14:49:07.987334
Compile Elapsed Time 0:00:00.354436
Execute Start Time 2011/02/18 14:49:07.987539
Execute End Time -1
Execute Elapsed Time 0:02:33.173486
State OPEN
Rows Affected -1
SQL Error Code 0
Stats Error Code 0
Query Type SQL_INSERT_NON_UNIQUE
Estimated Accessed Rows 101
Estimated Used Rows 101
Parent Qid MXID101001091200212164828759544076000000000217DEFAULT_MXCI_USER00_34SQLCI_DML_LAST
Child Qid NONE
Number of SQL Processes 1
Number of Cpus 1
Execution Priority 148
Transaction Id \ARC0101(2).9.9114503
Source String insert using sideinserts into CAT.SCH.ODETAIL select * from SALES.ODETAIL;
SQL Source Length 75
Rows Returned 0
First Row Returned Time -1
Last Error before AQR 0
Number of AQR retries 0
Delay before AQR 0
No. of times reclaimed 0
Stats Collection Type OPERATOR_STATS
Id LCId RCId PaId ExId Frag TDB Name Dispatches Oper CPU Records Records
Time Est. Used Act. Used Details
4 3 . 9 3 0 EX_SPLIT_TOP 1 10,062 100 0
3 2 . 4 2 0 EX_PARTN_ACCESS 66 9,649 100 0
--- SQL operation complete.
```
<<<
== Gathering Statistics About RMS
Use the GET STATISTICS FOR RMS command to get information about RMS
itself. The GET STATISTICS FOR RMS statement can be used to retrieve
information about one node or all nodes. An individual report is
provided for each node.
[cols="30%l,70%",options="header"]
|===
| Counter | Description
| CPU | The node number of the {project-name} cluster.
| RMS Version | Internal version of RMS.
| SSCP PID | SQL Statistics control process ID.
| SSCP Creation Timestamp | Actual timestamp when SQL statistics control process was created.
| SSMP PID | SQL statistics merge process ID.
| SSMP Creation Timestamp | Timestamp when SQL statistics merge was created.
| Source String Store Len | Storage length of source string.
| Stats Heap Allocated | Amount of memory allocated by all the queries executing in the given node in the RMS shared segments at this instance of time.
| Stats Heap Used | Amount of memory used by all the queries executing in the given node in the RMS shared segment at this instance of time.
| Stats Heap High WM | High amount of memory used by all the queries executing in the given node in the RMS shared segment until now.
| No. of Process Regd. | Number of processes registered in the shared segment.
| No. of Query Fragments Regd. | Number of query fragments registered in the shared segment.
| RMS Semaphore Owner | Process ID that locked the semaphore at this instance of time.
| No. of SSCPs Opened | Number of Statistics Control Processes opened. Normally, this should be equal to the number of nodes in the {project-name} cluster.
| No. of SSCPs Open Deleted | Number of Statistics Control Processes with broken communication. Usually, this should be 0.
| Last GC Time | The recent timestamp at which the shared segment was garbage collected.
| Queries GCed in Last Run | Number of queries that were garbage collected in the recent GC run.
| Total Queries GCed | Total number of queries that were garbage collected since the statistics reset timestamp.
| SSMP Request Message Count | Count of the number of messages sent from the SSMP process since the statistics reset timestamp.
| SSMP Request Message Bytes | Number of messages bytes that are sent as part of the request from the SSMP process since the statistics reset timestamp.
| SSMP Reply Message Count | Count of the number of reply messages received by the SSMP process since the statistics reset timestamp.
| SSMP Reply Message Bytes | Number of messages bytes that are sent as part of the reply messages received by the SSMP process since the statistics reset timestamp.
| SSCP Request Message Count | Count of the number of messages sent from the SSCP process since the statistics reset timestamp.
| SSCP Request Message Bytes | Number of messages bytes are sent as part of the request from the SSCP process since the statistics reset timestamp.
| SSCP Reply Message Count | Count of the number of reply messages received by the SSCP process since the statistics reset timestamp.
| SSCP Reply Message Bytes | Number of messages bytes that are sent as part of the reply messages received by the SSCP process since the statistics reset timestamp.
| RMS Stats Reset Timestamp | Timestamp for resetting RMS statistics.
|===
```
SQL> GET STATISTICS FOR RMS ALL;
Node name
CPU 0
RMS Version 2511
SSCP PID 19521
SSCP Priority 0
SSCP Creation Timestamp 2010/12/05 02:32:33.642752
SSMP PID 19527
SSMP Priority 0
SSMP Creation Timestamp 2010/12/05 02:32:33.893440
Source String Store Len 254
Stats Heap Allocated 0
Stats Heap Used 3,002,416
Stats Heap High WM 3,298,976
No.of Process Regd. 157
No.of Query Fragments Regd. 296 RMS Semaphore Owner -1
No.of SSCPs Opened 1
No.of SSCPs Open Deleted 0
Last GC Time 2010/12/06 10:53:46.777432
Queries GCed in Last Run 55
Total Queries GCed 167
SSMP Request Message Count 58,071
SSMP Request Message Bytes 14,161,144
SSMP Reply Message Count 33,466
SSMP Reply Message Bytes 15,400,424
SSCP Request Message Count 3,737
SSCP Request Message Bytes 837,744
SSCP Reply Message Count 3,736 SSCP
Reply Message Bytes 5,015,176
RMS Stats Reset Timestamp 2010/12/05 14:32:33.891083
--- SQL operation complete.
```
<<<
[[using_the_queryid_extract_function]]
== Using the QUERYID_EXTRACT Function
Use the QUERYID_EXTRACT function within an SQL statement to extract
components of a query ID for use in a SQL query. The query ID, or QID,
is a unique, cluster-wide identifier for a query and
is generated for dynamic SQL statements whenever a SQL string is
prepared.
=== Syntax of QUERYID_EXTRACT
```
QUERYID_EXTRACT ('query-id', 'attribute')
```
The syntax of the QUERYID_EXTRACT function is:
* `_query-id_`
+
is the query ID in string format.
* `_attribute_`
+
is the attribute to be extracted. The value of _attribute_ can be one of
these parts of the query ID:
+
[cols="30%l,70%",options="header"]
|===
| Attribute Value | Description
| SEGMENTNUM | Logical node ID in {project-name} cluster
| CPUNUM or CPU | Logical node ID in {project-name} cluster
| PIN | Linux process ID number
| EXESTARTTIME | Executor start time
| SESSIONNUM | Session number
| USERNAME | User name
| SESSIONNAME | Session name
| SESSIONID | Session ID
| QUERYNUM | Query number
| STMTNAME | Statement ID or handle
|===
+
NOTE: The SEGMENTNUM and CPUNUM attributes are the same.
The result data type of the QUERYID_EXTRACT function is a VARCHAR with a
length sufficient to hold the result. All values are returned in string
format. Here is the QUERYID_EXTRACT function in a SELECT statement:
```
SELECT QUERYID_EXTRACT('_query-id_', '_attribute-value_') FROM (VALUES(1)) AS t1;
```
<<<
[[examples_of_queryid_extract]]
=== Examples of QUERYID_EXTRACT
* This command returns the node number of the query ID:
+
```
SQL> SELECT
+> SUBSTR(
+> QUERYID_EXTRACT(
+> 'MXID11000022675212170554548762240000000000206U6553500_21_S1','CPU'
+> ), 1, 20
+> ) FROM (VALUES(1))
+> AS t1;
(EXPR)
---------------------------------------------------------------------------
0
--- 1 row(s) selected.
```
* This command returns the PIN of the query ID:
+
```
SQL> SELECT
+> SUBSTR(
+> QUERYID_EXTRACT(
+> 'MXID11000022675212170554548762240000000000206U6553500_21_S1','PIN'
+> ), 1, 20
+> ) FROM (VALUES(1)) AS t1;
(EXPR)
---------------------------------------------------------------------------
22675
--- 1 row(s) selected.
```
<<<
[[stats_each_fragment_instance_active_query]]
== Statistics for Each Fragment-Instance of an Active Query
You can retrieve statistics for a query while it executes by using the
STATISTICS table-valued function. Depending on the syntax used, you can
obtain statistics summarizing each parallel fragment-instance of the
query, or for any operator in each fragment-instance.
[[syntax_of_statistics_table-valued_function]]
=== Syntax of STATISTICS Table-Valued Function
```
TABLE(STATISTICS (NULL, 'qid-str'))
qid-str is:
QID=query-id [ ,{ TDBID_DETAIL=tdb-id | DETAIL=1 } ]
```
* `_query-id_`
+
is the system-generated query ID. For example:
+
```
QID=MXID11000022675212170554548762240000000000206U6553500_21_S1
```
* `_tdb-id_`
+
is the TDB ID of a given operator. TDB values can be obtained from the
report returned from the GET STATISTICS command.
[[considerations_obtaining_stats_fragment]]
=== Considerations For Obtaining Statistics For Each Fragment-Instance of an Active Query
If the DETAIL=1 or TDBID_DETAIL=_tdb_id_ options are used when the
query is not executing, the STATISTICS table-valued function will not
return any results.
The STATISTICS table-valued function can be used with a SELECT statement
to return several columns. Many different counters exist in the
_variable_info_ column. The counters in this column are formatted as
token-value pairs and the counters reported will depend on which option
is used: DETAIL=1 or TDBID_DETAIL=_tdb_id_. If the TDBID_DETAIL option
is used, the counters reported will also depend on the type of operator
specified by the _tdb_id_. The reported counters can also be
determined by the statsRowType counter.
The tokens for these counters are listed in the column
<<displaying_sql_runtime_statistics>>,Displaying SQL Runtime Statistics>>.
* This query lists process names of all ESPs of an executing query
identified by the given QID:
+
```
SQL> SELECT
+> SUBSTR(VARIABLE_INFO,
+> POSITION('ProcessId:' IN variable_info), 20) AS processes
+>FROM
+>TABLE(statistics(NULL,
+>'QID=MXID11000032684212170811581160672000000000206U6553500_19_S1,DETAIL=1'))
+>GROUP BY 1;
PROCESSES
--------------------
ProcessId: $Z0000GS
ProcessId: $Z0000GT
ProcessId: $Z0000GU
ProcessId: $Z0000GV
ProcessId: $Z0102IQ
ProcessId: $Z000RNU
ProcessId: $Z0102IR
ProcessId: $Z0102IS
ProcessId: $Z0102IT
--- 9 row(s) selected.
```
<<<
* This query gives BMO heap used for the hash join identified as TDB #15
in an executing query identified by the given QID:
+
```
SQL>SELECT CAST (
+> SUBSTR(variable_info,
+> POSITION('bmoHeapUsed:' IN variable_info),
+> POSITION('bmoHeapUsed:' in variable_info) +
+> 13 + (POSITION(' ' IN
+> SUBSTR(variable_info,
+> 13 + POSITION('bmoHeapUsed:' IN variable_info))) -
+> POSITION('bmoHeapUsed:' IN variable_info)))
+> AS CHAR(25))
+> FROM TABLE(statistics(NULL,
+>'QID=MXID11000021706212170733911504160000000000206U6553500_25_S1,TDBID_DETAIL=15'));
(EXPR)
-------------------------
bmoHeapUsed: 3147
bmoHeapUsed: 3147
bmoHeapUsed: 3147
bmoHeapUsed: 3147
bmoHeapUsed: 3147
bmoHeapUsed: 3147
bmoHeapUsed: 3147
bmoHeapUsed: 3147
--- 8 row(s) selected.
```