Before Primavera P6 EPPM came along, the Project Management client application had the job of cleaning up the database. It would do this when a user logged in, logged out or selected an option in the Tools menu. Depending on the amount of cleaning up required, this could take anything from a few seconds to a few minutes.
When Primavera P6 EPPM was released, the database clean-up tasks were taken out of the Project Management client and managed by one of two Background Jobs run by the RDBMS Scheduler. This is a big design improvement over using the Project Management client to do these jobs.
There are 11 tasks managed by these Background Jobs and this is the same for v7.0 through v8.3. Each task is an RDBMS Stored Procedure:
SYMON – (System Monitor) is responsible for running tasks that are expected to take a few seconds to run.
- USESSION_CLEANUP_EXPIRED – removes user sessions that haven’t been active for a given duration.
- OBSPROJ_PROCESS_QUEUE – processes changes to EPS and Project security.
DAMON – (Data Monitor) is responsible for running tasks that take longer than a few seconds to run.
- BGPLOG_CLEANUP – removes old entries from the log table for the Background Jobs.
- REFRDEL_CLEANUP – removes entries from the referential delete table (REFRDEL).
- CLEANUP_PRMQUEUE – removes completed EPS and project security tasks.
- USESSION_CLEAR_LOGICAL_DELETES – removes all records pertaining to a deleted session.
- CLEANUP_LOGICAL_DELETES – delete records from tables that have been deleted by user action. The P6 applications mark records as deleted, they don’t actually delete them.
- PRMAUDIT_CLEANUP – removes old database audit records.
- CLEANUP_USESSAUD – removes old audit records for user sessions.
- USER_DEFINED_BACKGROUND – made available for your customized code.
- CLEANUP_OLD_DATA – P6 version specific clean-up that doesn’t do anything.
The Problem With P6 Background Jobs
If you have had your P6 database for a few months you may want to count the number of records in the REFRDEL table.
SELECT count(*) FROM REFRDEL;
We did this at one of our clients recently and found over 70 million records in their REFRDEL table.
These records are not needed by P6 and there are plenty more similarly logically deleted records in just about every table in the P6 database. All in all they can take up a lot of space and slow the whole system down writing and reading data from tables that are much larger than they need to be.
The problem exists because the defaults for running the Background Jobs are not optimal. The solution is simple, and in this article I’ll illustrate what needs to be done using the “REFRDEL_CLEANUP” task ran by DAMON. Default Settings and the REFRDEL_CLEANUP task
The DAMON Background Job is set to run each week on Saturday. You can see this by running the following SQL:
SELECT setting_value FROM settings WHERE namespace = ‘database.background.Damon’ AND setting_name = ‘Interval’;
This means it will run each week on a Saturday. When the DAMON Job runs it executes each of the 9 tasks in turn. Each task has it’s own set of default settings that define how much will be deleted. The defaults are hard-coded into the procedures, but can be overridden if they also exist in the SETTINGS table. The trick is to write the correct values to the SETTINGS table. The REFRDELCLEANUP task uses three settings
The “KeepInterval” setting defaults to “5d”. It is the number of days that will be kept. If the Job was being executed on Saturday the 14th of a Month, then this setting prevents any records created in the REFRDEL table from Monday the 9th through until Saturday 14th from being deleted. It keeps five days in addition to the day it is being ran. The “DaysToDelete” setting defaults to “1”. This is the total number of days that will be deleted starting from the earliest record created in the REFRDEL table.
The last setting is the “IntervalStep” setting which is set to 15. This deletes 15 minutes worth of records from REFRDEL at a time before issuing a COMMIT to the database.
These settings mean that every 7 days when the Background Jobs are run, they will delete just one day of records. It is no wonder there are many Oracle Primavera P6 databases out there that are brim full of unnecessary records. Fixing the REFRDEL_CLEANUP task
The simple rule is to ensure the “DaysToDelete” setting for the “REFRDEL_CLEANUP” task is at least the same duration as the “KeepInterval” or the duration between running the DAMON Background Job, whichever is the larger..
I always set the “DaysToDelete” somewhat larger than the rule suggests, usually because there are many months or years of redundant records in the REFRDEL table. The client mentioned earlier had been running their P6 database for three years, so by setting the “DaysToDelete” to “100” it took 11 weeks to remove the backlog.
The following is part of the SQL script we wrote for their Oracle Database:
In Part 2, I tackle the other tasks which use a slightly different method to determine what to keep and what to delete. You can read Part 2 by clicking here
For more Primavera P6 articles click here