Oracle mechanics

30.09.2011

Bug типа performance: попытка №2

Filed under: bugs,CBO,Optimizer features,Oracle,RBO — Игорь Усольцев @ 21:48
Tags: , , ,

Ранее (Oracle 11.2.0.1: баг типа PERFORMANCE – filtering join elimination) я уже описывал, как можно идентифицировать опцию оптимизатора, приводящую к неправильному результату запроса.

В очередной раз наблюдая за действиями специалистов Oracle Support, которые при поиске причин неправильного результата сложного запроса, несмотря на/в тонны засланных трейсов, пытаются наощупь угадать фичу/опцию/версию оптимизатора, ответственную за проблему, расширил и дополнил процедуру, автоматизирующую поиск версии оптимизатора (определяемой параметром OPTIMIZER_FEATURES_ENABLE) и конкретной фичи (FIX_CONTROL), приводящих к неправильному результату запроса:

SQL> set serveroutput on
 SQL>
 SQL> declare
 2       v_datasource_id number;              -- problem query resultset
 3       v_outlet number;                     -- problem query resultset
 4       v_def_opt_feature varchar2(255);     -- current OPTIMIZER_FEATURES_ENABLE value
 5       v_err_opt_feature varchar2(255);     -- errorless OPTIMIZER_FEATURES_ENABLE value
 6 begin
 7 -- saving OPTIMIZER_FEATURES_ENABLE default values
 8    select value into v_def_opt_feature from v$parameter_valid_values
 9    where name = 'optimizer_features_enable' and ISDEFAULT='TRUE';
 10 -- finding OPTIMIZER_FEATURES_ENABLE with correct query results
 11 for freco in (select value from v$parameter_valid_values
 12               where name = 'optimizer_features_enable'
 13               order by ORDINAL desc)
 14 loop
 15    execute immediate 'alter session set optimizer_features_enable=''' || freco.value || '''';
 16    execute immediate 'select /*QUERY WITH ERROR RESULTS*/ ...'
 17                       into v_datasource_id, v_outlet;
 18    if v_outlet = 1 then -- check the results correctness
 19       v_err_opt_feature := freco.value;
 20       dbms_output.put_line('optimizer_features_enable = ''' || freco.value || ''' works without errors');
 21       exit;
 22    end if;
 23 end loop;
 24 -- restoring default OPTIMIZER_FEATURES_ENABLE
 25 execute immediate 'alter session set optimizer_features_enable=''' || v_def_opt_feature || '''';
 26 -- looking for the guilty optimizer FIX_CONTROL
 27 for reco in (select bugno from v$session_fix_control
 28              where session_id = sys_context('USERENV', 'SID')
 29              and optimizer_feature_enable in (select value from v$parameter_valid_values
 30                                               where name = 'optimizer_features_enable' and ORDINAL >
 31                                                    (select ORDINAL from v$parameter_valid_values
 32                                                     where name = 'optimizer_features_enable'
 33                                                     and value = v_err_opt_feature))
 34              and value = 1)
 35 loop
 36    execute immediate 'alter session set "_fix_control"=''' || reco.bugno || ':OFF''';
 37    execute immediate 'select /*QUERY WITH ERROR RESULTS*/ ...'
 38                       into v_datasource_id, v_outlet;
 39    if v_outlet = 1 then -- check the results correctness
 40       dbms_output.put_line(reco.bugno || ' is the reason of query results error');
 41    end if;
 42    execute immediate 'alter session set "_fix_control"=''' || reco.bugno || ':ON''';
 43 end loop;
 44 end;
 45 /

optimizer_features_enable = '11.1.0.6' works without errors
6776808 is the reason of query results error

Процедура (имени Oracle Support :) в этом случае отработала успешно, проверяю найденный результат:

SQL> select /*QUERY WITH ERROR RESULTS*/ ...;

DATASOURCE_ID     OUTLET
------------- ----------
            1          0 -- неправильный результат

Elapsed: 00:00:03.62
SQL> alter session set "_fix_control"='6776808:OFF';

Session altered.

SQL> select /*QUERY WITH ERROR RESULTS*/ ...;

DATASOURCE_ID     OUTLET
------------- ----------
            1          1 -- правильный результат

Elapsed: 00:00:03.06

Отлично, конкретная фича оптимизатора, ответственная за неправильный рез-т запроса найдена:

SQL> select bugno, sql_feature, description, optimizer_feature_enable, is_default
2  from v$system_fix_control where bugno = 6776808
3  /

BUGNO    SQL_FEATURE        DESCRIPTION                                                 OPTIMIZER_FEATURE_ENABLE  IS_DEFAULT
-------- ------------------ ----------------------------------------------------------- ------------------------- ----------
6776808  QKSFM_SVM_6776808  allow view merging for inline views defined in WITH clause  11.1.0.7                           1

— остаётся попробовать найти параметр оптимизатора, управляющий найденным фиксом, чтобы иметь возможность исключить баг не только на уровне сессии/системы, но и на уровне отдельного запроса (что может быть важно). Ищем синтаксически подходящие параметры:

SQL> @param_ view_merg

NAME                                       VALUE  IS_DEF   IS_MOD  IS_ADJ   DSC
------------------------------------------ ------ -------- ------- -------- ---------------------------------------------------------------
_complex_view_merging                      TRUE   TRUE     FALSE   FALSE    enable complex view merging
optimizer_secure_view_merging              FALSE  FALSE    FALSE   FALSE    optimizer secure view merging and predicate pushdown/movearound
_simple_view_merging                       TRUE   TRUE     FALSE   FALSE    control simple view merging performed by the optimizer

Параметр optimizer_secure_view_merging относится к несколько другой области, а вот _complex_view_merging_simple_view_merging можно попробовать изменить на уровне запроса и проверить результат:

SQL> select /*+ opt_param('_complex_view_merging' 'false') */ ...;

DATASOURCE_ID     OUTLET
 ------------- ----------
            1          1 -- правильный результат
Elapsed: 00:00:03.03
SQL> select /*+ opt_param('_simple_view_merging' 'false') */ ...;

DATASOURCE_ID     OUTLET
 ------------- ----------
            1          1 -- правильный результат
Elapsed: 00:00:03.01

— оба параметра (тут не важны различия механизмов, контролируемых этими параметрами) приводят к правильному результату, отключая фичу view merging for inline views defined in WITH clause, и судя по времени выполнения — планы запросов не сильно пострадали от отключения этих фич

Ну и как ещё один вариант получения правильного результата — использовать Rule-Based Optimization, что неоптимально по скорости, но обычно гарантирует верный результат :)

SQL> select/*+ rule*/ ...;

DATASOURCE_ID     OUTLET
 ------------- ----------
            1          1 -- правильный результат
Elapsed: 00:00:03.73

1 комментарий »

  1. Кроме проблем с неправильными результатами запросов, эта же процедура может быть полезна для борьбы с другими ошибками, связанными с фичами оптимизатора, например ORA-00600: internal error code, arguments: [15160]

    Практическическая иллюстрация:

    Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0
    Connected as SYS

    SQL> select -- some problem SQL
    ...
    49 /

    ORA-00600: internal error code, arguments: [15160], [], [], [], [], [], [], [], [], [], [], []

    Поскольку запрос успешно выполняется с подсказками /*+ RULE */ и /*+ NO_QUERY_TRANSFORMATION */, пробуем найти конкретную фичу оптимизатора, ответственную за проблему с помощью модификацированного скрипта:

    SQL> set serveroutput on
    SQL> declare -- модифицированная вышеописанная pl/sql процедура
    ...
    150 /

    optimizer_features_enable = '10.2.0.5' works without errors
    5505995 is the reason of query results error

    PL/SQL procedure successfully completed

    Проверяем результат:

    SQL> alter session set "_fix_control"="5505995:OFF";

    Session altered

    SQL> select -- some problem SQL
    ...
    49 /

    no errors

    комментарий от Igor Usoltsev — 14.11.2011 @ 11:47 | Ответить


RSS feed for comments on this post. TrackBack URI

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

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Блог на WordPress.com.

%d такие блоггеры, как: