Oracle mechanics

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

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

30.05.2016

Топ I/O ожиданий/блоков/операций по записям ASH

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

AWR классифицирует запросы в части I/O по след.метрикам:

  • SQL ordered by User I/O Wait Time — соответственно по User I/O Time, кол-ву ожиданий и потраченному DB Time, что соответствует распределению строк в ASH
  • SQL ordered by Reads, отсортированному по Physical Reads (in blocks)
  • SQL ordered by Physical Reads (UnOptimized), упорядоченному по кол-ву Physical Read Requsts ( с выденением UnOptimized), что крайне полезно при разборе проблем в части ввода/вывода

Набросал запрос для тех же метрик на основе записей ASH, добавив для полноты картины системные/Background Processes и возможности сортировки по WAITS (соответствует User I/O Wait Time в AWR), REQS (Physical Read Requsts) и BLOCKS (Physical Reads) с учётом отличия db file blocksize и log file blocksize, без учёта возможных различий blocksize разных db files

На примере одной OLTP бд, сортируя ASH по считанным/записанным блокам (в пересчёте на db file blocksize), можно видеть активную писательскую деятельность дочерних LG00: (more…)

27.04.2016

Скрипт для асинхронных блокировок/блокеров из [истории] ASH

Filed under: Active Session History,Диагностика системы (instance),Oracle,Scripts — Игорь Усольцев @ 16:04
Tags: ,

Для ответа на вопрос типа «Кто кого блокировал 21 апреля в период между 03:00 и 03:30 ?» можно посмотреть в соответствующий AWR и определить:

1) собственно факт вероятного наличия проблемы:

              Snap Id      Snap Time      Sessions Curs/Sess Instances
            --------- ------------------- -------- --------- ---------
Begin Snap:    325799 21-Apr-16 03:00:50       672       2.9         2
  End Snap:    325800 21-Apr-16 03:30:06       613       2.9         2
   Elapsed:               29.28 (mins)
   DB Time:            2,157.33 (mins) -- тут

2) проблемное ENQUEUE ожидание:

Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Total Wait       Wait   % DB Wait    
Event                                Waits Time (sec)    Avg(ms)   time Class   
------------------------------ ----------- ---------- ---------- ------ --------
DB CPU                                          50.8K              39.2         
enq: TX - row lock contention          907      26.2K   28850.95   20.2 Applicat -- вероятно, тут
latch: cache buffers chains      1,063,115     7905.4       7.44    6.1 Concurre
enq: UL - contention                38,803     4835.8     124.63    3.7 Applicat
db file sequential read          2,587,974     2225.1       0.86    1.7 User I/O

3) страждущий запрос(ы) по наименованию ожидания: (more…)

18.04.2016

ASH_SQLMON12S.SQL: блочный мониторинг длинных планов выполнения

Filed under: Active Session History,Oracle,Scripts — Игорь Усольцев @ 23:48
Tags: ,

Причины длительного выполнения запросов с обширным планом бывает непросто увидеть/определить как в связи объёмом, так и в связи с ненадёжностью / периодическим прерыванием функционирования DBMS_SQLTUNE.REPORT_SQL_MONITOR сообщениями типа:

Global Information
------------------------------
 Status                                 :  DONE (ERROR)

— для всё ещё продолжающих выполняться запросов

Идея скрипта проста — сгруппировать ASH строки по имени Query Block определённого плана выполнения, например так: (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…)

15.07.2015

12c: Сравнение планов выполнения и выявление соответствий в паре PLAN_HASH_VALUE >-< FULL_PLAN_HASH_VALUE

Filed under: Active Session History,CBO,Oracle — Игорь Усольцев @ 00:44
Tags: ,

Для одного из запросов внезапно сработал Statistics Feedback, что в V$SQL отразилось следующим образом:

EXEC LOAD_TIME   OPENING LAST_ACTIVE_TIME ELA_PER_EXEC PLAN_HASH_VALUE    COST CHILD BIND_SENSITIVE BIND_AWARE SHARABLE FEEDBACK_STATISTIC    ROWS PARSE_CALLS
---- ----------- ------- ---------------- ------------ --------------- ------- ----- -------------- ---------- -------- ------------------ ------- -----------
   1 29.06 12:07       0      29.06 12:45   1912450710      2108937779  396162     0 N              N          N        Y                  1106472           1
   1 29.06 15:17       1      30.06 15:07  85765923098      3773907172 5597267     1 N              N          Y        N                        0           1

— т.е. 29.06 в 12:45 запрос выполнялся с нормальным планом 2108937779, а при следующем выполнении в 30.06 в 15:07 под действием FEEDBACK_STATISTIC=Y с новым планом 3773907172 наблюдается затянувшееся выполнение, что в принципе не является чем-то необычным, любопытно что в AWR таблице DBA_HIST_SQLSTAT оба выполнения записаны с одним планом 2108937779, что, мягко говоря, не способствует правильной диагностике (первая строка SNAP_ID=87604 соотв.1-му, последующие — 2-му выполнению):

12.1.0.2.@ SQL> @dba_hist_sqlstat "sql_id = '38dyq2ab12nju' and snap_id >= 87605"
 
INST BEGIN_SNAP_ID BEGIN_SNAP_TIME    EXECS ROWS_PROCESSED SQL_ID              PLAN       COST PARSE_PER_EXEC ELA_PER_EXEC CPU_PER_EXEC GETS_PER_EXEC DISK_READS_PER_EXEC READ_MB_PER_EXEC READS_PER_EXEC WRITES_MB_PER_EXEC WRITES_PER_EXEC DIRECT_WRITES_PER_EXEC ROWS_PER_EXEC FETCHES_PER_EXEC IOWAITS_PER_EXEC CLWAITS_PER_EXEC_US
---- ------------- --------------- -------- -------------- ------------- ---------- ---------- -------------- ------------ ------------ ------------- ------------------- ---------------- -------------- ------------------ --------------- ---------------------- ------------- ---------------- ---------------- -------------------
   2         87604 29.06 12:30            0              0 38dyq2ab12nju 2108937779     396162              0    553193496    117012000      10507935             1083047             8461         954997                910            7763                 116445       1106472           158068        462927031             3887793
   2         87609 29.06 15:00            0              0 38dyq2ab12nju 2108937779     396162              0            0            0             0                   0                0              0                  0               0                      0             0                0                0                   0
   2         87610 29.06 15:30            0              0 38dyq2ab12nju 2108937779     396162              0            0            0             0                   0                0              0                  0               0                      0             0                0                0                   0
...
   2         87655 30.06 14:00            0              0 38dyq2ab12nju 2108937779     396162              0            0            0             0                   0                0              0                  0               0                      0             0                0                0                   0
   2         87656 30.06 14:30            0              0 38dyq2ab12nju 2108937779     396162              0            0            0             0                   0                0              0                  0               0                      0             0                0                0                   0

(more…)

19.06.2015

Короткие ожидания enq: TX — row lock contention в запросах FOR UPDATE SKIP LOCKED

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

Пример использования блокировок на уровне строки наблюдали с Дмитрием Балабановым для запросов этого типа:

11.2.0.3.@ SQL> @ash_sql_wait_tree "event = 'enq: TX - row lock contention' and sql_id in (select sql_id from gv$sqlarea where upper(sql_fulltext) like '%FOR%UPDATE%SKIP%LOCKED%' and command_type = 3)"


LVL  INST_ID BLOCKING_TREE  EVENT                          WAITS_COUNT  EXECS_COUNT  AVG_WAIT_TIME_MS DATA_OBJECT               BLOCK_SID       SQL_ID        SQL_PLAN_LINE_ID SQL_PLAN_OPERATION SQL_TEXT
--- -------- -------------- ----------------------------- ------------ ------------ ----------------- ------------------------- --------------- ------------- ---------------- ------------------ ----------------------------------------
  1        2 (USER)         enq: TX - row lock contention         1766         1764                 0 MY.AQ_TABLE.              GLOBAL i#       5cbp4s5jq04xg                1 FOR UPDATE         select  /*+ INDEX(TAB AQ$_AQ_TABLE_I) */
  1        1 (USER)         enq: TX - row lock contention         1163         1162                 0 MY.AQ_TABLE.              GLOBAL i#       5cbp4s5jq04xg                1 FOR UPDATE         select  /*+ INDEX(TAB AQ$_AQ_TABLE_I) */
  1        2 (USER)         enq: TX - row lock contention          304          304                 0 MY.U_TAABLE.U_PARTITION   GLOBAL i#       8x5xmcz928ydq                1 FOR UPDATE         SELECT                                   -- *
  1        1 (USER)         enq: TX - row lock contention          288          288                 0 MY.U_TAABLE.U_PARTITION   GLOBAL i#       8x5xmcz928ydq                1 FOR UPDATE         SELECT                                   -- *
  1        2 (USER)         enq: TX - row lock contention           29           29                 0 MY.AQ_TABLE.              UNKNOWN i#      5cbp4s5jq04xg                1 FOR UPDATE         select  /*+ INDEX(TAB AQ$_AQ_TABLE_I) */
  1        1 (USER)         enq: TX - row lock contention           11           11                 0 MY.AQ_TABLE.              UNKNOWN i#      5cbp4s5jq04xg                1 FOR UPDATE         select  /*+ INDEX(TAB AQ$_AQ_TABLE_I) */
  1        1 (USER)         enq: TX - row lock contention            7            7                 0 MY.U_TAABLE.OEBS          GLOBAL i#       fkrrfw2jmg1jw                1 FOR UPDATE         SELECT                                   -- *
  1        1 (USER)         enq: TX - row lock contention            7            7                 0 MY.U_TAABLE.U_PARTITION2  GLOBAL i#       8x5xmcz928ydq                1 FOR UPDATE         SELECT                                   -- *
  1        2 (USER)         enq: TX - row lock contention            3            3                 0 MY.AQ_TABLE.              VALID i#1       5cbp4s5jq04xg                1 FOR UPDATE         select  /*+ INDEX(TAB AQ$_AQ_TABLE_I) */
  1        2 (USER)         enq: TX - row lock contention            3            3                 0 MY.U_TAABLE.U_PARTITION2  GLOBAL i#       8x5xmcz928ydq                1 FOR UPDATE         SELECT                                   -- *
  1        1 (USER)         enq: TX - row lock contention            2            2                 0 MY.AQ_TABLE.              VALID i#2       5cbp4s5jq04xg                1 FOR UPDATE         select  /*+ INDEX(TAB AQ$_AQ_TABLE_I) */
  1        1 (USER)         enq: TX - row lock contention            1            1                 0 MY.U_LOCKS_TABLE.         GLOBAL i#       5c4mptv666r2x                1 FOR UPDATE         SELECT ID FROM U_LOCKS_TABLE WHERE NAME  -- *
  2        1   (USER)       On CPU / runqueue                        1            1                 0 ..                        NOT IN WAIT i#  5cbp4s5jq04xg                3 INDEX RANGE SCAN   select  /*+ INDEX(TAB AQ$_AQ_TABLE_I) */ -- **
  2        1   (USER)       On CPU / runqueue                        1            1                 0 MY.AQ$_AQ_TABLE_I.        NOT IN WAIT i#  5cbp4s5jq04xg                3 INDEX RANGE SCAN   select  /*+ INDEX(TAB AQ$_AQ_TABLE_I) */ -- **

— т.е. to skip any rows that are found to be already locked by another transaction, в ASH фиксируются формальные блокировки с 0-й длительностью AVG_WAIT_TIME_MS, вычисляемой как AVG(TIME_WAITED) / 1000

Среди запросов с использованием SKIP LOCKED, вролне естественно, много AQ-запросов, есть пользовательские запросы (*) и даже отмечена пара AQ-блокеров (**)

25.03.2015

12c: ASH.SQL_PLAN_HASH_VALUE и адаптивные эффекты

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

В Active Session History (ASH) версии 11g содержалось относительно небольшое кол-во записей с PLAN_HASH_VALUE = 0 о запросах в стадии выполнения (IN_SQL_EXECUTION = ‘Y’):

11.2@ SQL> select decode(sql_plan_hash_value, 0, '0', '>0') as sql_plan_hash_value,
  2         case when sql_plan_line_id is null then 'NULL' else '>= 0' end as SQL_PLAN_LINE_ID,
  3        count(*)
  4   from v$active_session_history
  5  where IN_PARSE         = 'N'
  6    and IN_HARD_PARSE    = 'N'
  7    and IN_SQL_EXECUTION = 'Y'
  8  group by decode(sql_plan_hash_value, 0, '0', '>0'),
  9           case when sql_plan_line_id is null then 'NULL' else '>= 0' end
 10  order by count(*)
 11  /
 
SQL_PLAN_HASH_VALUE SQL_PLAN_LINE_ID   COUNT(*)
------------------- ---------------- ----------
>0                  NULL                   1120
0                   NULL                   1903 -- немного, SQL_PLAN_LINE_ID is NULL - "служебные записи"
>0                  >= 0                 141186

, и, в основном, это ожидаемые куски PL/SQL кода или какие-то специальные случаи типа ожиданий по db link, etc…

В Oracle 12c кол-во таких записей может внезапно/периодически увеличиться:

12.1@ SQL> select decode(sql_plan_hash_value, 0, '0', '>0') as sql_plan_hash_value,
  2         count(*)
  3    from v$active_session_history
  4   where IN_PARSE         = 'N'
  5     and IN_HARD_PARSE    = 'N'
  6     and IN_SQL_EXECUTION = 'Y'
  7   group by decode(sql_plan_hash_value, 0, '0', '>0')
  8   order by count(*)
  9  /
 
SQL_PLAN_HASH_VALUE   COUNT(*)
------------------- ----------
0                        33215
>0                      163247

(more…)

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

Блог на WordPress.com.