Oracle mechanics

30.01.2016

Подсказка INDEX и bitmap-операции с B-tree индексами

Filed under: heuristics,hints,Oracle — Игорь Усольцев @ 13:01
Tags:

in English

Причиной увеличения DB Time/Elapsed:

WORKLOAD REPOSITORY report for
Elapsed:               30.08 (mins)
DB Time:            1,035.38 (mins)
...

Snap Time           Load    %busy    %user     %sys    %idle  %iowait
--------------- -------- -------- -------- -------- -------- --------
15-Jan 08:00:06     26.7      N/A      N/A      N/A      N/A      N/A
15-Jan 08:30:10     48.4     69.7     65.4      3.2     30.3      6.5 -- *
15-Jan 09:00:13      6.2     33.0     30.2      2.3     67.0      3.5

— с соопутствующим скачком Load Average (*) был хорошо заметный в том же AWR-отчёте запрос:

SQL ordered by Elapsed Time    DB/Inst: ORCL/orcl1  Snaps: 321153-321154
        Elapsed                  Elapsed Time                                   
        Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id    
---------------- -------------- ------------- ------ ------ ------ -------------
        32,145.0         33,505          0.96   51.7   72.6     .0 c9cxc4hy2tbsa -- определённо этот
...
         9,798.9              0           N/A   15.8   45.7   45.9 1mbx3m11g17vn
...
         2,533.0          4,113          0.62    4.1   22.7   22.1 c8cx3cxf286d7

, выполнявшийся в проблемый период попеременно по 2-м разным планам согласно статистике V$SQL/V$SQL_SHARED_CURSOR:

12.1.0.2@ SQL> @shared_cu12_noxml c9cxc4hy2tbsa
 
 EXECS USERS_OPENING LAST_ACTIVE_TIME    ELA_PER_EXEC CPU_PER_EXEC PARSES_PER_EXEC GETS_PER_EXEC READS_PER_EXEC CURSOR_STATUS PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS REOPT REOPT_HINTS ADAPT SPD_Valid SPD_Used  DS_LEVEL OPTIMIZER_STATS  BIND_EQ_FAILURE  ROLL REASON#1                              SQL_PLAN_BASELINE SQL_PATCH SQL_PROFILE
------ ------------- ------------------- ------------ ------------ --------------- ------------- -------------- ------------- --------------- -------------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- ---------------- ---------------- ---- ------------------------------------- ----------------- --------- -----------
197037             0 15.01.2016 05:27:57          754          492               1           528              0 VALID               705575856           3494863008             51     0 Y          N          Y          N                  N                 Y     4         3         2        N                N                N    Rolling Invalidate Window Exceeded(2)
     8             0 15.01.2016 05:36:03      1679082      1235000               3         65988              0 VALID              1305589332           4185007498            246     3 Y          N          N          N                  N                 Y     4         3         2        Y                N                Y    Bind mismatch(25)
     6             0 15.01.2016 07:27:00       127485        56833               3          3261              0 VALID               705575856           3494863008             34     2 Y          N          N          N                  N                 Y     4         3         2        Y                N                Y    Bind mismatch(25)
 36236             0 15.01.2016 07:27:02       636988       500368               1         36375              0 VALID              1305589332           4185007498            295     1 Y          Y          Y          N                  N                 Y     4         3         2        N                Y                Y    Rolling Invalidate Window Exceeded(2)
     1             0 15.01.2016 09:41:34       477813       315000            2352          7043              4 VALID               705575856           3494863008             70     6 Y          Y          N          N                  N                 Y     4         3         2        N                Y                Y    Bind mismatch(33)
    32             0 15.01.2016 10:31:28        12268         8313              15           333              0 VALID               705575856           3494863008            179     4 Y          Y          Y          N                  N                 Y     4         3         2        N                Y                Y    Bind mismatch(33)
 96224            27 15.01.2016 10:31:57       927255       684518               1          1914              0 VALID              1305589332           4185007498            490     5 Y          Y          Y          N                  N                 Y     4         3         2        N                N                Y    Bind mismatch(33)
 
7 rows selected
 
--------------------------------------------------------------
SQL_ID=c9cxc4hy2tbsa Shared Pool statistics by PLAN_HASH_VALUE -- **
--------------------------------------------------------------
 
 EXECS LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC CPU_PER_EXEC PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE AVG_CBO_COST     CHILDS BIND_SENSE BIND_AWARE REOPT ADAPT
------ -------------------- ------------------- ------------ ------------ --------------- -------------------- ------------ ---------- ---------- ---------- ----- -----
132469 2016-01-15/07:27:00  15.01.2016 10:32:01       847893       634173      1305589332           4185007498          344          3 Y          Y          N     Y
197076 2016-01-15/09:41:43  15.01.2016 10:31:28          763          496       705575856           3494863008           84          4 Y          Y          N     Y

