oaug archive & purge sig ahmed alomari applications performance group oracle corporation june...

OAUG Archive & Purge SIG

Ahmed AlomariApplications Performance Group

Oracle CorporationJune 13, 2005

Agenda Partitioning Best Practices

– Partitioning Concepts– Supportability– Partitioning Methods– Customer Case Studies

Purging– Business Processes– Purging Strategies– New Purge Portal

Q & A

Partitioning Concepts

Data Partitioning: – Process of logically and/or physically segmenting

data (and it’s associated storage, tables and indexes) into more manageable pieces, in order to improve manageability and accessibility.

– ExampleDivide large Applications tables and their

indexes such as GL_BALANCES into smaller segments.

Partitioning

Oracle Applications utilizes partitioning in the standard product in many modules:

– Advanced Planning and Scheduling– Payables (Trial Balances)– Projects Resources– Workflow

Directory Services Runtime tables

– Daily Business Intelligence– HR (Employee Directory)– Engineering

Partitioning Supportability

Oracle Applications fully supports the use of custom partitioning of either Applications standard or custom tables.

Definition of custom partitioning: Changing the partitioning definition of an existing Applications table as delivered (out-of-the-box).

If custom partitioning is used, you must license the partitioning option.

Partitioning Advantages Performance

– Improves access path of most queries since the majority of the access involves current data as opposed to historical data. Optimizer automatically prunes unnecessary

partitions.– Analytical reports or period close jobs/reports

improve by scanning the current partition as opposed to all the partitions.

– Improves purge performance.– Significantly improves upgrade performance

Minimizes upgrade downtime.

Partitioning Advantages

CBO is partition aware and employs partition pruning at runtime.

– Eliminates partitions which are not needed by the SQL statement.

– Joins are also partition aware and benefit from pruning.

Parallel operations can occur at the partition level. Utilizes the Information Lifecycle Management (ILM)

infrastructure to allow partitions containing historical Applications data to be placed on lower cost storage devices.

Partitioning Advantages – ILM

After creating the physical storage tiers

Assign data partitions to appropriate storage tiers

Segment Compression can be used on historical partitions which are not frequently accessed.

All Orders

Q1Orders

Q2Orders

Q3Orders

Q4Orders

High PerformanceStorage Tier

Low Cost Storage Tier

Historical Storage Tier

PreviousOrders

Active Data

Less

Active Data

Historical

Data

Partitioning Methods

Rangepartitioning

Hashpartitioning

Compositepartitioning

Listpartitioning

• The following partitioning methods are available:

Partitioning Methods - Range

Range partitioning requires that rows identified by a "partition key" fall into a predefined range of values.

Value of the partition key determines the partition to which a row belongs.

• Range partitioning was introduced in Oracle8. Range partitioning is useful for Applications

tables for which the partition key matches the access path key.


GL_BALANCES

338,120,360 rows

JUN-044,886,548

rows

JUL-044,949,399

rows

AUG-043,833,931

rows

SEP-04464,417

rows

OCT-0434,212rows

Partition the GL runtime tables by period_name


CREATE TABLE GL_BALANCES(SET_OF_BOOKS_ID NUMBER(15) NOT NULL,CODE_COMBINATION_ID NUMBER(15) NOT NULL,CURRENCY_CODE VARCHAR2(15) NOT NULL,PERIOD_NAME VARCHAR2(15) NOT NULL,ACTUAL_FLAG VARCHAR2(1) NOT NULL,BUDGET_VERSION_ID NUMBER(15),LAST_UPDATE_DATE DATE NOT NULL,. . . . . . )PARTITION BY RANGE (PERIOD_NAME)(PARTITION jan04_per VALUES LESS THAN (‘JAN-2005’),PARTITION feb04_per VALUES LESS THAN (‘FEB-2005’). . . . . .);

• Range Partitioning Example:

Partitioning Methods - List

• Partitions are mapped to an enumerated list. • Allows data to be mapped to partitions by

specifying a list of discrete values for the partitioning key.

• List partitioning does not support multi-column partition keys.

• List partitioning was introduced in Oracle9i.


OE_ORDER_LINES_ALL

(40,362,302 rows)

Open Orders(192,403 rows)

Closed Orders(40,169,899 rows)

Partition the OM runtime tables by open_flag


