Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Bloom Filter  [new]
dbms_photoshop
Member

Откуда: Ницца
Сообщений: 2826
В теме нет вопроса, просто может кому-то будет интересно. Сорри, если баян.
Я думал, что boom filter может быть в случае секционирования или параллельного выполнения как и пишет Антонини.
Но нашелся еще один интересный способ применения, когда, например, надо фильтрануть данные до группировки.
SQL> select
  2         *
  3    from (select rownum id from dual connect by level <= 10) t1
  4         join (select id, count(value) cnt from (select rownum id, rownum value from dual connect by level <= 2000000) group by id) t2
  5         on t1.id = t2.id;
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Starts | E-Rows | A-Rows |   A-Time   | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |      1 |        |     10 |00:00:08.24 |   5805 |   5805 |       |       |          |         |
|*  1 |  HASH JOIN                        |      |      1 |      1 |     10 |00:00:08.24 |   5805 |   5805 |  1517K|  1517K| 1102K (0)|         |
|   2 |   VIEW                            |      |      1 |      1 |     10 |00:00:00.01 |      0 |   0 |          |       |          |         |
|   3 |    COUNT                          |      |      1 |        |     10 |00:00:00.01 |      0 |   0 |          |       |          |         |
|   4 |     CONNECT BY WITHOUT FILTERING  |      |      1 |        |     10 |00:00:00.01 |      0 |   0 |          |       |          |         |
|   5 |      FAST DUAL                    |      |      1 |      1 |      1 |00:00:00.01 |      0 |   0 |          |       |          |         |
|   6 |   VIEW                            |      |      1 |      1 |   2000K|00:00:07.09 |   5805 |   5805 |       |       |          |         |
|   7 |    HASH GROUP BY                  |      |      1 |      1 |   2000K|00:00:06.35 |   5805 |   5805 |    76M|  5754K|   14M (1)|   50176 |
|   8 |     VIEW                          |      |      1 |      1 |   2000K|00:00:03.97 |      0 |   0 |          |       |          |         |
|   9 |      COUNT                        |      |      1 |        |   2000K|00:00:02.59 |      0 |   0 |          |       |          |         |
|  10 |       CONNECT BY WITHOUT FILTERING|      |      1 |        |   2000K|00:00:01.88 |      0 |   0 |          |       |          |         |
|  11 |        FAST DUAL                  |      |      1 |      1 |      1 |00:00:00.01 |      0 |   0 |          |       |          |         |
-------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select --+ px_join_filter(t2)
  2         *
  3    from (select rownum id from dual connect by level <= 10) t1
  4         join (select id, count(value) cnt from (select rownum id, rownum value from dual connect by level <= 2000000) group by id) t2
  5         on t1.id = t2.id;
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name    | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |         |      1 |        |     10 |00:00:03.85 |       |       |          |
|*  1 |  HASH JOIN                         |         |      1 |      1 |     10 |00:00:03.85 |  1517K|  1517K|  986K (0)|
|   2 |   JOIN FILTER CREATE               | :BF0000 |      1 |      1 |     10 |00:00:00.01 |       |       |          |
|   3 |    VIEW                            |         |      1 |      1 |     10 |00:00:00.01 |       |       |          |
|   4 |     COUNT                          |         |      1 |        |     10 |00:00:00.01 |       |       |          |
|   5 |      CONNECT BY WITHOUT FILTERING  |         |      1 |        |     10 |00:00:00.01 |       |       |          |
|   6 |       FAST DUAL                    |         |      1 |      1 |      1 |00:00:00.01 |       |       |          |
|   7 |   VIEW                             |         |      1 |      1 |    325 |00:00:03.84 |       |       |          |
|   8 |    HASH GROUP BY                   |         |      1 |      1 |    325 |00:00:03.84 |  1079K|  1079K| 1339K (0)|
|   9 |     JOIN FILTER USE                | :BF0000 |      1 |      1 |    325 |00:00:03.82 |       |       |          |
|  10 |      VIEW                          |         |      1 |      1 |   2000K|00:00:03.37 |       |       |          |
|  11 |       COUNT                        |         |      1 |        |   2000K|00:00:02.04 |       |       |          |
|  12 |        CONNECT BY WITHOUT FILTERING|         |      1 |        |   2000K|00:00:01.39 |       |       |          |
|  13 |         FAST DUAL                  |         |      1 |      1 |      1 |00:00:00.01 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------
Видно, что уменьшилось время выполнения, но еще более заметно сократилось потребление памяти для группировки, что очевидно.
Хотелось бы обратить внимание, что после применения фильтра число строк 325, а не 10. Хотя, например, если в таблице 2000, а не 2000000 строк, то все корректно.
Погрешность начинает появляться после определенного числа строк в таблице.
Про px_join_filter в доке утверждается следующее:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#SQLRF50585
This hint forces the optimizer to use parallel join bitmap filtering.
PS. 11.2.0.3
16 янв 13, 18:40    [13780552]     Ответить | Цитировать Сообщить модератору
 Re: Bloom Filter  [new]
xtender
Member

Откуда: Мск
Сообщений: 3220
dbms_photoshop,

