Oracle mechanics

Igor Usoltsev's Oracle blog

INDEX as inverse hint for BITMAP_TREE/INDEX_COMBINE

Posted by Игорь Усольцев on January 30, 2016

Suddenly the DB Time/Elapsed ratio bounced:

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

– causing significant Load [Average] rising (*) accordingly

The problem was caused by one problem sql_id, from the same 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 -- obviously this
...
         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

According to V$SQL/V$SQL_SHARED_CURSOR statistics query with sql_id = c9cxc4hy2tbsa used two execution plans with quite different average elapsed time (column ELA_PER_EXEC, in microseconds) for the problem period:

12.1.0.2@ SQL> @shared_cu12_noxml c9cxc4hy2tbsa
 
INST        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
---- ------------ ------------- ------------------- ------------ ------------ --------------- ------------- -------------- ------------- --------------- -------------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- ---------------- ---------------- ---- ------------------------------------- ----------------- --------- -----------
   1       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)
   1            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)
   1            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)
   1        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            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)
   1           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)
   1        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
--------------------------------------------------------------
 
INST        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
---- ------------ -------------------- ------------------- ------------ ------------ --------------- -------------------- ------------ ---------- ---------- ---------- ----- -----
   1       132469 2016-01-15/07:27:00  15.01.2016 10:32:01       847893       634173      1305589332           4185007498          344          3 Y          Y          N     Y
   1       197076 2016-01-15/09:41:43  15.01.2016 10:31:28          763          496       705575856           3494863008           84          4 Y          Y          N     Y

– these two plans were generated by influence of Bind Aware Cursor Sharing (according to columns BIND_SENSE, BIND_AWARE from V$SQL.IS_BIND_SENSITIVE, V$SQL.IS_BIND_AWARE), and 12c Adaptive Features (cols ADAPT (V$SQL.IS_RESOLVED_ADAPTIVE_PLAN) and SPD_Valid, SPD_Used, DS_LEVEL about SQL Plan Directives and Dynamic Sampling from V$SQL_PLAN.OTHER_XML)

Described effects are well known, but the concurrent execution plans comparison shows some interesting things to notice:

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")

– these two plans mainly differ by access method for INVOICESS table: the “slow plan” (PHV_1305589332) uses BITMAP INDEX JOIN operation (forced by BITMAP_TREE hint in SQL Plan “Outline” section), while “fast plan” (PHV_705575856) uses indexed TABLE ACCESS BY INDEX ROWID method (over INDEX hint) for the same table

Oracle provides some ways to stabilize the execution plan in this case:

1) fix “fast plan” using SQL Plan Baseline or SQL Profile, which may be the most tough and stable method

2) turn off 12c Adaptive Features and/or Bind Aware Cursor Sharing (to exclude the additional plan generation reason) for the problem sql_id by using hints opt_param(‘optimizer_adaptive_features’ ‘false’) and NO_BIND_AWARE, for example, or making SQL Patch which these – this method is quite effective but can not guarantee stability
3) partially fix “fast plan” for sql_id by forcing “fast” access method for INVOICESS table – enough soft method, which keeps all 12c Adaptive Features in situ

So if I chose the last (3) variant for problem resolving, then I have two opportunities:

1) disable BITMAP_TREE operation
or/and
2) force “fast” TABLE ACCESS BY INDEX ROWID method for access INVOICESS

The BITMAP_TREE hint:

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

and closely related old fashioned INDEX_COMBINE hint definitions (from V$SQL_HINT) does not show any available INVERSE hints:

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

So we can use the SQL Plan Outline sections diff data (from above SQL_PLAN_DIFF.SQL output) to force “fast” indexed TABLE ACCESS BY INDEX ROWID operation for INVOICESS table, and (maybe?) to find the appropriate INVERSE hint for BITMAP_TREE / INDEX_COMBINE

With BITMAP_TREE or INDEX_COMBINE hints the same “bad plan” (Plan hash value: 431499101 in this case) was generated:

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
  */

– with equivalent Outline Data section, where both INDEX_COMBINE and BITMAP_TREE hints from query text were transformed into the same BITMAP_TREE hint in SQL Plan Outline

But if I add the INDEX hint (from the same SQL_PLAN_DIFF.SQL output) in addition to BITMAP_TREE/INDEX_COMBINE hint:

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 generates “fast plan” (Plan hash value: 705575856) with desired TABLE ACCESS BY INDEX ROWID for INVOICESS table

According to 10053 trace this happens because the hints conflicts:

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 *********

– in other words the INDEX hint acts as inverse/opposite for BITMAP_TREE/INDEX_COMBINE hints in this case

As a result the user hints destroys each other, and Oracle builds default execution plan (without taking user hints into account at all):

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
  */

This seems very interesting by next reasons:

1) According Oracle documentation The INDEX hint instructs the optimizer to use an index scan for the specified table, but in this case index scan for INVOICESS table was used in both plans: INDEX RANGE SCAN in a “bad plan”, and INDEX UNIQUE SCAN in “fast plan”
So maybe more correct definition should looks like “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) For this case the INDEX and BITMAP_TREE/INDEX_COMBINE hints acts as INVERSE to each other, holding the exact matching position in feature/module hierarchy(acorrding to Tanel Poder script):

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

– which can help to find unknown INVERSE hints in other circumstances

Eventually, I’ve made SQL Patch with the INDEX hint:

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"))

– which not only forces the “fast” TABLE ACCESS BY INDEX ROWID method but disables/forbids the BITMAP_TREE operation

This SQL Patch acts for query exactly as user /*+ INDEX(…)*/ hint in a query text acording to 10053 trace:

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 -- a sign is here

After some time I got the desired result:

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            

– stable enough (always using indexed TABLE ACCESS BY INDEX ROWID) execution plan (fixed with soft SQL Patch c9cxc4hy2tbsa_INDEX) with all Bind-Aware and 12c adaptive features in place (cols BIND_SENSE, BIND_AWARE, USE_FEEDBACK_STATS, REOPT, REOPT_HINTS, SPD_Valid, SPD_Used, DS_LEVEL), and quite tolerable ELA_PER_EXEC ~ 1 millisecond

P.S. And last interesting note about unusual hint BEGIN_OUTLINE_DATA applying

If I try the following manual hint construction /*+ 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 choose very similar to the “bad” execution plan, which is not exactly the same in details but the same slow. It seems that the BITMAP_TREE hint next after the BEGIN_OUTLINE_DATA comment gets directly into SQL Plan Outline, and cancels the inverse INDEX hint effect

As a result the INDEX hint can be found as unused and transformed to “classic” form in 10053 trace tail:

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. For some applications Oracle recommends to forbid the bitmap plans with B-tree indexes usage on system level by hidden parameter:

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

– according to Database Initialization Parameters for Oracle E-Business Suite Release 12 (Doc ID 396009.1), and this works successfully on statement level too, overriding any of BITMAP_TREE/INDEX_COMBINE index effect:

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
  */

– despite of BITMAP_TREE hint the bitmap plan is disabled by OPT_PARAMETER (*)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: