My Oracle Support Banner

Comparing Statistics For Tables Using History (Same Database) And Stat Table (Different Database) (Doc ID 2285732.1)

Last updated on MARCH 18, 2022

Applies to:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

Purpose

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

Most of the optimizer issues in generating the optimal execution plans for SQL statements are due to the inaccurate statistics for the objects involved.
One of the main reasons an execution plan can differ from one system to another is because optimizer statistics are different.
Having said, from 12c, there is a mechanism in DBMS_STATS to compare the statistics versions within the same database as well with another database.
For example, statistics may be different in a test environment when compared to production if the data is not in sync.
To identify differences in statistics, the DBMS_STATS.DIFF_TABLE_STATS_IN_HISTORY/STATTAB functions can be used to compare statistics for two different sources (denoted source "A" and source "B").

Scope

Scope of this bulletin is to explain about the new feature in 12c about comparing the statistics for tables within the same database using statistics history and for tables in different database using statistics table (STATTAB).

DIFF_TABLE_STATS_IN_HISTORY: This function can be used to compare statistics for a table from two timestamps in past and compare the statistics as of that timestamps.
DIFF_TABLE_STATS_IN_STATTAB: This function can be used to compare statistics for a table from two different sources.

The "DIFF" functions also compare the statistics of the dependent objects (indexes, columns, partitions), and displays all the statistics for the object(s) from both sources if the difference between the statistics exceeds a specified threshold. The threshold can be specified as an argument to the function; the default value is 10%. The statistics corresponding to the first source will be used as the basis for computing the differential percentage.

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Purpose
Scope
Details
 To compare the statistics of an individual table at two different points in time using history (Same Database):
 To compare the statistics of an individual table from one database to another using STATTAB:

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.