Clarity Oracle Table Analyze Job vs. Oracle Stats

Products

• Clarity PPM On Premise

Issue/Introduction

After running Oracle Stats, the Paging space IR’s indicating usage >80%. 

DBA found that the Oracle process was holding onto the paging memory and unable to release the duplicate paging memory from the OS side. 

We would like to know the difference between the Clarity Oracle Table Analyze job and running Oracle Stats directly on the database for performance.

Environment

Clarity - All Supported Releases with Oracle 19 

Cause

Running Oracle statistics with histogram data enabled (method_opt=> 'FOR ALL COLUMNS SIZE AUTO') may lead to increased I/O consumption and higher paging memory usage in some environments. This is due to the more detailed statistical information collected, which can be resource-intensive.

Resolution

Context:

• Running Oracle Stats with histogram stats, may cause I/O consumption and use of paging memory to rise on some environments.
• The OOTB Oracle Table Analyze job has a change in Oracle 19 to use default method which will include histograms - method_opt=> 'FOR ALL COLUMNS SIZE AUTO'.
  • This change in the OOTB Oracle Table Analyze job was specifically done for Oracle 19 
  • Previously, on Oracle 12 we had the method_opt=> 'FOR ALL COLUMNS SIZE 1' method due to specific issues with histogram stats, that should be addressed in Oracle 19

Recommendations:

• It is generally recommended to use Oracle Table Analyze job and CMN_JOB_ANALYZE_SP on Oracle 19 if DBA is not directly involved in custom statistics collection.
• If you face performance issues, engage with your DBA and work with them to schedule regular table stats job instead of Oracle Table Analyze job.
• It is a normal and supported practice to collect statistics tailored to your environment, ensuring better performance.
• The method used by OOTB Oracle Table Analyze job may be revisited in future Oracle releases - so if you have made the change in your database jobs, check back after upgrading Oracle
• For best practices, check Clarity - Oracle Table Analyze Job - Best practices

Additional Information

Information on the Oracle Stats Options

1. method_opt=> 'FOR ALL COLUMNS SIZE AUTO'

   • What it does: 
     For every column, Oracle automatically determines if a histogram is needed and, if so, what the optimal number of buckets (up to 254) should be to accurately represent the data distribution.
   • Purpose:
     To provide the Oracle Cost-Based Optimizer (CBO) with detailed information about skewed data in columns. This helps the CBO make better choices for execution plans, especially for queries with predicates on those skewed columns (WHERE status = 'OPEN' where 'OPEN' might represent a tiny fraction of the rows).
   • Resource Impact: 
     Can be resource-intensive (I/O, CPU, memory) because Oracle has to scan each column to detect skew and build histograms. This is why it may be causing issues for Clarity's large customers on Oracle 19.
   • Default Behavior: 
     In modern Oracle versions, SIZE AUTO is often the default or recommended setting for general statistics gathering, as it aims for optimal CBO performance.

2. method_opt=> 'FOR ALL COLUMNS SIZE 1'

   • What it does: 
     For every column, Oracle will not gather any histograms. It will only gather basic column statistics (such as minimum value, maximum value, number of distinct values, number of nulls). While technically it's saying "create a histogram with 1 bucket," a 1-bucket histogram provides no useful information about data skew; it's effectively the same as not having a histogram for CBO purposes.
   • Purpose: 
     To gather basic, essential column statistics with the least amount of overhead. It avoids the resource consumption associated with building histograms.
   • Resource Impact: 
     Lower resource consumption compared to SIZE AUTO because it skips the often intensive histogram-building process.
   • Trade-off:
     Without histograms, the Oracle CBO might make suboptimal choices for queries on columns with skewed data. If a column has highly uneven data distribution and is frequently used in predicates, and SIZE 1 is used, the optimizer might estimate cardinality incorrectly, leading to inefficient execution plans (full table scans instead of index lookups, or vice-versa).