Get the best out of Oracle Partitioning

1
(No Transcript)
2
Get the best out of Oracle Partitioning

• Hermann Bär
• Director Product Management, Data Warehousing

3
Agenda

• Partitioning in a nutshell
• Getting optimal pruning
• Partition exchange loading
• Partitioning and unusable indexes
• Efficient statistics management
• QA

4
The Concept of PartitioningSimple Yet Powerful
ORDERS
USA
ORDERS
ORDERS
EUROPE
FEB
FEB
JAN
JAN
Large Table Difficult to Manage
Partition Divide and Conquer Easier to
Manage Improve Performance
Composite Partition Better Performance More
flexibility to match business needs
Transparent to applications
5
What is Oracle Partitioning?

• It is
• Powerful functionality to logically partition
  objects into smaller pieces
• Only driven by business requirements
• Partitioning for Performance, Manageability, and
  Availability

• It is not
• Just a way to physically divide or clump - any
  large data set into smaller buckets
• Enabling pre-requirement to support a specific
  hardware/software design
• Hash mandatory for shared nothing systems

6
Physical versus Logical PartitioningShared
Nothing Architecture

• Physical Partitioning
• Fundamental system setup requirement
• Node owns piece of DB
• Enables parallelism
• Number of partitions is equivalent to min.
  parallelism
• Always needs HASH distribution
• Equally sized partitions per node required for
  proper load balancing

DB
DB
DB
7
Physical versus Logical PartitioningShared
Everything Architecture - Oracle

• Logical Partitioning
• Does not underlie any constraints
• SMP, MPP, Cluster, Grid does not matter
• Purely based on the business requirement
• Availability, Manageability, Performance
• Beneficial for every environment
• Provides the most comprehensive functionality

DB
8
Agenda

• Partitioning in a nutshell
• Getting optimal pruning
• Partition exchange loading
• Partitioning and unusable indexes
• Efficient statistics management
• QA

9
Partition Pruning
Q What was the total sales for the weekend of
May 20 - 22 2008?
Select sum(sales_amount) From SALES Where
sales_date between to_date(05/20/2008,MM/DD/YYY
Y) And to_date(05/23/2008,MM/DD/YYYY)
10
Partition Pruning

• Works for simple and complex SQL statements
• Support for every data access
• Transparent to any application
• No extra coding required
• Two flavors of pruning
• Static pruning at compile time
• Dynamic pruning at runtime
• Complementary to Exadata Storage Server
• Partitioning prunes logically through partition
  elimination
• Exadata prunes physically through storage indexes
• Further data reduction through filtering and
  projection

11
Sun Oracle Database MachineOptimized for large
scans
1 TB with compression
10 TB of user data Requires 10 TB of IO
100 GB with partition pruning
Subseconds On Database Machine
20 GB with Storage Indexes
5 GB with Smart Scans
2000X less data needs to be processed
11
12
Static Partition Pruning

• Relevant Partitions are known at compile time
• Look for actual values in PSTART/PSTOP columns in
  the plan
• Optimizer has most accurate information for the
  SQL statement

SELECT sum(amount_sold) FROM salesWHERE times_id
BETWEEN 01-MAR-2004 and 31-MAY-2004
04-Apr
04-Feb
04-Jan
04-Mar
04-May
13
Static Pruning

• Sample plan

14
Static Pruning

• Sample plan

15
Dynamic Partition Pruning

• Advanced Pruning mechanism for complex queries
• Recursive statement evaluates the relevant
  partitions at runtime
• Look for the word KEY in PSTART/PSTOP columns
  in the Plan

SELECT sum(amount_sold)FROM sales s, times
tWHERE t.time_id s.time_id AND
t.calendar_month_desc IN (MAR-2004,
APR-2004, MAY-2004)
Time
Sales
16
Dynamic Partition PruningNested Loop

• Sample plan

Sample explain plan output
17
Dynamic Partition PruningNested Loop

• Sample plan

Sample explain plan output
18
Dynamic Partition PruningSubquery pruning

• Sample plan

19
Dynamic Partition PruningBloom filter pruning

• Sample plan

20
Enhanced Pruning CapabilitiesOracle Database 11g
Release 2

• Extended modeling capabilities for better data
  placement and pruning
• Support for virtual columns as primary and
  foreign key for Reference Partitioning
• Enhanced optimizer support for Partitioning
• AND pruning
• Intelligent multi-branch execution plan with
  unusable index partitions

20
21
AND Pruning

• All predicates on partition key will used for
  pruning
• Dynamic and static predicates will now be used
  combined
• A.k.a. multi-predicate pruning
• Example
• Star transformation with pruning predicate on
  both the FACT table and a dimensionFROM sales s,
  times t WHERE s.time_id t.time_id ..AND
  t.fiscal_year in (2000,1999)AND s.time_id
  between TO_DATE('01-JAN-1999','DD-MON-YYYY') and
  TO_DATE('01-JAN-2000','DD-MON-YYYY')

Dynamic pruning
Static pruning

21
22
AND Pruning

• Sample plan

23
Ensuring Partition Pruning

