Creating Older MUDR LP_INTERVALS Database Partitions

We were looking to retroactively load meter data from older MV90 meters up to 5 years into the past (3 years before we implemented energyIP 7.2).  I've not had extensive exposure to partitioning until taking on the administration for EnergyIP.  I'd created, dropped and truncated partitions mudr range partitions before but had never created a partition with a high date value in the past - always newer partitions to capture future data.

I'd thought this would be simple until I realized you can't add partitions in the past!  You have to split the oldest partition into two new ones.

To further complicate things I needed to do this for multiple tables and create an additional 60 partitions in each.  This would be a time consuming process to do manually so I decided to automate the process via a little PL/SQL.

For some reason oracle decided to store high value of each partition value in a long column instead of a varchar.  Not sure why but it causes some issues that were addressed using dbms_sql package tools.

Here is the anonymous PL/SQL block to add 60 monthly partitions in the past on the LP_INTERVALS table.  Modify accordingly to run on LP_BATCH, REGISTER_READS, etc.  USE AT YOUR OWN RISK.  TEST FIRST!


SET SERVEROUTPUT ON

DECLARE
   l_partition_name       VARCHAR2 (30 BYTE);
   l_hival                VARCHAR2 (4000);
   l_sql                  VARCHAR2 (4000);
   l_high_date            DATE;
   l_cursor               INTEGER DEFAULT DBMS_SQL.open_cursor;
   l_rows_back            NUMBER;
   l_lowval               DATE;
   l_new_partition_name   VARCHAR2 (30 BYTE);
