Transcript Document

Oracle and Infrastructure Expertise
Optimal Usage
of Oracle’s
Partitioning Option
Frank Bommarito
Paper # 35697
SageLogix, Inc.
www.sagelogix.com
Overview
Oracle and Infrastructure Expertise
Partitioning – The Beginning
Partitioning Concepts
Partition Maintenance
Types of Partitioning
Practical Partitioning Usages
Statistics
Partitioning Options
Conclusions
www.sagelogix.com
Partitioning –
The Beginning
Oracle and Infrastructure Expertise
The concept: one large table divided into many small tables
Database sizes growing at phenomenal pace
“interesting” challenges appeared for DBAs
rebuilding indexes
Duplication of tables
Query tuning
Partitioning allows for growth, with maintaining the DBA's
management of the database
The result = more uptime
www.sagelogix.com
Partitioning –
The Beginning
Oracle and Infrastructure Expertise
Oracle-based partitioning available in 8.0 & above
Smaller tables (partitions) can be accessed, maintained
separately
“Overview” mechanism
masks the division
operations performed with no modifications to
application
Oracle’s “optimizer”
partition aware
partition pruning
www.sagelogix.com
Partitioning Concepts
Oracle and Infrastructure Expertise
Two distinct object types for a partitioned table –
GLOBAL & LOCAL
GLOBAL objects refer to table as a whole
LOCAL objects are the individual partitions
Standard & partitioned tables have same features (indexes,
constraints, triggers), but implementation is different.
Table’s rows stored in multiple objects, not just one.
www.sagelogix.com
Partitioning Concepts
Oracle and Infrastructure Expertise
Example:
Create table range_partition (
Part_key number,
Value1 varchar2(30),
Value2 number)
Partition by range (part_key)
(
partition p1 values less than (80000),
partition p2 values less than (160000),
partition pmax values less than (maxvalue)
);
Single table created, with three physical segments.
Indexes need to account for each segment.
www.sagelogix.com
Partitioning Concepts
Oracle and Infrastructure Expertise
Statement creates NON-PREFIXED LOCAL index.
NON-PREFIXED = partition key not leading column
LOCAL = 3 separate indexes created
Partition key derived from: “Partition by range (part_key)”
Other examples:
Create index idx_example1 on range_partition (value2) LOCAL;
Column part_key = partition key.
Create index idx_example2 on range_partition (value1) GLOBAL;
Creates one single GLOBAL index - includes rows from all
partitions.
Create index idx_example3 on range_partition (part_key) LOCAL;
Creates a PREFIXED LOCAL index.
www.sagelogix.com
Performance
Considerations
Oracle and Infrastructure Expertise
Example:
Table range_partition loaded with 256,000 rows.
All partitions a near equal distribution of rows.
All three columns unique values.
Three indexes above created on the table.
20,000 queries generated & executed for each column
indexed.
www.sagelogix.com
Performance
Considerations
Oracle and Infrastructure Expertise
Results
Similar
Slowest ( index scans)
COLUMN
INDEX TYPE
TOTAL TIME
Value_1
GLOBAL
7 minutes 20 seconds
Value_2
NON-PREFIXED LOCAL
12 minutes 30 seconds
Part_key
PREFIXED LOCAL
6 minutes 10 seconds
Fastest (partition pruning)
Same table, without partitioning:
COLUMN
INDEX TYPE
TOTAL TIME
Value_1
STANDARD
7 minutes 20 seconds
Value_2
STANDARD
7 minutes 20 seconds
Part_key
STANDARD
7 minutes 20 seconds
www.sagelogix.com
Partition Maintenance
Oracle and Infrastructure Expertise
Why utilize a non-prefixed local index?
Partitions splits table into smaller sizes - allows maintenance
on excessively large tables.
A partition can have maintenance performed without affecting
other partitions.
However, this does impact GLOBAL table items.
Some of the maintenance operations include:
Rebuild a specific partitions data segments
Exchange a non-partitioned table with a partition
Merge two partitions together
Divide two partitions apart
Add new partitions to the table
Drop old partitions from the table
www.sagelogix.com
Partition Maintenance
Oracle and Infrastructure Expertise
Global Indexes
A single ROW affected by a partition maintenance
operation will cause ENTIRE global index to be invalid.
9i of Oracle contains “update global index” clause can be applied to partition maintenance operations.
Rebuilds only components of GLOBAL index
impacted.
Rebuilding can cause performance degradations but are minimal compared to impact of
INVALIDATING important indexes.
www.sagelogix.com
Partition Maintenance
Oracle and Infrastructure Expertise
Examples:
Alter table range_partition move partition p1 tablespace
new_tablespace;
Will rebuild partition & locate newly rebuilt partition in
“new_tablespace”. If partition “p1” = one or more
rows, global indexes become “UNUSABLE”. Application
will receive errors if application needs to access index.
Alter index idx_example2 rebuild;
Will rebuild unusable index. Needs to re-index entire
table’s contents, will not be working only on deltas.
Alter table range_partition move partition p1 tablespace
new_tablespace update global indexes;
Performs same operation, but adds task of “fixing”
global index (release 9i and above).
www.sagelogix.com
Partition Maintenance
Oracle and Infrastructure Expertise
Constraints
Likely largest prohibitive unit for partition maintenance.
Most partition maintenance operations do not work
when constraints are enabled.
Typically, constraint needs to be dropped and re-applied
after partition maintenance operations.
Oracle added new syntax that is handy when disabling
constraints.
Example: Alter constraint pk_contstraint disable keep index;
“Keep index” clause will not drop the index.
Maintenance operations proceed and index pieces can
be rebuilt. Once complete, constraint can be reenabled in relatively short time.
www.sagelogix.com
Partition Maintenance
Oracle and Infrastructure Expertise
Example:
CREATE TABLE part_test
(ID NUMBER NOT NULL, NUMB NUMBER)
PARTITION BY RANGE (ID)
(PARTITION P1 VALUES LESS THAN (10), PARTITION P2 VALUES LESS
THAN (20));
CREATE unique INDEX part_test_pkx ON part_test (ID) LOCAL;
ALTER TABLE part_test ADD CONSTRAINT part_test_pk PRIMARY KEY
(ID) USING INDEX;
create table fk_table (id number, descr varchar2(30));
www.sagelogix.com
Partition Maintenance
Oracle and Infrastructure Expertise
Example, cont.
ALTER TABLE fk_table ADD CONSTRAINT fk_table_fk FOREIGN
KEY (ID) REFERENCES PART_TEST(ID);
create table part_exch (ID NUMBER NOT NULL, NUMB
NUMBER);
insert into part_test values (1,1);
alter table part_test exchange partition p1 with table part_exch;
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled
foreign keys
www.sagelogix.com
Partition Maintenance
Oracle and Infrastructure Expertise
Stored PL/SQL
Stored PL/SQL often exists.
When objects are modified, PL/SQL program units
need re-compilation to ensure that modifications
are valid. Partition maintenance operations are
excluded. A new partition does not impact stored
PL/SQL. However, addition of new partition will
invalidate any dependent PL/SQL program.
Release 9i automatically recompiles the invalidated
programs.
Whenever data dictionary adds or removes row
resulting from partition maintenance operation,
PL/SQL become invalid.
www.sagelogix.com
Partition Maintenance
Oracle and Infrastructure Expertise
Stored PL/SQL, cont.
The following command does not cause invalidation as
the data dictionary is simply updated:
Alter table range_partition exchange partition p1 with table
no_partition;
The following command does cause invalidation as the
data dictionary is removing a row:
Alter table range_partition drop partition p1;
www.sagelogix.com
Types of Partitioning
Oracle and Infrastructure Expertise
RANGE
HASH
LIST
SUB-PARTITION (HASH or LIST)
www.sagelogix.com
Types of Partitioning
Oracle and Infrastructure Expertise
RANGE PARTITIONS
Most common.
Table & index partitions based on list of columns,
allowing database to store occurrence in given partition.
Typically used within data warehousing systems. Most
common range boundary = dates.
Each partition is defined with upper boundary. Storage
location found by comparing partitioning key with upper
boundary. Upper boundary is non-inclusive: the key of
each occurrence must be less than this limit for record
to be stored in this partition.
www.sagelogix.com
Types of Partitioning
Oracle and Infrastructure Expertise
HASH PARTITIONS
Ideal when there is no real method to divide table
based on range.
Utilize hashing algorithm to programmatically take
column value and store within given partition.
Each partition is defined with an internal hashing
algorithm.
Recommended when it is difficult to define criteria for
distribution of data.
www.sagelogix.com
Types of Partitioning
Oracle and Infrastructure Expertise
LIST PARTITIONS
Hard-coded LIST of values that will exist within any
partition.
Common usage would be states. A state partition table
would commonly have 50 partitions, one for each state.
SUB-PARTITIONS
Utilized most often when partition strategy does not
provide small enough partition units to achieve
maintenance goals. Sub-partitions further divide table
based another column.
www.sagelogix.com
Types of Partitioning
Oracle and Infrastructure Expertise
Example: RANGE - (a max partition will capture any values
beyond the stated ranges – including NULLS)
Create table range_partition
( date_col date)
partition by RANGE (date_col)
(
partition p_jan_2001 values less than
(to_date(‘01022001’,’ddmmyyyy’)),
partition p_feb_2001 values less than
(to_date(‘01032001’,’ddmmyyyy’)),
partition pmax values less than (maxvalue)
);
www.sagelogix.com
Types of Partitioning
Oracle and Infrastructure Expertise
Example: HASH – (most optimal when 8, 16, or 32
partitions are used).
Create table hash_partition
(account_id varchar2(30))
partition by HASH (account_id) partitions 16
www.sagelogix.com
Types of Partitioning
Oracle and Infrastructure Expertise
Example: LIST
Create table list_partition
(state_id varchar2(2))
partition by LIST (state_id)
(
partition P_MI values (‘MI’),
partition P_CO values (‘CO’)
);
www.sagelogix.com
Practical Partitioning
Usages
Oracle and Infrastructure Expertise
Partition Usage I – Data Warehousing
based on date ranges (daily or monthly)
Partition Usage II – OLTP
based upon frequently accessed key
Partition Usage III – ODS
based upon date range and key
Partition Usage IV – Temporary Storage
Partitions rotate and reused over time
Example: A partition is based by day of month. 31 partitions created & date
function used to place rows in partition based by day of month. Partitions
read by application that TRUNCATES partitions after reading data.
www.sagelogix.com
Statistics
Oracle and Infrastructure Expertise
Cost-based optimizer of Oracle is partitioning-aware.
Rule-based optimizer does not “do” partitions.
Cost-based optimizer works off of statistics. Statistics on
standard tables easier to generate & comprehend than
statistics on partition tables.
Statistics are the number one problem with partitioning
implementations.
With partitions, there are LOCAL and GLOBAL statistics.
GLOBAL statistics are utilized when GLOBAL operations
are performed.
LOCAL statistics are utilized when partition key is
available and partition elimination is possible.
www.sagelogix.com
Statistics
Oracle and Infrastructure Expertise
Examples:
Select * from range_partition where value1 = :b1;
Value1 is indexed GLOBALLY (i.e. only global statistics
are reviewed). Optimizer will determine if full tables
scan or index lookup is most appropriate.
Select * from range_partition
Where value1 = :b1
And value2 = :b2
And part_key = :b3
Local statistics & global statistics are evaluated. Local
statistics come into play because PART_KEY is within
the where clause.
www.sagelogix.com
Statistics
Oracle and Infrastructure Expertise
Statistics are gathered LOCALLY or GLOBALLY.
Once gathered, they are tied together (i.e. partition
maintenance operations that impact GLOBAL
operations also impact GLOBAL statistics).
If a partition is added to a table, GLOBAL statistics
may “disappear”.
www.sagelogix.com
Statistics
Oracle and Infrastructure Expertise
NO table statistics
If there are NO table statistics at all, the optimizer acts
“relatively” rule-based.
Relatively Rule
Rule 1: If GLOBAL index exists and can be used, it will be.
Rule 2: If there are no GLOBAL indexes, LOCAL indexes
will be used (if they exist).
Therefore, NO statistics is an option if all indexes created on the
table are good choices and any GLOBAL indexes are superior to
LOCAL indexes.
Conclusion: If the partition is to be queried from a single column
and that column is the partition key with an index, then the
absence of gathering statistics is optimal.
www.sagelogix.com
Statistics
Oracle and Infrastructure Expertise
Gathering Statistics - LOCALLY
If the following commands are used INITIALLY to gather
statistics, and no other command used, then GLOBAL
statistics are derived.
Execute dbms_stats.gather_table_stats
(owner,'RANGE_PARTITION','P2',CASCADE=>TRUE);
OR
execute dbms_stats.gather_table_stats
(owner,'RANGE_PARTITION','P2',CASCADE=>TRUE,
METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 200');
The only difference between these statistics commands is
the generation of histograms.
www.sagelogix.com
Statistics
Oracle and Infrastructure Expertise
Gathering Statistics – LOCALLY, How To:
The GLOBAL statistics are populated after running a
LOCAL script.
Before generating statistics on any of the partitions:
Select num_rows from dba_tables where
table_name=‘RANGE_PARTITION’;
NUM_ROWS=NULL
Select partition_name,num_rows from dba_tab_partitions
where table_name = ‘RANGE_PARTITION’;
All rows have a NUM_ROWS=NULL
www.sagelogix.com
Statistics
Oracle and Infrastructure Expertise
Gathering Statistics – LOCALLY, cont.
After a SINGLE execution of a statistic generation
statement:
execute dbms_stats.gather_table_stats
('SYSTEM','RANGE_PARTITION','P1',CASCADE=>TRUE);
Select num_rows from dba_tables where table_name =
‘RANGE_PARTITION’;
Global Result = 250,000
Select partition_name,num_rows from dba_tab_partitions
where table_name = ‘RANGE_PARTITION’;
Results = P1 79999, P2 NULL, PMAX NULL
www.sagelogix.com
Statistics
Oracle and Infrastructure Expertise
Gathering Statistics – LOCALLY, cont.
The GLOBAL statistics are “guessed” & populated.
Once LOCAL statistics are generated, GLOBAL statistics
are aggregate and not “reality”. Gathering statistics this
way uses the relatively rule method of optimization: if
GLOBAL index exists, it’s used. LOCAL indexes are
evaluated if the where clause allows for partition
pruning.
These commands do not account for GLOBAL table
units (the GLOBAL indexes were never analyzed).
Once GLOBAL indexes are analyzed, all needed “units”
have statistics and optimizer takes over.
execute dbms_stats.gather_index_stats
('SYSTEM','RANGE_PARTITION_DESC');
www.sagelogix.com
Statistics
Oracle and Infrastructure Expertise
Gathering Statistics – GLOBALLY
The following command will gather GLOBAL and LOCAL
statistics.
execute dbms_stats.gather_table_stats(owner,
‘RANGE_PARTITION', GRANULARITY=>'ALL',CASCADE=>TRUE);
This command is equivalent to all the commands earlier.
This is the recommended approach for initial gathering of
statistics on partitions, as it ensures that ALL statistics are
gathered.
www.sagelogix.com
Statistics
Oracle and Infrastructure Expertise
Partition Maintenance Effects
Vary by release.
Release 8.x, GLOBAL statistics temporarily
disappear (the num_rows value becomes NULL).
Release 9.x, the GLOBAL statistics do not change.
For both, statistics are no longer valid and need
updating.
An advantage of a partitioned table is the ability to
perform maintenance work on smaller segments - ONLY
the modified partitions are updated.
All GLOBAL statistics for the table will be corrected
(including GLOBAL index statistics).
www.sagelogix.com
Statistics
Oracle and Infrastructure Expertise
Partition Maintenance Effects, cont.
Once any partition modifications occur, ensure to run
the statistics immediately on the effected partitions.
Failure to do so can lead to the optimizer’s inability to
parse the SQL statement. HANGING CAN OCCUR.
If this occurs, best corrective actions are to remove all
statistics and generate them again.
www.sagelogix.com
Partitioning Options
Oracle and Infrastructure Expertise
Enable Row Movement
Release 8I and above: A new option with Oracle partition.
Allows updates to partition key to occur when update
would “relocate” a row from one partition to another.
As the partition id is stored within the ROWID, this WILL
CHANGE the ROWID for the row. This could impact
application programs that utilize ROWID.
Exchange without validation
When a partition is exchanged with another table:
Alter table part_table exchange partition p1
with table fk_table without validation;
www.sagelogix.com
Partitioning Options
Oracle and Infrastructure Expertise
Exchange without Validation, cont.
It is possible that rows from this partition cannot be
transparently queried.
Validation ensures rows in “fk_table” qualify for given
partition. When this option is bypassed (for
performance reasons), one must ensure that new
partition rows do not violate constrained partition
boundaries.
Given that partition pruning occurs prior to selection,
violation of boundaries could render false results from a
query.
www.sagelogix.com
Partitioning Options
Oracle and Infrastructure Expertise
Exchange without validation - Example:
CREATE TABLE part_test
(ID NUMBER NOT NULL) PARTITION BY RANGE (ID)
(PARTITION P1 VALUES LESS THAN (10), PARTITION P2 VALUES
LESS THAN (20));
create table fk_table (id number not null);
insert into part_test values (5);
insert into fk_table values (5);
commit;
alter table part_test exchange partition p2 with table fk_table without
validation;
-- Returns 2 rows - both with the value of 5
select * from SYSTEM.PART_TEST;
-- Returns 1 row - with the value of 5
select * from SYSTEM.PART_TEST where id=5;
www.sagelogix.com
Partitioning Options
Oracle and Infrastructure Expertise
Oracle Initialization Parameters
Oracle’s partitioning option coverts one table into many
physical segments.
More physical segments require more resources from
Oracle SGA.
In particular, Oracle initialization parameter DML_LOCKS
must be set to accommodate partitioning. (i.e. if a
table has 1000 partitions, then DML_LOCKS must be set
to at least 1000 or the table cannot be created.)
www.sagelogix.com
Conclusion
Oracle and Infrastructure Expertise
With the advent of partitioning, improved database
administration with maintenance operations occurring at a
partition level rather than at the table or index level, allow
Database Administrators to provide improved SLAs.
Partitioning then becomes a crucial aspect of any database
containing large amounts of data. As maintenance windows
decrease in length due to cost of downtime, understanding
methods to shorten database downtime is critical for success.
After researching the various methods, each DBA should test
partitioning scheme best suited for their environment.
Performance and maintenance are the primary concerns to
account for when implementing partitioning option. The
partitioning of large tables allows for faster data access, as
well as decreased maintenance windows.
www.sagelogix.com
Conclusion, cont.
Oracle and Infrastructure Expertise
Partitioning should not be taken lightly. However, it should be
considered for any database with excessive data or when
excessive growth is anticipated.
Please check out our website at
www.sagelogix.com/partitioning.
This location has a download zip file containing source code,
which will automate the maintenance of date based range
partitions.
www.sagelogix.com
Oracle and Infrastructure Expertise
Q&A
Frank Bommarito
SageLogix, Inc
www.sagelogix.com
www.sagelogix.com