For customer that unable to complete analyze partitions on daily basis OR would like to have latest partition populated with statistic.
This additional option has been enabled on TNPM1.3.2 IF5 onwards.
It will utilise copy stats from Oracle 11g and perform full table analyse to ensure statistic are up to date.
Improvement on part maint will be providing daily copy stats at 3am and analyze tables that older than 7 days from last analyzed on daily basis at 12am.
To run part maint using copy stats and analyze tables, run below command from application server.
$ dbsysval_admin -update PartMaintCopyStats true
To revert back part maint to default analyze partition, run below command from application server.
$ dbsysval_admin -update PartMaintCopyStats false
(Optional) To change the list of tables to be analyzed by ANALYZE_TABLES job. Updating the delay will change the job to select tables with (last_analyzed = sysdate - PartMaintAnalyzeTablesDelay) only. (default: 7). Applicable only when PartMaintCopyStats is set to true.
$ dbsysval_admin -update PartMaintAnalyzeTablesDelay N
where N = the number day to be excluded from daily analyze tables, default 7 days.
Note: Decrease the values if you want to have more frequency on analyze tables. Be caution that there are performance impact on Oracle whenever daily analyze is run.
(Optional) To increase the analyze tables job running time, run below command from application server. (default: 28800; seconds). Applicable only when PartMaintCopyStats is set to true.
$ dbsysval_admin -update PartMaintMaxAnalyzeRunTime N
where N = the number of seconds to run, default 28800 and maximum 82800.
Note: Increase the value if your analyze for all tables consume more than 56 hours.
Please consult support team if you're not sure.