Oracle mechanics

15.06.2018

Oracle 12c: Optimizer Adaptive Features как генератор планов выполнения и сокращение mutex-ожиданий cursor: pin S

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

Скачок Load Average на обеих нодах:


high_LA_06062018.png

был спровоцирован частым выполнением запроса со стабильным, но не очень удачным планом 4125831042: (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…)

17.02.2018

Oracle 12.1: OR-Expansion and Adaptive features

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

На нескольких запросах столкнулись со ситуацией, когда при по умолчанию включенными optimizer_adaptive_features=true план запроса может не применять преобразование OR-Expansion, что значительно увеличивает формальную стоимость/Cost и реальное время выполнения:

12.1.0.2.@ SQL> explain plan for
  2  select *
  3    from 
...
 64  /

Explained.

SQL> select * from table(dbms_xplan.display('','','+alias -predicate'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
Plan hash value: 1699389027

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                                         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                  |    10 | 17700 |       |  2197K  (1)| 00:00:43 |
|*  1 |  VIEW                                             |    10 | 17700 |       |  2197K  (1)| 00:00:43 |
|*  2 |   WINDOW SORT PUSHED RANK                         |    39 | 13767 |       |  2197K  (1)| 00:00:43 |
|   3 |    NESTED LOOPS OUTER                             |    39 | 13767 |       |  2197K  (1)| 00:00:43 |
...
|* 39 |      INDEX UNIQUE SCAN                            |     1 |       |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   1 - SEL$D48D1E2F / from$_subquery$_001@SEL$1
   2 - SEL$D48D1E2F
...

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 7 Sql Plan Directives used for this statement

114 rows selected.

Судя по трейсу 10053 оптимизатор выполняет попытки: (more…)

21.05.2017

12c: Dynamic Sampling при индексном доступе к данным

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

В дополнение к старому описанию особенностей DS версии 10.2 — Dynamic Sampling при индексном доступе к данным — на том же тесткейсе наблюдал как измененилось поведение Old Style Dynamic Sampling (DS) и Adaptive Dynamic Sampling (ADS) в новых версиях Oracle 12c

Итак, тестовый DDL без изменений:

alter session set statistics_level=all;
alter system flush shared_pool;
drop table t1
/
create table t1
as
select * from all_objects
where rownum < 10001
/
create index t1_object_type on t1(object_type);
create index t1_created on t1(created);
alter table t1 add constraint t1_pk primary key (object_id);
exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'T1',method_opt => 'for all columns size 1', cascade => true); -- чтобы DS было чем заняться)
select distinct histogram from user_tab_col_statistics where table_name = 'T1';

1) Old Style Dynamic Sampling, версия 12.1 (more…)

22.02.2017

Hintset Object

Filed under: Oracle,Oracle 12c,parameters,shared pool — Игорь Усольцев @ 18:52
Tags: ,

The plan_baseline hintset is just the list of hints SPM tried to use to reproduce the plan

В ТОПе Library Cache периодически появлялся непривычный объект:

SQL> select *
  2    from (select hash_value,
  3                 substr(name, 1, 100) as object_name,
  4                 namespace,
  5                 type,
  6                 kept,
  7                 count(*),
  8                 min(timestamp),
  9                 sum(locked_total),
 10                 sum(pinned_total),
 11                 sum(loads),
 12                 sum(executions),
 13                 sum(sharable_mem)
 14            from v$db_object_cache
 15           group by hash_value,
 16                    substr(name, 1, 100),
 17                    namespace,
 18                    type,
 19                    kept
 20           order by sum(sharable_mem) desc)
 21   where rownum <= 3
 22  /
 
HASH_VALUE OBJECT_NAME          NAMESPACE      TYPE           KEPT COUNT(*) MIN(TIMESTAMP)      SUM(LOCKED_TOTAL) SUM(PINNED_TOTAL) SUM(LOADS) SUM(EXECUTIONS) SUM(SHARABLE_MEM)
---------- -------------------- -------------- -------------- ---- -------- ------------------- ----------------- ----------------- ---------- --------------- -----------------
2209848120 6004199150836888961  HINTSET OBJECT HINTSET OBJECT NO          1                                 14872             14872          4               0         625277440
 838046317 select               SQL AREA       CURSOR         NO        459 2016-11-02/10:54:33            442985            288445      79518          180659         155325176
 269081421 select               SQL AREA       CURSOR         NO        627 2016-10-31/00:07:03            273128            218432     143717           91650         154551720
...

необычайно крупного размера SHARABLE_MEM > 600MB с цифровым наименованием, под коим числилось 2 объекта: (more…)

30.01.2017

12c Adaptive Bitmap Pruning — особый случай адаптивного выполнения: PHV, PHV2, FPHV & Outline Hints

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

В процессе подготовки семинара, столкнулся с любопытным случаем, для адаптивного плана выполнения:

12.1.0.2.SCOTT@/ORCL1201 SQL> exec dbms_stats.gather_schema_stats('SH')

PL/SQL procedure successfully completed.

SQL> SELECT /*+ star_transformation(s)                                   -- star_transformation_enabled = TRUE на уровне запроса
  2             gather_plan_statistics
  3             OPT_PARAM('_optimizer_dsdir_usage_control' 0)            -- для исключения эффектов
  4  --           OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
  5             OPT_PARAM('_optimizer_gather_feedback' 'false') */       -- --//--
  6   c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
  7    FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
  8   WHERE s.time_id = t.time_id
  9     AND s.cust_id = c.cust_id
 10     AND s.channel_id = ch.channel_id
 11     AND c.cust_state_province = 'CA'
 12     AND ch.channel_desc = 'Internet'
 13     AND t.calendar_quarter_desc IN ('1999-01', '1999-02')
 14   GROUP BY c.cust_city, t.calendar_quarter_desc
 15  /

22 rows selected.

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

29.11.2016

Наблюдение за Db File Scattered Read, SAMPLE операции планов выполнения и запросы с большим удельным Soft Parse

Разбираясь с использованием ожидания/активности db file scattered read при разных операциях планов выполнения, можно видеть:

12.1.0.2@ SQL> select sql_plan_operation, sql_plan_options, count(*)
  2    from gv$active_session_history
  3   where event = 'db file scattered read'
  4   group by sql_plan_operation, sql_plan_options
  5   order by count(*) desc
  6  /
 
SQL_PLAN_OPERATION  SQL_PLAN_OPTIONS                 COUNT(*)
------------------- ------------------------------ ----------
TABLE ACCESS        FULL                                 3220 -- ожидаемо большая доля FTS
                                                          901 -- scattered чтения без SQL_PLAN_OPERATION (*)
TABLE ACCESS        BY INDEX ROWID BATCHED                399 -- scattered read при INDEX BATCHED операциях
TABLE ACCESS        BY LOCAL INDEX ROWID BATCHED           57 -- // --
MAT_VIEW ACCESS     FULL                                   52
TABLE ACCESS        BY INDEX ROWID                         35 -- скорее всего также выполняется в режиме BATCHED
INDEX               FAST FULL SCAN                         23 -- ожидаемое многоблочное чтение
INDEX               SAMPLE FAST FULL SCAN                   5 -- SAMPLE операции (**)
INDEX               UNIQUE SCAN                             5
INDEX               RANGE SCAN                              3
TABLE ACCESS        SAMPLE                                  2 -- // -- (**)
MAT_VIEW ACCESS     SAMPLE                                  1 -- // -- (**)

(*) Scattered чтения без SQL_PLAN_OPERATION большей частью относятся к фазе Soft parse (***), определяемой здесь как промежуток, когда PLAN_HASH_VALUE уже сформирован (PHV exists), но выполнение запроса ещё не началось (SQL_EXEC_ID is null): (more…)

13.11.2016

Oracle 12.1: Library Cache Lock на объекте типа $BUILD$, Adaptive Plan и параллельное выполнение

Filed under: Oracle,Scripts,shared pool,wait events — Игорь Усольцев @ 23:37
Tags: ,

Активизировавшаяся конкуренция за объекты Library Cache в форме ожиданий library cache lock, и в меньшей степени kksfbc child completion, library cache: mutex X:

Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Total Wait       Wait   % DB Wait    
Event                                Waits Time (sec)    Avg(ms)   time Class   
------------------------------ ----------- ---------- ---------- ------ -----------
DB CPU                                          10.3K              77.6         
library cache lock                 159,109     1554.8       9.77   11.7 Concurrency -- здесь
db file sequential read            297,807      436.2       1.46    3.3 User I/O
cursor: pin S wait on X             26,087      322.5      12.36    2.4 Concurrency
log file sync                      116,938      267.6       2.29    2.0 Commit  
SQL*Net more data from client    1,809,223      131.1       0.07    1.0 Network 
kksfbc child completion              2,213      108.3      48.94     .8 Other       -- , здесь
control file sequential read       290,199       72.9       0.25     .5 System I
direct path write                    6,026       46.3       7.69     .3 User I/O
library cache: mutex X              83,522       45.7       0.55     .3 Concurrency -- и здесь

оказалось вызвана PX-slave процессами (P…) одного параллельно выполнявшимся запроса dx0fckp3gckku в стадии подготовки (IN_PARSE) адаптивного плана (SQL_ADAPTIVE_PLAN_RESOLVED = 1): (more…)

27.07.2016

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

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

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

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

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

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