DX Operational Intelligence

Tech Tip: Partitioning Pre_Staging_Entity_Metrics table 

01-15-2019 07:42 AM

The use of Oracle Partitioning and Compression capabilities (available in Oracle Enterprise Edition only) is recommended for environments where the total size of the Data Manager / CCC database exceeds 100-200GB in size.  This is discussed on the DocOps pages here:  Partition the Capacity Command Center Database - CA Capacity Management - 2.9.4 - CA Technologies Documentation 

 

Executing the above procedure on a fresh install with an empty database will work successfully.  However, if using the UIM integration via the Capman_DA probe, the default partitioning of the Pre_Staging_Entity_Metrics table can cause an error during the partitioning of the form: 

ORA-12801: error signaled in parallel query server P002
ORA-14400: inserted partition key does not map to any partition
ORA-06512: at "CCCUSER.PARTITION_UTILS", line 82

or in operation, of the form:

ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

 

This is due to the fact that the default partitioning regime is to 'PARTITION BY RANGE(date_hour_id) INTERVAL (PARTITION_SIZE)' where PARTITION_SIZE=5, which will no longer support the needed range of partitions.

 

To resolve this issue, the default partitioning regime should be modified to use 'PARTITION BY RANGE(metric_time) INTERVAL (NUMTODSINTERVAL(PARTITION_SIZE, ''HOUR''))where PARTITION_SIZE=1 as follows:

 

  • Edit the Oracle PARTITION_UTILS package header to replace the existing definition of PSEM_IP_DDL (lines 66-71) with the definition below, and re-compile the package.

-- 2019-01-16: Modified to support change in partitioning for PRE_STAGING_ENTITY_METRICS

PSEM_IP_DDL varchar2(4000) := 'create table pre_staging_entity_metrics_p
   NOLOGGING COMPRESS PARTITION BY RANGE(metric_time)
   INTERVAL (NUMTODSINTERVAL(PARTITION_SIZE, ''HOUR''))
   STORE IN(TABLESPACE_LIST)
   (PARTITION p_first VALUES LESS THAN (to_date(''01-JAN-2011'',''DD-MON-YYYY'')))
   as select * from pre_staging_entity_metrics where 1 = 2';

 

  • Update the value of PARTITION_SIZE for this table:

update PARTITION_TABLES set PARTITION_SIZE = 1 where TABLE_NAME = 'PRE_STAGING_ENTITY_METRICS'; commit;

 

  • If the partitioning of this table has already been performed, either successfully or unsuccessfully under the old partitioning regime, it is necessary to clean the intermediate tables and partitioning process control table prior to executing again:

drop table PRE_STAGING_ENTITY_METRICS_P;

drop table PRE_STAGING_ENTITY_METRICS_NP;

delete from PARTITION_PROCESS_CONTROL where TABLE_NAME = 'PRE_STAGING_ENTITY_METRICS'; commit;

 

  • Edit the Oracle DATAMART_LIB package body to replace the existing definition of getPartitionHighDayValue (lines 2876-2889) with the definition below, and re-compile the package.

FUNCTION getPartitionHighDayValue (
p_table_name IN VARCHAR2,
p_partition_name IN VARCHAR2
)RETURN INTEGER
IS
dayValue number;
BEGIN
-- 2019-01-16: Modified to support change in partitioning for PRE_STAGING_ENTITY_METRICS
if (upper(p_table_name) = 'ENTITY_DAY_FACT' or upper(p_table_name) = 'ENTITY_HOUR_FACT' /* or upper(p_table_name) = 'PRE_STAGING_ENTITY_METRICS' */ ) then
select to_number(getPartitionHighValueAsString(p_table_name,p_partition_name)) into dayValue from dual;
elsif (upper(p_table_name) = 'PRE_STAGING_ENTITY_METRICS' ) then
select to_number(to_char(getPartitionHighValueAsDate(p_table_name,p_partition_name),'yyyymmddhh24')) into dayValue from dual;
else
select to_number(to_char(getPartitionHighValueAsDate(p_table_name,p_partition_name),'yyyymmdd')) into dayValue from dual;
end if;
return dayValue;
END;

 

This will enable the partitioning of Pre_Staging_Entity_Metrics to complete and operate successfully, and has shown significant performance improvements, particularly in larger UIM environments.

 

For help with implementing this Tech Tip, please contact CA Support.

Statistics
0 Favorited
4 Views
0 Files
0 Shares
0 Downloads

Tags and Keywords

Comments

05-17-2019 07:21 AM

Adrian,

   Thank you for the detailed post.  Very helpful.

 

David

Related Entries and Links

No Related Resource entered.