Testing Periodic Estimation on Large Data Sets

This topic provides guidelines for testing periodic estimation on large data sets.

Before running periodic estimation on a large data set it is important to have an idea of roughly how many devices and for how long you expect to estimate. This can be gauged fairly quickly by looking at the Latest Measurement Date/Time on the measuring component. Here is a sample query that will provide a count of measuring components by the number of months from a supplied process date the last contiguous measurement date/time is:

select count(*)
, round((to_date(:processDateTime,'YYYY-MM-DD-HH24.MI.SS') - latest_msrmt_dttm)/31,0) MONTHS
from d1_measr_comp mc
group by round((to_date(:processDateTime,'YYYY-MM-DD-HH24.MI.SS') - latest_msrmt_dttm)/31,0)
order by 2
;

Below is a more complex query that will show you by thread how many measuring components for a given Latest Measurement Date/Time you will process, it does a bit more work to look at only installed devices and measuring components that are configured for periodic estimation and the measuring component is actually ready to estimate based on that configuration.

with threads as (
 select rownum thread_nbr
 , lpad(round((rownum - 1) * (rpad('9',12,'9')+1)/:threads,0),12,'0') low_id
 , lpad(round(rownum * ((rpad('9',12,'9')+1)/:threads),0)-1,12,'0') high_id
 from dual
 connect by rownum <= :threads
)
select /*+ parallel */ threads.thread_nbr
, threads.low_Id
, threads.high_id
, nvl(mc.adj_latest_msrmt_dttm,mc.latest_msrmt_dttm) LATEST_MSRMT_DTTM
, count(*) MC_COUNT
from threads
inner join d1_dvc dvc
 on dvc.d1_device_id between threads.low_id and threads.high_id
inner join d1_dvc_cfg dc
 on dc.d1_device_id = dvc.d1_device_id
inner join d1_install_evt ie
 on ie.device_config_id = dc.device_config_id
 and ie.d1_install_dttm < sysdate
 and (ie.d1_removal_dttm is null or ie.d1_removal_dttm >= sysdate)
inner join d1_measr_comp mc
 on mc.device_config_id = dc.device_config_id
inner join d1_measr_comp_type mct
 on mct.measr_comp_type_cd = mc.measr_comp_type_cd
 and hrs_wait_before_est > 0
where (to_date(nvl(:processDateTime,sysdate),'YYYY-MM-DD-HH24.MI.SS') - latest_msrmt_dttm) * 24 > mct.hrs_wait_before_est
group by threads.thread_nbr
, threads.low_Id
, threads.high_id
, nvl(mc.adj_latest_msrmt_dttm,mc.latest_msrmt_dttm)
order by 1, 4
;