Applicability of SHOW STATISTICS Requests
You can use SHOW STATISTICS requests to report information on both Optimizer statistics and QCD statistics.
Rules and Guidelines for SHOW STATISTICS Requests
- Vantage groups columns or indexes having the same USING options together in a single COLLECT STATISTICS request when reporting the SQL text for a database object.
If there are multiple columns with different USING options, Vantage reports multiple COLLECT STATISTICS requests with the corresponding using options.
- The VALUES option reports detailed statistics for the specified database object. If you do not specify any columns or indexes, Vantage reports detailed statistics for all of the columns in the database object.
- When you specify the SUMMARY option, Vantage reports only the table-level summary statistics for the specified table or index.
- If you submit your SHOW STATISTICS request without also specifying the IN XML option, you can do any of the following things with the reported statistics:
- Use the detailed statistics reported by the VALUES option as a backup of those statistics.
- Submit the reported statistics to Vantage as they are reported.
- Transfer the reported statistics to other systems in dual-active environments.
- If you submit your SHOW STATISTICS request and specify the IN XML option, you can use the output for advanced processing of statistical data using methods such as graphical displays, transformations, and so on.
- Vantage displays date, time and timestamp data in UTC format.
No conversion to local time zone is done when statistics are exported.
Similarly, when imported using COLLECT STATISTICS with VALUES clause, the Optimizer assumes the incoming data is formatted in UTC and does no conversions. This makes sure that the data is consistent during exports made using SHOW STATISTICS requests and imports resubmitting the SHOW STATISTICS output to Vantage operations irrespective of the session time zone.
SEQUENCED Option
The output of the SHOW STATISTICS VALUES option is a spool with a set of output request rows. If the table-level SHOW STATISTICS VALUES, or if you specify multiple column or index sets, the response can contain output from multiple statistics. The SEQUENCED option, which is only valid if you also specify the VALUES option, enables you to report sequence numbers for each such sequence of statistics data in a spool. The database uses the sequence number to identify the boundary of the each statistics output.
For example, assume you are interested in reported detailed statistics for 3 statistics on a table, and the individual detailed statistics have the following sizes.
| Statistics Group | Size (KB) |
|---|---|
| 1 | 150 |
| 2 | 30 |
| 3 | 100 |
The database inserts the output of the 3 statistics groups into a spool with sequence numbers that can be used to maintain the correct order of the output request text. The spool for the output is chunked into sequences up to 64 KB each. The database inserts up to 64 KB of the output request text into the first sequence, and whatever remains into subsequent sequences.
The final output with sequence numbers for this example is as follows. Statistics output 2 fits into a single sequence.
| Statistics Output | Sequence Number | Size (KB) |
|---|---|---|
| 1 | 1 | 64 |
| 2 | 64 | |
| 3 | 22 | |
| 2 | 1 | 30 |
| 3 | 1 | 64 |
| 2 | 36 |
The following examples demonstrate different uses of the SEQUENCED option.
The following SHOW STATISTICS request displays all the available detailed statistics for table_1 with sequence numbers.
SHOW STATISTICS VALUES SEQUENCED ON table_1;
The following SHOW STATISTICS request displays the detailed statistics on column x1 from table_1 with sequence numbers.
SHOW STATISTICS VALUES SEQUENCED COLUMN x1 ON table_1;
The following SHOW STATISTICS request displays the detailed statistics on columns x1 and y1 from table_1 with sequence numbers.
SHOW STATISTICS VALUES SEQUENCED COLUMN x1, COLUMN y1 ON table_1;
The following SHOW STATISTICS request displays all of the available detailed statistics for table_1 with sequence numbers in XML format.
SHOW IN XML STATISTICS VALUES SEQUENCED ON table_1;
Rules, Restrictions, and Usage Guidelines for the SEQUENCED Option
- You can specify the SEQUENCED option for both the Optimizer and QCD forms of SHOW STATISTICS.
- You can only specify SEQUENCED if you also specify VALUES.
- Client utilities can use sequence numbers to identify the boundaries of each statistics output.
Locks and Concurrency
Vantage places rowhash-level READ or ACCESS locks on DBC.StatsTbl to retrieve statistics information.
Detailed Interval Statistics Can Change from One Release to the Next
Detailed QCD interval statistics represent an internal structure that is subject to change from one release to the next in terms of content, structure, number of intervals, or format (see Query Capture Facility for more information). Therefore, avoid including such SHOW STATISTICS requests in applications that cannot easily be modified.
The same is true for detailed Optimizer interval statistics, which are used internally by the Optimizer. Optimizer statistics represent an internal structure that is subject to change from one release to the next in terms of content, structure, number of intervals, or format (see Interval Histograms for more information). Avoid including SHOW STATISTICS requests for detailed Optimizer statistics in applications that you cannot modify easily.
Detailed Interval Statistics Attributes (Field Mode)
When you request a detailed SHOW STATISTICS report in Field Mode, Vantage returns one row per interval (including the master record). The rows have multiple columns. Statistics values retain the underlying data types of the columns on which they were collected.