не могу сейчас найти, но читал что-то похожее, когда при parallel_max_servers=0 и дизейбленном параллел квери строился из-за хинта параллельный план. и, кажется, у льюиса в книге было что-то с сериализацией параллельного плана. попозже поищу в гуглоридере
16 янв 13, 19:29    [13780766]     Ответить | Цитировать Сообщить модератору
 Re: Bloom Filter  [new]
dbms_photoshop
Member

Откуда: Ницца
Сообщений: 2826
xtender
строился из-за хинта параллельный план
Здесь же план не параллельный, а суть в том, что удается отфильтровать набор данных до группировки.
Хотя Оракл должен был бы и так об этом догадываться, учитывая, что группировка и соединение идет по одному и тому же полю.
17 янв 13, 14:18    [13785165]     Ответить | Цитировать Сообщить модератору
 Re: Bloom Filter  [new]
xtender
Member

Откуда: Мск
Сообщений: 3220
сейчас обнаружил, что он может включаться и без параллельности, и без секционирования и без группировки, а при хэшджойне и без хинта px_join_filter:
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |    17 |   559 |  1031   (8)| 00:00:04 |
|   1 |  SORT UNIQUE                        |                     |    17 |   559 |  1031   (8)| 00:00:04 |
|   2 |   UNION-ALL                         |                     |       |       |            |       |
|*  3 |    HASH JOIN                        |                     |     1 |    47 |   952   (1)| 00:00:03 |
|   4 |     JOIN FILTER CREATE              | :BF0000             |    19 |   646 |   165   (1)| 00:00:01 |
|*  5 |      TABLE ACCESS BY INDEX ROWID    | xxxxxxxx            |    19 |   646 |   165   (1)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN              | ix_xxxxxxx          |  5388 |       |     8   (0)| 00:00:01 |
|   7 |     VIEW                            |                     |  5306 | 68978 |   787   (1)| 00:00:03 |
|*  8 |      FILTER                         |                     |       |       |            |       |
|   9 |       JOIN FILTER USE               | :BF0000             |       |       |            |       |
|  10 |        NESTED LOOPS                 |                     |       |       |            |       |
|  11 |         NESTED LOOPS                |                     |  5306 |   227K|   787   (1)| 00:00:03 |
|* 12 |          TABLE ACCESS BY INDEX ROWID| yyyyyyy             |  5306 |   165K|   255   (1)| 00:00:01 |
|* 13 |           INDEX RANGE SCAN          | ix_yyyyyyy          |  9564 |       |     5   (0)| 00:00:01 |
|* 14 |          INDEX UNIQUE SCAN          | uq_zzzzzzzz         |     1 |       |     1   (0)| 00:00:01 |
|  15 |         TABLE ACCESS BY INDEX ROWID | zzzzzzzzz           |     1 |    12 |     1   (0)| 00:00:01 |
|* 16 |    FILTER                           |                     |       |       |            |       |
|* 17 |     TABLE ACCESS BY INDEX ROWID     | aaaaaaa             |    16 |   512 |    76   (0)| 00:00:01 |
|* 18 |      INDEX RANGE SCAN               | ix_aaaaaaaaa        |  2838 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
31 янв 13, 11:17    [13855348]     Ответить | Цитировать Сообщить модератору
 Re: Bloom Filter  [new]
dbms_photoshop
Member

Откуда: Ницца
Сообщений: 2826
xtender,

То, что без группировки - это интересно. Можешь показать allstats last с "JOIN FILTER USE" и без оного?
Желательно для таких данных, когда он дает профит. :)
31 янв 13, 12:01    [13855722]     Ответить | Цитировать Сообщить модератору
 Re: Bloom Filter  [new]
xtender
Member

Откуда: Мск
Сообщений: 3220
dbms_photoshop
Желательно для таких данных, когда он дает профит. :)
В данном случае никакого профита, здесь вообще hash join не нужен был. А подходящую ситуевину пока эмулировать некогда, может вечером сделаю.
31 янв 13, 12:37    [13855985]     Ответить | Цитировать Сообщить модератору
 Re: Bloom Filter  [new]
dbms_photoshop
Member

Откуда: Ницца
Сообщений: 2826
In response to
Comment by Jonathan Lewis — August 6, 2013 @ 10:28 am BST Aug 6,2013 | Reply
Sayan,

Can you reproduce the plan – it’s important when checking Bloom filter plans to be able to check the actual execution stats since a plan could claim to create and use a bloom filter without actually doing to. The dbms_photoshop one above your seems to be a case in point – it can’t be using a Bloom filter because none of the later plan lines show any predicates that would have to exist to operate the sys_op_bloom_filter() predicate. Your plan has a line which could be the relevant predicate (the table access line 12, which could eliminate the unique access). Execution stats could then tell us something about whether we were seeing early data elimination due to the filter.
I assume that bloom filter takes place in above case even though SYS_OP_BLOOM_FILTER doesn't exist in the plan.
The reasons for that are following:
1. There are JOIN FILTER CREATE and JOIN FILTER USE steps in the query plan.
2. Execution time with px_join_filter is halved compare to original query.

SYS_OP_BLOOM_FILTER doesn't appear in "Predicate Information" because of using connect by for generating rows "on the fly".

Let's consider next example:
drop table x;
create table x as select rownum id, rownum value from dual connect by level <= 2000000;
The execution flan is below
SQL> select --+ px_join_filter(t2)
  2         *
  3    from (select rownum id from dual connect by level <= 10) t1
  4         join (select id, count(value) cnt from x group by id) t2
  5         on t1.id = t2.id;

        ID         ID        CNT
---------- ---------- ----------
         1          1          1
         2          2          1
         3          3          1
         4          4          1
         5          5          1
         6          6          1
         7          7          1
         8          8          1
         9          9          1
        10         10          1

10 rows selected.

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1hv4v1z01y6cj, child number 0
-------------------------------------
select --+ px_join_filter(t2)        *   from (select rownum id from
dual connect by level <= 10) t1        join (select id, count(value)
cnt from x group by id) t2        on t1.id = t2.id

Plan hash value: 2175305

-------------------------------------------------------------------------------------
| Id  | Operation                        | Name | E-Rows |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |      |        |       |       |          |
|   1 |  MERGE JOIN                      |      |      1 |       |       |          |
|   2 |   SORT JOIN                      |      |   1996K|    50M|  2500K|   45M (0)|
|   3 |    VIEW                          |      |   1996K|       |       |          |
|   4 |     HASH GROUP BY                |      |   1996K|    60M|  4599K|   64M (0)|
|   5 |      TABLE ACCESS FULL           | X    |   2000K|       |       |          |
|*  6 |   SORT JOIN                      |      |      1 |  2048 |  2048 | 2048  (0)|
|   7 |    VIEW                          |      |      1 |       |       |          |
|   8 |     COUNT                        |      |        |       |       |          |
|   9 |      CONNECT BY WITHOUT FILTERING|      |        |       |       |          |
|  10 |       FAST DUAL                  |      |      1 |       |       |          |
-------------------------------------------------------------------------------------

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

   6 - access("T1"."ID"="T2"."ID")
       filter("T1"."ID"="T2"."ID")
So query transformation prevents CBO from using bloom filter.

Let's bake table order in join
SQL> select --+ px_join_filter(t2) leading(t1)
  2         *
  3    from (select rownum id from dual connect by level <= 10) t1
  4         join (select id, count(value) cnt from x group by id) t2
  5         on t1.id = t2.id;

        ID         ID        CNT
---------- ---------- ----------
         1          1          1
         6          6          1
         2          2          1
         5          5          1
         4          4          1
         8          8          1
         3          3          1
         7          7          1
         9          9          1
        10         10          1

10 rows selected.

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fdn2s2nqzjp58, child number 0
-------------------------------------
select --+ px_join_filter(t2) leading(t1)        *   from (select
rownum id from dual connect by level <= 10) t1        join (select id,
count(value) cnt from x group by id) t2        on t1.id = t2.id

Plan hash value: 475396432

----------------------------------------------------------------------------------------
| Id  | Operation                        | Name    | E-Rows |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |         |        |       |       |          |
|*  1 |  HASH JOIN                       |         |      1 |  1066K|  1066K| 1065K (0)|
|   2 |   JOIN FILTER CREATE             | :BF0000 |      1 |       |       |          |
|   3 |    VIEW                          |         |      1 |       |       |          |
|   4 |     COUNT                        |         |        |       |       |          |
|   5 |      CONNECT BY WITHOUT FILTERING|         |        |       |       |          |
|   6 |       FAST DUAL                  |         |      1 |       |       |          |
|   7 |   VIEW                           |         |   1996K|       |       |          |
|   8 |    HASH GROUP BY                 |         |   1996K|   862K|   862K|   20M (0)|
|   9 |     JOIN FILTER USE              | :BF0000 |   2000K|       |       |          |
|* 10 |      TABLE ACCESS FULL           | X       |   2000K|       |       |          |
----------------------------------------------------------------------------------------

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

   1 - access("T1"."ID"="T2"."ID")
  10 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"ID"))
So bloom filter takes place as well as you can find SYS_OP_BLOOM_FILTER in predicate section.
7 авг 13, 03:39    [14673341]     Ответить | Цитировать Сообщить модератору
 Re: Bloom Filter  [new]
xtender
Member

Откуда: Мск
Сообщений: 3220
dbms_photoshop,

When i asked Jonathan after seminar in Moscow about bloom filter, he said that bloom filter using can be considered when small amount of data joins with big amount. So, i think it is the one of such cases. But also i think that the second reason was the requirement of a hash join.

+ merge
SQL> select --+ px_join_filter(t2)
  2         *
  3    from (select rownum id from dual connect by level <= 10) t1
  4           join (select id, count(value) cnt from x group by id) t2
  5          on t1.id = t2.id;

        ID         ID        CNT
---------- ---------- ----------
         1          1          1
         2          2          1
         3          3          1
         4          4          1
         5          5          1
         6          6          1
         7          7          1
         8          8          1
         9          9          1
        10         10          1

10 rows selected.


SQL> @last

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bpnwcvbuj1umm, child number 0
-------------------------------------
select --+ px_join_filter(t2)         *    from (select rownum id from
dual connect by level <= 10) t1         join (select id, count(value)
cnt from x group by id) t2         on t1.id = t2.id

Plan hash value: 2175305

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |      |      1 |        |       |  1359 (100)|          |     10 |00:00:04.34 |    4424 |   4420 |       |       |          |
|   1 |  MERGE JOIN                      |      |      1 |  23816 |   907K|  1359  (11)| 00:00:17 |     10 |00:00:04.34 |    4424 |   4420 |       |       |          |
|   2 |   SORT JOIN                      |      |      1 |   2381K|    59M|  1356  (10)| 00:00:17 |     11 |00:00:04.33 |    4424 |   4420 |    50M|  2500K|   45M (0)|
|   3 |    VIEW                          |      |      1 |   2381K|    59M|  1356  (10)| 00:00:17 |   2000K|00:00:03.20 |    4424 |   4420 |       |       |          |
|   4 |     HASH GROUP BY                |      |      1 |   2381K|    59M|  1356  (10)| 00:00:17 |   2000K|00:00:02.68 |    4424 |   4420 |    60M|  4599K|   55M (0)|
|   5 |      TABLE ACCESS FULL           | X    |      1 |   2381K|    59M|  1243   (2)| 00:00:15 |   2000K|00:00:00.79 |    4424 |   4420 |       |       |          |
|*  6 |   SORT JOIN                      |      |     11 |      1 |    13 |     3  (34)| 00:00:01 |     10 |00:00:00.01 |       0 |      0 |  2048 |  2048 | 2048  (0)|
|   7 |    VIEW                          |      |      1 |      1 |    13 |     2   (0)| 00:00:01 |     10 |00:00:00.01 |       0 |      0 |       |       |          |
|   8 |     COUNT                        |      |      1 |        |       |            |          |     10 |00:00:00.01 |       0 |      0 |       |       |          |
|   9 |      CONNECT BY WITHOUT FILTERING|      |      1 |        |       |            |          |     10 |00:00:00.01 |       0 |      0 |       |       |          |
|  10 |       FAST DUAL                  |      |      1 |      1 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       0 |      0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$16C51A37
   3 - SEL$3        / T2@SEL$1
   4 - SEL$3
   5 - SEL$3        / X@SEL$3
   7 - SEL$2        / T1@SEL$1
   8 - SEL$2
  10 - SEL$2        / DUAL@SEL$2

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

   6 - access("T1"."ID"="T2"."ID")
       filter("T1"."ID"="T2"."ID")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "T2"."ID"[NUMBER,22], "T1"."ID"[NUMBER,22], "T2"."CNT"[NUMBER,22]
   2 - (#keys=1) "T2"."ID"[NUMBER,22], "T2"."CNT"[NUMBER,22]
   3 - "T2"."ID"[NUMBER,22], "T2"."CNT"[NUMBER,22]
   4 - "ID"[NUMBER,22], COUNT("VALUE")[22]
   5 - "ID"[NUMBER,22], "VALUE"[NUMBER,22]
   6 - (#keys=1) "T1"."ID"[NUMBER,22]
   7 - "T1"."ID"[NUMBER,22]
   8 - ROWNUM[4]
   9 - LEVEL[4]

Note
-----
   - dynamic sampling used for this statement (level=2)


58 rows selected.
+ hash join
SQL> select --+ px_join_filter(t2) use_hash(t1 t2)
  2        *
  3   from (select rownum id from dual connect by level <= 10) t1
  4        join (select id, count(value) cnt from x group by id) t2
  5          on t1.id = t2.id;

        ID         ID        CNT
---------- ---------- ----------
         1          1          1
         5          5          1
         8          8          1
         7          7          1
         9          9          1
        10         10          1
         6          6          1
         2          2          1
         4          4          1
         3          3          1

10 rows selected.

SQL> @last

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  098yt0myu5gff, child number 0
-------------------------------------
select --+ px_join_filter(t2) use_hash(t1 t2)         *    from (select
rownum id from dual connect by level <= 10) t1         join (select id,
count(value) cnt from x group by id) t2         on t1.id = t2.id

Plan hash value: 475396432

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |         |      1 |        |       |  1370 (100)|          |     10 |00:00:01.02 |    4424 |   4420 |       |       |          |
|*  1 |  HASH JOIN                       |         |      1 |  23816 |   907K|  1370  (11)| 00:00:17 |     10 |00:00:01.02 |    4424 |   4420 |  1066K|  1066K| 1090K (0)|
|   2 |   JOIN FILTER CREATE             | :BF0000 |      1 |      1 |    13 |     2   (0)| 00:00:01 |     10 |00:00:00.01 |       0 |      0 |       |       |          |
|   3 |    VIEW                          |         |      1 |      1 |    13 |     2   (0)| 00:00:01 |     10 |00:00:00.01 |       0 |      0 |       |       |          |
|   4 |     COUNT                        |         |      1 |        |       |            |          |     10 |00:00:00.01 |       0 |      0 |       |       |          |
|   5 |      CONNECT BY WITHOUT FILTERING|         |      1 |        |       |            |          |     10 |00:00:00.01 |       0 |      0 |       |       |          |
|   6 |       FAST DUAL                  |         |      1 |      1 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       0 |      0 |       |       |          |
|   7 |   VIEW                           |         |      1 |   2381K|    59M|  1356  (10)| 00:00:17 |    646 |00:00:01.02 |    4424 |   4420 |       |       |          |
|   8 |    HASH GROUP BY                 |         |      1 |   2381K|    59M|  1356  (10)| 00:00:17 |    646 |00:00:01.02 |    4424 |   4420 |   862K|   862K|   17M (0)|
|   9 |     JOIN FILTER USE              | :BF0000 |      1 |   2381K|    59M|  1243   (2)| 00:00:15 |    646 |00:00:01.01 |    4424 |   4420 |       |       |          |
|* 10 |      TABLE ACCESS FULL           | X       |      1 |   2381K|    59M|  1243   (2)| 00:00:15 |    646 |00:00:01.01 |    4424 |   4420 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$16C51A37
   3 - SEL$2        / T1@SEL$1
   4 - SEL$2
   6 - SEL$2        / DUAL@SEL$2
   7 - SEL$3        / T2@SEL$1
   8 - SEL$3
  10 - SEL$3        / X@SEL$3

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

   1 - access("T1"."ID"="T2"."ID")
  10 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"ID"))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1) "T1"."ID"[NUMBER,22], "T2"."ID"[NUMBER,22], "T2"."CNT"[NUMBER,22]
   2 - "T1"."ID"[NUMBER,22]
   3 - "T1"."ID"[NUMBER,22]
   4 - ROWNUM[4]
   5 - LEVEL[4]
   7 - "T2"."ID"[NUMBER,22], "T2"."CNT"[NUMBER,22]
   8 - "ID"[NUMBER,22], COUNT("VALUE")[22]
   9 - "ID"[NUMBER,22], "VALUE"[NUMBER,22]
  10 - "ID"[NUMBER,22], "VALUE"[NUMBER,22]

Note
-----
   - dynamic sampling used for this statement (level=2)


58 rows selected.
+ merge+leading
SQL> explain plan for
  2 select --+ px_join_filter(t2) leading(t1) use_merge(t2)
  3         *
  4    from (select rownum id from dual connect by level <= 1000) t1
  5         join (select id, count(value) cnt from x group by id) t2
  6          on t1.id = t2.id;

Explained.

SQL> @xplan

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3386282022

-------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |      | 23816 |   907K|       | 19020   (2)| 00:03:49 |
|   1 |  MERGE JOIN                      |      | 23816 |   907K|       | 19020   (2)| 00:03:49 |
|   2 |   SORT JOIN                      |      |     1 |    13 |       |     3  (34)| 00:00:01 |
|   3 |    VIEW                          |      |     1 |    13 |       |     2   (0)| 00:00:01 |
|   4 |     COUNT                        |      |       |       |       |            |          |
|*  5 |      CONNECT BY WITHOUT FILTERING|      |       |       |       |            |          |
|   6 |       FAST DUAL                  |      |     1 |       |       |     2   (0)| 00:00:01 |
|*  7 |   SORT JOIN                      |      |  2381K|    59M|   164M| 19017   (2)| 00:03:49 |
|   8 |    VIEW                          |      |  2381K|    59M|       |  1356  (10)| 00:00:17 |
|   9 |     HASH GROUP BY                |      |  2381K|    59M|       |  1356  (10)| 00:00:17 |
|  10 |      TABLE ACCESS FULL           | X    |  2381K|    59M|       |  1243   (2)| 00:00:15 |
-------------------------------------------------------------------------------------------------

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

   5 - filter(LEVEL<=1000)
   7 - access("T1"."ID"="T2"."ID")
       filter("T1"."ID"="T2"."ID")

Note
-----
   - dynamic sampling used for this statement (level=2)
7 авг 13, 04:33    [14673352]     Ответить | Цитировать Сообщить модератору
 Re: Bloom Filter  [new]
dbms_photoshop
Member

Откуда: Ницца
Сообщений: 2826
xtender
и без группировки
Интересно все-таки как он у тебя заюзался без группировки.
Возвращаясь к моему примеру, ему зачем-то необходима уникальность (без distinct не появляется).
+
SQL> select --+ px_join_filter(t2) use_hash(t1 t2)
  2         *
  3    from (select rownum id from dual connect by level <= 10) t1
  4         join (select distinct x.* from x) t2
  5         on t1.id = t2.id;

        ID         ID      VALUE
---------- ---------- ----------
         6          6          6
         5          5          5
         9          9          9
        10         10         10
         4          4          4
         8          8          8
         1          1          1
         2          2          2
         3          3          3
         7          7          7

10 rows selected.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  fgq1qhkxp7saw, child number 1
-------------------------------------
select --+ px_join_filter(t2) use_hash(t1 t2)        *   from (select
rownum id from dual connect by level <= 10) t1        join (select
distinct x.* from x) t2        on t1.id = t2.id

Plan hash value: 1416051211

