Параллельное выполнение UNION ALL PUSHED PREDICATE и проблемы фиксации параллельных планов

I. Параллельное выполнение UNION ALL PUSHED PREDICATE

Иногда имеет смысл параллельное выполнение запроса с операцией UNION ALL PUSHED PREDICATE в плане, и если в 11g эту комбинацию воспроизвести просто — и на простом тесткейсе это выглядит так:

11.2.0.3.SCOTT@ SQL> select --+ PARALLEL(4) PUSH_PRED(d)
  2  * from scott.emp e,
  3        (select * from scott.dept
  4         union
  5         select * from scott.dept) d
  6  where d.deptno = e.deptno
  7    and e.job = 'SALESMAN'
  8  /

4 rows selected.

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |          |     7 |   476 |     5  (20)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                   |          |       |       |            |          |        |      |            | -- параллельное выполнение
|   2 |   PX SEND QC (RANDOM)             | :TQ10000 |     7 |   476 |     5  (20)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    NESTED LOOPS                   |          |     7 |   476 |     5  (20)| 00:00:01 |  Q1,00 | PCWP |            |
|   4 |     PX BLOCK ITERATOR             |          |       |       |            |          |  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL            | EMP      |     3 |   114 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   6 |     VIEW                          |          |     1 |    30 |     4  (50)| 00:00:01 |  Q1,00 | PCWP |            |
|   7 |      SORT UNIQUE                  |          |     2 |    40 |     4  (75)| 00:00:01 |  Q1,00 | PCWP |            |
|   8 |       UNION ALL PUSHED PREDICATE  |          |       |       |            |          |  Q1,00 | PCWP |            | -- операция UNION ALL PUSHED PREDICATE
|   9 |        TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    20 |     1   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 10 |         INDEX UNIQUE SCAN         | PK_DEPT  |     1 |       |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  11 |        TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    20 |     1   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 12 |         INDEX UNIQUE SCAN         | PK_DEPT  |     1 |       |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 4 because of hint

, то в Oracle 12c (возможно, как рез-т исправления Bug 14217009 : WRONG RESULTS FROM PARALLEL QUERY USING UNION ALL и ему подобных?) аналогичную операцию UNION ALL PUSHED PREDICATE уже не так просто заставить выполняться параллельно:

12c, адаптивный LGWR: ожидания log file sync и target log write size

Из более-менее традиционной картинки ожиданий log file sync:

12.1.0.2@ SQL> @ash_wait_tree "event = 'log file sync' and inst_id = 1"
 
LVL INST_ID BLOCKING_TREE     WAIT_CLASS  EVENT                         WAITS_COUNT SESS_COUNT AVG_WA
--- ------- ----------------- ----------- ----------------------------- ----------- ---------- ------
  1       1 (FOREGROUND)      Commit      log file sync                       10992       6879    130 -- польз.процессы
  1       1 (J...)            Commit      log file sync                          13         13      0 -- и Job-ы ожидают LGWR
  2       1   (LGWR)          Other       LGWR any worker group                4675          1    190 -- , висящий, восновном,
  2       1   (LGWR)          Other       target log write size                3880          1      4 -- на этих 3-х ожиданиях
  2       1   (LGWR)          Other       LGWR all worker groups                691          1    265 -- и облокируемый
  2       1   (LGWR)                      On CPU / runqueue                      61          1      0
...
  3       1     (LG..)        System I/O  log file parallel write              5064          1    217 -- LGWR-worker-ами (LG..)
  3       1     (LG..)        Other       LGWR worker group ordering            129          1    223
  3       1     (FOREGROUND)              On CPU / runqueue                       2          1      0
  4       1       (LG..)      System I/O  log file parallel write               129          1    347

— можно предположить, что для уменьшения log file sync достаточно сократить время/кол-во блокирующих ожиданий LGWR any worker group/LGWR all worker groups и target log write size, распределённых в этом случае приблизительно поровну

И если первая группа ожиданий очевидно «упирается» в кол-во/скорость работы LGWR-worker-ов, обозначенных как (LG..):

12c: Очередной пропущенный ORA-00979: not a GROUP BY expression — Bug 22338374

Евгений Калинин в очередной раз успешно сумел заставить Oracle выдать странное — на этот раз рез-т запроса (аналитических функции, CONNECT BY, SUBQUERY FACTORING, CASE, ANSI JOIN в комбинациях) с ошибкой в перечислении GROUP BY столбцов вместо ORA-00979 выдавал рез-ты, отличные от рез-тов правильного в части GROUP BY запроса, и, более того, зависел от применения или неприменения TEMP TABLE TRANSFORMATION в плане выполнения!

Столь странное поведение, очевидно, выходило за рамки приличий, пришлось/удалось сделать несложный тесткейс и заслать в оркест обратиться в техподдержку с SR-запросом Query with left outer join gives WRONG results instead of ORA-00979: not GROUP BY expression in 12c

Презентация Oracle 12c ADS

, представленная на Oracle Database Community Day 2016 (добавил комментарии на сером фоне):

Небольшое дополнение и иллюстрация:

12c: Wrong results в запросах и апрельский DB Bundle Patch

Коллега Александр Шведунов указал проблему:

SQL> select
  2   t1.id, t1.category
  3    from t1
  4   where t1.id in
  5         (select p_id from t2 where service_id = 11)
  6     and t1.firm_id in (111)
  7     and t1.category in ('yt', 'ytph')
  8  /

        ID CATEGORY
---------- --------------------
  11101013 ytph -- Wrong results for CATEGORY column
  11101014 ytph
  11101023 ytph
  11101036 ytph

4 rows selected.

SQL> select t1.id, t1.category
  2    from t1
  3   where t1.id in (11101013, 11101014, 11101023, 11101036)
  4  /
 
        ID CATEGORY
---------- --------------------
  11101013 yt   -- True results for CATEGORY column
  11101014 yt
  11101023 yt
  11101036 yt

Из плана ошибочного запроса:

12c: проблемы SQLID frjd8zfy2jfdq в ADG

Наблюдали с Русланом Бикбаевым на обычном, не-EXADATA RAC с ADG 12.1.0.2 проблему Bug 22364629 : EXADATA: ORA-29771: PROCESS USER BLOCKS LGWR FOR MORE THAN 70 SECONDS — казалось бы, мало ли в Oracle 12c багов?)

Тут интересно, что блокером для LGWR выступают пользовательские процессы (+PX-slaves), выполняющие известный* рекурсивный запрос frjd8zfy2jfdq, описанный в Bug 20413540 Excessive executions of SQL frjd8zfy2jfdq, являющемся base bug для Bug 22364629:

The query with SQLID frjd8zfy2jfdq is executed by Dynamic Statistics to figure out the past execution statistics and guide the time allowance for future execution of Dynamic Statistics

*) о том же запросе Рекурсивно-адаптивный SQL_ID «frjd8zfy2jfdq» версии 12.1.0.2

Детали проблемы и место SQLID frjd8zfy2jfdq расписаны в трейсе системного процесса LMHB:

Посказка BITMAP_TREE

Определение, приведённое ранее:

Usage: BITMAP_TREE([<@Block>] <Table> AND(<Index1>[ <Index2> ...]))
    or BITMAP_TREE([<@Block>] <Table> AND((<Index1 columns>)[ (<Index2 columns>) ...]))
Description: Instructs the optimizer to convert ROWIDs to bitmap, then performance bitmap operations

оказалось неполным, и в этом, как обычно, помогла проблема с планом критичного запроса типа:

SELECT *
  FROM dual
 WHERE EXISTS (SELECT *
          FROM (SELECT DISTINCT t_lines_1.*
                  FROM t_lines t_lines_1
                  JOIN t_lines
                    ON t_lines.group_lines_id = t_lines_1.id
                 WHERE t_lines.group_lines_id IS NOT NULL
                   AND t_lines_1.group_lines_id IS NULL
                   AND t_lines.service_id IN (:service_id_1, :service_id_2, :service_id_3, :service_id_4)                     -- [1]
                   AND (t_lines.person_id = :person_id_1 OR t_lines.client_id = :client_id_1 AND t_lines.person_id IS NULL))) -- [2]

, который, как обычно неожиданно, из 2-х планов выбрал худший (PHV 2429571734 — второй по счёту в нижеприведённом сравнении) — с использование комбинации двух BITMAP ROWIDS индексных пребразований, соответствующих 2-м вхождениям/использованиям оператора OR в запросе — в строках [1] и [2]

Синтаксическая ошибка в запросе, приводящая к бесконечному parsing-у

Наблюдали с коллегами запрос, часами не выходящий из фазы Hard Parse:

SQL> @ash_sqlmon2 81vm5p8sr91zr
 
LAST_PLSQL                SQL_ID        PLAN_HASH_VALUE   ID PLAN_OPERATION                    COST ASH_ROWS WAIT_PROFILE
------------------------- ------------- --------------- ---- --------------------------------- ---- -------- --------------------------------------------------------------------
Hard Parse                81vm5p8sr91zr               0    0 sql_plan_hash_value = 0                   32514 ON CPU(32514)
Main Query w/o saved plan 81vm5p8sr91zr               0                                                      
SQL Summary                                           0    0 ASH fixed 0 execs from 1 sessions         32514  ash rows were fixed from 01.06.2016 03:26:52 to 01.06.2016 12:29:00

— и так не сформировавшему за 8 часов плана выполнения!

Оказалось, что поведение запроса поменялось после незначительных изменений в тексте запроса (что ожидаемо и неудивительно), запрос «плотно висел» ON CPU, oradebug dump errorstack 3 стабильно указывал:

Топ I/O ожиданий/блоков/операций по записям ASH

AWR классифицирует запросы в части I/O по след.метрикам:

• SQL ordered by User I/O Wait Time — соответственно по User I/O Time, кол-ву ожиданий и потраченному DB Time, что соответствует распределению строк в ASH
• SQL ordered by Reads, отсортированному по Physical Reads (in blocks)
• SQL ordered by Physical Reads (UnOptimized), упорядоченному по кол-ву Physical Read Requsts ( с выденением UnOptimized), что крайне полезно при разборе проблем в части ввода/вывода

Набросал запрос для тех же метрик на основе записей ASH, добавив для полноты картины системные/Background Processes и возможности сортировки по WAITS (соответствует User I/O Wait Time в AWR), REQS (Physical Read Requsts) и BLOCKS (Physical Reads) с учётом отличия db file blocksize и log file blocksize, без учёта возможных различий blocksize разных db files

На примере одной OLTP бд, сортируя ASH по считанным/записанным блокам (в пересчёте на db file blocksize), можно видеть активную писательскую деятельность дочерних LG00:

Кластерные ожидания внутри одного инстанса RAC и количество LMS-процессов

Краткосрочный всплеск ожиданий типа Cluster:

Top 5 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Total Wait       Wait   % DB Wait    
Event                                Waits Time (sec)    Avg(ms)   time Class   
------------------------------ ----------- ---------- ---------- ------ --------
gc buffer busy acquire           1,157,219      59.3K      51.23   42.6 Cluster  
gc cr grant 2-way                2,838,562      24.8K       8.72   17.8 Cluster 
gc current grant busy              382,881      11.5K      30.05    8.3 Cluster 
DB CPU                                          10.6K               7.6         
gc current grant 2-way             141,095     8458.2      59.95    6.1 Cluster

был вызван сформирован* в бОльшей части двумя SQL с типом команд SELECT (без FOR UPDATE), выполнявшимися на 2-м инстансе: