To obtain UAP statistical reports, specify values to PDCLTPATH, PDSQLTRACE, and PDUAPREPLVL in the client environment definitions.

The UAP statistical report facility creates two UAP statistical report files in the directory specified by PDCLTPATH.

To create the report files in a different directory from the one specified by PDCLTPATH, specify a value to PDREPPATH. Also, to open and close SQL trace files in CONNECT and DISCONNECT units, specify CNCT in PDSQLTRCOPENMODE.

For details about the individual client environment definitions, see 6.6 Client environment definitions (setting environment variables).

You can specify the information to be obtained using PDUAPREPLVL in the client environment definitions. Table 10-3 shows the relationship between the value of PDUAPREPLVL and the information to be obtained.

Table 10-3 Relationship between the value of PDUAPREPLVL and information to be obtained

Value of PDUAPREPLVL	Information to be obtained
	By SQL	By UAP	Access path information2	SQL runtime interim results2
s1	Y	N	N	N
u	N	Y	N	N
p	N	N	Y	N
r	N	N	N	Y
su1	Y	Y	N	N
sp1	Y	N	Y	N
sr1	Y	N	N	Y
up	N	Y	Y	N
ur	N	N	Y	Y
pr	N	N	Y	Y
sur1	Y	Y	N	Y
spr1	Y	N	Y	Y
upr	N	Y	Y	Y
a or sup1	Y	Y	Y	Y

Y: Information is obtained.

N: Information is not obtained.

¹ If s is specified, SQL trace information is also obtained.

² When access path information or SQL runtime interim results are obtained, the server's workload may increase because the system re-creates an SQL object even if the SQL object is found in the buffer.

• Size of an SQL trace file

You can determine the size of an SQL trace file using the following formula:

Size of SQL trace file = 3208 + A + 80 number of operations + total length of SQL statements (maximum of 4096) (bytes)

A: Total length of character strings specified in PDHOST, PDFESHOST, PDSQLOPTLVL, PDADDITIONALOPTLVL, PDREPPATH, and PDTRCPATH in the client environment definitions

To output information by SQL, information by UAP, access path information, and SQL runtime interim results, add the following sizes (bytes):

Information by SQL: 83^* number of SQL statements

Information by UAP: 2740^* number of DISCONNECTs

Access path information: See (2)(b) Access path information.

SQL runtime interim results: See (2)(c) SQL runtime interim results.

^* This is the maximum value. The value changes according to the number of digits to be displayed.

• Notes
  
  1. If a program uses an API conforming to X/Open under OLTP, the facility does not output the information by the UAP.
  2. The facility does not display the access path information if its size exceeds one gigabyte.
  3. SQL runtime interim results are not output if the interim results during SQL execution exceed 1 gigabyte.
  4. For a HiRDB/Parallel Server, the information by the UAP does not include the privilege checking executed at the connected dictionary server.
  5. If you specify values that enable both the output of access path information and the inter-process memory facility (if you specify PDIPC=MEMORY in the client environment definitions), the PDIPC specification becomes invalid.
  6. If you specify output of access path information or SQL runtime interim results, and use the inter-process memory communication facility (if you specify PDIPC=MEMORY in the client environment definitions), the PDIPC specification becomes invalid.

The following shows a sample UAP statistical report, followed by explanations (a) through (d):

Output example

CNCT CLPID CLTID NO OP SEC SQL SQL START-TIME END-TIME OP NO CODE NO CODE WARN TION ---- ----- ----- -- ---- --- ---- ----- ------------ ------------ ---- 1 9155 0 1 CNCT 0 0 WC040 16:03:55.720 16:03:58.080 0001 1 9155 0 2 AUI2 1 0 -0000 16:03:58.630 16:03:59.400 0000 *SQL* INSERT INTO T1(C1,C2,C3,C4,C5,C6) VALUES(?,?,?,?,?,?) 00:00:00.770 00:00:00.430000 340 1 0 0 0 0 0 ........(a) [1] [2] [3] [4] [5] [6] [7] [8] [9] 1 9155 0 3 SET 2 0 -0000 16:04:00.820 16:04:01.540 0000 *SQL* SELECT * from T1, T2, T3 where ((T1.C1='a' and T1.C2='A') or (T1.C1='a' and T1.C2='B')) and T1.C1=T2.C1 and T1.C2=T2.C2 and T2.C3>=1995 and T1.C1=T3.C1 and T1.C2=T3.C2 order by T1.C1 00:00:00.720 00:00:00.240000 480 1 0 0 0 Result of SQL Optimizer : .........................................(b) Connect No : 1 -------------------------------------------------------------------------------- Section No : 2 UAP Source :XXXXXXXX.ec Optimize Mode : COST_BASE_2 SQL Opt Level : 0x00000420(1056) = "PRIOR_NEST_JOIN"(32),"RAPID_GROUPING"(1024) Add Opt Level : 0x00000003(3) = "COST_BASE_2"(1),"APPLY_HASH_JOIN"(2) Work Table : 0 Table Cost : 12672.66944 ----- QUERY EXPRESSION BODY ID : 1 ----- : ----- QUERY ID : 1 ----- : JOIN : SCAN : -------------------------------------------------------------------------------- 1 9155 0 4 OPEN 2 0 -0000 16:04:02.090 16:04:02.800 0000 Result of SQL Execution : .........................................(c) -------------------------------------------------------------------------------- Connect No : 1 UAP Source : XXXXXXXX.ec Section No : 2 ----- QUERY EXPRESSION BODY ID : 1 ----- : ----- QUERY ID : 1 ----- : JOIN : SCAN : -------------------------------------------------------------------------------- 1 9155 0 9 DISC 0 0 -0000 16:05:55.110 16:05:56.660 0004 UAP INFORMATION: .................................................(d) [1]UAPNAME() [2]SVHOST(dcm3500) [3]SVPORT(4439) [4]SVNAME(fes1) [5]CNCTNO(1) [6]SVPID(8945) [7]CLPID(9155) [8]CLTTID(0) [9]WAITT(0) [10]CTIME(0) [11]ROREQ(0) [12]ROHITS(0) [13]SOREQ(10) [14]SOHITS(3) [15]SOCRT(0) [16]SOMAX(0) [17]COMT(0) [18]ROLB(0) [19]FROW(0) [20]DROW(0) [21]IROW(3) [22]UROW(0) [23]SET(1) [24]OPEN(2) [25]FETC(1) [26]CLOS(0) [27]DESC(0) [28]SEL(1) [29]INS(3) [30]UPD(0) [31]DEL(0) [32]LOCK(0) [33]CRTT(0) [34]DRPT(0) [35]ALTT(0) [36]CRTI(0) [37]DRPI(0) [38]CMTT(0) [39]CMTC(0) [40]CRTS(0) [41]DRPS(0) [42]GRTR(0) [43]GRTS(0) [44]GRTA(0) [45]GRTC(0) [46]GRTD(0) [47]RVKR(0) [48]RVKS(0) [49]RVKA(0) [50]RVKC(0) [51]RVKD(0) [52]CRTV(0) [53]DRPV(0) [54]PRGT(0) [55]CRTP(0) [56]DRPP(0) [57]ALTP(0) [58]CALL(0) [59]DESI(0) [60]MISC(0) [61]MAXIO(0) [62]MAXIOM(0) [63]MINIO(0) [64]MINIOM(0) [65]IOTIM(0) [66]IOTIMM(0) [67]DIDRC(0) [68]DIDUC(0) [69]DIDHC(0) [70]DIDRD(0) [71]DIDWT(0) [72]LBRFC(0) [73]LBUPC(0) [74]LBRHC(0) [75]LBUHC(0) [76]LBRDC(0) [77]LBWTC(0) [78]BFSHC(2320) [79]BRDWC(0) [80]BWTWC(50) [81]BLKWC(2) [82]MWFN(0) [83]MWFEC(0) [84]MWFVL(0) [85]WFRDC(0) [86]WFWTC(0) [87]WBFOC(0) [88]MWHTS(0) [89]MBSL1(0) [90]MBSL2(0) [91]MBSL3(0) [92]SCHSKD(0) [93]SCHCHG(0) [94]CINSM(0) [95]CAFLS(0) [96]CAFWR(0) [97]CFMAX(0) [98]CFAVG(0) [99]LDIRC(0) [100]LDIUC(0) [101]LDIHC(0) [102]LDIRD(0) [103]LDIWT(0) [104]LBFSHC(0) [105]ARREQ(0) [106]ARWC(0) [107]ARWT(0) [108]ARWTM(0) [109]ARWTA(0) [110]ARWTMA(0) [111]ARSTA(0) [112]ARSTMA(0) [113]HJMAX(0) [114]HJCMC(0) [115]HJHTC(0)

(a) Information by the SQL

1. SQL execution time (milliseconds)
   Displays the SQL execution time in the format HH:MM:SS.mmm. If YES is specified in the PDSQLEXECTIME client environment definition, the unit becomes microseconds.
   
2. SQL execution time at server (microseconds)
   Displays the SQL execution time at the server in the format HH:MM:SS.mmmmmm.
   
3. Difference between 1 and 2 (milliseconds)
   Provides a guideline for communication time. If YES is specified in the PDSQLEXECTIME client environment definition, the unit becomes microseconds.
   
4. Number of processed rows
   Displays the number of rows processed by the SQL statements that were issued during the session.
   
5. Work table creations count
   Displays the number of times a work table was created during internal processing for the SQL statements that were issued during the session.
   
6. Work table deletions count
   Displays the number of times a work table was deleted during internal processing for the SQL statements that were issued during the session.
   
7. SQL object size (bytes)
   Displays the size of the SQL object created by the SQL statements that were issued during the session.
   
8. Total comparison count during hash table search processing by hash join, subquery hash execution
   Displays the total number of comparisons that the SQL statements issued during this connection perform on the data having the same hash value during the hash table search.
   
9. Total hash join search count during hash join, subquery hash execution
   Displays the number of times that the hash table was searched by the SQL statements issued during this connection.
   

(b) Access path information

A UAP statistical report displays access path information. Connect No displays the connect number. By executing an upward search based on the connect number, you can identify the SQL statements displayed in the SQL trace information. You can also use the connect number to find out the execution request start and end times of the SQL statements displayed in the SQL trace information. For dynamic SQL, execute a downward search based on the connect number, while for static SQL, execute an upward search. If you specify information acquisition in SQL units, the SQL execution times are also displayed. If you find an SQL statement that has a long SQL execution time, tune the UAP.

