Skip to content
CA Capacity Management - 2.9.4
Documentation powered by DocOps

Partition the Capacity Command Center Database

Last update May 22, 2015

Capacity Command Center (CCC) supports Oracle database partitioning and compression to enhance database administration, management, availability, and performance for Oracle Enterprise databases.

Benefits

For large enterprises with large amounts of data, the benefits include the following:

Partitioning

Benefits of partitioning include improving the performance of the following database activities:

  • Migrations.
  • Precalculation rollups.
  • Queries from CCC, CA Current Capacity Reporter (CCR), and CA Capacity Manager.
  • Data management activities, including the nightly purge process. Older data can be dropped by partition during the nightly purge process, and dropping is far faster than deletion.

Compression

The benefits of compression include a significant reduction in the amount of storage required by the tables. Depending on your environment, you might also see performance improvements for the rollup process.

Note: If you want to compress tables that you previously partitioned in an earlier version of CCC or if you want to compress tables without also partitioning them, contact the CA services team.

About the Partitioned Tables

The product supports partitioning all fact tables using Oracle Range Interval partitioning scheme. The tables including their partitioning key and partition size are shown in the following table.

Note: Partitioning for the SERVER_FACT, ENTITY_DAY_FACT, and ENTITY_HOUR_FACT tables was available in an earlier release. If you already partitioned those tables, you can use the instructions in this guide to partition and compress the remaining tables.

Partitioning key and size

Table Name Partitioning Key Partition Size
APPLICATION_FACT Gmt_metric_date 5 days
CLUSTER_FACT Gmt_metric_date 5 days
CPU_FACT Gmt_metric_date 5 days
DISK_FACT Gmt_metric_date 5 days
ENTITY_FACT Gmt_metric_date 5 days
ENTITY_DAY_FACT Day_id 1 month
ENTITY_HOUR_FACT Day_id 5 days
NETWORK_FACT Gmt_metric_date 5 days
PRE_STAGING_ENTRY_METRICS DATE_HOUR_ID Hourly
PROCESS_FACT Gmt_metric_date 5 days
RESOURCE_POOL_FACT Gmt_metric_date 5 days
SAP_DAILY_FACT Gmt_metric_date 5 days
SAP_DAILY_MEMORY_FACT Gmt_metric_date 5 days
SAP_DAILY_SUMMARY_FACT Gmt_metric_date 5 days
SAP_HOURLY_TASKTYPE_FACT Gmt_metric_date 5 days
SAP_HOURLY_TIME_FACT Gmt_metric_date 5 days
SAP_RFC_FACT Gmt_metric_date 5 days
SERVER_FACT Gmt_metric_date 5 days
WORKLOAD_LOG_FACT Gmt_metric_date 5 days
WORKLOAD_TRANSACTION_FACT Gmt_metric_date 5 days

How Partitioning Works

The partitioning process scripts perform the following steps:

  1. Create an intermediate partitioned table with a "_P" suffix.
  2. Move and compress data from original table to the partitioned table using parallel Data Manipulation Language (DML).
  3. Rename original table, indexes, and constraints. The original table will now have a “_NP” suffix
  4. Rename the partitioned table to the original table name.
  5. Create indexes on partitioned tables in NOLOGGING, unusable mode.
  6. Build indexes in parallel on partitioned tables.
  7. Calculate statistics on the partitioned tables.

How the Purge Process Manages Partitions

If a fact table is partitioned, the nightly purge process can optionally drop the old partitions based on the retention policy that you specify. Dropping is significantly faster than the delete process used on non-partitioned tables.

Oracle does not allow the very first partition in an interval partitioning scheme to be dropped. For this reason, CCC creates a first partition configured to contain data prior to 2011-01-01. In most cases, you do not have data that old and this partition is empty. If you have data that old, you can unable to drop it. Consider not including it in the partitioning process (see Prune unwanted data instead of partitioning it).

CCC creates partitions containing five days of data for raw and hour fact tables. It creates partitions containing a month of data for the daily data fact tables. The nightly purge process identifies the partitions that contain data past the retention period and drops them. If a partition contains some data past the retention window and some inside the retention window, that partition is not dropped.

Was this helpful?

Please log in to post comments.