Tony’s Oracle Tips

February 9, 2016

Parallel partition scans

Filed under: Uncategorized — tonyhasler @ 9:51 pm

When a table is accessed by multiple members of a parallel query server set, the execution plan may show the use of block range granules (PX BLOCK ITERATOR) or partition granules (PX PARTITION [RANGE|LIST|HASH] ITERATOR or PX PARTITION [RANGE|LIST|HASH] ALL).

The basic ideas surrounding these concepts are discussed in numerous blogs and books, including my own, but discussion of partition range granules is normally restricted to either partition wise joins or the creation and rebuild of partitioned indexes.

However, a colleague came to me recently with a problem with another use case. A problem that a quick Google search was unable to resolve and so after I worked it out I thought it was time for one of my rare blogs.

Let us begin by creating a table partitioned by range using the BUSINESS_DATE and AMOUNT columns.


CREATE TABLE part2
(
   business_date   DATE
  ,amount          NUMBER
)
PARTITION BY RANGE (business_date, amount)
   (PARTITION pdefault VALUES LESS THAN (maxvalue, maxvalue));

BEGIN
   FOR r
      IN (    SELECT TO_CHAR (DATE '2000-01-01' + TRUNC ((ROWNUM-1) / 3), 'YYYYMMDD')
                        partition_date
                    ,DECODE (MOD (ROWNUM-1, 3),  0, '10',  1, '100',  'MAXVALUE')
                        partition_amount
                FROM DUAL
          CONNECT BY LEVEL <= 510)
   LOOP
      EXECUTE IMMEDIATE
            '
ALTER TABLE PART2
   SPLIT PARTITION PDEFAULT
      AT (TO_DATE('''
         || r.partition_date
         || ''',''YYYYMMDD''),'
         || r.partition_amount
         || ')
      INTO (PARTITION P'
         || r.partition_date
         || '_'
         || r.partition_amount
         || ', PARTITION PDEFAULT)';
   END LOOP;
END;
/

After creating the table with 1 partition I added 510 more based on 170 dates with three amount ranges each.

Let us begin by looking at the execution plans of a few queries using 8 parallel query servers:


SET HEADING OFF PAGES 0 FEED OFF
EXPLAIN PLAN
   FOR
      SELECT /*+ parallel(8) */
            * FROM part2;

SELECT *
  FROM TABLE (DBMS_XPLAN.display (format => 'BASIC +PARTITION +OUTLINE'));

EXPLAIN PLAN
   FOR
      SELECT /*+ parallel(8) */
            *
        FROM part2
       WHERE business_date = DATE '2000-01-01';

SELECT *
  FROM TABLE (DBMS_XPLAN.display (format => 'BASIC +PARTITION +OUTLINE'));

EXPLAIN PLAN
   FOR
      SELECT /*+ parallel(8) */
            *
        FROM part2
       WHERE business_date = :b1;

SELECT *
  FROM TABLE (DBMS_XPLAN.display (format => 'BASIC +PARTITION +OUTLINE'));
  
Plan hash value: 3087476059                                                     
                                                                                
---------------------------------------------------------                       
| Id  | Operation            | Name     | Pstart| Pstop |                       
---------------------------------------------------------                       
|   0 | SELECT STATEMENT     |          |       |       |                       
|   1 |  PX COORDINATOR      |          |       |       |                       
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |       |       |                       
|   3 |    PX BLOCK ITERATOR |          |     1 |   511 |                       
|   4 |     TABLE ACCESS FULL| PART2    |     1 |   511 |                       
---------------------------------------------------------                       
                                                                                
Outline Data                                                                    
-------------                                                                   
                                                                                
  /*+                                                                           
      BEGIN_OUTLINE_DATA                                                        
      FULL(@"SEL$1" "PART2"@"SEL$1")                                            
      OUTLINE_LEAF(@"SEL$1")                                                    
      SHARED(8)                                                                 
      ALL_ROWS                                                                  
      DB_VERSION('12.1.0.1')                                                    
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')                                     
      IGNORE_OPTIM_EMBEDDED_HINTS                                               
      END_OUTLINE_DATA                                                          
  */
Plan hash value: 3618659711                                                     
                                                                                
---------------------------------------------------------                       
| Id  | Operation            | Name     | Pstart| Pstop |                       
---------------------------------------------------------                       
|   0 | SELECT STATEMENT     |          |       |       |                       
|   1 |  PX COORDINATOR      |          |       |       |                       
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |       |       |                       
|   3 |    PX BLOCK ITERATOR |          |     1 |     3 |                       
|   4 |     TABLE ACCESS FULL| PART2    |     1 |     3 |                       
---------------------------------------------------------                       
                                                                                
Outline Data                                                                    
-------------                                                                   
                                                                                
  /*+                                                                           
      BEGIN_OUTLINE_DATA                                                        
      FULL(@"SEL$1" "PART2"@"SEL$1")                                            
      OUTLINE_LEAF(@"SEL$1")                                                    
      SHARED(8)                                                                 
      ALL_ROWS                                                                  
      DB_VERSION('12.1.0.1')                                                    
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')                                     
      IGNORE_OPTIM_EMBEDDED_HINTS                                               
      END_OUTLINE_DATA                                                          
  */
Plan hash value: 3618659711                                                     
                                                                                
---------------------------------------------------------                       
| Id  | Operation            | Name     | Pstart| Pstop |                       
---------------------------------------------------------                       
|   0 | SELECT STATEMENT     |          |       |       |                       
|   1 |  PX COORDINATOR      |          |       |       |                       
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |       |       |                       
|   3 |    PX BLOCK ITERATOR |          |   KEY |   KEY |                       
|   4 |     TABLE ACCESS FULL| PART2    |   KEY |   KEY |                       
---------------------------------------------------------                       
                                                                                
Outline Data                                                                    
-------------                                                                   
                                                                                
  /*+                                                                           
      BEGIN_OUTLINE_DATA                                                        
      FULL(@"SEL$1" "PART2"@"SEL$1")                                            
      OUTLINE_LEAF(@"SEL$1")                                                    
      SHARED(8)                                                                 
      ALL_ROWS                                                                  
      DB_VERSION('12.1.0.1')                                                    
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')                                     
      IGNORE_OPTIM_EMBEDDED_HINTS                                               
      END_OUTLINE_DATA                                                          
  */

The first query selects all rows in the table, the second all rows for a specific hard-coded date, and the third all rows for a date specified by a bind variable. All three execution plans show the PX BLOCK ITERATOR operation indicating the use of block range granules.

Now let us add one more partition to bring the total number of partitions to 512

ALTER TABLE part2
   SPLIT PARTITION pdefault
      AT (DATE '2000-06-19', maxvalue)
      INTO (PARTITION p20000619_maxvalue, PARTITION pdefault);

Let us see what execution plans change.

Plan hash value: 1807983963                                                     
                                                                                
-------------------------------------------------------------                   
| Id  | Operation                | Name     | Pstart| Pstop |                   
-------------------------------------------------------------                   
|   0 | SELECT STATEMENT         |          |       |       |                   
|   1 |  PX COORDINATOR          |          |       |       |                   
|   2 |   PX SEND QC (RANDOM)    | :TQ10000 |       |       |                   
|   3 |    PX PARTITION RANGE ALL|          |     1 |   512 |                   
|   4 |     TABLE ACCESS FULL    | PART2    |     1 |   512 |                   
-------------------------------------------------------------                   
                                                                                
Outline Data                                                                    
-------------                                                                   
                                                                                
  /*+                                                                           
      BEGIN_OUTLINE_DATA                                                        
      FULL(@"SEL$1" "PART2"@"SEL$1")                                            
      OUTLINE_LEAF(@"SEL$1")                                                    
      SHARED(8)                                                                 
      ALL_ROWS                                                                  
      DB_VERSION('12.1.0.1')                                                    
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')                                     
      IGNORE_OPTIM_EMBEDDED_HINTS                                               
      END_OUTLINE_DATA                                                          
  */
Plan hash value: 3618659711                                                     
                                                                                
---------------------------------------------------------                       
| Id  | Operation            | Name     | Pstart| Pstop |                       
---------------------------------------------------------                       
|   0 | SELECT STATEMENT     |          |       |       |                       
|   1 |  PX COORDINATOR      |          |       |       |                       
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |       |       |                       
|   3 |    PX BLOCK ITERATOR |          |     1 |     3 |                       
|   4 |     TABLE ACCESS FULL| PART2    |     1 |     3 |                       
---------------------------------------------------------                       
                                                                                
Outline Data                                                                    
-------------                                                                   
                                                                                
  /*+                                                                           
      BEGIN_OUTLINE_DATA                                                        
      FULL(@"SEL$1" "PART2"@"SEL$1")                                            
      OUTLINE_LEAF(@"SEL$1")                                                    
      SHARED(8)                                                                 
      ALL_ROWS                                                                  
      DB_VERSION('12.1.0.1')                                                    
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')                                     
      IGNORE_OPTIM_EMBEDDED_HINTS                                               
      END_OUTLINE_DATA                                                          
  */
Plan hash value: 2732640421                                                     
                                                                                
------------------------------------------------------------------              
| Id  | Operation                     | Name     | Pstart| Pstop |              
------------------------------------------------------------------              
|   0 | SELECT STATEMENT              |          |       |       |              
|   1 |  PX COORDINATOR               |          |       |       |              
|   2 |   PX SEND QC (RANDOM)         | :TQ10000 |       |       |              
|   3 |    PX PARTITION RANGE ITERATOR|          |   KEY |   KEY |              
|   4 |     TABLE ACCESS FULL         | PART2    |   KEY |   KEY |              
------------------------------------------------------------------              
                                                                                
Outline Data                                                                    
-------------                                                                   
                                                                                
  /*+                                                                           
      BEGIN_OUTLINE_DATA                                                        
      FULL(@"SEL$1" "PART2"@"SEL$1")                                            
      OUTLINE_LEAF(@"SEL$1")                                                    
      SHARED(8)                                                                 
      ALL_ROWS                                                                  
      DB_VERSION('12.1.0.1')                                                    
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')                                     
      IGNORE_OPTIM_EMBEDDED_HINTS                                               
      END_OUTLINE_DATA                                                          
  */

We can see that block range granules continue to be used when a hard-coded date is specified but that in the other two cases partition granules are now used. Why?

The answer can be found by looking at the description of two hidden parameters:


NAME                            VALUE   DEFLT   TYPE    DESCRIPTION
_px_partition_scan_enabled      TRUE    TRUE    boolean enables or disables parallel
                                                        partition-based scan 
_px_partition_scan_threshold      64    TRUE    number  least number of partitions per slave
                                                        to start partition-based scan

What we learn from these descriptions is that once the number of partitions reaches 64 times the degree of paralellism (DOP) then the CBO will elect to use partition granules. The DOP is 8 meaning that when the number of partitions reached 512 (64 x 8 for those of you with arithmetical challenges) the CBO switched to using partition granules. When partition elimination occured as a result of the hard-coded date the CBO knew that only 3 subpartitions would be referenced and so block range granules were selected. But what about the bind variable case?

The issue here can be a bit tricky for some people to spot (mea culpa). Although we know that there are at most three partitions for each date this is not enforced and the CBO does not have a way of checking. Accordingly, in this specific case, partition elimination is not considered when determining which type of granule should be used. But we know that block ranges should be used if we are to utilize all 8 parallel query servers and not just 3.

Interestingly enough the outline hints do not change when the granule type changes, meaning that execution plans may be unstable even if you have a SQL Plan Baseline defined for a statement.

The good news is that if you recognize the problem you do have the option of adding an opt_param('_px_partition_scan_enabled','false') hint to force the use of block range granules (and that will propagate to your baseline). Furthermore, if you know your application well enough you may reasonably consider setting "_px_partition_scan_enabled" to FALSE at the system level. Do not worry. This won’t affect partition-wise joins!

Three more quick points before I close:

1. You will have noticed that empty partitions are not discounted. Yet another reason not to create lots of them in advance!
2. If you are wondering about whether bind variable peeking affects the result, I can tell you that it doesn’t!
3. I tested this on 11.2.0.2 and 12.1 with identical results. I don’t have access to anything earlier or later.

Update on composite partitioned tables

When we look at composite partitioned tables life gets a bit more complicated.  I originally thought that I would need another post to address it but at the end of the day the conclusions can be stated reasonably concisely.

  • If no equality predicate is specified for the partitioning column(s) and an equality predicate is specified for the subpartitioning column (or equality predicates specified for all subpartitioning columns) then the CBO knows that there will be exactly one subpartition accessed per partition.  Partition granules will be used when the number of partitions is 64 x DOP or greater.
  • If no equality predicate is specified for the subpartitioning column(s) and an equality predicate is specified for the partitioning column (or equality predicates specified for all partitioning columns) then the CBO divides the total number of subpartitions in the table by the number of partitions.  Partition granules are used if this calculated average exceeds 64 x DOP.
  • When the predicates are any more complicated then partition elimination is not considered and partition granules are used whenever the total number of subpartitions exceeds 64 x DOP.

In my opinion this is all fuzzy thinking.  The high value of "_px_partition_scan_threshold" suggests a manta of “if in doubt use block range granules” but by ignoring the effect of empty partitions or complex predicates the mantra “if in doubt use partition granules” comes to mind.  The latter is surely dangerous.

Create a free website or blog at WordPress.com.