Oracle mechanics

01.06.2018

12.2: ORA-979 при разборе запросов с использованием WITH clause, PLSQL function и View Merging

Filed under: 12.2,bugs,Oracle — Игорь Усольцев @ 10:25
Tags: ,

Евгений Калинин обнаружил запрос, факт успешности выполнения которого неожиданно зависел от использования/неиспользования столбцов Inline view(*) а качестве параметров PLSQL_FUNCTION, и худшем случае завершавшийся простой ошибкой:

12.2.0.1.@ SQL> with s_q_by_month as
  2   (select contract_id,
...
 19     group by contract_id,
...
 30  s_q as
 31   (select plsql_function(sum_rub, -- вот этих столбцов *
 32                          max_amt,
...
 36      from (select contract_id,    -- вот этого Inline view
...
 43                   max(amt) as max_amt,
 44                   sum(amt_rub) as sum_rub,
...
 48              from s_q_by_month
 49             group by contract_id,
...
 56  select
 57   * from s_q
 58  /
 * from s_q
          *
ERROR at line 57:
ORA-00979: not a GROUP BY expression

, возникавшей на этапе построения плана, обрывая трассировку где-то в районе Query Transformation — в данном случае трейс заканчивался упоминанием OJE(Outer Join Elimination)

Соответствующая трассировка по номеру ошибки, почерпнутая из Bug 21799456 : ORA-979 ERROR GENERATED FROM A SELECT STATEMENT IN 12C:

SQL> alter session  set events '979 trace name ERRORSTACK level 3';

Session altered.

даёт любопытный Wait Stack:

    Session Wait History:
        elapsed time of 0.933288 sec since last wait
     0: waited for 'PGA memory operation'
        =0x10000, =0x1, =0x0
        wait_id=239 seq_num=262 snap_id=1
        wait times: snap=0.000006 sec, exc=0.000006 sec, total=0.000006 sec
        wait times: max=infinite
        wait counts: calls=0 os=0
        occurred after 0.000512 sec of elapsed time
     1: waited for 'PGA memory operation'
        =0x10000, =0x1, =0x0
        wait_id=238 seq_num=261 snap_id=1
        wait times: snap=0.000006 sec, exc=0.000006 sec, total=0.000006 sec
        wait times: max=infinite
        wait counts: calls=0 os=0
        occurred after 0.000757 sec of elapsed time
     2: waited for 'PGA memory operation'
        =0x10000, =0x1, =0x0
        wait_id=237 seq_num=260 snap_id=1
        wait times: snap=0.000005 sec, exc=0.000005 sec, total=0.000005 sec
        wait times: max=infinite
        wait counts: calls=0 os=0
        occurred after 0.000464 sec of elapsed time
     3: waited for 'PGA memory operation'
...

— указывающий на неотносящуюся к ORA-979 особенность/проблему версии 12.2 — Sql Waiting on ‘PGA Memory Operation’ (Doc ID 2393515.1):

CAUSE
The wait event can be misleading. The session actually is utilizing CPU executing a SQL. -- [OR utilizing CPU parsing a SQL, in this case]

SOLUTION
Most likely SQL needs to be tuned...

По рассматриваемой проблеме получается следующий Call Stack Trace:

qecgoc <- qecsel <- qecpqbcheck  <- qecpqbcheckNSE <- kkqtutlCheckGroupBy <- kkqvmTrMrg

, более характерный для багов обновления 11.2.0.1 -> 11.2.0.2 типа:

Bug 13328418 : ORA-979: NOT A GROUP BY EXPRESSION IN 11.2.0.2.0

DIAGNOSTIC ANALYSIS:
--------------------
The workaround from bug 9824198 resolves the error:
 alter session set "_fix_control"= '5520732:off';

, либо

Bug 9824198 - ORA-979 "not a group by expression" occurs on 11.2 (Doc ID 9824198.8) -- https://support.oracle.com/epmos/faces/DocContentDisplay?id=9824198.8

Description
ORA-979 can occur for a complex query involving a view 
with a subquery in select list and an GROUP BY clause.
 
Workaround
 Workarounds can vary :
 you would have to  set for sure:
  alter session set "_optimizer_enhanced_filter_push"=false;
  alter session set "_fix_control"= '5520732:off';
 
 also may need to set "_complex_view_merging"=false;
 or
 alter session set "_fix_control"= '599680:off';

И действительно, отключения указанного fix_control:

SQL> @fix 5520732
 
  BUGNO  VALUE SQL_FEATURE        DESCRIPTION                              OPTIMIZER_FEATURE_ENABLE   EVENT IS_DEFAULT     CON_ID
------- ------ ------------------ ---------------------------------------- ------------------------- ------ ---------- ----------
5520732      1 QKSFM_SVM_5520732  light check of gby validity in subquery  11.2.0.1                       0          1          3

оказалось достаточно для избавления от ORA-979 в процессе Parse:

12.2.0.1.@ SQL> with s_q_by_month as
...
 56  select--+ opt_param('_fix_control' '5520732:0')
 57   * from s_q
 58  /

2 rows selected

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dctwmgpkcww70, child number 0
-------------------------------------

Plan hash value: 1482192663

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                                |       |       |       |  3193 (100)|          |        |      |            |
|   1 |  TEMP TABLE TRANSFORMATION                |                                |       |       |       |            |          |        |      |            |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D813F_A03B664E    |       |       |       |            |          |        |      |            |
...
| 311 |                      PX SEND HASH         | :TQ40008                       | 65292 |  3251K|       |    55   (0)| 00:00:01 |  Q4,08 | P->P | HASH       |
| 312 |                       VIEW                |                                | 65292 |  3251K|       |    55   (0)| 00:00:01 |  Q4,08 | PCWP |            |
| 313 |                        PX BLOCK ITERATOR  |                                | 65292 |  3634K|       |    55   (0)| 00:00:01 |  Q4,08 | PCWC |            |
|*314 |                         TABLE ACCESS FULL | SYS_TEMP_0FD9D8144_A03B664E    | 65292 |  3634K|       |    55   (0)| 00:00:01 |  Q4,08 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------

— решение из 11.2 неожиданно успешно в версии 12.2!

С другой стороны, вспоминая, что упомянутые выше баги, исцелявшиеся отключением fix 5520732 были характерны для перехода/обновления 11.2.0.1 -> 11.2.0.2, можно попробовать в качестве лечения отмотать время назад, подменив OFE:

12.2.0.1.@ SQL> with s_q_by_month as
...
 56  select--+ OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
 57   * from s_q
 58  /

2 rows selected

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  bqy3268603bkp, child number 1
-------------------------------------

Plan hash value: 4084753027

— и это также работает!

С третьей стороны, можно почитать богатый на описание багов документ OERR: ORA 979 not a GROUP BY expression (Doc ID 18571.1) в части версии 12.2 и найти очень похожий по описанию проблемы Bug 25643889 — Query Raises ORA-979 Error During View Merging (Doc ID 25643889.8):

Description
With clause with plsql function in select list raises ORA-979 error. -- With clause + plsql function = наш случай
  
Rediscovery Notes
 With clause with plsql function in select list raises ORA-979 error. Teh same query works in previous releases
 
Workaround
use a NO_MERGE hint

, и опять же, глобальное отключение VIEW MERGING на уровне запроса параметром «_SIMPLE_VIEW_MERGING»=’FALSE’ (который, стоит отметить, в виде OPT_PARAM(‘_SIMPLE_VIEW_MERGING’ ‘FALSE’) в Outline Data плана не попадает, вероятно, поскольку относится только к фазе Parse) — поскольку, как видно по длине плана выполнения — хинтов не наставишься, опять же исправляет ошибку:

12.2.0.1.@ SQL> with s_q_by_month as
...
 56  select--+ OPT_PARAM('_SIMPLE_VIEW_MERGING' 'FALSE')
 57   * from s_q
 58  /

2 rows selected

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID  638qc8sc3hk0g, child number 4
-------------------------------------

Plan hash value: 3425440851

и всё бы хорошо, но

The fix for 25643889 is first included in	12.2.0.1.170718 (Jul 2017) Database Release Update (DB RU)

, а все эксперименты мы проводим на:

Patch description:  "Database Release Update : 12.2.0.1.171017 (26710464)"
   Created on 12 Oct 2017, 02:31:27 hrs PST8PDT

— т.е., вероятно, столкнулись с другим либо неопознанным, либо циркулирующим между версиями 11.2.0.2 -> 12.2.0.1 багом и пора обращаться в поддержку

Реклама

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

Комментариев нет.

RSS feed for comments on this post. TrackBack URI

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

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

Логотип WordPress.com

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

Google+ photo

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

Фотография Twitter

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

Фотография Facebook

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

Connecting to %s

Блог на WordPress.com.

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