How to manually partition the UIM database for Oracle
search cancel

How to manually partition the UIM database for Oracle

book

Article ID: 71942

calendar_today

Updated On:

Products

DX Unified Infrastructure Management (Nimsoft / UIM) CA Unified Infrastructure Management On-Premise (Nimsoft / UIM) CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

The following document describes a manual process for partitioning tables on a UIM Database hosted on Oracle 11gr2 Enterprise or above.

When enabling partitioning in an environment with a very large database, it is advisable to manually partition the largest tables before allowing the data_engine to partition the smaller ones; the data_engine may otherwise time out during maintenance periods if it is allowed to attempt partitioning the tables on its own.

Generally speaking, we always recommend that partitioning should be enabled where possible; even on a smaller database, it will significantly improve the performance of data maintenance and help to reduce administration. Partitioning enables data management operations such data loads, index creation and rebuilding, and backup/recovery at the partition level, rather than on the entire table. This results in significantly reduced times for these operations. Partitioning improves query performance.

On a larger database (over 10GB in size) this can be especially important, as data maintenance on an unpartitioned database can take many hours to complete.

While it is difficult to pinpoint an exact number of rows that constitutes a "large" table that is a candidate for manual partitioning, a rule of thumb is to manually partition any table with greater than 50 million rows. 

To determine the top 20 largest RN_QOS_DATA tables, you may run the following query as SYS/SYSDBA on the Oracle Database:

select * from (select owner,segment_name,segment_type,tablespace_name,round(bytes/1024/1024/1024,1) "size_in_GB" from dba_segments where owner=upper('uimuser') order by bytes/1024/1024/1024 desc) where segment_name like '%RN_QOS_DATA%' and segment_type = 'TABLE' and rownum < 21 order by 5 desc;

Please also note that in the Additional Information section of this KB Article, it explains the steps to unpartition tables.

************Warning Use at your own Risk************

Please review this KB Article and all queries with your DBA before following the steps.

We also highly recommend that these queries and their results be tested in a Test/DEV environment before running in Production.

Environment

Release: CNMSPP99000-8.3-Unified Infrastructure Mgmt-Server Pack-- On Prem
Component: data_engine

Cause

  • data_engine partitioning option

Resolution

Below is the full process and query for manually partitioning the tables associated with a QoS definition.

************IMPORTANT WARNING************

Note that once you begin this process, you have 14 days to complete it. As soon as you begin the next step here there's no turning back. If the process is not completed within 14 days, then you risk losing 1 days’ worth of data each day until the process is completed.

Before beginning the process, edit your data_engine.cfg and set:

   data_management_timeout = 65534

The default value of 7200 is too low, and partitioning operations may not fully complete. 65534 is the maximum value and we strongly recommend setting this to the maximum if not set already.

1. Open the data_engine probe using Raw Configure mode (select the probe then hold down the SHIFT key and rt-click to open in Raw Configure mode)

2. Change the value of data_management_partition from "no" to "yes" and then restart the data_engine.

3. This will commit a change to the database which is needed for the next steps.

4. Now open Raw Configure again, and set data_management_active to "no" and restart data_engine again.

5. From this point on, it’s permissible to leave data_engine active – there is no need to deactivate it.

6. Determine which table to partition first - in the example below we'll use RN_QOS_DATA_0035 but this will depend on the table size. (It’s advisable to start with the largest tables as mentioned above.)

7. The number of this table (e.g., 0035) will be the number you will plug into the following query, for the "IQosDefId" parameter, to partition the desired table (strip the leading zeroes). This will also match the qos_def_id associated with this table in S_QOS_DEFINITION.

------

set serveroutput on size 30000; 

declare 
lQosDefId number default 35;   -- change this number to reflect which RN table you wish to partition
lMode varchar2(127) default 'all'; 
lStartTime timestamp default systimestamp; 
lLogLevel number default 5; 
lRawCompressedRows number; 
lHistoryCompressedRows number; 
lRawShrunkRows number; 
lHistoryShrunkRows number; 
lErrorMessage varchar2(1024); 
lReturnCode number; 
begin 
spn_de_DataMaint(lQosDefId, lMode, lStartTime, lLogLevel, lRawCompressedRows, lHistoryCompressedRows, lRawShrunkRows, lHistoryShrunkRows, lErrorMessage, lReturnCode); 
dbms_output.put_line('lRawCompressedRows=' || to_char(lRawCompressedRows)); 
dbms_output.put_line('lHistoryCompressedRows=' || to_char(lHistoryCompressedRows)); 
dbms_output.put_line('lRawShrunkRows=' || to_char(lRawShrunkRows)); 
dbms_output.put_line('lHistoryShrunkRows=' || to_char(lHistoryShrunkRows)); 
dbms_output.put_line('lErrorMessage=' || lErrorMessage); 
dbms_output.put_line('lReturnCode=' || to_char(lReturnCode)); 
end;

Once you have run this query against all the tables you wish to partition, you can then re-enable data_engine maintenance as mentioned above, by setting data_management_active to "yes" again and restarting the data_engine.

Then on the next maintenance run, the data_engine will partition the remaining tables automatically.

Sample data_engine.cfg:

<setup>
   loglevel = 3
   logfile = data_engine.log
   logsize = 5000
   locale = English
   data_management_active = no
   data_management_time_spec = DTSTART:20151104T004000|RRULE:FREQ=DAILY;INTERVAL=1
   data_management_timeout = 65534
   auto_reindex = no
   hub_bulk_size = 1750
   thread_count_insert = 24
   data_management_compress = yes
   delete_raw_samples = 30
   delete_history_samples = 180
   raw_data_extra = 15
   daily_avg_age = 744
   daily_avg_age_extra = 15
   number_of_subpartitions = 5
   
data_management_partition = yes
   provider = SQLOLEDB
   database = <database_name>
   user = sa
   password = <encrypted_password_string>
   parameters = Network Library=dbmssocn;Language=us_english
   min_free_space = 10
   monitor_interval = 300
   alarm_severity = 5
   mysql_buffer_increase = 5000
   mysql_buffer_size = 5000
   log_bulk_stats = 0
   log_inserted_rows = 1
   log_lsv_rows = 0
   table_maintenance_mode = 0
   table_maintenance_online_mode = 0
   table_maintenance_loglevel = 0
   statistics_age = 24
   statistics_pattern = RN_QOS_DATA%
   statistics_loglevel = 0
   qos_probes = no
   lsv_sleep = 3
   bucket_flush_size = 5000
   bucket_flush_time = 5
   show_admin_upgrade_primary = yes
   server = <server_fqdn>,####
   port = ####
   servicename = 
   db_plugin = Microsoft
   data_engine_id = 1
   threads = 0
   index_frag_low = 5
   index_frag_high = 30
   compress_indexes_only = no
   index_pattern = 
</setup>

Additional Information

To reverse this process if necessary, and UNPARTITION a database that  is already partitioned, the steps are the same as above, other than step 2:

2. Change the value of data_management_partition from "no" to "yes" and then restart the data_engine.

    Instead, you should reverse this to...

2. Change the value of data_management_partition from "yes" to "no" and then restart the data_engine.

The remaining steps are identical - the same stored procedure is used to partition and unpartition the database.

Whether a partitioning or unpartitioning operation is performed is simply determined by the data_engine setting in step 2.