To provide the optimizer with accurate information for determining an optimal access plan, update the statistics on all tables and indexes on a regular basis, at least once per week, especially if significant update activity has occurred since the last statistic update.
In regards to Oracle, it is also recommended to flush the database buffer cache after updating the schema statistics.
Oracle
Execute the following as the SYSTEM or SYS user to create the scheduled jobs. The date/time can be modified to fit your schedule.
For your convenience the script name fm_or_job_update_stats.sql is located in the EcoSys application package under \database\oracle\utils\
/* **************************************************** */
/* Update EPC Statistics Every Saturday at 2:00 am */
/* *************************************************** */
begin
dbms_scheduler.create_job (
job_name => 'EPC_STATS_REFRESH',
job_type => 'PLSQL_BLOCK',
job_action =>
'begin DBMS_STATS.GATHER_SCHEMA_STATS(ownname => ''FMADMIN'', cascade =>true,estimate_percent => NULL);END;',
start_date => trunc(sysdate) + 2/24,
repeat_interval => 'FREQ=DAILY; BYDAY=SAT; BYHOUR=2; BYMINUTE=0; BYSECOND=0',
comments => 'Gather stats on EPC tables at 2am every Saturday',
end_date => NULL,
enabled => true
);
END;
/
/* **************************************************** */
/* Flush DB Buffer_Cache Every Saturday at 4:00 am */
/* **************************************************** */
CREATE OR REPLACE PROCEDURE FLUSH_BUFFER AS
BEGIN
EXECUTE IMMEDIATE 'ALTER SYSTEM FLUSH BUFFER_CACHE';
END;
/
begin
dbms_scheduler.create_job (
job_name => 'EPC_FLUSH_BUFFERS',
job_type => 'PLSQL_BLOCK',
job_action =>
'begin FLUSH_BUFFER;END;',
start_date => trunc(sysdate) + 4/24,
repeat_interval => 'FREQ=DAILY; BYDAY=SAT; BYHOUR=4; BYMINUTE=0; BYSECOND=0',
comments => 'Flush DB Buffer Cache at 4am every Saturday',
end_date => NULL,
enabled => true
);
END;
/
SQL Server
-
Execute the script \Ecosys\database\sqlserver\utils\stats\PrerequisiteRunOnce.sql
-
This script needs to be executed only once to configure the required database tables/procedures needed for the stats collection process.
-
-
To execute stats, execute one of the two sections from the script
\Ecosys\database\sqlserver\utils\stats\fm_ss_job_update_stats_options.sql, updating the @Databases parameter to match your database name
-
It is recommended to run the 'Light' database statistics update process nightly. This method does not requires no EcoSys downtime.
-
It is recommended to run the 'Heavy' database statistics update process weekly. This method require EcoSys be offline during its execution.
-