//// | |
/** | |
* @@@ 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. | |
``` |