BITMAP CONVERSION TO ROWIDS

Ни для кого не секрет, что иногда оптимизатор Oracle ведет себя как хочет, и его настройка превращается в сплошное шаманство и пляски с бубном. На волне этой боли и душевных терзаний, я хочу поговорить о подсказке для оптимизатора OPT_PARAM.
Эта подсказка позволяет вам установить значение некоего параметра оптимизатора в процессе выполнения конкретного необходимого вам запроса.

SELECT /*+ OPT_PARAM('star_transformation_enabled' 'true') */ * FROM ... ;

Согласно документации, допустим, для 11G , с помощью этой подсказки можно установить значения всего пяти параметров:

1. OPTIMIZER_DYNAMIC_SAMPLING, 
2. OPTIMIZER_INDEX_CACHING, 
3. OPTIMIZER_INDEX_COST_ADJ, 
4. OPTIMIZER_SECURE_VIEW_MERGING,
5. STAR_TRANSFORMATION_ENABLED

Однако, на практике лучше самостоятельно проверить, включится у вас что-нибудь или нет.
Например, на моем 12C OPT_PARAM выключает _B_TREE_BITMAP_PLANS.

С этого места, начнем другую грустную историю. Скрытый параметр _B_TREE_BITMAP_PLANS начиная, пожалуй с Oracle 9i по умолчанию поставлен в true. Он позволяет генерировать BITMAP планы для таблиц в B-Tree индексами.  Так что CBO может устроить использование BITMAP доступа к данным и без существования BITMAP индекса как такового. Для того, чтобы достичь этой цели, он помещает в план выполнения операции BITMAP CONVERSION FROM ROWIDS и BITMAP CONVERSION TO ROWIDS, которые способны серьезно загрузить CPU. Так что если у вас много строк в таблице - у меня для вас плохие новости. Если сервер с БД целиком ваш, и вы можете делать на нем что вашей душе угодно, то нет проблем с тем, что бы написать

ALTER SYSTEM SET "_b_tree_bitmap_plans"=false SCOPE=SPFILE;

Или же:

ALTER SESSION SET "_b_tree_bitmap_plans"=false;

Но если вы не администратор проблемной БД,  и менять настройки вам кто-то не разрешает, то есть вариант положиться на тот самый OPT_PARAM.

Допустим, у вас есть следующий запрос:

UPDATE Item_Table ie
SET
   ie.UPDATE_LOAD_ID = 
   (
      SELECT  MIN(iet.LOAD_ID) KEEP (DENSE_RANK FIRST ORDER BY iet.ID) 
      FROM Item_Table iet
      WHERE
         iet.ID > ie.ID
         AND iet.KEY = ie.KEY
         AND nvl(iet.LOCATION_ID,'<**null**>') = nvl(ie.LOCATION_ID,'<**null**>')
         AND nvl(iet.JOB_ID,'<**null**>') = nvl(ie.JOB_ID,'<**null**>')
         
   )
WHERE ie.RECENT_FLAG = 'N';

Вот это ужасный план, который генерируется моей базой по умолчанию.

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                      |                              |       |       |   481K(100)|          |
|   1 |  UPDATE                               | ITEM_TABLE                   |       |       |            |          |
|*  2 |   TABLE ACCESS FULL                   | ITEM_TABLE                   | 43750 |    24M|   655   (1)| 00:00:01 |
|   3 |   SORT AGGREGATE                      |                              |     1 |   582 |            |          |
|*  4 |    TABLE ACCESS BY INDEX ROWID BATCHED| ITEM_TABLE                   |     1 |   582 |    10   (0)| 00:00:01 |
|   5 |     BITMAP CONVERSION TO ROWIDS       |                              |       |       |            |          |
|   6 |      BITMAP AND                       |                              |       |       |            |          |
|   7 |       BITMAP CONVERSION FROM ROWIDS   |                              |       |       |            |          |
|*  8 |        INDEX RANGE SCAN               | ITEM_TABLE_KEY_IX            |   175 |       |     1   (0)| 00:00:01 |
|   9 |       BITMAP CONVERSION FROM ROWIDS   |                              |       |       |            |          |
|  10 |        SORT ORDER BY                  |                              |       |       |            |          |
|* 11 |         INDEX RANGE SCAN              | XPK_ITEM_TABLE               |   175 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("IE"."RECENT_FLAG"='N')
   4 - filter((NVL("IET"."LOCATION_ID",:B1)=NVL(:B1,:B1) AND NVL("IET"."JOB_ID",:B1)=NVL(:B2,:B1)))
   8 - access("IET"."ITEM_KEY"=:B1)
  11 - access("IET"."ID">:B1)
       filter("IET"."ID">:B1)

На 43673 записях работает 182,655 секунд, грузит проц и всех бесит.
Теперь воспользуемся великой шаманской магией:

UPDATE Item_Table ie
SET
   ie.UPDATE_LOAD_ID = 
   (
      SELECT /*+ OPT_PARAM('_B_TREE_BITMAP_PLANS','FALSE') */
      MIN(iet.LOAD_ID) KEEP (DENSE_RANK FIRST ORDER BY iet.ID) 
      FROM Item_Table iet
      WHERE
         iet.ID > ie.ID
         AND iet.KEY = ie.KEY
         AND nvl(iet.LOCATION_ID,'<**null**>') = nvl(ie.LOCATION_ID,'<**null**>')
         AND nvl(iet.JOB_ID,'<**null**>') = nvl(ie.JOB_ID,'<**null**>')
         
   )
WHERE ie.RECENT_FLAG = 'N';

План выполнения запроса для этого случая:

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                      |                              |       |       |  3456K(100)|          |
|   1 |  UPDATE                               | ITEM_TABLE                   |       |       |            |          |
|*  2 |   TABLE ACCESS FULL                   | ITEM_TABLE                   | 43750 |    24M|   655   (1)| 00:00:01 |
|   3 |   SORT AGGREGATE                      |                              |     1 |   582 |            |          |
|*  4 |    TABLE ACCESS BY INDEX ROWID BATCHED| ITEM_TABLE                   |     1 |   582 |    78   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN                  | ITEM_TABLE_KEY_IX            |   175 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("IE"."RECENT_FLAG"='N')
   4 - filter(("IET"."ID">:B1 AND NVL("IET"."LOCATION_ID",'<**null**>')=NVL(:B2,'<**null**>') AND 
              NVL("IET"."JOB_ID",'<**null**>')=NVL(:B3,'<**null**>')))
   5 - access("IET"."KEY"=:B1)

Время работы на тех же данных: 1,405 секунд.
Теперь мы лишились возможности пойти налить кофе, пока у нас проходит обновление.