Oracle Table Statistics and MySQL Managing Statistics
Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences |
---|---|---|---|
|
N/A |
N/A |
Syntax and option differences, similar functionality. |
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:
-
Number of table rows.
-
Number of table blocks.
-
Number of distinct values or nulls.
-
Data distribution histograms.
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:
Statistics level | Description |
---|---|
|
Index statistics |
|
Table, column, and index statistics |
|
Statistics for all objects in a schema |
|
Statistics for all dictionary objects |
|
Statistics for all objects in a database |
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
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.
![Table statistics](/images/dms/latest/oracle-to-aurora-mysql-migration-playbook/images/oracle-aurora-mysql-table-stats.png)
The following image demonstrates an example of the mysql.innodb_index_stats
content.
![Index statistics](/images/dms/latest/oracle-to-aurora-mysql-migration-playbook/images/oracle-aurora-mysql-index-stats.png)
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.
Note
Amazon Relational Database Service (Amazon RDS) for MySQL version 8 adds new INFORMATION_SCHEMA.INNODB_CACHED_INDEXES
table which reports the number of index pages cached in the InnoDB buffer pool for each index.
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.
Feature | Aurora MySQL | Comments |
---|---|---|
Column statistics |
N/A |
|
Index statistics |
Implicit with every index |
Statistics are maintained automatically for every table index. |
Refresh or update statistics |
|
Minimal scope in Aurora MySQL is the entire table. No control over individual statistics. |
Auto create statistics |
N/A |
|
Auto update statistics |
Use the |
|
Statistics sampling |
Use the |
Can only use page number, not percentage for |
Full scan refresh |
N/A |
Using a very large |
Non-persistent statistics |
Use the |
For more information, see The INFORMATION_SCHEMA COLUMN_STATISTICS Table