10.11.3 Examples of output of and output items for access path statistical information

This subsection shows examples of the output and explains the output items for access path statistical information.

Access path statistical information is output as part of SQL trace information when executed SQL statements terminate. Access path statistical information might not be output for SQL statements that result in an error.

You can check the SQL trace information against the access path information by using as a search key the corresponding access path's tree row number that is output as access path statistical information. For details about access path information, see (5) Executed SQL statement and access path information in 10.11.2 Information that is output as SQL trace information.

Note

Access path statistical information is output in the following cases:

The call type is CLOS (including when the statement handle was released or the transaction was concluded without closing the cursor).
The call type is EXEC, EXDI, or SQL.

Organization of this subsection

(1) Example of output of access path statistical information
(2) Items that are output as access path statistical information
(3) Notes about access path statistical information

(1) Example of output of access path statistical information

The following shows an example of access path statistical information that is output. The example is in two parts because it consists of a large amount of data.

■ Example of output of access path statistical information (part 1)

[Figure]

■ Example of output of access path statistical information (part 2)

[Figure]

To Page Top

(2) Items that are output as access path statistical information

The following table lists the items that are output as access path statistical information and the types of access paths for which the items are output.

Table 10‒20: List of items that are output as access path statistical information
No.	Output item	Type of access path subject to output operation		Details of information that is output
1	Data access information	Work table creation information	`CREATE` `GLOBAL` `WORK` `TABLE`	See (a) Data access information.
2		Work table creation information	`CREATE` `LOCAL` `WORK` `TABLE`
3		Table retrieval method information	`TABLE` `SCAN`
4			`KEY` `SCAN`
5			`INDEX` `SCAN`
6		Work table retrieval information	`WORK` `TABLE` `SCAN`
7		Derived table information	`DERIVED` `TABLE`
8		Table-function derived table information	`TABLE` `FUNCTION` `DERIVED` `TABLE`
9		Join method information	`HASH` `JOIN`
10		Join method information	`NESTED` `LOOP` `JOIN`
11		Subquery type information	`SUBQUERY` `HASH`
12		Grouping set information	`GROUPING SET`
13	Information related to retrieval processing^{#1, #2, #3}	Subquery type information	`SUBQUERY` `HASH`	See (b) Information related to retrieval processing.
14		Derived table information	`DERIVED` `TABLE`
15		Table-function derived table information	`TABLE` `FUNCTION` `DERIVED` `TABLE`
16		Table value constructor retrieval information	`TABLE VALUE CONSTRUCTOR SCAN`
17		Grouping processing method information	`GLOBAL` `HASH` `GROUPING`
18		Duplication elimination method information	`GLOBAL` `HASH` `UNIQUE`
19		Table retrieval method information	`TABLE` `SCAN`
20			`KEY` `SCAN`
21			`INDEX` `SCAN`
22		Work table retrieval information	`WORK` `TABLE` `SCAN`
23		Join method information	`NESTED` `LOOP` `JOIN`
24		Join method information	`HASH` `JOIN`
25	Information related to set operations^#4	Set operation type information	`SET` `OPERATION`	See (c) Information related to set operations.
26	Information related to hash grouping areas^#5	Grouping processing method information	`LOCAL` `HASH` `GROUPING`	See (d) Information related to hash grouping areas.
27	Information related to hash table areas	Duplication elimination method information	`GLOBAL` `HASH` `UNIQUE`	See (e) Information related to hash table areas.
28		Subquery type information	`SUBQUERY` `HASH`
29		Grouping processing method information	`GLOBAL` `HASH` `GROUPING`
30		Join method information	`HASH` `JOIN`
31	Information related to subqueries containing external reference columns^#6,#7	Subquery type information	`SUBQUERY` `LOOP`	See (f) Information related to subqueries containing external reference columns.
32	Information related to caches for storing subquery requests	Subquery cache information	`USING` `CACHE`	See (g) Information related to caches for storing subquery requests.
33	Information related to recursive queries	Recursive query information	`RECURSIVE`	See (h) Information related to recursive queries.
34	Information related to execution of SQL statements	--	--	See (i) Information related to execution of SQL statements.

Legend:

--: When an SQL statement subject to output of access path statistical information is executed, the information is output regardless of the type of access path.

#1

If the retrieval processing is performed within a subquery containing an external reference column whose processing method is nested loops work table execution or nested loops row value execution, the target access path is not output as part of the access path statistical information.

However, the access paths in the following processing that is executed in a subquery that includes an external reference column are output to the access path statistical information:

Retrieval performed in a subquery that does not include an external reference column

#2

If the retrieval processing is performed on a work table that contains the results of a subquery whose processing method is work table execution, work table row value execution, or nested loops work table execution, the target access path is not output as part of the access path statistical information.

#3

If the retrieval processing is performed on an outer table or an inner table for nested loop join during table join processing to which nested loop join is applied, the target access path is not output as part of the access path statistical information. However, when the outermost table for nested loop join is a derived table, if the retrieval processing is performed for the derived query, the target access path is output as part of the access path statistical information.

#4

If the set operation is performed within a subquery containing an external reference column whose processing method is nested loops work table execution or nested loops row value execution, the target access path is not output as part of the access path statistical information.

However, the access paths in the following processing that is executed in a subquery that includes an external reference column are output to the access path statistical information:

Set operation performed in a subquery that does not include an external reference column

#5

If local hash grouping is performed within a subquery containing an external reference column whose processing method is nested loops work table execution or nested loops row value execution, the target access path is not output as part of the access path statistical information.

However, the access paths in the following processing that is executed in a subquery that includes an external reference column are output to the access path statistical information:

Local hash grouping performed in a subquery that does not include an external reference column

#6

If there are multiple incidences of SUBQUERY LOOP in the part where subquery results are evaluated, this information is output on the first line.

#7

If SUBQUERY LOOP is nested in the tree view of access path information, access path statistical information might not be output for the nested SUBQUERY LOOP.

(a) Data access information

The table below explains the data access information that is output as access path statistical information.

In the case of access path statistical information related to update SQL statements, only information about retrieving the data to be updated or deleted is output for the tables subject to update processing. Information about adding, updating, and deleting data is not output.

Multiple lines of data access information might be output for an access path. For details, see Table 10‒22: Number of lines and information that are output as data access information for each access path.

Table 10‒21: Items that are output as access path statistical information (Data access information)
No.	Column header	Information that is output	Output format	In the event of overflow
1	`Data_path#`	Corresponding access path's tree row number	`UINT8`	--
2	`Data_dbbuff_page_request_cnt(table)`	Page request count of global buffers that are used when base tables are accessed	`UINT8`	Maximum value
3	`Data_dbbuff_page_hit_cnt(table)`	Hit count of global buffers that are used when base tables are accessed	`UINT8`	Maximum value
4	`Data_dbbuff_page_read_cnt(table)`	Number of read operations from files to global buffers that are used when base tables are accessed	`UINT8`	Maximum value
5	`Data_dbbuff_page_write_cnt(table)`	Number of write operations to files from global buffers that are used when base tables are accessed	`UINT8`	Maximum value
6	`Data_dbbuff_tblscan_request_cnt`	Page request count of table scan buffers that are used when base tables are accessed	`UINT8`	Maximum value
7	`Data_dbbuff_tblscan_hit_cnt`	Page hit count of table scan buffers that are used when base tables are accessed	`UINT8`	Maximum value
8	`Data_dbbuff_tblscan_read_cnt`	Number of read operations from files to table scan buffer blocks that are used when base tables are accessed	`UINT8`	Maximum value
9	`Data_dbbuff_tblscan_read_size`	Size of data read from files to the table scan buffer that is used when base tables are accessed (kilobytes)	`UINT8`	Maximum value
10	`Data_dbbuff_tblscan_failed_cnt`	Allocation failure count of table scan buffer blocks that are used when base tables are accessed When allocation fails, the global buffer is used.	`UINT8`	Maximum value
11	`Data_dbbuff_page_request_cnt(btree)`	Page request count of global buffers that are used when B-tree indexes are accessed	`UINT8`	Maximum value
12	`Data_dbbuff_page_hit_cnt(btree)`	Hit count of global buffers that are used when B-tree indexes are accessed	`UINT8`	Maximum value
13	`Data_dbbuff_page_read_cnt(btree)`	Number of read operations from files to global buffers that are used when B-tree indexes are accessed	`UINT8`	Maximum value
14	`Data_dbbuff_page_write_cnt(btree)`	Number of write operations to files from global buffers that are used when B-tree indexes are accessed	`UINT8`	Maximum value
15	`Data_dbbuff_page_request_cnt(text)`	Page request count of global buffers that are used when text indexes are accessed	`UINT8`	Maximum value
16	`Data_dbbuff_page_hit_cnt(text)`	Hit count of global buffers that are used when text indexes are accessed	`UINT8`	Maximum value
17	`Data_dbbuff_page_read_cnt(text)`	Number of read operations from files to global buffers that are used when text indexes are accessed	`UINT8`	Maximum value
18	`Data_dbbuff_page_write_cnt(text)`	Number of write operations to files from global buffers that are used when text indexes are accessed	`UINT8`	Maximum value
19	`Data_dbbuff_page_request_cnt(range)`	Page request count of global buffers that are used when range indexes are accessed	`UINT8`	Maximum value
20	`Data_dbbuff_page_hit_cnt(range)`	Hit count of global buffers that are used when range indexes are accessed	`UINT8`	Maximum value
21	`Data_dbbuff_page_read_cnt(range)`	Number of read operations from files to global buffers that are used when range indexes are accessed	`UINT8`	Maximum value
22	`Data_dbbuff_page_write_cnt(range)`	Number of write operations to files from global buffers that are used when range indexes are accessed	`UINT8`	Maximum value
23	`Data_dbbuff_wrktbl_clt_tbl_cnt`	Number of times buffers for local work tables were created	`UINT8`	Maximum value
24	`Data_dbbuff_wrktbl_clt_request_cnt`	Page request count of buffers for local work tables	`UINT8`	Maximum value
25	`Data_dbbuff_wrktbl_clt_page_hit_cnt`	Hit count of buffers for local work tables	`UINT8`	Maximum value
26	`Data_dbbuff_wrktbl_clt_read_cnt`	Number of read operations from files to buffers for local work tables	`UINT8`	Maximum value
27	`Data_dbbuff_wrktbl_clt_write_cnt`	Number of write operations to files from buffers for local work tables	`UINT8`	Maximum value
28	`Data_dbbuff_page_request_cnt(glb_wrk)`	Page request count of global buffers for global work tables	`UINT8`	Maximum value
29	`Data_dbbuff_page_hit_cnt(glb_wrk)`	Hit count of global buffers for global work tables	`UINT8`	Maximum value
30	`Data_dbbuff_page_read_cnt(glb_wrk)`	Number of read operations from files to global buffers for global work tables	`UINT8`	Maximum value
31	`Data_dbbuff_page_write_cnt(glb_wrk)`	Number of write operations to files from global buffers for global work tables	`UINT8`	Maximum value
32	`Data_bidx_page_split_cnt`	Value used by the system This is always `0`.	`UINT8`	Maximum value
33	`Data_bidx_validation_check_cnt`	Number of times whether rows were valid was checked during retrieval using B-tree indexes	`UINT8`	Maximum value
34	`Data_ridx_chunk_judge_cnt`	Number of times table chunks were checked during retrieval using range indexes	`UINT8`	Maximum value
35	`Data_ridx_chunk_skip_cnt`	Number of times table chunks were skipped during retrieval using range indexes	`UINT8`	Maximum value
36	`Data_ridx_sgmt_judge_cnt`	Number of times table segments were checked during retrieval using range indexes	`UINT8`	Maximum value
37	`Data_ridx_sgmt_skip_cnt`	Number of times table segments were skipped during retrieval using range indexes	`UINT8`	Maximum value
38	`Data_tidx_page_split_cnt`	Value used by the system This is always `0`.	`UINT8`	Maximum value
39	`Data_wrktbl_create_cnt`	Number of times work tables were created	`UINT8`	Maximum value
40	`Data_wrktbl_drop_cnt`	Number of times work tables were deleted	`UINT8`	Maximum value
41	`Data_wrktbl_page_assign_num`	Number of work table pages allocated	`UINT8`	Maximum value
42	`Data_wrktbl_page_free_num`	Number of work table pages freed	`UINT8`	Maximum value
43	`Data_wrktbl_page_use_max`	Number of pages in the largest work table that was allocated	`UINT8`	Maximum value
44	`Data_wrktbl_sort_merge_cnt`	Maximum number of sorting stages in sorting of the work table	`UINT8`	Maximum value
45	`Data_wrktbl_sort_page_max_cnt`	Number of pages allocated to the largest work table for sorting work tables	`UINT8`	Maximum value
46	`Data_dbarea_extension_cnt`	Value used by the system This is always `0`.	`UINT8`	Maximum value
47	`Data_segmentrel_rthd_max_num`	Value used by the system This is always `0`.	`UINT8`	Maximum value
48	`Data_log_usrfile_write_cnt`	Write count of user log files	`UINT8`	Maximum value
49	`Data_log_usrbuf_out_cnt`	Flush count resulting from full user log buffers	`UINT8`	Maximum value
50	`Data_log_usrfile_max_size`	Largest size of user log files used	`UINT8`	Maximum value
51	`Data_directory_send_num`	Value used by the system This is always `0`.	`UINT8`	Maximum value
52	`Data_access_info_type`	Type of data access `Access` Indicates data access information when schema objects subject to access paths are accessed. `Create_hash_wrktbl` Indicates data access information when work tables are created due to a shortage of hash table areas. `Scan_hash_wrktbl` Indicates data access information when work tables created due to a shortage of hash table areas are retrieved. `Create_split_hash_wrktbl` Indicates data access information when a work table^# is newly created due to a shortage of hash table areas. `Scan_split_hash_wrktbl` Indicates data access information when a work table^# newly created due to a shortage of hash table areas is retrieved. `Rowid_fetch` Indicates data access information when retrieval processing is performed to fetch data from data pages by using row IDs. `Create_grpset_wrktbl` Indicates that the data access information pertains to creation of a work table that is used when grouping is performed multiple times. `Scan_grpset_wrktbl` Indicates that the data access information pertains to retrieval of a work table that is used when grouping is performed multiple times. For the relationship between data access types and access paths, see Table 10‒22: Number of lines and information that are output as data access information for each access path.	`CHAR`	--
53	`Data_csvread_file_cnt`	Number of files opened by using the `ADB_CSVREAD` function These files include those opened by using the `ADB_CSVREAD` function, which is generated by equivalent exchange of the SQL statement used to access the archivable multi-chunk table.	`UINT8`	Maximum value
54	`Data_csvread_file_read_size`	Total size of data read from files by using the `ADB_CSVREAD` function (bytes) This data size includes the size of data read from files by using the `ADB_CSVREAD` function that is generated by equivalent exchange of the archivable multi-chunk table.	`UINT8`	Maximum value
55	`Data_deleted_rows_cnt`	Number of rows that are determined to be invalid rows during retrieval For details about invalid rows, see the following sections: 11.1.9 Checking whether a single-chunk table needs to be reorganized 11.4.13 Checking whether a multi-chunk table needs to be reorganized 11.17.1 Reason for reorganizing a system table	`UINT8`	Maximum value
56	`Data_tidx_all_search_cnt`	Number of times when the entire data in a chunk is retrieved by using text index	`UINT8`	Maximum value
57	`Data_auditread_file_cnt`	Number of files opened by using the `ADB_AUDITREAD` function	`UINT8`	Maximum value
58	`Data_auditread_file_read_size`	Total size of data read from files by using the `ADB_AUDITREAD` function (bytes)	`UINT8`	Maximum value
59	`Data_cs_column_invalid_rows_cnt`	Number of rows that are determined to be invalid rows during retrieval for column-data segments in a column store table	`UINT8`	Maximum value
60	`Data_cs_rowstore_fetch_rows_cnt`	Number of rows found when data in row store format (rows added by using the `INSERT` or `UPDATE` statement) is accessed during retrieval in a column store table	`UINT8`	Maximum value
61	`Data_cs_invalid_info_page_request_cnt`	Number of page requests for invalid row information pages in a column store table	`UINT8`	Maximum value

Legend:

UINT8: Information is output as an 8-byte unsigned integer. If the target access path was not executed or was being executed, 0 or an intermediate value might be output.

CHAR: The character string indicated in Information that is output is output. If the target access path was not executed or was being executed, blanks might be output.

Maximum value: In the event of an overflow, 18,446,744,073,709,551,615 is output.

--: Overflow does not occur. Otherwise, overflow is not applicable.

Note

The output items that are not listed in the table are all maintenance information.

#

If a shortage of hash table areas occurs while a hash table is created, multiple work tables are created, and data to be stored in the hash table are divided and stored into individual work tables. If another shortage of hash table areas occurs while hash tables for individual work tables are created, a new work table is created. The work table (with hash mark) indicates this type of work table.

The following table shows the number of lines and the information that are output as data access information for each access path.

Table 10‒22: Number of lines and information that are output as data access information for each access path
No.	Type of access path	Number of lines and information that are output as data access information	Information that is output as Data_access_info_type
1	`CREATE` `GLOBAL` `WORK` `TABLE`	Number of lines output One line of information is output for the access path. Output information Data access information when creating a global work table (including sort processing) is output. This includes the information when creating a local work table at the stage prior to creation of the global work table.	`Access`
2	`CREATE` `LOCAL` `WORK` `TABLE`	Number of lines output One line of information is output for the access path. Output information Data access information when creating a local work table (including sort processing) is output.	`Access`
3	`TABLE` `SCAN`	Output information (consisting of one line) Normally, one line of information is output for the access path. The data access information when retrieving target base tables, work tables, or table-function derived tables is output. Output information (consisting of three lines) If the access path is one of the following, three lines are output: Inner table of hash join Target of the outer query of a subquery to which hash execution is applied The first line displays the data access information when retrieving a target base table, work table, or table-function derived table. The second line displays the data access information when a work table was created due to a shortage of hash table areas. The third line displays the data access information when a work table was retrieved due to a shortage of hash table areas. Output information (consisting of five lines) If the access path is one of the following, five lines are output: Outer table of hash join Target of a subquery to which hash execution is applied Target of global hash grouping Target of `SELECT DISTINCT` to which hash execution is applied The first line displays the data access information when retrieving a target base table, work table, or table-function derived table. The second line displays the data access information when a work table was created due to a shortage of hash table areas. The third line displays the data access information when a work table was retrieved due to a shortage of hash table areas. The fourth line displays the data access information when the work table was created due to a shortage of hash table areas after data in a hash table was divided into work tables. The fifth line displays the data access information when the work table was retrieved due to a shortage of hash table areas after data in a hash table was divided into work tables.	When one line is output `Access` When three lines are output `Access` (Line 1) `Create_hash_wrktbl` (Line 2) `Scan_hash_wrktbl` (Line 3) When five lines are output `Access` (Line 1) `Create_hash_wrktbl` (Line 2) `Scan_hash_wrktbl` (Line 3) `Create_split_hash_wrktbl` (Line 4) `Scan_split_hash_wrktbl` (Line 5)
4	`INDEX` `SCAN`
5	`KEY` `SCAN`
6	`WORK` `TABLE` `SCAN`	Output information (consisting of one line) Normally, one line of information is output for the access path. The data access information when retrieving target work tables is output. Output information (consisting of two lines) If the following applies to the access path, two lines are output: Storing the row IDs of a table specified in the `FROM` clause for a work table created by an SQL statement with the `ORDER` `BY` clause specified The first line displays the data access information when retrieving from the target work table. The second line displays the data access information when retrieval processing was performed to fetch data from data pages by using row IDs. If data is retrieved from multiple tables, the data access information is the sum of the values when multiple tables are accessed. Output information (consisting of three lines) If the access path is one of the following, three lines are output: Inner table of hash join Target of the outer query of a subquery to which hash execution is applied The first line displays the data access information when retrieving from the target work table. The second line displays the data access information when a work table was created due to a shortage of hash table areas. The third line displays the data access information when a work table was retrieved due to a shortage of hash table areas. Output information (consisting of five lines) If the access path is one of the following, five lines are output: Outer table of hash join Target of a subquery to which hash execution is applied Target of global hash grouping Target of `SELECT DISTINCT` to which hash execution is applied The first line displays the data access information when retrieving from the target work table. The second line displays the data access information when a work table was created due to a shortage of hash table areas. The third line displays the data access information when a work table was retrieved due to a shortage of hash table areas. The fourth line displays the data access information when the work table was created due to a shortage of hash table areas after data in a hash table was divided into work tables. The fifth line displays the data access information when the work table was retrieved due to a shortage of hash table areas after data in a hash table was divided into work tables.	When one line is output `Access` When two lines are output `Access` (Line 1) `Rowid_fetch` (Line 2) When three lines are output `Access` (Line 1) `Create_hash_wrktbl` (Line 2) `Scan_hash_wrktbl` (Line 3) When five lines are output `Access` (Line 1) `Create_hash_wrktbl` (Line 2) `Scan_hash_wrktbl` (Line 3) `Create_split_hash_wrktbl` (Line 4) `Scan_split_hash_wrktbl` (Line 5)
7	`DERIVED` `TABLE`	Output information (consisting of two lines) If the access path is one of the following, two lines are output: Inner table of hash join Target of the outer query of a subquery to which hash execution is applied The first line displays the data access information when the work table was created due to a shortage of hash table areas. The second line displays the data access information when the work table was retrieved due to a shortage of hash table areas. Output information (consisting of four lines) If the access path is one of the following, four lines are output: Outer table of hash join Target of a subquery to which hash execution is applied Target of global hash grouping Target of `SELECT DISTINCT` to which hash execution is applied Derived table created for a set operation to which hash execution is applied The first line displays the data access information when the work table was created due to a shortage of hash table areas. The second line displays the data access information when the work table was retrieved due to a shortage of hash table areas. The third line displays the data access information when the work table was created due to a shortage of hash table areas after data in a hash table was divided into work tables. The fourth line displays the data access information when the work table was retrieved due to a shortage of hash table areas after data in a hash table was divided into work tables.	When two lines are output `Create_hash_wrktbl` (Line 1) `Scan_hash_wrktbl` (Line 2) When four lines are output `Create_hash_wrktbl` (Line 1) `Scan_hash_wrktbl` (Line 2) `Create_split_hash_wrktbl` (Line 3) `Scan_split_hash_wrktbl` (Line 4)
8	`TABLE` `FUNCTION` `DERIVED` `TABLE`
9	`HASH` `JOIN`
10	`NESTED` `LOOP` `JOIN`
11	`SUBQUERY` `HASH`
12	`GROUPING SET`	Number of lines output Two lines are output per access path. Output information The first line displays the data access information that pertains to creation of a work table that is used when grouping is performed multiple times. The second line displays the data access information that pertains to retrieval of a work table that is used when grouping is performed multiple times.	`Create_grpset_wrktbl` (Line 1) `Scan_grpset_wrktbl` (Line 2)

(b) Information related to retrieval processing

The table below explains the information related to retrieval processing that is output as access path statistical information.

Table 10‒23: Items that are output as access path statistical information (Information related to retrieval processing)
No.	Column header	Information that is output	Output format	In the event of overflow
1	`Scan_path#`	Corresponding access path's tree row number	`UINT8`	--
2	`Scan_row_cnt`	Number of rows retrieved^{#1, #5}	`UNIT8`	`WRAP`
3	`Scan_start_time`	Retrieval processing start date and time^{#2, #6} The date and time the retrieval processing started are output in the following format: `YYYY-MM-DD∆hh:mm:ss.nnnnnn`^#4	`CHAR`	--
4	`Scan_end_time`	Retrieval processing end date and time^{#3, #7} The date and time the retrieval processing ended are output in the following format: `YYYY-MM-DD∆hh:mm:ss.nnnnnn`^#4	`CHAR`	--

Legend:

UINT8: Information is output as an 8-byte unsigned integer. If the target access path was not executed or was being executed, 0 or an intermediate value might be output.

CHAR: The character string indicated in Information that is output is output. If the target access path was not executed or was being executed, blanks might be output.

WRAP: When the value exceeds 18,446,744,073,709,551,615, it is reset to the minimum value in wraparound mode.

--: Overflow does not occur. Otherwise, overflow is not applicable.

Note

The output items that are not listed in the table are all maintenance information.

#1

If the retrieval processing references the recursive query names in recursive queries, the total value for all recursive queries is output.

#2

If the retrieval processing references the recursive query names in recursive queries, the start time of the first recursive query is output.

#3

If the retrieval processing references the recursive query names in recursive queries, the end time of the last recursive query is output.

#4

Four digits are displayed for year (YYYY) and two digits are displayed for month (MM), date (DD), hour (hh), minute (mm), and second (ss). Six digits are displayed for microsecond (nnnnnn). A value that consists of fewer digits is padded with leading zeros. Δ indicates a single-byte space.

#5

If multiple DISTINCT set functions are specified with different arguments in the same query specification, grouping is performed multiple times to determine the result of each DISTINCT set function. In this case, the total number of rows retrieved during all grouping processes is output.

#6

If multiple DISTINCT set functions are specified with different arguments in the same query specification, grouping is performed multiple times to determine the result of each DISTINCT set function. In this case, the start date and time for the first grouping process is output.

#7

If multiple DISTINCT set functions are specified with different arguments in the same query specification, grouping is performed multiple times to determine the result of each DISTINCT set function. In this case, the end date and time for the last grouping process is output.

(c) Information related to set operations

The table below explains the information related to set operations that is output as access path statistical information.

Table 10‒24: Items that are output as access path statistical information (Information related to set operations)
No.	Column header	Information that is output	Output format	In the event of overflow
1	`Setop_path#`	Corresponding access path's tree row number	`UINT8`	--
2	`Setop_type`	Type of set operation: `UNION` `ALL` `UNION` `DISTINCT` `EXCEPT` `ALL` `EXCEPT` `DISTINCT` `INTERSECT` `ALL` `INTERSECT` `DISTINCT` This might differ from the set operation type specified in SQL statements.	`CHAR`	--
3	`Setop_sort`	Whether sort processing is performed during the set operation^#1: `Y`: Sort processing is performed. `N`: Sort processing is not performed.	`CHAR`	--
4	`Setop_row_cnt`	Number of rows processed by the set operation	`UINT8`	`WRAP`
5	`Setop_start_time`	Date and time the set operation processing started The date and time the set operation processing started are output in the following format^#2: `YYYY-MM-DD∆hh:mm:ss.nnnnnn`^#4	`CHAR`	--
6	`Setop_end_time`	Date and time the set operation processing ended The date and time the set operation processing ended are output in the following format^#3: `YYYY-MM-DD∆hh:mm:ss.nnnnnn`^#4	`CHAR`	--

Legend:

UINT8: Information is output as an 8-byte unsigned integer. If the target access path was not executed or was being executed, 0 or an intermediate value might be output.

CHAR: The character string indicated in Information that is output is output. If the target access path was not executed or was being executed, blanks might be output.

WRAP: When the value exceeds 18,446,744,073,709,551,615, it is reset to the minimum value in wraparound mode.

--: Overflow does not occur. Otherwise, overflow is not applicable.

#1

If the processing is performed by the set operation that references the recursive query names in recursive queries, the total value for all recursive queries is output.

#2

If the processing is performed by the set operation that references the recursive query names in recursive queries, the start time of the first recursive query is output.

#3

If the processing is performed by the set operation that references the recursive query names in recursive queries, the end time of the last recursive query is output.

#4

Four digits are displayed for the year (YYYY), and two digits are displayed for the month (MM), date (DD), hour (hh), minute (mm), and second (ss). Six digits are displayed for the microsecond (nnnnnn). A value that consists of fewer digits is padded with leading zeros. Δ indicates a single-byte space.

(d) Information related to hash grouping areas

The table below explains the information related to hash grouping areas that is output as access path statistical information.

Table 10‒25: Items that are output as access path statistical information (Information related to hash grouping areas)
No.	Column header	Information that is output	Output format	In the event of overflow
1	`Hashgrp_path#`	Corresponding access path's tree row number	`UINT8`	--
2	`Hashgrp_area_shortage`	Whether a space shortage has occurred in the hash grouping area: `Y`: A space shortage has occurred. `N`: A space shortage has not occurred.	`CHAR`	--
3	`Hashgrp_area_sufficient_size`	Sufficient size (in kilobytes) of hash grouping area that will not result in a shortage of space. If this sufficient size exceeds the maximum value for the `adb_sql_exe_hashgrp_area_size` operand in the server definition or the client definition, the maximum value for each operand is displayed.	`UINT8`	--
4	`Hashgrp_group_num`	Number of rows processed by grouping (before the `HAVING` clause is evaluated)	`UINT8`	`WRAP`
5	`Hashgrp_wrktbl_max_row_cnt`	Maximum number of rows inserted into a work table (maximum value per real thread)	`UINT8`	`WRAP`

Legend:

UINT8: Information is output as an 8-byte unsigned integer. If the target access path was not executed or was being executed, 0 or an intermediate value might be output.

CHAR: The character string indicated in Information that is output is output. If the target access path was not executed or was being executed, blanks might be output.

WRAP: When the value exceeds 18,446,744,073,709,551,615, it is reset to the minimum value in wraparound mode.

--: Overflow does not occur. Otherwise, overflow is not applicable.

Note

The output items that are not listed in the table are all maintenance information.

(e) Information related to hash table areas

The table below explains the information related to hash table areas that is output as access path statistical information.

Information about hash filters is also included in the information related to hash table areas.

Table 10‒26: Items that are output as access path statistical information (Information related to hash table areas)
No.	Column header	Information that is output	Output format	In the event of overflow
1	`Hashtbl_path#`	Corresponding access path's tree row number	`UINT8`	--
2	`Hashtbl_area_shortage`	Whether a space shortage has occurred in the hash table area^{#1, #6}: `Y`: A space shortage has occurred. `N`: A space shortage has not occurred.	`CHAR`	--
3	`Hashtbl_wrktbl_bckt_max_row_cnt`	Maximum number of rows inserted into a work table (maximum value per real thread) in the event of a space shortage in the hash table area^{#2, #7}	`UINT8`	`WRAP`
4	`Hashtbl_filter_num`	Number of hash filters^#3	`UINT8`	--
5	`Hashtbl_filter_disabled_num`	Number of invalidated hash filters^{#4, #5}	`UINT8`	--
6	`Hashtbl_sum_filter_check_cnt`	Total number of times a hash value was checked by using hash filters^{#4, #5} The total value for all hash filters is output.	`UINT8`	--
7	`Hashtbl_max_filter_check_cnt`	Largest number of times a hash value was checked by using a specific hash filter^{#4, #5} After the number of time a hash value was checked is obtained for each hash filter, the largest one of the obtained numbers is output.	`UINT8`	--
8	`Hashtbl_min_filter_check_cnt`	Smallest number of times a hash value was checked by using a specific hash filter^{#4, #5} After the number of time a hash value was checked is obtained for each hash filter, the smallest one of the obtained numbers is output.	`UINT8`	--
9	`Hashtbl_sum_filtering_cnt`	Total number of times a hash value was excluded by hash filters^{#4, #5} The total value for all hash filters is output.	`UINT8`	--
10	`Hashtbl_max_filtering_cnt`	Largest number of times a hash value was excluded by a specific hash filter^{#4, #5} After the number of time a hash value was excluded is obtained for each hash filter, the largest one of the obtained numbers is output.	`UINT8`	--
11	`Hashtbl_min_filtering_cnt`	Smallest number of times a hash value was excluded by a specific hash filter^{#4, #5} After the number of time a hash value was excluded is obtained for each hash filter, the smallest one of the obtained numbers is output.	`UINT8`	--

Legend:

UINT8: Information is output as an 8-byte unsigned integer. If the target access path was not executed or was being executed, 0 or an intermediate value might be output.

CHAR: The character string indicated in Information that is output is output. If the target access path was not executed or was being executed, blanks might be output.

WRAP: When the value exceeds 18,446,744,073,709,551,615, it is reset to the minimum value in wraparound mode.

--: Overflow does not occur. Otherwise, overflow is not applicable.

Note

The output items that are not listed in the table are all maintenance information.

#1

If the hash table area is used by the retrieval processing that references the recursive query names in recursive queries, whether a space shortage has occurred is output after all recursive queries are completed.

#2

If the hash table area is used by the retrieval processing that references the recursive query names in recursive queries, of the values for individual recursive queries, the largest value is output.

#3

For an SQL statement to which no hash filter is applied, 0 is output. 0 is also output if the value of the adb_sql_exe_hashflt_area_size operand is 0.

#4

For an SQL statement to which no hash filter is applied, 0 is output. 0 is also output if the value of the adb_sql_exe_hashflt_area_size operand is 0 and if the hash filter size is insufficient.

#5

For a hash filter area used for retrieval processing that references the recursive query names in recursive queries, information about the summation results for each hash filter through all recursive queries is output.

#6

If multiple DISTINCT set functions are specified with different arguments in the same query specification, grouping is performed multiple times to determine the result of each DISTINCT set function. In this case, the information about all grouping processes is output.

#7

If multiple DISTINCT set functions are specified with different arguments in the same query specification, grouping is performed multiple times to determine the result of each DISTINCT set function. In this case, the largest one of the values resulting from all grouping processes is output.

(f) Information related to subqueries containing external reference columns

The table below explains the information related to subqueries containing external reference columns that is output as access path statistical information.

Table 10‒27: Items that are output as access path statistical information (Information related to subqueries containing external reference columns)
No.	Column header	Information that is output	Output format	In the event of overflow
1	`Corsubq_path#`	Corresponding access path's tree row number	`UINT8`	--
2	`Corsubq_request_cnt`	Number of times subqueries containing an external reference column were requested^#	`UINT8`	`WRAP`

Legend:

UINT8: Information is output as an 8-byte unsigned integer. If the target access path was not executed or was being executed, 0 or an intermediate value might be output.

WRAP: When the value exceeds 18,446,744,073,709,551,615, it is reset to the minimum value in wraparound mode.

--: Overflow does not occur. Otherwise, overflow is not applicable.

Note

The output items that are not listed in the table are all maintenance information.

#

If the subquery contains an external reference column and is under retrieval processing that references the recursive query names in recursive queries, the total value for all recursive queries is output.

(g) Information related to caches for storing subquery requests

The table below explains the information related to caches for storing subquery requests that is output as access path statistical information.

Table 10‒28: Items that are output as access path statistical information (Information related to caches for storing subquery requests)
No.	Column header	Information that is output	Output format	In the event of overflow
1	`Corsubqc_path#`	Corresponding access path's tree row number	`UINT8`	--
2	`Corsubqc_request_cnt`	Number of times caches were requested^#	`UINT8`	`WRAP`
3	`Corsubqc_exec_cnt`	Number of times subqueries were executed with no cache hits^#	`UINT8`	`WRAP`

Legend:

UINT8: Information is output as an 8-byte unsigned integer. If the target access path was not executed or was being executed, 0 or an intermediate value might be output.

WRAP: When the value exceeds 18,446,744,073,709,551,615, it is reset to the minimum value in wraparound mode.

--: Overflow does not occur. Otherwise, overflow is not applicable.

Note

The output items that are not listed in the table are all maintenance information.

#

If the subquery is under retrieval processing that references the recursive query names in recursive queries, the total value for all recursive queries is output.

(h) Information related to recursive queries

The following table describes the information related to recursive queries that is output as access path statistical information.

Table 10‒29: Items that are output as access path statistical information (Information related to recursive queries)
No.	Column header	Information that is output	Output format	In the event of overflow
1	`Recurq_path#`	Corresponding access path's tree row number	`UINT8`	--
2	`Recurq_row_cnt`	Number of rows processed for recursive queries	`UINT8`	`WRAP`
3	`Recurq_start_time`	Date and time the recursive query processing started The date and time the recursive query processing started are output in the following format: `YYYY-MM-DD∆hh:mm:ss.nnnnnn`^#	`CHAR`	--
4	`Recurq_end_time`	Date and time the recursive query processing ended The date and time the recursive query processing ended are output in the following format: `YYYY-MM-DD∆hh:mm:ss.nnnnnn`^#	`CHAR`	--
5	`Recurq_max_recursion`	Value for the maximum-number-of-recursions specification	`UINT8`	--
6	`Recurq_recursion_cnt`	Number of recursions	`UINT8`	`WRAP`

Legend:

UINT8: Information is output as an 8-byte unsigned integer. If the target access path was not executed or was being executed, 0 or an intermediate value might be output.

CHAR: The character string indicated in Information that is output is output. If the target access path was not executed or was being executed, blanks might be output.

WRAP: When the value exceeds 18,446,744,073,709,551,615, it is reset to the minimum value in wraparound mode.

--: Overflow does not occur. Otherwise, overflow is not applicable.

Note

The output items that are not listed in the table are all maintenance information.

#

Four digits are displayed for the year (YYYY). Two digits are displayed for the month (MM), date (DD), hour (hh), minute (mm), and second (ss). Six digits are displayed for the microsecond (nnnnnn). A value that consists of fewer digits is padded with leading zeros. Δ indicates a single-byte space.

(i) Information related to execution of SQL statements

The table below explains the information related to execution of SQL statements that is output as access path statistical information.

Table 10‒30: Items that are output as access path statistical information (Information related to execution of SQL statements)
No.	Column header	Information that is output	Output format	In the event of overflow
1	`SQLexec_result_wrk_size`	Size of the area for storing SQL statement execution results (bytes)	`UINT8`	--

Legend:

UINT8: Information is output as an 8-byte unsigned integer. If the target access path was not executed or was being executed, 0 or an intermediate value might be output.

--: Overflow does not occur. Otherwise, overflow is not applicable.

Note

The output items that are not listed in the table are all maintenance information.

To Page Top

(3) Notes about access path statistical information

Not all access paths are subject to output of access path statistical information. Therefore, the contents of the access path statistical information might not match the SQL statement statistical information. For details about the items that are output as access path statistical information, see (2) Items that are output as access path statistical information.
For details about the SQL statements subject to output of access path statistical information, see SQL statements for which access paths are output under About access paths under How to use access paths (how to use SQL statement execution plans) in Tuning Application Programs in the HADB Application Development Guide.
Normally, access path statistical information is not output when batch transfer of dynamic parameter values is used to execute SQL statements. However, access path statistical information is output when batch transfer of dynamic parameter values is used to execute SQL statements when only one set of dynamic parameters is used. For details about batch transfer of dynamic parameter values, see Batch transfer of dynamic parameter values in Designs Related to Improvement of Application Program Performance in the HADB Application Development Guide.

To Page Top