ODBC Server Installation and Management Manual

Managing the NonStop ODBC Server
HP NonStop ODBC Server Installation and Management Manual—429395-002
4-7
Performance Tuning
1. Add a trace definition to record cache statistics:
NOSCOM> ADD TRACE TRACE_CACHE
TRA_LOG_TABLENAME SYSTEM_VOL_SUBVOL.SQL_ODBC.LOG1
CACHE_STATISTICS Y
2. Change the user profile to reference the trace definition:
NOSCOM> MODIFY PROFILE CACHE TRACE_NAME TRACE_CACHE
At the next polling cycle, the NOS server sets creates a trace record in the ZNSTRA
system table. The server reads this record and logs cache statistics to the table
specified after the LOG_TABLENAME option. It writes a record every time a statement
comes in, listing whether it found the record and what the cost was. Statistics about the
cache are written at the end of the session.
The following information is displayed in the log:
ID DEBUG_FLAG DEBUG_STRING
------ ---------- ----------------------------------------
96 LOGON DEPT_ORDER
96 TIME 1995-01-12 16:25:46
96 CACHE Number of Statements: 2
96 CACHE Number of Lookups: 3
96 CACHE Number of Hits: 1
96 CACHE Number of Drops: 0
ID is the session’s ID. A unique ID is generated for each NonStop ODBC Server
session. The following information is shown for that ID.
Performance Tuning
The issue of performance is concerned with metrics such as the response times seen
by clients when they send requests to NonStop ODBC Servers. It also deals with
resource usage on the NonStop system (CPU, memory, disk), on the LAN, and on the
client PC or workstation. Performance tuning involves altering configurations in such a
way as to increase or decrease the desired metric, such as response time, without
letting other metrics, such as memory usage, exceed necessary limits.
Two of the major areas for performance tuning, using SCS server classes and
statement caching, have been discussed previously in this section. This subsection
provides additional suggestions on how to improve the performance of the system.
Number of Statements The total number of SQL statements in the cache at the end
of the session
Lookups The number of times the cache was referenced, once for
each statement
Hits The number of lookups that were successful; that is, the
statement was found in the cache
Drops The number of times the cache was full and a statement
had to be dropped so a new statement could be cached