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
Run the following script manually or create a new T-SQL maintenance plan and paste the code into the script section. Schedule for at least once per week. SQL Server Agent must be installed and running.
For your convenience the script name fm_ss_job_update_stats.sql is located in the EcoSys application package under \database\sqlserver\utils\
sp_MSforeachdb @command1='
USE ?;
IF ("?" IN ("esfm"))
BEGIN
EXEC sp_createstats;
DECLARE @tbls TABLE (rowId INT IDENTITY(1,1), tblschema NVARCHAR(128), tblname NVARCHAR(128));
DECLARE @stmt NVARCHAR(2000), @rowId INT, @maxRowId INT, @tblschema NVARCHAR(128), @tblname NVARCHAR(128);
INSERT INTO @tbls (tblschema, tblname)
SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_type="BASE TABLE";
SELECT @rowId = MIN(rowId), @maxRowId = MAX(rowId) FROM @tbls;
WHILE @rowId <= @maxRowId
BEGIN
SELECT @tblschema = tblschema, @tblname = tblname FROM @tbls WHERE rowId = @rowId;
SET @stmt = "UPDATE STATISTICS " + "[" + @tblschema + "].[" + @tblname + "];";
PRINT "?: " + @stmt;
EXECUTE (@stmt);
SET @rowId = @rowId + 1;
END
END
';