Oracle mechanics

14.09.2016

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

Filed under: heuristics,Oracle,Plan Management,Scripts — Игорь Усольцев @ 19:24
Tags: ,

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 уже не так просто заставить выполняться параллельно: (more…)

Реклама

18.02.2016

12c: эффекты Automatic Dynamic Sampling

Filed under: heuristics,Oracle,SQL Tuning — Игорь Усольцев @ 23:57
Tags: ,

Невысокая скорость выполнения запроса 8tx5kfa369rdb:

SQL> @shared_cu12_noxml 8tx5kfa369rdb 0
 
EXECS USERS_OPENING LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC CPU_PER_EXEC PARSES_PER_EXEC GETS_PER_EXEC READS_PER_EXEC UIO_PER_EXEC CONC_PER_EXEC PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS REOPT REOPT_HINTS ADAPT SPD_Valid SPD_Used  DS_LEVEL REASON#1 SQL_PLAN_BASELINE SQL_PATCH OUTLINE_CATEGORY SQL_PROFILE
----- ------------- -------------------- ------------------- ------------ ------------ --------------- ------------- -------------- ------------ ------------- --------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- -------- ----------------- --------- ---------------- -----------
 2374             9 2016-02-08/12:47:01  12.02.2016 18:33:56   1474675990   1032495912               1        304100           8512     43756160     340539863      4044237314          10748     6 Y          N          Y          N                  N                       12        0                                                                                   

— ср.время / ELA_PER_EXEC ~ 1474 сек., много CPU и Concurrency, что визуально ассоциируется с 9 (в данном примере) одновременно открытыми курсорами / [V$SQL.]USERS_OPENING при полном отсутствии адаптивных эффектов (столбцы USE_FEEDBACK_STATS, REOPT, REOPT_HINTS, ADAPT, SPD_Used и DS_LEVEL) и нежданное событие ожидания в топе AWR:

Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Total Wait       Wait   % DB Wait    
Event                                Waits Time (sec)    Avg(ms)   time Class   
------------------------------ ----------- ---------- ---------- ------ --------
DB CPU                                         181,6K              70.2         
latch: row cache objects        21,615,357      33,1K       1.53   12.8 Concurre -- тут
db file sequential read          4,369,250      23,5K       5.37    9.1 User I/O

судя по SQL Monitoring-у оказались связаны:) (more…)

30.01.2016

Подсказка INDEX и bitmap-операции с B-tree индексами

Filed under: heuristics,hints,Oracle — Игорь Усольцев @ 13:01
Tags:

in English

Причиной увеличения DB Time/Elapsed:

WORKLOAD REPOSITORY report for
Elapsed:               30.08 (mins)
DB Time:            1,035.38 (mins)
...

Snap Time           Load    %busy    %user     %sys    %idle  %iowait
--------------- -------- -------- -------- -------- -------- --------
15-Jan 08:00:06     26.7      N/A      N/A      N/A      N/A      N/A
15-Jan 08:30:10     48.4     69.7     65.4      3.2     30.3      6.5 -- *
15-Jan 09:00:13      6.2     33.0     30.2      2.3     67.0      3.5

— с соопутствующим скачком Load Average (*) был хорошо заметный в том же AWR-отчёте запрос:

SQL ordered by Elapsed Time    DB/Inst: ORCL/orcl1  Snaps: 321153-321154
        Elapsed                  Elapsed Time                                   
        Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id    
---------------- -------------- ------------- ------ ------ ------ -------------
        32,145.0         33,505          0.96   51.7   72.6     .0 c9cxc4hy2tbsa -- определённо этот
...
         9,798.9              0           N/A   15.8   45.7   45.9 1mbx3m11g17vn
...
         2,533.0          4,113          0.62    4.1   22.7   22.1 c8cx3cxf286d7

, выполнявшийся в проблемый период попеременно по 2-м разным планам согласно статистике V$SQL/V$SQL_SHARED_CURSOR: (more…)

26.07.2015

Одиночное выполнение SQL запроса с несколькими SQL_EXEC_ID и различными планами

Filed under: heuristics,Oracle,SQL Tuning — Игорь Усольцев @ 00:46
Tags: ,

В процессе совместного с Евгением Калининым тестирования с целью стандартизации/оптимизации времени выполнения запроса посчастливилось наблюдать диво дивное нечасто встречающееся событие: выполнение одного запроса очевидно затягивалось по причине периодической инвалидации используемого курсора, следом генеровался новый курсор (с новым планом) и начинал выполняться с начала, при этом со стороны запускающего приложения выполнение запроса не прерывалось, просто затянулось на 1,5 часа вместо обычных 20 минут:

12.1.0.2@ SQL> select nvl(qc_session_id, session_id) sid,
  2         nvl(qc_session_serial#, session_serial#) serial#,
  3         sql_exec_id,
  4         sql_child_number                    as CHILD,
  5         sql_plan_hash_value                 as PHV,
  6         sql_full_plan_hash_value            as FPHV,
  7         min(sample_time),
  8         max(sample_time),
  9         count(*)                            as ASH_ROWS,
 10         count(distinct session_id) - 1      as PX_COUNT,
 11         max(sample_time) - min(sample_time) as DURATION
 12    from v$active_session_history
 13   where module = 'sqlplus.exe'
 14     and sql_id = '5s0w4cubz5yyt'
 15     and sql_child_number >= 0
 16   group by nvl(qc_session_id, session_id),
 17            nvl(qc_session_serial#, session_serial#),
 18            sql_exec_id,
 19            sql_child_number,
 20            sql_plan_hash_value,
 21            sql_full_plan_hash_value
 22   order by max(sample_time)
 23  /
 
 SID SERIAL# SQL_EXEC_ID CHILD        PHV       FPHV MIN(SAMPLE_TIME)       MAX(SAMPLE_TIME)       ASH_ROWS PX DURATION
 --- ------- ----------- ----- ---------- ---------- ---------------------- ---------------------- -------- -- -------------------
  43    2250    16777216     0 1177121823 1348556722 21.07.15 14:56:58,291  21.07.15 15:15:56,736      3802  8 +000000000 00:18:58
  43    2250                 1  598640444 1944960939 21.07.15 15:15:57,736  21.07.15 15:15:58,736         2  1 +000000000 00:00:01
  43    2250                 1 2027645636 2114082327 21.07.15 15:15:59,736  21.07.15 15:16:13,736        15  0 +000000000 00:00:14
  43    2250                 1 2864496871 3345652639 21.07.15 15:16:14,736  21.07.15 15:16:27,736        14  0 +000000000 00:00:13
  43    2250                 0 1852732831 4122082463 21.07.15 15:16:28,736  21.07.15 15:16:28,736         1  0 +000000000 00:00:00
--43    2250                 1          0          0 21.07.15 15:54:16,636  21.07.15 15:54:30,646        15  0 +000000000 00:00:14
--43    2250                 3 3013780789 4105931067 21.07.15 15:55:48,694  21.07.15 15:56:00,694        13  0 +000000000 00:00:12
--43    2250                 2  667538297 1913179438 21.07.15 15:56:28,694  21.07.15 15:58:23,740         4  2 +000000000 00:01:55
--43    2250                 2 3013780789 4105931067 21.07.15 15:55:18,674  21.07.15 15:59:47,777       112  5 +000000000 00:04:29
--43    2250                 1  667538297 1913179438 21.07.15 15:27:52,024  21.07.15 16:00:50,807        16  5 +000000000 00:32:58
--43    2250                 1 3013780789 4105931067 21.07.15 15:27:53,024  21.07.15 16:01:17,807       332  6 +000000000 00:33:24
--43    2250                 0          0 4122082463 21.07.15 16:11:06,080  21.07.15 16:11:06,080         4  3 +000000000 00:00:00
  43    2250    16777217     0 1852732831 4122082463 21.07.15 15:16:29,736  21.07.15 16:11:06,080      7198  8 +000000000 00:54:36
  43    2250    16777218     0 2628977128  173302026 21.07.15 16:11:07,080  21.07.15 16:16:07,196       630  8 +000000000 00:05:00

(more…)

07.09.2014

Материализация XMLTYPE запросов

Filed under: error,heuristics,Oracle,XML DB — Игорь Усольцев @ 19:10
Tags: ,

Попытка выполнение стандартного запроса по извлечению текста подсказок (outline hint) из плана выполнения (V$SQL_PLAN.OTHER_XML) до версии 12.1.0.1 включительно выдаёт характерную при материализации подзапроса с XMLTYPE ошибку — XMLTYPE in a WITH Clause Fails with ORA-06512 & ORA-06502 (Doc ID 1640869.1):

11.2.0.3.@ SQL> with ol as
  2   (select--+ materialize
  3           plan_hash_value, b.hint
  4      from v$sql_plan m,
  5           xmltable('/other_xml/outline_data/hint' passing
  6                    xmltype(m.OTHER_XML) columns hint varchar2(4000) path '/hint') b
  7     where trim(OTHER_XML) is not null
  8       and rownum <= 2)
  9  select * from ol
 10  /
                  xmltype(m.OTHER_XML) columns hint varchar2(4000) path '/hint') b
                  *
ERROR at line 6:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1

(more…)

02.09.2014

Запросы к GV$-обзорам в RAC-системах, включая использование GV$-функций

Filed under: Fixed tables,heuristics,Oracle,RAC,SQL — Игорь Усольцев @ 00:03
Tags:

В кластерных системах Oracle 11g:

11.2.0.3@ SQL> @inst

INST_ID INSTANCE_NAME  VERSION    PLATFORM_NAME        DATABASE_STATUS DATABASE_ROLE    STATUS   OPEN_MODE
------- -------------- ---------- -------------------- --------------- ---------------- -------- ----------
1*      my_inst1       11.2.0.3.0 Linux x86 64-bit     ACTIVE          PRIMARY          OPEN     READ WRITE
2       my_inst2       11.2.0.3.0 Linux x86 64-bit     ACTIVE          PRIMARY          OPEN     READ WRITE

можно наблюдать небыстрое выполнение простых запросов к глобальным обзорам типа GV$SQL, GV$SQL_PLAN, например, по SQL_ID: (more…)

21.07.2014

JPPD в присутствии удалённой таблицы и View Merging

Filed under: CBO,heuristics,Oracle — Игорь Усольцев @ 00:50
Tags: ,

На продакшн системе версии 11.2.* наблюдал, как сам факт использования удалённой (remote) таблицы блокировал использование Join Predicate Push-Down (JPPD) в запросе следующего типа:

select t.char_column, analytic_view.max_id, analytic_view.max_char_column
  from t,
       t@SCOTT_LOOPBACK
         t2,
       (select id1,
               max(id2) keep(dense_rank first order by id1 desc) max_id,
               max(char_column) max_char_column
          from t
         group by id1) analytic_view
 where t.id1 = t2.id1
   and t.id2 = analytic_view.id1
   and t.id1 = 10

— несмотря на то, что удалённая таблица, казалось бы, никакими условиями запроса с inline view связана не была. Т.е. JPPD работает для локальной таблицы T2 и не работает для удалённой.
Использование аналитического запроса в inline view препятствием для применения JPPD не является

Тестовая схема для версий 11.2.0.3/12.1.0.1: (more…)

07.06.2014

Dynamic Sampling для индексов с отсутствующей статистикой

Filed under: CBO,heuristics,Oracle,statistics — Игорь Усольцев @ 18:12
Tags:

План удаления 80+ млн.строк радовал простотой и доступностью:

11.2.0.3.@ SQL> explain plan for
  2  DELETE FROM SYSTEM.DB_AUDIT_TRAIL_STORE
  3  /

------------------------------------------------------------------------------------------------
| Id  | Operation        | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT |                             |    83M|  2792M|    26   (0)| 00:00:01 |
|   1 |  DELETE          | DB_AUDIT_TRAIL_STORE        |       |       |            |          |
|   2 |   INDEX FULL SCAN| DB_AUDIT_TRAIL_STORE_ACTION |    83M|  2792M|    26   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

Получение более реалистичного плана через сбор/установку реальной статистики индекса очевидно, однако, возникает вопрос: почему в таком случае не используются разного рода динамические технологии, типа Dynamic Sampling/Dynamic Statistics (DS), к примеру, и можно ли заставить их работать?

Итак, после создания тестовой таблицы на версии 11.2.0.3: (more…)

01.06.2014

CBQT при создании/обновлении матвью в 12.1.0.1

Filed under: heuristics,mview,Oracle,Oracle 12c — Игорь Усольцев @ 22:13
Tags: , ,

Тема предыдущего поста про BEGIN_OUTLINE_DATA возникла во время проверки действительно нового и, не побоюсь этого слова, революционного фикса:

SQL> @fix 9852856
 
  BUGNO VALUE SQL_FEATURE          DESCRIPTION                 OPTIMIZER_FEATURE_ENABLE
------- ----- -------------------- --------------------------- ------------------------
9852856     1 QKSFM_CBO_3904125    Enable CBQT for MV refresh  12.1.0.1

, указанного Леонидом Борчуком при обсуждении неоднозначных результатов оптимизации запросов создания/полного обновления материализованных представлений в версиях Oracle 11.2

Это полезное улучшение могло было бы решить множество проблем, но радость была преждевременной (more…)

20.04.2014

Partition Push-Up при выполнении MIN/MAX операций

Filed under: heuristics,Oracle,Partitioning — Игорь Усольцев @ 00:44
Tags:

При наблюдении за замедлением выполнения запросов типа:

select * from 
(select max(max_value) supermax_value           -- этот подзапрос был оформлен в виде VIEW
          from (select max(x) max_value from t1 -- одна из таблиц партицирована RANGE PARTITION по искомому ключу
                union all
                select max(y)           from t2
                union all
                select max(z)           from t3
                union all
                ...)
)

обратил внимание на неожиданно простую «синтактическую чувствительность» запроса: если сам запрос выполняется удовлетворительно быстро:

11.1.0.7.@ SQL> select max(last_date) max_last_date
  2            from (select last_date
  3                    from (select max(showtime) last_date
  4                            from shows))
  5  /

MAX_LAST
--------
16.04.13

1 row selected.

Elapsed: 00:00:00.47 -- быстро

, то при добавлении обвязки типа SELECT * FROM (), характерной при использовании обзора, скорость резко падает:

SQL> select *  from (select max(last_date) max_last_date
  2                    from (select last_date
  3                            from (select max(showtime) last_date
  4                                    from shows)))
  5  /

MAX_LAST
--------
16.04.13

1 row selected.

Elapsed: 00:06:10.67 -- небыстро

При детальном рассмотрении оказалось, такое поведение характерно до версии Oracle 12c включительно, не относится к оптимизатору и плану выполнения, и, похоже, определяется на стадии выполнения механизмом PARTITION PUSHUP (more…)

Следующая страница →

Создайте бесплатный сайт или блог на WordPress.com.