Clarity

Hi All,

Did anyone has changed this stored procedure cmn_job_analyze_sp for Oracle 12c to improve the performance of the database?

Thanks,

Pragya Singh

You wouldn't need to change that job to do database performance adjustments - all that that job does is analyse some stats ; if you wanted to get stats in a a different manner just get your DBA to do it!

(Your DBA should know more about your database than the Clarity/PPM application software does)

Hi Pragya

CMN_JOB_ANALYZE_SP will by default analyze PPM table stats at 50%. You may increase this percentage but in our experience it's usually more beneficial to run 50% of the stats as opposed to 100% (comparing results, efforts etc.). This is why this setting is by default. If your DBA finds some specific tables need more stats to be run, they should run the stats more only for these tables. The job is here for your convenience but it doesn't allow for much customization. As Dave said above anything that goes beyond should be taken care by the DBA.

I hope this helps -Nika

Thank you so much.

We will contact our DBA to check tables which are showing slow performance.

Also, what is the best frequency to schedule this job? once/twice in a week?

Thanks,

Pragya Singh

Hi Pragya,

It really depends on the DBA to see how frequently he is seeing the stale objects at database. However once every day off peak hours are recomended. 

Regards
Suman Pramanik