ODBC Server Installation and Management Manual

Managing the NonStop ODBC Server
HP NonStop ODBC Server Installation and Management Manual—429395-002
4-6
Flushing the Statement Cache
•
When statement caching is enabled, the sort processes started when a SQL
statement is first executed remain active on the system for the time the statement
remains in the cache. For this reason, it is recommended that statement caching
not be used where SQL statements can start many sort processes, unless you also
use the NonStop SQL/MP DEFINE =_SQL_EXE_STOP_SORTPROGS to stop all
sort processes started on behalf of a cursor whenever the cursor is closed. This
DEFINE is described under Configuring SQL/MP on page 2-56.
•
Statement caching is not used when the SQL compiler generates a query plan that
would use parallel execution. The reason for this is that if a statement using
parallel execution were cached, the ESP and sort processes used when the
statement is executed would remain in the system until either the cache was
cleared or the NonStop ODBC Server was stopped. This scenario could result in
many ESP and sort processes remaining active on the system for extended
periods, which would not be acceptable. In addition, the savings from caching
compiled statements would normally be insignificant compared to the time required
to execute a statement with parallel execution.
•
Although a statement cache is preserved across session boundaries, each
NonStop ODBC Server has its own statement cache, which means that on a
subsequent connection a user might get a different NonStop ODBC Server with a
different statement cache. This scenario can cause unexpected performance
problems.
Flushing the Statement Cache
Flushing the statement cache means dropping all the cached statements from the
cache. The cache can be flushed as a side effect by issuing a pass-through CONTROL
statement to NonStop SQL/MP. For example:
SELECT "tdm: SQL CONTROL EXECUTOR PARALLEL EXECUTION OFF"
The CONTROL statement should be a non operative statement (in this case, parallel
execution should already be off) so that you do not influence future SQL compilations.
Any of the NonStop SQL/MP CONTROL statements have the same effect of flushing
the cache.
Displaying Cache Statistics
The field SQL_MAX_STATEMENT_CACHE in the profile indicates how many
statements should be cached per server (not per server class). Begin by configuring
the cache size to be the maximum number of DML statements in your application.
Then check cache statistics, as in the following procedure, and alter the cache size to
obtain the minimum cache size with the most number of cache hits.
To obtain cache statistics, follow these steps: