Updating EcoSys Database Statistics - EcoSys - Administration & Configuration - Hexagon

EcoSys System Administration

Language
English
Product
EcoSys
Search by Category
Administration & Configuration
EcoSys Version
9.0

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

  • 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.