Vishal desai’s Oracle Blog

September 18, 2013

Learning Bloom Filters

Filed under: Exadata, Performance Tuning — vishaldesai @ 9:35 pm

Thanks to Jonathan Lewis for quiz on bloom filter. I learned more about bloom filters from quiz and trying to understand answers for below questions:

1) How many bloom filters are created?

2) How many bloom filters are used and where exactly they are used?

Scripts to create test case are as follows:

drop table t1 purge;
drop table t2 purge;
drop table t3 purge;
drop table t4 purge;

create table t1 tablespace ism_ts
as
select
    rownum        id,
    to_char(rownum)    small_vc,
    rpad('x',100)    padding
from
    all_objects
where
    rownum <= 100
;

alter table t1 
    add constraint t1_pk primary key(id)
;

create table t2 tablespace ism_ts
as
select
    rownum        id,
    to_char(rownum)    small_vc,
    rpad('x',100)    padding
from
    all_objects
where
    rownum <= 100
;

alter table t2
    add constraint t2_pk primary key(id)
;

create table t3 tablespace ism_ts
as
select
    rownum        id,
    to_char(rownum)    small_vc,
    rpad('x',100)    padding
from
    all_objects
where
    rownum <= 100
;

alter table t3
    add constraint t3_pk primary key(id)
;

create table t4 tablespace ism_ts
nologging
as
select
    t1.id            id1,
    t2.id            id2,
    t3.id            id3,
    rpad(rownum,10)        small_vc,
    rpad('x',100)        padding
from
    t1, t2, t3
;


begin
    dbms_stats.gather_table_stats(
        user,
        't1',
        cascade => true,
        estimate_percent => null,
        method_opt => 'for all columns size 1'
    );
end;
/

begin
    dbms_stats.gather_table_stats(
        user,
        't2',
        cascade => true,
        estimate_percent => null,
        method_opt => 'for all columns size 1'
    );
end;
/

begin
    dbms_stats.gather_table_stats(
        user,
        't3',
        cascade => true,
        estimate_percent => null,
        method_opt => 'for all columns size 1'
    );
end;
/

begin
    dbms_stats.gather_table_stats(
        user,
        't4',
        cascade => true,
        estimate_percent => null,
        method_opt => 'for all columns size 1'
    );
end;
/

Case 1: Broadcast method

SQL, Output and plan are as follows:

select /*+
             monitor
             parallel(t1,4)
             parallel(t2,4)
             parallel(t3,4)
             parallel(t4,4)
             full(t1)
             full(t2)
             full(t3)
             full(t4)
     */
      count(t1.small_vc),
      count(t2.small_vc),
      count(t3.small_vc),
      count(t4.small_vc)
  from
      t3,
      t2,
      t1,
      t4
  where
      t1.id = t4.id1
  and t2.id = t4.id2
  and t3.id = t4.id3
  and t1.small_vc in (1,2,3)
  and t2.small_vc in (1,2,3)
  and t3.small_vc in (1,2,3)
;

COUNT(T1.SMALL_VC) COUNT(T2.SMALL_VC) COUNT(T3.SMALL_VC) COUNT(T4.SMALL_VC)
------------------ ------------------ ------------------ ------------------
                27                 27                 27                 27
                
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |       |       |   833 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE             |          |     1 |    38 |            |          |        |      |            |
|   2 |   PX COORDINATOR            |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)      | :TQ10003 |     1 |    38 |            |          |  Q1,03 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE          |          |     1 |    38 |            |          |  Q1,03 | PCWP |            |
|*  5 |      HASH JOIN              |          |    26 |   988 |   833   (1)| 00:00:01 |  Q1,03 | PCWP |            |
|   6 |       PX RECEIVE            |          |     3 |    18 |     2   (0)| 00:00:01 |  Q1,03 | PCWP |            |
|   7 |        PX SEND BROADCAST    | :TQ10000 |     3 |    18 |     2   (0)| 00:00:01 |  Q1,00 | P->P | BROADCAST  |
|   8 |         PX BLOCK ITERATOR   |          |     3 |    18 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  9 |          TABLE ACCESS FULL  | T1       |     3 |    18 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 10 |       HASH JOIN             |          |   882 | 28224 |   830   (1)| 00:00:01 |  Q1,03 | PCWP |            |
|  11 |        PX RECEIVE           |          |     3 |    18 |     2   (0)| 00:00:01 |  Q1,03 | PCWP |            |
|  12 |         PX SEND BROADCAST   | :TQ10001 |     3 |    18 |     2   (0)| 00:00:01 |  Q1,01 | P->P | BROADCAST  |
|  13 |          PX BLOCK ITERATOR  |          |     3 |    18 |     2   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|* 14 |           TABLE ACCESS FULL | T2       |     3 |    18 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|* 15 |        HASH JOIN            |          | 29701 |   754K|   828   (1)| 00:00:01 |  Q1,03 | PCWP |            |
|  16 |         PX RECEIVE          |          |     3 |    18 |     2   (0)| 00:00:01 |  Q1,03 | PCWP |            |
|  17 |          PX SEND BROADCAST  | :TQ10002 |     3 |    18 |     2   (0)| 00:00:01 |  Q1,02 | P->P | BROADCAST  |
|  18 |           PX BLOCK ITERATOR |          |     3 |    18 |     2   (0)| 00:00:01 |  Q1,02 | PCWC |            |
|* 19 |            TABLE ACCESS FULL| T3       |     3 |    18 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  20 |         PX BLOCK ITERATOR   |          |  1000K|    19M|   824   (1)| 00:00:01 |  Q1,03 | PCWC |            |
|* 21 |          TABLE ACCESS FULL  | T4       |  1000K|    19M|   824   (1)| 00:00:01 |  Q1,03 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------
  
Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T1"."ID"="T4"."ID1")
   9 - access(:Z>=:Z AND :Z<=:Z)
       filter((TO_NUMBER("T1"."SMALL_VC")=:SYS_B_0 OR TO_NUMBER("T1"."SMALL_VC")=:SYS_B_1 OR
              TO_NUMBER("T1"."SMALL_VC")=:SYS_B_2))
  10 - access("T2"."ID"="T4"."ID2")
  14 - access(:Z>=:Z AND :Z<=:Z)
       filter((TO_NUMBER("T2"."SMALL_VC")=:SYS_B_3 OR TO_NUMBER("T2"."SMALL_VC")=:SYS_B_4 OR
              TO_NUMBER("T2"."SMALL_VC")=:SYS_B_5))
  15 - access("T3"."ID"="T4"."ID3")
  19 - access(:Z>=:Z AND :Z<=:Z)
       filter((TO_NUMBER("T3"."SMALL_VC")=:SYS_B_6 OR TO_NUMBER("T3"."SMALL_VC")=:SYS_B_7 OR
              TO_NUMBER("T3"."SMALL_VC")=:SYS_B_8))
  21 - access(:Z>=:Z AND :Z<=:Z)
       filter(SYS_OP_BLOOM_FILTER_LIST(SYS_OP_BLOOM_FILTER(:BF0000,"T4"."ID3"),SYS_OP_BLOOM_FILTER(:BF0000,"T
              4"."ID2"),SYS_OP_BLOOM_FILTER(:BF0000,"T4"."ID1")))

 

image

 

image

1) How many bloom filters are created?

Bloom filter/filters were applied at line 21. Based on comment from Sayan on Jonathan’s blog it looks like only one filter was created after accessing table T1 and same bloom filter was appended after accessing table T2 and T3 (:BF0000) which got applied on line 21. So probably the answer to this question is 1.

2) How many bloom filters are used and where exactly they are used?

If answer to above question is 1, then obviously oracle used only one bloom filter at line 21. Did it really converted all joins into one bloom filter?

SQL> select distinct id from t1,t4 where t1.id = t4.id1 and t1.small_vc in (1,2,3);

        ID
----------
         1
         2
         3<= output 1

SQL> select distinct id from t2,t4 where t2.id = t4.id2 and t2.small_vc in (1,2,3);

        ID
----------
         1
         2
         3<= output 2

SQL> select distinct id from t3,t4 where t3.id = t4.id3 and t3.small_vc in (1,2,3);

        ID
----------
         1
         2
         3<= output 3
         
SQL> select count(1) from t4 where id1 in (1,2,3) AND id2 in (1,2,3) AND id3 in (1,2,3);

  COUNT(1)
----------
        27 <= output 4

Bloom filters basically converts join into filters and that’s why its a big deal on Exadata as they can be offloaded to Cell server. As you can see from above bloom filter created got applied at line 21 and as per real time SQL monitoring actual number of rows returned are 27 which matches with output 4.

So  one bloom filter (appended with multiple filters) is created and its used on single plan line.

Case 2: HASH method

select /*+
          monitor
             parallel(t1,4)
             parallel(t2,4)
             parallel(t3,4)
             parallel(t4,4)
             full(t1)
             full(t2)
             full(t3)
             full(t4)
             PQ_DISTRIBUTE(@"SEL$1" "T4"@"SEL$1" HASH HASH)
             PQ_DISTRIBUTE(@"SEL$1" "T2"@"SEL$1" HASH HASH)
             PQ_DISTRIBUTE(@"SEL$1" "T1"@"SEL$1" HASH HASH)
     */
      count(t1.small_vc),
      count(t2.small_vc),
      count(t3.small_vc),
      count(t4.small_vc)
  from
      t3,
      t2,
      t1,
      t4
  where
      t1.id = t4.id1
  and t2.id = t4.id2
  and t3.id = t4.id3
  and t1.small_vc in (1,2,3)
  and t2.small_vc in (1,2,3)
  and t3.small_vc in (1,2,3)
;

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |          |       |       |   833 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE                    |          |     1 |    38 |            |          |        |      |            |
|   2 |   PX COORDINATOR                   |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)             | :TQ10006 |     1 |    38 |            |          |  Q1,06 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE                 |          |     1 |    38 |            |          |  Q1,06 | PCWP |            |
|*  5 |      HASH JOIN                     |          |    26 |   988 |   833   (1)| 00:00:01 |  Q1,06 | PCWP |            |
|   6 |       JOIN FILTER CREATE           | :BF0000  |     3 |    18 |     2   (0)| 00:00:01 |  Q1,06 | PCWP |            |
|   7 |        PX RECEIVE                  |          |     3 |    18 |     2   (0)| 00:00:01 |  Q1,06 | PCWP |            |
|   8 |         PX SEND HASH               | :TQ10004 |     3 |    18 |     2   (0)| 00:00:01 |  Q1,04 | P->P | HASH       |
|   9 |          PX BLOCK ITERATOR         |          |     3 |    18 |     2   (0)| 00:00:01 |  Q1,04 | PCWC |            |
|* 10 |           TABLE ACCESS FULL        | T1       |     3 |    18 |     2   (0)| 00:00:01 |  Q1,04 | PCWP |            |
|  11 |       PX RECEIVE                   |          |   882 | 28224 |   830   (1)| 00:00:01 |  Q1,06 | PCWP |            |
|  12 |        PX SEND HASH                | :TQ10005 |   882 | 28224 |   830   (1)| 00:00:01 |  Q1,05 | P->P | HASH       |
|  13 |         JOIN FILTER USE            | :BF0000  |   882 | 28224 |   830   (1)| 00:00:01 |  Q1,05 | PCWP |            |
|* 14 |          HASH JOIN BUFFERED        |          |   882 | 28224 |   830   (1)| 00:00:01 |  Q1,05 | PCWP |            |
|  15 |           JOIN FILTER CREATE       | :BF0001  |     3 |    18 |     2   (0)| 00:00:01 |  Q1,05 | PCWP |            |
|  16 |            PX RECEIVE              |          |     3 |    18 |     2   (0)| 00:00:01 |  Q1,05 | PCWP |            |
|  17 |             PX SEND HASH           | :TQ10002 |     3 |    18 |     2   (0)| 00:00:01 |  Q1,02 | P->P | HASH       |
|  18 |              PX BLOCK ITERATOR     |          |     3 |    18 |     2   (0)| 00:00:01 |  Q1,02 | PCWC |            |
|* 19 |               TABLE ACCESS FULL    | T2       |     3 |    18 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  20 |           PX RECEIVE               |          | 29701 |   754K|   828   (1)| 00:00:01 |  Q1,05 | PCWP |            |
|  21 |            PX SEND HASH            | :TQ10003 | 29701 |   754K|   828   (1)| 00:00:01 |  Q1,03 | P->P | HASH       |
|  22 |             JOIN FILTER USE        | :BF0001  | 29701 |   754K|   828   (1)| 00:00:01 |  Q1,03 | PCWP |            |
|* 23 |              HASH JOIN BUFFERED    |          | 29701 |   754K|   828   (1)| 00:00:01 |  Q1,03 | PCWP |            |
|  24 |               JOIN FILTER CREATE   | :BF0002  |     3 |    18 |     2   (0)| 00:00:01 |  Q1,03 | PCWP |            |
|  25 |                PX RECEIVE          |          |     3 |    18 |     2   (0)| 00:00:01 |  Q1,03 | PCWP |            |
|  26 |                 PX SEND HASH       | :TQ10000 |     3 |    18 |     2   (0)| 00:00:01 |  Q1,00 | P->P | HASH       |
|  27 |                  PX BLOCK ITERATOR |          |     3 |    18 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|* 28 |                   TABLE ACCESS FULL| T3       |     3 |    18 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  29 |               PX RECEIVE           |          |  1000K|    19M|   824   (1)| 00:00:01 |  Q1,03 | PCWP |            |
|  30 |                PX SEND HASH        | :TQ10001 |  1000K|    19M|   824   (1)| 00:00:01 |  Q1,01 | P->P | HASH       |
|  31 |                 JOIN FILTER USE    | :BF0002  |  1000K|    19M|   824   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|  32 |                  PX BLOCK ITERATOR |          |  1000K|    19M|   824   (1)| 00:00:01 |  Q1,01 | PCWC |            |
|* 33 |                   TABLE ACCESS FULL| T4       |  1000K|    19M|   824   (1)| 00:00:01 |  Q1,01 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------

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

   5 - access("T1"."ID"="T4"."ID1")
  10 - access(:Z>=:Z AND :Z<=:Z)
       filter((TO_NUMBER("T1"."SMALL_VC")=:SYS_B_0 OR TO_NUMBER("T1"."SMALL_VC")=:SYS_B_1 OR
              TO_NUMBER("T1"."SMALL_VC")=:SYS_B_2))
  14 - access("T2"."ID"="T4"."ID2")
  19 - access(:Z>=:Z AND :Z<=:Z)
       filter((TO_NUMBER("T2"."SMALL_VC")=:SYS_B_3 OR TO_NUMBER("T2"."SMALL_VC")=:SYS_B_4 OR
              TO_NUMBER("T2"."SMALL_VC")=:SYS_B_5))
  23 - access("T3"."ID"="T4"."ID3")
  28 - access(:Z>=:Z AND :Z<=:Z)
       filter((TO_NUMBER("T3"."SMALL_VC")=:SYS_B_6 OR TO_NUMBER("T3"."SMALL_VC")=:SYS_B_7 OR
              TO_NUMBER("T3"."SMALL_VC")=:SYS_B_8))
  33 - access(:Z>=:Z AND :Z<=:Z)
       filter(SYS_OP_BLOOM_FILTER(:BF0000,"T4"."ID3"))

image

image

image

image

1) How many bloom filters are created?

If we look at SQL predicate information it shows only one bloom filter is used (:BF0000) at line 33. According to PLAN, there are three bloom filters created (:BF0000, :BF0001, :BF0002). So answer is 3 bloom filters are created.

2) How many bloom filters are used and where exactly they are used?

SQL> select count(1) from t3,t4 where t3.id = t4.id3 and t3.small_vc in (1,2,3);

  COUNT(1)
----------
     30000 <= output 5

Based on real time SQL monitoring, actual rows returned from line 33 is 30,000 which matches with output 5. :BF0000 was created at line 6 (For T1, t1.id = t4.id1) and as per filter predicate it used :BF0000 for T4.ID3 and line 31 says it used :BF0002 which is confusing.

Going back to 3 bloom filters where are :BF0000 and  :BF0001 used?

I think they are used exactly where they are shown in plan. Notice number of rows returned drops from 30k to 906 and 900 to 33.

image

With "_bloom_filter_enabled" set to FALSE plan looks like following:

image

Memory consumption for hash join buffered also went up from 1 MB to 3 MB.

So three bloom filters (each having one filter) are created and all three are used on different plan lines. One filter was used while accessing T4 and other two were used when accessing data from hash joins result set.

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: