Oracle mechanics

30.07.2019

Несовместимость и совместное использование SQL Patch и SQL Plan Baseline

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

Тесты с простым тестовым запросом проводились на версии 18c — там трассировка и комментарии Oracle полнее, хотя описываемые особенности известны и наблюдаемы, как минимум, с версии 12.1

Итак, для запроса одновременно созданы два элемента SPMSQL Patch и SQL Plan Baseline:

18.0.0.0.SCOTT@localhost/ORCLCDB SQL> @spm_check4sql_id 2rv6gr3ufpa78

SIGNATURE             SPM_TYPE          SQL_HANDLE                     PATCH_NAME                     ORIGIN         VERSION    CREATED             LAST_MODIFIED       LAST_EXECUTED       ENABLED ACCEPTED FIXED REPROD PURGE ADAPT
--------------------- ----------------- ------------------------------ ------------------------------ -------------- ---------- ------------------- ------------------- ------------------- ------- -------- ----- ------ ----- -----
 17843842952808529353 SQL Patch         SQL_f7a2109ff6027dc9           2rv6gr3ufpa78_hash             MANUAL-SQLTUNE 18.0.0.0.0 26.07.2019 02:25:34 26.07.2019 02:25:34                     YES     NO       NO    YES    NO    NO
 17843842952808529353 SQL Plan Baseline SQL_f7a2109ff6027dc9           SQL_PLAN_gg8hhmzv04zf916ed3edf UNKNOWN        18.0.0.0.0 25.07.2019 04:09:23 25.07.2019 04:09:29 25.07.2019 04:09:29 YES     YES      YES   YES    YES   NO

, Baseline фиксирует индексный доступ:

SQL> @bl12_hints SQL_PLAN_gg8hhmzv04zf916ed3edf

OUTLINE_HINTS
--------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('18.1.0')
DB_VERSION('18.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "O"@"SEL$1")
INDEX(@"SEL$1" "P"@"SEL$1" ("PRODUCT_INFORMATION"."PRODUCT_ID"))
LEADING(@"SEL$1" "O"@"SEL$1" "P"@"SEL$1")
USE_NL(@"SEL$1" "P"@"SEL$1")
NLJ_BATCHING(@"SEL$1" "P"@"SEL$1")

, в то время как SQL Patch настроен на Hash Join / Full Table Scan:

SQL> @sqlpatch_hints 2rv6gr3ufpa78_hash

SQL_PATCH_HINTS
-------------------------------------------------------------
FULL(@"SEL$1" "P"@"SEL$1") USE_HASH(@"SEL$1" "P"@"SEL$1")

(more…)

07.07.2019

Особенности, наблюдаемые при применении подсказки STATEMENT_QUEUING

Filed under: hints,Oracle,Plan Management — Игорь Усольцев @ 12:00
Tags: ,

Особенность первая

Проблема с непараллельным выполнением казалось бы гарантированно захинтованного запроса, SQL Monitor которого показывал:

SQL Text
------------------------------
select /*+ STATEMENT_QUEUING PARALLEL(16) */...

Global Information
------------------------------
 Status              :  DONE (ERROR)
...
 SQL ID              :  64mga9tafr17z
 SQL Execution ID    :  50331648
 Execution Started   :  07/01/2019 11:23:28
 First Refresh Time  :  07/01/2019 11:23:28
 Last Refresh Time   :  07/01/2019 13:33:25
 Duration (Queued)   :  7797s (83s)
...
 DOP Downgrade       :  100%                -- *
 Fetch Calls         :  1

Binds
...

Global Stats
==================================================================================================================================================
| Elapsed | Queuing |   Cpu   |    IO    | Application | Concurrency | Cluster  | Fetch | Buffer | Read | Read  | Write | Write |    Offload     |
| Time(s) | Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) | Calls |  Gets  | Reqs | Bytes | Reqs  | Bytes | Returned Bytes |
==================================================================================================================================================
|    8705 |      83 |    2573 |     1836 |        0.00 |        0.00 |     4213 |     1 |   647M |  11M |  42GB |  7387 |   2GB |            2GB |
==================================================================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=47594419)
=================================================================================================================================================================================================
| Id  |    Operation          |      Name      |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Write | Write |  Mem  | Temp  | Activity |         Activity Detail    |
|     |                       |                | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes | Reqs  | Bytes | (Max) | (Max) |   (%)    |           (# samples)      |
================================================================================================================================================================================================
|   0 | SELECT STATEMENT      |                |         |       |       100 |     +2 |     1 |        0 |      |       |       |       |     . |     . |     4.93 | cursor: pin S wait on X (8)| -- ***
|     |                       |                |         |       |           |        |       |          |      |       |       |       |       |       |          | library cache lock (303)   | -- ***
|     |                       |                |         |       |           |        |       |          |      |       |       |       |       |       |          | resmgr:pq queued (80)      | -- **
|   1 |   PX COORDINATOR      |                |         |       |         1 |    +84 |     1 |        0 |  576 |   2MB |       |       |     . |     . |     0.01 | Cpu (1)                    |
|   2 |    PX SEND QC (ORDER) | :TQ60024       |       1 |    2M |           |        |     1 |          |      |       |       |       |     . |     . |          |                            |
...
| 192 |     INDEX UNIQUE SCAN | SYS_C002853735 |       1 |       |           |        |       |          |      |       |       |       |     . |     . |          |                            |
=================================================================================================================================================================================================

(more…)

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

17.05.2018

12c: EBS R12 система с отключёнными адаптивными фичами / OPTIMIZER_ADAPTIVE_FEATURES = FALSE

Filed under: Oracle,Oracle 12c,Plan Management — Игорь Усольцев @ 18:01
Tags:

В докуметации Database Initialization Parameters for Oracle E-Business Suite Release 12 (Doc ID 396009.1) определённо(*) рекомендуется отключить адаптивные фичи:

#########
#
# Optimizer Parameters
#
# Release 12 uses cost based optimization. The following optimizer
# parameters must be set as shown, and should not be changed.
# It is recommended to disable the adaptive optimizer features: adaptive plans,
# automatic re-optimization, and SQL plan directives.
#
#########

optimizer_adaptive_features = FALSE #MP -- Mandatory parameters --*

— как указано, чтобы использовать «чистый» CBO, т.о. блокируя избыточную генерацию планов и общую нестабильность (см., например, 12c: адаптивная оптимизация и CBO), что и было давно выполнено на уровне системы:

12.1.0.2@ SQL> @param optimizer_adaptive_features
 
NAME                         VALUE  IS_DEF   IS_MOD     DSC
---------------------------- ------ -------- ---------- --------------------------
optimizer_adaptive_features  FALSE  FALSE    FALSE      controls adaptive features

, отключив Adaptive Plans, Automatic Re-Optimization / Statistics Feedback, and SQL plan directives как следствие

Однако, так просто условно-избыточная генерация планов выполнения не прекратилась: (more…)

14.12.2016

12c: наблюдаемые ограничения хранения в AWR и управления адаптивными планами

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

Для демонстрации/проверки многообразия планов у меня есть простой скрипт SQL_PLAN_HASHS.SQL, вытаскивающий хеши планов из AWR и/или SqlArea по SQL_ID [и, опционально, по FULL_PLAN_HASH_VALUE и/или PLAN_HASH_VALUE], и один планов запросов SQL_ID=fkw6hb5mrw02t и PHV=3887377781 а течение последних нескольких дней показывал следующую примечательную картину:

12.1.0.2.@ SQL> @sql_plan_hashs fkw6hb5mrw02t "" 3887377781
 
SRC SQL_ID        FULL_PLAN_HASH_VALUE PLAN_HASH_VALUE TIMESTAMP
--- ------------- -------------------- --------------- --------------------
SQL fkw6hb5mrw02t           1266372674      3887377781 12.12.2016 17:47:16
AWR fkw6hb5mrw02t           2002632649      3887377781 31.10.2015 23:32:38

— в SqlArea (SRC=SQL) и в DBA_HIST_SQL_PLAN (SRC=AWR) этот запрос сосуществует с одним и тем же PLAN_HASH_VALUE, но с разными FULL_PLAN_HASH_VALUE

При этом, если для «старого» FULL_PLAN_HASH_VALUE=2002632649 был зафиксирован лишь один PLAN_HASH_VALUE:

SQL> @sql_plan_hashs fkw6hb5mrw02t 2002632649
 
SRC SQL_ID        FULL_PLAN_HASH_VALUE PLAN_HASH_VALUE TIMESTAMP
--- ------------- -------------------- --------------- --------------------
AWR fkw6hb5mrw02t           2002632649      3887377781 31.10.2015 23:32:38

, то для относительно «свежего» FULL_PLAN_HASH_VALUE=1266372674 наблюдается два отличных PHV:

SQL> @sql_plan_hashs fkw6hb5mrw02t 1266372674
 
SRC SQL_ID        FULL_PLAN_HASH_VALUE PLAN_HASH_VALUE TIMESTAMP
--- ------------- -------------------- --------------- --------------------
AWR fkw6hb5mrw02t           1266372674       864598493 04.10.2016 12:37:20
SQL fkw6hb5mrw02t           1266372674      3887377781 12.12.2016 17:47:16

— как и положено для адаптивного плана
В этом ничего удивительного нет, и я уже описывал подобную ситуацию сосуществования адаптивного и НЕадаптивного планов с совпадающими PHV, однако, в рассматриваемом случае проявляется пара новых проблем:

1) план с FULL_PLAN_HASH_VALUE=1266372674 и PHV=3887377781 наблюдается только в SqlArea Shared Pool, и, повторюсь, в течение достаточно длительного времени не сохраняется в AWR (не может быть сохранен в AWR в принципе?)

2) собственно сосуществование (и возможность управления) 2-мя различающимися адаптивными планами одного простого (без использования обзоров) запроса с совпадающими PHV и различными FULL_PLAN_HASH_VALUE (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…)

30.03.2016

12c: Automatic Dynamic Statistics в присутствии SPM Baseline — 2. Testcases

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

В дополнение к предыдущему могут быть полезны простые testcase-ы поведения Oracle 12.1.0.2 при разборе / hard parse запроса с применением SQL Plan Baseline

Первый case был предложен/подготовлен коллегой Русланом Бикбаевым на противопоставлении Baseline-а,фиксирующего FULL TABLE SCAN и добавленным позднее индексом:

SQL> create table large_table (c1 number, c2 varchar2 (1000))
  2  /

Table created.

SQL> insert into large_table
  2    select level, 'some fool text and number ' || level
  3      from dual
  4    connect by level < 1000000 5 / 999999 rows created. SQL> exec dbms_stats.gather_table_stats('','LARGE_TABLE')

PL/SQL procedure successfully completed.

SQL> select c2 from large_table where c1 = 402200;

C2
---------------------------------------------------------------------------------
some fool text and number 402200

1 row selected.

SQL> @xplan "" "" "-predicate +alias"

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_ID  cgrtc39b07cg4, child number 0
-------------------------------------
select c2 from large_table where c1 = 402200

Plan hash value: 1101256009

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |       |       |  1643 (100)|          |
|   1 |  TABLE ACCESS FULL| LARGE_TABLE |     1 |    38 |  1643   (1)| 00:00:01 |
---------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / LARGE_TABLE@SEL$1

SQL> -- фиксация плана 
SQL> @bl_create cgrtc39b07cg4 1101256009 "2test_baseline"

Baseline SQL_ff24385815ccded7 SQL_PLAN_gy91sb0awtrqr07ba49cd was [re]created
for SQL_ID=cgrtc39b07cg4, SQL_PLAN_HASH=1101256009

SQL> create index lt_idx on large_table(c1)
  2  /

Index created.

SQL> select c2 from large_table where c1 = 402200;

1 row selected.

SQL> @xplan "" "" "-predicate +alias"

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_ID  cgrtc39b07cg4, child number 1
-------------------------------------
select c2 from large_table where c1 = 402200

Plan hash value: 1101256009

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |       |       |  1643 (100)|          |
|   1 |  TABLE ACCESS FULL| LARGE_TABLE |     1 |    38 |  1643   (1)| 00:00:01 |
---------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / LARGE_TABLE@SEL$1

Note
-----
   - SQL plan baseline SQL_PLAN_gy91sb0awtrqr07ba49cd used for this statement

, 10053 трейс которого отчётливо показывает все 3 фазы разбора: (more…)

02.03.2016

12c: данные SPM Baseline в SYS.SQLOBJ$PLAN

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

Ежели в версии 11g Oracle хранил данные всех элементов системы SQL Plan Mangement (SPM, включая SQL Patch, SQL Profile и SQL Plan Baseline (SPB)) в SYS.SQLOBJ$DATA.COMP_DATA, то, начиная с 12.1 данные, составляющие собственно Baseline, переместились в clob OTHER_XML новой таблицы SQLOBJ$PLAN, которая также содержит полный план выполнения на момент создания SPM Baseline-а

Этот сохранённый план может быть любопытен в иллюстративных целях, например, при просмотре через Enterprise Manager/Cloud Control, или через соответствующий command line скрипт SPB12.SQL представляет информацию в следующем виде: (more…)

17.02.2016

UPGRADE 12c: краткий список возможных проблем

Filed under: bugs,Oracle,Plan Management — Игорь Усольцев @ 22:37
Tags:

В блогах Oracle мелькнула заметка Mike Dietrich Some Parameter Recommendations for Oracle 12c, обобщающая некоторые проблемы обновления

Появилась и пропала (#гугльпомнит), вероятно, ввиду недостаточного оптимизма)

Однако, кроме описания различных случаев/багов и соответствующих параметров/workaround-ов, заметка содержала практически официальные описания параметров управления SQL Plan Directives (SPD): (more…)

13.01.2016

Oracle 12c Adaptive Soft Parse overhead

Логичным развитием темы предыдущей заметки будет оценка вклада типа Oracle 12c Adaptive Soft Parse overhead на систему в целом через подсчёт доли в общем DB Time (через кол-во ASH rows) «адаптивных» запросов в момент между генерацией плана и началом выполнения запроса (sql_plan_hash_value > 0 AND sql_exec_id is null)

Как-то так раскадка DB Time выглядит на примере системы (в бОльшей части OLTP типа) для sql типа SELECT:

SQL> select plan_hash_value,
  2         sql_exec_id,
  3         adaptive_plan,
  4         sum(ash_rows),
  5         listagg(session_state || '(' || ash_rows || ')', ';') within group(order by ash_rows desc) as DB_TIME_WASTE,
  6         to_char(RATIO_TO_REPORT(sum(ash_rows)) OVER() * 100,'990.99')                              as "PerCent"
  7    from (select decode(nvl(sql_plan_hash_value, -1), -1, 'not yet', 0, '0', 'exists')              as PLAN_HASH_VALUE,
  8                 nvl2(sql_exec_id, 'exists', 'not yet')                                             as SQL_EXEC_ID,
  9                 session_state,
 10                 sql_adaptive_plan_resolved                                                         as ADAPTIVE_PLAN,
 11                 count(*)                                                                           as ASH_ROWS
 12            from gv$active_session_history
 13           where sql_opname = 'SELECT'
 14           group by decode(nvl(sql_plan_hash_value, -1), -1, 'not yet', 0, '0', 'exists'),
 15                    nvl2(sql_exec_id, 'exists', 'not yet'),
 16                    session_state,
 17                    sql_adaptive_plan_resolved)
 18   group by plan_hash_value, sql_exec_id, adaptive_plan
 19   order by sum(ash_rows) desc
 20  /
 
PLAN_HASH_VALUE SQL_EXEC_ID ADAPTIVE_PLAN SUM(ASH_ROWS) DB_TIME_WASTE                 PerCent
--------------- ----------- ------------- ------------- ----------------------------- -------
exists          exists                  1        210576 ON CPU(173241);WAITING(37335)   85.23 -- фаза обычного выполнения, планы отмечены как адаптивные в 85% случаев
exists          not yet                 1         30274 WAITING(20739);ON CPU(9535)     12.25 -- (*) 12c Adaptive Soft Parse, то что подлежит оценке
0               exists                  0          2279 ON CPU(1254);WAITING(1025)       0.92 -- (**) строки нормального выполнения в ASH с PLAN_HASH_VALUE=0, особый случай
exists          not yet                 0          2161 WAITING(1939);ON CPU(222)        0.87 -- Soft Parse НЕадаптивных планов
0               not yet                 0          1415 ON CPU(986);WAITING(429)         0.57 -- Hard Parse НЕадаптивный
0               not yet                 1           366 ON CPU(283);WAITING(83)          0.15 -- Hard Parse адаптивный
0               exists                  1             1 ON CPU(1)                        0.00 -- (**) выполнение в ASH с PLAN_HASH_VALUE=0, особый случай
exists          exists                  0             1 ON CPU(1)                        0.00 -- фаза обычного выполнения НЕадаптивных планов

(more…)

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

Блог на WordPress.com.