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 

Resolution

• On Oracle 19: running Oracle Stats with histogram stats, may cause I/O consumption and use of paging memory to rise.
• The OOTB Oracle Table Analyze job has a change in Oracle 19 to suppress method_opt=> 'FOR ALL COLUMNS SIZE AUTO'.
• This change in the OOTB Oracle Table Analyze job was specifically done for Oracle 19 as we have noticed issues with some large customers
• We recommend using the table stats methods as per Oracle Table Analyze job and CMN_JOB_ANALYZE_SP on Oracle 19 if you face performance issues 
• This 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