The UAP statistical report facility does not include the following information in the access path information: HiRDB version, number of back-end servers, UAP name, authorization identifier, SQL optimization processing time, and SQL statements. However, if the routine contains data manipulation SQL statements, the facility displays them as the SQL statements.

If the access path is SELECT-APSL for a HiRDB/Single Server (access path is to be selected from multiple candidates by the boundary value during execution), the facility displays the boundary value at the beginning, followed by multiple candidates separated by Section No.

For details about the access path information, see the access path display utility in the manual HiRDB Version 8 Command Reference.

• Notes
  
  1. The facility does not display the access path information for a Java routine.
  2. For an SQL routine, the facility displays the access path information if the SQL object's index information becomes invalid due to an index addition or deletion made to the table used within the routine.
  3. The access path information increases the size of the SQL trace file. You can determine this increase in size using the following formula. This is just a guideline; the actual size of the access path information depends on the table definitions, index definitions, and SQL statement used.
     

     

(c) SQL runtime interim results

A UAP statistical report displays SQL runtime interim results.

When SQL runtime interim results are displayed, the information listed below can be checked. (The number of rows displayed in the results is the number of rows that HiRDB actually processed at the stage that the interim results are displayed.)

• Number of rows fetched from the table
• Number of rows narrowed by the index
• Number of rows in the results for each join
• Number of input/output rows for any duplicate exclusion, GROUP BY, ORDER BY, or LIMIT specified in the query and number of rows in the query results
• Number of rows in the results for each set operation

Use the SQL runtime interim results and the access path information to carry out SQL tuning. For details about using access path information for SQL tuning, see the description of the access path display utility in the manual HiRDB Version 8 Command Reference.

Output format

------------------------------------------------------- Connect No : aa...a UAP Source : bb...b Section No : cc...c ----- QUERY EXPRESSION BODY ID : ... ----- .............1 : ----- QUERY ID : ... ----- .............................2 : JOIN ...................................................3 : SCAN ...................................................4 :

Explanation

1. Set operation process information
   For details about set operation process information, see Set operation process information.
   
2. Query process information
   For details about query process information, see Query process information.
   
3. Join process information
   For details about join process information, see Join process information.
   
4. Base table search process information
   For details about base table search process information, see Base table search process information.
   

aa...a

Displays the connection sequence number.

bb...b

Displays the UAP source file name.

cc...c

Displays the section number (number for checking the SQL correspondence).

The information after Connect No is repeated for each SQL statement. By conducting a search using a connection sequence number and a section number, you can identify correspondences with the SQL statements displayed in SQL trace information and the access path information.

• Set operation process information
  

  ----- QUERY EXPRESSION BODY ID : aa...a ----- Query : bb...b ROWS Limit : cc...c ROWS <-- dd...d ROWS Order by : ee...e ROWS SetOpe Process : ff...f = gg...g ROWS <-- hh...h ii...i hh...h :

Explanation

aa...a

Displays the query express body ID.

An ID number is assigned to each query expression body that includes a set operation. If the SQL statement consists of multiple query expression bodies, this line is used to separate the information displayed for each query expression body.

When (b) Access path information is being displayed, this value corresponds to the query expression body ID displayed in the access path information.

bb...b

Displays the number of rows in the results of the query expression.

cc...c ROWS <-- dd...d ROWS

Displays the final number of rows for the process (LIMIT process) that gets search results for the maximum number of rows to return.

If LIMIT clause is not specified, this line is not displayed.

cc...c

Displays the number of output rows in the LIMIT process.

dd...d

Displays the number of input rows in the LIMIT process.

ee...e

Displays the number of rows of the sort process (ORDER BY process).

This line is not displayed if any one of the following conditions applies:

• An ORDER BY clause is not specified.
• The sort processing specified in the ORDER BY clause is omitted.
• A LIMIT clause is specified.

ff...f = gg...g ROWS <-- hh...h ii...i hh...h

Displays the number of rows in the results of the set operation.

If multiple set operations are specified, the information is displayed over several lines.

If the facility that executes partitioned scanning of UNION ALL is applied (this facility returns the search results of each query in succession without creating a work table), this line is not displayed.

ff...f

Displays the set operation number of the set operation results in the format LID(set-operation-number).

If access path information is being displayed, this corresponds to the set operation number displayed in the access path information.

gg...g

Displays the number of rows in the set operation results.

hh...h

If the query expression body to be operated is a query specification, this information is displayed in the format QID(query-ID). If the query expression body to be operated is the joined result of multiple query specifications, LID(set-operation-number) is displayed.

ii...i

Displays the set operation type (UNION, UNION ALL, EXCEPT, or EXCEPT ALL). The hh...h values before and after this value form the query expression body.

• Query process information
  

  ----- QUERY ID : aa...a ----- Query : bb...b ROWS Limit : cc...c ROWS <-- dd...d ROWS Order by : ee...e ROWS Distinct : ff...f ROWS <-- gg...g ROWS Having : hh...h ROWS Group by : ii...i ROWS <-- jj...j ROWS

Explanation

aa...a

Displays the query ID.

A number is assigned to each query specification. If the SQL statement consists of multiple query specifications, this line is used to separate the information displayed for each specification.

If access path information is being displayed, this value corresponds to the query ID displayed in the access path information.

bb...b

Displays the number of rows in the query results.

cc...c ROWS <-- dd...d ROWS

Displays the final number of rows for the process (LIMIT process) that gets the search results for the maximum number of rows to return.

If LIMIT is not specified, this line is not displayed.

cc...c

Displays the number of output rows in the LIMIT process.

dd...d

Displays the number of input rows in the LIMIT process.

ee...e

The number of rows in sort processing (ORDER BY processing) is displayed. Note that ORDER BY processing may be executed implicitly even if an ORDER BY clause is not specified.

This line is not displayed if any one of the following conditions applies:

• An ORDER BY clause is not specified.
• The sort processing specified in the ORDER BY clause is omitted.
• ORDER BY processing is not executed implicitly.
• A LIMIT clause is specified.

ff...f ROWS <-- gg...g ROWS

Displays the number of rows processed by duplicate exclusion. Note that duplicate exclusion may be executed implicitly even if duplicate exclusion is not specified.

This line is not displayed if any one of the following conditions applies:

• Duplicate exclusion is not specified.
• Duplicate exclusion is not executed implicitly.
• A LIMIT clause is specified.

ff...f

The number of output rows in duplicate exclusion processing is displayed.

gg...g

The number of input rows in duplicate exclusion processing is displayed.

hh...h

Displays the number of rows after the HAVING clause is evaluated.

If a HAVING clause is not specified, this line is not displayed.

ii...i ROWS <-- jj...j ROWS

Displays the number of rows processed by grouping (including implicit grouping).

If grouping is not executed, this line is not displayed.

ii...i

Displays the number of output rows in grouping.

jj...j

Displays the number of input rows in grouping.

• Join process information
  

  JOIN # Join ID : aa...a Row Count : bb...b ROWS Left : cc...c ROWS Right : dd...d ROWS Join Type : ee...e(ff...f)

Explanation

aa...a

Displays the join process ID.

An ID number is assigned to each join process unit, and if there are multiple join processes, the processes are separated with this line.

If access path information is being displayed, this value corresponds to the join process ID displayed in the access path information.

bb...b

Displays the number of rows in the join process results.

cc...c

Displays the number of rows that were fetched from the join partner on the left side.

dd...d

Displays the number of rows that were fetched from the join partner on the right side.

ee...e

• For HiRDB/Single Server and for HiRDB/Parallel Server when the join method is not determined dynamically during SQL execution
  Displays the join process type (MERGE JOIN, NESTED LOOPS JOIN, CROSS JOIN, or HASH JOIN).
  
• For HiRDB/Parallel Server when the join method is determined dynamically during SQL execution
  Displays SELECT-APSL as the join process type.
  

ff...f

Displays the execution type of the join process (INNER, LEFT OUTER, EXIST, NOT EXIST, ALL, or VALUE).

• Base table search process information

• When no index or only one index is used in the search process
  

  SCAN # Table Name : aa...a(aa...a) 0xbbbbbbbb(bb...b) Row Count : cc...c ROWS Index Name : dd...d 0xeeeeeeee(ee...e) Search : ff...f gg...g Key : hh...h gg...g

Explanation

aa...a(aa...a)

Displays the name of the table to be searched and the correlation name (in parentheses). If a correlation name is not being used, the correlation name (in parentheses) is not displayed. If there are several search processes, this line is used to separate the information displayed for each search.

0xbbbbbbbb(bb...b)

Displays the ID of the table to be searched in hexadecimal and decimal (in parentheses) formats.

cc...c

Displays the number of rows fetched from the base table.

dd...d

Displays the index name to be used in the search.

This line is not displayed in the following cases:

• The search is performed without the use of an index.
• HiRDB/Parallel Server dynamically determines the search method during SQL execution.

0xeeeeeeee(ee...e)

Displays the ID of the index used in the search. The ID is displayed in hexadecimal and decimal (in parentheses) formats.

ff...f

Displays the number of rows in the results narrowed by the search condition.

When an index is used in the search, the number of rows that make up the index is displayed, even if there is no search condition.

This line is not displayed when the surrogate facility for plug-in indexes is used to determine the results of a set function.

gg...g

Displays ELEMENTS for an index that contains a repetition column and ROWS for all other cases.

hh...h

Displays the number of rows in the results narrowed by the key condition.

If there is no key condition, this line is not displayed.

• When multiple indexes are used in the search process
  

  SCAN # Table Name : aa...a(aa...a) 0xbbbbbbbb(bb...b) Row Count : cc...c ROWS Index Name : dd...d = ee...e 0xffffffff(ff...f) Search : gg...g hh...h Key : ii...i hh...h Row Count : jj...j ROWS dd...d = ee...e 0xffffffff(ff...f) Search : gg...g hh...h Key : ii...i hh...h Row Count : jj...j ROWS dd...d = kk...k ROWS <-- ll...l mm...m ll...l

Explanation

aa...a(aa...a)

Displays the name of the table to be searched and the correlation name (in parentheses).

If a correlation name is not being used, the correlation name (in parentheses) is not displayed. If there are multiple search processes, this line is used to separate the information displayed for each process.

0xbbbbbbbb(bb...b)

Displays the ID of the table to be searched in hexadecimal and decimal (in parentheses) formats.

cc...c

Displays the number of rows fetched from the base table.

dd...d

Displays the number of the work table created when AND PLURAL INDEXES SCAN^* is executed. The work table number is displayed in the LID(work-table-number) format.

If access path information is being displayed, this value corresponds to the work table number displayed in the access path information.

ee...e

Displays the name of the index used to create the work table when AND PLURAL INDEXES SCAN^* or OR PLURAL INDEXES SCAN^* is executed. The index name is displayed in multiple lines. However, if a work table is created without the use of an index, (NO USE) is displayed as the index name.

0xffffffff(ff...f)

Displays the index IDs used in the search. The IDs are displayed in hexadecimal and decimal (in parentheses) formats.

gg...g

Displays the number of rows in the results narrowed by the search condition.

Even if there is no search condition, the number of rows that make up the index is displayed when a search using an index is executed.

hh...h

Displays ELEMENTS for an index that contains a repetition column and ROWS for all other cases.

ii...i

Displays the number of rows in the results narrowed by the key condition.

If there is no key condition, this line is not displayed.

jj...j

Displays the number of rows fetched from the base table.

dd...d = kk...k ROWS <-- ll...l mm...m ll...l

Displays the creation sequence of the work tables created when AND PLURAL INDEXES SCAN^* is executed. When three or more indexes are used in the search process, this information is displayed in multiple lines.

kk...k

Displays the number of rows in the operation results.

ll...l

Displays the work table that becomes the input for the operation. The work table is displayed in the LID(work-table-number) format.

mm...m

Displays the operation type (AND, OR, or ANDNOT) performed on the work tables.

^* For details about AND PLURAL INDEXES SCAN and OR PLURAL INDEXES SCAN, see the description of the access path display utility in the manual HiRDB Version 8 Command Reference.

• When a work table is created for retrieving the results of a view table
  

  SCAN # Table Name : aa...a(aa...a) 0xbbbbbbbb(bb...b) Row Count : cc...c ROWS

Explanation

aa...a(aa...a)

Displays the view name and the correlation name (in parentheses).

If a correlation name is not being used, the correlation name (in parentheses) is not displayed.

0xbbbbbbbb(bb...b)

Displays the view ID in hexadecimal and decimal (in parentheses) formats.

cc...c

Displays the number of rows that were fetched from the table.

• When a work table is created for the WITH clause
  

  SCAN # Table Name : aa...a(aa...a) Row Count : bb...b ROWS

Explanation

aa...a(aa...a)

Displays the WITH clause query name and the correlation name (in parentheses).

If a correlation name is not being used, the correlation name (in parentheses) is not displayed.

bb...b

Displays the number of rows that were fetched from the table.

• When a work table is created for the derived table specified in the FROM clause
  

  SCAN # Table Name : aa...a(aa...a) Row Count : bb...b ROWS

Explanation

aa...a(aa...a)

Displays (NO NAME) or (NO NAME)(correlation-name).

bb...b

Displays the number of rows that were fetched from the table.

• When a work table that HiRDB creates internally is searched
  

  SCAN # Table Name : aa...a Row Count : bb...b ROWS

Explanation

aa...a

Displays the name of the work table that HiRDB created internally.

The name of the work table that HiRDB created internally is displayed in (DUMMY work-table-number) format.

The work table number is a three-digit integer.

bb...b

Displays the number of rows fetched from the work table that HiRDB created internally.

• When the query results for an external server are retrieved
  

  SCAN # Table Name : aa...a Row Count : bb...b ROWS

Explanation

aa...a

Displays the name of the table identifier that is created internally so that the local HiRDB can access results fetched from an external server. The table identifier name is displayed in (FOREIGNSQL table-number) format.

bb...b

Displays the number of rows that were fetched from the external server.

Notes

1. SQL runtime interim results are displayed when of one of the following SQL statements is executed:
   
   • Definition SQL¹
   • ASSIGN LIST statement⁵
   • CLOSE statement
   • DELETE statement⁶
   • EXECUTE statement¹
   • EXECUTE IMMEDIATE statement ²
   • INSERT statement^{3, 6}
   • PREPARE statement⁴
   • PURGE TABLE statement¹
   • Single-row SELECT statement
   • UPDATE statement⁶
   • COMMIT statement¹
   • DISCONNECT statement¹
   • ROLLBACK statement¹
   • If an error that has implicit rollback occurs¹

   ¹ SQL runtime interim results are displayed if there is a cursor that has not been closed.

   ² SQL runtime interim results are displayed for the following SQL statements:

   ASSIGN LIST statement

   DELETE statement

   INSERT statement

   UPDATE statement

   ³ SQL runtime interim results are displayed when a scalar subquery or a query specification is specified in the VALUES clause.

   ⁴ If YES is specified in the PDPRPCRCLS client environment definition and an SQL identifier being used by an open cursor is reused by a PREPARE statement, the SQL runtime interim results of the open cursor are displayed.

   ⁵ SQL runtime interim results are not displayed when FOR ALTER LIST is specified.

   ⁶ SQL runtime interim results are not displayed when a foreign table is specified as the table target.

2. SQL runtime interim results are not displayed for an SQL statement described in a stored procedure, even if the CALL statement is executed.
3. SQL runtime interim results are not displayed for a trigger SQL statement described in a trigger, even if the trigger is executed.
4. When HiRDB/Parallel Server is used, the total number of rows of all servers is displayed.
5. The displayed number of rows may not be an accurate value.
6. When SQL runtime interim results are displayed, the size of the SQL trace file increases by the size shown in the expression below. Note this increase when estimating the size of the SQL trace file. However, the size of the interim results varies significantly depending on the table definitions, the index definitions, and the SQL statements. The value estimated with the following expression should be used only as a rough guideline.
   

   Size of SQL runtime interim results n = 0.8 + 0.1 set-operation-count + 0.9 (Si) (kilobytes) i=1

(d) Information by the UAP

1. UAP name
   This is the name of the UAP for which statistical information was edited.
   
2. Host name
   This is the name of the host at the connected server.
   
3. Port number
   This is the port number at the connected server.
   
4. Connected server name
   This is the name of the front-end server or single server that was connected.
   
5. Connection sequence number
   This is the sequence number assigned by the server each time CONNECT is accepted.
   
6. Server process number
   This is the connected server's process number.
   
7. Client process number
   This is the UAP's process number.
   
8. Client's thread number
   This is the thread number of the UAP that is running in multi-thread.
   
9. Lock release wait time (milliseconds)¹
   This is the length of time during which a lock acquisition request in the server was placed on lock release wait status because another user locked the requested resource.
   
10. CPU time (milliseconds)¹
    This is the CPU time at the server that was used by transaction during UAP execution.
    
11. Stored procedure's SQL object acquisition requests count
    This is the number of times a stored procedure's SQL object acquisition request was issued for the SQL object buffer at the single server or front-end server.
    
12. Stored procedure object buffer hits count
    This is the number of times requested information was found in the SQL object buffer at the single server or front-end server.
    
13. SQL object acquisition requests count
    This is the number of times an SQL object acquisition request was issued for the SQL statements issued during the session.
    
14. SQL object buffer hits count
    This is the number of times requested information was found in the SQL object buffer for the SQL statements issued during the session.
    
15. SQL object creations count
    This is the number of times an SQL object was created for the SQL statements issued during the session.
    
16. Maximum size of SQL object created (bytes)
    This is the maximum size of the SQL object created with the SQL statements issued during the session.
    
17. COMMIT statement executions count during the session.
18. ROLLBACK statement executions count during the session.
19. Number of retrieval rows passed to UAP by the FETCH and SELECT statements during the session.
20. Number of rows deleted by the DELETE statements during the session.
21. Number of rows inserted by the INSERT statements during the session.
22. Number of rows updated by the UPDATE statements during the session.
23. Preprocessing time during the session.
24. OPEN statement executions count during the session.
25. FETCH statement executions count during the session.
26. CLOSE statement executions count during the session.
27. DESCRIBE statement executions count during the session.
28. SELECT statement executions count during the session.
29. INSERT statement executions count during the session.
30. UPDATE statement executions count during the session.
31. DELETE statement executions count during the session.
32. LOCK statement executions count during the session.
33. CREATE TABLE executions count during the session.
34. DROP TABLE executions count during the session.
35. ALTER TABLE executions count during the session.
36. CREATE INDEX executions count during the session.
37. DROP INDEX executions count during the session.
38. COMMENT (TABLE) executions count during the session.
39. COMMENT (COLUMN) executions count during the session.
40. CREATE SCHEMA executions count during the session.
41. DROP SCHEMA executions count during the session.
42. GRANT RDAREA executions count during the session.
43. GRANT SCHEMA executions count during the session.
44. GRANT access privilege executions count during the session.
45. GRANT CONNECT executions count during the session.
46. GRANT DBA executions count during the session.
47. REVOKE RDAREA executions count during the session.
48. REVOKE SCHEMA executions count during the session.
49. REVOKE access privilege executions count during the session.
50. REVOKE CONNECT executions count during the session.
51. REVOKE DBA executions count during the session.
52. CREATE VIEW executions count during the session.
53. DROP VIEW executions count during the session.
54. PURGE TABLE statement executions count during the session.
55. CREATE PROCEDURE executions count during the session.
56. DROP PROCEDURE executions count during the session.
57. ALTER PROCEDURE executions count during the session.
58. CALL statement executions count during the session.
59. DESCRIBE statement (INPUT) executions count during the session.
60. Other SQL executions count during the session.
61. Maximum input/output time (seconds).
62. Maximum input/output time (microseconds).
63. Maximum input/output time (seconds).
64. Maximum input/output time (microseconds)
    Check whether the input and output times are appropriate. If input/output processing takes longer than necessary, obtain and check the hardware log for any hardware errors.
    If you used the asynchronous READ facility, the input and output times for batch look-ahead reading by the asynchronous READ process are not included.
    
65. Cumulative input/output time for database (seconds).
66. Cumulative input/output time for database (microseconds)
    Use this information to determine whether the cause is input/output or CPU.
    If you used the asynchronous READ facility, the input and output times for batch look-ahead reading by the asynchronous READ process are not included.
    
67. Data, index, and directory page references count
    This is the number of times a data, index, or directory page was referenced from this UAP.
    
68. Data, index, and directory page updates count
    This is the number of times a data, index, or directory page was updated from this UAP.
    
69. Data, index, and directory page buffer hits count
    This is the number of times a requested data, index, or directory page was found in the buffer. If the hit rate ((item 69 item 67) 100) is low, obtain the global buffer statistical information and tune the global buffer with a low hit rate. In this case, all global buffers are subject to tuning except for the LOB global buffer.
    
70. Data, index, and directory page real READs count
    This is the number of times a data, index, or directory page was actually read by this UAP.
    If you are using the prefetch facility, the number of look-ahead READs by the prefetch facility is included. If you used the asynchronous READ facility, the number of look-ahead READs by the asynchronous READs process is also included.
    If the buffer hit rate is low, the READs count becomes high.
    
71. Data, index, and directory page real WRITEs count
    This is the number of times a data, index, or directory page was actually written by this UAP. If the commit output facility is used, this count includes the number of outputs to the database during commit processing.
    
72. LOB page references count
    This is the number of times a LOB page was referenced by this UAP. This count includes the LOB data and plug-in retrieval operations.
    
73. LOB page updates count
    This is the number of times a LOB page was updated by this UAP. This count includes the LOB data and plug-in update operations.
    
74. LOB page reference buffer hits count
    This is the reference buffer hits count. This information is applicable if the LOB global buffer is used. If the hit rate ((item 74 item 72) 100) is low, obtain the global buffer statistical information and tune the global buffer with a low hit rate. In this case, the LOB global buffer is subject to tuning. If the LOB global buffer is not used, the hit rate is 0.
    
75. LOB page update buffer hits count
    This is the update buffer hits count. This information is applicable if the LOB global buffer is used. If the hit rate (item 75 item 73 100) is low for LOB data updating or plug-in index updating, obtain the global buffer statistical information and tune the global buffer with a low hit rate. In this case, the LOB global buffer is subject to tuning. If the LOB global buffer is not used, the hit rate is 0. Update buffer hits are not applicable to addition of new LOB data.
    
76. LOB page real READs count
    This is the number of times a LOB page was actually read by this UAP. If the LOB global buffer is used and the READ buffer hit rate is low, the READs count becomes high.
    
77. LOB page real WRITEs count
    This is the number of times a LOB page was actually written by this UAP. When updating a plug-in index, you can reduce the real WRITEs count by using the LOB global buffer.
    
78. Global buffer flushes count
    This is the number of times the buffer was flushed to create space for a new page. This indicates the number of times a page was swept out of memory because the buffer was full.²
    
79. Global buffer READ waits count
    This is the number of times the UAP was placed on wait status because a page in the global buffer was being read from a HiRDB file by another user. This indicates the number of times the UAP was placed on wait status until a READ operation was completed because the page to be referenced was under READ operation by another user.²
    
80. Global buffer WRITE waits count
    This is the number of times the UAP was placed on wait status because a page in the global buffer was being output to a HiRDB file by another user. This indicates the number of times the UAP was placed on wait status until a WRITE operation was completed because the page to be updated was under WRITE operation by another user.²
    
81. Global buffer lock release waits count
    This is the number of times the UAP was placed on wait status because a page in the global buffer was in use by another user. This indicates the number of times the UAP was placed in wait status until update processing was completed because the page to be referenced or updated was under update processing by another UAP.²
    
82. Maximum work table files count
    This is the maximum number of work table files used by this UAP.³ You can determine the validity of the -l option value (maximum number of files) specified in the pdfmkfs command. The value of the -l option must satisfy the following condition:⁴
    Value of -l option total number of work table files for all UAPs that are executed concurrently + 20
    
83. Maximum work table file extensions count
    This is the maximum number of work table file extensions for this UAP. You can determine the validity of the -e option value (maximum number of extensions) specified in the pdfmkfs command. The value of the -e option must satisfy the following condition:⁴
    Value of -e option total number of work table file extensions for all UAPs that are executed concurrently
    
84. Maximum size of work table file (MB)
    This is the maximum size of a work table file for this UAP. You can determine the validity of the -n option value (maximum number of extensions) specified in the pdfmkfs command. The value of the -n option must satisfy the following condition:⁴
    Value of -n option total size of work table files for all UAPs that are executed concurrently + management area size for HiRDB file system area
    
85. Work table file READs count
    This is the number of times work table data was input from file to buffer.¹
    
86. Work table file WRITEs count
    This is the number of times work table data was output from buffer to file.¹
    
87. Forced outputs count for the work table buffer
    This is the number of times buffer contents in use were forcibly output to a file due to a shortage of the work table buffer.¹ If this value is not 0, increase the value of the pd_work_buff_size operand (size of the work table buffer) in the system definitions.
    
88. Estimated value for expanding hash table in batch mode (KB)
    This is the estimated size of the hash table required to expand the processed hash data in batch mode during hash join or subquery hash execution.³
    If the size of the hash table is greater than this value, batch hash join is assumed, which does not involve any packet division.⁵ If this value exceeds the specified range of the hash table size, batch hash join is not possible. If this value is 0, hash join or subquery hash execution has not taken place.
    
89. Maximum packet size at level 1 (KB)
    This is the maximum packet size after level 1 packet division during hash join or subquery hash execution.³
    If the size of the hash table is at least this value, packet division was completed at level 1. If the packet division level is 2 or more, you can complete the packet division at level 1 by specifying this value as the hash table size.⁶ For a batch hash join that does not involve any packet division, this value is 0.
    
90. Maximum packet size at level 2 (KB)
    This is the maximum packet size after level 2 packet division during hash join or subquery hash execution.³
    If the size of the hash table is at least this value, packet division was completed at level 2. If the packet division level is 3 or more, you can complete the packet division at level 2 by specifying this value as the hash table size.⁶ If level 2 packet division did not take place, this value is 0.
    
91. Maximum packet size at level 3 (KB)
    This is the maximum packet size after level 3 packet division during hash join or subquery hash execution.³
    If the size of the hash table is at least this value, data was processed in packets with a maximum level of 3. If the hash table size is not greater than this value, a packet was partially expanded in the hash table, thereby adversely affecting the processing efficiency. In this case, specify at least this value as the hash table size.⁶ Alternatively, performance may improve by avoiding the hash join or subquery hash execution. If level 3 packet division did not take place, this value is 0.
    
92. Unsuccessful page searches count during free space reusage execution
    This is the number of times that the mode was returned to new page allocation mode because the free space reusage facility was unable to find reusable free space when the mode was switched from new page allocation mode to free page reuse mode. If this value is a value other than 0, an inefficient page search process may have occurred during an update or insertion process executed by the UAP. For details about the free space reusage facility, see the HiRDB Version 8 Installation and Design Guide.
    
93. Mode switches count from new page allocation mode to free page reuse mode
    This is the number of times that the mode was switched from new page allocation mode to free page reuse mode when the free area reusage facility was executed. If this value is close to the number of update and insertion processes executed by the UAP, an inefficient page search process may have occurred.
    
94. Cache buffer shortage occurrences count
    This is internal information used by the system.
    
95. Cache buffer allocation flushes count
    This is internal information used by the system.
    
96. WRITEs count during cache buffer area allocation flushing
    This is internal information used by the system.
    
97. Maximum cache buffer allocation flushes count
    This is internal information used by the system.
    
98. Average cache buffer allocation flushes count
    This is internal information used by the system.
    
99. Data and index page references count when local buffer used
    This is the number of times a data or index page was referenced from this UAP.
    
100. Data and index page updates count when local buffer used
     This is the number of times a data or index page was updated from this UAP.
     
101. Data and index page buffer hits count in local buffer
     This is the buffer hits count for data pages and index pages.
     If the buffer hit rate (101 99 100) is low for a UAP that performs random access, tune the buffer.
     
102. Data and index page real READs count when local buffer is used
     This is the number of times a data or index page was actually read by this UAP.
     If the prefetch facility is being used, the number of look-ahead READs by the prefetch facility is also included. If the buffer hit rate is low, the READs count becomes high.
     
103. Data and index page real WRITEs count when local buffer is used
     This is the number of times a data or index page was actually written by this UAP.
     
104. Local buffer flush count
     This is the number of times the buffer was flushed to create space for a new page. This indicates the number of times a page was swept out of memory because the buffer was full.
     
105. Asynchronous READ request count
     This is the number of times the asynchronous READ process requested a batch look-ahead read processing when the asynchronous READ facility was used.
     
106. Synchronization wait count during asynchronous READ
     This is the number of times a synchronization wait occurred while the asynchronous READ process performed a batch look-ahead read when the asynchronous READ facility was used.
     
107. Cumulative synchronization wait time during asynchronous READ
     This is the cumulative wait time (seconds) of the synchronization waits that occurred while the asynchronous READ process performed a batch look-ahead read when the asynchronous READ facility was used.
     
108. Cumulative synchronization wait time during asynchronous READ
     This is the cumulative wait time (microseconds) of the synchronization waits that occurred while the asynchronous READ process performed a batch look-ahead read when the asynchronous READ facility was used.
     
109. Average synchronization wait time during asynchronous READ
     This is the average wait time (seconds) of the synchronization waits that occurred while the asynchronous READ process performed a batch look-ahead read when the asynchronous READ facility was used.
     
110. Average synchronization wait time during asynchronous READ
     This is the average wait time (microseconds) of the synchronization waits that occurred while the asynchronous READ process performed a batch look-ahead read when the asynchronous READ facility was used.
     
111. Average synchronous input/output time during asynchronous READ
     This is the average synchronous READ time (seconds) for initial batch reads of the first page when the asynchronous READ facility was used.
     
112. Average synchronous input/output time during asynchronous READ
     This is the average synchronous READ time (microseconds) for initial batch reads of the first page when the asynchronous READ facility was used.
     
113. Maximum comparison count³ during hash table search processing in hash join, subquery hash execution
     This is the maximum number of comparisons for data items that have the same hash value in one hash table search.
     
114. Total comparison count¹ during hash table search processing in hash join, subquery hash execution
     This is the total number of comparisons for data items that have the same hash value during hash table search processing.
     
115. Total hash table search count¹ in hash join, subquery hash execution
     This is the number of times the hash table is searched.
     

¹ For HiRDB/Parallel Server, this is the total of all servers.

² This is the sum of all global buffers.

³ For HiRDB/Parallel Server, this is the maximum value of each back-end server.

⁴ More resources than the value obtained from the formula may be required due to temporary fragmentation. Therefore, specify a sufficient value.

⁵ If the hash table size increases, the number of packet divisions may increase; therefore, a bigger hash table may be required than when the tuning information was obtained. If you have increased the hash table size on the basis of this tuning information, obtain the tuning information again. If an expected result is not obtained, you need to increase the hash table size again on the basis of the obtained tuning information.

⁶ If the hash table size increases, the number of packet divisions may increase; therefore, a smaller hash table may be enough to complete packet division at an intended level than when the tuning information was obtained. On the other hand, if you reduce the hash table size, the number of packet divisions may decrease; therefore, packet division may not be completed at the same level as when the tuning information was obtained. Therefore, use the tuning information for the purpose of increasing the hash table size.