Dave

TIP : Whats going on with Time Slicing?

Discussion created by Dave on Dec 21, 2010
Latest reply on Dec 29, 2010 by Dave
(in the spirit of Tuesday's tips... :bashful:)

--

Often one wonders what on Earth is going on in the Clarity back-end with that 'Time Slicing' job? (or maybe that is just me? :sad )

Here is some SQL (Oracle format from a Clarity v8.1 instance, but pretty simple to MSSQL-ify it) that I use to tell me what is happening.


"To Process" is how many records the Clarity application has flagged as needing to be processed by the Time Slicing job,
"In Progress" is what the 'Time Slicing' job is currently working on (1000 records at a time)
"To Rollover" is what gets flagged by the 'Time Slicing' job itself as needing processing on its fist run after a Time Slice expiration date (eg first of the week / month)
SELECT to_char(sysdate,'dd/mm/yyyy hh24:mi:ss') AS "When"
,'TO PROCESS' AS "What"
,( SELECT COUNT(*) FROM PRTEAM WHERE SLICE_STATUS = 1 ) AS PRTEAM_SS
,( SELECT COUNT(*) FROM PRTEAM WHERE INCIDENT_SLICE_STATUS = 1 ) AS PRTEAM_ISS
,( SELECT COUNT(*) FROM PRTEAM WHERE HARD_SLICE_STATUS = 1 ) AS PRTEAM_HSS
,( SELECT COUNT(*) FROM PRASSIGNMENT WHERE SLICE_STATUS = 1 ) AS PRASSIGNMENT_SS
,( SELECT COUNT(*) FROM PRJ_RESOURCES WHERE SLICE_STATUS = 1 ) AS PRJ_RESOURCES_SS
,( SELECT COUNT(*) FROM SRM_RESOURCES WHERE SLICE_STATUS = 1 ) AS SRM_RESOURCES_SS
,( SELECT COUNT(*) FROM PRTIMEENTRY WHERE SLICE_STATUS = 1 ) AS PRTIMEENTRY_SS
,( SELECT COUNT(*) FROM CAP_SCENARIO_ASSIGNMENTS WHERE SLICE_STATUS = 1 ) AS CAP_SCENARIO_ASSIGNMENTS_SS
,( SELECT COUNT(*) FROM CAP_SCENARIO_TASKS WHERE SLICE_STATUS = 1 ) AS CAP_SCENARIO_TASKS_SS
,( SELECT COUNT(*) FROM CAP_SCENARIO_TEAM WHERE SLICE_STATUS = 1 ) AS CAP_SCENARIO_TEAM_SS
,( SELECT COUNT(*) FROM CAP_SCENARIO_TEAM WHERE HARD_CURVE_SLICE_STATUS = 1 ) AS CAP_SCENARIO_TEAM_HCSS
,( SELECT COUNT(*) FROM INV_INVESTMENTS WHERE SLICE_STATUS = 1 ) AS INV_INVESTMENTS_SS
,( SELECT COUNT(*) FROM PRJ_PROJECTS WHERE SLICE_STATUS = 1 ) AS PRJ_PROJECTS_SS
,( SELECT COUNT(*) FROM PRCALENDAR WHERE SLICE_STATUS = 1 ) AS PRCALENDAR_SS
,( SELECT COUNT(*) FROM PRJ_BASELINES WHERE SLICE_STATUS = 1 ) AS PRJ_BASELINES_SS
,( SELECT COUNT(*) FROM PRJ_BASELINE_DETAILS WHERE SLICE_STATUS = 1 ) AS PRJ_BASELINE_DETAILS_SS
,( SELECT COUNT(*) FROM PRJ_TENTATIVE_ASSIGNMENTS WHERE SLICE_STATUS = 1 ) AS PRJ_TENTATIVE_ASSIGNMENTS_SS
,( SELECT COUNT(*) FROM RSM_REQ_REQUISITIONS WHERE SLICE_STATUS = 1 ) AS RSM_REQ_REQUISITIONS_SS
from dual
UNION
SELECT ''
,'IN PROGRESS' AS "What"
,( SELECT COUNT(*) FROM PRTEAM WHERE SLICE_STATUS = 2 ) AS PRTEAM_SS
,( SELECT COUNT(*) FROM PRTEAM WHERE INCIDENT_SLICE_STATUS = 2 ) AS PRTEAM_ISS
,( SELECT COUNT(*) FROM PRTEAM WHERE HARD_SLICE_STATUS = 2 ) AS PRTEAM_HSS
,( SELECT COUNT(*) FROM PRASSIGNMENT WHERE SLICE_STATUS = 2 ) AS PRASSIGNMENT_SS
,( SELECT COUNT(*) FROM PRJ_RESOURCES WHERE SLICE_STATUS = 2 ) AS PRJ_RESOURCES_SS
,( SELECT COUNT(*) FROM SRM_RESOURCES WHERE SLICE_STATUS = 2 ) AS SRM_RESOURCES_SS
,( SELECT COUNT(*) FROM PRTIMEENTRY WHERE SLICE_STATUS = 2 ) AS PRTIMEENTRY_SS
,( SELECT COUNT(*) FROM CAP_SCENARIO_ASSIGNMENTS WHERE SLICE_STATUS = 2 ) AS CAP_SCENARIO_ASSIGNMENTS_SS
,( SELECT COUNT(*) FROM CAP_SCENARIO_TASKS WHERE SLICE_STATUS = 2 ) AS CAP_SCENARIO_TASKS_SS
,( SELECT COUNT(*) FROM CAP_SCENARIO_TEAM WHERE SLICE_STATUS = 2 ) AS CAP_SCENARIO_TEAM_SS
,( SELECT COUNT(*) FROM CAP_SCENARIO_TEAM WHERE HARD_CURVE_SLICE_STATUS = 2 ) AS CAP_SCENARIO_TEAM_HCSS
,( SELECT COUNT(*) FROM INV_INVESTMENTS WHERE SLICE_STATUS = 2 ) AS INV_INVESTMENTS_SS
,( SELECT COUNT(*) FROM PRJ_PROJECTS WHERE SLICE_STATUS = 2 ) AS PRJ_PROJECTS_SS
,( SELECT COUNT(*) FROM PRCALENDAR WHERE SLICE_STATUS = 2 ) AS PRCALENDAR_SS
,( SELECT COUNT(*) FROM PRJ_BASELINES WHERE SLICE_STATUS = 2 ) AS PRJ_BASELINES_SS
,( SELECT COUNT(*) FROM PRJ_BASELINE_DETAILS WHERE SLICE_STATUS = 2 ) AS PRJ_BASELINE_DETAILS_SS
,( SELECT COUNT(*) FROM PRJ_TENTATIVE_ASSIGNMENTS WHERE SLICE_STATUS = 2 ) AS PRJ_TENTATIVE_ASSIGNMENTS_SS
,( SELECT COUNT(*) FROM RSM_REQ_REQUISITIONS WHERE SLICE_STATUS = 2 ) AS RSM_REQ_REQUISITIONS_SS
from dual
UNION
SELECT ''
,'TO ROLLOVER' AS "What"
,( SELECT COUNT(*) FROM PRTEAM WHERE SLICE_STATUS = 3 ) AS PRTEAM_SS
,( SELECT COUNT(*) FROM PRTEAM WHERE INCIDENT_SLICE_STATUS = 3 ) AS PRTEAM_ISS
,( SELECT COUNT(*) FROM PRTEAM WHERE HARD_SLICE_STATUS = 3 ) AS PRTEAM_HSS
,( SELECT COUNT(*) FROM PRASSIGNMENT WHERE SLICE_STATUS = 3 ) AS PRASSIGNMENT_SS
,( SELECT COUNT(*) FROM PRJ_RESOURCES WHERE SLICE_STATUS = 3 ) AS PRJ_RESOURCES_SS
,( SELECT COUNT(*) FROM SRM_RESOURCES WHERE SLICE_STATUS = 3 ) AS SRM_RESOURCES_SS
,( SELECT COUNT(*) FROM PRTIMEENTRY WHERE SLICE_STATUS = 3 ) AS PRTIMEENTRY_SS
,( SELECT COUNT(*) FROM CAP_SCENARIO_ASSIGNMENTS WHERE SLICE_STATUS = 3 ) AS CAP_SCENARIO_ASSIGNMENTS_SS
,( SELECT COUNT(*) FROM CAP_SCENARIO_TASKS WHERE SLICE_STATUS = 3 ) AS CAP_SCENARIO_TASKS_SS
,( SELECT COUNT(*) FROM CAP_SCENARIO_TEAM WHERE SLICE_STATUS = 3 ) AS CAP_SCENARIO_TEAM_SS
,( SELECT COUNT(*) FROM CAP_SCENARIO_TEAM WHERE HARD_CURVE_SLICE_STATUS = 3 ) AS CAP_SCENARIO_TEAM_HCSS
,( SELECT COUNT(*) FROM INV_INVESTMENTS WHERE SLICE_STATUS = 3 ) AS INV_INVESTMENTS_SS
,( SELECT COUNT(*) FROM PRJ_PROJECTS WHERE SLICE_STATUS = 3 ) AS PRJ_PROJECTS_SS
,( SELECT COUNT(*) FROM PRCALENDAR WHERE SLICE_STATUS = 3 ) AS PRCALENDAR_SS
,( SELECT COUNT(*) FROM PRJ_BASELINES WHERE SLICE_STATUS = 3 ) AS PRJ_BASELINES_SS
,( SELECT COUNT(*) FROM PRJ_BASELINE_DETAILS WHERE SLICE_STATUS = 3 ) AS PRJ_BASELINE_DETAILS_SS
,( SELECT COUNT(*) FROM PRJ_TENTATIVE_ASSIGNMENTS WHERE SLICE_STATUS = 3 ) AS PRJ_TENTATIVE_ASSIGNMENTS_SS
,( SELECT COUNT(*) FROM RSM_REQ_REQUISITIONS WHERE SLICE_STATUS = 3 ) AS RSM_REQ_REQUISITIONS_SS
from dual;
You can simply "portlet-ify" that SQL and present it in the application... and then spend endless hours pressing F5 hoping that the numbers in the first row move into the second row soon 8o

PS : I don't trust the PRJ_BASELINE_DETAILS numbers though - they always seem to be quite high and never change, I don't really know what that means! :wacko:

Outcomes