— как можно заметить по 2-й части вывода (** — усредненной по PLAN_HASH_VALUE статистике V$SQL) используемые планы имели существенно отличающиеся ср.времена выполнения (ELA_PER_EXEC, в микросекундах) и были сгенерированы под воздействием Bind Aware Cursor Sharing (столбцы BIND_SENSE, BIND_AWARE из V$SQL.IS_BIND_SENSITIVE, V$SQL.IS_BIND_AWARE соответственно) и отдельных 12c Adaptive Features (столбцы ADAPT из V$SQL.IS_RESOLVED_ADAPTIVE_PLAN и SPD_Valid, SPD_Used, DS_LEVEL про SQL Plan Directives и Dynamic Sampling из V$SQL_PLAN.OTHER_XML)

Картина привычная, интересно рассмотреть отличия планов:

SQL> @sql_plan_diff c9cxc4hy2tbsa 1305589332 c9cxc4hy2tbsa 705575856
 
-------------------------------
SQL Plans [by Query block] diff
-------------------------------
 
PLAN_HASH_VALUE   ID OPERATION                                         QBLOCK_NAME   OBJECT_ALIAS     OBJECT                               CARDINALITY COST(IO) TIME
--------------- ---- ------------------------------------------------- ------------- ---------------- ------------------------------------ ----------- -------- ----
SQL_1305589332     0 SELECT STATEMENT                                                                                                                  490()   
                   1  COUNT STOPKEY                                    SEL$E213D7C1                                                                    ()      
                   2    NESTED LOOPS                                                                                                                 1 490(99)     1
                   3      NESTED LOOPS                                                                                                             585 33(33)      1
                   4       TABLE ACCESS BY INDEX ROWID                 SEL$E213D7C1  ORDERSS@SEL$2    "IO"."ORDERSS"                                 1 4(4)        1
                   5        INDEX UNIQUE SCAN                          SEL$E213D7C1  ORDERSS@SEL$2    "IO"."ORDERSS_PK2"                             1 3(3)        1
                   6       PARTITION LIST ALL                                                                                                      585 29(29)      1
                   7        TABLE ACCESS BY LOCAL INDEX ROWID BATCHED  SEL$E213D7C1  PURCHASES@SEL$2  "IO"."PURCHASES"                             585 29(29)      1
                   8         INDEX RANGE SCAN                          SEL$E213D7C1  PURCHASES@SEL$2  "IO"."PURCHASES_IDX_PARENORDERSS_ID"          33 0(0)    
                   9     BITMAP CONVERSION TO ROWIDS                   SEL$E213D7C1  INVOICESS@SEL$3                                                 1 490(99)     1
                  10      BITMAP AND                                                                                                                   ()      
                  11       BITMAP CONVERSION FROM ROWIDS                                                                                               ()      
                  12        INDEX RANGE SCAN                                                          "IO"."INVOICESS_PK"                            1 0(0)    
                  13       BITMAP CONVERSION FROM ROWIDS                                                                                               ()      
                  14        INDEX RANGE SCAN                                                          "IO"."INVOICESS_PAYSYS_ID_IDX"                 1 1(0)        1
---------------      ------------------------------------------------- ------------- ---------------- ------------------------------------                     
SQL_705575856      0 SELECT STATEMENT                                                                                                                  70()    
                   1  COUNT STOPKEY                                    SEL$E213D7C1                                                                    ()      
                   2    NESTED LOOPS                                                                                                                 1 70(70)      1
                   3     NESTED LOOPS                                                                                                               37 70(70)      1
                   4       NESTED LOOPS                                                                                                             37 33(33)      1
                   5        TABLE ACCESS BY INDEX ROWID                SEL$E213D7C1  ORDERSS@SEL$2    "IO"."ORDERSS"                                 1 4(4)        1
                   6         INDEX UNIQUE SCAN                         SEL$E213D7C1  ORDERSS@SEL$2    "IO"."ORDERSS_PK2"                             1 3(3)        1
                   7        PARTITION LIST ALL                                                                                                      37 29(29)      1
                   8         TABLE ACCESS BY LOCAL INDEX ROWID BATCHED SEL$E213D7C1  PURCHASES@SEL$2  "IO"."PURCHASES"                              37 29(29)      1
                   9          INDEX RANGE SCAN                         SEL$E213D7C1  PURCHASES@SEL$2  "IO"."PURCHASES_IDX_PARENORDERSS_ID"          33 0(0)    
                  10      INDEX UNIQUE SCAN                            SEL$E213D7C1  INVOICESS@SEL$3  "IO"."INVOICESS_PK"                            1 0(0)    
                  11     TABLE ACCESS BY INDEX ROWID                   SEL$E213D7C1  INVOICESS@SEL$3  "IO"."INVOICESS"                               1 1(1)        1
 
------------------------------
SQL Plan "Notes" sections diff
------------------------------
 
PHV_1305589332                  PHV_705575856
------------------------------- ------------------------------
                                
   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:   yes            adaptive_plan:   yes
   spd_used:        3              spd_used:        3
   spd_valid:       4              spd_valid:       4
   gtt_sess_stat:                  gtt_sess_stat:
   db_version:      12.1.0.2       db_version:      12.1.0.2
   plan_hash_full:  4185007498     plan_hash_full:  3494863008
   plan_hash:       1305589332     plan_hash:       705575856
   plan_hash_2:     3143080312     plan_hash_2:     904061134
 
--------------------------------
SQL Plan "Outline" sections diff
--------------------------------
 
PHV_1305589332                                                                                       PHV_705575856
---------------------------------------------------------------------------------------------------- -------------------------------------------------------------
BITMAP_TREE(@"SEL$E213D7C1" "INVOICESS"@"SEL$3" AND(("INVOICESS"."ID") ("INVOICESS"."PAYSYS_ID")))   
                                                                                                     INDEX(@"SEL$E213D7C1" "INVOICESS"@"SEL$3" ("INVOICESS"."ID"))
                                                                                                     NLJ_BATCHING(@"SEL$E213D7C1" "INVOICESS"@"SEL$3")

— эти два плана отличались методом доступа к таблице INVOICESS: «медленный» PHV_1305589332 использовал INDEX JOIN BITMAP-типа с обычными (B-tree) индексами (отражённом хинтом BITMAP_TREE в Outline секции плана), в то время как «быстрый» план PHV_705575856 использовал TABLE ACCESS BY INDEX ROWID с доступом по одному индексу (через сооветстующий хинт INDEX там же)

Для скорой стабилизации «быстрого» плана имеется по крайней мере несколько возможностей:

1) зафиксировать план с помощью SQL Plan Baseline / SQL Profile — наиболее жёсткий и стабильный метод

2) отключить 12c Adaptive Features / Bind Aware Cursor Sharing для исключения причин генерации доп.планов с помощью подсказок OPT_PARAM(‘optimizer_adaptive_features’ ‘false’) и NO_BIND_AWARE в тексте запроса, либо через SQL Patch — достаточно эффективный метод, но гарантировать стабильный выбор «быстрого» плана таким образом трудно — тут как карта ляжет

3) частичная фиксация «быстрого» плана для sql_id через фиксацию быстрого метода доступа к таблице INVOICESS (в данном случае TABLE ACCESS BY INDEX ROWID) — достаточно мягкий метод, оставляющий все 12c Adaptive Features в действии

Для реализации последнего 3-го подхода нужно:

1) запретить BITMAP_TREE операции
и/или
2) каким-то образом форсировать использование TABLE ACCESS BY INDEX ROWID в качестве метода доступа к таблице INVOICESS

Посказка BITMAP_TREE:

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

, а также близкая по функционалу подсказка старого поколения INDEX_COMBINE по определению (V$SQL_HINT),к сожалению, не имеют прямых подсказок обратного действия типа INVERSE:

SQL> @hint BITMAP_TREE
 
NAME           SQL_FEATURE          CLASS   INVERSE  TLVL PROP VERSION   VERSION_OUTLINE
-------------- -------------------- ------- -------- ---- ---- --------- ----------------
BITMAP_TREE    QKSFM_BITMAP_TREE    ACCESS              4  304 10.2.0.1  10.2.0.1
 
SQL> @hint INDEX_COMBINE
 
NAME           SQL_FEATURE          CLASS   INVERSE  TLVL PROP VERSION   VERSION_OUTLINE
-------------- -------------------- ------- -------- ---- ---- --------- ----------------
INDEX_COMBINE  QKSFM_INDEX_COMBINE  ACCESS              4  432 8.1.0     8.1.7

Однако секция отличия SQL Plan Outline-ов (из предыдущего вывода скрипта SQL_PLAN_DIFF.SQL) настолько проста и привлекательна в этом случае, что позволяет добиться желаемого TABLE ACCESS BY INDEX ROWID одним хинтом INDEX, а заодно проверить, не является ли этот хинт протиядием (INVERSE) против подсказок BITMAP_TREE / INDEX_COMBINE, пробуем

С подсказками BITMAP_TREE или INDEX_COMBINE ожидаемо генерируется «плохой» план (Plan hash value: 431499101):

SQL> explain plan for
  2  SELECT --+ BITMAP_TREE(@"SEL$E213D7C1" "INVOICESS"@"SEL$3" AND(("INVOICESS"."ID") ("INVOICESS"."PAYSYS_ID")))
...

