SQLMDX

Eventually it's all about data

Yet another approach to overcome bloom filter bug

Posted by sqlmdx on October 4, 2014

Bloom filter may help to dramatically reduce amount of data to be read from partitioned table by eliminating unneeded partitions.
Here is a nice introduction into bloom filters.

Unfortunately bloom filter doesn’t work with DML which causes a lot of challenges in 11gR2.
This bug is documented on metalink Bug 13801198 : BLOOM PRUNING/FILTER NOT USED IN DML STATEMENTS however that note doesn’t suggest any workarounds.

Let’s consider some example and possible workarounds.
Tables sales and product equi-partitioned

partition by range (dt)
   interval ( numtodsinterval(1, 'DAY') )
   subpartition by hash (product_id)
      subpartitions 256
   (partition empty values less than (date '2013-01-01'))

Below query returns count of sales for products with given category:

SQL> explain plan for
  2  select --+ parallel(4)
  3        count(*)
  4    from sales s
  5         join (select *
  6                 from product
  7                where dt = :dt and category_id = :category_id) p
  8            on s.dt = p.dt and s.product_id = p.product_id;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1102004967

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |    27 |   284   (3)| 00:00:04 |       |    |   |      |            |
|   1 |  SORT AGGREGATE              |          |     1 |    27 |            |          |       |    |   |      |            |
|   2 |   PX COORDINATOR             |          |       |       |            |          |       |    |   |      |            |
|   3 |    PX SEND QC (RANDOM)       | :TQ10001 |     1 |    27 |            |          |       |    |  Q1,01 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE           |          |     1 |    27 |            |          |       |    |  Q1,01 | PCWP |       |
|*  5 |      HASH JOIN               |          |   100K|  2636K|   284   (3)| 00:00:04 |       |    |  Q1,01 | PCWP |       |
|   6 |       PART JOIN FILTER CREATE| :BF0000  |   200 |  3000 |    52   (0)| 00:00:01 |       |    |  Q1,01 | PCWP |       |
|   7 |        PX RECEIVE            |          |   200 |  3000 |    52   (0)| 00:00:01 |       |    |  Q1,01 | PCWP |       |
|   8 |         PX SEND BROADCAST    | :TQ10000 |   200 |  3000 |    52   (0)| 00:00:01 |       |    |  Q1,00 | P->P | BROADCAST  |
|   9 |          PX BLOCK ITERATOR   |          |   200 |  3000 |    52   (0)| 00:00:01 |     1 |   256 |  Q1,00 | PCWC |            |
|* 10 |           TABLE ACCESS FULL  | PRODUCT  |   200 |  3000 |    52   (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWP |            |
|  11 |       PX BLOCK ITERATOR      |          |  1000K|    11M|   230   (3)| 00:00:03 |     1 |   256 |  Q1,01 | PCWC |            |
|* 12 |        TABLE ACCESS FULL     | SALES    |  1000K|    11M|   230   (3)| 00:00:03 |   KEY |   KEY |  Q1,01 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("S"."DT"="PRODUCT"."DT" AND "S"."PRODUCT_ID"="PRODUCT"."PRODUCT_ID")
  10 - filter("CATEGORY_ID"=TO_NUMBER(:CATEGORY_ID) AND "DT"=:DT)
  12 - filter("S"."DT"=:DT)

Note
-----
   - Degree of Parallelism is 4 because of hint

30 rows selected.

Bloom filter was created on a step with id = 6 which in result helps to access only necessary sub-partitions during access to sales table.
If we try to insert result of a query into another table then bloom filter will not be used any longer and “PART JOIN FILTER CREATE” will disappear from a plan.

There are a few workarounds in 11gR2 but all of them have drawbacks.

  1. Dynamic SQL: build “in list” with a filter
  2. Insert into collection instead of a table
  3. Use undocumented precompute_subquery hint
  4. Execute query in a loop for each value of join key

The best way to achieve the same performance as with bloom filter (end event better) is to use partition-wise join.

SQL> explain plan for
  2  select --+ parallel(4) pq_distribute(s none none)
  3        count(*)
  4    from sales s
  5         join (select *
  6                 from product
  7                where dt = :dt and category_id = :category_id) p
  8            on s.dt = p.dt and s.product_id = p.product_id;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4291862942

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |     1 |    27 |   284   (3)| 00:00:04 |       |    |           |      |            |
|   1 |  SORT AGGREGATE                 |          |     1 |    27 |            |          |       |    |           |      |            |
|   2 |   PX COORDINATOR                |          |       |       |            |          |       |    |           |      |            |
|   3 |    PX SEND QC (RANDOM)          | :TQ10000 |     1 |    27 |            |          |       |    |  Q1,00 | P->S | QC (RAND)     |
|   4 |     SORT AGGREGATE              |          |     1 |    27 |            |          |       |    |  Q1,00 | PCWP |               |
|   5 |      PX PARTITION HASH ALL      |          |   100K|  2636K|   284   (3)| 00:00:04 |     1 |   256 |  Q1,00 | PCWC |            |
|*  6 |       HASH JOIN                 |          |   100K|  2636K|   284   (3)| 00:00:04 |       |    |  Q1,00 | PCWP |               |
|   7 |        PX PARTITION RANGE SINGLE|          |   200 |  3000 |    52   (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL       | PRODUCT  |   200 |  3000 |    52   (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWP |            |
|   9 |        PX PARTITION RANGE SINGLE|          |  1000K|    11M|   230   (3)| 00:00:03 |   KEY |   KEY |  Q1,00 | PCWC |            |
|* 10 |         TABLE ACCESS FULL       | SALES    |  1000K|    11M|   230   (3)| 00:00:03 |   KEY |   KEY |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("S"."PRODUCT_ID"="PRODUCT"."PRODUCT_ID" AND "S"."DT"="PRODUCT"."DT")
   8 - filter("CATEGORY_ID"=TO_NUMBER(:CATEGORY_ID) AND "DT"=:DT)
  10 - filter("S"."DT"=:DT)

Note
-----
   - Degree of Parallelism is 4 because of hint

28 rows selected.

So plan with partition-wise join has the same cost as the plan with bloom filter but Oracle chooses the latter.
To enforce it to use partition-wise join we can use hint pq_distribute([alias] none none).
Both tables must be equi-partitioned on the join key.
In this case no data redistribution happens between individual PX servers, because they are able to complete the join by joining matching partitions. So number of parallel slaves will be the same as DOP unlike query with bloom filter which generates 2 * DOP slaves due to producer -> consumer model.
In above example Oracle first reads all products for specified date then filters them by category and then derives data for each product from sales table in the correspondent parallel slave.

It’s worth to note that partition-wise join can be applied in cases if

  • either both recordsets to be joined are tables
  • or any of them can be inline view with some restrictions: no joins, no rownum in select list, no filters by analytic functions etc

So if records from one of equi-partitioned tables should be derived in a complex inline view before joining then it makes sense to store result of that inline view in an auxiliary equi-partitioned table. Unfortunately it’s impossible to use temporary table for that purpose because it cannot be partitioned.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: