Oracle mechanics

17.10.2016

12c: Wrong Results, параллельное выполнение и OPTIMIZER_FEATURES_ENABLE

Filed under: Oracle,parameters,PX,Scripts — Игорь Усольцев @ 23:49
Tags: ,

В процессе разбора очередного случая с неправильными рез-том в Oracle 12.1.0.2.*, на этот раз при параллельном выполнении запроса, и предварительно проверив варианты, перечисленные в How to Narrow Down Wrong Results Issues from Parallel Execution (Doc ID 1340246.1), по предложению представителя поддержки, проверил относительно новый функционал — DBMS_SQLDIAG с ключом PROBLEM_TYPE_WRONG_RESULTSHow to use DBMS_SQLDIAG to Assist Diagnosis of Wrong Results Issues (Doc ID 1492650.1):

SQL> declare
declare
  2  l_sql_diag_task_id  varchar2(100);
  3  begin
  4      l_sql_diag_task_id :=  dbms_sqldiag.create_diagnosis_task (
  5        sql_id => '67z9jx22r5sx8',
  6        problem_type => dbms_sqldiag.PROBLEM_TYPE_WRONG_RESULTS,
  7        task_name => 'Test_WR_diagnostic_task' );
  8      dbms_sqltune.set_tuning_task_parameter(
  9        l_sql_diag_task_id,
 10        '_SQLDIAG_FINDING_MODE',
 11        DBMS_SQLDIAG.SQLDIAG_FINDINGS_FILTER_PLANS);
 12  end;
 13  /
/
 
PL/SQL procedure successfully completed

SQL> exec dbms_sqldiag.execute_diagnosis_task ( task_name  => 'Test_WR_diagnostic_task' )
 
PL/SQL procedure successfully completed
 
SQL> select dbms_sqldiag.report_diagnosis_task ('Test_WR_diagnostic_task')   as recommendations  from dual;

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : Test_WR_diagnostic_task
Tuning Task Owner  : APPS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 09/23/2016 13:47:24
Completed at       : 09/23/2016 14:16:36

-------------------------------------------------------------------------------
Schema Name: APPS
SQL ID     : 67z9jx22r5sx8
SQL Text   : select count(*) from (
                               (select /*+ parallel(8)*/ trunc(sysdate),...
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Patch Finding (see explain plans section below)
------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation
  --------------
  - Consider accepting the recommended SQL patch.
    execute dbms_sqldiag.accept_sql_patch(task_name =>
            'Test_WR_diagnostic_task', task_owner => 'APPS', replace => TRUE);

  Rationale
  ---------
    Recommended plan with hash value 1517915354 has number of rows 1, check
    sum 1017476723, execution time 35176 and 5432190 buffer gets

-------------------------------------------------------------------------------

— и даже получил результат определённый рез-т(!), что само по себе порадовало, в отличие от содержимого сгенерированного патча:

SQL> select distinct attr5 from DBA_ADVISOR_RATIONALE where task_name = 'Test_WR_diagnostic_task';
 
ATTR5
--------------------
NOPARALLEL

, в чём легко удостовериться, применив патч к проблемному запросу:

SQL> exec dbms_sqldiag.accept_sql_patch(task_name => 'Test_WR_diagnostic_task', task_owner => 'APPS', replace => TRUE)
 
PL/SQL procedure successfully completed
 
SQL> @spm_check4sql_id 67z9jx22r5sx8
 
SIGNATURE             SPM_TYPE          SQL_HANDLE                     PATCH_NAME                     ORIGIN         VERSION    CREATED             LAST_MODIFIED       LAST_EXECUTED       LAST_VERIFIED       ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE
--------------------- ----------------- ------------------------------ ------------------------------ -------------- ---------- ------------------- ------------------- ------------------- ------------------- ------- -------- ----- ---------- ---------
  7972662918300275522 SQL Patch         SQL_6ea496aee7379342           SYS_SQLPTCH_015757254e000000   MANUAL-LOAD    12.1.0.2.0 23.09.2016 16:04:05 23.09.2016 16:04:05                                         YES     NO       NO    YES        NO
 
SQL> -- и посмотрев содержимое
SQL> @sqlpatch_hints SYS_SQLPTCH_015757254e000000
 
SQL_PATCH_HINTS
--------------------
NOPARALLEL

Кроме того, что синтаксис NOPARALLEL является deprecated по крайней мере с версии 11.2, предложенный способ исправления рез-тов запроса оказался настолько же самоочевиден, насколько и абсолютно бесценен — но чувство юмора у разработчиков на уровне:), а я то романтично надеялся, что чудо пакет DBMS_SQLDIAG последовательно и монотонно переберёт используемые параметры оптимизатора и FIX_CONTROL-ы и найдёт-таки «корень зла» — увы…

Пока представитель поддержки проявлял довольно бесполезную активность, хаотично подбрасывая «на пробу» разнонаправленные патчи/баги по тегу #px_wrong_results, элементарная проверка показала, что баг определённо связан со значением OFE, и при OPTIMIZER_FEATURES_ENABLE = 11.2.* не воспроизводится

Оставалось выяснить точное наименование новой фичи параллельного выполнения (_px_) / параметра оптимизатора, ответственной за wrong results, например, воспользовавшись сравнительным списком из diff-а 10053 трейсов запроса с разными OFE, например, между версиями 11.2.0.3 и 12.1.0.2:

$ diff CLONE_ora_3277_OFE_11203.trc CLONE_ora_3277_OFE_12102.trc | grep "_px_" | sort | uniq
> _px_adaptive_dist_method            = choose
< _px_adaptive_dist_method            = off
< _px_concurrent                      = false
> _px_concurrent                      = true
< _px_cpu_autodop_enabled             = false
> _px_cpu_autodop_enabled             = true
< _px_external_table_default_stats    = false
> _px_external_table_default_stats    = true
< _px_filter_parallelized             = false
> _px_filter_parallelized             = true
< _px_filter_skew_handling            = false
> _px_filter_skew_handling            = true
< _px_groupby_pushdown                = choose
> _px_groupby_pushdown                = force
< _px_join_skew_handling              = false
> _px_join_skew_handling              = true
< _px_object_sampling_enabled         = false
> _px_object_sampling_enabled         = true
< _px_parallelize_expression          = false
> _px_parallelize_expression          = true
> _px_partial_rollup_pushdown         = adaptive
< _px_partial_rollup_pushdown         = off
< _px_replication_enabled             = false
> _px_replication_enabled             = true
< _px_scalable_invdist                = false -- added in 11.2.0.4
> _px_scalable_invdist                = true  -- added in 11.2.0.4
< _px_single_server_enabled           = false
> _px_single_server_enabled           = true
> _px_wif_dfo_declumping              = choose
< _px_wif_dfo_declumping              = off
< _px_wif_extend_distribution_keys    = false
> _px_wif_extend_distribution_keys    = true

и простым перебором отпеделить «виновный» параметр:

SQL> @param_ _px_single_server_enabled
 
NAME                       VALUE  DSC
-------------------------- ------ ----------------------------------------
_px_single_server_enabled  TRUE   allow single-slave dfo in parallel query -- Data Flow Operator

, отключение которого решает проблему и, возможно, указывает на Bug 23047460 Wrong Results from «PX SEND 1 SLAVE» and COUNT — хотя трейс оптимизатора не содержит характерного PX SEND 1 SLAVE и план выполнения не использует операцию COUNT STOPKEY — точнее можно будет сказать после установки патча, с которым тоже вышел казус: как оказалось, для некоторых специалистов поддержки возможность применения Proactive Bundle Patch для не-exadata систем оказалась откровением, требующим подтверждений от каких-то неведомых неторопливых разработчиков — но ровно до того момента, пока я в качестве аргумента не привёл ссылку на извесный блог непосредственного руководителя этих спец-ов Mike Dietrich — после чего оказалось, что это — любимый блог всех без исключения сотрудников техподдержки и, конечно же, все сомнения были мгновенно были исчерпаны)))

Далее, поскольку diff 10053 трейсов — не самый красивый способ поиска различий параметров для отличных OFE, в блоге Oracle Optimizer можно найти готовый скрипт, выводящий списки OFE-related fix controls и OFE-related parameters (из SYS.X$KSPPI), добавленных или измененных между сравниваемыми версиями

Учитывая то, что Setting a Session Parameter Overrides OFE (but Hints OPTIMIZER_FEATURES_ENABLE are different because they override session settings!) сравнение имеет смысл проводить на «чистых» тестовых системах

2 комментария »

  1. Игорь, добрый день!

    для некоторых специалистов поддержки возможность применения Proactive Bundle Patch для не-exadata систем оказалась откровением, требующим подтверждений от каких-то неведомых неторопливых разработчиков

    У меня не Exadata и DBBP тоже использую. Часто приходилось в SR проходить по одному сценарию :)

    Инженер OSS: у вас не Exadata?
    Я: Нет
    Инженер: точно не Exadata и не Supercluster? Давайте opatch посмотрим
    Я: приложен при заведении SR

    Как-то раз после «Awaiting Internal Response» ответили вот такое:

    I had checked with Development team and while looking for the inventory they mentioned, that if your system is not exadata or supercluster then the following patch was not suppose to be installed
    ..
    Database Bundle Patches are only for Exadata, SAP or Windows environment which is not in your case. They also mentioned that in future this could cause lot of troubles.

    На что я ответил: «Oracle Database — Overview of Database Patch Delivery Methods ( Doc ID 1962125.1 )»
    https://support.oracle.com/epmos/faces/ui/km/DocContentDisplay.jspx?id=1962125.1

    Где указано:

    Can I use «Database Proactive Bundle Patch» (12.1.0.2+) on non-Exadata systems?
    Yes. This patch is applicable for non-Exadata systems.
    The fix information for the bundle may include some «Exadata» specific fixes but that is because the bundle is also applicable to Exadata systems.
    Please refer to Note 1937782.1 12.1.0.2 Database Proactive Bundle Patches / Bundle Patches for Engineered Systems and DB In-Memory — List of Fixes in each Bundle — List of Fixes in each Bundle for more information.

    PS: PDB не используете? А то решил размер PDB ограничить, а не выходит каменный цветок: https://community.oracle.com/thread/3982127

    комментарий от Mikhail Velikikh — 18.10.2016 @ 09:33 | Ответить

    • Спасибо за развёрнутый комментарий, Михаил! — как видно, проблема с пониманием Proactive Bundle Patch [сотрудниками техподдержки] — совсем не редкость)

      Насчёт PDB — используем не очень активно, но у вас вполне убедительные тесты, могу лишь предположить, что фича MAXSIZE пока находится на декларативном уровне, и где-нибудь в 12.2+ будет реализована, учитывая то, что Non-CDB architecture of Oracle databases is DEPRECATED since Oracle Database 12.1.0.2, а в 12.2, как обещают, станет и вовсе DESUPPORTED

      комментарий от Игорь Усольцев — 18.10.2016 @ 15:29 | Ответить


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 такие блоггеры, как: