SET STATS

Go Up to isql Command Reference

Specifies whether to display performance statistics after the results of a query.

SET STATS [ON | OFF];

Argument	Description
ON	Turns on display of performance statistics
OFF	Turns off display of performance statistics [default]

Description: By default, when a SELECT statement retrieves rows from a query, isql does not display performance statistics after the results. Use SET STATS ON to change the default behavior and display performance statistics. To restore the default behavior, use SET STATS OFF. Performance statistics include:

• Current memory available, in bytes
• Change in available memory, in bytes
• Maximum memory available, in bytes
• Elapsed time for the operation
• CPU time for the operation
• Number of cache buffers used
• Number of reads requested
• Number of writes requested
• Number of fetches made

Performance statistics can help determine if changes are needed in system resources, database resources, or query optimization.

Tip: The ON and OFF keywords are optional. If they are omitted, SET STATS switches from one mode to the other. Although you can save typing by omitting the optional keyword, including the keyword is recommended because it avoids potential confusion.

Do not confuse SET STATS with the SQL statement SET STATISTICS, which recalculates the selectivity of an index.

Example: The following part of a script file turns on display of statistics and then performs a query:

SET STATS ON;
SELECT JOB_COUNTRY, MIN_SALARY FROM JOB
WHERE MIN_SALARY > 50000
AND JOB_COUNTRY = 'France';

The output displays the results of the SELECT statement and the performance statistics for the operation:

JOB_COUNTRY MIN_SALARY
=============== ======================
France 118200.00

Current memory = 407552
Delta memory = 0
Max memory = 412672
Elapsed time= 0.49 sec
Cpu = 0.06 sec
Buffers = 75
Reads = 3
Writes = 2
Fetches = 441

See Also

• SHOW DATABASE