ODBC Server Installation and Management Manual

Managing NonStop ODBC Server Resources
HP NonStop ODBC Server Installation and Management Manual—429395-002
5-8
Usage Considerations
•
SQL/MP execution cost includes only the master executor time.
Currently, the SQL/MP master executor does not record the process time of its
child processes. To obtain a reasonable set of data for cost estimation, both the
run-time statistics generated by the SQL/MP executor and the process time in the
NonStop ODBC Server components must be recorded. The statistics can then be
used to predict resource cost for an SQL statement.
•
SQL/MP statement execution includes the following run-time statistics:
•
Records accessed
•
Records used
•
Disk reads
•
Message counts
•
Message bytes
•
Number of lock waits (concurrency cost)
•
Number of times record locks are escalated to file locks
•
Gateway messages and message bytes between the SCS process and the
NonStop ODBC Server are also collected.
Certain queries require temporary tables to be used by the SQL/MP executor. The
statistics returned in the SQLSA contain the resource used in processing both the SQL
tables and the temporary tables. Therefore, the total rows accessed can at times
appear to be greater than the number of rows in the SQL tables.
When a transaction is rolled back, the resource accounting data for the statements
executed is still recorded; it is not backed out.
Usage Considerations
The following subsections describe things to be considered when using the resource
accounting facility.
Timing of Resource Accounting
The resource accounting data includes all types of statements. Depending on how the
resource accounting is configured, however, this data can be written out either at the
end of a session (per-session basis) or after each statement is executed (per-
statement basis).
Record Data for an Entire Session
If a per-session basis is specified for the ACC_LEVEL attribute, all metrics are
collected and accumulated after each statement execution, but data is written to the
table only once, at the end of the session when the client disconnects. Certain data,
such as CACHED, STATEMENT_TYPE, and STATEMENT_TEXT, is blanked out.