Oracle mechanics

05.06.2016

Посказка BITMAP_TREE

Filed under: CBO,hints,Oracle — Игорь Усольцев @ 17:52
Tags:

Определение, приведённое ранее:

Usage: BITMAP_TREE([<@Block>] <Table> AND(<Index1>[ <Index2> ...]))
    or BITMAP_TREE([<@Block>] <Table> AND((<Index1 columns>)[ (<Index2 columns>) ...]))
Description: Instructs the optimizer to convert ROWIDs to bitmap, then performance bitmap operations

оказалось неполным, и в этом, как обычно, помогла проблема с планом критичного запроса типа:

SELECT *
  FROM dual
 WHERE EXISTS (SELECT *
          FROM (SELECT DISTINCT t_lines_1.*
                  FROM t_lines t_lines_1
                  JOIN t_lines
                    ON t_lines.group_lines_id = t_lines_1.id
                 WHERE t_lines.group_lines_id IS NOT NULL
                   AND t_lines_1.group_lines_id IS NULL
                   AND t_lines.service_id IN (:service_id_1, :service_id_2, :service_id_3, :service_id_4)                     -- [1]
                   AND (t_lines.person_id = :person_id_1 OR t_lines.client_id = :client_id_1 AND t_lines.person_id IS NULL))) -- [2]

, который, как обычно неожиданно, из 2-х планов выбрал худший (PHV 2429571734 — второй по счёту в нижеприведённом сравнении) — с использование комбинации двух BITMAP ROWIDS индексных пребразований, соответствующих 2-м вхождениям/использованиям оператора OR в запросе — в строках [1] и [2]

Сравнение планов:

SQL> @sql_plan_diff am58dqu0y36wr 4214844351 am58dqu0y36wr 2429571734
 
-------------------------------
SQL Plans [by Query block] diff
-------------------------------
 
PLAN_HASH_VALUE QBLOCK_NAME    ID OPERATION                                  QBLOCK_NAME   OBJECT_ALIAS               OBJECT                        CARDINALITY    BYTES COST(IO)      
--------------- ------------ ---- ------------------------------------------ ------------- -------------------------- ----------------------------- ----------- -------- --------------
AWR_4214844351                  0 SELECT STATEMENT                                                                                                                       141675()      
                SEL$1           1  FILTER                                    SEL$1                                                                                       ()            
                SEL$1           2   TABLE ACCESS FULL                        SEL$1         DUAL@SEL$1                 "SYS"."DUAL"                            1        2 2(2)          
                SEL$37633EB5    3   VIEW                                     SEL$37633EB5  from$_subquery$_002@SEL$2                                       1558          141673(141573)
                SEL$37633EB5    4    SORT UNIQUE                             SEL$37633EB5                                                                  1558   364572 141673(141573)
                                5     NESTED LOOPS                                                                                                         8959  2096406 141672(141573)
                                6      NESTED LOOPS                                                                                                       92153  2096406 141672(141573)
                SEL$37633EB5    7       TABLE ACCESS BY INDEX ROWID BATCHED  SEL$37633EB5  T_LINES@SEL$3              "SU"."T_LINES"                      92153  1382295 49460(49420)  
                                8        BITMAP CONVERSION TO ROWIDS                                                                                                     ()            
                                9         BITMAP OR                                                                                                                      ()            
                               10          BITMAP CONVERSION FROM ROWIDS                                                                                                 ()            
                               11           INDEX RANGE SCAN                                                          "SU"."T_LINES_PERSON_ID_IDX"                       344(344)      
                               12          BITMAP CONVERSION FROM ROWIDS                                                                                                 ()            
                               13           INDEX RANGE SCAN                                                          "SU"."T_LINES_CLIENT_ID_IDX"                       47(47)        
                SEL$37633EB5   14       INDEX UNIQUE SCAN                    SEL$37633EB5  T_LINES_1@SEL$3            "SU"."T_LINES_PK"                       1          0(0)          
                SEL$37633EB5   15      TABLE ACCESS BY INDEX ROWID           SEL$37633EB5  T_LINES_1@SEL$3            "SU"."T_LINES"                          1      219 1(1)          