SQL> select * from table(dbms_xplan.display('','','-predicate +outline'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 431499101

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                               |     1 |   631 |   114  (16)| 00:00:01 |       |       |
|*  1 |  COUNT STOPKEY                                 |                               |       |       |            |          |       |       |
|   2 |   NESTED LOOPS                                 |                               |     1 |   631 |   114  (16)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                                |                               |   860K|   631 |   114  (16)| 00:00:01 |       |       |
|   4 |     NESTED LOOPS                               |                               |     2 |   798 |    32   (0)| 00:00:01 |       |       |
|   5 |      TABLE ACCESS BY INDEX ROWID               | ORDERSS                       |     1 |   294 |     4   (0)| 00:00:01 |       |       |
|*  6 |       INDEX UNIQUE SCAN                        | ORDERSS_PK2                   |     1 |       |     3   (0)| 00:00:01 |       |       |
|   7 |      PARTITION LIST ALL                        |                               |     2 |   210 |    28   (0)| 00:00:01 |     1 |     2 |
|*  8 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PURCHASES                     |     2 |   210 |    28   (0)| 00:00:01 |     1 |     2 |
|*  9 |        INDEX RANGE SCAN                        | PURCHASES_IDX_PARENORDERSS_ID |    33 |       |     0   (0)| 00:00:01 |     1 |     2 |
|  10 |     BITMAP CONVERSION TO ROWIDS                |                               |       |       |            |          |       |       |
|  11 |      BITMAP AND                                |                               |       |       |            |          |       |       |
|  12 |       BITMAP CONVERSION FROM ROWIDS            |                               |       |       |            |          |       |       |
|* 13 |        INDEX RANGE SCAN                        | INVOICESS_PK                  |   430K|       |     0   (0)| 00:00:01 |       |       |
|  14 |       BITMAP CONVERSION FROM ROWIDS            |                               |       |       |            |          |       |       |
|* 15 |        INDEX RANGE SCAN                        | INVOICESS_PAYSYS_ID_IDX       |   430K|       |     7 (100)| 00:00:01 |       |       |
|  16 |    TABLE ACCESS BY INDEX ROWID                 | INVOICESS                     |     1 |   232 |   114  (16)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      ...
      BITMAP_TREE(@"SEL$E213D7C1" "INVOICESS"@"SEL$3" AND(("INVOICESS"."ID") ("INVOICESS"."PAYSYS_ID")))
      ...
      END_OUTLINE_DATA
  */

SQL> explain plan for
  2  SELECT --+ INDEX_COMBINE(@"SEL$E213D7C1" "INVOICESS"@"SEL$3" INVOICESS_PK INVOICESS_PAYSYS_ID_IDX)
...

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 431499101

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                               |     1 |   631 |   114  (16)| 00:00:01 |       |       |
|*  1 |  COUNT STOPKEY                                 |                               |       |       |            |          |       |       |
|   2 |   NESTED LOOPS                                 |                               |     1 |   631 |   114  (16)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                                |                               |   860K|   631 |   114  (16)| 00:00:01 |       |       |
|   4 |     NESTED LOOPS                               |                               |     2 |   798 |    32   (0)| 00:00:01 |       |       |
|   5 |      TABLE ACCESS BY INDEX ROWID               | ORDERSS                       |     1 |   294 |     4   (0)| 00:00:01 |       |       |
|*  6 |       INDEX UNIQUE SCAN                        | ORDERSS_PK2                   |     1 |       |     3   (0)| 00:00:01 |       |       |
|   7 |      PARTITION LIST ALL                        |                               |     2 |   210 |    28   (0)| 00:00:01 |     1 |     2 |
|*  8 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PURCHASES                     |     2 |   210 |    28   (0)| 00:00:01 |     1 |     2 |
|*  9 |        INDEX RANGE SCAN                        | PURCHASES_IDX_PARENORDERSS_ID |    33 |       |     0   (0)| 00:00:01 |     1 |     2 |
|  10 |     BITMAP CONVERSION TO ROWIDS                |                               |       |       |            |          |       |       |
|  11 |      BITMAP AND                                |                               |       |       |            |          |       |       |
|  12 |       BITMAP CONVERSION FROM ROWIDS            |                               |       |       |            |          |       |       |
|* 13 |        INDEX RANGE SCAN                        | INVOICESS_PK                  |   430K|       |     0   (0)| 00:00:01 |       |       |
|  14 |       BITMAP CONVERSION FROM ROWIDS            |                               |       |       |            |          |       |       |
|* 15 |        INDEX RANGE SCAN                        | INVOICESS_PAYSYS_ID_IDX       |   430K|       |     7 (100)| 00:00:01 |       |       |
|  16 |    TABLE ACCESS BY INDEX ROWID                 | INVOICESS                     |     1 |   232 |   114  (16)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      ...
      BITMAP_TREE(@"SEL$E213D7C1" "INVOICESS"@"SEL$3" AND(("INVOICESS"."ID") ("INVOICESS"."PAYSYS_ID")))
      ...
      END_OUTLINE_DATA
  */

— два абсолютно эквивалентных плана, в процессе компиляции которых хинты INDEX_COMBINE и BITMAP_TREE из текста запроса преобразуются в один и тот же BITMAP_TREE секции Outline Data

Но если после BITMAP_TREE/INDEX_COMBINE добавить подсказку INDEX (всё из того же вывода SQL_PLAN_DIFF.SQL):

SQL> explain plan for
  2  SELECT --+ BITMAP_TREE(@"SEL$E213D7C1" "INVOICESS"@"SEL$3" AND(("INVOICESS"."ID") ("INVOICESS"."PAYSYS_ID"))) INDEX(@"SEL$E213D7C1" "INVOICESS"@"SEL$3" ("INVOICESS"."ID"))
...

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 705575856

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                               |     1 |   631 |    34   (0)| 00:00:01 |       |       |
|*  1 |  COUNT STOPKEY                                 |                               |       |       |            |          |       |       |
|   2 |   NESTED LOOPS                                 |                               |     1 |   631 |    34   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                                |                               |     2 |   631 |    34   (0)| 00:00:01 |       |       |
|   4 |     NESTED LOOPS                               |                               |     2 |   798 |    32   (0)| 00:00:01 |       |       |
|   5 |      TABLE ACCESS BY INDEX ROWID               | ORDERSS                       |     1 |   294 |     4   (0)| 00:00:01 |       |       |
|*  6 |       INDEX UNIQUE SCAN                        | ORDERSS_PK2                   |     1 |       |     3   (0)| 00:00:01 |       |       |
|   7 |      PARTITION LIST ALL                        |                               |     2 |   210 |    28   (0)| 00:00:01 |     1 |     2 |
|*  8 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PURCHASES                     |     2 |   210 |    28   (0)| 00:00:01 |     1 |     2 |
|*  9 |        INDEX RANGE SCAN                        | PURCHASES_IDX_PARENORDERSS_ID |    33 |       |     0   (0)| 00:00:01 |     1 |     2 |
|* 10 |     INDEX UNIQUE SCAN                          | INVOICESS_PK                  |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 11 |    TABLE ACCESS BY INDEX ROWID                 | INVOICESS                     |     1 |   232 |     1   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      ...
      INDEX(@"SEL$E213D7C1" "INVOICESS"@"SEL$3" ("INVOICESS"."ID"))
      ...
      END_OUTLINE_DATA
  */

SQL> explain plan for
  2  SELECT --+ INDEX_COMBINE(@"SEL$E213D7C1" "INVOICESS"@"SEL$3" INVOICESS_PK INVOICESS_PAYSYS_ID_IDX) INDEX(@"SEL$E213D7C1" "INVOICESS"@"SEL$3" ("INVOICESS"."ID"))
...

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 705575856

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                               |     1 |   631 |    34   (0)| 00:00:01 |       |       |
|*  1 |  COUNT STOPKEY                                 |                               |       |       |            |          |       |       |
|   2 |   NESTED LOOPS                                 |                               |     1 |   631 |    34   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                                |                               |     2 |   631 |    34   (0)| 00:00:01 |       |       |
|   4 |     NESTED LOOPS                               |                               |     2 |   798 |    32   (0)| 00:00:01 |       |       |
|   5 |      TABLE ACCESS BY INDEX ROWID               | ORDERSS                       |     1 |   294 |     4   (0)| 00:00:01 |       |       |
|*  6 |       INDEX UNIQUE SCAN                        | ORDERSS_PK2                   |     1 |       |     3   (0)| 00:00:01 |       |       |
|   7 |      PARTITION LIST ALL                        |                               |     2 |   210 |    28   (0)| 00:00:01 |     1 |     2 |
|*  8 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PURCHASES                     |     2 |   210 |    28   (0)| 00:00:01 |     1 |     2 |
|*  9 |        INDEX RANGE SCAN                        | PURCHASES_IDX_PARENORDERSS_ID |    33 |       |     0   (0)| 00:00:01 |     1 |     2 |
|* 10 |     INDEX UNIQUE SCAN                          | INVOICESS_PK                  |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 11 |    TABLE ACCESS BY INDEX ROWID                 | INVOICESS                     |     1 |   232 |     1   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      ...
      INDEX(@"SEL$E213D7C1" "INVOICESS"@"SEL$3" ("INVOICESS"."ID"))
      ...
      END_OUTLINE_DATA
  */

— Oracle генерирует «быстрый» план (Plan hash value: 705575856) с желаемой операцией TABLE ACCESS BY INDEX ROWID для таблицы INVOICESS

Так происходит не от того, что подсказка INDEX имеет большее влияние на оптимизатор (может быть, к сожалению), просто эти хинты для этого запроса находятся в состоянии неразрешимого противоречия (конфликтуют), что видно из 10053 трейса:

Dumping Hints
=============
  atom_hint=(@=0x1628ecd568 err=0 resol=1 used=0 token=1093 org=1 lvl=3 txt=BITMAP_TREE ("INVOICESS" AND ("INVOICESS_PK" "INVOICESS_PAYSYS_ID_IDX")) )
  atom_hint=(@=0x1628ecd1e8 err=0 resol=1 used=0 token=83 org=1 lvl=3 txt=INDEX ("INVOICESS" "INVOICESS_PK") )
  atom_hint=(@=0x162ab398e8 err=4 resol=1 used=0 token=83 org=1 lvl=3 txt=INDEX ("INVOICESS" "INVOICESS_PK") )
  atom_hint=(@=0x155e4310a0 err=4 resol=1 used=0 token=1093 org=1 lvl=3 txt=BITMAP_TREE ("INVOICESS" AND ("INVOICESS_PK" "INVOICESS_PAYSYS_ID_IDX")) )
********** WARNING: SOME HINTS HAVE ERRORS *********

— т.е. подсказка INDEX несовместима (INVERSE) с операциями/хинтами BITMAP_TREE/INDEX_COMBINE

В ре-тате такого конфликта Oracle эти подсказки игнорирует и строит план по стандартным правилам CBO, как для запроса без хинтов:

SQL> explain plan for
  2  SELECT
  3         *
...
 15  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 705575856

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                               |     1 |   631 |    34   (0)| 00:00:01 |       |       |
|*  1 |  COUNT STOPKEY                                 |                               |       |       |            |          |       |       |
|   2 |   NESTED LOOPS                                 |                               |     1 |   631 |    34   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                                |                               |     2 |   631 |    34   (0)| 00:00:01 |       |       |
|   4 |     NESTED LOOPS                               |                               |     2 |   798 |    32   (0)| 00:00:01 |       |       |
|   5 |      TABLE ACCESS BY INDEX ROWID               | ORDERSS                       |     1 |   294 |     4   (0)| 00:00:01 |       |       |
|*  6 |       INDEX UNIQUE SCAN                        | ORDERSS_PK2                   |     1 |       |     3   (0)| 00:00:01 |       |       |
|   7 |      PARTITION LIST ALL                        |                               |     2 |   210 |    28   (0)| 00:00:01 |     1 |     2 |
|*  8 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PURCHASES                     |     2 |   210 |    28   (0)| 00:00:01 |     1 |     2 |
|*  9 |        INDEX RANGE SCAN                        | PURCHASES_IDX_PARENT_ORDER_ID |    33 |       |     0   (0)| 00:00:01 |     1 |     2 |
|* 10 |     INDEX UNIQUE SCAN                          | INVOICESS_PK                  |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 11 |    TABLE ACCESS BY INDEX ROWID                 | INVOICESS                     |     1 |   232 |     1   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      ...
      INDEX(@"SEL$E213D7C1" "INVOICESS"@"SEL$3" ("INVOICESS"."ID"))
      ...
      END_OUTLINE_DATA
  */

Это наблюдение интересно по следующим причинам:

1) Согласно документации Oracle The INDEX hint instructs the optimizer to use an index scan for the specified table, однако в нашем случае оба плана использовали an index scan для доступа к INVOICESS: «медленный» план использовал INDEX RANGE SCAN, а «быстрый» план — INDEX UNIQUE SCAN, другое дело как в планах использовались рез-ты индексных операций.

Возможно, более корректным определением было бы The INDEX hint instructs the optimizer to use an index scan for direct access to the specified table data or followed by TABLE ACCESS BY INDEX ROWID operation for the specified table?

2) В рассматриваемом случае подсказки INDEX и BITMAP_TREE/INDEX_COMBINE действуют как обратные/INVERSE друг другу, занимая при этом совпадающие позиции в feature/module иерархии хинтов оптимизатора (скрипт hinth.sql by Tanel Poder):

SQL> @hinth INDEX
Display Hint feature hierarchy for hints like INDEX
 
NAME                      HINTH_PATH
------------------------- ---------------------------------------------------------
INDEX                     ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX
 
SQL> @hinth BITMAP_TREE
Display Hint feature hierarchy for hints like BITMAP_TREE
 
NAME                      HINTH_PATH
------------------------- ---------------------------------------------------------
BITMAP_TREE               ALL -> COMPILATION -> CBO -> ACCESS_PATH -> BITMAP_TREE
 
SQL> @hinth INDEX_COMBINE
Display Hint feature hierarchy for hints like INDEX_COMBINE
 
NAME                      HINTH_PATH
------------------------- ---------------------------------------------------------
INDEX_COMBINE             ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_COMBINE

— т.о. иерархическое положение можно использовать в дальнейшем для быстого поиска INVERSE хинтов, как вариант

В итоге, сделал простой SQL Patch:

SQL> @sqlpatch+
&SQL_ID: c9cxc4hy2tbsa
&SQL_PATCH_TEXT: INDEX(@"SEL$E213D7C1" "INVOICESS"@"SEL$3" ("INVOICESS"."ID"))
&SQL_PATCH_NAME: c9cxc4hy2tbsa_INDEX
 
PL/SQL procedure successfully completed
 
