The following table lists system defined jobs that perform AutoStats operations using automated scheduling provided by the AutoDb tenant service. Default schedules are subject to change based on the system's observed activity levels over time.
| Job Name | Default Schedule | Description |
|---|---|---|
| Analyze Missing Stats | Every 2 hours | Recommend missing (new) statistics by analyzing DBQL query logs. |
| Analyze Unused Stats | Weekly | Recommend dropping unused statistics by analyzing Optimizer usage counts as recorded by USECOUNT. |
| Apply Stat Recs | Every 1 hour | Apply recently approved recommendations to collect missing (new) statistics and drop unused statistics. The tuning recommendations on fully-autonomous AutoDBA marked objects are auto approved.
|
| Automate Stats | Every 1 hour | Designate objects under AutoStats control within TDStats metadata based on most recent AutoDBA data dictionary metadata. |
| InLine Event Trigger | Every 5 minutes | Trigger stats tuning based on detected InLine events such as bulk loads within the Parsing Engine. |
| Refresh Stale Stats | Every 6 hours | Analyze all automated statistics for staleness and refresh if necessary. |
Schedule Information
- Two (2) hours is the assumed period of time for logging a representative query workload for recommending missing (new) stats.
- All stats are routinely checked for potential staleness every 6 hours but tables undergoing load operations are checked within 5 minutes of load completion.
- It can take up to 1 hour for newly marked AutoDBA objects to be analyzed by jobs for missing and stale stats. However, DBQL logging and USECOUNT tracking starts immediately and will be included in the analysis. Conversely, it can take up to 1 hour for objects removed of their AutoDBA designation to be excluded by automated tuning jobs.
- DBA approved recommendations for missing and unused stats on semi-autonomous designated tables can take up to 1 hour to be submitted for application and can take longer to complete depending on the total number to be applied.
- The weekly Analyze Unused Stat job assumes stats that originated from AutoStats recommendations and not used by the Query Optimizer within the past 31 days can be safely dropped.
- Stats that originated as user defined are retained for 93 days to support non-frequent but important usage such as quarterly reports.
Job Configuration and History
Definitions of the AutoStats jobs listed above are configured in system table TDaaS_DB. Similarly, AutoStats job history is stored in table TDaaS_DB.AutoDBJobTbl.
Job As-A-Service Users
All SQL-related work performed by AutoStats Jobs is executed by As-A-Service user TDaaS_AutoDB1 (or TDaaS_AutoDB2) which is created during the installation of the AutoDB Service infrastructure. All SQL executed by these users is logged using DBQL (default level is standard in Lake) and labeled with well-defined Query Bands as summarized in the following table.
| Analytics Database User | Session Query Band | Possible Query Band Values for AutoStats Jobs |
|---|---|---|
| TDaaS_AutoDB1 TDaaS_AutoDB2 |
AutoDbaJobName | stats_automate, stats_missing, stats_unused, stats_apply, stats_stale, stats_inline |
| TDaaS_AutoDB1 TDaaS_AutoDB2 |
AutoDbaAction | stats_analysis, stats_collect, stats_drop |
| TDaaS_AutoDB1 TDaaS_AutoDB2 |
AutoDbaJobThread | 0, 1, 2, 3, 4, 5 |
The TDaaS_AutoDB1/2 users are defined with a system defined profile (TDAAS_USRPROFILELOW) that defines password policies along with an account string that informs TASM to classify the priority of its work as TimeShare Low which reduces the impact on other higher priority customer work.