Using Database Partitioning with Oracle E-Business Suite
The latest version of this document can be found in My Oracle Support Note "Using Database Partitioning with Oracle E-Business Suite (Document 554539.1)"
A list of "Out-of-the-Box Partitioned Tables in Oracle E-Business Suite" is also attached to this document.
In This Document
- Overview
- 1 Understanding Partitioning
- 2 Partitioning and Compression
- 3 The Evolution of Partitioning
- 4 Using Database Partitioning with Oracle E-Business Suite
- 5 Defining Partitioning Design
- 5.1 Overview
- 5.2 Is Partitioning Necessary ?
- 5.3 What are the Benefits of Partitioning ?
- 5.4 Which Objects Should be Partitioned ?
- 5.5 Which Partitioning Method and Keys Should be Used ?
- 5.6 How Should Indexes be Partitioned ?
- 6 Implementing and Testing Partitioning
- 6.1 Partitioned Table and Index Creation and Data Migration
- 6.2 Testing - Access Path Analysis
- 6.3 Performance Testing
- 6.4 Maintenance
- 7 Examples of Partition Methods and Keys for Oracle E-Business Suite Tables
- 8 Example of How to Partition an Oracle E-Business Suite Table - AP.AP_INVOICES_ALL (Oracle Payables)
- 8.1 Functional Analysis
- 8.2 How is this Table Used ? Analysis of eTRM/Object Dependencies
- 8.3 What Access Paths Are Used - Analysis of AWR views
- 8.4 Index Analysis
- 8.5 Conclusion for AP_INVOICES_ALL
- 8.6 Partitioning AP_INVOICES_ALL using Online Table Redefinition
- 9 Oracle Database Documentation Library Links
- Change Log
Overview
This document describes how partitioning can be used with Oracle E-Business Suite and the challenges involved.
It discusses the process, techniques and methods that can be used to:
Define the partitioning design (methods/keys) and it's impact on performance.
Implement and test the partitioning design.
To assist with understanding partitioning, it also provides links to the relevant sections of Oracle Documentation.
Partitioning can bring significant benefits in performance and manageability. However, if used improperly, it can have a negative impact on performance and manageability elsewhere.
Oracle E-Business Suite is a pre-built application with pre-defined tables and columns, which limits the choice of methods, keys and extensions (manageability and key). In addition, some tables in the base product are already partitioned as delivered (seeded). Do not make changes to this seeded partitioning.
As a result, the introduction of partitioning is a complex task. The partitioning design must be extensively defined, analyzed and tested.
In particular, the partitioning design should be tested thoroughly to ensure that performance will be enhanced in the intended areas, without resulting in significant performance regressions in other areas.
The information in this paper is not a substitute for the careful and extensive analysis and testing that should be performed before partitioning is implemented on a live system.
1 Understanding Partitioning
Before defining any partitioning design for Oracle E-Business Suite, it is important to understand partitioning. What it is, how it works and what the benefits are.
The following is a suggested list of documents to read or reference.
All the references below are based on the Oracle Database 12c Release 1 (12.1) and 19c documentation sets. The chapter numbers and locations may differ in earlier (and subsequent versions).
In particular, the content in the Oracle Database SQL Tuning Guide was previously found in the Oracle Database Performance Tuning Guide.
Oracle Database Concepts
Chapter 4 Partitions, Views, and Other Schema Objects
- Overview of Partitions
Oracle Database VLDB and Partitioning Guide
Chapter 1 Introduction to Very Large Databases
- All sections
Chapter 2 Partitioning Concepts
Partitioning Overview, particularly sections: Basics of Partitioning, Partitioning Key, Partitioned Tables, Partitioning for Information Lifecycle Management
Benefits of Partitioning - all sections
Partitioning Strategies - all sections
Partition Extensions - all sections
Indexing on Partitioned Tables, particularly sections: Deciding on the Type of Partitioned Index to Use, Local Partitioned Indexes, Global Partitioned Indexes, Global Non Partitioned Indexes
Chapter 3 Partitioning for Availability, Manageability and Performance
Partition Pruning - all sections
Partition Wise Joins (Partition Wise Operations 19c) - all sections
Index Partitioning - all sections
Recommendations for Choosing a Partitioning Strategy, particularly sections: When to Use Range or Interval Partitioning, When to Use List Partitioning, When to Use Hash Partitioning, When to Use Composite Partitioning
Chapter 4 Partition Administration
Specifying Partitioning When Creating Tables and Indexes, particularly sections: About Creating Range-Partitioned Tables and Global Indexes, Creating Interval-Partitioned Tables, Specifying Hash Partitioning When Creating Tables and Global Indexes (12.1 only). About Creating Hash Partitioned Tables and Global Indexes (19c only) , About Creating List-Partitioned Tables, Specifying Composite Partitioning When Creating Tables, Specifying Partitioning on Key Columns
Maintaining Partitioned Tables and Indexes, particularly sections: Maintenance Operations on Partitions That Can Be Performed, Updating Indexes Automatically (12.1 only)
Maintenance Operations Supported on Partitions and Updating Indexes Automatically (19c only)
Viewing Information About Partitioned Tables and Indexes: This gives a list of dictionary views that contain information on partitioned tables and indexes (see Oracle Database Reference > Part II Static Data Dictionary Views. - for a full description of each dictionary view).
Chapter 7 Using Partitioning in an Online Transaction Processing Environment
- All sections
Oracle Database SQL Language Reference
Basic Elements of SQL
- Syntax for Schema Objects and Parts in SQL Statements > References to Partitioned Tables and Indexes
ALTER INDEX:
Clauses alter_index_partitioning, index properties
Semantics > References to partitions and partitioning (especially restrictions with other features), especially the section "alter_index_partitioning"
Semantics > References to maintenance operations (add, cleanup, coalesce, drop, exchange, merge, modify, rebuild, rename, split, truncate etc)
Examples > Renaming an Index Partition / Splitting a Partition
ALTER TABLE:
Clause alter_table_partitioning
Semantics > References to partitions and partitioning (especially restrictions with other features), especially the section "alter_table_partitioning"
Semantics > References to maintenance operations (add, cleanup, coalesce, drop, exchange, merge, modify, rebuild, rename, split, truncate etc)
Examples > Splitting Table Partitions, Merging Two Table Partitions, Merging Four Adjacent Range Partitions, Adding Multiple Partitions to a Table, Dropping a Table Partition, Exchanging Table Partitions, Modifying Table Partitions, Moving Table Partitions, Renaming Table Partitions, Truncating Table Partitions, Updating Global Indexes, Updating Partitioned Indexes
CREATE INDEX:
Clauses global_partitioned_index / local_partitioned_index
Semantics > References to partitions and partitioning, especially restrictions with other features and the section Index Partitioning Clauses
Examples > Partitioned Index Examples
CREATE TABLE:
table_partitioning_clauses
Semantics > References to partitions and partitioning, especially restrictions and the sections :Table Partitioning Clauses > Notes on Partitioning in General / Restrictions on Partitioning in General
Examples > Partitioning Examples
Oracle Database Administrator's Guide
Chapter 20 Managing Tables
Redefining Tables Online
Oracle Database Reference
Part II Static Data Dictionary Views
This contains a full description of each dictionary view (listed in Oracle Database VLDB and Partitioning Guide > 4 Partition Administration > Viewing Information About Partitioned Tables and Indexes).
Oracle Database SQL Tuning Guide
Chapter 6 Explaining and Displaying Execution Plans > Display Execution Plans > Displaying Plans for Partitioned Objects: Example (19c only)
Chapter 7 Reading Execution Plans > Reading Execution Plans: Advanced > Viewing Partitioned Objects with EXPLAIN PLAN (12.1 only)
Chapter 12 Managing Optimizer Statistics: Basic Topics > Gathering Optimizer Statistics Manually > Guidelines for Gathering Optimizer Statistics Manually > Guideline for Partitioned Objects (12.1 only)
Chapter 12 Managing Optimizer Statistics: Basic Topics > Gathering Optimizer Statistics Manually > Gathering Incremental Statistics on Partitioned Objects (12.1 only)
Chapter 13. Gathering Optimizer Statistics > Gathering Optimizer Statistics Manually > Guidelines for Gathering Optimizer Statistics Manually > Guideline for Partitioned Objects (19c only)
Chapter 13. Gathering Optimizer Statistics > Gathering Optimizer Statistics Manually > Gathering Incremental Statistics on Partitioned (19c only)
Chapter 21 Optimizing Access Paths with SQL Access Advisor > About SQL Access Advisor > SQL Access Advisor Architecture > SQL Access Advisor Recommendations > Partitions (12.1 only)
Chapter 21 Optimizing Access Paths with SQL Access Advisor > About SQL Access Advisor > SQL Access Advisor Architecture > SQL Access Advisor Actions > PARTITION BASE TABLE / Special Considerations for Partitioning Recommendations (12.1 only)
Chapter 25 Optimizing Access Paths with SQL Access Advisor > About SQL Access Advisor > SQL Access Advisor Architecture > SQL Access Advisor Recommendations > Partitions (19c only)
Chapter 25 Optimizing Access Paths with SQL Access Advisor > About SQL Access Advisor > SQL Access Advisor Architecture > SQL Access Advisor Actions > PARTITION BASE TABLE / Special Considerations for Partitioning Recommendations (19c only)
Appendix A Using Indexes and Table Clusters > Guidelines for Using Partitioned Indexes for Performance (12.1 only)
Note that on 11g Release 2 (11.2) and prior releases all these sections are contained within the Oracle Database Performance Tuning Guide > Part IV Optimizing SQL Statements > Chapter 12 Using EXPLAIN PLAN / Chapter 13 Managing Optimizer Statistics / Chapter 14 Using Indexes and Clusters / Chapter 18 SQL Access Advisor.
My Oracle Support Documents
Information Center: Partitioning in the Oracle Database (Document 1477997.2)
Information Center: Using Partitioning in the Oracle Database (Document 1551512.2)
Information Center: Install and Configure Partitioning in the Oracle Database (Document 1484347.2)
Information Center: Partition Performance Issues (Document 1600945.2)
2. Partitioning and Compression
Partitioning is often used with compression and advanced compression in Oracle E-Business Suite. This is in cases where some parts of a table or index are candidates for compression, but others are not.
Compression may be used for lifecycle reasons, to reduce the storage (costs) for older data or for performance reasons (the reduced I/O outweighs the increase in CPU).
To find out more, read or reference the following documents.
All the references below are based on the Oracle Database 12c Release 1 (12.1) and 19c documentation sets. The chapter numbers and locations may differ in earlier (and subsequent versions).
Oracle Database Concepts
Chapter 2 Tables and Table Clusters
Overview of Tables > Table Storage > Table Compression
Chapter 3 Indexes and Index-Organized Tables
Overview of B-Tree Indexes > Index Compression
Chapter 12 Logical Storage Structures
Overview of Data Blocks > Data Block Compression
Oracle Database VLDB and Partitioning Guide
Chapter 3 Partitioning for Availability, Manageability, and Performance
Partitioning and Table Compression
Chapter 4 Partition Administration
Specifying Partitioning When Creating Tables and Indexes > Using Table Compression with Partitioned Tables
Specifying Partitioning When Creating Tables and Indexes > Using Key Compression with Partitioned Indexes
Chapter 6 Using Partitioning in a Data Warehouse Environment
Manageability > Partitioning and Data Compression (12.1 only)
Manageability in a Data Warehouse > Partitioning and Data Compression (19c only)
Oracle Database SQL Language Reference
ALTER INDEX
key_compression clause
Semantics > references to compression, especially the section on "Key Compression"
ALTER TABLE
table_compression clause
Semantics > references to compression, especially the section on "Table Compression"
CREATE INDEX
key_compression clause
Semantics > references to compression, especially the section on "Key Compression"
Examples > Compressing an Index: Example
CREATE TABLE
table_compression clause
Semantics > references to compression, especially the section on "Table Compression"
Oracle Database Administrator's Guide
Chapter 20 Managing Tables
Guidelines for Managing Tables > Consider Using Table Compression > Compression and Partitioned Tables
Oracle Database Performance Tuning Guide
Chapter 4 Configuring a Database for Performance
Creating and Maintaining Tables for Optimal Performance > Table Compression
Oracle BLogs
3. The Evolution of Partitioning
The partitioning functionality has been expanded with each new version of the database.
See Oracle Database New Features Guide for the new features in each version.
For the full set of partitioning functionality in each version, see the relevant sections in the following :
4. Using Database Partitioning with Oracle E-Business Suite
Typically, for Oracle E-Business Suite implementations, the majority of transactional data is concentrated in a small subset of key tables. These tables and the volumes/distributions of data within them is likely to vary between customers.
Database partitioning features can be used in an Oracle E-Business Suite environment and is supported, but it will require a full license for the Oracle Database Enterprise Edition along with the Oracle Partitioning option. Partitioning does not require any changes to the Oracle E-Business Suite application code.
However, partitioning is implemented at the database level, and it will only be effective if the application code includes conditions for it to be used. For example, it is easy to partition an existing non-partitioned table. But, if not planned carefully, it may severely degrade performance, with the inadvertent introduction of extra gets/reads, locking or other issues.
It is important to note that if an incorrect partitioning design is chosen, partitioning can actually degrade rather than enhance performance.
Note that several Oracle E-Business Suite modules take advantage of seeded partitioning. Do not make changes to this Seeded Partitioning (e.g. methods, keys, list values, range intervals, additional sub-partitioning etc).
4.1 What Is Seeded Partitioning?
Seeded Partitioning is also known as "partitioning out of the box".
Some partitioning (of tables/indexes) is included as part of the Oracle E-Business Suite install os is applied by standard upgrades and patches. This is "Seeded Partitioning".
Currently, several modules in Oracle E-Business Suite utilize partitioning in the base product.
Depending on version, these include:
- Subledger Accounting (XLA)
- Advanced Supply Chain Planning (MSC)
- Oracle Manufacturing Operations Center (MTH)
- Bills of Material (BOM)
- CRM Foundation (JTF)
- Workflow (WF)
- Receivables (Table AR_AUTOREM_INTERIM + Trading Community Architecture (HZ) tables)
- Product Hub (EGO)
- iStore (IBE)
- Engineering (ENG)
- Human Resources (PER)
- Projects (PA)
- Oracle In-Memory Cost Management (CMI)
- Sales Foundation (AS)
- Payables (AP_LIABILITY_BALANCE)
- Applications DBA (AD_PARALLEL_UPDATE_UNITS)
- Supply Chain Intelligence (ISC - obsolete)
- Project Intelligence (PJI - obsolete)
- Human Resources Intelligence (HRI - Obsolete)
- Operations Intelligence (OPI - Obsolete)
Some modules have widespread use of partitioning (e.g. Subledger Accounting (XLA), Advanced Supply Chain Planning (MSC), Oracle Manufacturing Operations Center (MTH)). Others only have one or two tables partitioned (e.g. Payables (AP), iStore (IBE), Applications DBA (AD)).
4.2 What Is Custom Partitioning?
Custom partitioning applies to cases where an existing (unpartitioned) Oracle E- Business Suite product table / index is redefined as a partitioned table / index.
For example, partitioning the table OE_ORDER_LINES_ALL, which is not partitioned in the standard product.
Note that the customer is responsible for the performance of custom partitioning.
4.3 Support Implications for Custom Partitioning
Custom Partitioning should be transparent to the users and should not cause any functional issues.
Custom partitioning should not cause Oracle E-Business Suite flows or transactions to fail.
Oracle Development will not consider changes to code to support a specific custom partitioning design as a product defect.
The customer is responsible for the resolution of performance issues directly arising from custom partitioning.
4.4 Why Seeded Partitioning (Out of the Box) Should not be Altered
Do not modify existing base product tables and indexes that have already been partitioned by Oracle (Seeded Partitioning). Doing so can cause application errors, especially if the application code explicitly identify partitions. It is also unlikely to be as effective because the Oracle E-Business Suite code will have been written to take advantage of a specific partitioning design.
There are some cases where the standard application code that depends on seeded partitioning may break. For example:
Subledger Accounting application code explicitly references partitions
Workflow Directory Services has a concurrent program that bulk loads data into the WF_LOCAL_USER_ROLES table partitions.
These would stop working if the partitioning design is changed.
Other examples include some tables in MSC Planning, which are partitioned by PLAN_ID. In this case, the application code prunes by partition, and changing the design would cause performance issues.
4.5 What Licenses are Required for Custom Partitioning?
By default Oracle E-Business Suite is delivered with a restricted-use license for the Oracle Database Enterprise Edition. Modifications to the database (e.g. creating custom objects such as tables, packages, views, synonyms etc) are not licensed.
This restricted-use license covers the seeded partitioning delivered with Oracle E-Business Suite.
However, custom partitioning will require a full license for the Oracle Database Enterprise Edition along with the Oracle Partitioning option.
For more information see:
Applications Licensing Table > Oracle Technology Prerequisites Rules for Oracle E-Business Suite Applications
Oracle Database Licensing Information > Chapter 1 Licensing Information > Permitted Features, Options, and Management Packs by Oracle (look for Oracle Partitioning)
Oracle Database Licensing Information > Chapter 1 Licensing Information > Oracle Database Options and Their Permitted Features > Oracle Partitioning
5. Defining Partitioning Design
5.1 Overview
This section discusses how partitioning can be applied to Oracle E-Business Suite installations.
The key reasons for partitioning tables and indexes include improving performance, manageability and lifecycle. It is essential to employ an optimal partitioning design to achieve the performance that will meet the business objectives, without significant performance regressions elsewhere.
The biggest challenge when defining partition strategies for the Oracle E-Business Suite is that the application code has been prebuilt, which limits the choice of partitioning methods, keys and extensions (Manageability and Partitioning Key).
Therefore, the introduction of partitioning is complex, requiring both substantial analysis and robust testing of its effect on all the different components of workload that use the partitioned objects.
Furthermore, do not make changes to the seeded partitioning design delivered with the base product.
The following steps will help formulate a partitioning design. The process may be iterative. For example the benefits of partitioning are dependent on the proposed partition methods and key(s).
5.2 Is Partitioning Necessary ?
Partitioning is not necessarily the best solution to performance, availability or manageability problems.
Advances in hardware (processing speeds, I/O response times and bandwidth) and improvements in the Oracle Database (ITLs, parallel execution, backup and restore, online redefinition) have addressed many of the performance, availability and manageability issues that were previously encountered. On the other hand, the move to larger single instances and the increases in scale of hardware have resulted in larger environments.
For example, advances in I/O response times (especially due to solid state disks, engineered systems and caching) and device striping capabilities mean that partitioning is not necessarily needed as a solution to distribute I/O across devices. However, partitioning may still be needed to distribute workload and alleviate contention within the database.
There may also be other ways of resolving performance issues. For example :
SQL performance could be addressed by:
- Adding Indexes
Distributing workload between workers
Altering Initialization Parameters (e.g. SGA, redo log, CBO parameters)
Increasing Extent sizes
Archiving or purging data
Reconfiguring redo log
Re-organizing/rebuilding Tables
etc.
So, it is important to identify the root cause of a performance issue.
See My Oracle Support document "Oracle E-Business Suite Performance Guide (Document 1672174.1)" for more information on investigating performance issues.
Similarly, there may be alternative solutions to manageability issues.
5.3 What are the Benefits of Partitioning ?
There are many benefits of partitioning a table. These should be considered when choosing the partitioning method and key for a particular table (or group of tables).
The questions to answer are :
Performance - Distribution of Workload
Will the partition method assist performance on batch inserts, updates or deletes ?
Will the partition method and key(s) be able to spread the workload (relatively) evenly across partitions ?
Will there be enough partitions to minimize contention ?
Performance - Pruning and Joining
Will the partition key(s) enable significant use of partition pruning and partition-wise joins ?
Are the partition key(s) used as filters in a significant number of SQLs on the table ?
Are the partition key(s) used as a join in a significant number of SQLs on the table ?
Other Access Paths
Although, the partition key may be the most popular access path (either join or filter) to the table. There maybe many other access paths. The impact on these should be considered.
What indexes will other access paths (that do not include the partition key(s)) use ?
How much of the workload uses these access paths ?
What performance impact (improvement or regression) will partitioning have if these indexes are locally partitioned ?
Manageability
Will the partitioning method and key improve manageability of the table ? Will it split the table into manageable chunks, i.e. not too many ?
Are the groupings logical, e.g. splitting into separate business areas or organizations ?
Lifecycle
Will the partition method and key enable older data to be stored on slower and cheaper storage ?
How will the indexes be partitioned to avoid older partitions being accessed in most operational SQL (e.g. SQL used for online transactions, enquiries and reports) ?
Or will the design be compromised by significant full table scans or range scans that scan both newer and older partitions ?
Could the data be purged by dropping (or truncating) partitions ? Or will there be data integrity/consistency issues ?
5.4 Which Objects Should be Partitioned ?
Having identified a table or index as a candidate for partitioning, the following two questions should be answered:
What is the functional purpose of the table, and what exactly does it store?
This will help determine how the table is being used within Oracle E- Business Suite.
How is the data being accessed?
There are several methods to determine access paths, such as Automatic Workload Repository(AWR) views or Partition Advisor (an Oracle Enterprise Manager plug-in). These tools should be run over at least a complete monthly business cycle to provide a full understanding of how a particular table is accessed. Traces for a particular set of Oracle Applications Framework (Self Service/HTML) pages, Oracle Forms, Workflows, or concurrent requests can also help in the diagnosis.
Information on the content of AWR views is available in :
My Oracle Support Document "Performance Diagnosis with Automatic Workload Repository (AWR) (Document 1674086.1)" > 14 AWR Views
Oracle Database Reference > Part II Static Data Dictionary Views
The eTRM (Electronic Technical Reference Manual) can be used to analyze which objects and modules reference the tables and give an indication of how and where the table is used.
It will be necessary to drill down at least a couple of levels. Depending on version, many Oracle E-Business tables are referenced in turn by an editioning view, synonyms (implementing multi-organization or security functionality) and then subsequent views. The dictionary view DBA_DEPENDENCIES can also be used to obtain the same information as contained within the eTRM.
The eTRM can also be accessed from My Oracle Support Document "ETRM DIRECT ACCESS DOCUMENT (Document 150230.1)".
Business and Functional Analysts should also be involved in this process. They should have a detailed knowledge of how the Oracle E-Business Suite modules are used, what the main business flows are and where the performance bottlenecks are occurring (from a business perspective).
What is the growth rate of the table? Analyzing the statistics over a period of time will help to understand the growth rate and any data patterns.
SQL Access Advisor
Although the partition advice in SQL Access Advisor (used against a real workload) may give some indication of where partitioning can help for specific activities and give a suggested method/key(s), the recommendations are made in isolation on a single table and usually with reference to a limited workload or a single SQL or SQL set.
The advice should be used as another piece of evidence within a more strategic approach to partitioning, considering other tables, their use and the wider workload.
For more information see:
Oracle Database SQL Tuning Guide > 21 Optimizing Access Paths with SQL Access Advisor > About SQL Access Advisor > SQL Access Advisor Architecture > SQL Access Advisor Actions > Special Considerations for Partitioning Recommendations (12.1 only)
Oracle Database SQL Tuning Guide > 25 Optimizing Access Paths with SQL Access Advisor > About SQL Access Advisor > SQL Access Advisor Architecture > SQL Access Advisor Actions > Special Considerations for Partitioning Recommendations (19c only)
5.5 Which Partitioning Method and Keys Should be Used ?
After it has been determined that a group of tables will benefit from partitioning, the next step is to identify an appropriate partitioning design (method and key(s)).
Partition Key
At the heart of any good partitioning design lies the choice of a suitable partitioning key.
There will be a natural partition key for many large transaction tables or entities. This will typically be the column that is most frequently used as the predicate to access (or join) the data. This can be confirmed from an examination of which SQL statements are using the table or by reviewing the AWR views.
Because the Oracle E-Business Suite application has already been designed and coded, the choice can be limited.
Partition Key Columns often used in equality conditions (e.g. = and IN) will make use of partition pruning on individual tables.
There will be columns that are shared by multiple tables and that are used in joins between these tables. Partition keys using these columns will make use of partition-wise joins.
Performance - Distribution of Workload
Partition Methods HASH, RANGE and LIST are applicable.
LIST will require a relatively short and fixed list of values for the partition key (column). Data should be evenly distributed across all partitions. There should be an even distribution of the "age" of data for each set of list values (i.e. new rows will be inserted evenly into all partitions).
RANGE will require a key that can be easily split into ranges of values, with data spread evenly across all ranges (partitions). It is not always possible to predict how much data will map into a given range. In some cases, sizes of partitions may differ substantially, resulting in a marked reduction of performance for operations such as parallel DML.
The column cannot indicate age. There should be an even distribution of the "age" of data for each partition (range of values); new rows should be inserted evenly into all partitions. Sequential IDs should not be used as partition keys with the RANGE method in this case.
There should be no significant overhead in adding partitions for new data.
HASH will require a column with a relatively even distribution (i.e. no significant skew). It should have a significant number of distinct values, many 100s of times greater than the number of partitions. Sequential IDs (on transactional entities) can be ideal choices in this case: hashing ensures that new rows are inserted evenly into all partitions, the IDs are highly distinct.
Partition key(s), which are used in predicates (joins or filters) for a significant number of SQLs on the table, should be favoured. This is because they will improve performance elsewhere (pruning and joining) and improve manageability (locally partitioned indexes can be used more widely).
Note that HASH partitioning only utilizes partition pruning on equality predicates (e.g. = or IN).
Generally, this method of partitioning is most useful for Oracle E-Business Suite batch programs that use parallel workers and where database contention (e.g. extent / block contention : high water mark waits, buffer waits etc.) is significant.
Performance - Pruning and Joining
Partition Methods HASH, RANGE and LIST are applicable.
The partition key(s) must be used in predicates (joins or filters) for a significant number of SQLs on the table. Typically the tables joined to will be in the same group of tables being partitioned.
Manageability
Partition methods RANGE and LIST are applicable.
Ideally, the partition key should indicate a logical divide across the application (e.g. organization or business unit/area), but this is not essential.
Ideally, the partition key should have a relatively even distribution, but this is not essential.
Partition key(s), which are used in predicates (joins or filters) for a significant number of SQLs on the table, should be favoured. It is preferable to have locally partitioned indexes to avoid rebuilding whole indexes (when partition maintenance requires this).
Lifecycle
Partition Methods RANGE and LIST are applicable.
Note that a LIST partition can be used for multiple values. For example a single partition (for a year) could contain multiple values for period_name. For LIST there should be a relatively short list of fixed values (e.g. period number/name).
For the RANGE method the partition key might be a date, period number/name or a sequential ID.
The partition key(s) should define the age of the data and the likelihood of it being accessed. For example MTL_SYSTEM_ITEMS_B.inventory_item_id is a sequential ID, with the most recent items having the highest values. However, this does not indicate the age of the data or the likelihood of access, whereas AP_INVOICES_ALL.invoice_id does.
In order for partitioning to be used for purging (i.e. DROP PARTITION) then the partition key(s) must be able to define "purgeability" across all tables (in the same group that are purged together) without the risk of data consistency/integrity issues. The opportunities for this are limited in Oracle E-Business Suite.
If used to separate older data onto cheaper/slower storage then the partition key(s) should allow the majority of operational SQLs (online transactions, enquiries and reports) to only access segments that are on the faster storage.
There should be few SQLs that require a full scan of all partitions. Where the optimizer does favour full scans and hash joins then partition pruning and partition-wise joining should be used. Typically, the segments of partitioned indexes not prefixed by the partition key(s) should be stored on the faster storage. The same applies to non-partitioned and globally partitioned indexes.
Partition key(s), which are used in predicates (joins or filters) for a significant number of SQLs on the table, should be favoured. This is because they will improve performance elsewhere (pruning and joining) and improve manageability (locally partitioned indexes can be used more widely).
Although date columns (especially transaction or accounting date) can often be suitable for this purpose, they are not often used for filtering and never used for joining in Oracle E-Business Suite. So for other access paths there will be a significant overhead: scanning multiple partitions of local indexes (even though the older table partitions are not actually visited).
There are a few instances where a period column is used in Oracle E-Business Suite and this can be a good choice for a partition key; it is likely to be used a lot more in filters and joins. In this case, care needs to be taken when initially configuring periods (i.e. the naming structure used), as this could make specifying and maintaining partitions difficult.
Allowance must be made for old data that cannot be purged yet. It may be wise to have a single "catch all" partition for data that cannot be purged yet (e.g. old blanket purchase orders in PO_HEADERS_ALL that are still referenced by current purchase order releases, or invoices that have delayed payments, or long payment schedules). As older partitions become "nearly empty" they can be merged into this "catch all" partition.
The impact of storing older data on cheaper/slower storage on future upgrades/patches should be considered. Future upgrades/patches sometimes need to change the structure of data or update data for all rows on a particular table. Storing older data on slower storage will slow the upgrade down considerably.
Row Movement
Consider if row movement should be enabled for the table (partitions). Can the partition key columns be updated ? If so an UPDATE statement will translate into a DELETE statement followed by an INSERT statement.
Nullable Columns
Nullable columns that contain a significant percentage of NULLs will not be a good choice for partition keys in any case. This means that a large number of rows (those where partition key = NULL) will be placed in the same partition.
System Partitioning
In system partitioning the placement of data is controlled by the application (for example the explicit specification of which partition to insert into). So it is not possible for Oracle E-Business Suite.
Reference Partitioning
Reference partitioning enables the partitioning of two tables that are related to one another by referential constraints. The partitioning key is resolved through an existing parent-child relationship, enforced by primary key and foreign key constraints. There are not many primary key constraints in Oracle E-Business Suite and even fewer foreign key constraints, so there are very few places where reference partitioning will be possible. There are a few on Activity Based Management (ABM), Global Accounting Engine (AX), Learning Management (OTA) and more on Human Resources (PER) and Payroll (PAY).
In addition Reference Partitioning is not supported with the online redefinition package (DBMS_REDEFINITION).
Virtual Column-Based Partitioning
PL/SQL function calls (to read data from other tables) cannot be used in virtual column definitions. So Virtual Column-Based Partitioning cannot be used to reference columns on other tables.
There are limited opportunities to use virtual-columns in Oracle E-Business Suite, most columns have one purpose or meaning, they do not (as seeded) contain substrings which give additional meaning.
Interval Partitioning
Interval Partitioning is an extension of RANGE partitioning. Additional partitions of a specified interval are created when data that is inserted exceeds all of the existing range partitions.
It will usually be suitable for cases where tables are Range partitioned on a key that is chronological (e.g. Date, Period etc) or pseudo-chronological (i.e. increases with time, such as a sequential ID).
5.6 How Should Indexes be Partitioned ?
Evaluate how each of the indexes should be partitioned. Should they be local partitioned (prefixed or nonprefixed), global non-partitioned or global partitioned.
Note that all partitions of a locally partitioned index will be scanned if the partition key is not an access predicate, whereas there will be only one index scan for a globally partitioned index.
Indexes Containing the Partition Key(s)
If the leading column(s) of the index is the partition key(s) then the index should be locally partitioned. This is a Local Prefixed Index.
Even if the partition key(s) is not the leading column(s) the index should usually be considered for local partitioning. It is likely that the partition key will be used as a predicate in many access paths, so partition pruning and partition-wise joining will still take place in those cases. Partitioning indexes by the same partition key as the table simplifies administration and decreases the likelihood of the index requiring rebuild.
Indexes not Containing the Partition Key(s)
What positive impact will local partitioning (instead of global non-partitioning) of these indexes have on distributing the workload across index partitions ?
In this case splitting into local partitions will spread the workload across more blocks. Usually, this impact will be higher for indexes with low selectivity (low number of distinct keys on index), because the inserts/updates into indexes are more likely to update the same blocks. It will also be higher for indexes that are growing monotonically (index insertions occur on the right edge).
What negative impact will local partitioning of these indexes have on SQLs that use access paths including the index ?
Usually, this impact will be lower for access paths with low selectivity (low number of distinct keys on index), because the overhead of visiting multiple index partitions will be lower (in comparison to the overall workload).
So, indexes with a lower selectivity will normally be considered for local partitioning (Local Nonprefixed Indexes) and those with higher selectivity will be considered for global non-partitioning.
However, the business needs are crucial. If, for example, the time taken by a batch insert (load) process is critical, but the customer can live with slower online queries and reports then local indexes will be favoured.
Normally all remaining indexes (that are not locally partitioned) should be Globally Non-Partitioned. But there could be reasons for global partitioning by a different key(s).
Other Advantages of Locally Partitioned Indexes
Locally partitioned indexes are better for manageability, as the index is split up into smaller and more manageable chunks.
Locally partitioned indexes are more likely to remain usable during administration tasks.
6. Implementing and Testing Partitioning
6.1 Partitioned Table and Index Creation and Data Migration
Using Online Table Redefinition (using the DBMS_REDEFINITION package) is the preferred method.
See Oracle Database PL/SQL Packages and Types Reference for more information on the built in package DBMS_REDEFINITION.
It can be used to make table structure modifications without significantly affecting the availability of the table. This allows the table to be accessible to both SQL queries and DML during much of the redefinition process. Although, if possible, it is recommended to complete the whole process during scheduled downtime.
Other methods can be used, such as manually creating an interim table and indexes with the partitioning structure and all dependent objects, populating data into the new table (either using Insert or Datapump Export/Import) and then renaming all the dependent objects for both the original and interim tables.
However, Online Table Redefinition is the simplest method, with the least opportunity for introducing errors. It also requires the minimum downtime.
Note that online table redefinition requires an amount of free space that is approximately equivalent to the space used by the table being redefined.
Note that the following tables cannot be redefined online :
Tables for which Flashback Data Archive is enabled. Flashback Data Archive cannot be enabled for the interim table.
Tables with BFILE columns. There are a very small number of Oracle E-Business Suite tables columns with this datatype.
Tables with LONG or LONG RAW columns. Note that these can be redefined online, but those columns must be converted to CLOBS or BLOBS respectively before online redefinition. This is not possible in Oracle E-Business Suite, the application code is expecting LONG or LONG RAW. There are a very small number of Oracle E-Business Suite tables columns with any of these datatypes.
Tables in the SYS and SYSTEM schema.
Temporary tables.
Tables redefinition cannot be done NOLOGGING.
However, it should be possible to use Online Table Redefinition to partition almost all Oracle E-Business Suite tables.
Care may need to be taken when applying subsequent patches and update packs, these may contain unpartitioned definition files (xdf or odf) for the partitioned tables.
See the example Partitioning AP_INVOICES_ALL using Online Table Redefinition for instructions on how to partition a table using Online Table Redefinition.
See the following for more information:
Oracle Database Administrator's Guide > 20 Managing Tables > Redefining Tables Online, particularly the sections:
Performing Online Redefinition with Multiple Procedures in DBMS_REDEFINITION. Note that the single procedure DBMS_REDEFINITION.REDEF_TABLE cannot be used to partition a table.
Online Table Redefinition Examples - Example 2 Redefines a table by adding new columns and adding partitioning (ignore the adding of new columns).
- Oracle Database VLDB and Partitioning Guide > 4 Partition Administration > Evolving a Non Partitioned Table into a Partitioned Table (12.1 only)
- Oracle Database VLDB and Partitioning Guide > 4 Partition Administration > Changing a Non Partitioned Table into a Partitioned Table (19c only)
6.2 Testing - Access Path Analysis
It is important to ensure that the proposed partitioning design does not cause performance regressions, so the theoretical partitioning design should be tested; for example to ensure that the transactions access the correct number of partitions. Always thoroughly test the newly partitioned tables and indexes before introducing them into a production instance. An inefficient partitioning scheme for a given table may result in worse performance rather than better.
Suitability of a partitioning design can be checked by performing some basic execution/explain plan analysis on the most popular SQLs containing the partitioned tables.
The explain / execution plans for SQLs can be displayed using EXPLAIN PLAN and DBMS_XPLAN.display.
The SQLT tool can also be used with the XPLAIN method.
See My Oracle Support document "Oracle E-Business Suite Performance Guide (Document 1672174.1)" for more information.
See Oracle Database PL/SQL Packages and Types Reference for more information on DBMS_XPLAN.
For example the output can be used to check if partition pruning is occurring (the optimizer is accessing the correct number of partitions); the output will include the columns PSTART and PSTOP.
Note: the examples below are not actual SQL used in Oracle E-Business Suite, but simplifications used for illustrative purposes.
Example 1
In this case, AP_INVOICES_ALL is HASH partitioned on INVOICE_ID. Index AP_INVOICES_N7 is on columns VENDOR_SITE_ID and is locally partitioned.
So, we can see that all partitions of the index AP_INVOICES_N7 have to be scanned and rows are (likely to be) accessed on all partitions of AP_INVOICES_ALL. There is no partition pruning.
variable B1 number; exec :B1 := 1991; EXPLAIN PLAN SET Statement_Id = 'PART_TEST' FOR SELECT av.vendor_id, av.vendor_site_id, ai.invoice_id, ai.invoice_num, ai.description, ai.invoice_amount, ai.amount_paid FROM ap_invoices_all ai, ap_supplier_sites_all av WHERE ai.vendor_site_id = av.vendor_site_id AND av.vendor_site_id = :B1; SELECT * FROM TABLE(dbms_xplan.display(statement_id => 'PART_TEST', FORMAT => 'ALL +OUTLINE'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 86910260
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 147 | 12789 | 129 (0)| 00:00:01 | | |
| 1 | NESTED LOOPS | | 147 | 12789 | 129 (0)| 00:00:01 | | |
| 2 | TABLE ACCESS BY INDEX ROWID | AP_SUPPLIER_SITES_ALL | 1 | 10 | 2 (0)| 00:00:01 | | |
|* 3 | INDEX UNIQUE SCAN | AP_SUPPLIER_SITES_U1 | 1 | | 1 (0)| 00:00:01 | | |
| 4 | PARTITION HASH ALL | | 147 | 11319 | 127 (0)| 00:00:01 | 1 | 8 |
| 5 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| AP_INVOICES_ALL | 147 | 11319 | 127 (0)| 00:00:01 | 1 | 8 | |* 6 | INDEX RANGE SCAN | AP_INVOICES_N7 | 147 | | 8 (0)| 00:00:01 | 1 | 8 | ------------------------------------------------------------------------------------------------------------------------------------- .... Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("AV"."VENDOR_SITE_ID"=TO_NUMBER(:B1)) 6 - access("AI"."VENDOR_SITE_ID"=TO_NUMBER(:B1))
....
Example 2
In this case, we can see that only one partition of the index AP_INVOICES_U1 and table AP_INVOICES_ALL is accessed; partition pruning has taken place. And this is determined by the partition key, which is included in the predicate (filter).
variable B1 number; exec :B1 := 69107; EXPLAIN PLAN SET Statement_Id = 'PART_TEST' FOR SELECT
ai.vendor_id,
ai.vendor_site_id,
ai.invoice_num,
ai.description,
ai.invoice_amount,
ai.amount_paid
FROM
ap_invoices_all ai
WHERE ai.invoice_id = :B1; SELECT * FROM TABLE(dbms_xplan.display(statement_id => 'PART_TEST', FORMAT => 'ALL +OUTLINE')); PLAN_TABLE_OUTPUT -----------------------------------------------------------------------------------------------------------------------
Plan hash value: 1826775486
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION HASH SINGLE | | 1 | 81 | 2 (0)| 00:00:01 | KEY | KEY |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| AP_INVOICES_ALL | 1 | 81 | 2 (0)| 00:00:01 | KEY | KEY |
|* 3 | INDEX UNIQUE SCAN | AP_INVOICES_U1 | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
----------------------------------------------------------------------------------------------------------------------- .... Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("AI"."INVOICE_ID"=TO_NUMBER(:B1))
....
Example 3
In this case, we can see that all partitions of the index AP_INVOICES_U1 and table AP_INVOICES_ALL have to be scanned.
This is because they are HASH partitioned by INVOICE_ID, so invoices in the range could hash to any of the partitions.
Note that if the table/index were RANGE partitioned, then partition pruning would take place, as it would be possible to identify which table/index partitions the INVOICE_IDs were in.
variable B1 number;
variable B2 number;
exec :B1 := 69100;
exec :B2 := 70000; EXPLAIN PLAN SET Statement_Id = 'PART_TEST' FOR SELECT
ai.vendor_id,
ai.vendor_site_id,
ai.invoice_num,
ai.description,
ai.invoice_amount,
ai.amount_paid
FROM
ap_invoices_all ai
WHERE ai.invoice_id BETWEEN :B1 AND :B2; SELECT * FROM TABLE(dbms_xplan.display(statement_id => 'PART_TEST', FORMAT => 'ALL +OUTLINE')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 942247692
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 239 | 19359 | 327 (0)| 00:00:01 | | |
|* 1 | FILTER | | | | | | | |
| 2 | PARTITION HASH ALL | | 239 | 19359 | 327 (0)| 00:00:01 | 1 | 8 |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| AP_INVOICES_ALL | 239 | 19359 | 327 (0)| 00:00:01 | 1 | 8 |
|* 4 | INDEX RANGE SCAN | AP_INVOICES_U1 | 430 | | 2 (0)| 00:00:01 | 1 | 8 |
--------------------------------------------------------------------------------------------------------------------------------
.... Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(TO_NUMBER(:B2)>=TO_NUMBER(:B1))
4 - access("AI"."INVOICE_ID">=TO_NUMBER(:B1) AND "AI"."INVOICE_ID"<=TO_NUMBER(:B2))
....
6.3 Performance Testing
Workload Testing
Although access path analysis can identify if a partitioning design is unsuitable, it will not identify if there are unexpected performance regressions or side-effects.
The only way of checking the full performance impact is by thorough performance testing of all the main processes that use the partitioned tables with real life workloads and data volumes. This should not only include the area targeted for performance improvement, but all other processes where the partitioned tables are used.
For example, a batch insert process may be the key process that requires performance improvement, but the tables may also be used in downstream processes, online enquiries and period end reports.
It is important to test a copy of the environment (with the proposed partitioning design) with normal loads, including:
Batch insert/update/delete
Other main flows
Data entry and maintenance using forms/self service pages
Reports and enquiries
Workload and scenarios can be replicated using Oracle Real Application Testing or Loadrunner.
See the following for more information:
Oracle Database Licensing Information > Chapter 1 Licensing Information > Oracle Database Options and Their Permitted Features > Oracle Real Application Testing
Oracle Database Testing Guide (Known as "Oracle Database Real Application Testing User’s Guide" in 11g Release 2 (11.2) and prior releases)
The performance of different scenarios tested using the above can also be monitored and measured using a number of diagnostics tools, such as AWR/ASH reports, SQL Trace/TKPROF, Operating System Statistics.
For more information see My Oracle Support documents:
Oracle E-Business Suite Performance Guide (Document 1672174.1)
Performance Diagnosis with Automatic Workload Repository (AWR) (Document 1674086.1)
Oracle E-Business SQL Trace and TKPROF Guide (Document 1674024.1)
Testing Specific SQLs
In addition the performance of individual processes and SQLs can also be monitored and measured using tools such as SQL Trace/TKPROF, AWR/ASH reports, Display Cursor / SQL Monitor Reports, SQLHC, SQLT etc.
For more information see My Oracle Support documents:
Oracle E-Business Suite Performance Guide (Document 1672174.1)
Performance Diagnosis with Automatic Workload Repository (AWR) (Document 1674086.1)
Oracle E-Business SQL Trace and TKPROF Guide (Document 1674024.1)
Example using Display Cursor report
In this example, we can see what impact HASH partitioning of AP_INVOICES_ALL by invoice_id has when index AP_INVOICES_N7 is locally partitioned (HASH by invoice_id).
Without Partitioning
ALTER SYSTEM FLUSH SHARED_POOL; ALTER SYSTEM FLUSH BUFFER_CACHE;
variable B1 number; exec :B1 := 1991;
SELECT av.vendor_id, av.vendor_site_id, ai.invoice_id, ai.invoice_num, ai.description, ai.invoice_amount, ai.amount_paid FROM ap_invoices_all ai, ap_supplier_sites_all av WHERE ai.vendor_site_id = av.vendor_site_id AND av.vendor_site_id = :B1;
SET pages 0 SET lines 300 SET LONG 10000 SET LONGCHUNKSIZE 10000 SPOOL <report_name>.txt
SELECT * FROM TABLE(dbms_xplan.display_cursor('1xnhr4bk9fcxg', NULL, 'ALL +ALLSTATS')); SPOOL OFF; ....
----------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 97 (100)| | 2229 |00:00:00.05 | 1695 | 1342 | | 1 | NESTED LOOPS | | 1 | 147 | 12789 | 97 (0)| 00:00:01 | 2229 |00:00:00.05 | 1695 | 1342 | | 2 | TABLE ACCESS BY INDEX ROWID | AP_SUPPLIER_SITES_ALL | 1 | 1 | 10 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | 3 | |* 3 | INDEX UNIQUE SCAN | AP_SUPPLIER_SITES_U1 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 2 | | 4 | TABLE ACCESS BY INDEX ROWID BATCHED| AP_INVOICES_ALL | 1 | 147 | 11319 | 95 (0)| 00:00:01 | 2229 |00:00:00.05 | 1692 | 1339 | |* 5 | INDEX RANGE SCAN | AP_INVOICES_N7 | 1 | 147 | | 1 (0)| 00:00:01 | 2229 |00:00:00.01 | 14 | 9 | ----------------------------------------------------------------------------------------------------------------------------------------------------------------- ....
With Partitioning
....
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 129 (100)| | | | 2229 |00:00:00.06 | 1802 | 1792 |
| 1 | NESTED LOOPS | | 1 | 147 | 12789 | 129 (0)| 00:00:01 | | | 2229 |00:00:00.06 | 1802 | 1792 |
| 2 | TABLE ACCESS BY INDEX ROWID | AP_SUPPLIER_SITES_ALL | 1 | 1 | 10 | 2 (0)| 00:00:01 | | | 1 |00:00:00.01 | 3 | 3 |
|* 3 | INDEX UNIQUE SCAN | AP_SUPPLIER_SITES_U1 | 1 | 1 | | 1 (0)| 00:00:01 | | | 1 |00:00:00.01 | 2 | 2 |
| 4 | PARTITION HASH ALL | | 1 | 147 | 11319 | 127 (0)| 00:00:01 | 1 | 8 | 2229 |00:00:00.06 | 1799 | 1789 |
| 5 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| AP_INVOICES_ALL | 8 | 147 | 11319 | 127 (0)| 00:00:01 | 1 | 8 | 2229 |00:00:00.05 | 1799 | 1789 |
|* 6 | INDEX RANGE SCAN | AP_INVOICES_N7 | 8 | 147 | | 8 (0)| 00:00:01 | 1 | 8 | 2229 |00:00:00.01 | 29 | 24 |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
....
So, we can see that the overall impact is not significant in terms of response times. The most important statistic in this isolated test case is buffers; the Actual Time and Reads could be influenced by other factors and activities taking place on the environment during the two tests. Although, there is an increase in the number of buffer gets, it is only an increase of around 6%.
We can also see that the number of reads has increased by 33%, this will be due to partitioning spreading the data (for a vendor site in this case) across more blocks. However, this is likely to be mitigated by blocks being in buffer cache (SGA) in a live scenario. The impact will also be reduced for higher data volumes (this test was on an instance of AP_INVOICES_ALL with less than 100,000 rows) and for a more even distribution of data (for a particular vendor site) over time.
The workload testing mentioned previously will assess whether the total impact of such regressions either outweighs the improvements; or is not a feasible price to pay for improved performance elsewhere (on critical time limited processes).
6.4 Maintenance
The impact on maintenance has to be considered as well.
How often will partitions need to be added, dropped, split ?
e.g. For tables partitioned by RANGE on a chronological key (such as sequential ID), it is likely that there will be a "catch all" partition that requires splitting at periodical intervals, there may also be historical partitions that require dropping or merging.
Will indexes require rebuilding ?
How can this be done repeatedly and robustly, without error ?
Can the process be automated ?
What impact will multiple partitions have on database maintenance activities ? Will it increase the DBA workload or will it make it more manageable ?
Are there available time windows for maintenance activities ?
Testing Maintenance
The maintenance processes should also be thoroughly tested for performance (ability to be carried out in the time windows available) and robustness.
7. Examples of Partition Methods and Keys for Oracle E-Business Suite Tables
This is not an exhaustive list, just an example of some of partitioning designs that may work well for some customers. There are many other examples that are not covered here.
There could be reasons why the examples that are covered are not suitable in all cases. Each customer should investigate, determine and test their own partitioning strategies.
There may also be additional tables that can be added to each case (for the same partition key) for some customers. They could have been omitted from the list below because (in the cases we examined) the volumes were too low or there were too many null values or there is not significant access via the partition key.
Note that the partitioning design should be checked for likely future business change. For example, is there likely to be a large increase in ORG_IDs or SET_OF_BOOKS_IDs as a system is rolled out to different geographies or as new businesses are acquired.
Table name(s) | Partition Method | Partition Key | Possible Partition Benefits | Discussion |
| AP_INVOICES_ALL, AP_INVOICE_LINES_ALL, AP_INVOICE_DISTRIBUTIONS_ALL Possibly AP_LIABILITY_BALANCE, AP_PAYMENT_SCHEDULES_ALL, AP_INVOICE_PAYMENTS_ALL | HASH | INVOICE_ID | Distribution of Workload Pruning and Joining | Used in significant number of filters and joins. Very high number of distinct keys and even distribution. |
| AP_INVOICES_ALL, AP_INVOICE_LINES_ALL, AP_INVOICE_DISTRIBUTIONS_ALL Possibly AP_LIABILITY_BALANCE, AP_PAYMENT_SCHEDULES_ALL, AP_INVOICE_PAYMENTS_ALL | RANGE | INVOICE_ID | Pruning and Joining DB Manageability Lifecycle | Because new / most recent invoices will be in the same partition, this method cannot be used for Distribution of Workload. |
| AP_AE_LINES_ALL, AP_AE_HEADERS_ALL | HASH | AE_HEADER_ID | Distribution of Workload Pruning and Joining | Used in significant number of filters and joins. Very high number of distinct keys and even distribution. |
| AP_AE_LINES_ALL, AP_AE_HEADERS_ALL, AP_LIABILITY_BALANCE (if AE_HEADER_ID is only null on a few rows) | RANGE | AE_HEADER_ID | Pruning and Joining DB Manageability Lifecycle | Because new / most recent AE_HEADER_IDs will be in the same partition, this method cannot be used for Distribution of Workload. |
ZX_LINES_DET_FACTORS, ZX_LINES | HASH | TRX_ID | Distribution of Workload Pruning and Joining | Used in significant number of filters and joins. Very high number of distinct keys and even distribution. |
| ZX_LINES_DET_FACTORS, ZX_LINES | RANGE | TRX_ID | Pruning and Joining DB Manageability Lifecycle | Because new / most recent TRX_IDs will be in the same partition, this method cannot be used for Distribution of Workload. |
| MTL_MATERIAL_TRANSACTIONS MTL_CST_ACTUAL_COST_DETAILS MTL_TRANSACTION_ACCOUNTS | HASH | TRANSACTION_ID | Distribution of Workload Pruning and Joining | Used in significant number of filters and joins. Very high number of distinct keys and even distribution. |
| MTL_MATERIAL_TRANSACTIONS MTL_CST_ACTUAL_COST_DETAILS MTL_TRANSACTION_ACCOUNTS | RANGE | TRANSACTION_ID | Pruning and Joining DB Manageability Lifecycle | Because new / most recent TRANSACTION_IDs will be in the same partition, this method cannot be used for Distribution of Workload. |
| MTL_MATERIAL_TRANSACTIONS MTL_TXN_REQUEST_LINES MTL_SYSTEM_ITEMS_B MTL_SYSTEM_ITEMS_TL MTL_ITEM_CATEGORIES MTL_DEMAND | HASH | INVENTORY_ITEM_ID | Distribution of Workload Pruning and Joining | Used in significant number of filters and joins. High number of distinct keys and even distribution. |
| MTL_MATERIAL_TRANSACTIONS MTL_TXN_REQUEST_LINES MTL_SYSTEM_ITEMS_B MTL_SYSTEM_ITEMS_TL MTL_ITEM_CATEGORIES MTL_DEMAND | RANGE | INVENTORY_ITEM_ID | Distribution of Workload Pruning and Joining DB Manageability | Because INVENTORY_ITEM_ID does not indicate the age (or likelihood of access) of a transaction / row then this method/key cannot be used for Lifecycle reasons |
| MTL_TXN_REQUEST_LINES, MTL_TXN_REQUEST_HEADERS | HASH | HEADER_ID | Distribution of Workload Pruning and Joining | Used in significant number of filters and joins. High number of distinct keys and even distribution. |
| MTL_TXN_REQUEST_LINES, MTL_TXN_REQUEST_HEADERS | RANGE | HEADER_ID | Pruning and Joining DB Manageability Lifecycle | Because new / most recent HEADER_IDs will be in the same partition, this method cannot be used for Distribution of Workload. |
| PAY_ELEMENT_ENTRIES_F PER_ALL_ASSIGNMENTS_F PAY_ASSIGNMENT_ACTIONS PER_PAY_PROPOSALS possibly PAY_PERSONAL_PAYMENT_METHODS_F PAY_PROCESS_EVENTS PAY_US_EMP_FED_TAX_RULES_F and other PAY_US_EMP tables | HASH | ASSIGNMENT_ID | Distribution of Workload Pruning and Joining | Used in significant number of filters and joins. High number of distinct keys and even distribution. |
| PAY_ELEMENT_ENTRIES_F PER_ALL_ASSIGNMENTS_F PAY_ASSIGNMENT_ACTIONS PER_PAY_PROPOSALS possibly PAY_PERSONAL_PAYMENT_METHODS_F PAY_PROCESS_EVENTS PAY_US_EMP_FED_TAX_RULES_F and other PAY_US_EMP tables | RANGE | ASSIGNMENT_ID | Distribution of Workload Pruning and Joining DB Manageability | Because ASSIGNMENT_ID does not indicate the age (or likelihood of access) of a transaction / row then this method/key cannot be used for Lifecycle reasons |
| PAY_RUN_RESULTS PAY_RUN_RESULT_VALUES PAY_COSTS | HASH | RUN_RESULT_ID | Distribution of Workload Pruning and Joining | Used in significant number of filters and joins. Very high number of distinct keys and even distribution. |
| PAY_RUN_RESULTS PAY_RUN_RESULT_VALUES PAY_COSTS | RANGE | RUN_RESULT_ID | Pruning and Joining DB Manageability Lifecycle | Because new / most recent RUN_RESULT_IDs will be in the same partition, this method cannot be used for Distribution of Workload. |
| PAY_ELEMENT_ENTRIES_F PAY_ELEMENT_ENTRY_VALUES_F | HASH | ELEMENT_ENTRY_ID | Distribution of Workload Pruning and Joining | Used in significant number of filters and joins. Very high number of distinct keys and even distribution. |
| PAY_ELEMENT_ENTRIES_F PAY_ELEMENT_ENTRY_VALUES_F | RANGE | ELEMENT_ENTRY_ID | Pruning and Joining DB Manageability Lifecycle | Because new / most recent ELEMENT_ENTRY_IDs will be in the same partition, this method cannot be used for Distribution of Workload. |
| RA_CUSTOMER_TRX_ALL RA_CUSTOMER_TRX_LINES_ALL RA_CUST_TRX_LINE_GL_DIST_ALL RA_CUST_TRX_LINE_SALESREPS_ALL | HASH | CUSTOMER_TRX_ID | Distribution of Workload Pruning and Joining | Used in significant number of filters and joins. Very high number of distinct keys and even distribution. |
| RA_CUSTOMER_TRX_ALL RA_CUSTOMER_TRX_LINES_ALL RA_CUST_TRX_LINE_GL_DIST_ALL RA_CUST_TRX_LINE_SALESREPS_ALL | RANGE | CUSTOMER_TRX_ID | Pruning and Joining DB Manageability Lifecycle | Because new / most recent CUSTOMER_TRX_IDs will be in the same partition, this method cannot be used for Distribution of Workload. |
| OE_ORDER_LINES_ALL, OE_ORDER_HEADERS_ALL, OE_SALES_CREDITS | HASH | HEADER_ID | Distribution of Workload Pruning and Joining | Used in significant number of filters and joins. Very high number of distinct keys and even distribution. |
| OE_ORDER_LINES_ALL, OE_ORDER_HEADERS_ALL, OE_SALES_CREDITS | RANGE | HEADER_ID | Pruning and Joining DB Manageability Lifecycle | Because new / most recent HEADER_IDs will be in the same partition, this method cannot be used for Distribution of Workload. |
GL_BALANCES Possibly GL_JE_LINES | LIST or RANGE | PERIOD_NAME | Pruning and Joining DB Manageability Lifecycle | Used in significant number of filters and joins. Because new / most recent PERIOD_NAMEs will be in the same partition, this method cannot be used for Distribution of Workload. The HASH method cannot be used for distribution of workload either, because all new/recent rows will have the same PERIOD_NAME. |
| GL_BALANCES GL_JE_LINES GL_BC_PACKETS | HASH | CODE_COMBINATION_ID | Distribution of Workload Pruning and Joining | Used in significant number of filters and joins. High number of distinct keys and even distribution. |
| GL_BALANCES GL_JE_LINES GL_BC_PACKETS | RANGE | CODE_COMBINATION_ID | Distribution of Workload Pruning and Joining DB Manageability | Because CODE_COMBINATION_ID does not indicate the age (or likelihood of access) of a transaction / row then this method/key cannot be used for Lifecycle reasons |
| GL_JE_HEADERS GL_JE_LINES GL_IMPORT_REFERENCES GL_JE_SEGMENT_VALUES | HASH | JE_HEADER_ID | Distribution of Workload Pruning and Joining | Used in significant number of filters and joins. Very high number of distinct keys and even distribution. |
| GL_JE_HEADERS GL_JE_LINES GL_IMPORT_REFERENCES GL_JE_SEGMENT_VALUES | RANGE | JE_HEADER_ID | Pruning and Joining DB Manageability Lifecycle | Because new / most recent JE_HEADER_IDs will be in the same partition, this method cannot be used for Distribution of Workload. |
| WIP_PERIOD_BALANCES WIP_TRANSACTION_ACCOUNTS WIP_TRANSACTIONS WIP_ENTITIES WIP_FLOW_SCHEDULES | HASH | WIP_ENTITY_ID | Distribution of Workload Pruning and Joining | Used in significant number of filters and joins. High number of distinct keys and even distribution. |
| WIP_PERIOD_BALANCES WIP_TRANSACTION_ACCOUNTS WIP_TRANSACTIONS WIP_ENTITIES WIP_FLOW_SCHEDULES | RANGE | WIP_ENTITY_ID | Distribution of Workload Pruning and Joining DB Manageability | Because WIP_ENTITY_ID does not indicate the age (or likelihood of access) of a transaction / row then this method/key cannot be used for Lifecycle reasons |
| PO_HEADERS_ALL PO_LINES_ALL PO_RELEASES_ALL PO_LINE_LOCATIONS_ALL PO_DISTRIBUTIONS_ALL RCV_TRANSACTIONS RCV_ACCOUNTING_EVENTS | HASH | PO_HEADER_ID | Distribution of Workload Pruning and Joining | Used in significant number of filters and joins. Very high number of distinct keys and even distribution. |
PO_HEADERS_ALL | RANGE | PO_HEADER_ID | Pruning and Joining DB Manageability Lifecycle | Because new / most recent PO_HEADER_IDs will be in the same partition, this method cannot be used for Distribution of Workload. |
| Tables in many modules | HASH | ORG_ID or SET_OF_BOOKS_ID / LEDGER_ID | Distribution of Workload Pruning and Joining | ORG_ID or SET_OF_BOOKS_ID/LEDGER_ID are present on many tables across Oracle E-Business Suite. They are often used as a predicate (ORG_ID almost universally in Multi-Org views ). Concurrent programs often only process data for single ORG_IDs or SET_OF_BOOKS_IDs. Used in significant number of filters and joins. Only feasible if there are a large number of distinct values (>100) and a relatively even distribution. |
| Tables in many modules | RANGE | ORG_ID or SET_OF_BOOKS_ID / LEDGER_ID | Distribution of Workload Pruning and Joining DB Manageability | Only feasible if there is a relatively even distribution. |
| Tables in many modules | LIST | ORG_ID or SET_OF_BOOKS_ID LEDGER_ID | Distribution of Workload Pruning and Joining DB Manageability | Only feasible if there are relatively few values (< 100) being split up into a few list partitions (<20) |
8. Example of How to Partition an Oracle E-Business Suite Table - AP.AP_INVOICES_ALL (Oracle Payables)
For a larger Oracle E-Business Suite customers this table can be very large (upwards of 50GB in size, with over 100 million rows) and can be an ideal candidate for partitioning.
8.1 Functional Analysis
From a functionality perspective, this table holds the invoice information that is either:
- Populated into Payables by interfaces from other Oracle E-Business Suite modules (such as Purchasing) or external sources.
or
- Manually entered.
Typically, the information is accessed by self service pages, forms, concurrent programs and reports within the same module, but can also be accessed by other interrelated products.
8.2 How is this Table Used ? Analysis of eTRM/Object Dependencies
The eTRM can be used to identify the other objects and products that use the table AP.AP_INVOICES_ALL.
However, it will be necessary to view several levels of dependencies (referenced by) :
Table AP.AP_IMVOICES_ALL is referenced (in Oracle E-Business Suite 12.2.n) by editioning view AP.AP_INVOICES_ALL# and subsequently by synonyms AP.AP_INVOICES, AP.AP_INVOICES_ALL and triggers in the IGI, ITG, JAI and OIE modules.
- And in turn synonyms AP.AP_INVOICES, AP.AP_INVOICES_ALL are referenced by objects (views, packages, triggers) in modules:
- Receivables (AR)
- Bills of Material (BOM)
- Cash Management (CE)
- Cost Management (CST)
- Transportation Execution (FTE)
- Financials Common Modules (FUN)
- Federal Financials (FV)
- Oracle Environmental Accounting And Reporting (GHG)
- Process Manufacturing Financials (GMF)
- Grants Accounting (GMS)
- Payments (IBY)
- Oracle iProcurement (ICX)
- Contract Commitment (IGC)
- Public Sector Financials International (IGI)
- Oracle Landed Cost Management (INL)
- Inventory (INV)
- Internet Procurement Enterprise Connector (ITG)
- Asia/Pacific Localizations (JA)
- European Localizations (JE)
- Regional Localizations (JG)
- Latin America Localizations (JL)
- Assets (OFA)
- Lease and Finance Management (OKL)
- Projects (PA)
- Project Manufacturing (PJM)
- Property Manager (PN)
- Purchasing (PO)
- Sourcing (PON)
- iSupplier Portal (POS)
- Public Sector Financials (PSA)
- Payables (SQLAP)
- General Ledger (SQLGL)
- Treasury (XTR)
- E-Business Tax (ZX)
A hierarchy of objects that reference table AP.AP_INVOICES_ALL can be obtained by using the following SQL (iteratively for a few levels):
SELECT * FROM dba_dependencies WHERE referenced_owner = 'AP' and referenced_name ='<object_name>';
Take particular note of the views that reference AP.AP_INVOICES_ALL and its dependent objects (to several levels). In this case (for R12.2) there are a large number, across a number of different Oracle E-Business Suite modules. For example:
APPS ADS_DOCUMENT_NUMBERS_V APPS ADS_INVOICE_DETAILS ... APPS AP_DOCUMENTS_PAYABLE APPS AP_DOCUMENT_LINES_V APPS AP_EID_HOLD_V ... APPS AP_HOLDS_V APPS AP_INVOICES_ALL1_DFV APPS AP_INVOICES_ALL2_DFV APPS AP_INVOICES_ALL_DFV APPS AP_INVOICES_READY_TO_PAY_V APPS AP_INVOICES_V APPS AP_INVOICE_DISTRIBUTIONS_V APPS AP_INVOICE_EXTRACT_DETAILS_V APPS AP_INVOICE_EXTRACT_HEADER_V APPS AP_INVOICE_LINES_V APPS AP_INVOICE_PAYMENTS_V APPS AP_INVOICE_PAYMENT_HISTORY_V APPS AP_INVOICE_PRICE_VAR_V APPS AP_PAYMENT_EXTRACT_DETAILS_V APPS AP_PAYMENT_SCHEDULES_V APPS AP_PA_HEADER_REF_V ... APPS CE_AP_FC_DISC_INVOICES_V APPS CE_AP_FC_DUE_INVOICES_V ... APPS GHG_TRANSACTIONS_HISTORY_V APPS GHG_TRANSACTIONS_V ... APPS JG_ZZ_AP_IR_REP_ITF_V ... APPS PA_PROJ_AP_INV_DIST_V APPS PA_PROJ_AP_INV_DIST_V1 ... APPS XTR_AP_OPEN_TRX_V APPS XTR_AP_ORIG_TRX_V
8.3 What Access Paths Are Used - Analysis of AWR views
Analysis of the AWR views can be used to identify the best choice for partition key(s) and also analyze index usage.
Information on the content of AWR views is available in :
My Oracle Support Document "Performance Diagnosis with Automatic Workload Repository (AWR) (Document 1674086.1)" > 14 AWR Views
Oracle Database Reference > Part II Static Data Dictionary Views
Note that AWR retention period will limit the time period that can be reported (the default is 8 days), so it may be necessary to repeat the analysis several times to cover key periods such as Month End.
SQL similar to the following will identify the most common SQLs that reference AP_INVOICES_ALL and it's dependent views:
SELECT * FROM
(SELECT st.sql_id,
SUM(ss.executions_delta) total_execs
FROM dba_hist_sqlstat ss,
dba_hist_sqltext st,
v$database d
WHERE st.dbid = ss.dbid
AND ss.dbid = d.dbid
AND st.sql_id = ss.sql_id
AND
(st.sql_text LIKE '%AP_INVOICE%'
OR
st.sql_text LIKE '%ADS_DOCUMENT_NUMBERS_V%'
OR
....
OR
st.sql_text LIKE '%ADS_INVOICE_DETAILS%'
OR
....
OR
st.sql_text LIKE '%PA_PROJ_AP_INV_DIST_V%'
OR
....
OR
st.sql_text LIKE '%PO_SCC_DASHBOARD_V%'
OR
....
)
AND st.command_type != 47 -- exclude anonymous blocks
GROUP BY st.sql_id)
ORDER BY total_execs DESC;Note: Predicates to select ranges of snapshots (snap_id) could be added.
Additional delta columns could be added to identify other execution statistics (such as fetches, parses, disk reads, buffer gets, rows processed, cpu time, elapsed time etc).
Be careful to use the delta columns, which contain the totals for the snapshot, rather than the total columns, which contain cumulative totals. Also, if the AWR has been exported from the production environment and imported into a test environment, then the dbid could be explicitly specified (rather than joining with the current database id on view v$database).
The following query will return the SQL text for each SQL ID.
SELECT sql_id, sql_text FROM dba_hist_sqltext WHERE sql_id = '<sql_id>';
The following will give an indication of the bind values used and may indicate any skew, which may be relevant to the choice of partitioning method.
SELECT sql_id, name, value_string, count(*) FROM dba_hist_sqlbind WHERE sql_id = '<sql_id>' GROUP BY sql_id, name, value_string ORDER BY sql_id, name;
SQL similar to the following will show usage of AP_INVOICES_ALL indexes.
SELECT n.owner,
n.object_name,
SUM(r.logical_reads),
SUM(r.physical_reads),
SUM(r.physical_writes),
SUM(r.physical_read_req),
SUM(r.physical_write_req)
FROM dba_hist_seg_stat_obj n,
(SELECT ss.dataobj#,
ss.obj#,
ss.dbid,
SUM(ss.logical_reads_delta) logical_reads,
SUM(ss.physical_reads_delta) physical_reads,
SUM(ss.physical_writes_delta) physical_writes,
SUM(ss.physical_read_requests_delta) physical_read_req,
SUM(ss.physical_write_requests_delta) physical_write_req
FROM dba_hist_seg_stat ss,
v$database d
WHERE ss.dbid = d.dbid
GROUP BY ss.dataobj#, ss.obj#, ss.dbid) r
WHERE n.dataobj# = r.dataobj#
AND n.obj# = r.obj#
AND n.dbid = r.dbid
AND n.object_name like '%AP_INVOICES%'
AND n.object_type like '%INDEX%' -- include INDEX and INDEX PARTITIONS
GROUP BY n.owner, n.object_name
ORDER BY n.owner, n.object_name;Note that this will not include custom indexes if their name does not include the string 'AP_INVOICES'.
This will indicate the main columns used to access AP_INVOICES_ALL in filters (and joins) as well as reporting index usage.
If index usage has been monitored for any of the indexes (by using the command 'ALTER INDEX <index> MONITORING USAGE') then this will be recorded on DBA_OBJECT_USAGE / V$OBJECT_USAGE, but it will not indicate the level of usage.
The following will identify the access methods used for AP_INVOICES_ALL on actual execution plans:
SELECT p.object_type,
p.object_owner,
p.object_name,
p.operation,
p.options,
COUNT(*) plan_count,
SUM(p.cost),
SUM(p.cpu_cost),
SUM(p.io_cost),
SUM(p.temp_space)
FROM dba_hist_sql_plan p,
v$database d
WHERE p.dbid = d.dbid
AND p.object_name like '%AP_INVOICE%'
GROUP BY p.object_type, p.object_owner, p.object_name, p.operation, p.options
ORDER BY p.object_type, p.object_owner, p.object_name, p.operation, p.options;Unfortunately, it does not indicate the number of executions for each execution plan line.
Similarly the Active Session History (ASH) can be analyzed to give some idea of the level of activity for each access path (and index):
SELECT s.current_obj#,
o.owner,
o.object_name,
o.object_type,
s.sql_plan_operation,
s.sql_plan_options,
COUNT(*) samples,
SUM(s.tm_delta_time) sampled_time,
SUM(s.tm_delta_cpu_time) cpu_time,
SUM(s.tm_delta_db_time) db_time
FROM dba_hist_active_sess_history s,
dba_objects o,
v$database d
WHERE s.dbid = d.dbid
AND o.object_id = s.current_obj#
AND s.sql_plan_operation IS NOT NULL
AND o.object_name like '%AP_INVOICE%'
GROUP BY o.object_type, o.owner, o.object_name, s.sql_plan_operation, s.sql_plan_options, s.current_obj#
ORDER BY o.object_type, o.owner, o.object_name, s.sql_plan_operation, s.sql_plan_options, s.current_obj#;This could be further grouped by the columns program, module and action (if these have been populated) to ascertain where the main access/workload is occurring.
When identifying columns that are suitable for partition keys, the most suitable are those that are present across multiple tables, where the tables are joined using that column. Columns with low numbers of distinct values are unsuitable in some cases and those with high skew (including NULLs) are usually poor choices. So the following SQL can assist in identifying the groups of tables that can be partitioned on the same column(s).
SELECT c.owner,
c.table_name,
c.column_name,
c.nullable,
c.num_distinct,
c.num_nulls,
t.num_rows
FROM dba_tab_columns c,
dba_tables t
WHERE c.owner = t.owner
AND c.table_name = t.table_name
AND t.owner = '<owner>'
AND c.column_name = '<column_name>'
ORDER BY t.num_rows DESC, c.owner, c.table_name, c.column_name;8.4 Index Analysis
The usage of indexes can be ascertained from the SQLs on AWR views DBA_HIST_ACTIVE_SESS_HISTORY, DBA_HIST_SEG_STAT_OBJ and DBA_HIST_SQL_PLAN above.
Analysis of the statistics (DBA_INDEXES) on the indexes can indicate the size of the indexes (num_rows, leaf_blocks), the selectivity (distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key) and the clustering_factor.
SELECT owner,
index_name,
distinct_keys,
num_rows
FROM dba_indexes
WHERE table_name = 'AP_INVOICES_ALL'
ORDER BY index_name;
OWNER INDEX_NAME DISTINCT_KEYS NUM_ROWS
----- ---------------- ------------- ----------
AP AP_INVOICES_N1 92345 763824184
AP AP_INVOICES_N10 23896523 47739768
AP AP_INVOICES_N11 1243 24567123
AP AP_INVOICES_N12 56734 56734
AP AP_INVOICES_N13 15634 49872
AP AP_INVOICES_N14 3 567819879
AP AP_INVOICES_N15 2563 10734
AP AP_INVOICES_N16 5 954786784
AP AP_INVOICES_N17 0 0
AP AP_INVOICES_N18 954786784 954786784
AP AP_INVOICES_N19 14532 15324
AP AP_INVOICES_N2 35476 954786784
AP AP_INVOICES_N20 949831672 954786784
AP AP_INVOICES_N21 68 954786784
AP AP_INVOICES_N3 141 954786784
AP AP_INVOICES_N5 6532 954786784
AP AP_INVOICES_N6 939921731 954786784
AP AP_INVOICES_N7 67342 954786784
AP AP_INVOICES_N8 17435216 954786784
AP AP_INVOICES_U1 954786784 954786784
AP AP_INVOICES_U3 49872 49872Analysis of dictionary view DBA_IND_COLUMNS indicates which columns are in each index:
SELECT index_owner,
index_name,
column_name,
column_position
FROM dba_ind_columns
WHERE table_name = 'AP_INVOICES_ALL'
ORDER BY index_name, column_position;INDEX_OWNER INDEX_NAME COLUMN_NAME COLUMN_POSITION ----------- --------------- -------------------------- --------------- AP AP_INVOICES_N1 BATCH_ID 1 AP AP_INVOICES_N10 PO_HEADER_ID 1 AP AP_INVOICES_N11 PROJECT_ID 1 AP AP_INVOICES_N11 TASK_ID 2 AP AP_INVOICES_N12 VOUCHER_NUM 1 AP AP_INVOICES_N13 DOC_SEQUENCE_VALUE 1 AP AP_INVOICES_N14 GLOBAL_ATTRIBUTE1 1 AP AP_INVOICES_N15 PAID_ON_BEHALF_EMPLOYEE_ID 1 AP AP_INVOICES_N16 WFAPPROVAL_STATUS 1 AP AP_INVOICES_N17 VALIDATION_REQUEST_ID 1 AP AP_INVOICES_N18 VENDOR_ID 1 AP AP_INVOICES_N18 INVOICE_NUM 2 AP AP_INVOICES_N18 ORG_ID 3 AP AP_INVOICES_N18 EARLIEST_SETTLEMENT_DATE 4 AP AP_INVOICES_N18 INVOICE_DATE 5 AP AP_INVOICES_N19 CREDITED_INVOICE_ID 1 AP AP_INVOICES_N2 VENDOR_ID 1 AP AP_INVOICES_N2 INVOICE_TYPE_LOOKUP_CODE 2 AP AP_INVOICES_N2 PAYMENT_STATUS_FLAG 3 AP AP_INVOICES_N20 INVOICE_NUM 1 AP AP_INVOICES_N20 PARTY_ID 2 AP AP_INVOICES_N20 PAYMENT_STATUS_FLAG 3 AP AP_INVOICES_N21 ORG_ID 1 AP AP_INVOICES_N21 APPROVAL_READY_FLAG 2 AP AP_INVOICES_N21 VALIDATION_REQUEST_ID 3 AP AP_INVOICES_N21 PAYMENT_STATUS_FLAG 4 AP AP_INVOICES_N21 HISTORICAL_FLAG 5 AP AP_INVOICES_N3 PAYMENT_STATUS_FLAG 1 AP AP_INVOICES_N3 PAY_GROUP_LOOKUP_CODE 2 AP AP_INVOICES_N3 ORG_ID 3 AP AP_INVOICES_N5 INVOICE_DATE 1 AP AP_INVOICES_N5 ORG_ID 2 AP AP_INVOICES_N6 INVOICE_NUM 1 AP AP_INVOICES_N7 VENDOR_SITE_ID 1 AP AP_INVOICES_N8 CREATION_DATE 1 AP AP_INVOICES_U1 INVOICE_ID 1 AP AP_INVOICES_U3 DOC_SEQUENCE_ID 1 AP AP_INVOICES_U3 DOC_SEQUENCE_VALUE 2
8.5 Conclusion for AP_INVOICES_ALL
In this example, the majority of invoices are loaded from an external source and are not generated by another Oracle E-Business Suite module (e.g. Purchasing).
The primary performance concern is the length of time that batch processes are taking (e.g. load/interface, validation, payment and accounting) and the contention between different sessions/workers on these processes. Other means of reducing the contention on the interface and other batch processes have been carried out (e.g. striping, large extents to avoid High Water mark waits, SQL Tuning to reduce buffer busy waits etc), but there are still "hot blocks". These occur on the tables and on some of the indexes.
And of course there are downstream wait events caused by the delays (e.g. read by other session, enq: TX – contention etc.)
So a partitioning method of HASH is chosen to randomly and evenly distribute data across partitions to reduce the probability of "collisions" between multiple workers/sessions on "hot blocks".
The number of partitions should be high enough to avoid the contention. Too high a value will create additional administration and could cause performance issues on locally partitioned indexes. In this case a value of 8 is chosen. Note that all tables (of the same group), which share the same partition key and are often joined together by that key, should have the same number of hash partitions.
The analysis of AWR/eTRM has revealed that the largest number of filters and joins are on the column INVOICE_ID, which is a unique key.
So INVOICE_ID is chosen as the partition key. Because INVOICE_ID is unique (there is no skew) it will be distributed completely evenly across all 8 partitions.
It will also enable significant use of partition pruning and partition-wise joins.
Analysis of the AWR/eTRM has also revealed that table AP_INVOICES_ALL is frequently joined to tables AP_INVOICE_DISTRIBUTIONS_ALL and AP_INVOICE_LINES_ALL using INVOICE_ID. These tables could be candidates for hash partitioning too, using the same partition key: INVOICE_ID.
There are many other tables in Oracle Payables and other modules that contain the same column INVOICE_ID (e.g. Payables Localizations (JA, JE, JG, JL), Cost Management (CST), Federal Financials (FV), Payments (IBY), Public Sector Financials International (IGI), Assets (OFA), Projects (PA) etc). Some of these might be candidates for has partitioning by invoice_id, but it is more likely that they will not:
Many will be much smaller tables and/or only be populated for a subset of invoices (e.g. AP.AP_HOLDS_ALL, AP.AP_MC_INVOICES, AP.AP_INVOICE_PREPAYS_ALL, AP.AP_TRIAL_BALANCE, Localization tables, Projects tables).
Others will be temporary tables (e.g. AP.AP_DIST_LINE_GT) or may not be used by the customer (e.g. AP.AP_HISTORY_INVOICES_ALL, IGI.IGI_AP_INVOICES_ALL).
The interface tables AP.AP_INVOICES_INTERFACE, AP.AP_INVOICE_LINES_INTERFACE contain INVOICE_ID and maybe candidates for HASH partitioning by INVOICE_ID, however they may not often be joined with AP_INVOICES_ALL and other tables.
Other tables may contain INVOICE_ID and be populated for all invoices, but they may contain other columns that are more frequently used as joins/filters ( e.g. AP.AP_INVOICE_PAYMENTS_ALL (check_id, invoice_payment_id) and AP.AP_LIABILITY_BALANCE(ae_line_header_id, ae_line_id)).
The choice of whether to partition the indexes, locally or globally could be as follows :
Many of the indexes below will be less efficient (for queries) if locally partitioned. However, the prime concern is contention during the load (interface) and other batch processes, so local will be preferred
AP.AP_INVOICES_U1 (INVOICE_ID) - This index is prefixed by the partition key, so local is the ideal choice. It grows monotonically (index insertions occur on the right edge) and has a high number of distinct keys (unique) so hash partitioning by the same partition key as the table is ideal. Local Partitioned (prefixed).
AP.AP_INVOICES_U3 (DOC_SEQUENCE_ID, DOC_SEQUENCE_VALUE) - The columns in this index are not populated (at any stage) for the bulk of invoices on this system (which are imported from another source). So there is no contention during any of the batch processes. Global Non-Partitioned.
AP.AP_INVOICES_N1(BATCH_ID) - This column is populated (for all invoices from the external source, i.e. 80%) during the load/interface and not updated after that. However, each batch, typically contains around 8,000 rows. They are likely to all be in the same blocks (the index grows monotonically), but they are likely to be spread across more than 15 blocks, so the overhead of accessing by batch_id on queries and during subsequent batch processes, when the index is partitioned locally, is not high. Local Partitioned (Non-prefixed).
AP.AP_INVOICES_N2 (VENDOR_ID, INVOICE_TYPE_LOOKUP_CODE, PAYMENT_STATUS_FLAG) - This index is populated for all invoices during entry/interface and updated during payment. There is an average of around 27,000 rows for each distinct key. The index does not grow monotonically, so those values are likely to be spread across a high number of blocks. There will be some vendors with relatively few invoices, but still the overhead of local partitions on queries will be relatively low. Local Partitioned (Non-prefixed).
AP.AP_INVOICES_N3 (PAYMENT_STATUS_FLAG, PAY_GROUP_LOOKUP_CODE, ORG_ID) - This index is populated for all invoices during entry/interface and updated during payment. There are around 6.8 million rows for each distinct key. The index does not grow monotonically, so those values are likely to be spread across a high number of blocks. Local Partitioned (Non-prefixed).
AP.AP_INVOICES_N5 (INVOICE_DATE, ORG_ID) - This index is populated for all invoices during entry/interface, it is not updated after that. It has a low number of distinct values, there are 146,000 values for each distinct key. It also grows monotonically. It is not extensively used and is mostly used to return a range of values. The overhead of local partitions will be low. Local Partitioned (Non-prefixed).
AP.AP_INVOICES_N6 (INVOICE_NUM) - This index is populated for all invoices during entry/interface, it is rarely updated after that. It has a very high number of distinct values, it is nearly unique. It is also populated for all invoices. Rows are usually added at the same points in the index (each of the large vendors uses ranges of invoice numbers). It is used extensively for queries and the overhead of local partitions would be high. Global Non-Partitioned.
AP.AP_INVOICES_N7 (VENDOR_SITE_ID) - This index is populated for all invoices during entry/interface, it is not updated after that. This index has an average of around 14,000 rows for each distinct key. It does not grow monotonically. There will be some vendor_sites with relatively few invoices, but still the overhead of local partitions on queries will be relatively low. Local Partitioned (Non-prefixed).
AP.AP_INVOICES_N8 (CREATION_DATE) - This index is populated for all invoices during entry/interface, it is not updated after that. It has a high number of distinct values, there are only around 54 values for each distinct key. It also grows monotonically. It would be a candidate for Global Non-Partitioning. However, it is not extensively used and is mostly used to return a range of values. Local Partitioned (Non-prefixed).
AP.AP_INVOICES_N10 (PO_HEADER_ID) - The columns in this index are not populated (at any stage) for the bulk of invoices on this system (which are imported from another source). So there is no contention during any of the batch processes. Global Non-Partitioned.
AP.AP_INVOICES_N11 (PROJECT_ID, TASK_ID) - The columns in this index are not populated (at any stage) for the bulk of invoices on this system (which are imported from another source). So there is no contention during any of the batch processes. Global Non-Partitioned.
AP.AP_INVOICES_N12 (VOUCHER_NUM) - The column in this index is not populated (at any stage) for the bulk of invoices on this system (which are imported from another source). So there is no contention during any of the batch processes. Global Non-Partitioned.
AP.AP_INVOICES_N13 (DOC_SEQUENCE_VALUE) - The column in this index is not populated (at any stage) for the bulk of invoices on this system (which are imported from another source). So there is no contention during any of the batch processes. Global Non-Partitioned.
AP.AP_INVOICES_N14 (GLOBAL_ATTRIBUTE1) - This index has a very low number of distinct keys. There are a very high number of values (millions) for each key. There will be no discernible overhead from local partitioning. Local Partitioned (Non-prefixed).
AP.AP_INVOICES_N15 (PAID_ON_BEHALF_EMPLOYEE_ID) - The column in this index is not populated (at any stage) for the bulk of invoices on this system (which are imported from another source). So there is no contention during any of the batch processes. Global Non-Partitioned.
AP.AP_INVOICES_N16 (WFAPPROVAL_STATUS) - This index has a very low number of distinct keys. There are a very high number of values (100,000s) for each key. There will be no discernible overhead from local partitioning. Local Partitioned (Non-prefixed).
AP.AP_INVOICES_N17 (VALIDATION_REQUEST_ID) - This column is normally not populated. Global Non-Partitioned.
AP.AP_INVOICES_N18 (VENDOR_ID, INVOICE_NUM, ORG_ID, EARLIEST_SETTLEMENT_DATE, INVOICE_DATE) - This index is populated for all invoices during entry/interface, it is rarely updated after that (just for prepayments). It has a very high number of distinct values, it is nearly unique. It does not grow monotonically. It is used extensively for queries and the overhead of local partitions would be high. Global Non-Partitioned.
AP.AP_INVOICES_N19 (CREDITED_INVOICE_ID) - This column is only present for a low number of invoices. It will rarely be inserted to or updated during batch processes. Global Non-Partitioned.
AP.AP_INVOICES_N20 (INVOICE_NUM, PARTY_ID, PAYMENT_STATUS_FLAG) - This index is populated for all invoices during entry/interface. It is updated after that (during payment). It has a very high number of distinct values, it is nearly unique. It does not grow monotonically. It is used extensively for queries and the overhead of local partitions would be high. Global Non-Partitioned.
AP.AP_INVOICES_N21 (ORG_ID, APPROVAL_READY_FLAG, VALIDATION_REQUEST_ID, PAYMENT_STATUS_FLAG, HISTORICAL_FLAG) - This index has a very low number of distinct keys. Most distinct keys have a high number of values for each key. There will be no discernible overhead from local partitioning. Local Partitioned (Non-prefixed).
Note that these are not suggested partitioning strategies for AP_INVOICES_ALL indexes, they are the ones suitable to this case, the usage, statistics and distribution of data is likely to be very different for other customers.
Similarly, the partitioning method and key may not be the best solution for all or many customers. Each customer / partner must carry out their own analysis to determine the best partitioning method and key(s) and whether to partition indexes, either locally or globally.
8.6 Partitioning AP_INVOICES_ALL using Online Table Redefinition
The APPS user can be used to do this, it has the EXECUTE_CATALOG_ROLE and all the privileges needed.
For information on the DBMS_REDEFINITION built in package see Oracle Database PL/SQL Packages and Types Reference.
Note that the single procedure DBMS_REDEFINITION.REDEF_TABLE cannot be used to partition a table. This procedure provides a single interface that integrates several redefinition steps including the CAN_REDEF_TABLE Procedure, the START_REDEF_TABLE Procedure, the COPY_TABLE_DEPENDENTS Procedure and the FINISH_REDEF_TABLE Procedure. This procedure can change data storage properties and compress type, but cannot be used to partition a table.
1. Always ensure that there is a method in place to revert to a non- partitioned table if serious problems occur.
This will normally be a backup. In addition to this, it will be useful to identify and test removing the partitioning (and reverting to non-partitioned) using Online Table Redefinition. Keeping the original table and indexes (now with the interim names) for a while after the process may assist with this, although they will need truncating before reverting.
If there are errors or issues during the redefinition process, the procedure DBMS_REDEFINITION.abort_redef_table can be used to terminate the redefinition process, leaving the original and interim tables in their initial state.
See Oracle Database Administrator's Guide > 20 Managing Tables > Redefining Tables Online > Aborting Online Table Redefinition and Cleaning Up After Errors.
2. Verify that the table can be redefined online by invoking the CAN_REDEF_TABLE procedure.
In this case AP_INVOICES_ALL has a pseudo primary key (index AP_INVOICES_U1) so the redefinition method can be "by key" (DBMS_REDEFINITION.CONS_USE_PK).
"By key" is the preferred and default method, but will not be suitable for all tables. It requires a primary key or pseudo-primary key (unique key with all component columns having NOT NULL constraints). If there is no suitable primary key (or pseudo primary key) then use the method "by rowid" (DBMS_REDEFINITION.CONS_ USE_ROWID).
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(uname => 'AP',
tname => 'AP_INVOICES_ALL',
options_flag => DBMS_REDEFINITION.CONS_USE_PK);
END;
/It should be possible to redefine almost all Oracle E-Business Suite tables (as they were initially installed/seeded).
3. Create an interim table AP.AP_INVOICES_ALL_INT with the new partitioning structure.
Include the constraints. This should be created in the same schema as the table being redefined. Take care that the table has exactly the same properties (storage, tablespace etc) as the original.
In this example, constraints (e.g. NOT NULL) have been created. However, they can be omitted and copied from the original table using procedure COPY_TABLE_DEPENDENTS (see below).
CREATE TABLE "AP"."AP_INVOICES_ALL_INT"
(INVOICE_ID NUMBER(15,0) NOT NULL,
LAST_UPDATE_DATE DATE NOT NULL,
LAST_UPDATED_BY NUMBER(15,0) NOT NULL,
VENDOR_ID NUMBER(15,0) NOT NULL,
INVOICE_NUM VARCHAR2(50 BYTE) NOT NULL,
SET_OF_BOOKS_ID NUMBER(15,0) NOT NULL,
INVOICE_CURRENCY_CODE VARCHAR2(15 BYTE) NOT NULL,
PAYMENT_CURRENCY_CODE VARCHAR2(15 BYTE) NOT NULL,
PAYMENT_CROSS_RATE NUMBER NOT NULL,
.....
RELATIONSHIP_ID NUMBER(15,0),
PO_MATCHED_FLAG VARCHAR2(1 BYTE),
VALIDATION_WORKER_ID NUMBER(15,0))
....
PARTITION BY HASH (invoice_id) PARTITIONS 8;
COMMENT ON COLUMN AP.AP_INVOICES_ALL_INT.CUST_REGISTRATION_CODE IS 'Customer legal registration code';
COMMENT ON COLUMN AP.AP_INVOICES_ALL_INT.CUST_REGISTRATION_NUMBER IS 'Customer legal registration number';Also create indexes on the interim table with the new partitioning structure.
CREATE INDEX AP.AP_INVOICES_INT_N1 ON AP.AP_INVOICES_ALL_INT (BATCH_ID) .... LOCAL; CREATE INDEX AP.AP_INVOICES_INT_N10 ON AP.AP_INVOICES_ALL_INT (PO_HEADER_ID) ....; CREATE INDEX AP.AP_INVOICES_INT_N11 ON AP.AP_INVOICES_ALL_INT (PROJECT_ID, TASK_ID) .... LOCAL; CREATE INDEX AP.AP_INVOICES_INT_N12 ON AP.AP_INVOICES_ALL_INT (VOUCHER_NUM) ....; CREATE INDEX AP.AP_INVOICES_INT_N13 ON AP.AP_INVOICES_ALL_INT (DOC_SEQUENCE_VALUE) ....; CREATE INDEX AP.AP_INVOICES_INT_N14 ON AP.AP_INVOICES_ALL_INT (GLOBAL_ATTRIBUTE1) .... LOCAL; CREATE INDEX AP.AP_INVOICES_INT_N15 ON AP.AP_INVOICES_ALL_INT (PAID_ON_BEHALF_EMPLOYEE_ID) .... LOCAL; CREATE INDEX AP.AP_INVOICES_INT_N16 ON AP.AP_INVOICES_ALL_INT (WFAPPROVAL_STATUS) .... LOCAL; CREATE INDEX AP.AP_INVOICES_INT_N17 ON AP.AP_INVOICES_ALL_INT (VALIDATION_REQUEST_ID) ....; CREATE INDEX AP.AP_INVOICES_INT_N18 ON AP.AP_INVOICES_ALL_INT (VENDOR_ID, INVOICE_NUM, ORG_ID, EARLIEST_SETTLEMENT_DATE, INVOICE_DATE) ....; CREATE INDEX AP.AP_INVOICES_INT_N19 ON AP.AP_INVOICES_ALL_INT (CREDITED_INVOICE_ID) ....; CREATE INDEX AP.AP_INVOICES_INT_N2 ON AP.AP_INVOICES_ALL_INT (VENDOR_ID, INVOICE_TYPE_LOOKUP_CODE, PAYMENT_STATUS_FLAG) .... LOCAL; CREATE INDEX AP.AP_INVOICES_INT_N20 ON AP.AP_INVOICES_ALL_INT (INVOICE_NUM, PARTY_ID, PAYMENT_STATUS_FLAG) ....; CREATE INDEX AP.AP_INVOICES_INT_N21 ON AP.AP_INVOICES_ALL_INT (ORG_ID, APPROVAL_READY_FLAG, VALIDATION_REQUEST_ID, PAYMENT_STATUS_FLAG, HISTORICAL_FLAG) .... LOCAL; CREATE INDEX AP.AP_INVOICES_INT_N3 ON AP.AP_INVOICES_ALL_INT (PAYMENT_STATUS_FLAG, PAY_GROUP_LOOKUP_CODE, ORG_ID) .... LOCAL; CREATE INDEX AP.AP_INVOICES_INT_N5 ON AP.AP_INVOICES_ALL_INT (INVOICE_DATE, ORG_ID) .... LOCAL; CREATE INDEX AP.AP_INVOICES_INT_N6 ON AP.AP_INVOICES_ALL_INT (INVOICE_NUM) ....; CREATE INDEX AP.AP_INVOICES_INT_N7 ON AP.AP_INVOICES_ALL_INT (VENDOR_SITE_ID) .... LOCAL; CREATE INDEX AP.AP_INVOICES_INT_N8 ON AP.AP_INVOICES_ALL_INT (CREATION_DATE) .... LOCAL; CREATE UNIQUE INDEX AP.AP_INVOICES_INT_U1 ON AP.AP_INVOICES_ALL_INT (INVOICE_ID) .... LOCAL; CREATE UNIQUE INDEX AP.AP_INVOICES_INT_U3 ON AP.AP_INVOICES_ALL_INT (DOC_SEQUENCE_ID, DOC_SEQUENCE_VALUE) ....;
Note that if indexes are not created on the interim table (just copied from the original table as part of the COPY_TABLE_DEPENDENTS procedure) then the indexes will not be partitioned, they will be global non partitioned.
Grants (privileges) and triggers do not need to be created, because they can be created when dependent objects are copied below (using the COPY_TABLE_DEPENDENTS procedure).
4. Start the redefinition process by calling START_REDEF_TABLE
Note : For large tables, the performance of the redefinition step can be significantly improved by forcing parallel DDL, DML and parallel query e.g. :
ALTER SESSION FORCE PARALLEL DDL; ALTER SESSION FORCE PARALLEL DML; ALTER SESSION FORCE PARALLEL QUERY;
Remember to revert after online redefinition (if necessary) if the same session will be used for subsequent actions.
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(uname => 'AP',
orig_table => 'AP_INVOICES_ALL',
int_table => 'AP_INVOICES_ALL_INT',
options_flag => DBMS_REDEFINITION.CONS_USE_PK);
END;
/
Note that the copy_vpd_opt parameter does not need to be specified.
There are no VPD policies on the AP_INVOICES_ALL. In Oracle E-Business Suite the VPD policies are on either views or synonyms.
Editioning policies (ZD_SEED), where present, are on table# views (e.g. AP_EXPENSE_REPORTS_ALL#). Multi organization (ORG_SEC) policies, where present are almost always on SYNONYMs, although there are a few on views.
There are a few other sundry policies on synonyms and views.
5. Register the indexes as dependent.
The indexes will not be copied using the COPY_TABLE_DEPENDENTS procedure, the indexes from the interim table will be used instead. However, the indexes on the interim table need to be registered as dependent:
BEGIN
DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(uname => 'AP',
orig_table => 'AP_INVOICES_ALL',
int_table => 'AP_INVOICES_ALL_INT',
dep_type => DBMS_REDEFINITION.CONS_INDEX,
dep_owner => 'AP',
dep_orig_name => 'AP_INVOICES_N1',
dep_int_name => 'AP_INVOICES_INT_N1');DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(uname => 'AP',
orig_table => 'AP_INVOICES_ALL',
int_table => 'AP_INVOICES_ALL_INT',
dep_type => DBMS_REDEFINITION.CONS_INDEX,
dep_owner => 'AP',
dep_orig_name => 'AP_INVOICES_N2',
dep_int_name => 'AP_INVOICES_INT_N2');..... and so on for each of the indexes. END; /
6. Copy the dependent objects from the original table to the interim table using the COPY_TABLE_DEPENDENTS procedure
This includes triggers, materialized view logs, grants (privileges) and statistics.
Constraints and indexes do not need to be copied, these have already been created on the main table.
Errors should not be ignored.
DECLARE
l_num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'AP',
orig_table => 'AP_INVOICES_ALL',
int_table => 'AP_INVOICES_ALL_INT',
copy_indexes => 0,
copy_triggers => TRUE, -- default
copy_constraints => FALSE,
copy_privileges => TRUE, -- default
ignore_errors => FALSE, -- default
num_errors => l_num_errors,
copy_statistics => TRUE,
copy_mvlog => TRUE);
END;
/7. Perform Intermediate Synchronization (if necessary)
After the redefinition process has been started by calling START_REDEF_TABLE and before FINISH_REDEF_TABLE has been called, a large number of DML statements might have been executed on the original table.
If this is the case, then it is recommended that the interim table is periodically synchronized with the original table using the SYNC_INTERIM_TABLE procedure.
Calling this procedure reduces the time taken by FINISH_REDEF_TABLE to complete the redefinition process. There is no limit to the number of times that SYNC_INTERIM_ TABLE can be called.
8. Execute the FINISH_REDEF_TABLE procedure to complete the redefinition of the table.
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname => 'AP',
orig_table => 'AP_INVOICES_ALL',
int_table => 'AP_INVOICES_ALL_INT', dml_lock_timeout => NULL); -- default
END;
/
Use a value of NULL (default) for the dml_lock_timeout parameter so that the procedure FINISH_REDEF_TABLE does not time out.
During this procedure, the original table is locked in exclusive mode for a very short time, independent of the amount of data in the original table. However, FINISH_REDEF_TABLE will wait for all pending DML to commit before completing the redefinition.
9. Wait for any long-running queries against the interim table to complete, and then drop the interim table.
If the interim table is dropped while there are active queries running against it, an ORA-08103 error ("object no longer exists") may be encountered.
However, the interim table can be kept for a short while (and truncated) as part of a reversion strategy (see step 1).
10. Re-compilation and Refresh
Partitioning a previously non-partitioned table in Oracle E-Business Suite should not cause any other dependent objects (including PL/SQL and triggers) to become invalidated. However, a check for invalid objects should still be carried out. Re-compile any packages or triggers that have become invalid.
After redefining a table that has materialized view logs, the subsequent refresh of any dependent materialized views must be a complete refresh.
11. Testing
The whole process must be tested thoroughly, including:
Performance of affected online and batch processes (regression testing).
Functionality of affected online and batch processes (regression testing).
Performance (elapsed time and workload) during the partitioning process - particularly if carrying out the activity whilst users are online.
Performance and functional testing of the chosen reversion strategy (e.g. restore from backup or online table redefinition).
9. Oracle Database Documentation Library Links
The documentation libraries are available here.
Concepts
VLDB and Partitioning Guide
SQL Language Reference
Administrator's Guide
Reference
Performance Tuning Guide
SQL Tuning Guide
Prior to 12c Release 1 the content in the Oracle Database SQL Tuning Guide was found in the Oracle Database Performance Tuning Guide
PL/SQL Packages and Types Reference
Testing Guide
Known as "Oracle Database Real Application Testing User’s Guide" in 11g Release 2 and before
New Features Guide
19c
18c
12c Release 2
12c Release 1
11g Release 2
11g Release 1
10g Release 2
Licensing Information
Change Log
| Date | Description |
|---|---|
| 11 Sept 2019 | Updating for DB 19c, plus a few other minor changes |
| 27 Jan 2017 | Creation of completely re-written HTML Document to replace old whitepaper (pdf). Old whitepaper (pdf) is still attached to this document. |
| 29-Jan-2015 | Updated Out-of-the-Box Partitioned Tables for 12.1.3 and 12.2.4 |
| 28-Aug-2012 | Amended revision date on attachment with list of partitioned tables. Updated "Additional Information" reference. Reformatted document. |
| 01-Jun-2010 | Added document with lists of out-of-box partitioned tables. |
| 16-Mar-2009 | Revised and renamed paper. |
| 26-Nov-2008 | Added reference to Note 752322.1. |
| 16-Oct-2008 | Added references section. |
| 20-Feb-2008 | Initial publication |
No comments:
Post a Comment