This JSON-based API reports the current best set of missing (new) statistics recommendations along with supporting evidence about their estimated benefit. Callers can limit the report to a specified set of objects or maximum ranking.
Syntax
REPLACE PROCEDURE TDSTATS.MissingStatRecs ( IN jln JSON(2000) CHARACTER SET UNICODE) DYNAMIC RESULT SETS 1
Syntax Elements
MissingStatRecs is implemented as a SQL procedure with input parameters in the form of JSON key-value pairs and output in the form of Dynamic SQL result.
| JSON Key | IN/OUT | Data Type | Description |
|---|---|---|---|
| DatabaseName | IN | VARCHAR(128) | Limit recommendations to those on a specified database. |
| ObjectName | IN | VARCHAR(128) | Further limit recommendations to those on specified table of DatabaseName. |
| ObjectListName | IN | VARCHAR(128) | Limit recommendations to named list of objects. See CreateObjectList. |
| MaxRank | IN | INTEGER | Limit returned recommendations to those with a ranking less than or equal to the specified value. |
| Dynamic SQL Result Column | Data Type | Description | |
|---|---|---|---|
| Rank | OUT | INTEGER | Ranking of recommendation relative to others. The best recommendations has a ranking of 1. |
| SCOID | OUT | BIGINT | TDStats unique numeric identifier for the stat recommendation. |
| DatabaseName | OUT | VARCHAR(128) | Database on which the recommendation was made. |
| TableName | OUT | VARCHAR(128) | Table on which the recommendation was made. |
| FieldNames | OUT | VARCHAR(10000) | Comma separated list of Field names on which the recommendation was made. |
| StatType | OUT | CHAR(1) |
|
| Status | OUT | VARCHAR(128) | Status that indicates if recommendation is awaiting approval or already approved but still awaiting application. |
| MostRecentTimeStamp | OUT | TIMESTAMP(6) | Timestamp of when recommendation was most recently made. |
| TotalQueries | OUT | BIGINT | Total number of queries expected to benefit from recommendation. |
| CardinalityEstimateErrors | OUT | BIGINT | Number of relevant queries that had Optimizer cardinality estimation errors. |
| MissedSLGFrequency | OUT | BIGINT | Name of relevant queries that missed their Service Level Goal (SLG). |
| AvgOptimizerImportance | OUT | VARCHAR(10) | Optimizer assigned importance of the recommendation. |
| PlanCostMagnitude | OUT | INTEGER | Average cost of relevant queries expressed as number of digits in cost. |
| RecDDLText | OUT | VARCHAR(10000) | SQL DDL text associated with the recommendation. |
| QueryList | OUT | VARCHAR(2000) | Comma separated list of DBQL QueryIds expected to benefit from statistic. |
| Description | OUT | VARCHAR(256) | Test description of supporting evidence that is like EXPLAIN output. |
Usage Notes
- To limit the recommendations to a particular set of databases or tables, callers can specify a values for DatabaseName, TableName, or ObjectListName. Otherwise, the scope is all automated tables.
- Recommendations are returned in ranked order according to their estimated benefit to total system performance.
- Supporting evidence for each recommendation can be found in output columns:
- TotalQueries
- CardinalityEstimateErrors
- MissedSLGFrequency
- AvgOptimizerImportance
- PlanCostMagnitude
- Occur frequently across the workload.
- Are made in conjunction with plan steps whose estimated spool size are significantly different than actual.
- Are made in conjunction with queries that failed to achieve their Service Level Goal.
- Are considered to be of high importance to the Optimizer for selecting the chosen plan.
- Are made in conjunction with high cost (long running) queries.
- The Description output column contains a textual description that is similar to EXPLAIN of all the combine supporting evidence.
Example: Using MissingStatRecs
CALL TDSTATS.MissingStatRecs('{"DatabaseName":"V6_ADW"}');
Result:
*** ResultSet# 1 : 3 rows returned by "TDSTATS.MISSINGSTATRECS".
Rank 1
SCOID 11700117
DatabaseName V6_ADW
TableName ordertbl_ppi
FieldNames "O_ORDERKEY","O_TOTALPRICE","O_ORDERDATE"
StatType L
Status Approved; Awaiting Collection
MostRecentTimeStamp 2019-04-01 12:59:50.930000+00:00
TotalQueries 18
CardinalityEstimateErrors 5
MissedSLGFrequency 3
AvgOptimizerImportance 2.00000000000000E 000
PlanCostMagnitude 3
RecDDLText COLLECT STATISTICS COLUMN("O_ORDERKEY","O_TOTALPRICE","O_ORDERDATE") ON V6_ADW.ordertbl_ppi;
QueryList 307190584648047536,307190584648047537,307190584648047172,307190584648047537, ....
Description Missing stat rec on set of columns that may benefit 18 queries with 5 cardinality estimation errors and 3 missed SLGs with an
average Optimizer assigned importance of 2-High
....