APPLIES TO:
Oracle Demantra Demand Management - Version 7.0.1 and later
Information in this document applies to any platform.
GOAL
Understand what Oracle partitions are and how they can improve Demantra performance.
Are you implementing partitions? Please see below for a link that provides exact instructions to partition the sales_data table. Your implementation may require slight modifications.
Demantra How to Create Sales_Data Partition SQL Exact Procedure Document 1516204.1
SOLUTION
This document discusses the following:
What to do before implementing partitions.
Basic Partitioning. What is partitioning?
Benefits of partitioning.
Partitioning for Availability.
Partition Options
Overview of Partitioned Indexes.
Partitioning for Performance.
Statistics Performed Correctly Improves Partition Performance.
Useful SQL
Recommendations
See Oracle Demantra Implementation Guide Release 7.3 Part No. E05136-10 Chapter 56 Document 443969.1
See Using Database Partitioning with Oracle E-Business Suite Document 554539.1
1. What to do before implementing partitions.
a. Use more database buffers to reduce the need for Disk I/O.
b. Organize objects that are heavily accesses such that they are on separate disks.
c. Separate indexes from tables, place into different tablespaces and split these tablespaces onto
different disks.
d. Stripe heavily accessed objects over multiple disks.
e. Separate redo logs onto a lightly loaded disk drive.
**Note: Raid 5 based disks are NOT a good location for redo logs!**
f. Place rollback segments in to separate tablespaces and then by the listing order in the init.ora
interleaf the access between the two tablespaces.
g. Use the Explain plan utility to check the most common SQL statements that are used. From
this it may be possible to utilize indexes that will prevent sorting and hence reduce I/O to the
temporary tablespace.
If having done all these you are still identifying an I/O problem, then now is the time to implement Partitions and consider raw partitions.
2. Basic Partitioning. What is partitioning?
- Partitioning allows a table, index or index-organized table to be subdivided into smaller pieces.
- Each partition has its own name, and may optionally have its own storage characteristics.
- Can be managed either collectively or individually.
- From the perspective of the application, a partitioned object is identical to a non-partitioned, no modifications are necessary when accessing a partitioned object.
Today's challenge of storing vast quantities of data for the lowest possible cost can be optimally addressed using Oracle Partitioning. The independence of individual partitions is the key enabler
for addressing the online portion of a “tiered archiving” strategy. Specifically in tables containing historical data, the importance - and access pattern – of the data heavily relies on the age of the
data; Partitioning enables individual partitions (or groups of partitions) to be stored on different storage tiers, providing different physical attributes and price points. For example an Orders
table containing 2 years worth of data could have only the most recent quarter being stored on an expensive high-end storage tier and keep the rest of the table (almost 90% of the data) on an inexpensive low cost storage tier.
3. Benefits of partitioning
Partitioning can provide tremendous benefit to a wide variety of applications by improving performance, manageability, and availability. It is not unusual for partitioning to improve the performance of certain queries or maintenance operations by an order of magnitude. Moreover, partitioning can greatly simplify common administration tasks.
Partitioning is a key tool for building multi-terabyte systems or systems with extremely high availability requirements.
Partitioning larger databases is standard for systems approaching 500 gigabytes.
Range and List partitioning methods are the best approach when partitioning for Oracle Demantra. The table is partitioned then each individual range-partition is further sub-partitioned using list partitioning. Each list is a subset of the partition data.
Partitions can be managed individually. For example, the DBA can back-up a single partition if needed. There is reduced need to execute commands on the entire table when applying the command to individual partitions accomplishes the desired task.
Demantra is partition-aware, and some SQL performance will greatly improve.
Set the following parameters so that the Analytical Engine can find the partition on which any combination resides:
Parameter Purpose
PartitionColumnItem Specifies the name of the column that partitions the data by item.
PartitionColumnLoc Specifies the name of the column that partitions the data by location.
Note: When the SALES_DATA table is not partitioned by a level column, you need to set:
update init_params_0 set value_string = NULL
where pname in ('PartitionColumnItem', 'PartitionColumnLoc');
Set the value_string to 1 when implementing partitions.
Oracle partitioning physically sequences rows in index-order. This will deliver a performance improvement for partition-key scans.
Partitioning Pruning: Partitioning pruning (a.k.a. Partition elimination) is the simplest and also the most substantial means to improve performance using partitioning. Partition pruning can often improve query performance by several orders of magnitude. For example, suppose an application contains an ORDERS table containing an historical record of orders, and that this table has been partitioned by day. A query requesting orders for a single week would only access seven partitions of the ORDERS table. If the table had 2 years of historical data, this query would access seven partitions instead of 730 partitions. This query could potentially execute 100x faster simply because of partition pruning. Partition pruning works with all of Oracle's other performance features. Oracle will utilize partition pruning in conjunction with any indexing technique, join technique, or parallel access method.
Partition-wise joins use the specific sub-set of the query partitions. Nested loops and has joins benefit. Partition-wise joins can be applied when two tables are being joined together, and at least one of these tables is partitioned on the join key.
4. Partitioning for Availability
Partitioned database objects provide partition independence. This characteristic of partition independence can be an important part of a high-availability strategy. For example, if one partition of a partitioned table is unavailable, all of the other partitions of the table remain online and available. The application can continue to execute queries and transactions against this partitioned table, and these database operations will run successfully if they do not need to access the unavailable partition.
The database administrator can specify that each partition be stored in a separate tablespace; this would allow the administrator to do backup and recovery operations on each individual partition, independent of the other partitions in the table. Therefore in the event of a disaster, the database could be recovered with just the partitions comprising of the active data, and then the inactive data in the other partitions could be recovered at a convenient time. Thus decreasing the system down-time.
Moreover, partitioning can reduce scheduled downtime. The performance gains provided by partitioning may enable database administrators to complete maintenance operations on large database objects in relatively small batch windows.
5. Partition Options
Range Partitioning
This is the most general type of partition created. Create a range partition when you want to group large amounts of data similar in likeness (e.g. all rows in the month of January, all rows in the month of February, etc.).
Composite Partitioning: Combinations of two data distribution methods are used to define a composite partitioned table. First, the table is partitioned by data distribution method one and then each partition is further subdivided into subpartitions using a second data distribution method. All sub-partitions for a given partition together represent a logical subset of the data.
For example, a range-hash composite partitioned table is first range-partitioned, and then each individual range-partition is further sub-partitioned using the hash partitioning technique.
Available composite partitioning techniques are range-hash, range-list, range-range, list-range, list-list, list-hash, and – new in Oracle Database 11g Release 2 – hash-hash.
Note: Index-organized tables (IOTs) can be partitioned using range, hash, and list partitioning. Composite partitioning is not supported for IOTs.
* This would eliminate the composite partition method for Demantra.
Interval Partitioning: A new partitioning strategy in Oracle Database 11g, Interval partitioning extends the capabilities of the range method to define equi-partitioned ranges using an interval definition. Rather than specifying individual ranges explicitly, Oracle will create any partition automatically as-needed whenever data for a partition is inserted for the very first time.Interval partitioning greatly improves the manageability of a partitioned table.
For example, an interval partitioned table could be defined so that Oracle creates a new partition for every month in a calendar year; a partition is then automatically created for 'September 2007' as soon as the first record for this month is inserted into the database.
The available techniques for an interval partitioned table are Interval, Interval-List, Interval-Hash, and Interval-Range.
* This eliminates the need to forward create partitions.
* Interval-Range is an option that can be implemented With Demantra.
6. Overview of Partitioned Indexes
Just like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability. They can either be partitioned independently (global indexes) or automatically linked to a table's partitioning method, local indexes.
In general, you should use global indexes for OLTP applications and local indexes for data warehousing or Decision Support (DSS) applications. - Whenever possible, you should try to use local indexes because they are easier to manage. When deciding what kind of partitioned index to use, you should consider the following guidelines in order:
1. If the table partitioning column is a subset of the index keys, use a local index. If this is the
case, you are finished. If this is not the case, continue to guideline 2.
2. If the index is unique and does not include the partitioning key columns, then use a global
index. If this is the case, then you are finished. Otherwise, continue to guideline 3.
3. If your priority is manageability, use a local index. If this is the case, you are finished. If this is
not the case, continue to guideline 4.
4. If the application is an OLTP one and users need quick response times, use a global index. If
the application is a DSS one and users are more interested in throughput, use a local index.
- Each partition of a local index corresponds to one partition of the underlying table.
- Enables optimized partition maintenance.
- When a table partition is dropped, Oracle simply has to drop the corresponding index partition as
well.
- Local indexes are most common in data warehousing environments.
- Offer greater availability and are common in DSS environments. The reason for this is
equipartitioning:
each partition of a local index is associated with exactly one partition of the table. This enables
Oracle to automatically keep the index partitions in sync with the table partitions, and makes
each table-index pair independent. Any actions that make one partition's data invalid or
unavailable only affect a single partition.
- You cannot explicitly add a partition to a local index. Instead, new partitions are added to local
indexes only when you add a partition to the underlying table. Likewise, you cannot explicitly
drop a partition from a local index. Instead, local index partitions are dropped only when you drop
a partition from the underlying table.
* For Demantra, local partitioned indexes are recommended. The global partitioned index is
typically found in OLTP however, global partitioned indexes are supported.
You could test partitioning by making the seeded indexes with partition key local since the
engine is using local indexes these should help with the worksheet performance as well.
7. Partitioning for Performance
- The Oracle query optimizer relies on both the statistics of the entire table (global statistics) and the
statistics of the individual partitions (partition statistics) to select a good execution plan for a SQL
statement.
- A well known issue is the "Out of Range" condition triggered during process execution. This can
be prevented by using the new copy table statistics procedure available in Oracle Database
10.2.0.4 and 11g. This procedure copies the statistics of the source [sub] partition to the
destination [sub] partition.
It also copies the statistics of the dependent objects: columns, local (partitioned) indexes etc. It
adjusts the minimum and maximum values of the partitioning column as follows; it uses the high
bound partitioning value as the maximum value of the first partitioning column (it is possible to
have concatenated partition columns) and high bound partitioning value of the previous partition as
the minimum value of the first partitioning column for range partitioned table.
It can optionally scale some of the other statistics like the number of blocks, number of rows etc.
of the destination partition.
- Incremental statistics maintenance was introduced in 11gR1 to improve the performance of
statistics gathering on partitioned tables. This is done by skipping statistics gathering on non
touched partitions.
Incremental maintenance feature is disabled by default. It can be enabled by changing the
INCREMENTAL table preference to true. It can also be enabled for a particular schema or at the
database level
8. Statistics Performed Correctly Improves Partition Performance
See also
How to Move from ANALYZE to DBMS_STATS on Partitioned Tables - Some Examples
Optimizer statistics in Oracle are managed via a PL/SQL package, DBMS_STATS. It provides
several PL/SQL procedures to gather statistics for a table, schema, or a database. For example,
gather_table_statistics is used to gather statistics on a table. This procedure has an
estimate_percent parameter, which specifies the sampling percentage of the statistics gathering.
The users can specify any number between 0 ~ 100 for this parameter. For example, suppose
you have a table TABLEX, you can specify a 10% sampling
percentage as follows:
exec dbms_stats.gather_table_stats(null, 'TABLEX', estimate_percent => 10);
It is not always easy for users to pick the appropriate sampling percentage. If the specified
sampling percentage is too high, it can take longer to gather statistics. On the contrary, if the data
is skewed and the specified sampling percentage is too low, the resulting statistics can be
inaccurate.
For this reason, Oracle introduced the AUTO value for the estimate_percent parameter. For
example, you can gather statistics on TABLEX as follows:
exec dbms_stats.gather_table_stats(null, 'TABLEX', estimate_percent =>
dbms_stats.auto_sample_size);
The advantage of using AUTO sample size over a fixed number is two-folds. First, when AUTO
sample size is specified, the system automatically determines the appropriate sampling
percentage. Second, AUTO sample size is more flexible than a fixed sampling percentage. A
fixed sampling percentage size that was good at some point in time may not be appropriate after
the data distribution in the table has changed. On the other hand when AUTO value is used
Oracle will adjust the sample size when the data distribution changes.
- First, AUTO sampling now generates deterministic statistics.
- Second, and more importantly, AUTO sampling generates statistics that are almost as accurate as
100% sampling but takes much less time than 100% sampling.
- Within each partition the physical data rows should be stored in PK order. There are a number of
ways to determine this, including the CLUSTERING_FACTOR in the USER_INDEXES view.
The closer it is to the number of data blocks the better organized your data rows. As it
approaches the number of rows in the table, the more the data rows are out of sync with the
index.
select index_name, clustering_factor, num_rows, leaf_blocks
from user_indexes
where table_name = 'SALES_DATA'
See Document 1085012.1 for Reordering to the Primary Key.
9. Useful SQL
Do you have the partition option?
select value
from v$option
where parameter = 'Partitioning';
VALUE
------
TRUE
- Partition columns must be a subset of the primary key columns
- Secondary indexes can be partitioned (both locally and globally)
- OVERFLOW data segments are always equi-partitioned with the table partitions
create table test
(pk_id number(5), insdate date)
partition by range (insdate)
interval (numtoyminterval(1, 'MONTH'))
(partition test07_11p values less than (to_date('2007-12-01','YYYY-MM-DD')),
partition test07_12p values less than (to_date('2008-01-01','YYYY-MM-DD')),
partition test08_01p values less than (to_date('2008-02-01','YYYY-MM-DD')));
Setting Storage Parameters for Partitioned Tables
With partitioned tables, you can set default storage parameters at the table level. When creating a
new partition of the table, the default storage parameters are inherited from the table level (unless
you specify them for the individual partition). If no storage parameters are specified at the table
level, then they are inherited from the tablespace.
Verify
------
And now I will check on the indexes.
column table_name format a5 heading 'Table|Name'
column index_name format a5 heading 'Index|Name'
column partition_name format a4 heading 'Part|Name'
column partitioned heading 'Part?#'
column status heading 'Index|Status'
SELECT table_name, index_name, partitioned, status
FROM user_indexes where table_name = 'T' ORDER BY 1,2;
SELECT index_name, partition_name, status
FROM user_ind_partitions WHERE index_name IN (SELECT index_name FROM user_indexes WHERE table_name = 'T') order by 1,2,3;
Now validate the high value for each partition:
select partition_name, high_value from user_tab_partitions;
PARTITION_NAME
------------------------------
HIGH_VALUE
-----------------------------------------------------------------------------
TEST07_11P
TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST07_12P
TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST08_01P
TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
10. Recommendations
The Center of Expertise recommends the following to begin:
Sample
CREATE TABLESPACE striped
DATAFILE 'file_on_disk_1' SIZE 1GB,
'file_on_disk_2' SIZE 1GB,
'file_on_disk_3' SIZE 1GB,
Sample
CREATE TABLE sales_data (
col_1 NUMBER(2),
col_2 VARCHAR2(10) )
TABLESPACE striped
STORAGE ( INITIAL 1023MB NEXT 1023MB
MINEXTENTS 5 PCTINCREASE 0 );
Then proceed to create MDP_MATRIX either on different disks 4,5,6.
1) Test several methods. Perhaps Range and Interval-Range partitions. Partition SALES_DATA,
MDP_MATRIX and PROMOTION_DATA in monthly partitions to begin in test. You have the
choice to move to weekly, etc.
2) Each partition should have its own tablespace. All partitions of a partitioned object must reside
in tablespaces of a single block size.
3) Each of the large tables should have their own tablespace.
4) Use either local or global partitioned indexes.
5) Implement the new copy table statistics procedure available in Oracle Database 10.2.0.4 and 11g.
6) Export the table and import. This should fill the partitions.
* Within each SALES_DATA partition make sure that the physical data rows are stored in PK
order. Determine the need using the CLUSTERING_FACTOR in the USER_INDEXES view.
7) Set the following parameters so that the Analytical Engine can find the partition on which any combination resides:
Parameter Purpose
PartitionColumnItem Specifies the name of the column that partitions the data by item.
PartitionColumnLoc Specifies the name of the column that partitions the data by location.
Note: When the SALES_DATA table is not partitioned by a level column, you need to set:
update init_params_0 set value_string = column name
where pname in ('PartitionColumnItem', 'PartitionColumnLoc');
REFERENCES
NOTE:1085012.1 - Reordering Columns and Rows in Oracle For Demantra Performance Improvement Prior to 7.3.1.3
NOTE:237538.1 - How to Move from ANALYZE to DBMS_STATS on Partitioned Tables - Some Examples
NOTE:443969.1 - Oracle Demantra: Documentation, Release Notes, Transfer of Information (TOI), and Training
NOTE:554539.1 - Using Database Partitioning with Oracle E-Business Suite
NOTE:1516204.1 - Demantra How to Create Sales_Data Partition SQL Exact Procedure Create Table
