Tuesday Apr 10, 2012

Self-Study course on ODI Application Adapter for Hadoop

For those of you who want to work with Oracle Data Integrator and its Hadoop capabilities, a good way to start is the newly released self-study course from Oracle University. You can find the course here.

Enjoy, and if you have any feedback, do send this into Oracle University by logging in (so we can unleash our big data analytics on it ;-) ).

Monday Jul 18, 2011

Is Parallelism married to Partitioning?

In the last couple of months I have been asked a number of times about the dependency of parallelism on partitioning. There seems to be some misconceptions and confusion about their relationship, and some people still believe that partitioning is required to perform operations in parallel. Well, this is not the case, so let me try to briefly explain how Oracle parallelizes operations. Note that 'parallel operation' in this context means to break down a single operation – e.g. a single SQL statement – into smaller unit of works that are processed in parallel to leverage more or even all resources in a system to return the result faster. More in-depth information and whitepapers on Parallel Execution and Partitioning can be found on OTN.

Let’s begin with a quick architecture recap. Oracle is a shared everything system, and yes, it can run single operations in a massively parallel fashion. All of the data is shared between all nodes in a cluster and the capability of how to parallelize operations is not restricted to any predetermined static data distribution done at database/table setup (creation) time. In a pure shared nothing architecture, database files (or tables) have to be partitioned to map disjoint data sets – “partitions” of data – to specific “nodes” of a multi-core or multi-node system to enable parallel processing as illustrated below (note that the distribution in a shared nothing system is normally done based on a hash algorithm; I just used letter ranges for illustration purposes here):

Design strategies for parallelization: Shared Nothing v Shared Everything architecture

In other words, with the Oracle Database any node in a cluster configuration can access any piece of data in an arbitrary parallel fashion, and the data does not have to be “partitioned” at creation time for parallelization.

Oracle’s parallel execution determines the most optimal subset of the smaller parallel units of work based on the user’s request – namely the SQL statement. Oracle does not rely on Oracle Partitioning to enable parallel execution, but takes advantage of partitioning wherever it makes sense, just like a shared nothing system; we will talk about this a bit later.

So how does Oracle dynamically determine these smaller units of work? In Oracle, the basic unit of work in parallelism is called a granule. Oracle divides the operation being parallelized (for example a table scan, table update, or index creation) into granules. Oracle distinguishes two different kinds of granules for initial data access: Block range granules and Partition based granules.

Block range granules are ranges of physical blocks from a table. Each parallel execution server works on its own block range granule. Block range granules are the basic unit of most parallel operations even on partitioned tables. Therefore, from an Oracle Database perspective, the degree of parallelism (DOP) is not related to the number of partitions or even the fact whether a table is partitioned or not. The number of granules and their size correlates with the DOP and the object size.

That said, Oracle is flexible in its decisions: some operations can benefit from an underlying partitioned data structure and leverage individual partitions as granules of work, which brings me to the second kind of granules; with partition based granules only one parallel execution server performs the work for all of the data in a single partition or subpartition (the maximum allowable DOP obviously then becomes the number of partitions). 

The best known operation that can benefit from parallel granules is a join between large equi-partitioned tables, a so-called partition-wise join. A partition-wise join takes place when tables to be joined are equi-partitioned on the join key or the operation is executed in parallel and the larger one of the tables to being joined is partitioned on the join key.

By joining the equivalent partitions the database has to do less data distribution and the sort/join resource requirements are reduced. The picture below illustrates the execution of a partition-wise join. Instead of joining all data of SALES with all data of CUSTOMERS, partition pairs are joined in parallel by multiple parallel execution servers, having each parallel execution server working on one pair of partitions. You can understand this a little bit like shared-nothing-like processing at runtime.

Now that we know about Oracle’s choice of granules how do we tell which type of granules was chosen?  The answer lies in the execution plan. In the example below, operation ID 7 (above the table access of SALES) it says PX BLOCK ITERATOR, which means that block range granules have been used to access table SALES in parallel.

To illustrate partition-wise joins and the use of partition based granules consider the following example: table SALES is range partitioned by date (8 partitions) and sub partitioned by hash on cust_id using 16 subpartitions for each range partition, for a total of 128 subpartitions. The CUSTOMER table is hash partitioned on cust_id with 16 hash partitions.

Below is the execution plan for the following query:

SELECT c.cust_last_name, COUNT(*)

FROM sales s, customers c

WHERE s.cust_id = c.cust_id AND

s.time_id BETWEEN TO_DATE('01-JUL-1999', 'DD-MON-YYYY') AND

     (TO_DATE('01-OCT-1999', 'DD-MON-YYYY'))

GROUP BY c.cust_last_name HAVING COUNT(*) > 100;

Operation ID 11 above the table access of SALES says PX PARTITION RANGE ITERATOR which means partition based granules were used to access partition 8 and 9 and subpartitions 113 to 144 (Partition  pruning which be discussed in another blog)

Operation ID 8 says PX PARTITIONS HASH ALL which means the corresponding 16 hash subpartitions of the SALES table are joined to customer table using a full partition-wise join. (For more information on Partition-Wise joins refer to  Oracle® Database VLDB and Partitioning Guide)

As we have seen,unlike shared nothing systems, Oracle parallel execution combines the best of both worlds, providing the most flexible way of parallelizing an operation but still take full advantage of pre-existing partitioning strategies for optimal execution when needed.

Wednesday May 25, 2011

Parallel Load: Uniform or AutoAllocate extents?

Over the last couple of years there has been a lot of debate about space management in Data Warehousing environments and the benefits of having fewer larger extents. Many believe the easiest way to achieve this is to use uniform extents but the obvious benefits can often be out weighed by some not so obvious drawbacks.

For performance reasons most loads leverage direct path operations where the load process directly formats and writes Oracle blocks to disk instead of going through the buffer cache. This means that the loading process allocates extents and fills them with data during the load. During parallel loads, each loader process will allocate it own extent and  no two processes work on the same extent. When loading data into a table with UNIFORM extents each loader process will allocate its own Uniform extent and begin loading the data, if the extents  are not fully populated the table is left with a lot of partially filled extents, effectively creating ‘HOLES’ in the table. Not only is this space wastage but it also impacts query performance as subsequent queries that scan the table have to scan all of the extents even if they are not fully filled.

What is different with AUTOALLOCATE? AUTOALLOCATE will dynamically adjust the size of an extent and trim the extent after the load in case it is not fully loaded (Extent Trimming)

Tom Kyte covers this problem in great details in his post Loading and Extents but below is a simple example just to illustrate what a huge difference there can be in space management when you load into a table with uniform extents versus a table with autoallocated extents.

1) Create two tablespaces: Test_Uniform (using uniform extent management), Test_Allocate (using auto allocate)

create tablespace test_uniform datafile '+DATA/uniform.dbf' SIZE 1048640K


EXTENT management local uniform size 100m;

create tablespace test_allocate datafile '+DATA/allocate2.dbf' SIZE 1048640K


EXTENT management local autoallocate segment space management auto;

2)Create a flat file with a 10,000,000 records.

-rw-r--r-- 1 oracle dba 1077320689 May 17 16:59 TEST.dat

3)Do a parallel direct path load of this file into each tablespace:

create table UNIFORM_TEST                                                                         parallel
tablespace Test_
_uniform                                                                        as  select * from big_table_ext;

tablespace Test_allocate
as select * from big_table_ext;

Let's view the space usage using a PL/SQL package called show_space  .


 As you can see from the results there are no unformatted blocks in the autoallocate table as extent trimming has taken place after the load was complete. The same can not be said for the uniform_test table. It is quite evident from the numbers that there is substantial space wastage in the uniform_test table. Although the count of full blocks are the same in both cases, the Total Mbytes used is 10x greater in the Uniform table.


Space utilization is much better with autoallocate becuase of extent trimming. As I said before more information on this topic can be found on Tom's Kyte post.


Wednesday Apr 20, 2011

Calculating Parameter Values for Parallelism

[Read More]

Wednesday Mar 23, 2011

Best Practices: Data Warehouse in Archivelog mode?

[Read More]

Monday Feb 14, 2011

Auto DOP and Queuing Parameter Settings

[Read More]

Wednesday Sep 08, 2010

Session Queuing and Workload Management

[Read More]

Friday Apr 23, 2010

Oracle Communications Data Model

[Read More]

Friday Apr 09, 2010

Auto DOP and Concurrency

[Read More]

Monday Nov 30, 2009

TechCast: Data Warehouse Best Practices – Dec 2nd

[Read More]

Friday Jul 24, 2009

Why does it take forever to build ETL processes?

[Read More]

Thursday Jun 25, 2009

Best Practices at ODTUG

[Read More]

Friday Apr 10, 2009

Compressing Individual Partitions in the warehouse

[Read More]

Wednesday Mar 04, 2009

Managing Optimizer statistics in an Oracle Database 11g

Knowing when and how to gather optimizer statistics has become somewhat of dark art especially in a data warehouse environment where statistics maintenance can be hindered by the fact that as the data set increases the time it takes to gather statistics will also increase. By default the DBMS_STATS packages will gather global (table level), partition level, and sub-partition statistics for each of the tables in the database. The only exception to this is if you have hash sub-partitions. Hash sub-partitions do not need statistics, as the optimizer can accurately derive any necessary statistics from the partition level statistic because the hash partitions are all approximately the same size due to linear hashing algorithm.
As mentioned above the length of time it takes to gather statistics will grow proportionally with your data set, so you may now be wondering if the optimizer truly need statistics at every level for a partitioned table or if time could be saved by skipping one or more levels? The short answer is "no" as the optimizer will use statistics from one or more of the levels in different situations.

The optimizer will use global or table level statistics if one or more of your queries touches two or more partitions.

The optimizer will use partition level statistics if your queries do partition elimination, such that only one partition is necessary to answer each query. If your queries touch two or more partitions the optimizer will use a combination of global and partition level statistics.

The optimizer will user sub-partition level statistics if your queries do partition elimination, such that only one sub-partition is necessary. If your queries touch two more sub-partitions the optimizer will use a combination of sub-partition and partition level statistics.

How to gather statistics?
Global statistics are by far the most important statistics but they also take the longest time to collect because a full table scan is required. However, in Oracle Database 11g this issue has been addressed with the introduction of Incremental Global statistics. Typically with partitioned tables, new partitions are added and data is loaded into these new partitions. After the partition is fully loaded, partition level statistics need to be gathered and the global statistics need to be updated to reflect the new data. If the INCREMENTAL value for the partition table is set to TRUE, and the DBMS_STATS GRANULARITY parameter is set to AUTO, Oracle will gather statistics on the new partition and update the global table statistics by scanning only those partitions that have been modified and not the entire table. Below are the steps necessary to do use incremental global statistics

SQL> exec dbms_stats.set_table_prefs('SH', 'SALES', 'INCREMENTAL', 'TRUE');

SQL> exec dbms_stats.gather_table_stats( Owname=>'SH', Tabname=>'SALES', Partname=>'23_MAY_2008', Granularity=>'AUTO');

Incremental Global Stats works by storing a synopsis for each partition in the table. A synopsis is statistical metadata for that partition and the columns in the partition. Each synopsis is stored in the SYSAUX tablespace and takes approximately 10KB. Global statistics are generated by aggregating the synopses from each partition, thus eliminating the need for the full table scan (see Figure below). When a new partition is added to the table you only need to gather statistics for the new partition. The global statistics will be automatically updated by aggregating the new partition synopsis with the existing partitions synopsis.


But what if you are not using Oracle Database 11g and you can't afford to gather partition level statistic (not to mention global statistics) after data is loaded? In Oracle Database 10g ( you can use the DBMS_STATS.COPY_TABLE_STATS procedure. This procedure enables you to copy statistics from an existing [sub] partition to the new [sub] partition and will adjust statistics to account for the additional partition of data (for example the number of blks, number of rows). It sets the new partition's high bound partitioning value as the maximum value of the first partitioning column and high bound partitioning value of the previous partition as the minimum value of the first partitioning column for a range partitioned table. For a list-partitioned table it will find the maximum and minimum from the list of values.

SQL>exec dbms_stats.copy_table_stats('sh','sales','sales_q3_2000','sales_q44_2000', force=>TRUE);

When should you gather Statistics?
If you use the automatic stats job or dbms_stats.gather_schema_stats with the option "GATHER AUTO", Oracle only collect statistics at the global level if the table has changed more than 10% or if the global statistics have not yet been collected. Partition level statistics will always be gathered if they are missing. For most tables this frequency is fine.
However, in a data warehouse environment there is one scenario where this is not the case. If a partition table is constantly having new partitions added and then data is loaded into the new partition and users instantly begin querying the new data, then it is possible to get a situation where an end-users query will supply a value in one of the where clause predicate that is outside the [min,max] range for the column according to the optimizer statistics. For predicate values outside the statistics [min,max] range the optimizer will prorates the selectivity for that predicate based on the distance between the value the max (assuming the value is higher than the max). This means, the farther the value is from the maximum value the lower is the selectivity will be, which may result in sub-optimal execution plans.
You can avoid this "Out of Range" situation by using the new incremental Global Statistics or the copy table statistics procedure.

More information on Incremental Global Statistics or the copy table statistics procedure can be found on the Optimizer developers blog.


The data warehouse insider is written by the Oracle product management team and sheds lights on all thing data warehousing and big data.


« October 2015