SQL/MX 2.x Installation and Management Guide (H06.10+, J06.03+)

Enhancing SQL/MX Database Performance
HP NonStop SQL/MX Installation and Management Guide—544536-007
15-11
Using SCF STATS DISK to Monitor SQL/MX
Statistics and Reuse Failures
If you are running SQL/MX Release 2.0 or SQL/MX Release 2.1 and want to leverage
query plan size reduction, you must upgrade your system to SQL/MX Release 2.3.1
and then SQL compile your SQL/MX applications. For more information, see the
SQL/MX Database and Application Migration Guide.
Using SCF STATS DISK to Monitor SQL/MX Statistics and Reuse
Failures
Use the SCF STATS DISK command to monitor statistics for the SQL/MX buffer,
including the incidence of reuse failures. For example:
SCF - T9082G02 - (04MAY04) (07APR04) - 05/12/2005 22:26:46 System \DRIS1 (C)
1986 Tandem (C) 2004 Hewlett Packard Development Company, L.P.
(Invoking \DRIS1.$SYSTEM.STARTUP.SCFCSTM)
1-> stats disk $D*,sqlmx
STORAGE - Stats DISK \DRIS1.$D01001
SQL/MX Statistics:
Session Data bytes....... 131072 KB Max Data bytes... 131072 KB
Total Sessions........... 1708 Active Sessions.. 0
- 4KB Blocks - - - - - - - - - - - - Reuse - - - - - - - - - -
Max...................... 195840 Attempts......... 60155
Number................... 195840 OK............... 58214
In Use................... 53621 Failed FST....... 12
Failed ID........ 1929
•
Session Data bytes identifies the amount of virtual memory currently allocated
to SQL/MX buffer space for this DP2 process.
•
Total Sessions identifies the total number of sessions, including both active
and ended sessions.
•
Max Data bytes identifies the configured size of the SQL/MX buffer for the disk.
In this example, the disk has been configured for 131,072 KB, or 128 MB, which is
the default value.
•
Active Sessions identifies the number of SQL/MX sessions stored in the buffer
that are currently active.
•
MAX under 4KB Blocks identifies the maximum number of 4 KB blocks located in
the SQL/MX buffer for the current value of Max Data bytes.
•
Number under 4KB Blocks identifies the number of 4 KB blocks located in the
SQL/MX buffer for the current value of Session Data bytes.
•
In Use under 4KB Blocks identifies the number of 4 KB blocks in the buffer that
are currently in use or reusable.
•
Attempts under Reuse identifies the number of times an attempt has been made
to reuse plan fragments located in the SQL/MX buffer.
•
OK under Reuse identifies the number of times a successful attempt has been
made to reuse plan fragments located in the SQL/MX buffer.
•
Failed FST under Reuse identifies the number of plan fragments that are not
located in the free space table of the SQL/MX buffer and not available for reuse.