Oracle mechanics

30.11.2016

12c: Log File Sync, параметр _DB_MTTR_ADVICE и опасности увеличения кол-ва LGWR-workers

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

После switchover-а на аналогичное железо в разы выросло время ожидания log file sync:

12.1.0.2@ SQL> @ash_wait_tree "event = 'log file sync'" 0 "where inst_id = 1"
 
LVL BLOCKING_TREE         EVENT                         WAIT_CLASS WAITS_COUNT SESS_COUNT AVG_WA  EST_WAITS EST_AVG_LATENCY_MS
--- --------------------- ----------------------------- ---------- ----------- ---------- ------ ---------- ------------------
  1 (FOREGROUND)          log file sync                 Commit           37104       2019    395    1992400                 19 -- ну не 19 мс, конечно, но точно > 10 мс - недопустимо долго
  1 (J...)                log file sync                 Commit              37         35      0          0               1000
  2   (LGWR)              LGWR any worker group         Other            31107          1    518     134101                231 -- осн.блокер для log file sync
  2   (LGWR)              target log write size         Other             4053          1      7    1898397                  2
  2   (LGWR)              LGWR all worker groups        Other             1137          1    187      59184                 19
  2   (LGWR)              On CPU / runqueue                                 54          1      0          0               1000
  2   (LGWR)              enq: CF - contention          Other               19          1    316         60                317
  2   (LGWR)              control file sequential read  System I/O          12          1      1      10607                  1
  2   (LGWR)              control file parallel write   System I/O           7          1      1       6261                  1
  3     (LG..)            LGWR wait for redo copy       Other            25103          1    606      73038                340 -- осн.блокер для LGWR any worker group *
  3     (LG..)            log file parallel write       System I/O        6290          1    209     200335                 31
  3     (LG..)            LGWR worker group ordering    Other              406          1    223       2500                162
  4       (FOREGROUND)    On CPU / runqueue                              21073        354      0          0               1000
  4       (J...)          On CPU / runqueue                               3908         21      0          0               1000
  4       (LG..)          log file parallel write       System I/O         230          1    228       1014                227
  4       (LG..)          LGWR wait for redo copy       Other              176          1    198       1391                127
  4       (Q...)          On CPU / runqueue                                122          1      0          0               1000
  5         (FOREGROUND)  On CPU / runqueue                                176          4      0          0               1000

— при этом в качестве важного промежуточного блокера в вышеприведённой цепочке по частоте и продолжительности неожиданно проявилось LGWR wait for redo copy (*), что было отчётливо заметно в цепочке ожиданий, начиная с LGWR any worker group: (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…)

14.11.2016

Скоро 12.2

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

На прошлой неделе появилась полная версия документации Oracle Database 12c Release 2

, следом Mike Dietrich публикует триптих:

New SPFILE parameters in Oracle Database 12.2.0.1
Obsolete SPFILE Parameters 12.2.0.1
Deprecated Parameters in Oracle Database 12.2.0.1

, и Российский Oracle, по слухам, в конце месяца готовит целенаправленный семинар с более подробным описанием/обсуждением новых опций «Oracle Database 12c Release 2»

— всё идёт к скорому релизу

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

10.11.2016

Обновил Scripts

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

На страничке Scripts — [почти полное] описание используемого набора скриптов (своих и позаимствованных), с практическими примерами применения и ссылками на zip и GitHub

Хорошо работают в Command Window PL/SQL Developer, эпизодически проверяю в SQL*Plus и SQL Developer — там возможны проблемы оформления/форматирования, замечания и предложения пишите в комментариях к странице — обязательно исправлю

17.10.2016

12c: Wrong Results, параллельное выполнение и OPTIMIZER_FEATURES_ENABLE

Filed under: Oracle,parameters,PX,Scripts — Игорь Усольцев @ 23:49
Tags: ,

В процессе разбора очередного случая с неправильными рез-том в Oracle 12.1.0.2.*, на этот раз при параллельном выполнении запроса, и предварительно проверив варианты, перечисленные в How to Narrow Down Wrong Results Issues from Parallel Execution (Doc ID 1340246.1), по предложению представителя поддержки, проверил относительно новый функционал — DBMS_SQLDIAG с ключом PROBLEM_TYPE_WRONG_RESULTSHow to use DBMS_SQLDIAG to Assist Diagnosis of Wrong Results Issues (Doc ID 1492650.1):

SQL> declare
declare
  2  l_sql_diag_task_id  varchar2(100);
  3  begin
  4      l_sql_diag_task_id :=  dbms_sqldiag.create_diagnosis_task (
  5        sql_id => '67z9jx22r5sx8',
  6        problem_type => dbms_sqldiag.PROBLEM_TYPE_WRONG_RESULTS,
  7        task_name => 'Test_WR_diagnostic_task' );
  8      dbms_sqltune.set_tuning_task_parameter(
  9        l_sql_diag_task_id,
 10        '_SQLDIAG_FINDING_MODE',
 11        DBMS_SQLDIAG.SQLDIAG_FINDINGS_FILTER_PLANS);
 12  end;
 13  /
/
 
PL/SQL procedure successfully completed

(more…)

06.10.2016

ORA-07445 [kteclck()+363]

Filed under: Active Data Guard,Oracle — Игорь Усольцев @ 23:44
Tags: ,

— дважды встречена на ADG Standby при выполнении запросов с использованием TEMP TABLE TRANSFORMATION после применения обновлений Database Patch Set Update : 12.1.0.2.160419 или Database Bundle Patch: 12.1.0.2.160719 (в виде [kteclck()+586])

Workaround в виде «_with_subquery» = inline* был найден самостоятельно и быстро, а по ре-там SR, заведённого / доведённого Сергеем Щукиным до Severity 1 поддержка завела Bug 23732326 : ORA-07445 [KTECLCK+363] IN ADG ENVIRONMENT и даже сопроводительный документ ORA-07445 [kteclck()+363] In Active Data Guard (ADG) Environment (Doc ID 2177342.1), не предложив, однако, на настоящий момент никакого другого, лучшего решения

Типичный stacktrace:

...
kteclck()+586           signal   __sighandler()
ktelbhw()+809           call     kteclck()
qesldBumpHwm()+197      call     ktelbhw()
qesldCommitHwmLoad()+92 call     qesldBumpHwm()
qesldCommit3tIDL()+210  call     qesldCommitHwmLoad()
qes3tExecSQL()+1110     call     qesldCommit3tIDL()
qerleStart()+220        call     qes3tExecSQL()
...

*) установка «_with_subquery» = inline на уровне системы, кроме ущербности в выборе планов выполнения (Performanz), вызывает:

ORA-32034: unsupported use of WITH clause

на Primary db версии 12.1.0.2.3, например

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

24.08.2016

12c, адаптивный LGWR: ожидания log file sync и target log write size

Из более-менее традиционной картинки ожиданий log file sync:

12.1.0.2@ SQL> @ash_wait_tree "event = 'log file sync' and inst_id = 1"
 
LVL INST_ID BLOCKING_TREE     WAIT_CLASS  EVENT                         WAITS_COUNT SESS_COUNT AVG_WA
--- ------- ----------------- ----------- ----------------------------- ----------- ---------- ------
  1       1 (FOREGROUND)      Commit      log file sync                       10992       6879    130 -- польз.процессы
  1       1 (J...)            Commit      log file sync                          13         13      0 -- и Job-ы ожидают LGWR
  2       1   (LGWR)          Other       LGWR any worker group                4675          1    190 -- , висящий, восновном,
  2       1   (LGWR)          Other       target log write size                3880          1      4 -- на этих 3-х ожиданиях
  2       1   (LGWR)          Other       LGWR all worker groups                691          1    265 -- и облокируемый
  2       1   (LGWR)                      On CPU / runqueue                      61          1      0
...
  3       1     (LG..)        System I/O  log file parallel write              5064          1    217 -- LGWR-worker-ами (LG..)
  3       1     (LG..)        Other       LGWR worker group ordering            129          1    223
  3       1     (FOREGROUND)              On CPU / runqueue                       2          1      0
  4       1       (LG..)      System I/O  log file parallel write               129          1    347

— можно предположить, что для уменьшения log file sync достаточно сократить время/кол-во блокирующих ожиданий LGWR any worker group/LGWR all worker groups и target log write size, распределённых в этом случае приблизительно поровну

И если первая группа ожиданий очевидно «упирается» в кол-во/скорость работы LGWR-worker-ов, обозначенных как (LG..): (more…)

23.08.2016

12c: Очередной пропущенный ORA-00979: not a GROUP BY expression — Bug 22338374

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

Евгений Калинин в очередной раз успешно сумел заставить Oracle выдать странное — на этот раз рез-т запроса (аналитических функции, CONNECT BY, SUBQUERY FACTORING, CASE, ANSI JOIN в комбинациях) с ошибкой в перечислении GROUP BY столбцов вместо ORA-00979 выдавал рез-ты, отличные от рез-тов правильного в части GROUP BY запроса, и, более того, зависел от применения или неприменения TEMP TABLE TRANSFORMATION в плане выполнения!

Столь странное поведение, очевидно, выходило за рамки приличий, пришлось/удалось сделать несложный тесткейс и заслать в оркест обратиться в техподдержку с SR-запросом Query with left outer join gives WRONG results instead of ORA-00979: not GROUP BY expression in 12c (more…)

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

Блог на WordPress.com.