onstat -g spf: Print prepared statement profiles

Use the onstat -g spf command to display current statistics about SQL queries.

You can use the statistics to determine the cost of each statement.

Read syntax diagramSkip visual syntax diagram
Syntax:

>>-onstat-- -g--spf--------------------------------------------><

If SQL tracing is enabled, the information that is shown is a snapshot of the work that is completed by the statement and might change as the statement continues to run. For example, to monitor the growth rate of buffer reads or writes in an active statement, you can issue three onstat -g spf runs at 2-second intervals.

If SQL tracing is disabled, a warning message is issued: "Statistics disabled".

Example output

Figure 1. onstat -g spf command output
Statement profiles
sid  sdb      tottm  execs runtm  pdq scans sorts bfrd  pgrd  bfwrt  pgwrt  lkrqs  lkwts
35   4de84028 0.01   0     0.01   0   0     0     301   352   0      512    2998   0
25   4dc0b028 0.00   0     0.00   0   0     0     0     0     0      0      0      0
...

Output description

sid
The session ID.
sdb
The last 8 digits of the statement pointer.
tottm
The current total run time, in seconds, of all statements.
execs
The current number of completed statement runs. This value does not include statements that are running.
runtm
The current run time of the statement, in seconds.
pdq
The current parallel database queries (PDQ) priority level. The PDQ priority value can be any integer from 0 through 100. For more information, see Managing PDQ queries.
scans
The current number of PDQ scans that are allocated.
sorts
The current number of completed sorts.
bfrd
The current number of buffer reads.
pgrd
The current number of page reads.
bfwrt
The current number of buffer writes.
pgwrt
The current number of page writes.
lkrqs
The current number of lock requests.
lkwts
The current number of lock waits.

Copyright© 2019 HCL Technologies Limited