• Dont use functions on partition key filter
  predicates

24
Ensuring Partition Pruning

• Dont use functions on partition key filter
  predicates

25
Agenda

• Partitioning in a nutshell
• Getting optimal pruning
• Partition exchange loading
• Partitioning and unusable indexes
• Efficient statistics management
• QA

26
Partition Exchange loading
27
Agenda

• Partitioning in a nutshell
• Getting optimal pruning
• Partition exchange loading
• Partitioning and unusable indexes
• Efficient statistics management
• QA

28
Segment Creation On-DemandA.k.a. deferred
segment creation

• Segment creation for nonpartitioned tables (and
  indexes) is delayed until first data inserted
• No support for partitioned objects (yet)
• Specifically beneficial for pre-packaged
  applications
• Common deployments consist of thousands of
  tables, many of them being empty
• Reduced storage foot print
• Faster initial deployment
• Leverage this functionality after database
  migration
• API to drop segments for existing empty objects

28
29
Segment Creation On-DemandTechnical details

• Enabled by DEFAULT with compatible11.2
• Init.ora deferred_segment_creation TRUE
  FALSE
• Session and system level attribute
• Object level SEGMENT CREATION IMMEDIATE
  DEFERRED
• Indexes inherit the attribute from the table
• No support for partitioned indexes, bitmap join
  indexes, domain indexes
• Same infrastructure is leveraged for unusable
  indexes
• Both non-partitioned and partitioned indexes
• Unusable index segments can never be re-used

29
30
Unusable Indexes

• Unusable index partitions are commonly used in
  environments with fast load requirements
• Safe the time for index maintenance at data
  insertion
• Unusable index segments do not consume any space
  (11.2)
• Unusable indexes are ignored by the optimizer
• SKIP_UNUSABLE_INDEXES TRUE FALSE
• Partitioned indexes can be used by the optimizer
  even if some partitions are unusable
• Prior to 11.2, static pruning and only access of
  usable index partitions mandatory
• With 11.2, intelligent rewrite of queries using
  UNION ALL

31
Intelligent Multi-Branch Execution

• Intelligent UNION ALL expansion in the presence
  of partially unusable indexes
• Transparent internal rewrite
• Usable index partitions will be used
• Full partition access for unusable index
  partitions

32
Multi-Branch Execution

• Sample plan

33
Agenda

• Partitioning in a nutshell
• Getting optimal pruning
• Partition exchange loading
• Partitioning and unusable indexes
• Efficient statistics management
• QA

34
Statistics Gathering

• You must gather optimizer statistics
• Using dynamic sampling is not an adequate
  solution
• Statistics on global and partition level
  recommended
• Run all queries against empty tables to populate
  column usage
• This helps identify which columns automatically
  get histograms created on them
• Optimizer statistics should be gathered after the
  data has been loaded but before any indexes are
  created
• Oracle will automatically gather statistics for
  indexes as they are being created

35
Statistics Gathering

• By default DBMS_STATS gathers following stats for
  each table
• global (table level)
• partition level
• Sub-partition
• Optimizer uses global stats if query touches two
  or more partitions
• Optimizer uses partition stats if queries do
  partition elimination and only one partition is
  necessary to answer the query
• If queries touch two or more partitions the
  optimizer will use a combination of global and
  partition level statistics
• Optimizer uses sub-partition level statistics
  only if your queries do partition elimination and
  one sub-partition is necessary to answer query

36
Efficient Statistics Management

• Use AUTO_SAMPLE_SIZE
• The only setting that enables new efficient
  statistics collection
• Hash based algorithm, scanning the whole table
• Speed of sampling, accuracy of compute
• Enable incremental global statistics collection
• Avoids scan of all partitions after changing
  single partitions
• Prior to 11.1, scan of all partitions necessary
  for global stats
• Managed on per table level
• Static setting

37
Incremental Global Statistics
Sysaux Tablespace
38
Incremental Global Statistics Contd
3. A new partition is added to the table Data
is Loaded
Sales Table
May 18th 2008
May 19th 2008
May 20th 2008
May 21st 2008
May 22nd 2008
May 23rd 2008
May 24th 2008
May 24th 2008
Sysaux Tablespace
39
Step necessary to gather accurate statistics

• Turn on incremental feature for the table
• EXEC DBMS_STATS.SET_TABLE_PREFS('SH,'SALES','INCR
  EMENTAL','TRUE')
• After load gather table statistics using
  GATHER_TABLE_STATS
• No need to specify parameters
• EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','SALES')
• The command will collect statistics for
  partitions and update the global statistics based
  on the partition level statistics and synopsis
• Possible to set incremental to true for all
  tables
• Only works for already existing tables
• EXEC DBMS_STATS.SET_GLOBAL_PREFS('INCREMENTAL','TR
  UE')

40
Summary

• Partitioning in a nutshell
• Getting optimal pruning
• Partition exchange loading
• Partitioning and unusable indexes
• Efficient statistics management

41
Q A
42
For More Information
search.oracle.com
Oracle Partitioning
or oracle.com
43
(No Transcript)