Performing the task of updating the statistics manually, might consume time, lead to a huge maintenance effort or call for administrative skills. To avoid this, you can automatically update and collect statistics through AUS during a maintenance window.

A database considers several plans to answer a query. For a given query, the query optimizer evaluates and chooses the best execution plan. The OE SQL Optimizer relies on statistics when creating a query plan. Statistics are table metadata. Statistics provide information about the distribution of column values across participating rows, helping the optimizer to estimate the number of rows, or cardinality, of the query results.

Statistics provide the optimizer with clues on the best ways to process the query and consequently maximize performance. However, the statistics used might not always be up-to-date, or altogether, the tables in question might be devoid of statistics. In such a scenario, if a SQL query is executed, the statistics used might be wrong leading to decreased performance.

To prevent such a situation, users need to update statistics.

Overview

The AUS feature ensures that all statistics are up to date. AUS updates the statistics for the needed tables, indexes and columns in the database, to ensure that the OE SQL queries are always executed on top of updated or valid statistics. The input which determines that AUS must update statistics or not, is captured when users read a table via SQL clients. So, if a user modifies only the data in a table without having any reads from the SQL clients, then the AUS server might not update the statistics of such a table.

AUS offers the following capabilities:
  • Manage and administer automatic statistics generation
  • Configure the Maintenance Window to gather statistics
  • Monitor autonomous SQL statistics generation activities
  • Specify the number of parallel tasks (threads) that can be spawned to update the statistics in parallel

AUS Architecture

AUS works in a client/server model. A dedicated AUS server process updates the statistics automatically. The SQL Server serves user connections along with a dedicated thread which is an AUS client.

The AUS client/server model is depicted in the picture below.

AUS is executed within the set of OpenEdge SQL server processes. AUS execution is enabled or disabled through a broker startup parameter and separate statement such as AUTOUPDATE STATISTICS ON. AUS is triggered only in a Maintenance Window provided by the user.

The execution of AUS tasks include:
  • Update statistic statements
  • Minimal impact on existing database clients, database resources, and system resources
The AUS is enabled through the AUS broker startup parameter and SQL statements. The broker startup parameter for AUS allows to:
  • Enable and disable the auto-stats
  • Create the Autonomous SQL Statistics policies such as the Maintenance Window
  • Expose data that describes the Autonomous SQL Statistics policies

To understand how the OpenEdge SQL Optimizer works, see Optimize query performance. For more information on the SYSSQL_PROPERTIES system catalog table, see OpenEdge SQL system catalog tables.