Oracle mechanics

14.12.2018

row cache lock: global deadlock

Filed under: Блокировки,Oracle,shared pool — Игорь Усольцев @ 00:33
Tags:

С Иваном Постниковым наблюдали/разбирали нечасто встречаемое ожидание library cache load lock, в течение заметного времени наблюдавшееся у ряда сессий 2-го инстанса:

12.1.0.2@inst#2 SQL> select * from v$session where state = 'WAITING' and event = 'library cache load lock';
 
  SID    SERIAL# USERNAME COMMAND STATUS   SERVER    SQL_ID        SQL_CHILD_NUMBER SQL_EXEC_START SQL_EXEC_ID MODULE                   ACTION ROW_WAIT_OBJ# LAST_CALL_ET  BLOCKING_SESSION_STATUS BLOCKING_INSTANCE BLOCKING_SESSION FINAL_BLOCKING_SESSION_STATUS FINAL_BLOCKING_INSTANCE FINAL_BLOCKING_SESSION EVENT                    P1TEXT          P1RAW            P2TEXT        P2RAW            P3TEXT             P3RAW            WAIT_CLASS  SECONDS_IN_WAIT STATE  
----- ---------- -------- ------- -------- --------- ------------- ---------------- -------------- ----------- ------------------------ ------ ------------- ------------ ------------------------ ----------------- ---------------- ----------------------------- ----------------------- ---------------------- ------------------------ --------------- ---------------- ------------- ---------------- ------------------ ---------------- ----------- --------------- -------
   13      54031 APPS           3 ACTIVE   DEDICATED 8r49n0b59js0a                0                            e:SQLGL:cp:xla/XLADRPGLT SQLGL/       2707322        81853  VALID                                   2              392 VALID                                               2                    392 library cache load lock  object address  0000005D14491B18 lock address  0000005CA8C05AD0 100*mask+namespace 0000000000010003 Concurrency           80854 WAITING
  652      55148 APPS           3 ACTIVE   DEDICATED a742atptz32bs                0                            e:SQLGL:cp:xla/XLADRPGLT SQLGL/            -1        81853  VALID                                   2              392 VALID                                               2                    392 library cache load lock  object address  0000005D14491B18 lock address  0000005DA2ACFDF0 100*mask+namespace 0000000000010003 Concurrency           80854 WAITING
 1632      58741 APPS           3 ACTIVE   DEDICATED gn7fg92jr1vkj                0                            e:SQLGL:cp:xla/XLADRPGLT SQLGL/       2707322        81851  VALID                                   2              392 VALID                                               2                    392 library cache load lock  object address  0000005D14491B18 lock address  0000005C037E1AC0 100*mask+namespace 0000000000010003 Concurrency           81758 WAITING
 1801      31526 APPS           3 ACTIVE   DEDICATED 99340wc8xpd52                0                            e:SQLGL:cp:xla/XLADRPGLT SQLGL/       2707322        81852  VALID                                   2              392 VALID                                               2                    392 library cache load lock  object address  0000005D14491B18 lock address  0000005BA6313E70 100*mask+namespace 0000000000010003 Concurrency           80854 WAITING
 2495      38991 APPS           3 ACTIVE   DEDICATED 39wnr00vh9t1b                0                            e:SQLGL:cp:xla/XLADRPGLT SQLGL/            -1        81852  VALID                                   2              392 VALID                                               2                    392 library cache load lock  object address  0000005D14491B18 lock address  0000005DA151AF38 100*mask+namespace 0000000000010003 Concurrency           80854 WAITING

, содержимое ROW_WAIT_OBJ# не очень полезно:

SQL> select * from dba_objects where object_id  = 2707322;
 
OWNER OBJECT_NAME  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
----- ----------- ---------- -------------- -----------
SYS   USER$          2707322        2707320 TABLE

, судя по пустым SQL_EXEC_START / SQL_EXEC_ID запросы/курсоры находятся в процессе погрузки в Library Cache (на что указывает и название ожидания длительностью SECONDS_IN_WAIT 80000+ сек) и имеют сходные тексты, относящиеся к одной и той же временной по сути, но не по определению (TEMPORARY=N) таблице: (more…)

