Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Oracle Table Statistics and MySQL Managing Statistics

Oracle Usage

Table statistics are one of the important aspects affecting SQL query performance. They turn on the query optimizer to make informed assumptions when deciding how to generate the run plan for each query. Oracle provides the DBMS_STATS package to manage and control the table statistics, which you can collected automatically or manually.

The following statistics are usually collected on database tables and indexes:

Automatic Optimizer Statistics Collection

By default, Oracle collects table and index statistics during predefined maintenance windows using the database scheduler and automated maintenance tasks. The automatic statistics collection mechanism uses Oracle data modification monitoring feature that tracks the approximate number of INSERT, UPDATE, and DELETE statements to determine which table statistics should be collected.

In Oracle 19, you can gather real-time statistics on tables during regular UPDATE, INSERT, and DELETE operations, which ensures that statistics are always up-to-date and are not going stale.

Oracle 19 also introduces high-frequency automatic optimizer statistics collection. Use this feature to set up automatic task that will collect statistics for stale objects.

Manual Optimizer Statistics Collection

When the automatic statistics collection is not suitable for a particular use case, you can perform the optimizer statistics collection manually at several levels:

Examples

Collect statistics at the table level for the HR schema and the EMPLOYEES table.

BEGIN
DBMS_STATS.GATHER_TABLE_STATS('HR','EMPLOYEES');
END;
/

PL/SQL procedure successfully completed.

Collect statistics at a specific column level for the HR schema, the EMPLOYEES table, and the DEPARTMENT_ID column.

BEGIN
DBMS_STATS.GATHER_TABLE_STATS('HR','EMPLOYEES',
METHOD_OPT=>'FOR COLUMNS department_id');
END;
/

PL/SQL procedure successfully completed.

For more information, see Optimizer Statistics Concepts in the Oracle documentation.

MySQL Usage

Aurora MySQL supports two modes of statistics management: Persistent Optimizer Statistics and Non-Persistent Optimizer Statistics. As the name suggests, persistent statistics are written to disk and survive service restart. Non-persistent statistics are kept in memory and need to be recreated after service restart. It is recommended to use persistent optimizer statistics (the default for Aurora MySQL) for improved plan stability.

Statistics in Aurora MySQL are created for indexes only. Aurora MySQL does not support independent statistics objects on columns that are not part of an index.

Typically, administrators change the statistics management mode by setting the global parameter innodb_stats_persistent = ON. Therefore, control the statistics management mode by changing the behavior for individual tables using the table option STATS_PERSISTENT = 1. There are no column-level or statistics-level options for setting parameter values.

To view statistics metadata, use the INFORMATION_SCHEMA.STATISTICS standard view. To view detailed persistent optimizer statistics, use the innodb_table_stats and innodb_index_stats tables.

The following image demonstrates an example of the mysql.innodb_table_stats content.

The following image demonstrates an example of the mysql.innodb_index_stats content.

Automatic refresh of statistics is controlled by the global parameter innodb_stats_auto_recalc, which is set to ON in Aurora MySQL. You can set it individually for each table using the STATS_AUTO_RECALC=1 option.

To explicitly force a refresh of table statistics, use the ANALYZE TABLE statement. It is not possible to refresh individual statistics or columns.

Use the NO_WRITE_TO_BINLOG, or its clearer alias LOCAL, to avoid replication to replication secondaries.

Use ALTER TABLE …​ ANALYZE PARTITION to analyze one or more individual partitions.

Syntax

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE <Table Name> [,...];

CREATE TABLE ( <Table Definition> ) | ALTER TABLE <Table Name>
STATS_PERSISTENT = <1|0>,
STATS_AUTO_RECALC = <1|0>,
STATS_SAMPLE_PAGES = <Statistics Sampling Size>;

Migration Considerations

Unlike Oracle, Aurora MySQL collects only density information. It does not collect detailed key distribution histograms. This difference is critical for understanding execution plans and troubleshooting performance issues that are not affected by individual values used by query parameters.

Statistics collection is managed at the table level. You cannot manage individual statistics objects or individual columns. In most cases, that should not pose a challenge for successful migration.

Examples

The following example creates a table with explicitly set statistics options.

CREATE TABLE MyTable
(Col1 INT NOT NULL AUTO_INCREMENT,
Col2 VARCHAR(255),
DateCol DATETIME,
PRIMARY KEY (Col1),
INDEX IDX_DATE (DateCol)
) ENGINE=InnoDB,
STATS_PERSISTENT=1,
STATS_AUTO_RECALC=1,
STATS_SAMPLE_PAGES=25;

The following example refreshes all statistics for MyTable1 and MyTable2.

ANALYZE TABLE MyTable1, MyTable2;

The following example changes the MyTable settings to use non-persistent statistics.

ALTER TABLE MyTable STATS_PERSISTENT=0;

Summary

The following table identifies Aurora MySQL features. All of the features are accessed in Oracle using the DBMS_STATS package.

For more information, see The INFORMATION_SCHEMA COLUMN_STATISTICS Table, Configuring Persistent Optimizer Statistics Parameters, Configuring Non-Persistent Optimizer Statistics Parameters, and Configuring Optimizer Statistics for InnoDB in the MySQL documentation.