CREATE TABLE OE_ORDER_LINES_ALL(LINE_ID NUMBER NOT NULL,ORG_ID NUMBER,HEADER_ID NUMBER NOT NULL,LINE_TYPE_ID NUMBER NOT NULL,LINE_NUMBER NOT NULL NUMBERORDERED_ITEM VARCHAR2(2000),OPEN_FLAG VARCHAR2(1) NOT NULL,. . . . . . )PARTITION BY LIST (open_flag)(PARTITION open_par VALUES (‘Y’),PARTITION close_par VALUES (‘N’)

);

• List Partitioning Example:

Partitioning Methods - Hash

Applies a hash function to the partitioning key to stripe data into partitions.

Controls the physical placement of data across a fixed number of partitions.

Hash partitioning was introduced in Oracle8i. Generally useful for Applications batch programs

which use parallel workers for which block contention is significant.

Order Import Invoice Import


Hash15,045,430

rows

Hash25,045,402

rows

Hash45,042,929

rows

Partition the OM runtime tables by the primary key

OE_ORDER_LINES_ALL

(40,362,340 rows)

Hash35,043,712

rows

Hash55,046,608

rows

Hash65,045,024

rows

Hash75.048.923

rows

Hash85,044,312

rows


CREATE TABLE OE_ORDER_LINES_ALL(LINE_ID NUMBER NOT NULL,ORG_ID NUMBER,HEADER_ID NUMBER NOT NULL,LINE_TYPE_ID NUMBER NOT NULL,LINE_NUMBER NOT NULL NUMBERORDERED_ITEM VARCHAR2(2000),OPEN_FLAG VARCHAR2(1) NOT NULL,. . . . . . )PARTITION BY HASH (LINE_ID)PARTITIONS 8. . .. . .;

• Hash Partitioning Example:

Partitioning Methods - Composite

Data is partitioned by using both the range method and then sub-partitioned by the hash method.

Composite partitioning combines the best of both worlds (range and hash) by allowing logical groupings at the partition level and handling data skew within the sub-partitions.

Composite partitioning was introduced in Oracle8i.


hash1 hash2 hash4

The Workflow Runtime tables use composite partitioning.

WF_ITEM_ACTIVITY_STATUSES

(51,741,860 rows)

hash3 hash5 hash6 hash7 hash8

Order Item Type(3,402,130 rows)

hash1 hash2 hash4hash3 hash5 hash6 hash7 hash8

Purchase Order Item Type

(13,319,867 rows)

Item KeyHash


create table wf_item_activity_statuses partition by range (item_type) subpartition by hash (item_key) subpartitions 8 (partition wf_item1 values less than ('A1'), partition wf_item2 values less than ('AM'), partition wf_item3 values less than ('AP'), partition wf_item4 values less than ('AR'), partition wf_item5 values less than ('AZ'), . . . partition wf_item48 values less than ('OE'), partition wf_item49 values less than ('OF'), partition wf_item50 values less than ('OK'), partition wf_item51 values less than ('OL'), . . . partition wf_item56 values less than ('PO'), partition wf_item57 values less than ('PQ'), partition wf_item58 values less than ('PR'), partition wf_item59 values less than ('QA'), . . . );

• Composite Partitioning Example:

Partitioning of Indexes

Indexes can be partitioned like tables Partitioned or nonpartioned indexes can be

used with partitioned or nonpartitioned tables Partioned indexes can be

– Global or local– Prefixed or nonprefixed

Normal Partitioned

Table Index Table Index

Partitioning in Oracle Applications

Customer Case Study– International Bank– Data Volumes

GL_JE_LINES (1.1 Billion rows) GL_CODE_COMBINATIONS (203 Million rows) GL_BALANCES (1.3 Billion Rows)

– Partitioning Method: Range– Partition Key: set_of_books_id– # of Partitions: 34– Achieved Throughput:

11.4 million journal lines imported and posted per hour


Customer Case Study– Australian Bank– Data Volumes

GL_JE_LINES (650 million rows) GL_CODE_COMBINATIONS (8.5 Million rows) GL_BALANCES (200 million Rows)

– Partitioning Method: Range– Partition Key: period_name– # of Partitions: 109– Achieved Throughput:

7.5 million journal lines imported and posted per hour


Partitioning Case Study– Payables Trial Balances– Data Volumes

AP_LIABILITY_BALANCE (64,254,450 rows)– Partitioning Method: Hash– Partition Key: ORG_ID– # of Partitions: 32– Achieved Throughput:

