Home > Uncategorized > Undocumented parameter – _bloom_filter_enabled

Undocumented parameter – _bloom_filter_enabled

Update 24.5.2011: Turns out I seem to be wrong, these are two to separate features. So additinally I should understand those features, but kind of hard, if they are not documented. Anyway, here is the orginal artical:

You should not use undocumented parameters unless directed by Oracle Support. You ever read this? Here is post to remind, one should not set _ parameters and blindly assume they continue to work for a follow up release.

On one instance, 10g, due to some bug, directed by Oracle support _bloom_pruning_enabled was set to false. Then there was an upgrade, the parameter “survived” and one day I looked at an excecution plan, and hey, bloom filters in partition pruning appeard again.

Turns out, there is a new setting _bloom_pruning_enabled that controls now this behaviour. I do not know wheater this supercedes the former, is finer modeled or is a replacement . At least on 11.1.0.7 (no other tests done) the former setting _bloom_filter_enabled=false does not work fully.

The test:

create table testsz
(
  i number,
  j number,
  l number,
  x varchar2(1000)
)
partition by range (i)
 (
 partition partition_p1 values less than (1000) ,
 partition partition_p2 values less than (2000) ,
 partition partition_p3 values less than (3000) ,
 partition partition_p4 values less than (4000) ,
 partition partition_p5 values less than (5000) ,
 partition partition_p6 values less than (6000) ,
 partition partition_p7 values less than (7000) ,
 partition partition_p8 values less than (8000) ,
 partition partition_p9 values less than (9000) ,
 partition partition_p10 values less than (10000),
 partition partionte_max values less than (maxvalue)
 );

insert into testsz
with gen as (select rownum rn  from dual connect by levelselect rn, mod(rn, 100), trunc(rn,100),  lpad('x',300,'x') from gen;
commit;

create table testsz2 (i number);
insert into testsz2
select rownum*10 from dual connect by level commit;

exec dbms_stats.gather_table_stats(user, 'TESTSZ');
exec dbms_stats.gather_table_stats(user, 'TESTSZ2');

set echo on
set timing on

alter session set "_bloom_filter_enabled"=false;      
alter session set "_bloom_pruning_enabled"=true;   

explain plan for
  SELECT *
    FROM testsz A,
         testsz2 b
   WHERE A.i=B.i;

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

alter session set "_bloom_filter_enabled"=false;
alter session set "_bloom_pruning_enabled"=false;

explain plan for
  SELECT  *
    FROM testsz A,
         testsz2 b
   WHERE A.i=B.i;

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

and here is the output, the plans, clearly show the effect of the new parameter:

TEST @ PX> alter session set "_bloom_filter_enabled"=false;

Session altered.

Elapsed: 00:00:00.11
TEST @ PX> alter session set "_bloom_pruning_enabled"=true;

Session altered.

Elapsed: 00:00:00.04
TEST @ PX> 
TEST @ PX> explain plan for
  2    SELECT *
  3      FROM testsz A,
  4           testsz2 b
  5     WHERE A.i=B.i;

Explained.

Elapsed: 00:00:00.32
TEST @ PX> 
TEST @ PX> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4003749116

--------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    50 | 15800 |    61   (9)| 00:00:01 |       |       |
|*  1 |  HASH JOIN                   |         |    50 | 15800 |    61   (9)| 00:00:01 |       |       |
|   2 |   PART JOIN FILTER CREATE    | :BF0000 |    50 |   150 |     3   (0)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS FULL         | TESTSZ2 |    50 |   150 |     3   (0)| 00:00:01 |       |       |
|   4 |   PARTITION RANGE JOIN-FILTER|         |   100K|    29M|    56   (6)| 00:00:01 |:BF0000|:BF0000|
|   5 |    TABLE ACCESS FULL         | TESTSZ  |   100K|    29M|    56   (6)| 00:00:01 |:BF0000|:BF0000|

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------

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

   1 - access("A"."I"="B"."I")

Note
-----
   - 'PLAN_TABLE' is old version

21 rows selected.

Elapsed: 00:00:00.92
TEST @ PX> 
TEST @ PX> alter session set "_bloom_filter_enabled"=false;

Session altered.

Elapsed: 00:00:00.03
TEST @ PX> alter session set "_bloom_pruning_enabled"=false;

Session altered.

Elapsed: 00:00:00.04
TEST @ PX> 
TEST @ PX> explain plan for
  2    SELECT  *
  3      FROM testsz A,
  4           testsz2 b
  5     WHERE A.i=B.i;

Explained.

Elapsed: 00:00:00.03
TEST @ PX> 
TEST @ PX> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1042833598

------------------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |    50 | 15800 |    61   (9)| 00:00:01 |       |       |
|*  1 |  HASH JOIN           |         |    50 | 15800 |    61   (9)| 00:00:01 |       |       |
|   2 |   TABLE ACCESS FULL  | TESTSZ2 |    50 |   150 |     3   (0)| 00:00:01 |       |       |
|   3 |   PARTITION RANGE ALL|         |   100K|    29M|    56   (6)| 00:00:01 |     1 |    11 |
|   4 |    TABLE ACCESS FULL | TESTSZ  |   100K|    29M|    56   (6)| 00:00:01 |     1 |    11 |
------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

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

   1 - access("A"."I"="B"."I")

Note
-----
   - 'PLAN_TABLE' is old version

20 rows selected.

and by the way, the statement with bloom filter pruning was faster (I mean the actual one from the application). and do not say anything about old plan table ……

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

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: