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.

Figure 1: Syntax:

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 2: 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.
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.