Clarity - Oracle Table Analyze Job - Best practices

Products

• Clarity PPM On Premise

Issue/Introduction

What are some best practices to run Oracle Table Analyze job to optimize queries performance, how to schedule/run it

Possible impacts of not following the best practices:

• Performance issues in Clarity
• Some queries are slow
• Database locks, blockings, database CPU is high

Environment

Clarity with Oracle Database 

Resolution

• This job gather statistics ensuring better performance in Clarity
• The recommendations are to set this job run once per day one time per week during the off-peak hours
• If you have scheduled it, then default oracle stats collection should be stopped
• Running Oracle Stats includes histogram stats that increase input-output consumption and paging memory. Because the ClarityOracle Table Analyze job does not gather histogram stats, it does not cause these performance issues.
• Broadcom Support recommends using the Clarity Oracle Table Analyze job
• If you prefer letting DBA manage the statistics instead, then this is possible and supported as well, whilst you should be doing a full performance testing to ensure the performance is optimal

Additional Information

More details about the job in the Broadcom Documentation Oracle Table Analyze Job