Updating EcoSys Database Statistics - EcoSys - Administration - Hexagon PPM

EcoSys System Administration

PPMProduct
EcoSys
PPMCategory_custom
Administration & Configuration
Version_EcoSys
8.6

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

';