SQL_PATCH_HINTS
-------------------------------------------------------------
INDEX(@"SEL$E213D7C1" "INVOICESS"@"SEL$3" ("INVOICESS"."ID"))

— который не только форсирует быстрый TABLE ACCESS BY INDEX ROWID, но и блокирует использование медленных BITMAP_TREE операций

Согласно 10053 тпейсу SQL Patch действует в точности как если бы подсказка /*+ INDEX(…)*/ располагалась в тексте запроса:

Index Stats::
  Index: INVOICESS_PK  Col#: 1
  LVLS: 3  #LB: 586225  #DK: 46021952  LB/K: 1.00  DB/K: 1.00  CLUF: 5585233.00  NRW: 46021952.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 1 BSZ: 1
  KKEISFLG: 1
    User hint to use this index -- верный признак

И через некоторое время можно видеть рез-т:

SQL> @shared_cu12_noxml c9cxc4hy2tbsa
 
 EXECS USERS_OPENING LAST_ACTIVE_TIME    ELA_PER_EXEC CPU_PER_EXEC CURSOR_STATUS   PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS REOPT REOPT_HINTS ADAPT SPD_Valid SPD_Used  DS_LEVEL OPTIMIZER_STATS  BIND_EQ_FAILURE  ROLL REASON#1               SQL_PLAN_BASELINE SQL_PATCH           SQL_PROFILE
------ ------------- ------------------- ------------ ------------ --------------- --------------- -------------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- ---------------- ---------------- ---- ---------------------- ----------------- ------------------- -----------
     2             0 16.01.2016 19:25:28       130868        92000 INVALID_UNAUTH        705575856            904061134           1206    19 Y          N          N          N                  N                       4         3         2        Y                N                Y    Bind mismatch(25)                        c9cxc4hy2tbsa_INDEX
   101             0 16.01.2016 19:36:12         7062         3416 INVALID_UNAUTH        705575856            904061134             70    20 Y          Y          N          N                  N                       4         3         2        N                N                Y    Bind mismatch(33)                        c9cxc4hy2tbsa_INDEX
    18             0 17.01.2016 00:36:11        18127        12500 INVALID_UNAUTH        705575856            904061134            620    23 Y          N          N          N                  N                       4         3         2        Y                N                Y    Bind mismatch(25)                        c9cxc4hy2tbsa_INDEX
 36708             0 17.01.2016 02:54:51         1618         1029 INVALID_UNAUTH        705575856            904061134             52    24 Y          Y          N          N                  N                       4         3         2        N                N                Y    Bind mismatch(33)                        c9cxc4hy2tbsa_INDEX
    18             0 18.01.2016 04:15:53       197566        11333 INVALID_UNAUTH        705575856            904061134           4730     1 Y          Y          N          Y                  Y               4       4         3         2        N                N                N    Optimizer mismatch(13)                   c9cxc4hy2tbsa_INDEX
208444             0 18.01.2016 08:40:09          805          540 INVALID_UNAUTH        705575856            904061134             35     0 Y          Y          N          N                  N                       4         3         2        N                Y                N    Bind mismatch(33)                        c9cxc4hy2tbsa_INDEX
     1             0 18.01.2016 12:08:03       220584       176000 VALID                 705575856            904061134            621     2 Y          Y          N          Y                  Y               3       4         3         2        N                Y                N    Optimizer mismatch(13)                   c9cxc4hy2tbsa_INDEX
  3701             0 18.01.2016 12:46:30         4098         1279 VALID                 705575856            904061134            621     3 Y          Y          N          N                  N                       4         3         2        N                Y                N    Bind mismatch(33)                        c9cxc4hy2tbsa_INDEX
  2388            27 18.01.2016 13:04:06         2198         1521 VALID                 705575856            904061134            107     4 Y          Y          Y          N                  N                       4         3         2        N                Y                N    Bind mismatch(33)                        c9cxc4hy2tbsa_INDEX
 
9 rows selected
 
--------------------------------------------------------------
SQL_ID=c9cxc4hy2tbsa Shared Pool statistics by PLAN_HASH_VALUE
--------------------------------------------------------------
 
 EXECS LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC CPU_PER_EXEC PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE AVG_CBO_COST CHILDS BIND_SENSE BIND_AWARE REOPT ADAPT SQL_PLAN_BASELINE SQL_PATCH           SQL_PROFILE
------ -------------------- ------------------- ------------ ------------ --------------- -------------------- ------------ ------ ---------- ---------- ----- ----- ----------------- ------------------- -----------
251381 2016-01-18/12:40:14  18.01.2016 13:04:06         1005          636       705575856            904061134          896      9 Y          Y          Y     N                       c9cxc4hy2tbsa_INDEX            

— операция TABLE ACCESS BY INDEX ROWID вместе с планом в этом случае оказались стабилизированы патчем (SQL Patch c9cxc4hy2tbsa_INDEX), в то же время все Bind-Aware и 12c adaptive features остались на месте (см. BIND_SENSE, BIND_AWARE, USE_FEEDBACK_STATS, REOPT, REOPT_HINTS, SPD_Valid, SPD_Used, DS_LEVEL) и пытаются работать; есть единичные медленные выполнения в сотни миллисекунд, но среднее ELA_PER_EXEC ~ 1 мс, что важнее для OLTP

P.S. Попутно поэкспериментировал с чудесным комментарием BEGIN_OUTLINE_DATA, проверив конструкцию /*+ INDEX(…) BEGIN_OUTLINE_DATA BITMAP_TREE(…)*/:

SQL> explain plan for
  2  SELECT --+ INDEX(@"SEL$E213D7C1" "INVOICESS"@"SEL$3" ("INVOICESS"."ID")) BEGIN_OUTLINE_DATA BITMAP_TREE(@"SEL$E213D7C1" "INVOICESS"@"SEL$3" AND(("INVOICESS"."ID") ("INVOICESS"."PAYSYS_ID")))
...

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 909801170

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                               |     1 |   632 |   120  (15)| 00:00:01 |       |       |
|   1 |  COUNT STOPKEY                        |                               |       |       |         |             |       |       |
|   2 |   NESTED LOOPS                        |                               |     1 |   632 |   120  (15)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                       |                               |     2 |   798 |    34   (0)| 00:00:01 |       |       |
|   4 |     TABLE ACCESS BY INDEX ROWID       | ORDERSS                       |     1 |   294 |     4   (0)| 00:00:01 |       |       |
|   5 |      INDEX UNIQUE SCAN                | ORDERSS_PK2                   |     1 |       |     3   (0)| 00:00:01 |       |       |
|   6 |     PARTITION LIST ALL                |                               |     2 |   210 |    30   (0)| 00:00:01 |     1 |     2 |
|   7 |      TABLE ACCESS BY LOCAL INDEX ROWID| PURCHASES                     |     2 |   210 |    30   (0)| 00:00:01 |     1 |     2 |
|   8 |       INDEX RANGE SCAN                | PURCHASES_IDX_PARENT_ORDER_ID |    33 |       |     0   (0)| 00:00:01 |     1 |     2 |
|   9 |    TABLE ACCESS BY INDEX ROWID        | INVOICESS                     |     1 |   233 |   120  (15)| 00:00:01 |       |       |
|  10 |     BITMAP CONVERSION TO ROWIDS       |                               |       |       |         |             |       |       |
|  11 |      BITMAP AND                       |                               |       |       |         |             |       |       |
|  12 |       BITMAP CONVERSION FROM ROWIDS   |                               |       |       |         |             |       |       |
|  13 |        INDEX RANGE SCAN               | INVOICESS_PK                  |   428K|       |     0   (0)| 00:00:01 |       |       |
|  14 |       BITMAP CONVERSION FROM ROWIDS   |                               |       |       |         |             |       |       |
|  15 |        INDEX RANGE SCAN               | INVOICESS_PAYSYS_ID_IDX       |   428K|       |     7 (100)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      ...
      BITMAP_TREE(@"SEL$E213D7C1" "INVOICESS"@"SEL$3" AND(("INVOICESS"."ID") ("INVOICESS"."PAYSYS_ID")))
      ...
      END_OUTLINE_DATA
  */

— Oracle выбирает план, очень близкий (не эквивалентный) к «плохому», с использованием тех же bitmap-операций с B-tree индексами

Похоже, что подсказки BITMAP_TREE/INDEX_COMBINE (возможно, и не только они), следующие за BEGIN_OUTLINE_DATA попадает напрямую в мозг в SQL Plan Outline, отменяя действие противоположной/INVERSE подсказки INDEX, которую можно обнаружить преобразованной к «классическому» виду в статусе unused в трейсе:

Dumping Hints
=============
  atom_hint=(@=0x26d4212dd8 err=0 resol=1 used=0 token=83 org=1 lvl=3 txt=INDEX ("INVOICESS" "INVOICESS_PK") )
  atom_hint=(@=0x26d6469860 err=5 resol=1 used=0 token=83 org=1 lvl=3 txt=INDEX ("INVOICESS" "INVOICESS_PK") )
********** WARNING: SOME HINTS HAVE ERRORS *********

P.P.S. В некоторых приложениях(OEBS) использование bitmap-операций с B-tree индексами отключается на системном уровне параметром:

SQL> @param_ _b_tree_bitmap_plans
 
NAME                  VALUE  IS_DEF   DSC
--------------------- ------ -------- ----------------------------------------------------------------
_b_tree_bitmap_plans  FALSE  FALSE    enable the use of bitmap plans for tables w. only B-tree indexes

в соответствии с Database Initialization Parameters for Oracle E-Business Suite Release 12 (Doc ID 396009.1), изменение этого параметра так же работает и на уровне отдельного запроса, отменяя действие любой из подсказок BITMAP_TREE/INDEX_COMBINE:

SQL> explain plan for
  2  SELECT --+ INDEX_COMBINE(@"SEL$E213D7C1" "INVOICESS"@"SEL$3" INVOICESS_PK INVOICESS_PAYSYS_ID_IDX) opt_param('_b_tree_bitmap_plans' 'false')
...

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 705575856 -- "fast plan"

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                               |     1 |   632 |    36   (0)| 00:00:01 |       |       |
|   1 |  COUNT STOPKEY                                 |                               |       |       |            |          |       |       |
|   2 |   NESTED LOOPS                                 |                               |     1 |   632 |    36   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                                |                               |     2 |   632 |    36   (0)| 00:00:01 |       |       |
|   4 |     NESTED LOOPS                               |                               |     2 |   798 |    34   (0)| 00:00:01 |       |       |
|   5 |      TABLE ACCESS BY INDEX ROWID               | ORDERSS                       |     1 |   294 |     4   (0)| 00:00:01 |       |       |
|   6 |       INDEX UNIQUE SCAN                        | ORDERSS_PK2                   |     1 |       |     3   (0)| 00:00:01 |       |       |
|   7 |      PARTITION LIST ALL                        |                               |     2 |   210 |    30   (0)| 00:00:01 |     1 |     2 |
|   8 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PURCHASES                     |     2 |   210 |    30   (0)| 00:00:01 |     1 |     2 |
|   9 |        INDEX RANGE SCAN                        | PURCHASES_IDX_PARENT_ORDER_ID |    33 |       |     0   (0)| 00:00:01 |     1 |     2 |
|  10 |     INDEX UNIQUE SCAN                          | INVOICESS_PK                  |     1 |       |     0   (0)| 00:00:01 |       |       |
|  11 |    TABLE ACCESS BY INDEX ROWID                 | INVOICESS                     |     1 |   233 |     1   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      ...
      INDEX(@"SEL$E213D7C1" "INVOICESS"@"SEL$3" ("INVOICESS"."ID"))
      ...
      OPT_PARAM('_b_tree_bitmap_plans' 'false')
      ...
      END_OUTLINE_DATA
  */

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

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

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 такие блоггеры, как: