pdobils (Display SQL object buffer statistics)
Function
The pdobils command displays statistical information about the SQL objects stored in the SQL object buffer. This information enables you to identify SQL objects that take a long time to process or that are input and output frequently; you can also obtain the object reuse status of SQL objects.
Executor
Format
pdobils [-s server-name] [-r] |
Operands
- -s server-name
<identifier> ((1-8))
Specifies the name of a server (single server or front-end server) whose information is to be displayed or whose statistical information counters are to be initialized.
Specifies that the statistical information counters are to be initialized. For example, this option is specified when overflow occurs in statistical information counters.*
For details about the items for which counters are initialized, see Output format.
- * When an SQL object is reused many times, its counter in the SQL object buffer may overflow. When a counter overflows, * is displayed for the corresponding item in the statistical information.
Rules
- The pdobils command can be executed only when HiRDB is running.
- The pdobils command can be executed at any server machine.
Notes
- The execution results of the pdobils command can be confirmed from the return code after the command has executed. Return code 0 indicates normal termination, and return code 4 indicates abnormal termination.
- Because the pdobils command manipulates the SQL object buffer, the overhead associated with this manipulation may increase the HiRDB workload.
- When the pdobils command is terminated forcibly, SQL processing on the SQL object buffer and post-processing may be placed in wait status for up to 1 minute. For this reason, it is unwise to terminate the pdobils command forcibly.
- The amount of time required for an SQL object to execute is recorded in the SQL object buffer when the transaction is completed or the SQL object is released. Figure 2-5 illustrates the SQL object execution time (duration) that is displayed when the pdobils command is executed.
Figure 2-5 SQL object execution time (duration) displayed when the pdobils command is executed
![[Figure]](figure/zu020140.gif)
- Explanation
- The display of the amount of time (duration) it takes an SQL object to execute depends on the timing of execution of the pdobils command. The following table shows the validity of the time that is displayed for each point of execution:
Execution sequence of pdobils command | SQL1 | SQL2 | SQL3 | SQL4 |
---|
1 | N | N | N | N |
2 | Y | Y | N | N |
3 | Y | Y | Y | Y |
- Legend:
- Y: The displayed execution duration time is valid for this SQL object.
- N: The displayed execution duration time is not valid for this SQL object.
- Note 1
- When the cursor is used, the displayed execution duration time is the server's total processing time from opening to closing of the cursor.
- Note 2
- While the pdobils command is executing, the SQL object buffer is used by other UAPs. If there is an SQL object whose transaction is completed during execution of the pdobils command and the corresponding information is available, the information is applied to the output information.
- Note 3
- The execution time (duration) is displayed in seconds (rounded off to a whole second).
Output format
pdobils VV-RR-ZZ [70]
<< SQL OBJECT CACHE INFORMATION >> DATE: 2004/01/01 TIME: 00:00:00 [1]
HOST NAME : node01 [2]
SERVER NAME : fes01 [3]
CACHE SIZE(KB) : 2048 [4]
SQL OBJECT TOTAL SIZE(B) : 1932556 [5]
<< SQL OBJECT LIST >>
*SQL OBJECT NO : 1 [6]
STATUS : ACTIVE [7]
TYPE : STATIC SQL [8]
SIZE : 7792 [9]
EXECUTE COUNT : 10 [10]
EXECUTE TIME AVG(s) : 1.234567 [11]
EXECUTE TIME MAX(s) : 9.000000 [12]
# SERVICE NAME : [13]
# UAP NAME : Unknown [14]
# CONNECT NO : 123 [15]
# SQL NO : 456 [16]
# RECORD DATE/TIME : 2004/01/01 00:00:00 [17]
DB REFERENCE GET COUNT : 55500 [28] AVG 5550 [29] MAX 5600 [30]
DB UPDATE GET COUNT : 0 [31] AVG 0 [32] MAX 0 [33]
DB READ COUNT : 800 [34] AVG 80 [35] MAX 85 [36]
DB WRITE COUNT : 0 [37] AVG 0 [38] MAX 0 [39]
LOB REFERENCE GET COUNT : 0 [40] AVG 0 [41] MAX 0 [42]
LOB UPDATE GET COUNT : 0 [43] AVG 0 [44] MAX 0 [45]
LOB READ COUNT : 0 [46] AVG 0 [47] MAX 0 [48]
LOB WRITE COUNT : 0 [49] AVG 0 [50] MAX 0 [51]
LIST REFERENCE GET COUNT : 0 [52] AVG 0 [53] MAX 0 [54]
LIST UPDATE GET COUNT : 0 [55] AVG 0 [56] MAX 0 [57]
LIST READ COUNT : 0 [58] AVG 0 [59] MAX 0 [60]
LIST WRITE COUNT : 0 [61] AVG 0 [62] MAX 0 [63]
WKFILE READ COUNT : 0 [64] AVG 0 [65] MAX 0 [66]
WKFILE WRITE COUNT : 0 [67] AVG 0 [68] MAX 0 [69]
PREPROCESSOR USER : user1 [18]
PREPROCESSOR SOURCE : abc.ec [19]
SECTION NO : 123 [20]
ISOLATION LEVEL : 2 [21]
OPTIMIZE LEVEL : 10 [22]
ADDITIONAL OPTIMIZE LEVEL : 0 [23]
DEFAULT SCHEMA : [24]
ROUTINE ID : 123 [25]
SQL : SELECT GNO,GNAME,KIKAKU,TANKA,SURYO,GENKA FROM ZAIKO [26]
<< SQL OBJECT STATUS COUNT >> [27]
STATUS | TYPE STATIC SQL DYNAMIC SQL ROUTINE TOTAL
ACTIVE : 0 0 0 0
LRU : 0 0 0 0
TEMPORARY : 0 0 0 0
COMPILE/TRANSFER : 0 0 0 0
PROCESS : - - - 0
RELEASE : - - - 0
TOTAL : 0 0 0 0 |
- Note 1
- The information that is displayed depends on the server's type and status.
- Note 2
- Items 6 through 69 (except for item 27) are repeated for each SQL object. The information that is displayed depends on the type and status. For details, see Notes.
- Note 3
- When the -s option is omitted, items 1 through 69 are displayed for each single server or front-end server.
- Note 4
- When the -r option is specified, items 10 through 17 and 28 through 69 are initialized.
- Explanation
- Statistical information display time
Displays the time the statistical information was displayed.
- Host name
Displays the name of the host for which statistical information was acquired.
- Server name
Displays the name of the server for which statistical information was acquired.
- Size of SQL object buffer
Displays the size of the SQL object buffer (in kilobytes).
- Total size of SQL objects
Displays the total size of SQL objects (in bytes) stored in the SQL object buffer. If the SQL object buffer is used by another UAP during execution of the pdobils command, the sum of the sizes of each SQL object may not match the total size of all SQL objects.
- SQL object number
Displays the management number of the SQL object.
- Status
Displays one of the following, indicating the status of the SQL object:
Status | Description |
---|
ACTIVE | A UAP is using the SQL object. |
LRU | Although the SQL object is stored in the SQL object buffer, no UAP is currently using it. |
TEMPORARY | The SQL object is not shared among processes. This is applicable when either of the following is true:
- Creation of the SQL object is specified in the PDVWOPTMODE or PDUAPREPLVL client environment definition.
- A dynamic SQL object is stored in the SQL object buffer.
|
COMPILE/TRANSFER | A static SQL object is being created, or the SQL object is being transferred from the front-end server to the back-end server or dictionary server. |
PROCESS | The SQL object is being managed in the process memory. |
RELEASE | The SQL object is being released or is no longer valid. |
- Type
Displays the type of the SQL object:
STATIC SQL: Static SQL object
DYNAMIC SQL: Dynamic SQL object
ROUTINE: Routine SQL object
- Size
Displays the size of the SQL object (in bytes).
- Execution count4
Displays the number of times the SQL object has executed.
- Average execution time
Displays the average execution time for the SQL object (in seconds).
- Maximum execution time
Displays the maximum execution time for the SQL object (in seconds).
- Service name
When UAPs were executed in an OLTP environment, displays the name of the service that recorded the maximum execution time.
- UAP name
Displays the name of the UAP that recorded the maximum execution time (not displayed if the UAP name cannot be identified).
- CONNECT sequence number4
Displays the CONNECT sequence number of the UAP that recorded the maximum execution time.
- SQL number4
Displays the number of the SQL that recorded the maximum execution time.
- Recorded time
Displays the time at which the maximum execution time was recorded.
- Preprocessing user's authorization identifier
Displays the authorization identifier of the user who executed preprocessing of the UAP source program. This item is displayed for static SQL objects.
- Preprocessed source program name
Displays the name of the source program that was preprocessed (this item is displayed for static SQL objects).
- Section number
Displays the section number (this item is displayed for static SQL objects).
- Data guarantee level
Displays the data guarantee level (this item is displayed for static and dynamic SQL objects).
- SQL optimization option
Displays the SQL optimization option (this item is displayed for static and dynamic SQL objects).
- SQL extension optimizing option
Displays the SQL extension optimizing option (this item is displayed for static and dynamic SQL objects).
- Default schema name
Displays the assumed authorization identifier when the authorization identifier is omitted from the SQL statement (this item is not displayed when an authorization identifier is specified in the SQL statement) (this item is displayed for dynamic SQL objects).
- Routine's object ID
Displays the routine's object ID (this item is displayed for routine SQL objects).
- SQL statement
Displays the SQL statement (this item is displayed for static and dynamic SQL objects).
- Type and number of SQL objects for each status
Displays for each status the number of SQL objects of each type that are being managed in the SQL object buffer.
- Reference count for data, index, and directory pages (total)2, 4
Displays the total number of times that buffer acquisition requests were issued to the global buffer using the SQL object for referencing purposes.
- Reference count for data, index, and directory pages (average)1, 2, 4
Displays for the SQL object the average number of times that acquisition requests for referencing were issued to the global buffer.
- Reference count for data, index, and directory pages (maximum)2, 4
Displays for the SQL object the maximum number of times that acquisition requests for referencing were issued to the global buffer.
- Update count for data, index, and directory pages (total)2, 4
Displays the total number of times that buffer acquisition requests for updating were issued to the global buffer.
- Update count for data, index, and directory pages (average)1, 2, 4
Displays for the SQL object the average number of times that acquisition requests for updating were issued to the global buffer.
- Update count for data, index, and directory pages (maximum)2, 4
Displays the maximum number of times that buffer acquisition requests for updating were issued to the global buffer.
- Real READ count for data, index, and directory pages (total)2, 3, 4
Displays the total number of input operations that were performed on data, index, and directory pages. This value includes the number of input operations performed by the prefetch facility, but does not include input operations performed by the asynchronous READ facility.
- Real READ count for data, index, and directory pages (average)1, 2, 3, 4
Displays for the SQL object the average number of input operations that were performed on data, index, and directory pages. This value includes the number of input operations performed by the prefetch facility, but does not include input operations performed by the asynchronous READ facility.
- Real READ count for data, index, and directory pages (maximum)2, 3, 4
Displays for the SQL object the maximum number of input operations that were performed on data, index, and directory pages. This value includes the number of input operations performed by the prefetch facility, but does not include input operations performed by the asynchronous READ facility.
- Real WRITE count for data, index, and directory pages (total)2, 3, 4
Displays the total number of output operations that were performed on data, index, and directory pages. This value does not include the number of output operations performed by deferred write processing.
- Real WRITE count for data, index, and directory pages (average)1, 2, 3, 4
Displays for the SQL object the average number of output operations that were performed on data, index, and directory pages. This value does not include the number of output operations performed by deferred write processing.
- Real WRITE count for data, index, and directory pages (maximum)2, 3, 4
Displays for the SQL object the maximum number of output operations that were performed on data, index, and directory pages. This value does not include the number of output operations performed by deferred write processing.
- LOB column data page reference count (total)4
Displays the total number of times that buffer acquisition requests were issued to reference LOB column data pages.
- LOB column data page reference count (average)1, 4
Displays for the SQL object the average number of times that acquisition requests were issued to reference LOB column data pages.
- LOB column data page reference count (maximum)4
Displays for the SQL object the maximum number of times that acquisition requests were issued to reference LOB column data pages.
- LOB column data page update count (total)4
Displays the total number of times that buffer acquisition requests were issued to update LOB column data pages.
- LOB column data page update count (average)1, 4
Displays for the SQL object the average number of times that buffer acquisition requests were issued to update LOB column data pages.
- LOB column data page update count (maximum)4
Displays for the SQL object the maximum number of times that buffer acquisition requests were issued to update LOB column data pages.
- Real READ count for LOB column data pages (total)4
Displays the total number of real READ operations that were performed on LOB column data pages.
- Real READ count for LOB column data pages (average) 1, 4
Displays for the SQL object the average number of real READ operations that were performed on LOB column data pages.
- Real READ count for LOB column data pages (maximum)4
Displays for the SQL object the maximum number of real READ operations that were performed on LOB column data pages.
- Real WRITE count for LOB column data pages (total)4
Displays the total number of real WRITE operations that were performed on LOB column data pages.
- Real WRITE count for LOB column data pages (average)1, 4
Displays for the SQL object the average number of real WRITE operations that were performed on LOB column data pages.
- Real WRITE count for LOB column data pages (maximum)4
Displays for the SQL object the maximum number of real WRITE operations that were performed on LOB column data pages.
- List page reference count (total)4
Displays the total number of times that buffer acquisition requests were issued to the global buffer to reference list pages.
- List page reference count (average)1, 4
Displays for the SQL object the average number of times that buffer acquisition requests were issued to the global buffer to reference list pages.
- List page reference count (maximum)4
Displays for the SQL object the maximum number of times that buffer acquisition requests were issued to the global buffer to reference list pages.
- List page update count (total)4
Displays the total number of times that buffer acquisition requests were issued to the global buffer to update list pages.
- List page update count (average)1, 4
Displays for the SQL object the average number of times that buffer acquisition requests were issued to the global buffer to update list pages.
- List page update count (maximum)4
Displays for the SQL object the maximum number of times that buffer acquisition requests were issued to the global buffer to update list pages.
- Real READ count for list pages (total)4
Displays the total number of real READ operations that were performed on list pages.
- Real READ count for list pages (average)1, 4
Displays for the SQL object the average number of real READ operations that were performed on list pages.
- Real READ count for list pages (maximum)4
Displays for the SQL object the maximum number of real READ operations that were performed on list pages.
- Real WRITE count for list pages (total)4
Displays the total number of real WRITE operations that were performed on list pages.
- Real WRITE count for list pages (average)1, 4
Displays for the SQL object the average number of real WRITE operations that were performed on list pages.
- Real WRITE count for list pages (maximum)4
Displays for the SQL object the maximum number of real WRITE operations that were performed on list pages.
- READ count for work table files (total)4
Displays the total number of READ operations that were performed on work table files.
- READ count for work table files (average)1, 4
Displays for the SQL object the average number of READ operations that were performed on work table files.
- READ count for work table files (maximum)4
Displays for the SQL object the maximum number of READ operations that were performed on work table files.
- WRITE count for work table files (total)4
Displays the total number of WRITE operations that were performed on work table files.
- WRITE count for work table files (average)1, 4
Displays for the SQL object the average number of WRITE operations that were performed on work table files.
- WRITE count for work table files (maximum)4
Displays for the SQL object the maximum number of WRITE operations that were performed on work table files.
- HiRDB version
When there is no ZZ, only VV-RR is displayed.
- 1 The average value is rounded off. If the total value overflows, **** is displayed as the average value.
- 2 This does not include accesses to data dictionary tables that store definition information, such as for tables and indexes.
- 3 Deferred write processing involves writing pages updated in the global buffer to disk when the number of updated pages reaches a specified value, rather than when the COMMIT statement is issued. Deferred write processing outputs updated pages to HiRDB files asynchronously with the database processes.
- The prefetch facility enables database processes to read multiple pages of table data on disk volumes in batch mode. The asynchronous READ facility reads multiple pages of table data on disk volumes in batch mode asynchronously with the database processes.
- 4 The counter may overflow. If an overflow has occurred, * is displayed as the lead digit of the corresponding counter. Counting continues when an overflow occurs.
Notes
How to interpret statistical information
The following describes how to interpret statistical information.
- PROCESS in item 27
If a TOTAL count is provided for SQL objects whose STATUS is PROCESS, the SQL objects are being managed for each process because SQL objects cannot be stored in the SQL object buffer.
If SQL objects are always being counted, you should re-evaluate the size of the SQL object buffer.
- DYNAMIC in item 27
- Part 1
If a STATIC SQL count is provided for SQL objects whose STATUS is TEMPORARY, UAPs for which the PDUAPREPLVL client environment definition has been set are being executed.
If PDUAPREPLVL is not needed for a UAP, you should delete the PDUAPREPLVL specification and then execute the UAP.
- Part 2
If a DYNAMIC SQL count is always provided for SQL objects whose STATUS is TEMPORARY, UAPs for which the PDUAPREPLVL and PDVWOPTMODE client environment definitions have been set are being executed.
If PDUAPREPLVL and PDVWOPTMODE are not needed for a UAP, you should delete the PDUAPREPLVL and PDVWOPTMODE specifications and then execute the UAP.
- Identifying the SQL statement that is executing
If the SQL object buffer is adequate, you can identify the SQL statement that is executing by referencing the statistical information by means of the procedure described below. To be able to identify the SQL statement that is executing, you must omit PDUAPREPLVL and PDVWOPTMODE and you must specify REUSE.
- Identify the SQL object whose STATUS is ACTIVE.
- Reference the SQL object identified in 1. In the case of a static SQL object, you can also identify the UAP that is executing the SQL object. Reference the SQL object's PREPROCESSOR USE and PREPROCESSOR SOURCE. The UAP using the preprocessing source is the one that is executing the SQL statement.
- About SQL objects subject to tuning
Reference the items listed below. The SQL statements for which a large value is shown should be considered for tuning.
- SQL object's execution count
- SQL object's average execution time
- SQL object's maximum execution time
- Reference count for data, index, and directory pages
- Update count for data, index, and directory pages
- Real READ count for data, index, an directory pages
- Real WRITE count for data, index, and directory pages
- READ count for work table files
- WRITE count for work table files
For details about tuning, see the discussion of tuning SQL objects that take a long time to execute in the HiRDB Version 8 System Operation Guide.