--------------- ------------      ------------------------------------------ ------------- -------------------------- -----------------------------                                    
AWR_2429571734                  0 SELECT STATEMENT                                                                                                                       42094()       
                SEL$1           1  FILTER                                    SEL$1                                                                                       ()            
                SEL$1           2   TABLE ACCESS FULL                        SEL$1         DUAL@SEL$1                 "SYS"."DUAL"                            1        2 2(2)          
                SEL$37633EB5    3   VIEW                                     SEL$37633EB5  from$_subquery$_002@SEL$2                                      14309          42092(41922)  
                SEL$37633EB5    4    SORT UNIQUE                             SEL$37633EB5                                                                 14309  3319688 42092(41922)  
                                5     NESTED LOOPS                                                                                                        14309  3319688 40876(40709)  
                                6      NESTED LOOPS                                                                                                       14309  3319688 40876(40709)  
                SEL$37633EB5    7       TABLE ACCESS BY INDEX ROWID BATCHED  SEL$37633EB5  T_LINES@SEL$3              "SU"."T_LINES"                      14309   214635 26558(26400)  
                                8        BITMAP CONVERSION TO ROWIDS                                                                                                     ()            
                                9         BITMAP AND                                                                                                                     ()            
                               10          BITMAP OR                                                                                                                     ()            
                               11           BITMAP CONVERSION FROM ROWIDS                                                                                                ()            
                               12            INDEX RANGE SCAN                                                         "SU"."I_LINES_SERVICE_ID_IDX"                      23(23)        
                               13           BITMAP CONVERSION FROM ROWIDS                                                                                                ()            
                               14            INDEX RANGE SCAN                                                         "SU"."I_LINES_SERVICE_ID_IDX"                      23(23)        
                               15           BITMAP CONVERSION FROM ROWIDS                                                                                                ()            
                               16            INDEX RANGE SCAN                                                         "SU"."I_LINES_SERVICE_ID_IDX"                      4(4)          
                               17           BITMAP CONVERSION FROM ROWIDS                                                                                                ()            
                               18            INDEX RANGE SCAN                                                         "SU"."I_LINES_SERVICE_ID_IDX"                      4(4)          
                               19          BITMAP OR                                                                                                                     ()            
                               20           BITMAP CONVERSION FROM ROWIDS                                                                                                ()            
                               21            INDEX RANGE SCAN                                                         "SU"."T_LINES_PERSON_ID_IDX"                       2326(2323)    
                               22           BITMAP CONVERSION FROM ROWIDS                                                                                                ()            
                               23            INDEX RANGE SCAN                                                         "SU"."T_LINES_CLIENT_ID_IDX"                       48(48)        
                SEL$37633EB5   24       INDEX UNIQUE SCAN                    SEL$37633EB5  T_LINES_1@SEL$3            "SU"."T_LINES_PK"                       1          0(0)          
                SEL$37633EB5   25      TABLE ACCESS BY INDEX ROWID           SEL$37633EB5  T_LINES_1@SEL$3            "SU"."T_LINES"                          1      217 1(1)          
 
------------------------------
SQL Plan "Notes" sections diff
------------------------------
 
PHV_4214844351                       PHV_2429571734
------------------------------------ -----------------------------------
                                     
   sql_profile:                         sql_profile:
   sql_patch:                           sql_patch:
   baseline:                            baseline:
   outline:                             outline:
   dyn_sampling:    2                   dyn_sampling:    2
   dop:                                 dop:
   dop_reason:                          dop_reason:
   card_feedback:                       card_feedback:
   perf_feedback:                       perf_feedback:
   adaptive_plan:                       adaptive_plan:
   spd_used:        5                   spd_used:        2
   spd_valid:       1                   spd_valid:       5
   gtt_sess_stat:                       gtt_sess_stat:
   db_version:      12.1.0.2            db_version:      12.1.0.2
   plan_hash_full:  2307009094          plan_hash_full:  2602716375
   plan_hash:       4214844351          plan_hash:       2429571734
   plan_hash_2:     2307009094          plan_hash_2:     2602716375
 
--------------------------------
SQL Plan "Outline" sections diff
--------------------------------
 
PHV_4214844351
----------------------------------------------------------------------------------------------------
BITMAP_TREE(@"SEL$37633EB5" "T_LINES"@"SEL$3" OR(2 1 ("T_LINES"."PERSON_ID") 2 ("T_LINES"."CLIENT_ID")))
 
PHV_2429571734
----------------------------------------------------------------------------------------------------
BITMAP_TREE(@"SEL$37633EB5" "T_LINES"@"SEL$3" AND(OR(1 1 ("T_LINES"."SERVICE_ID") 2 ("T_LINES"."SERVICE_ID") 3 ("T_LINES"."SERVICE_ID") 4 ("T_LINES"."SERVICE_ID")) OR(2 1 ("T_LINES"."PERSON_ID") 2 ("T_LINES"."CLIENT_ID"))))

— кроме прочего, указало в последней секции SQL Plan «Outline» sections diff допустимый и используемый синтаксис подсказки BITMAP_TREE, для случая использования нескольких операторов OR в запросе, что позволяет немного расширить/обогатить рукописное определение:

Usage: BITMAP_TREE([<@Block>] <Table> AND(<Index1>[ <Index2> ...]))
    or BITMAP_TREE([<@Block>] <Table> AND((<Index1 columns>)[ (<Index2 columns>) ...]))
    or BITMAP_TREE([<@Block>] <Table>  OR(<OR_Entry1#> <Operand1#> (<Index1 columns>) [<Operand2#> (<Index2 columns>)...]))
    or BITMAP_TREE([<@Block>] <Table> AND(OR(<OR_Entry1#> <Operand1#> (<Index1 columns>) [<Operand2#> (<Index2 columns>)...]) [OR(<OR_Entry2#> <Operand1#> (<Index1 columns>) [<Operand2#> (<Index2 columns>) ...])] ...))
Description: Instructs the optimizer to convert ROWIDs to bitmap, then performance bitmap operations

Плюс ещё одно практическое наблюдение: похоже, подсказка BITMAP_TREE яляется директивной в том смысле, что в рассмотренном случае добавление одной лишь подсказки BITMAP_TREE(@»SEL$37633EB5″ «T_LINES»@»SEL$3» OR(2 1 («T_LINES».»PERSON_ID») 2 («T_LINES».»CLIENT_ID»))) к запросу через SQL Patch стабильно зафиксировало целевой PHV 4214844351, ислючив нежелательный PHV 2429571734, несмотря на то, что определяемые хинтами BITMAP_TREE этих планов BITMAP-операции/комбинации не противоречат друг другу и формально использованный хинт допускает использование обоих планов

Добавить комментарий »

Комментариев нет.

RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Создайте бесплатный сайт или блог на WordPress.com.

%d такие блоггеры, как: