Oracle mechanics

09.04.2019

Oracle 12.2: неоднозначность определения PLAN_HASH_VALUE

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

Начиная с версии 12.2 часто наблюдаю, как 2 плана выполнения с различными PHV используются для обозначения одного и того же фактического плана выполнения

Например, запрос в анамнезе показывает 2 несовпадающих PLAN_HASH_VALUE при эквивалентной паре (FULL_PLAN_HASH_VALUE, PLAN_HASH_2):

SQL>  @sql_plan_hashs cj1sg1hk1n3ct

SRC SQL_ID        FULL_PLAN_HASH_VALUE PLAN_HASH_VALUE PLAN_HASH_2 TIMESTAMP
--- ------------- -------------------- --------------- ----------- --------------------
AWR cj1sg1hk1n3ct           2164351587      2164351587  2164351587 26.02.2019 17:37:36
AWR cj1sg1hk1n3ct           2164351587      3400946657  2164351587 26.02.2019 17:29:31

При этом, PHV=2164351587 и PHV=3400946657 по содержимому совпадают абсолютно: (more…)

06.04.2019

Dynamic Sampling Longops

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

Запись V$SESSION_LONGOPS, в частности, поля MESSAGE, TOTALWORK для операций Dynamic Sampling выглядят очень серьёзно:

12.2.0.1@ SQL> select * from gv$session_longops where (inst_id, sid, serial#) in ((1,4974,54090));

  SID  SERIAL# OPNAME      TARGET           SOFAR  TOTALWORK UNITS   START_TIME           LAST_UPDATE_TIME     ELAPSED_SECONDS MESSAGE                                                            USERNAME  SQL_ADDRESS      SQL_HASH_VALUE SQL_ID        SQL_PLAN_HASH_VALUE SQL_EXEC_START SQL_EXEC_ID SQL_PLAN_LINE_ID SQL_PLAN_OPERATION  SQL_PLAN_OPTIONS  QCSID CON_ID
----- -------- ----------- ----------- ---------- ---------- ------- -------------------- -------------------- --------------- ------------------------------------------------------------------ --------- ---------------- -------------- ------------- ------------------- -------------- ----------- ---------------- ------------------- ----------------- ----- ------
 4974    54090 Table Scan  XO.PAYMENTS  278127738  278127738 Blocks  05.04.2019 17:59:00  05.04.2019 17:59:08                8  Table Scan:  XO.PAYMENTS: 278127738 out of 278127738 Blocks done  REPORTS   00000005FA90E240      896485255 98qwqt8uqyjw7                   0                                           2 TABLE ACCESS        SAMPLE                0      3

— при рекордно малой продолжительности ELAPSED_SECONDS = 8 для немаленькой таблицы — 278,127,738 Blocks — и только пустые SQL_PLAN_HASH_VALUE, SQL_EXEC_START, SQL_EXEC_ID да SQL_PLAN_OPTIONS выдают неладное)

В ASH та же операция Dynamic Sampling также отчётливо отображена:

SQL> select inst_id,
  2         session_id,
  3         session_serial#,
  4         sql_id,
  5         sql_plan_hash_value,
  6         sql_full_plan_hash_value,
  7         sql_exec_id,
  8         count(*),
  9         min(sample_time),
 10         max(sample_time)
 11    from gv$active_session_history
 12   where sql_id = '98qwqt8uqyjw7'
 13   group by inst_id,
 14            session_id,
 15            session_serial#,
 16            sql_id,
 17            sql_exec_id,
 18            sql_plan_hash_value,
 19            sql_full_plan_hash_value
 20  /

   INST_ID SESSION_ID SESSION_SERIAL# SQL_ID        SQL_PLAN_HASH_VALUE SQL_FULL_PLAN_HASH_VALUE SQL_EXEC_ID   COUNT(*) MIN(SAMPLE_TIME)       MAX(SAMPLE_TIME)
---------- ---------- --------------- ------------- ------------------- ------------------------ ----------- ---------- ---------------------- ---------------------
         1       4974           54090 98qwqt8uqyjw7           637899589               2462500783                      9 05.04.19 17:59:01,494  05.04.19 17:59:09,521 -- здесь
         1       4974           54090 98qwqt8uqyjw7           637899589               2462500783    16777216       2068 05.04.19 17:59:10,525  05.04.19 18:33:42,965

— те же 8 секунд / 9 записей ASH, в отличие от V$SESSION_LONGOPS с определённым SQL_PLAN_HASH_VALUE, но логично пустым SQL_EXEC_ID

Причина — форсированный через SQL Patch уровень DS:

SQL> @spm_check4sql_id 98qwqt8uqyjw7

SPM_TYPE   SQL_HANDLE            PATCH_NAME         ORIGIN         VERSION    CREATED             LAST_MODIFIED       LAST_EXECUTED LAST_VERIFIED ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE SPM_SIGNATURE         SQL_EXACT_SIGNATURE   SQL_FORCE_SIGNATURE
---------- --------------------- ------------------ -------------- ---------- ------------------- ------------------- ------------- ------------- ------- -------- ----- ---------- --------- --------------------- --------------------- ---------------------
SQL Patch  SQL_867bc1c7ebfabd0d  98qwqt8uqyjw7_DS6  MANUAL-SQLTUNE 12.2.0.1.0 05.04.2019 17:27:17 05.04.2019 17:27:17                             YES     NO       NO    YES        NO          9690552087619484941   9690552087619484941   1482752548320229273

SQL> @sqlpatch_hints 98qwqt8uqyjw7_DS6

SQL_PATCH_HINTS
--------------------
dynamic_sampling(6)

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor( '98qwqt8uqyjw7',format => 'all allstats advanced last'));
...
Note
-----
   - dynamic statistics used: dynamic sampling (level=6)
   - SQL patch "98qwqt8uqyjw7_DS6" used for this statement

10.03.2019

Ограничение DOP с помощью SQL Patch

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

Если вдруг в приступе доброты душевной разработчик решил не ограничивать себя в фантазиях потребление ресурсов и, например, в определении materialized view
(или в запросе) по-простому вставил /*+ PARALLEL */, и это породило не всегда приятный для администраторов DML вида:

/* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "SCOTT"."SOME_MATVIEW" select /*+ parallel */ ...

, который в полном соответствии с Automatic Degree of Parallelism (AutoDOP) is enabled when «PARALLEL_DEGREE_POLICY=MANUAL and PARALLEL HINT» is Used at SQL Statement (Doc ID 1542556.1) и по данным SQL MONITOR:

Parallel Execution Details (DOP=30 , Servers Requested=224 , Servers Allocated=60)

— запрашивает 224 PX процесса, но получает «лишь» 60 из 60 имеющихся:

12.2.0.1@ SQL> @param parallel_max_servers

NAME                  VALUE  DSC
--------------------- ------ -------------------------------------------
parallel_max_servers  60     maximum parallel query servers per instance

— простой SQL Patch:

SQL> @sqlpatch+ 15b35sdpa9rgg "PARALLEL(8)" "15b35sdpa9rgg_PARALLEL_8"

к счастью, приводит ситуацию в порядок: (more…)

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…)

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

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