I just recently returned from the Middle East where I was visiting a client to conduct a performance analysis. As part of this exercise I spent a lot of time going over their purging strategies for their BPEL dehydration store. Since I just finished writing the 10g BPEL purging strategy white paper, this was a perfect ground to test some of the theories. After some careful analysis we concluded that we would have to use the hybrid approach – the multi-looped purge script and partitioning for XML_DOCUMENT as our purging strategy. While conducting this exercise I came up with an interesting way to automate the deletion (drop partitions) for the XML_DOCUMENT.
For this exercise we need to use the partitioning scheme and verify scripts as described in Michael Bousamra’s partitioning white paper. I am not going into the semantics of the hybrid approach (which is mentioned in my strategy white paper) but focus more on how to automate the deletion of XML_DOCUMENT partitions. Currently the verify script takes an array with a name of the partitions to check for deletion – this is a manual task that the DBA has to conduct i.e. the partition names have to be provided manually. The problem with this approach is that not only does someone have to remember all the partition names that are ready for deletion but also keep track of partitions that were not previously dropped.
For e.g. there are 6 monthly partitions: partitionA, partitionB, partitionC, partitionD, partitionE, partitionF and they are dropped on a monthly basis. Lets assume that in the 1st month partitionA was not dropped since there were still running BPEL instances. In the 2nd month the DBA would now have to pass in two names to the verify script, partitionA and partitionB. Lets assume that partitionB was dropped but partitionA was not (long running BPEL processes) then in the 3rd month the DBA has to pass in partitionA and partitionC to the verify scripts and so on. So in this case not only does the DBA have to remember what names to pass to the verify script but also keep a track of partitions that were not dropped in past purge cycles. As you can imagine this can quickly get complicated with a lot of partitions in the mix. Here is how this can be automated:
1. Create a new table called XML_DOC_PARTITION_STATE with 4 columns in the ORABPEL schema. The columns names are: PartitionName, StartDate, ExpiryDate, isDropped
XML_DOC_PARTITION_STATE
| PartitionName | StartDate | ExpiryDate | isDropped |
| partitionA | 01-01-2010 | 31-01-2010 | N |
| partitionB | 01-02-2010 | 28-02-2010 | Y |
| partitionC | 01-03-2010 | 31-03-2010 | N |
2. Create a DB trigger to automatically populate the above table whenever a new XML_DOCUMENT partition is created.
3. Create a SQL query which will read from the XML_DOC_PARTITION_STATE based on the expiry date and state and pass the names of the partitions to the verify script. (If you would like you can embed this query into the verify script directly – DC_EXEC_VERIFY): The SQL would look like this:
SELECT PARTITIONAME FROM XML_DOC_PARTITION_STATE WHERE EXPIRYDATE < SYSDATE-1 AND ISDROPPED=’N’;
The goal is to select all the partition names that have not been dropped and meet the purging date criteria. So based on the above example partitionA and partitionC would be selected and passed to the DC_EXEC_VERIFY.sql.
4. At the moment the verify script creates a report stating if a partition can be dropped or not. The actual deletion happens outside of the verify scripts. This can be automated by changing the DC_VERIFY.sql to add the ALTER table command directly in the verify script to do this in one shot:
THEN
IF (Ci_Ref_Part_Ok(UPPER(doc_drv_list(i))))
THEN
IF (Ad_Part_Ok(UPPER(doc_drv_list(i))))
THEN
UTL_FILE.Put_Line (PART_HANDLE,'PASS: ALL DOCUMENTS ARE UNREFERENCED THUS THE');
UTL_FILE.Put_line (PART_HANDLE,' XML_DOCUMENT PARTITION CAN BE DROPPED');
Delete_Partition(UPPER(doc_drv_list(i)));
ELSE
UTL_FILE.Put_Line (PART_HANDLE,'FAIL: AUDIT_DETAILS TABLE HAS ACTIVE DOCUMENTS');
UTL_FILE.Put_Line (PART_HANDLE,' THUS THE XML_DOCUMENT PARTITON CANNOT BE DROPPED');
The Delete_Partition function call will just do the following (pseudo code):
ALTER TABLE DROP PARTITION doc_drv_list(i) –> which is the current partition name that the script is looping over.
The above mechanism will generate the report and also drop the partition at the same time instead of doing this at separate times.
5. Once a partition has been dropped update the XML_DOC_PARTITION_STATE table to update the isDropped column to ‘Y’ for that partition. So the SQL in the DC_VERIFY.sql would look like:
THEN
IF (Ci_Ref_Part_Ok(UPPER(doc_drv_list(i))))
THEN
IF (Ad_Part_Ok(UPPER(doc_drv_list(i))))
THEN
UTL_FILE.Put_Line (PART_HANDLE,'PASS: ALL DOCUMENTS ARE UNREFERENCED THUS THE');
UTL_FILE.Put_line (PART_HANDLE,' XML_DOCUMENT PARTITION CAN BE DROPPED');
Delete_Partition(UPPER(doc_drv_list(i)));
Update_State_Table((UPPER(doc_drv_list(i)));
where the Update_State_Table function is just updating the state for that partition (pseudo code):
UPDATE XML_DOC_PARTITION_STATE SET ISDROPPED=’Y’ WHERE PARTITIONNAME=doc_drv_list(i);
COMMIT;
So using our above example if both partitionA and partitionC were dropped the XML_DOC_PARTITION_STATE would look like this:
XML_DOC_PARTITION_STATE
| PartitionName | StartDate | ExpiryDate | isDropped |
| partitionA | 01-01-2010 | 31-01-2010 | Y |
| partitionB | 01-02-2010 | 28-02-2010 | Y |
| partitionC | 01-03-2010 | 31-03-2010 | Y |
Summary:
By using the STATE tables approach you can automate the purging of XML_DOCUMENT partitions. There is no need to track or remember the partition names and the partitions can be dropped directly in the verify scripts. This same methodology can be applied for other partitioned tables to help with automated purging.
As always would love to hear your comments and or questions.
DA!
Posts