Реклама

10.12.2018

12.2: JPPD/OJPPD и параметр _OPTIMIZER_PUSH_PRED_COST_BASED

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

До обновления 12.1 -> 12.2 запрос удовлетворительно (SLA, timeouts,…) быстро выполнялся по плану:

SQL_ID 4dgcbuug0z2mk
--------------------
  
Plan hash value: 1982197705
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation                                          | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                   |        |       |     8 (100)|          |
|   1 |  VIEW                                              |      1 |  1258 |     8  (13)| 00:00:01 |
|   2 |   WINDOW SORT                                      |      1 |  1180 |     8  (13)| 00:00:01 |
|   3 |    SORT GROUP BY ROLLUP                            |      1 |  1180 |     8  (13)| 00:00:01 |
|   4 |     NESTED LOOPS                                   |      1 |  1180 |     7   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                                  |      1 |  1180 |     7   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                                 |      1 |  1171 |     6   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                                |      1 |    24 |     5   (0)| 00:00:01 |
|   8 |         TABLE ACCESS BY INDEX ROWID                |      1 |    12 |     4   (0)| 00:00:01 |
|   9 |          INDEX UNIQUE SCAN                         |      1 |       |     3   (0)| 00:00:01 |
|  10 |         TABLE ACCESS BY INDEX ROWID BATCHED        |      1 |    12 |     1   (0)| 00:00:01 |
|  11 |          INDEX RANGE SCAN                          |      1 |       |     0   (0)|          |
|  12 |        VIEW                                        |      1 |  1147 |     1   (0)| 00:00:01 | -- **
|  13 |         UNION-ALL PARTITION                        |        |       |            |          | -- ***
|  14 |          NESTED LOOPS                              |      1 |   156 |    24   (0)| 00:00:01 |
|  15 |           NESTED LOOPS                             |      1 |   156 |    24   (0)| 00:00:01 |
|  16 |            NESTED LOOPS OUTER                      |      1 |   100 |    22   (0)| 00:00:01 |
|  17 |             NESTED LOOPS                           |      1 |    60 |    21   (0)| 00:00:01 |
|  18 |              TABLE ACCESS BY INDEX ROWID BATCHED   |      1 |    50 |    20   (0)| 00:00:01 | -- ****
|  19 |               INDEX RANGE SCAN                     |     17 |       |     4   (0)| 00:00:01 | -- ****
|  20 |              MAT_VIEW ACCESS BY INDEX ROWID BATCHED|      1 |    10 |     1   (0)| 00:00:01 | -- ****
|  21 |               INDEX RANGE SCAN                     |      1 |       |     0   (0)|          | -- ****
|  22 |             TABLE ACCESS BY INDEX ROWID            |      1 |    40 |     1   (0)| 00:00:01 | -- ****
|  23 |              INDEX UNIQUE SCAN                     |      1 |       |     0   (0)|          | -- ****
|  24 |            INDEX RANGE SCAN                        |      1 |       |     0   (0)|          | -- ****
|  25 |           TABLE ACCESS BY INDEX ROWID              |      1 |    56 |     2   (0)| 00:00:01 | -- ****
...
|  52 |       INDEX UNIQUE SCAN                            |      1 |       |     0   (0)|          |
|  53 |      TABLE ACCESS BY INDEX ROWID                   |      1 |     9 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
...
      OLD_PUSH_PRED(@"SEL$641071AC" "MS"@"SEL$3" ("INVOICE"."CLIENT_ID")) -- *
...
      END_OUTLINE_DATA
  */

, с использованием OJPPD — Old-stype (non-cost-based) Join Predicate Push-Down (*) высокоизбирательного условия («INVOICE».»CLIENT_ID») в обзор (**) вида UNION-ALL PARTITION (***) с последующим быстрым индексным доступом (****)

В версии 12.2, похоже, что-то существенно изменилось, OJPPD в процессе компиляции плана перестал рассматриваться совсем, в то время как Cost-based JPPD, похоже, как и в 12.1 (возможно и раньше), отказывается работать с Union-All View под надуманным предлогом (*), меняя при этом устаревшую (?) операцию UNION-ALL PARTITION на UNION-ALL: (more…)

