Oracle mechanics

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

14.01.2016

12c: старая проблема High version count и параметр PARALLEL_INSTANCE_GROUP

Filed under: Диагностика системы (instance),Oracle,shared pool — Игорь Усольцев @ 08:13
Tags:

С коллегой Сергеем Щукиным смотрели на проблему избыточной генерации трейсов типа Huge Trace Files Created Containing «—— Cursor Obsoletion Dump sql_id=%s ——» (Doc ID 1955319.1) — где в соотвествии с рекомендациями, установка параметра _kks_obsolete_dump_threshold = 0 помогла отключить/избавиться от трейсов

Но проблему High version count это не решило:

12.1.0.2@ SQL> select inst_id, sql_id, count(*)
  2    from gv$sql
  3   group by inst_id, sql_id
  4   having count(*) > 1000
  5   order by count(*) desc
  6  /
 
INST_ID SQL_ID          COUNT(*)
------- ------------- ----------
      1 7u1z273sfty8z      93057
      1 5cbp4s5jq04xg       7169
      2 gyvrs5a6pm7h7       1225

Топовые запросы имели схожую статистику курсоров: (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…)

17.12.2015

Фиксация плана с помощью SQL Profile и накладные расходы/overhead версии 12c

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

Процедура DBMS_SQLTUNE.CREATE_TUNING_TASK для конкретного SQL_ID игнорирует наличие SQL Profile для настраимоемого запроса:

SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'fppuw3hpvww2d' ) FROM DUAL;

...
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- SQL Profile "TASK-3017" exists for this statement and was ignored
  during the tuning process.
...

, в то время как запрос с существующим SQL Patch настройке не подлежит:)

...
-------------------------------------------------------------------------------
ERRORS SECTION
-------------------------------------------------------------------------------
- ORA-16961: SQL statement with SQL patch is ignored

При этом список подсказок автоматически генерируемого SQL Profile, по крайней мере, с версии 12c, уже не ограничивается привычными корректирующими OPT_ESTIMATE: (more…)

16.12.2015

SQL Patch для решения стандартных проблем версии 12c

Filed under: Oracle,Oracle 12c,Oracle new features — Игорь Усольцев @ 22:56
Tags:

Как бы решая проблему High Version Count находим пациента:

SQL> select *
  2    from (select inst_id,
  3                 sql_id,
  4                 count(*),
  5                 sum(count(*)) over(partition by sql_id) as child_count
  6            from gv$sql
  7           group by inst_id, sql_id
  8           order by 4 desc, 1)
  9   where child_count > 200
 10  /

INST_ID SQL_ID          COUNT(*) CHILD_COUNT
------- ------------- ---------- -----------
...
      1 a40b55bnbjm5t        179         362
      2 a40b55bnbjm5t        183         362
...

Суммарный запрос по причинам нерасшаривания курсора указывает на Auto Reoptimization Mismatch(1) + Bind mismatch(25): (more…)

15.11.2015

_high_priority_processes 12c

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

Максим Филатов привлёк внимание к странным изменениям в приоритетах background процессов в Oracle 12c

Например, на 12.1.0.2 OEL x86_64 release 6.4 доморощенным скриптом наблюдаю:

SQL> @param_ _high_priority_processes

 
NAME                      VALUE                                        IS_DEF   IS_MOD     IS_ADJ   DSC
------------------------- -------------------------------------------- -------- ---------- -------- -------------------------------
_high_priority_processes  LMS*|LM*|LCK0|GCR*|DIAG|CKPT|DBRM|RMS0|LGWR  TRUE     FALSE      FALSE    High Priority Process Name Mask

— расширенный в 12c список процессов, которые должны запускаться с высоким приоритетом, однако на хосте вижу, что повышенный приоритет имеют лишь:

$ ps axl|grep '\-2'|grep ora_
0   500 13933     1  -2   - 184900632 19376  hrtime Ss ?      582:49 ora_vktm_INST1
0   500 13966     1  -2   - 185022444 647692 poll_s Ss ?      302:28 ora_lms0_INST1
0   500 13970     1  -2   - 185022444 647544 poll_s Ss ?      298:04 ora_lms1_INST1
0   500 13974     1  -2   - 185022444 647728 poll_s Ss ?      300:44 ora_lms2_INST1

— как это и было в 11g, а вот фактически рекомендованный в параметре LGWR работает со стандартным приоритетом(: (more…)

14.11.2015

Патч 20476175 для версий до 12.1.0.2.5

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

В предыдущей заметке фактически продублировал описание известного Bug 20476175 High VERSION_COUNT (in V$SQLAREA) for query with OPT_PARAM(‘_fix_control’) hint

Баг этот, кроме случая использования подсказок OPT_PARAM(‘_fix_control’) непосредственно в теле запроса, обладает ещё одной неприятной особенностью — он «работает» также в случае применения этих подсказок через SQL Patch (и, подозреваю, через SQL Profile)

Например, если для простого запроса select count(*) from emp (SQL_ID=g59vz2u4cu404) сотворить простой SQL Patch: (more…)

08.11.2015

12c: hardcoded подсказка _fix_control и новая проблема High Version Count

Filed under: Диагностика системы (instance),hints,Oracle — Игорь Усольцев @ 22:22
Tags: ,

Как бы продолжая тему багов/особенностей 12c, появился запрос с заметной долей Shared Pool Concurrency ожиданий в процессе выполнения с ASH мониторингом вида:

SQL> @ash_sqlmon2 45u45mpanbatr
 
LAST_PLSQL SQL_ID        PLAN_HASH_VALUE   ID PLAN_OPERATION                                 PX   ASH_ROWS WAIT_PROFILE
---------- ------------- --------------- ---- ------------------------------------------------- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hard Parse 45u45mpanbatr               0    0 sql_plan_hash_value = 0                       385      48755 cursor: pin S wait on X(47474); library cache lock(653); ON CPU(318); cursor: mutex X(121); cursor: mutex S(120); kksfbc child completion(52); cursor: pin S(8); library cache: mutex X(7);
Soft Parse 45u45mpanbatr      2447725225    0 sql_plan_hash_value > 0; sql_exec_id is null  514      37420 cursor: pin S wait on X(36708); library cache lock(454); cursor: mutex X(88); cursor: mutex S(77); ON CPU(44); kksfbc child completion(32); library cache: mutex X(11); SQL*Net message from dblink(3);
Soft Parse 45u45mpanbatr      2907472699    0 sql_plan_hash_value > 0; sql_exec_id is null  601     113412 cursor: pin S wait on X(107536); ON CPU(3566); library cache lock(1607); cursor: mutex X(286); cursor: mutex S(255); kksfbc child completion(88); PX Deq: Signal ACK EXT(26); library cache: mutex X(24);
...

— на этапах Hard/Soft Parse, доля которых для запроса составляла ~ 2/3 общего времени выполнения: (more…)

07.11.2015

ORA-01841, Table Expansion и Interval Partitioning в версии 12.1.0.2

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

Коллеги-разработчики с радостью сообщили, что наконец-то сломали 12-й Oracle обнаружили проблему, а Александр Шакура подготовил отличный тесткейс:

12.1.0.2@ SQL> create table partit_Tab tablespace users
  2  PARALLEL ( DEGREE 16 INSTANCES 1 ) PARTITION BY RANGE(DT) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
  3  ( PARTITION p0 VALUES LESS THAN (TO_DATE('01-01-2005', 'DD-MM-YYYY')))
  4  as
  5  select sysdate dt from dual
  6  union all
  7  select sysdate-300 dt from dual
  8  union all
  9  select sysdate+300 dt from dual
 10  /
 
Table created
 
SQL> select * from partit_Tab ch
  2   where ch.dt < to_date('01-SEP-15','DD-MON-YY')
  3      or ch.dt between to_date('01-SEP-15','DD-MON-YY') and to_date('30-SEP-15','DD-MON-YY')
  4  /
 
DT
-----------
03.01.2015
 
SQL> select * from partit_Tab ch
  2   where ch.dt < date '2015-09-01'
  3      or ch.dt between date '2015-09-01' and date '2015-09-30'
  4  /
 
select * from partit_Tab ch
 where ch.dt < date '2015-09-01'
    or ch.dt between date '2015-09-01' and date '2015-09-30'
 
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

В процессе обследования выяснилось, что: (more…)

30.10.2015

12c: простое управление планом с использованием SQL Plan Directives (SPD)

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

После обновления на версию 12.1.0.2 обнаружился деградировавший запрос:

12.1.0.2.@ SQL> @shared_cu12_noxml 4nut8pr74vpff
 
EXECS USERS_OPENING LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC CURSOR_STATUS 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
----- ------------- -------------------- ------------------- ------------ ------------- --------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- ----------------------
  374             2 2015-10-28/01:30:14  28.10.2015 15:38:55    349992290 VALID              2500238505           2253     1 Y          N          Y          N                  N                       0         1         2        Optimizer mismatch(12)

— со ср.временем ~ 350 сек. при SLA/таймауте в 300

Из запроса заметно, что вероятной причиной деградации может быть использованная в процессе директива (SPD_Used), которую удалось запеленговать командой EXPLAIN PLAN: (more…)

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

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

Отслеживать

Настройте получение новых записей по электронной почте.

Присоединиться к ещё 182 подписчикам