THE HOLY BIBLE - King James Version - БИБЛИЯ в Синодальном переводе
"Нас Атакуют!" Изобличи козни лукавого, запрети диаволу

Изменяемые 'адаптивные' планы выполнения SQL запросов в Оракл 12c

Новая версия Oracle database 12c предлагает разработчикам значительно улучшенный "умный" SQL оптимизатор. Множество доработок и новых возможностей позволяют правильно использовать статистику, оценивать действительное число возвращаемых строк и учитывать фактическое распределение данных в столбцах.

При этом все предыдущие версии SQL оптимизатора, основанного на относительной стоимости операций, периодически ошибались, позволяя первому запросу "пойти в разнос" за счёт выбора неудачного плана. Последующие выполнения того же SQL могли пройти более успешно если оптимизатор мог учесть допущенные ошибки. Но первое выполнение всегда могло быть отработано неоптимальным образом, что было особенно ощутимо в системах хранилищ данных, где зачастую каждый запрос уникален и выполняется однократно.

В предлагаемой заметке я привожу пример использования одной из важных принципиально новых возможностей Оракла 12c изменять SQL план первого запроса "на лету" непосредственно во время его исполнения, приводя к значительному сокращению времени обработки данных. Так называемые "адаптивные" планы могут буквально спасти ситуацию, позволяя правильно и эффективно выполнить даже уникальный неповторяющийся SQL.

Прежде чем мы продолжим, я хотел бы привести строки из Евангелия:



......................... == Книга пророка Исаии == ............................
=== Глава 12, Стих 1 ===
1 И скажешь  в  тот  день:  славлю  Тебя,  Господи;  Ты  гневался  на  меня,  но
  отвратил гнев Твой и утешил меня.
2 Вот, Бог - спасение мое: уповаю на Него и не боюсь; ибо Господь  -  сила  моя,
  и пение мое - Господь; и Он был мне во спасение.
3 И   в   радости   будете   почерпать   воду    из    источников      спасения,
4 и скажете в тот день:  славьте  Господа,  призывайте  имя  Его;  возвещайте  в
  народах дела Его; напоминайте, что велико имя Его;
5 пойте Господу, ибо  Он  соделал  великое,  -  да  знают  это  по  всей  земле.

Лично для вас благая весть - Единородный Сын Божий Иисус Христос любит вас, Он взошёл на крест за ваши грехи, был распят и на третий день воскрес, сел одесную Бога и открыл нам дорогу в Царствие Небесное.

Мы все живём в те самые последние времена, когда брат идёт войной на брата, сердца людские ожесточились и каждый обороняет своё, не внимая голосу рассудка и зову сердца. Сатана запутал нас в бесконечном лабиринте жадности и мирских похотей - и, казалось бы, нет выхода из этого тупика.

Но ведь каждому из нас открыта дорога к спасению от этих окружающих нас повседневных бед - уповай на Господа, прославляй имя Его и ищи в Нём силы для борьбы со злом. Такое вот простое и незатейливое, казалось бы, решение. И тем не менее, уже много тысяч лет Господь спасает взывающих к Нему. И сегодня, на очередном витке нашего с вами безумия именуемого "историей", нам как никогда нужно Божие прощение и милость. Ибо наши страшные душевные раны могут быть омыты только той самой водой из источников спасения - остальным же уготована жажда на пустынной дороге самолюбия и зла, ведущей прямо в ад.

Задумайтесь над этими словами, серьёзно задумайтесь - выбор между спасительной верой и осуждающим для ада неверием неизбежен, и вполне возможно, дорогой брат, что именно сейчас решается ваша судьба - и не на год или десятилетие, а навсегда.

Покайтесь, примите Иисуса как вашего Спасителя, ибо наступают последние времена и время близко - стоит Судья у ворот.

Пожалуйста, в своих каждодневных трудах, какими бы занятыми вы себе ни казались - находите время для Бога, Его заповедей и Библии.

На главной странице этого сайта вы найдете программу для чтения Библии в командной строке - буду очень рад если программа окажется полезной. Пожалуйста, читайте Библию, на экране или в печатном виде - вы будете искренне удивлены как много там сказано лично про вас и ваши обстоятельства.


Вернёмся к нашим техническим деталям.