Trial Balance report runtime reduced from 2 hours to 10 minutes.

Purging in the eBusiness Suite

Purging

Archive and Purge are linked Purge should be done periodically

– Transient data (daily/weekly)– Closed documents (monthly)

Archive and Purge should be done before Upgrading– Pre-Upgrade Purge (Recommended by Oracle)– Considered part of upgrade strategy– Improves Upgrade performance (Less Data.. Less Work)

Reduced backup time, Less Maintenance

Purging

TechnicalFunctional Architecture

UpgradeStrategy

Archive/Purge Review



Purging

End User Requirements– Business: What data do I want?

Record Retention Requirements– Operational: What data can I remove?

Systems Analysis– Consider the tables to determine which of them

are candidates for purging– Check if the product has archive/purge

programs?

Purging

Specific to the needs of different types of users

Examples of questions that need to be answered:

– What are the business reporting requirements of archived data?

– Can the data be accessed?– Which Reports/UIs are accessed for historical

data?

Purging

You should implement a company wide archive and purge policy

– Requires both Functional & Technical resources.– Functional resource focuses on the needs of the

business and impact on the business and the users.

– Technical resource focuses on system sizing and impact on the system.

Purging

Use the Oracle supplied programs– Purge/Archive programs are available at the

Module Level in Oracle Applications.

Financials– GL, AR, AP, – Costing, – Cash Management, – HR, FA, PA

Manufacturing– Order Management, – INV, BOM, – WIP, QP, – Shipping, Purchasing

Product Concurrent Program Name

AOL Purge Concurrent Request and/or Manager Data

Purge "Sign on" Audit data

General Ledger GL Archive and Purge

Assets Mass Additions Purge Report

Purge accounting tables

Payables Purge

Receivables Archive and Purge

Order Management

Purchasing

Order Purge

Purge and Archive POs

Purge and Archive Programs

Purge Programs - Entities

Product Entity

Payables Invoices, Payments, Requisitions

Purchase Orders, Receipts, Suppliers

CUM Periods, Supplier Schedules, Accounting

Receivables Invoice, Debit Memo, Credit Memo, Chargeback, Deposit, Guarantee

General

Ledger

Journal Batches, Headers, Lines, Daily Aggregate Balances

Purging

Table Name Number of Rows Physical Size (GB)----------------------------- -------------- -----------------GL_JE_LINES 313,801,740 73RA_CUST_TRX_LINE_GL_DIST_ALL 307,912,540 56GL_IMPORT_REFERENCES 311,017,080 47AP_AE_LINES_ALL 142,108,220 40PA_EXPENDITURE_ITEMS_ALL 59,368,483 38GL_BALANCES 285,667,600 31OE_ORDER_LINES_ALL 37,135,440 28AP_INVOICE_DISTRIBUTIONS_ALL 42,744,430 20PA_COST_DISTRIBUTION_LINES_ALL 67,053,710 17RA_CUSTOMER_TRX_LINES_ALL 35,518,920 14WF_ITEM_ATTRIBUTE_VALUES 226,371,730 13AX_SLE_LINES 26,815,110 13GL_INTERFACE_HISTORY 34,729,540 13OKC_RULES_B 54,565,780 12OKC_RULES_B_X 48,339,090 11OKC_RULES_TL 108,173,990 10

Purging – 11i10 Enhancements

The new Purge Portal (11i10)– Allows all purge programs to be configured,

monitored, and initiated from a single portal.– View and set the execution frequency as well as

the purge job history.– Accessible from the Oracle Applications Manager

Site Map->Maintenance->Purging

Purging – New Purge Portal

Purging

Oracle is committed to providing Purge/Archive solutions.

Oracle strives to ensure that Purge/Archive programs exist for the key transactional entities.

Oracle continues to enhance the purging functionality in the eBusiness Suite.

Oracle solution is the only solution which automatically evolves with product and schema evolution.

Feedback

If you have any suggestions on Purge/Archive functionality in Oracle Applications please

send us an

e-mail:

[email protected]

AQ&Q U E S T I O N SQ U E S T I O N S

A N S W E R SA N S W E R S

oaug archive & purge sig ahmed alomari applications performance group oracle corporation june...

Documents

partitioning definition

partitioning advantagescbo

partitioning option

partitioning methods

use of custom partitioning

partition pruning

current partition

historical applications