17.08.2018

12.2 Materialized View Refresh Statistics Slowdown

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

Заметно замедлившайся рутинная операция обновления matview в версии Oracle 12.2 указывала, что выполняющая обновление сессия помимо необходимых и понятных операций DELETE+INSERT значительную часть времени проводила в дублирующих общую статистику DBMS_STATS подсчётах Cardinality всех подряд странного набора matview нашей базы: (more…)

SQL Developer и связанные переменные вида :”1”

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

Поскольку некоторые инструменты разработки генерируют связанные переменные вида :"1", :"2",.., а некоторые классы запросов могут по факту вытолнятся только на Readonly Standby, не присутствуя на Primary ни в каком виде, для фиксации/оптимизации плана бывает необходимо каким-то образом поместить такой запрос (sql_id) в Primary Shared Pool, что можно сделать стандартным способом через execute immediate:

12.1.0.2.@ SQL> declare
  2  t date;
  3  begin
  4    execute immediate 'SELECT sysdate from dual where 1 = :"1"' into t using 1;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select sql_id, sql_text from v$sql where sql_text like 'SELECT sysdate from dual where 1 = :"1"%';

SQL_ID        SQL_TEXT
------------- -----------------------------------------------------------------------------------------
2cg0gucz7scgq SELECT sysdate from dual where 1 = :"1"

, что для громоздких запросов с большим кол-вом bind-ов может потребовать определённых усилий

Прямое выполнение запроса в SQL*Plus для этой цели приспособить затруднительно: (more…)

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

01.06.2018

12.2: ORA-979 при разборе запросов с использованием WITH clause, PLSQL function и View Merging

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

Евгений Калинин обнаружил запрос, факт успешности выполнения которого неожиданно зависел от использования/неиспользования столбцов Inline view(*) а качестве параметров PLSQL_FUNCTION, и худшем случае завершавшийся простой ошибкой:

12.2.0.1.@ SQL> with s_q_by_month as
  2   (select contract_id,
...
 19     group by contract_id,
...
 30  s_q as
 31   (select plsql_function(sum_rub, -- вот этих столбцов *
 32                          max_amt,
...
 36      from (select contract_id,    -- вот этого Inline view
...
 43                   max(amt) as max_amt,
 44                   sum(amt_rub) as sum_rub,
...
 48              from s_q_by_month
 49             group by contract_id,
...
 56  select
 57   * from s_q
 58  /
 * from s_q
          *
ERROR at line 57:
ORA-00979: not a GROUP BY expression

, возникавшей на этапе построения плана, обрывая трассировку где-то в районе Query Transformation — в данном случае трейс заканчивался упоминанием OJE(Outer Join Elimination)

Соответствующая трассировка по номеру ошибки, почерпнутая из Bug 21799456 : ORA-979 ERROR GENERATED FROM A SELECT STATEMENT IN 12C: (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…)

25.03.2018

TM — Library Cache Deadlock на этапе Parsing и Optimizer Finding

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

При выполнении ALTER TABLE DROP CONSTRAINT случился TM — library cache deadlock распределённого (RAC) вида, отразившийся в ASH следующим образом:

12.1.0.2@ SQL> @ash_sql_wait_tree "(session_id,session_serial#) in ((4460,16750)) and sample_time > trunc(sysdate,'hh24')" 0
 
LVL INST_ID BLOCKING_TREE   EVENT                 WAIT_CLASS   DATA_OBJECT_P1RAW  IN_PARSE SQL_CHILD_NUMBER WAITS_COUNT EXECS_COUNT AVG_WA SESS_COUNT BLOCK_SID  MIN_STIME                  MAX_STIME                  SQL_ID        SQL_PLAN_HASH_VALUE SQL_EXEC_ID SQL_TEXT
--- ------- --------------- --------------------- ------------ ------------------ -------- ---------------- ----------- ----------- ------ ---------- ---------- -------------------------- -------------------------- ------------- ------------------- ----------- ----------------------------------------------------------------------------------------------------
  1       1 (USER)          enq: TM - contention  Application  MY.PAYMENT_Z.      N                       0          11           1      0          1 GLOBAL i#  26-JUN-17 04.06.39.431 PM  26-JUN-17 04.06.49.451 PM  7kpzqksdg4wu8                   0           1 
  1       1 (USER)          enq: TM - contention  Application  MY.PAYMENT_Z.      N                       0           2           1      0          1 VALID i#2  26-JUN-17 04.06.50.451 PM  26-JUN-17 04.06.51.451 PM  7kpzqksdg4wu8                   0           1 
  1       1 (USER)          library cache pin     Concurrency  000000261138EA48   Y                       0           1           0      1          1 GLOBAL i#  26-JUN-17 04.06.37.431 PM  26-JUN-17 04.06.37.431 PM  73f85gf8qvb34          2024994732           0 select NULL AS TABLE_CAT, OWNER AS TABLE_SCHEM, 'NO' as IS_AUTOINCREMENT, cc.COMMENTS AS REMARKS,OWN
  1       1 (USER)          library cache pin     Concurrency  000000274A98AEE0   Y                       0           1           0      0          1 GLOBAL i#  26-JUN-17 04.06.36.431 PM  26-JUN-17 04.06.36.431 PM  6196bjzyj6dv7          2050374072           0 SELECT null as TABLE_CAT, a.OWNER as TABLE_SCHEM, a.TABLE_NAME as TABLE_NAME, a.TEMPORARY as TEMPORA
  2       2   (USER)        library cache lock    Concurrency  00000027E6E364A8   Y                      -1           4           0   6807          1 VALID i#1  26-JUN-17 04.06.50.574 PM  26-JUN-17 04.06.51.574 PM  0w12fswfwtrdv          3054558131           0 UPDATE payment_z SET ...
  2       2   (USER)        library cache lock    Concurrency  00000027E6E364A8   Y                      -1           1           0      0          1 GLOBAL i#  26-JUN-17 04.06.49.564 PM  26-JUN-17 04.06.49.564 PM  0w12fswfwtrdv          3054558131           0 UPDATE payment_z SET ...
  3       1     (USER)      enq: TM - contention  Application  MY.PAYMENT_Z.      N                       0           4           1      0          1 VALID i#2  26-JUN-17 04.06.50.451 PM  26-JUN-17 04.06.51.451 PM  7kpzqksdg4wu8                   0           1 
  3       1     (USER)      enq: TM - contention  Application  MY.PAYMENT_Z.      N                       0           2           1      0          1 GLOBAL i#  26-JUN-17 04.06.49.451 PM  26-JUN-17 04.06.49.451 PM  7kpzqksdg4wu8                   0           1 
  4       2       (USER)    library cache lock    Concurrency  00000027E6E364A8   Y                      -1           4           0   6807          1 VALID i#1  26-JUN-17 04.06.50.574 PM  26-JUN-17 04.06.51.574 PM  0w12fswfwtrdv          3054558131           0 UPDATE payment_z SET ...
  4       2       (USER)    library cache lock    Concurrency  00000027E6E364A8   Y                      -1           2           0      0          1 GLOBAL i#  26-JUN-17 04.06.49.564 PM  26-JUN-17 04.06.49.564 PM  0w12fswfwtrdv          3054558131           0 UPDATE payment_z SET ...
  5       1         (USER)  enq: TM - contention  Application  MY.PAYMENT_Z.      N                       0           2           1      0          1 GLOBAL i#  26-JUN-17 04.06.49.451 PM  26-JUN-17 04.06.49.451 PM  7kpzqksdg4wu8                   0           1 
 
11 rows selected

где: (more…)

21.03.2018

ASH_IO_WAITSBY.SQL

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

По причине ошибки ORA-00600: Internal Error Code, Arguments: [qmxtcsxmlt:xmltype] (Doc ID 2079499.1), характерной для использования функции XMLAgg в бд состояния MOUNTED (standby or non-standby), переписал скрипт ASH_IO_WAITS.SQL для поиска наиболее активных в WAIT_CLASS-ах ‘User I/O‘ и ‘System I/O‘ процессов/запросов на STANDBY и прочих MOUNTED системах

Например, при замедлившемся накате standby (в отсутствии Read Only пользовательских запросов) можно было наблюдать аномально интенсивную IO активность пользовательских процессов RFS в части control file sequential read и RFS write:

SQL> @ash_io_waitsby blocks 10 "1 = 1"
 
INST SQL_PROCESS   SUM(WAIT_COUNT) waits%  SUM(REQUESTS) reqs%   SUM(BLOCKS) blocks% event(waits:requests:blocks)
---- ------------- --------------- ------- ------------- ------- ----------- ------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   1 (USER)                 156134   73.56        156134   11.11     1269165   39.07 control file sequential read(11699:11699:1123994); RFS write(105538:105538:105538); RMAN backup & recovery I/O(29486:29486:29486); Data file init write(6877:6877:6877); control file parallel write(736:736:1472); Disk file operations I/O(1049:1049:1049); Disk file Mirror Read(641:641:641); Log archive I/O(108:108:108)
   1 (DBW.)                  14696    6.92       1207318   85.94     1207318   37.16 db file parallel write(14696:1207318:1207318)
   1 (PR..)                  17569    8.28         17569    1.25      523541   16.12 log file sequential read(3984:3984:506025); recovery read(13311:13311:13311); control file sequential read(120:120:4020); direct path write(91:91:91); control file parallel write(31:31:62); direct path read(31:31:31); Disk file operations I/O(1:1:1)
   1 (ARC.)                  16169    7.62         16169    1.15      234540    7.22 control file sequential read(8614:8614:226728); Log archive I/O(7104:7104:7104); control file parallel write(280:280:560); Disk file Mirror Read(106:106:106); Disk file operations I/O(40:40:40); log file sequential read(25:25:2)
   1 (CKPT)                   4624    2.18          4624    0.33        8932    0.27 control file parallel write(3301:3301:6602); control file sequential read(1007:1007:2014); Disk file Mirror Read(316:316:316)
   1 a5xcbum9zpyzn            1750    0.82          1750    0.12        2912    0.09 control file sequential read(1162:1162:2324); Disk file Mirror Read(588:588:588)
   1 6jaghrm3vy74f             769    0.36           769    0.05        1303    0.04 control file sequential read(534:534:1068); Disk file Mirror Read(235:235:235)
   1 89z2u0fdrdg66             397    0.19           397    0.03         761    0.02 control file sequential read(364:364:728); Disk file Mirror Read(33:33:33)
   1 (M...)                     50    0.02            50    0.00          92    0.00 control file sequential read(42:42:84); Disk file Mirror Read(8:8:8)
   1 7mgr3uwydqq8j              13    0.01            13    0.00          22    0.00 control file sequential read(9:9:18); Disk file Mirror Read(4:4:4)
 
10 rows selected

в сравнении с нормальным поведением, где RFS заметен лишь на 4-м месте с 5% прочитанных/записанных blocks: (more…)

09.03.2018

log file sequential read

Filed under: Active Session History,AWR,Oracle — Игорь Усольцев @ 12:14
Tags:

Всплеск нагрузки на IO подсистему:

сопровождался, по нашим с Дмитрием Якубеней наблюдениям, дисбалансом в отчёте AWR — совершенно незначительные цифры пользовательской нагрузки (*):

Load Profile                    Per Second   Per Transaction  Per Exec  Per Call
~~~~~~~~~~~~~~~            ---------------   --------------- --------- ---------
             DB Time(s):              73.0               0.0      0.01      0.00
              DB CPU(s):              10.5               0.0      0.00      0.00
      Background CPU(s):               0.9               0.0      0.00      0.00
      Redo size (bytes):       6,734,104.6           3,153.3
  Logical read (blocks):       2,698,366.4           1,263.6
          Block changes:          41,001.2              19.2
 Physical read (blocks):          11,301.7               5.3
Physical write (blocks):           3,319.9               1.6
       Read IO requests:           4,417.3               2.1
      Write IO requests:             721.0               0.3
           Read IO (MB):              44.2               0.0                      -- *
          Write IO (MB):              13.0               0.0                      -- *
...

никак не коррелировали с Total (MB) нагрузкой AWR IO Profile (**): (more…)

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

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