В заметке рассматриваются несколько видов запросов, приводящих к генерации изменяемого плана:

  • "внутренние" служебные запросы самой БД (и как их распознать)
  • простой пример с двумя таблицами пользователя
  • пример, использующий схему "OE", предлагаемый самим Ораклом
  • Прочитать подробно об изменяемых планах ("adaptive plan") можно в документации Оракла 12 - смотрите "Concept" и "SQL Tuning Guide".

    Я же скажу вкратце так - при первом "разборе" SQL выражения оптимизатор начинает строить план его выполнения, опираясь на имеющиеся данные о количестве записей и числе уникальных значений в каждой колонке таблиц, участвующих в запросе. При определённых обстоятельствах оптимизатор замечает "неясность" в эффективности способа "сведения" таблиц - и вот в этот момент в план вставляется "ветвление" - кусок кода, выполняющийся позже, уже вместе с самим запросом. На данный момент код "ветвления" может сгенерировать только "Hash Join" или "Nested Loops", но я уверен - вскоре появятся новые варианты.

    Непосредственно во время первого выполнения запроса при достижении "ветвления" процесс, исполняющий запрос по динамическому плану, оценивает количество уже обработанных фактически строк и сравнивает с числом строк, "предсказанных" планом. Если число отличается не сильно - ветвление просто игнорируется (и фактически выбрасывается, так что последующие выполнения SQL по этому адаптивному плану уже не будут изменяться - т.е. план превращается из адаптивного в обычный "фиксированный") и запрос "сводит" таблицы способом, предложенным в плане изначально.

    Если же разница в числе строк велика - код "ветвления" выполняется и генерирует другой способ сведения таблиц - например, если изначально оптимизатор предсказал всего 20 строк в каждой таблице и решил использовать "Nested Loops", то теперь, когда вместо 20 строк мы уже обработали 2 тысячи, становится ясным что NL будет работать долго и нудно - и это же самое первое выполнение запроса "на лету" переходит на "Hash Join". И таким же образом, код ветвления удаляется из плана, превращая изменяемый план в его фиксированный эквивалент, но теперь уже содержащий HJ вместо NL.

    Такой подход позволяет выполнить запрос оптимально с первой попытки, даже при неточностях в собранной статистике. При этом код ветвления либо не выполняется вообще, либо выполняется только один раз, сводя на нет возможные потери производительности при обработке последующих запросов по этому же плану.

    "Внутренние" служебные запросы самой БД

    Сам по себе, Оракл является одним из основных пользователей адаптивных планов. Сбор статистики, Automated Workload Repository и прочие "maintenance jobs" извлекают значительную выгоду из этой новой особенности продукта. Распознать запросы, выполненные по адаптивному плану, очень легко используя колонку V$SQL.IS_RESOLVED_ADAPTIVE_PLAN:

    .......... == Второе послание к Тимофею святого апостола Павла == ..............
    === Глава 4, Стих 22 ===
    22 Господь  Иисус  Христос  со  духом   твоим.   Благодать   с   вами.    Аминь.
    
    (b+/b-, c+/c-, +/-, *) > 
    
    [oracle@localhost ~]$ sqlplus
    
    SQL*Plus: Release 12.1.0.1.0 Production
    ...
    SQL> l
      1  select sql_id, IS_REOPTIMIZABLE, IS_RESOLVED_ADAPTIVE_PLAN, child_number
      2  from v$sql
      3  where IS_RESOLVED_ADAPTIVE_PLAN is not null
      4* order by sql_id, child_number
    15:27:36 SQL> /
    
    SQL_ID        I I CHILD_NUMBER
    ------------- - - ------------
    ...
    fuqs31uwjmbkz N N            0
    fuqs31uwjmbkz N Y            1
    ...
    

    Мы видим, что запрос с sql_id "fuqs31uwjmbkz" был выполнен дважды - первый раз это происходило по "начальному" плану, который был "адаптирован" и в конце второго выполнения уже превратился в "фиксированный" план. Колонка IS_RESOLVED_ADAPTIVE_PLAN говорит нам именно о том, что код ветвления присутствовал в начальном плане, был выполнен и удалён.

    Всё же возникает сомнение - а действительно ли план выполнения был адаптивным и вообще, не являются ли две строки выше просто двумя записями обработки одного и того же плана разными сессиями?

    Замена Hash Join на Nested Loops

    Я скажу сразу - изменения в версии 12c, связанные с "адаптивными" запросами и статистикой настолько кардинальны, что во многих случаях ответ просто неясен :-) Методом проб и ошибок, выполнением простых запросов на известных наборах данных получается добраться до реальной картины происходящего - но это требует усилий и времени. Попробуем посмотреть внимательнее:

    ....................... == Книга пророка Аввакума == ...........................
    === Глава 2, Стих 19 ===
    18 Что  за   польза   от   истукана,   сделанного   художником,   этого   литого
       лжеучителя,  хотя  ваятель,  делая   немые   кумиры,   полагается   на   свое
       произведение?
    19 Горе  тому,  кто   говорит   дереву:   "встань!"   и   бессловесному   камню:
       "пробудись!" Научит ли он чему-нибудь? Вот, он обложен  золотом  и  серебром,
       но дыхания в нем нет.
    
    (b+/b-, c+/c-, +/-, *) > 
    
    [oracle@localhost ~]$ sqlplus
    
    SQL*Plus: Release 12.1.0.1.0 Production
    ...
    
    15:45:52 SQL> select IS_REOPTIMIZABLE, IS_RESOLVED_ADAPTIVE_PLAN, child_number,
    15:46:12   2  EXECUTIONS, fetches, END_OF_FETCH_COUNT, LAST_ACTIVE_TIME, PLAN_HASH_VALUE
    15:46:46   3  from v$sql
    15:46:49   4  where sql_id = 'fuqs31uwjmbkz';
    
    I I CHILD_NUMBER EXECUTIONS    FETCHES END_OF_FETCH_COUNT LAST_ACTIVE_TIME  PLAN_HASH_VALUE
    - - ------------ ---------- ---------- ------------------ ----------------- ---------------
    N N            0          0          0                  0 17-03-14 14:00;32      4259802813
    N Y            1          1          2                  1 17-03-14 14:00;32      4259802813
    
    2 rows selected.
    

    Совпадение времени окончания выполнения и хэшей планов показывает нам, что наше предположение оказалось верным - мы говорим об адаптивном плане. Что же именно делал SQL запрос 'fuqs31uwjmbkz' и как менялся "адаптивный" план? Посмотрим (и я заранее извиняюсь за выступающий за поля текст плана):

    ......................... == Книга пророка Ионы == .............................
    === Глава 2, Стих 10 ===
    9 Чтущие   суетных   и   ложных   _богов_   оставили    Милосердаго      своего,
    10 а я гласом  хвалы  принесу  Тебе  жертву;  что  обещал,  исполню:  у  Господа
      спасение!
    
    (b+/b-, c+/c-, +/-, *) >
    
    [oracle@localhost ~]$ sqlplus
    
    SQL*Plus: Release 12.1.0.1.0 Production
    ...
    SQL> l
      1* select * from table(dbms_xplan.display_cursor('fuqs31uwjmbkz', 0, 'ALL +ADAPTIVE +NOTE'))
    15:37:04 SQL> /
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------
    SQL_ID  fuqs31uwjmbkz, child number 0
    -------------------------------------
     SELECT  count(*) as cnt ,         a.SQL_ID, a.CON_DBID,
    a.CURRENT_OBJ#,         sum(case when a.event_id in (:e0, :e1, :e2,
    :e3, :e4, :e5, :e6, :e7) then 1 else 0 end) as full_scan  FROM
    WRH$_ACTIVE_SESSION_HISTORY a     ,  WRH$_EVENT_NAME en  WHERE  a.dbid
    = :dbid  AND a.instance_number = :inst  AND a.snap_id > :bid AND
    a.snap_id <= :eid  AND  a.sql_id in (:sqlid0, :sqlid1, :sqlid2)   AND
    en.dbid = :dbid   AND  en.event_id = a.event_id    AND  en.wait_class
    in ('Cluster', 'User I/O')    AND  a.CURRENT_OBJ# > 0  AND a.sql_id is
    NOT NULL  AND a.session_type = 1  GROUP BY a.SQL_ID, a.CON_DBID,
    a.CURRENT_OBJ#  HAVING count(*) >= :rowcnt ORDER BY count(*) DESC,
    a.CURRENT_OBJ# ASC
    
    Plan hash value: 4259802813
    
    -----------------------------------------------------------------------------------------------------------------------------------
    |   Id  | Operation                         | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -----------------------------------------------------------------------------------------------------------------------------------
    |     0 | SELECT STATEMENT                  |                             |       |       |     7 (100)|          |       |       |
    |     1 |  SORT ORDER BY                    |                             |     1 |    58 |     7  (29)| 00:00:01 |       |       |
    |  *  2 |   FILTER                          |                             |       |       |            |          |       |       |
    |     3 |    HASH GROUP BY                  |                             |     1 |    58 |     7  (29)| 00:00:01 |       |       |
    |  *  4 |     FILTER                        |                             |       |       |            |          |       |       |
    |- *  5 |      HASH JOIN                    |                             |     1 |    58 |     5   (0)| 00:00:01 |       |       |
    |     6 |       NESTED LOOPS                |                             |     1 |    58 |     5   (0)| 00:00:01 |       |       |
    |     7 |        NESTED LOOPS               |                             |     1 |    58 |     5   (0)| 00:00:01 |       |       |
    |-    8 |         STATISTICS COLLECTOR      |                             |       |       |            |          |       |       |
    |     9 |          PARTITION RANGE ITERATOR |                             |     1 |    37 |     3   (0)| 00:00:01 |   KEY |   KEY |
    |  * 10 |           TABLE ACCESS FULL       | WRH$_ACTIVE_SESSION_HISTORY |     1 |    37 |     3   (0)| 00:00:01 |   KEY |   KEY |
    |  * 11 |         INDEX RANGE SCAN          | WRH$_EVENT_NAME_PK          |     1 |       |     1   (0)| 00:00:01 |       |       |
    |  * 12 |        TABLE ACCESS BY INDEX ROWID| WRH$_EVENT_NAME             |     1 |    21 |     2   (0)| 00:00:01 |       |       |
    |- * 13 |       TABLE ACCESS FULL           | WRH$_EVENT_NAME             |     1 |    21 |     2   (0)| 00:00:01 |       |       |
    -----------------------------------------------------------------------------------------------------------------------------------
    
    ...
    
    Note
    -----
       - this is an adaptive plan (rows marked '-' are inactive)
    
    Elapsed: 00:00:00.06
    15:37:04 SQL> 
    

    План для "child 1" будет таким же самым. Действия в плане, помеченные дефисом, не выполнялись, именно как результат работы кода ветвления (обозначенного на плане "STATISTICS COLLECTOR", который тоже был помечен "-" после первого обращения и не является активным для последующих сессий).

    В этом запросе процесс, выполняющий план с хэшем 4259802813, решил не использовать Hash Join и полное сканирование таблицы WRH$_EVENT_NAME, вместо этого был использован индекс WRH$_EVENT_NAME_PK с последующим Nested Loops Join. Также заметьте наличие Bind Variables - без них план вероятнее всего не был бы адаптивным и колонка v$sql.IS_RESOLVED_ADAPTIVE_PLAN содержала бы NULL.

    Замена Nested Loops на Hash Join

    Теперь посмотрим на обратное действие - похожий запрос от того же модуля AWR, только выполнялся он 4 раза, и все разы использовал адаптивный план, в котором вместо NL использовался HJ и полное сканирование таблицы WRH$_EVENT_NAME. Даты и количество END_OF_FETCH_COUNT показывают нам, что Child 0 был принят к обработке, но не закончил выполнения. Child 1 был полностью выполнен 4 раза.

    .............. == Послание к Галатам святого апостола Павла == .................
    === Глава 6, Стих 18 ===
    18 Благодать  Господа  нашего  Иисуса  Христа  со  духом  вашим,  братия. Аминь.
    
    (b+/b-, c+/c-, +/-, *) >
    
    [oracle@localhost ~]$ sqlplus
    
    SQL*Plus: Release 12.1.0.1.0 Production
    ...
    
    16:04:16 SQL> l
      1  select IS_REOPTIMIZABLE, IS_RESOLVED_ADAPTIVE_PLAN, child_number,
      2  EXECUTIONS, fetches, END_OF_FETCH_COUNT, FIRST_LOAD_TIME,
      3   LAST_ACTIVE_TIME, full_PLAN_HASH_VALUE,
      4  PLAN_HASH_VALUE, hash_value, invalidations
      5  from v$sql
      6* where sql_id = '6cp74g22fzahf'
    16:04:19 SQL> /
    
    I I CHILD_NUMBER EXECUTIONS    FETCHES END_OF_FETCH_COUNT FIRST_LOAD_TIME LAST_ACTIVE_TIME
    - - ------------ ---------- ---------- ------------------ --------------- -----------------
    FULL_PLAN_HASH_VALUE PLAN_HASH_VALUE HASH_VALUE INVALIDATIONS
    -------------------- --------------- ---------- -------------
    N N            0          0          0                  0 2014-03-17/13:0 17-03-14 13:00;30
                                                              0:30
              3348789387      3902652512 2230299150             0
    
    N Y            1          4        529                  4 2014-03-17/13:0 17-03-14 16:00;42
                                                              0:30
              3348789387      3902652512 2230299150             0
    
    
    2 rows selected.
    
    Elapsed: 00:00:00.00
    16:04:21 SQL> 
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------
    SQL_ID  6cp74g22fzahf, child number 0
    -------------------------------------
     WITH ash as  (SELECT ash.event_id,  sum(decode(ash.session_type, 1, 1,
    0)) as cnt_fg,  count(*) as cnt_total  FROM
    WRH$_ACTIVE_SESSION_HISTORY ash  WHERE  ash.dbid = :dbid  AND
    ash.instance_number = :inst  AND ash.snap_id > :bid AND ash.snap_id <=
    :eid  AND  ash.wait_time = 0  GROUP BY ash.event_id  ),  evts as
    (SELECT event_id, sum(w_cnt) as w_cnt, sum(to_cnt) as to_cnt,
    sum(w_time) as w_time  ,sum(w_cnt_fg) as w_cnt_fg, sum(to_cnt_fg) as
    to_cnt_fg,  sum(w_time_fg) as w_time_fg  FROM (SELECT es.event_id as
    event_id,  es.total_waits - nvl(bs.total_waits,0) as w_cnt,
    es.TOTAL_TIMEOUTS - nvl(bs.TOTAL_TIMEOUTS,0) as to_cnt,
    es.TIME_WAITED_MICRO - nvl(bs.TIME_WAITED_MICRO,0)  as w_time
    ,es.total_waits_fg - nvl(bs.total_waits_fg,0)  as w_cnt_fg,
    es.TOTAL_TIMEOUTS_FG - nvl(bs.TOTAL_TIMEOUTS_FG,0)  as to_cnt_fg,
    es.TIME_WAITED_MICRO_FG - nvl(bs.TIME_WAITED_MICRO_FG,0)  as w_time_fg
    FROM  WRH$_SYSTEM_EVENT es , WRH$_SYSTEM_EVENT bs  WHERE  es.dbid =
    :dbid  AND es.instance_number = :i
    
    Plan hash value: 3902652512
    
    ------------------------------------------------------------------------------------------------------------------------------------------------------------
    |   Id  | Operation                                                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ------------------------------------------------------------------------------------------------------------------------------------------------------------
    |     0 | SELECT STATEMENT                                           |                             |       |       |    21 (100)|          |       |       |
    |     1 |  SORT ORDER BY                                             |                             |    97 | 19982 |    21  (15)| 00:00:01 |       |       |
    |  *  2 |   FILTER                                                   |                             |       |       |            |          |       |       |
    |  *  3 |    HASH JOIN RIGHT OUTER                                   |                             |    97 | 19982 |    20  (10)| 00:00:01 |       |       |
    |     4 |     FIXED TABLE FULL                                       | X$KEHEVTMAP                 |   105 |  1155 |     0   (0)|          |       |       |
    |  *  5 |     HASH JOIN RIGHT OUTER                                  |                             |    97 | 18915 |    20  (10)| 00:00:01 |       |       |
    |     6 |      VIEW                                                  |                             |     2 |    66 |     4  (25)| 00:00:01 |       |       |
    |     7 |       HASH GROUP BY                                        |                             |     2 |    58 |     4  (25)| 00:00:01 |       |       |
    |  *  8 |        FILTER                                              |                             |       |       |            |          |       |       |
    |     9 |         PARTITION RANGE ITERATOR                           |                             |     2 |    58 |     3   (0)| 00:00:01 |   KEY |   KEY |
    |  * 10 |          TABLE ACCESS FULL                                 | WRH$_ACTIVE_SESSION_HISTORY |     2 |    58 |     3   (0)| 00:00:01 |   KEY |   KEY |
    |  * 11 |      HASH JOIN                                             |                             |    97 | 15714 |    16   (7)| 00:00:01 |       |       |
    |  * 12 |       FIXED TABLE FULL                                     | X$KEHECLMAP                 |    12 |   156 |     0   (0)|          |       |       |
    |  * 13 |       HASH JOIN                                            |                             |   105 | 15645 |    16   (7)| 00:00:01 |       |       |
    |-   14 |        NESTED LOOPS                                        |                             |   105 | 15645 |    16   (7)| 00:00:01 |       |       |
    |-   15 |         NESTED LOOPS                                       |                             |       |       |            |          |       |       |
    |-   16 |          STATISTICS COLLECTOR                              |                             |       |       |            |          |       |       |
    |    17 |           VIEW                                             |                             |   105 |  8925 |     9  (12)| 00:00:01 |       |       |
    |    18 |            HASH GROUP BY                                   |                             |   105 |  8925 |     9  (12)| 00:00:01 |       |       |
    |    19 |             VIEW                                           |                             |   270 | 22950 |     8   (0)| 00:00:01 |       |       |
    |    20 |              UNION-ALL                                     |                             |       |       |            |          |       |       |
    |  * 21 |               HASH JOIN OUTER                              |                             |   165 | 14190 |     8   (0)| 00:00:01 |       |       |
    |-   22 |                NESTED LOOPS OUTER                          |                             |   165 | 14190 |     8   (0)| 00:00:01 |       |       |
    |-   23 |                 STATISTICS COLLECTOR                       |                             |       |       |            |          |       |       |
    |    24 |                  PARTITION RANGE SINGLE                    |                             |    90 |  3870 |     4   (0)| 00:00:01 |   KEY |   KEY |
    |    25 |                   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| WRH$_SYSTEM_EVENT           |    90 |  3870 |     4   (0)| 00:00:01 |   KEY |   KEY |
    |  * 26 |                    INDEX RANGE SCAN                        | WRH$_SYSTEM_EVENT_PK        |    46 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
    |-   27 |                 PARTITION RANGE SINGLE                     |                             |     2 |    86 |     4   (0)| 00:00:01 |   KEY |   KEY |
    |-   28 |                  TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | WRH$_SYSTEM_EVENT           |     2 |    86 |     4   (0)| 00:00:01 |   KEY |   KEY |
    |- * 29 |                   INDEX RANGE SCAN                         | WRH$_SYSTEM_EVENT_PK        |    46 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
    |    30 |                PARTITION RANGE SINGLE                      |                             |    90 |  3870 |     4   (0)| 00:00:01 |   KEY |   KEY |
    |    31 |                 TABLE ACCESS BY LOCAL INDEX ROWID BATCHED  | WRH$_SYSTEM_EVENT           |    90 |  3870 |     4   (0)| 00:00:01 |   KEY |   KEY |
    |  * 32 |                  INDEX RANGE SCAN                          | WRH$_SYSTEM_EVENT_PK        |    46 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
    |    33 |               FIXED TABLE FULL                             | X$KEHEVTMAP                 |   105 |   735 |     0   (0)|          |       |       |
    |- * 34 |          INDEX RANGE SCAN                                  | WRH$_EVENT_NAME_PK          |       |       |            |          |       |       |
    |- * 35 |         TABLE ACCESS BY INDEX ROWID                        | WRH$_EVENT_NAME             |     1 |    64 |     7   (0)| 00:00:01 |       |       |
    |  * 36 |        TABLE ACCESS FULL                                   | WRH$_EVENT_NAME             |  1501 | 96064 |     7   (0)| 00:00:01 |       |       |
    ------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    
    Note
    -----
       - this is an adaptive plan (rows marked '-' are inactive)
    

    Обратите внимание, что "код ветвления" был вставлен в этот план дважды.

    Простой пример с двумя таблицами пользователя

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

    Создание набора данных пользователя

    Используя приведенные ниже команды, создайте в своей базе подобные объекты в какой-нибудь из схем. Используйте "локального" пользователя с минимальным набором привилегий в одной из "подключаемых" PDB баз. Как я говорил в своей заметке про PDB/CDB, для удобства подключения установите переменную окружения среды TWO_TASK.

    ........................ == Книга пророка Иоиля == ............................
    === Глава 2, Стих 21 ===
    20 И пришедшего от севера удалю от вас, и изгоню в  землю  безводную  и  пустую,
       переднее полчище его - в море восточное,  а  заднее  -  в  море  западное,  и
       пойдет от него зловоние, и  поднимется  от  него  смрад,  так  как  он  много
       наделал _зла._
    21 Не бойся, земля: радуйся и  веселись,  ибо  Господь  велик,  чтобы  совершить
       это.
    
    (b+/b-, c+/c-, +/-, *) > 
    
    [oracle@localhost ~]$ sqlplus
    
    SQL*Plus: Release 12.1.0.1.0 Production
    ...
    
    11:53:00 SQL> create table customer (id number, name varchar2(100));
    
    Table created.
    
    Elapsed: 00:00:00.05
    11:53:47 SQL> 
    
    11:54:18 SQL> create table visits (cust_id number, visit_date date);
    
    Table created.
    
    Elapsed: 00:00:00.01
    11:55:01 SQL> 
    
    11:55:31 SQL> insert into customer values (1, 'ClientA');
    
    1 row created.
    
    Elapsed: 00:00:00.09
    11:55:43 SQL> commit;
    
    Commit complete.
    
    Elapsed: 00:00:00.00
    11:55:46 SQL>
    
    11:55:54 SQL> insert into visits values (1, sysdate);
    
    1 row created.
    
    Elapsed: 00:00:00.03
    11:56:09 SQL> /
    
    1 row created.
    
    Elapsed: 00:00:00.00
    11:56:16 SQL> /
    
    1 row created.
    
    Elapsed: 00:00:00.01
    11:56:26 SQL> commit;
    
    Commit complete.
    
    Elapsed: 00:00:00.01
    11:56:29 SQL> 
    
    
    -- Добавим данных для создания неравномерного распределения.
    12:37:35 SQL> insert into customer values(2, 'Client B');
    
    1 row created.
    
    Elapsed: 00:00:00.00
    12:37:57 SQL> insert into customer values(3, 'Client C');
    
    1 row created.
    
    Elapsed: 00:00:00.01
    12:38:12 SQL>  insert into customer values(4, 'Client D');
    
    1 row created.
    
    Elapsed: 00:00:00.00
    12:38:22 SQL> commit;
    
    Commit complete.
    
    Elapsed: 00:00:00.01
    12:38:24 SQL> 
    
    12:38:42 SQL> insert into visits select 2, VISIT_DATE from visits;
    
    3 rows created.
    
    Elapsed: 00:00:00.01
    12:39:19 SQL> c/2/3
      1* insert into visits select 3, VISIT_DATE from visits
    12:39:24 SQL> /
    
    6 rows created.
    
    Elapsed: 00:00:00.02
    12:39:26 SQL> c/3/4
      1* insert into visits select 4, VISIT_DATE from visits
    12:39:30 SQL> /
    
    12 rows created.
    
    Elapsed: 00:00:00.01
    12:39:31 SQL> commit;
    
    Commit complete.
    
    Elapsed: 00:00:00.02
    12:39:35 SQL> 
    
    
    
    12:39:48 SQL> select count(*) from customer;
    
      COUNT(*)
    ----------
             4
    
    1 row selected.
    
    Elapsed: 00:00:00.01
    12:39:58 SQL> select count(*) from visits;
    
      COUNT(*)
    ----------
            24
    
    1 row selected.
    
    Elapsed: 00:00:00.02
    12:40:09 SQL> 
    
    
    -- Теперь "раздуем" таблицу, копируя её саму в себя:
    insert into visits select * from visits;
    ...
    13:48:46 SQL> select count(*) from visits;
    
      COUNT(*)
    ----------
      25165824
    
    1 row selected.
    
    Elapsed: 00:00:00.28
    13:48:59 SQL> 
    
    
    -- Создадим отдельного клиента с минимумом записей
    10:32:21 SQL> insert into customer values (0, 'Small Client');
    
    1 row created.
    
    Elapsed: 00:00:00.00
    10:32:57 SQL> insert into visits values (0, sysdate);
    
    1 row created.
    
    Elapsed: 00:00:00.01
    10:33:19 SQL> /
    
    1 row created.
    
    Elapsed: 00:00:00.00
    10:33:20 SQL> /
    
    1 row created.
    
    Elapsed: 00:00:00.00
    10:33:20 SQL> commit;
    
    Commit complete.
    
    Elapsed: 00:00:00.01
    10:33:22 SQL> 
    
    
    
    -- Создадим необходимые связи между таблицами
    12:02:40 SQL> alter table customer add constraint PK_CUS_ID primary key (id);
    
    Table altered.
    
    Elapsed: 00:00:00.09
    12:03:05 SQL> alter table visits add constraint FK_VIS_CUS -
    > foreign key (cust_id) references customer(id);
    
    Table altered.
    
    Elapsed: 00:00:00.01
    12:03:12 SQL> 
    
    
    create index IDX_VIS_CUSID  on visits(cust_id) nologging;
    
    Index created.
    
    Elapsed: 00:00:00.08
    
    
    -- Соберём статистику "по умолчанию" для всех объектов схемы
    10:10:24 SQL> exec dbms_stats.gather_schema_stats(user);
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:02.68
    10:28:02 SQL>
    
    
    10:28:53 SQL> l
      1  select table_name, degree, last_analyzed, num_rows
      2* from user_tables
    10:28:55 SQL> /
    
    10:34:16 SQL> select table_name, degree, last_analyzed, num_rows
    10:34:25   2  from user_tables;
    
    TABLE_NAME           DEGREE     LAST_ANALYZED       NUM_ROWS
    -------------------- ---------- ----------------- ----------
    VISITS                        1 18-03-14 10:34:16   25165827
    CUSTOMER                      1 18-03-14 10:34:14          5
    
    2 rows selected.
    
    Elapsed: 00:00:00.01
    
    -- Which customer has most records?
      1  select cust_id, count(*)
      2  from visits
      3  group by cust_id
      4* order by 2
    10:35:37 SQL> /
    
       CUST_ID   COUNT(*)
    ---------- ----------
             0          3
             2    3145728
             1    3145728
             3    6291456
             4   12582912
    
    5 rows selected.
    
    Elapsed: 00:00:00.66
    10:35:40 SQL> 
    
    

    После сбора статистики Оракл построил для нас частотную гистограмму для колонки CUST_ID таблицы VISITS:

    .......... == Первое соборное послание святого апостола Иоанна == ..............
    === Глава 2, Стих 22 ===
    22 Кто  лжец,  если  не  тот,  кто  отвергает,  что  Иисус  есть  Христос?   Это
       антихрист, отвергающий Отца и Сына.
    
    (b+/b-, c+/c-, +/-, *) > 
    
    [oracle@localhost ~]$ sqlplus
    
    SQL*Plus: Release 12.1.0.1.0 Production
    ...
    
    SQL> l
      1  select TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE, 
      2  ENDPOINT_REPEAT_COUNT from user_histograms
      3* order by TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER
    10:38:29 SQL> /
    
    TABLE_NAME           COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_REPEAT_COUNT
    -------------------- ---------- --------------- -------------- ---------------------
    CUSTOMER             ID                       0              0                     0
    CUSTOMER             ID                       1              4                     0
    CUSTOMER             NAME                     0     3.5008E+35                     0
    CUSTOMER             NAME                     1     4.3318E+35                     0
    VISITS               CUST_ID                  3              0                     0
    VISITS               CUST_ID            3145731              1                     0
    VISITS               CUST_ID            6291459              2                     0
    VISITS               CUST_ID           12582915              3                     0
    VISITS               CUST_ID           25165827              4                     0
    VISITS               VISIT_DATE               0      2456734.5                     0
    VISITS               VISIT_DATE               1     2456735.44                     0
    
    11 rows selected.
    
    Elapsed: 00:00:00.08
    10:38:31 SQL> 
    
      1  select TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, NUM_BUCKETS, HISTOGRAM
      2  from user_tab_col_statistics
      3* where HISTOGRAM != 'NONE'
    10:47:38 SQL> /
    
    TABLE_NAME           COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM
    -------------------- ---------- ------------ ----------- ---------------
    VISITS               CUST_ID               5           5 FREQUENCY
    
    1 row selected.
    
    Elapsed: 00:00:00.48
    10:47:39 SQL> 
    

    Существуют параметры, определяющие эффект адаптивных планов, по умолчанию оптимизатор запросов будет их создавать и использовать:

    12:23:06 SQL> show parameter adaptive
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    optimizer_adaptive_features          boolean     TRUE
    optimizer_adaptive_reporting_only    boolean     FALSE
    parallel_adaptive_multi_user         boolean     TRUE
    12:30:02 SQL>
    

    Итак, данные созданы - пора перейти к запросу.

    Запрос пользователя и начальный план его выполнения

    Запрос очень простой и предсказуемый, посмотрим какой план Оракл нам предложит до его исполнения:

      1  explain plan for
      2  select /*+ gather_plan_statistics Test1 */ c.name, v.visit_date
      3  from customer c, visits v
      4  where c.id = v.cust_id
      5  and c.id > :custid
      6  and v.visit_date < sysdate -1
      7* order by v.visit_date
    11:55:21 SQL> /
    
    Explained.
    
    Elapsed: 00:00:00.03
    11:55:21 SQL> 
    11:55:22 SQL> 
    11:55:22 SQL> 
    11:55:22 SQL> 
    11:55:22 SQL> select * from table(dbms_xplan.display(format=>'ADAPTIVE NOTE'));
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------
    Plan hash value: 3793304633
    
    -----------------------------------------------------------------------------------------------------
    |   Id  | Operation                             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------------------
    |     0 | SELECT STATEMENT                      |           |     1 |    24 |     6   (0)| 00:00:01 |
    |     1 |  SORT ORDER BY                        |           |     1 |    24 |     6   (0)| 00:00:01 |
    |- *  2 |   HASH JOIN                           |           |     1 |    24 |     6   (0)| 00:00:01 |
    |     3 |    NESTED LOOPS                       |           |       |       |            |          |
    |     4 |     NESTED LOOPS                      |           |     1 |    24 |     6   (0)| 00:00:01 |
    |-    5 |      STATISTICS COLLECTOR             |           |       |       |            |          |
    |  *  6 |       TABLE ACCESS FULL               | VISITS    |     1 |    11 |     5   (0)| 00:00:01 |
    |  *  7 |      INDEX UNIQUE SCAN                | PK_CUS_ID |     1 |       |     0   (0)| 00:00:01 |
    |     8 |     TABLE ACCESS BY INDEX ROWID       | CUSTOMER  |     1 |    13 |     1   (0)| 00:00:01 |
    |-    9 |    TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER  |     1 |    13 |     1   (0)| 00:00:01 |
    |- * 10 |     INDEX RANGE SCAN                  | PK_CUS_ID |     1 |       |     0   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("C"."ID"="V"."CUST_ID")
       6 - filter("V"."CUST_ID">TO_NUMBER(:CUSTID) AND "V"."VISIT_DATE"TO_NUMBER(:CUSTID))
      10 - access("C"."ID">TO_NUMBER(:CUSTID))
    
    Note
    -----
       - this is an adaptive plan (rows marked '-' are inactive)
    
    30 rows selected.
    
    Elapsed: 00:00:00.17
    

    За исключением создания адаптивного плана, ничего выдающегося. Оптимизатор решил не использовать Hash Join ввиду малого числа ожидаемых строк (всего одна).

    Hint "gather_plan_statistics" нам понадобится во всех тестах, наравне с другим полезным хинтом "monitor". Почитайте в документации про них. Не забудьте проверить ваше лицензирование вообще, в том числе если используете "management pack".

    Запускаем запрос и проверяем действительный план выполнения

    Вы наверняка помните, что "explain plan" более не является полностью надежной утилитой - он может показать одно, а на самом деле запрос будет выполнен иначе. Запустим SQL и после его завершения проверим план по "sql_id".

    11:56:01 SQL> print :custid
    
        CUSTID
    ----------
             2
    
    11:58:21 SQL> 
    
      1  select /*+ gather_plan_statistics Test1 */ c.name, v.visit_date
      2  from customer c, visits v
      3  where c.id = v.cust_id
      4  and c.id > :custid
      5  and v.visit_date < sysdate -1
      6* order by v.visit_date
    11:58:53 SQL> /
    
    no rows selected
    
    Elapsed: 00:00:00.01
    11:58:53 SQL> 
    

    Итак, оптимизатор был неправ - наш запрос не вернул строк вообще. Определим sql_id запроса и проверим как же он был выполнен.

      1  select LAST_ACTIVE_TIME, sql_id, sql_text, child_number, IS_REOPTIMIZABLE,
      2  IS_RESOLVED_ADAPTIVE_PLAN, fetches, executions, end_of_fetch_count, 
      3  rows_processed, plan_hash_value from v$sql
      4* where sql_text like '%Test1%'
    12:00:55 SQL> /
    
    LAST_ACTIVE_TIME  SQL_ID
    ----------------- -------------
    SQL_TEXT
    ----------------------------------------------------------------------------------------
    CHILD_NUMBER I I    FETCHES EXECUTIONS END_OF_FETCH_COUNT ROWS_PROCESSED PLAN_HASH_VALUE
    ------------ - - ---------- ---------- ------------------ -------------- ---------------
    
    24-03-14 11:58:53 7spzkn428fjdu
    select /*+ gather_plan_statistics Test1 */ c.name, v.visit_date from customer c, visits v 
    where c.id = v.cust_id and c.id > :custid and 
    v.visit_date < sysdate -1 order by v.visit_date
               0 N Y          1          1                  1              0       865602956
    
    
    SQL> select * from table(dbms_xplan.display_cursor('7spzkn428fjdu', 0, -
    > 'ADAPTIVE NOTE ALLSTATS'))
    13:45:52 SQL> /
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------
    SQL_ID  7spzkn428fjdu, child number 0
    -------------------------------------
    select /*+ gather_plan_statistics Test1 */ c.name, v.visit_date from
    customer c, visits v where c.id = v.cust_id and c.id > :custid and
    v.visit_date < sysdate -1 order by v.visit_date
    
    Plan hash value: 865602956
    
    ----------------------------------------------------------------------------------------------------------------------
    |   Id  | Operation                                | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ----------------------------------------------------------------------------------------------------------------------
    |     0 | SELECT STATEMENT                         |               |      1 |        |      0 |00:00:00.01 |       2 |
    |     1 |  SORT ORDER BY                           |               |      1 |      1 |      0 |00:00:00.01 |       2 |
    |- *  2 |   HASH JOIN                              |               |      1 |      1 |      0 |00:00:00.01 |       2 |
    |     3 |    NESTED LOOPS                          |               |      1 |        |      0 |00:00:00.01 |       2 |
    |     4 |     NESTED LOOPS                         |               |      1 |      1 |      0 |00:00:00.01 |       2 |
    |-    5 |      STATISTICS COLLECTOR                |               |      1 |        |      0 |00:00:00.01 |       2 |
    |  *  6 |       TABLE ACCESS BY INDEX ROWID BATCHED| VISITS        |      1 |      1 |      0 |00:00:00.01 |       2 |
    |  *  7 |        INDEX RANGE SCAN                  | IDX_VIS_CUSID |      1 |      2 |      0 |00:00:00.01 |       2 |
    |  *  8 |      INDEX UNIQUE SCAN                   | PK_CUS_ID     |      0 |      1 |      0 |00:00:00.01 |       0 |
    |     9 |     TABLE ACCESS BY INDEX ROWID          | CUSTOMER      |      0 |      1 |      0 |00:00:00.01 |       0 |
    |-   10 |    TABLE ACCESS BY INDEX ROWID BATCHED   | CUSTOMER      |      0 |      1 |      0 |00:00:00.01 |       0 |
    |- * 11 |     INDEX RANGE SCAN                     | PK_CUS_ID     |      0 |      1 |      0 |00:00:00.01 |       0 |
    ----------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("C"."ID"="V"."CUST_ID")
       6 - filter("V"."VISIT_DATE":CUSTID)
       8 - access("C"."ID"="V"."CUST_ID")
           filter("C"."ID">:CUSTID)
      11 - access("C"."ID">:CUSTID)
    
    Note
    -----
       - this is an adaptive plan (rows marked '-' are inactive)
    

    Итак, при выполнении использовался слегка другой план (сравните hash плана в предыдущем подразделе и этого) - оптимизатор решил использовать индексы для обеих таблиц. Но этот план тоже был адаптивным, при этом оптимизатор не был уверен надо ли делать Hash Join или Nested Loops до тех пор, пока не начал получать (отсутствующие) результаты.

    Обратили ли вы внимание на привязку запроса к текущей дате? С тем же самым значением переменной :custid (равному 2) через 24 часа этот же запрос вернёт миллионы строк и оптимизатор решит "переключиться" во время исполнения на Hash Join. Правда, при этом необходимо очистить library cache или дождаться естественного устаревания и исчезновения уже ставшего постоянным плана с хэшем 865602956 (об этом свидетельствует IS_RESOLVED_ADAPTIVE_PLAN=Y в результате запроса вверху). Так что, по-прежнему, ещё есть возможность "обмануть" оптимизатор. Надо отметить, что другие средства оптимизации могут заставить Оракл сгенерировать новый фиксированный план даже при уже имеющемся адаптивном плане (например, обновление или устаревание статистики) - но в нашем случае привязки к дате вполне вероятна ситуация, когда миллионы строк будут возвращаться через Nested Loops. Я оставляю этот случай для вас в качестве упражнения.

    Скрипт для тестирования

    Используйте этот скрипт для тестирования разных условий и планов. Результаты должны показать, что внесение потенциальной "неясности" сразу же приводит к генерации изменяемого плана. Соответственно, ясно выраженные условия, при наличии гистограммы и правильной статистики, приводят к использованию простого фиксированного постоянного плана с самого начала.

    var custid number
    exec :custid:=2;
    print :custid;
    
    explain plan for
    select /*+ gather_plan_statistics Test1 */ c.name, v.visit_date from
    customer c, visits v where c.id = v.cust_id 
    -- Откомментируйте следующие строки по одной
    --and c.id = :custid  			-- fixed plan
    --and c.id > :custid  			-- adaptive plan
    --and v.visit_date < sysdate -1 	-- adaptive plan
    --and v.visit_date = :custid		-- fixed plan
    order by v.visit_date
    /
    select * from table(dbms_xplan.display(format=>'ADAPTIVE NOTE'))
    /
    rollback
    /
    

    В конце тестирования, измените степень параллельного выполнения на таблицах и повторите тесты в приведенном выше скрипте. Изменятся ли полученные планы?

    12:05:11 SQL> alter table VISITS parallel 8;
    
    Table altered.
    
    Elapsed: 00:00:00.01
    12:05:19 SQL>  alter table CUSTOMER parallel 8;
    
    Table altered.
    

    Смогли ли вы добиться адаптивных планов при параллельном выполнении запросов?

    Моё тестирование показало, что в текущей версии Оракла 12.1.0.1 адаптивные планы используются только когда возможна ошибка оптимизации. Они используются почти всегда при наличии условий с неравенством и однопоточного выполнения запроса. В случае параллельного выполнения адаптивные планы почти никогда не генерируются. Также для создания изменяемых планов используйте PDB, подключаясь к ней как локальный пользователь - во многих случаях оптимизатор не генерирует адаптивные планы при подключении к контейнеру CDB$ROOT или не-CDB базе данных.

    Пример, использующий схему "OE"

    Поставляемая с Оракл схема Order Entry (загрузите и установите "Oracle Database 12c Release 1 Examples") тоже генерирует адаптивные планы. Используем простой запрос:

    SQL> l
      1  explain plan for
      2  select product_name
      3  from oe.order_items o, oe.product_information p
      4  where o.unit_price = 15
      5  and o.quantity >1
      6* and p.product_id = o.product_id
    09:49:55 SQL>
    

    Какой план нам "предскажет" оптимизатор до выполнения запроса?

      1* select * from table(dbms_xplan.display(format=>'ADAPTIVE NOTE ALLSTATS'))
    09:50:54 SQL> /
    
    PLAN_TABLE_OUTPUT
    ------------------------------
    Plan hash value: 1553478007
    
    ---------------------------------------------------------------------------
    |   Id  | Operation                     | Name                   | E-Rows |
    ---------------------------------------------------------------------------
    |     0 | SELECT STATEMENT              |                        |     13 |
    |  *  1 |  HASH JOIN                    |                        |     13 |
    |-    2 |   NESTED LOOPS                |                        |        |
    |-    3 |    NESTED LOOPS               |                        |     13 |
    |-    4 |     STATISTICS COLLECTOR      |                        |        |
    |  *  5 |      TABLE ACCESS FULL        | ORDER_ITEMS            |     13 |
    |- *  6 |     INDEX UNIQUE SCAN         | PRODUCT_INFORMATION_PK |        |
    |-    7 |    TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION    |      1 |
    |     8 |   TABLE ACCESS FULL           | PRODUCT_INFORMATION    |    288 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
       5 - filter("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1)
       6 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
    
    Note
    -----
       - this is an adaptive plan (rows marked '-' are inactive)
       - Warning: basic plan statistics not available. These are only collected when:
           * hint 'gather_plan_statistics' is used for the statement or
           * parameter 'statistics_level' is set to 'ALL', at session or system level
    
    29 rows selected.
    
    Elapsed: 00:00:00.04
    09:50:54 SQL> 
    

    Выполним запрос:

      1  select product_name
      2   from oe.order_items o, oe.product_information p
      3   where o.unit_price = 15
      4   and o.quantity >1
      5*  and p.product_id = o.product_id
    09:52:17 SQL> /
    
    PRODUCT_NAME
    --------------------------------------------------
    Screws 
    Screws 
    Screws 
    Screws 
    Screws 
    Screws 
    Screws 
    Screws 
    Screws 
    Screws 
    Screws 
    Screws 
    Screws 
    
    13 rows selected.
    
    Elapsed: 00:00:00.01
    

    Похоже, оптимизатор угадал число возвращаемых строк (13) - а это значит, что изначальная версия адаптивного плана окажется приемлемой, план не должен измениться, код ветвления не будет выполнен ни разу и план просто превратится в постоянный в конце первого запуска запроса. Проверим это:

    SQL_ID  8bwz2v2542zm4, child number 0
    -------------------------------------
    select /*+ gather_plan_statistics */ product_name from oe.order_items
    o, oe.product_information p where o.unit_price = 15 and o.quantity >1
    and p.product_id = o.product_id
    
    Plan hash value: 1553478007
    
    -----------------------------------------------------------------------------------------------------------------------------------------------
    |   Id  | Operation                     | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
    -----------------------------------------------------------------------------------------------------------------------------------------------
    |     0 | SELECT STATEMENT              |                        |      1 |        |     13 |00:00:00.01 |      24 |       |       |          |
    |  *  1 |  HASH JOIN                    |                        |      1 |     13 |     13 |00:00:00.01 |      24 |  2061K|  2061K|     1/0/0|
    |-    2 |   NESTED LOOPS                |                        |      1 |        |     13 |00:00:00.01 |       7 |       |       |          |
    |-    3 |    NESTED LOOPS               |                        |      1 |     13 |     13 |00:00:00.01 |       7 |       |       |          |
    |-    4 |     STATISTICS COLLECTOR      |                        |      1 |        |     13 |00:00:00.01 |       7 |       |       |          |
    |  *  5 |      TABLE ACCESS FULL        | ORDER_ITEMS            |      1 |     13 |     13 |00:00:00.01 |       7 |       |       |          |
    |- *  6 |     INDEX UNIQUE SCAN         | PRODUCT_INFORMATION_PK |      0 |        |      0 |00:00:00.01 |       0 |       |       |          |
    |-    7 |    TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION    |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
    |     8 |   TABLE ACCESS FULL           | PRODUCT_INFORMATION    |      1 |    288 |    288 |00:00:00.01 |      17 |       |       |          |
    -----------------------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
       5 - filter(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))
       6 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
    
    Note
    -----
       - this is an adaptive plan (rows marked '-' are inactive)
    

    Всё оказалось именно так - даже хэш плана до и после выполнения остался неизменным. Что ещё раз показывает исключительную важность правильной статистики для SQL оптимизатора версии Oracle 12c. Кстати, статистика теперь тоже стала адаптивной, как и планы - но это уже другая история.

    Спасибо что зашли,

    Будьте благословенны!
    Денис

    27 Марта 2014 года.


    Сайт не содержит активных элементов - скриптов и cookies