----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |         |       |       |       |  8868 (100)|          |
|*  1 |  HASH JOIN                       |         |     1 |    32 |       |  8868   (2)| 00:01:47 |
|   2 |   JOIN FILTER CREATE             | :BF0000 |     1 |    13 |       |     2   (0)| 00:00:01 |
|   3 |    VIEW                          |         |     1 |    13 |       |     2   (0)| 00:00:01 |
|   4 |     COUNT                        |         |       |       |       |            |          |
|   5 |      CONNECT BY WITHOUT FILTERING|         |       |       |       |            |          |
|   6 |       FAST DUAL                  |         |     1 |       |       |     2   (0)| 00:00:01 |
|   7 |   VIEW                           |         |  2000K|    36M|       |  8854   (2)| 00:01:47 |
|   8 |    HASH UNIQUE                   |         |  2000K|    19M|    38M|  8854   (2)| 00:01:47 |
|   9 |     JOIN FILTER USE              | :BF0000 |  2000K|    19M|       |   803   (3)| 00:00:10 |
|* 10 |      TABLE ACCESS FULL           | X       |  2000K|    19M|       |   803   (3)| 00:00:10 |
----------------------------------------------------------------------------------------------------

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

   1 - access("T1"."ID"="T2"."ID")
  10 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"X"."ID"))


30 rows selected.

SQL> select --+ px_join_filter(t2) use_hash(t1 t2)
  2         *
  3    from (select rownum id from dual connect by level <= 10) t1
  4         join (select distinct rowid z, x.* from x) t2
  5         on t1.id = t2.id;

        ID Z                          ID      VALUE
---------- ------------------ ---------- ----------
         1 AAE/QeAAKAAF8xMAAA          1          1
         2 AAE/QeAAKAAF8xMAAB          2          2
         3 AAE/QeAAKAAF8xMAAC          3          3
         4 AAE/QeAAKAAF8xMAAD          4          4
         5 AAE/QeAAKAAF8xMAAE          5          5
         6 AAE/QeAAKAAF8xMAAF          6          6
         7 AAE/QeAAKAAF8xMAAG          7          7
         8 AAE/QeAAKAAF8xMAAH          8          8
         9 AAE/QeAAKAAF8xMAAI          9          9
        10 AAE/QeAAKAAF8xMAAJ         10         10

10 rows selected.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  6w89xnxx01390, child number 1
-------------------------------------
select --+ px_join_filter(t2) use_hash(t1 t2)        *   from (select
rownum id from dual connect by level <= 10) t1        join (select
distinct rowid z, x.* from x) t2        on t1.id = t2.id

Plan hash value: 3719396414

----------------------------------------------------------------------------------------
| Id  | Operation                       | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |      |       |       |   817 (100)|          |
|*  1 |  HASH JOIN                      |      |     1 |    23 |   817   (5)| 00:00:10 |
|   2 |   VIEW                          |      |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |    COUNT                        |      |       |       |            |          |
|   4 |     CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   5 |      FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL             | X    |  2000K|    19M|   803   (3)| 00:00:10 |
----------------------------------------------------------------------------------------

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

   1 - access("T1"."ID"="X"."ID")


25 rows selected.

SQL> select --+ px_join_filter(t2) use_hash(t1 t2)
  2         *
  3    from (select rownum id from dual connect by level <= 10) t1
  4         join (select distinct rowidtochar(rowid) z, x.* from x) t2
  5         on t1.id = t2.id;

        ID Z                          ID      VALUE
---------- ------------------ ---------- ----------
         4 AAE/QeAAKAAF8xMAAD          4          4
         3 AAE/QeAAKAAF8xMAAC          3          3
        10 AAE/QeAAKAAF8xMAAJ         10         10
         5 AAE/QeAAKAAF8xMAAE          5          5
         6 AAE/QeAAKAAF8xMAAF          6          6
         9 AAE/QeAAKAAF8xMAAI          9          9
         7 AAE/QeAAKAAF8xMAAG          7          7
         1 AAE/QeAAKAAF8xMAAA          1          1
         2 AAE/QeAAKAAF8xMAAB          2          2
         8 AAE/QeAAKAAF8xMAAH          8          8

10 rows selected.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  akqpy5sdpuddg, child number 1
-------------------------------------
select --+ px_join_filter(t2) use_hash(t1 t2)        *   from (select
rownum id from dual connect by level <= 10) t1        join (select
distinct rowidtochar(rowid) z, x.* from x) t2        on t1.id = t2.id

Plan hash value: 1416051211

----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |         |       |       |       |  8868 (100)|          |
|*  1 |  HASH JOIN                       |         |     1 |    43 |       |  8868   (2)| 00:01:47 |
|   2 |   JOIN FILTER CREATE             | :BF0000 |     1 |    13 |       |     2   (0)| 00:00:01 |
|   3 |    VIEW                          |         |     1 |    13 |       |     2   (0)| 00:00:01 |
|   4 |     COUNT                        |         |       |       |       |            |          |
|   5 |      CONNECT BY WITHOUT FILTERING|         |       |       |       |            |          |
|   6 |       FAST DUAL                  |         |     1 |       |       |     2   (0)| 00:00:01 |
|   7 |   VIEW                           |         |  2000K|    57M|       |  8854   (2)| 00:01:47 |
|   8 |    HASH UNIQUE                   |         |  2000K|    19M|    38M|  8854   (2)| 00:01:47 |
|   9 |     JOIN FILTER USE              | :BF0000 |  2000K|    19M|       |   803   (3)| 00:00:10 |
|* 10 |      TABLE ACCESS FULL           | X       |  2000K|    19M|       |   803   (3)| 00:00:10 |
----------------------------------------------------------------------------------------------------

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

   1 - access("T1"."ID"="T2"."ID")
  10 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"X"."ID"))


30 rows selected.
Интересен обратный пример, когда хочется отменить bloom filter.
Здесь надо учитывать, что хинт не имеет особого отношения, когда bloom filter появляется в случае секционированной таблицы.
+ DDL
create table fact
(
   dt
  ,value
)
partition by range (dt)
   interval ( numtodsinterval(1, 'DAY') )
   (partition empty values less than (date '2001-01-01'))
as
   select t.*, dbms_random.value
     from (select trunc(sysdate) - level
             from dual
           connect by level <= 100) t
         ,(select dummy
             from dual
           connect by level <= 100);

exec dbms_stats.gather_table_stats (user,'fact');
SQL> select --+ use_hash(f d) no_px_join_filter(f)
  2        count(*) cnt
  3    from    fact f
  4         join
  5            (select date '2013-09-09' dt from dual
  6             union all
  7             select date '2013-08-08' from dual) d
  8         on f.dt = d.dt;

       CNT
----------
       200

SQL> select * from table (dbms_xplan.display_cursor(format => 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1uvf4fyswdqbj, child number 0
-------------------------------------
select --+ use_hash(f d) no_px_join_filter(f)       count(*) cnt   from
   fact f        join           (select date '2013-09-09' dt from dual
          union all            select date '2013-08-08' from dual) d
    on f.dt = d.dt

Plan hash value: 3452777814

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |      1 |00:00:00.02 |       4 |    |          |          |
|   1 |  SORT AGGREGATE               |         |      1 |      1 |      1 |00:00:00.02 |       4 |    |          |          |
|*  2 |   HASH JOIN                   |         |      1 |    200 |    200 |00:00:00.02 |       4 |  1269K|  1269K|  502K (0)|
|   3 |    PART JOIN FILTER CREATE    | :BF0000 |      1 |      2 |      2 |00:00:00.01 |       0 |    |          |          |
|   4 |     VIEW                      |         |      1 |      2 |      2 |00:00:00.01 |       0 |    |          |          |
|   5 |      UNION-ALL                |         |      1 |        |      2 |00:00:00.01 |       0 |    |          |          |
|   6 |       FAST DUAL               |         |      1 |      1 |      1 |00:00:00.01 |       0 |    |          |          |
|   7 |       FAST DUAL               |         |      1 |      1 |      1 |00:00:00.01 |       0 |    |          |          |
|   8 |    PARTITION RANGE JOIN-FILTER|         |      1 |  10000 |    200 |00:00:00.02 |       4 |    |          |          |
|   9 |     TABLE ACCESS FULL         | FACT    |      2 |  10000 |    200 |00:00:00.01 |       4 |    |          |          |
------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("F"."DT"="D"."DT")


29 rows selected.

SQL> alter session set "_bloom_pruning_enabled" = false;

Session altered.

SQL> select --+ use_hash(f d) no_px_join_filter(f)
  2        count(*) cnt
  3    from    fact f
  4         join
  5            (select date '2013-09-09' dt from dual
  6             union all
  7             select date '2013-08-08' from dual) d
  8         on f.dt = d.dt;

       CNT
----------
       200

SQL> select * from table (dbms_xplan.display_cursor(format => 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1uvf4fyswdqbj, child number 1
-------------------------------------
select --+ use_hash(f d) no_px_join_filter(f)       count(*) cnt   from
   fact f        join           (select date '2013-09-09' dt from dual
          union all            select date '2013-08-08' from dual) d
    on f.dt = d.dt

Plan hash value: 3021729463

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        |      1 |00:00:00.01 |     200 |       |       |          |
|   1 |  SORT AGGREGATE       |      |      1 |      1 |      1 |00:00:00.01 |     200 |       |       |          |
|*  2 |   HASH JOIN           |      |      1 |    200 |    200 |00:00:00.01 |     200 |  1269K|  1269K|  493K (0)|
|   3 |    VIEW               |      |      1 |      2 |      2 |00:00:00.01 |       0 |       |       |          |
|   4 |     UNION-ALL         |      |      1 |        |      2 |00:00:00.01 |       0 |       |       |          |
|   5 |      FAST DUAL        |      |      1 |      1 |      1 |00:00:00.01 |       0 |       |       |          |
|   6 |      FAST DUAL        |      |      1 |      1 |      1 |00:00:00.01 |       0 |       |       |          |
|   7 |    PARTITION RANGE ALL|      |      1 |  10000 |  10000 |00:00:00.01 |     200 |       |       |          |
|   8 |     TABLE ACCESS FULL | FACT |   4642 |  10000 |  10000 |00:00:00.01 |     200 |       |       |          |
-------------------------------------------------------------------------------------------------------------------

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

   2 - access("F"."DT"="D"."DT")


28 rows selected.
В последнем случае совершенно дикое число Starts...
16 сен 13, 15:42    [14845350]     Ответить | Цитировать Сообщить модератору
 Re: Bloom Filter  [new]
xtender
Member

Откуда: Мск
Сообщений: 3220
dbms_photoshop
Интересно все-таки как он у тебя заюзался без группировки.
некогда пока было ковырять, но я Джонатану тогда послал тест-кейс. У меня на продах их довольно много разных.
dbms_photoshop
Интересен обратный пример, когда хочется отменить bloom filter.
да, у меня была такая ситуация на бою, но уже не помню, почему он так сильно мешал и как решал. Кажется, я сохранил "на потом", поищу на днях.
dbms_photoshop
Здесь надо учитывать, что хинт не имеет особого отношения, когда bloom filter появляется в случае секционированной таблицы.
Ну, не совсем: если посмотреть 10053, то видно что хинт валидный и применился, но только именно для простого фильтра:
Consider using bloom filter between D[from$_subquery$_002] and F[FACT] with ??
1Js1M4$v01aP1JcP01+P4411:kkoBloomFilter*kkopq.c*bloom_filter*5WBDF1
kkoBloomFilter: join ndv:0 reduction:1.000000 (limit:0.500000)  rejected because Negative hint found
А если посмотреть фикс. контрол:
SQL> @fix_control.sql bloom

    BUGNO VALUE SQL_FEATURE                         DESCRIPTION                                                                      OPTIMIZER_      EVENT IS_DEFAULT
--------- ----- ----------------------------------- -------------------------------------------------------------------------------- ---------- ---------- ----------
  6982954     1 QKSFM_PQ_6982954                    bloom filter for hash join with broadcast left                                   11.1.0.7            0          1
  7613118     1 QKSFM_PQ_7613118                    bloom filter costing takes account of join input swap                            11.2.0.1            0          1
  7199035     1 QKSFM_PQ_7199035                    bloom filter with broadcast left for partial parallel plan                       11.2.0.1            0          1
  8243446     1 QKSFM_PQ_8243446                    do not create bloom filter for broadcast if not pushable to scan                 11.2.0.1            0          1
  8890233     1 QKSFM_PQ_8890233                    enable pushing bloom filter through NLJ                                          11.2.0.2            0          1
  9203723     1 QKSFM_SQL_CODE_GENERATOR_9203723    allow bloom pruning and bloom filtering on the same join                         11.2.0.2            0          1
  9762592     3 QKSFM_PQ_9762592                    fold bloom filter when offload to storage                                        11.2.0.2            0          1
 10117760     1 QKSFM_CURSOR_SHARING_10117760       cardinality feedback should account for bloom filters                            11.2.0.3            0          1
, становится ясно, что механизмы независимые.

зы. Первое подсвеченное как раз к тому, о чем выше говорилось - 14673352
зы2. Да и вообще интересные описания...
16 сен 13, 23:21    [14847176]     Ответить | Цитировать Сообщить модератору
 Re: Bloom Filter  [new]
Alexander Anokhin
Member

Откуда: Moscow
Сообщений: 425
dbms_photoshop
В последнем случае совершенно дикое число Starts...

Это жук 12693573. Он не связан с фильтрами Блума, а связан с определением партиций в интервальном партиционировании.

> select count(*)
  2    from fact
  3   where value = 123;

  COUNT(*)
----------
         0

-------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |       |       |      1 |00:00:00.02 |     269 |
|   1 |  SORT AGGREGATE      |      |      1 |      1 |       |       |      1 |00:00:00.02 |     269 |
|   2 |   PARTITION RANGE ALL|      |      1 |      1 |     1 |1048575|      0 |00:00:00.02 |     269 |
|*  3 |    TABLE ACCESS FULL | FACT |   4642 |      1 |     1 |1048575|      0 |00:00:00.01 |     269 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("VALUE"=123)


dbms_photoshop
xtender
и без группировки
Интересно все-таки как он у тебя заюзался без группировки.
Возвращаясь к моему примеру, ему зачем-то необходима уникальность (без distinct не появляется).

Насколько я знаю, чтобы фильтры стали возможными в serial execution, должен быть "тяжелый" оператор (sort, group by, filter, etc.) выше использования фильтра. Причем этот оператор не обязательно должен быть между filter create/use.
17 сен 13, 17:21    [14850691]     Ответить | Цитировать Сообщить модератору
 Re: Bloom Filter  [new]
dbms_photoshop
Member

Откуда: Ницца
Сообщений: 2826
xtender
только именно для простого фильтра
Это и имелось в виду.
xtender
1Js1M4$v01aP1JcP01+P4411:kkoBloomFilter*kkopq.c*bloom_filter*5WBDF1
Каким образом у тебя кусок trm файла влез в trc?
18 сен 13, 00:11    [14851908]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить
 
Лучший учебный центр Microsoft!
Новейшие курсы Microsoft SQL Server 2014!
Статус Academy Oracle. Очень привлекательные цены на курсы Oracle!
Отсрочка платежа или скидка 5% на комплексные программы!