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.
Tony's Oracle Tips 
Hi Tony, great post. I am facing a problem with a SQL query, that is choosing PARTITION granules just because of the number of partitions, no matter how many of them are empty. In my case, I have 39 partitions, with just 2 of them not empty. One has almost all the data, and the other has much less rows. If I choose any DoP up to 19, Oracle chooses partition-based granules. With DoP >= 20, it chooses block-based granules, which is the correct for the case. So, it seems to me that in this case Oracle is choosing block-based if DoP*2 > Partitions. I tried to change the “_” parameters, but I couldn’t change the behaviour. For me it’s a mistake to consider even empty partitions, do you agree?
Comment by Eduardo Claro — May 27, 2016 @ 7:26 pm |
Eduardo,
The various algorithms being used by the CBO in relation to partition tables are inconsistent with each other and with the approaches used for non-partitioned tables. I have another blog to write but the depressing nature of the post means that it is always at the bottom of my “to do” list.
But back to your points. The whole idea of mixing historic information from object statistics with current information about the number of partitions is ill-conceived in the first place but, as you say, if your are going to go that far why not take the extra step and exclude partitions with no associated segment?
I do not know what version of Oracle you are running but I would have thought that just setting “_px_partition_scan_enabled” to false should be sufficient to solve your problem. If you could post a script that shows partition granules still being used after disabling their use (together with the Oracle version) I may be able to see what is going on.
Comment by tonyhasler — May 28, 2016 @ 3:09 pm |