Partition Maintenance
In this section:
- Prospective Allocation of clinical_study_ids for Partitioning
- Strategy for Ongoing Partition Maintenance
- Instructions for Partition Maintenance
- Rebuild Indexes after Partition Maintenance
- Using Read-Only Partitions to Minimize Backup
Parent topic: Partitioning and Indexing
Prospective Allocation of clinical_study_ids for Partitioning
-
Ensure that distributed studies do not intersperse clinical_study_ids arbitrarily.
If you intend to collapse studies into single partitions or to consolidate dormant studies into read-only partitions, you must ensure that new studies do not get created whose clinical_study_ids lie between existing studies. This can occur in distributed installations of Oracle Clinical when studies are created at multiple locations using database seeds that differ only in their trailing digits. To address this you should either share clinical_study_id sequence across locations by replicating OCL_STUDIES and creating studies centrally or, alternatively, you should ensure that sequences do not overlap by seeding at non-overlapping starting seed values, not just separate trailing digits. For instance, start location 1 at ID 101 and location 2 at ID 1000102 rather than 102.
-
Preallocate planned studies to achieve grouping of small studies.
To consolidate small studies in single partitions, you should make sure that they have contiguous ranges. For instance, for a new project, create placeholder planned studies for all phase 1 and phase 2 studies so that they have contiguous IDs. This allows them to be grouped into a single partition.
Parent topic: Partition Maintenance
Strategy for Ongoing Partition Maintenance
There are three types of partition maintenance activities:
-
Activities that rebuild partitions
-
Activities that split or merge partitions
-
Activities that change ongoing characteristics of partitions
Activities that rebuild partitions must be performed manually. These include rebuilding the indexes on a partition, moving the data in a partition to a different tablespace, and restructuring the data in a partition to reduce the number of extents or change the PCTFREE storage parameter. See the Oracle documentation for instructions on how to carry out these activities.
Oracle Clinical includes the partition mapping form (see planning-partitioning.html#GUID-B34AE0D4-2088-4CE2-8A62-6B5820E001FF__I1002624) and a utility script generator that support SPLIT and MERGE operations and changes to ongoing storage characteristics.
Note that all SQL activities are carried out while connected to the RXC account.
For more information , see:
- Rebuild Partitions
- Managing New Studies
- Reviewing for Partitions that Need Splitting or Merging
- Maintenance of Cost-Based Statistics
Parent topic: Partition Maintenance
Rebuild Partitions
Periodically, at weekly or monthly intervals depending on growth rate, review the current partition growth by querying the USER_SEGMENTS view:
SQL> select segment_name, partition_name, extents SQL> from user_segments SQL> where extents > 20 SQL> and segment_name like 'RESPONSE%';
This shows you any partitions that might need to be rebuilt.
In addition, you might want to initiate a manual process for identifying studies that are about to enter periods of intense reporting. The partitions for these studies could then be rebuilt, both to coalesce index space by rebuilding the indexes and, perhaps, to decrease the PCTFREE storage parameter if data is no longer changing.
See Example 9-6 for instructions on rebuilding partition indexes.
Parent topic: Strategy for Ongoing Partition Maintenance
Managing New Studies
Unless there is a study that will grow extremely rapidly, for instance, due to batch loading, there is no need to anticipate the creation of studies and preallocate their partitions. Since new studies are created with incrementally higher clinical_study_ids, they are automatically added to the maximum partition. As long as this partition has a storage clause with large enough space allocations, these studies can start there without significant performance impact. At planned intervals you should review this partition to determine how these studies should be partitioned and use the partition maintenance script (Instructions for Partition Maintenance) to split out the new partitions.
Use the script list_study_resp_cnt_part.sql to obtain a count by study of studies in a particular partition:
list_study_resp_cnt_part.sql partition_name spool_file_name
The following example lists the studies in the last partition, that is, the new ones:
SQL>@list_study_resp_cnt_part.sql RESP_CSID_LE_9999999998 new_studies.lis
Parent topic: Strategy for Ongoing Partition Maintenance
Reviewing for Partitions that Need Splitting or Merging
As with new studies, existing partitions that contain multiple studies should be periodically reviewed and split as needed.
You may also want to merge partitions for studies that become inactive or that did not grow as planned. In particular, you might want to use the approach of using read-only tablespaces for inactive studies discussed in Using Read-Only Partitions to Minimize Backup.
Parent topic: Strategy for Ongoing Partition Maintenance
Maintenance of Cost-Based Statistics
Whenever a partition is merged or split, or when the data volume in a partition has changed significantly, the cost-based statistics for that partition need to be refreshed. The command to refresh the statistics for a partition is:
ANALYZE TABLE Responses PARTITION (partition_name) COMPUTE STATISTICS;
If the partition is large, you can use the statistics estimation with a small sample size. However, since you do not need to analyze the whole table, but just partitions with changes, this may no longer be necessary:
SQL> analyze table responses partition (partition_name)
SQL> estimate statistics sample 5 percent;
One approach to maintaining the statistics is to use a table to hold the partition statistics from the previous partition maintenance and then drive the creation of the analyze statements from that table and the current statistics. The code fragment in Example 9-4 illustrates this approach. It triggers the ANALYZE when the data volume in a partition increases by more than 50%.
Example 9-4 Using a Table to Compute Statistics
CREATE TABLE resp_part AS SELECT partition_name, bytes FROM user_segments WHERE segment_name = 'RESPONSES'; spool analyze_responses.sql SELECT 'ANALYZE TABLE RESPONSES PARTITION ('|| u.partition_name||') COMPUTE STATISTICS' FROM resp_part r, user_segments u WHERE u.segment_name = 'RESPONSES' AND u.partition_name = r.partition_name AND u.bytes/1.5 > r.bytes UNION ALL SELECT 'ANALYZE TABLE RESPONSES PARTITION ('|| u.partition_name||') COMPUTE STATISTICS' FROM user_segments u WHERE u.segment_name = 'RESPONSES' AND not exists (select null from resp_part r where r.partition_name = u.partition_name)
Parent topic: Strategy for Ongoing Partition Maintenance
Instructions for Partition Maintenance
When you determine that you need to merge or split partitions, first use the Maintain Partition Mapping form (see planning-partitioning.html#GUID-B34AE0D4-2088-4CE2-8A62-6B5820E001FF__I1002624). Insert records for partitions that are to be split or delete records for partitions that are to be merged. You can also alter the storage clauses for partitions, although this affects only new storage allocations — it does not rebuild the existing partition space.
You then use the utility script gen_alter_partition.sql to generate the SQL partition maintenance commands.
gen_alter_partition output_file.sql
For example:
SQL> @gen_alter_partition.sql alter.sql
The generation script compares the partition definition in the Oracle Clinical Partition Mapping table with the actual partition structure in the database. It then generates the SQL DDL statements to merge or split the partitions and to alter the storage clauses (see Example 9-5).
You should review the generated script and make any necessary modifications before you run it.
Example 9-5 Sample Output from the Partition Maintenance Script
REM Split case 4: Split RESP_CSID_LE_10002201 into RESP_CSID_LE_10001901 at 10001902 ALTER TABLE responses SPLIT PARTITION RESP_CSID_LE_10002201 AT (10001902) INTO (PARTITION RESP_CSID_LE_10001901 , PARTITION RESP_CSID_LE_10002201 STORAGE (INITIAL 4M NEXT 4M MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) ); ALTER INDEX RESPONSE_PK_IDX MODIFY PARTITION RESP_CSID_LE_10001901 STORAGE (NEXT 2M MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) ; ALTER INDEX RESPONSE_RDCM_NFK_IDX MODIFY PARTITION RESP_CSID_LE_10001901 STORAGE (NEXT 4M MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) ; REM Merge case 2: Merge RESP_CSID_LE_10001102 into RESP_CSID_LE_10001202 ALTER TABLE responses MERGE PARTITIONS RESP_CSID_LE_10001102, RESP_CSID_LE_10001202 INTO PARTITION RESP_CSID_LE_10001202; REM Merge case 2: Merge RESP_CSID_LE_10001101 into RESP_CSID_LE_10001202 ALTER TABLE responses MERGE PARTITIONS RESP_CSID_LE_10001101, RESP_CSID_LE_10001202 INTO PARTITION RESP_CSID_LE_10001202;
Parent topic: Partition Maintenance
Rebuild Indexes after Partition Maintenance
After you run the partition maintenance script (see Example 9-5), some of the associated index partitions may be unusable. Use the SQL code shown in Example 9-6 to detect the affected partitions. It generates a script (rebuild.sql) that you run to rebuild the index partitions.
Example 9-6 SQL Code for Rebuilding Index Partitions
set pagesize 1000 set verify off set feedback off set heading off spool rebuild.sql select distinct 'ALTER TABLE RESPONSES MODIFY PARTITION '|| partition_name||' REBUILD UNUSABLE LOCAL INDEXES /' from all_ind_partitions where index_name in ('RESPONSE_PK_IDX', 'RESPONSE_RDCM_NFK_IDX') and status = 'UNUSABLE' / spool off set verify on set feedback on set heading on
Parent topic: Partition Maintenance
Using Read-Only Partitions to Minimize Backup
Oracle enables you to designate tablespaces as read-only. If a tablespace is designated read-only, any attempt to modify data in that tablespace causes an Oracle error. Since the tablespace can not be modified, it can be created using data files on a separate physical device that only needs to be backed-up after maintenance activity during which the tablespace is modifiable. Over time, a significant portion of the Responses table data volume could be migrated to such read-only tablespaces, dramatically reducing the data volume that needs to be backed up by nightly backups.
To implement this strategy, you would set up the tablespace using data files on a separate physical device. A manual process of identifying studies that will no longer have modifications must be implemented. At periodic intervals, quarterly or semi-annually, you would perform partition maintenance to rebuild the partitions for those studies. During the maintenance, you alter the tablespace to make it writable. You move the data by exporting the partition, dropping the partition, and recreating the partition using the new tablespace. Once all studies have been moved, you alter the tablespace to be read-only once again and perform a backup of the device.
Parent topic: Partition Maintenance