Database Table Analyze Job and its effect on Oracle storage parameters

Products

• Clarity PPM On Premise

Issue/Introduction

Does the Oracle Table analyze job in Clarity modify INITRANS & PCTFREE parameters?

Resolution

Generally, the Oracle Table Analyze Job in Clarity does not change physical storage parameters of a table like PCTFREE or INITRANS. The Oracle Table Analyze job is equivalent to DBMS_STATS in Oracle, which is responsible for gathering statistics.

The clarity job analyzes the data distribution, row count, number of blocks, etc..but does not analyze or update the histogram like the default Oracle stats gathering job. Broadcom recommendation is to run the Clarity job "Database (Oracle/PostgreSQL) Table Analyze Job" and to pause the Oracle native stats gathering job.

The Oracle Table Analyze job in the background executes the Oracle Stored Procedure cmn_job_analyze_sp (clarity schema). The Oracle DBA could review this SP for additional context. 

Additional Information

Clarity - Oracle Table Analyze Job - Best Practices