Фев 132013
 

При смене параметра optimizer_features_enable с 9.2.0.8 на 11.2.0.3, очевидно, могут сильно измениться планы запросов и их стоимость с точки зрения оптимизатора. Но есть и неочевидные следствия изменения поведения оптимизатора, об одном из которых и будет эта статья.

Начиная с версии Oracle 10g в запросах с группировкой вместо операции SORT GROUP BY при операциях сортировки может использоваться новый внутренний алгоритм сортировки, HASH GROUP BY. В общем случае (если не используется специфический хинт /*+ no_use_hash_aggregation */), предсказать, какой именно алгоритм выберет оптимизатор, нельзя. Для нас важно то, что в случае использования HASH GROUP BY поменяется порядок возвращаемых строк, т.е. результат будет неотсортированным!

Рассмотрим небольшой пример:

  1. Возьмём запрос

    -- Пока попробуем только в пределах одной сессии
    ALTER SESSION SET optimizer_features_enable = '11.2.0.3';
     
    SELECT COUNT(*), TYPE# FROM sys.obj$ GROUP BY TYPE#;

    Увидим, что данные возвращаюстся в неотсортированном виде. Посмотрим план запроса:

    ----------------------------------------------------------------
    | Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)|
    ----------------------------------------------------------------
    |   0 | SELECT STATEMENT |        |    47 |   141 |    54   (8)|
    |   1 |  HASH GROUP BY   |        |    47 |   141 |    54   (8)|
    |   2 |   INDEX FULL SCAN| I_OBJ1 |   107K|   315K|    50   (0)|
    ----------------------------------------------------------------
    
  2. Теперь установим внутренний параметр _gby_hash_aggregation_enabled в значение FALSE и попробуем выполнить наш запрос снова:

    -- Запретим HASH GROUP BY
    ALTER SESSION SET "_gby_hash_aggregation_enabled" = FALSE;
     
    SELECT COUNT(*), TYPE# FROM sys.obj$ GROUP BY TYPE#;

    Другое дело! Данные теперь возвращаются в отсортированном виде. Изменился и план запроса:

    ----------------------------------------------------------------
    | Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)|
    ----------------------------------------------------------------
    |   0 | SELECT STATEMENT |        |    47 |   141 |    54   (8)|
    |   1 |  SORT GROUP BY   |        |    47 |   141 |    54   (8)|
    |   2 |   INDEX FULL SCAN| I_OBJ1 |   107K|   315K|    50   (0)|
    ----------------------------------------------------------------
    

    Теперь группировка использует старый алгоритм SORT GROUP BY, который сортирует данные.

Мораль простая: не надо игнорировать рекомендации Oracle никогда не полагаться на естественный порядок выдачи строк. Единственный способ гарантировать порядок выдачи - абсолютно всегда использовать ORDER BY в запросе, даже если данные в таблице лежат строго в отсортированном порядке!
Если же есть в системе запросы с группировкой, полагающиеся на порядок выдачи строк, то при переходе с 9i на 10g и выше, надо использовать параметр _gby_hash_aggregation_enabled во избежание неприятностей.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre lang="" line="" escaped="" cssfile="">

(обязательно)

(обязательно)