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.
-
threads: enter 1 to avoid splitting the results into threads
-
processDateTime: leave blank to use the system date, otherwise provide a date/time in the format: YYYY-MM-DD-HH24.MI.SS
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
;