BEGIN
   FOR i IN 1 .. (12 * 5) --number of months of partitions to create back in the past from the oldest partition
   LOOP
      -- partition position = 1 always grabs the "oldest" partition
      SELECT high_value, partition_name
        INTO l_hival, l_partition_name
        FROM dba_tab_partitions
       WHERE table_name = 'LP_INTERVALS' AND partition_position = 1;

      --scrape date from high_value stupid long column into varchar

      DBMS_SQL.parse (l_cursor,
                      'begin :retval := ' || l_hival || '; end;',
                      DBMS_SQL.native);
      --put scraped valued into date variable
      DBMS_SQL.bind_variable (l_cursor, ':retval', l_high_date);
      l_rows_back := DBMS_SQL.execute (l_cursor);
      DBMS_SQL.variable_value (l_cursor, ':retval', l_high_date);

      --generate datevariable  at which to split the partition
      l_lowval := ADD_MONTHS (l_high_date, -1);

      --generate name for newly created older partition resulting from split
      l_new_partition_name :=
         'LP_' || TO_CHAR (ADD_MONTHS (l_lowval, -1), 'MON_YYYY');

      --DBMS_OUTPUT.put_line (
      --'ALTER TABLE EMAPP.LP_INTERVALS SPLIT PARTITION '||l_partition_name||' AT (TO_DATE ('''||l_lowval||''', ''DD-MON-YYYY'')) INTO (PARTITION '||l_partition_name||' TABLESPACE DATA), PARTITION '||l_new_partition_name||' tablespace DATA) UPDATE GLOBAL INDEXES');

      --build alter statement to split the oldest partition into two
      l_sql :=
            'ALTER TABLE EMAPP.LP_INTERVALS SPLIT PARTITION '
         || l_partition_name
         || ' AT (TO_DATE ('''
         || TO_CHAR (l_lowval, 'DD-MON-YYYY')
         || ''', ''DD-MON-YYYY'')) INTO (PARTITION '
         || l_new_partition_name
         || ' TABLESPACE DATA, PARTITION '
         || l_partition_name
         || ' tablespace DATA) UPDATE GLOBAL INDEXES';

      DBMS_OUTPUT.put_line (l_sql);

      --split the oldest partition
      EXECUTE IMMEDIATE l_sql;
   END LOOP;
END;
/

have fun!

Finding Register Reads With Values That Go Backwards

We recently came across an issue where register reads may go backwards due to a quirk in our AMI system.  I was tasked with mining the EnergyIP data to find these.  While not perfect, this script does the job.  Below script will only look 15 days in the past.  You may want to go back further.



SELECT *
  FROM (  SELECT meter.udc_id,
                 rr.channel_id,
                 rr.insert_time,
                 rr.local_read_time,
                 rr.cum_read register,
                 /* lag function return the previous record value in a sorted and/or grouped list */
                 LAG (
                    cum_read,
                    1,
                    0)
                 OVER (
                    /* group by meter AND channel so we don't evaluate the previous record */
                    /* if the meter was changed out */
                    PARTITION BY meter.udc_id, rr.channel_id
                    ORDER BY meter.udc_id, rr.channel_id, rr.local_read_Time)
                    AS prior_register
            FROM emapp.register_reads rr,
                 emapp.lookup_mv_meters meter,
                 emapp.lookup_mv_channels1 channel
           WHERE     channel.channel_type = 'Cumulative Consumption'
                 AND channel.logical_channel_no = 1
                 AND channel.channel_id = rr.channel_id
                 AND rr.utc_read_time > SYSDATE - 15
                 AND channel.meter_id = meter.meter_id
        ORDER BY meter.udc_id, rr.channel_id, rr.utc_read_time ASC)
 WHERE /* skip the first interval */
       prior_Register > 0
       /* register appeared to go backwards */
       AND register < prior_register
       /* omit cases that are likely due to differences in granularity between actual registers */
      /* and End of Interval snapshots due to decimals and rounding */
       AND prior_register - register > 1;

Correcting LP and Register Data Processed Delivered With Wrong Multiplier (7.2)

Every now and then our AMI system gets the wrong multiplier set for a module.  Since it delivers the data fully multiplied to EnergyIP, the data is stored incorrectly.  Fixing this can be daunting, so I put together a little script to help.  This script can easily be modified to divide the existing values should the multiplier have been set to high.  In our case its usually that the multiplier was set too low.

USE AT YOUR OWN RISK


/* This script takes arguments and will MULTIPLY reading values to correct them */
/* use with caution */
/* update 6/7/2013 */
/* This script requires a function call to convert UTC to our local time, mountain time */

/* the function */

CREATE OR REPLACE FUNCTION AMFC.to_mountain_time (DATE_INPUT IN DATE)
   RETURN DATE DETERMINISTIC
IS
   MT_DATE   DATE;
/******************************************************************************
   NAME:       to_mountain_time
   PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        9/7/2012   asanchez       1. Created this function.

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     utc_to_mountain_time
      Sysdate:         9/7/2012
      Date and Time:   9/7/2012, 3:39:25 PM, and 9/7/2012 3:39:25 PM
      Username:        asanchez (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/
BEGIN
   MT_DATE :=
      TO_DATE (
         TO_CHAR (
            FROM_TZ (
               TO_TIMESTAMP (TO_CHAR (DATE_INPUT, 'MM/DD/YYYY HH:MI:SS PM'),
                             'MM/DD/YYYY HH:MI:SS PM'),
               'UTC')
               AT TIME ZONE 'US/Mountain',
            'MM/DD/YYYY HH:MI:SS PM'),
         'MM/DD/YYYY HH:MI:SS PM');



   RETURN MT_DATE;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RAISE;
END to_mountain_time;
/

/* The script */

VAR before_date VARCHAR2(50);
VAR register_channel_id VARCHAR2(50);
VAR lp_channel_id VARCHAR2(50);
VAR multiplier_adjustment NUMBER;

EXEC :before_date := '05/01/2013 23:59:00';
EXEC :register_channel_id := '1-4A5QB';
EXEC :lp_channel_id := '1-4A5Q8';
EXEC :multiplier_adjustment := 10;

/* registers */

  SELECT local_read_time, cum_read rr_before
    FROM emapp.register_reads
   WHERE     channel_id = :register_channel_id
         AND local_read_time <= TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS')
ORDER BY local_read_time DESC;

UPDATE emapp.register_reads
   SET cum_read = cum_read * :multiplier_adjustment
 WHERE     channel_id = :register_channel_id
       AND local_read_time <= TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS');

  SELECT local_read_time, cum_read rr_after
    FROM emapp.register_reads
   WHERE     channel_id = :register_channel_id
         AND local_read_time <= TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS')
ORDER BY local_read_time DESC;

/* intervals */

  SELECT local_interval_time, lp_value lp_before
    FROM emapp.lp_intervals
   WHERE     channel_id = :lp_channel_id
         AND local_interval_time <=
                TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS')
ORDER BY local_interval_time DESC;

UPDATE emapp.lp_intervals
   SET lp_value = lp_value * :multiplier_adjustment
 WHERE     channel_id = :lp_channel_id
       AND local_interval_time <=
              TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS');

  SELECT local_interval_time, lp_value lp_after
    FROM emapp.lp_intervals
   WHERE     channel_id = :lp_channel_id
         AND local_interval_time <=
                TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS')
ORDER BY local_interval_time DESC;

/* lp batch update */

  SELECT end_read lpb_before
    FROM emapp.lp_batch
   WHERE     channel_id = :lp_channel_id
         AND amfc.to_mountain_Time (utc_end_time) <=
                TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS')
ORDER BY utc_end_time DESC;

UPDATE emapp.lp_batch
   SET end_read = end_read * :multiplier_adjustment
 WHERE     channel_id = :lp_channel_id
       AND amfc.to_mountain_Time (utc_end_time) <=
              TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS');

  SELECT end_read lpb_after
    FROM emapp.lp_batch
   WHERE     channel_id = :lp_channel_id
         AND amfc.to_mountain_Time (utc_end_time) <=
                TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS')
ORDER BY utc_end_time DESC;

Pruning data from billing tables in EnergyIP 7.2

I've published live, custom, cross system Oracle Apex reports linking the billing request table in EnergyIP to the billing information in our Customer Information System (Ventyx).  Performance was starting to take a turn for the worse since I didn't want to further index the tables for fear of breaking a future application of service packs or upgrades.  Also,  the table was getting fairly large (1.5 million rows) and it is not partitioned.

The right thing to do is probably to use dbms_redefinition to partition the table(s).  I'll push for a product enhancement instead :).

Since the source of record for our billing was our CIS system I decided that pruning was in order.  I came up with the following to prune all but the last ~90 days of data from the billing tables (adhering to constraints).  Take a backup/data pump export first!


DELETE FROM emapp.billing_comp_tracking
      WHERE billing_request_id IN
               (SELECT billing_request_id
                  FROM emapp.billing_request
                 WHERE utc_request_start_time < SYSDATE - 90);

DELETE FROM emapp.billing_data_change_event
      WHERE billing_request_id IN
               (SELECT billing_request_id
                  FROM emapp.billing_request
                 WHERE utc_request_start_time < SYSDATE - 90);

DELETE FROM EMAPP.BILLING_EXPORT_LOG
      WHERE billing_request_id IN
               (SELECT billing_request_id
                  FROM emapp.billing_request
                 WHERE utc_request_start_time < SYSDATE - 90);

DELETE FROM emapp.billing_detail
      WHERE billing_request_id IN
               (SELECT billing_request_id
                  FROM emapp.billing_request
                 WHERE utc_request_start_time < SYSDATE - 90);

DELETE FROM emapp.billing_sum_check_detail
      WHERE billing_sum_check_log_id IN
               (SELECT billing_sum_check_log_id
                  FROM emapp.billing_sum_check_log
                 WHERE billing_request_id IN
                          (SELECT billing_request_id
                             FROM emapp.billing_request
                            WHERE utc_request_start_time < SYSDATE - 90));

DELETE FROM emapp.billing_sum_check_log
      WHERE billing_request_id IN
               (SELECT billing_request_id
                  FROM emapp.billing_request
                 WHERE utc_request_start_time < SYSDATE - 90);

DELETE FROM emapp.billing_request
      WHERE utc_request_start_time < SYSDATE - 90;