Oracle mechanics

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

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

10.10.2014

Простая проблема при создании AWR снапшотов в Oracle 11g

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

Проблема действительно несложная (более интересный вариант отписывал ранее) и выражается, кроме собственно прекращения генерации снапшотов, сообщениями в alert.log вида:

ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_1245278029_19314 by 128 in tablespace SYSAUX
ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_1245278029_19314 by 8192 in tablespace SYSAUX

, ошибками в трейсах процесса Manageability Monitor (mmon) и детей ея m00x, выраженным ростом партиций сегмента WRH$_LATCH_CHILDREN:

11.2.0.4@ SQL> select * from dba_segments where tablespace_name = 'SYSAUX' and bytes > 1e9 order by bytes desc;
 
SEGMENT_NAME                    PARTITION_NAME                 SEGMENT_TYPE       SEGMENT_SUBTYPE TABLESPACE_NAME      BYTES     BLOCKS
------------------------------- ------------------------------ ------------------ --------------- --------------- ---------- ----------
WRH$_LATCH_CHILDREN             WRH$_LATCH__1245278029_19314   TABLE PARTITION    ASSM            SYSAUX          3414687744    4168320
WRH$_LATCH_CHILDREN_PK          WRH$_LATCH__1245278029_19314   INDEX PARTITION    ASSM            SYSAUX          2771281510    3382912
WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_1245278029_19314   TABLE PARTITION    ASSM            SYSAUX          1407188992     171776

, отражена в документе поддержки SYSAUX Tablespace can Become Full Due to WRH$_LATCH_CHILDREN growth (Doc ID 874518.1) и чаще всего возникает при установке statistics_level=ALL на уровне системы

Некоторой определённости заслуживают быстрые методы очистки SYSAUX и перезапуска mmon_slave процессов для возобновления автогенерации снапшотов (more…)

21.05.2014

Ожидание utl_file I/O

Filed under: AWR,Oracle,wait events — Игорь Усольцев @ 21:51
Tags: , ,

При анализе проблем длительного выполнения concurrent request в системе OEBS, зная CLIENT_ID/SID-ы медленно выполнявшихся дочерних процессов и номера начального/конечного снапшотов интересующего периода, достаточно просто выделить «проблемный» запрос из истории ASH:

11.2.0.4@ SQL> @ash_sql_wait_tree_hist "client_id = 'USER007' and (instance_number, session_id, session_serial#) in ((1,2251,19231),(1,1707,26715))" 67983 67991
 
LVL INST_ID BLOCKING_TREE  CLIENT_ID   EVENT                          WAITS_COUNT EXECS_COUNT SESS_COUNT AVG_WA SQL_ID        TOP_LEVEL_SQL_ID SQL_TEXT
--- ------- -------------- ----------- ------------------------------ ----------- ----------- ---------- ------ ------------- ---------------- ------------------------------------------------
  1       1 FOREGROUND     USER007     On CPU / runqueue                      940        1064          2      0 c4wtbv2xcmq78 c4wtbv2xcmq78    begin fnd_file_private.logfile_get(:s, :b); end;
  1       1 FOREGROUND     USER007     On CPU / runqueue                      285           1          2      0                                
  1       1 FOREGROUND     USER007     library cache: mutex X                  30          30          2     10 c4wtbv2xcmq78 c4wtbv2xcmq78    begin fnd_file_private.logfile_get(:s, :b); end;
  1       1 FOREGROUND     USER007     library cache: mutex X                  14           0          2     11                                
...

и убедившись, что запрос c4wtbv2xcmq78 выполнялся в это время только сессиями интересующего нас клиента: (more…)

19.04.2014

Практический тест параллельной последовательной (многоблочной) записи на SSD

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

Некоторое время назад с Дмитрием Якубеней использовали возможность протестировать SSD на предмет массированной/параллельной многоблочной записи (что обычно считается слабым местом SSD против обычных дисков), в роли временного/temporary tablespace на бд с преобладающими операциями direct path write temp / direct path read temp в профиле нагрузки

Кратко можно отметить, что один SSD почти успешно заменил семь SATA (7,200 rpm) дисков в части записи-чтения временных сегментов на умеренно нагруженной бд DWH профиля. При этом на протяжении тестов все остальные файлы бд оставались на дисковой группе SATA, однако временная запись составляла заметно более 50% всей записи, а чтение >= 35-40% от общего объёма чтения бд. Почти успешно потому, что замедление временной записи всё-таки было заметно для отдельных запросов в относительно высоким DOP

Некоторые детали и прочая статистика (more…)

23.02.2014

Отличная презентация по анализу AWR

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

Для тех, кто ещё не видел — Interpreting AWR Report — Straight to the Goal by Franck Pachot

 Interpreting AWR Report

— виртуозно по испонению и полезно по содержанию!

21.01.2014

Ожидание log file sync на медленных дисках

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

В результате переноса бд с SAS на более ёмкие SATA (в обоих случаях — ASM), с заметной периодичностью на уровне ОС стали наблюдаться симптомы перегрузки отдельных дисков в случайном порядке следующего типа:

# iostat -x 2 /dev/sd*5
...
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          12.81    0.00    0.66   10.37    0.00   76.16

Device:         rrqm/s   wrqm/s    r/s    w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda5              0.00     0.00   0.00   0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdb5              9.50     0.00  82.00 181.50 17616.00  6864.00    92.90     3.08   11.69   1.68  44.15
sdc5             11.00     0.00 113.00 176.50 22608.00  7136.00   102.74     3.23   11.17   1.70  49.15
sdh5             11.50     0.00  53.50 207.50 19120.00  8292.00   105.03     3.84   14.74   1.80  46.85
sdi5              8.00     0.00  47.50 148.50 15568.00  5616.00   108.08     1.61    8.16   1.65  32.35
sdj5              7.50     0.00  56.50 163.50 16016.00  6576.00   102.69     2.70   12.28   1.77  38.85
sdg5             10.00     0.00  58.50 185.00 19104.00  7344.00   108.62     3.77   15.49   1.82  44.40
sdd5             15.50    15.50  94.00 182.00 21840.00  8000.00   108.12   115.31  494.38   3.62 100.00 -- здесь,
sde5             13.00     5.00  68.00 179.50 15536.00  7760.00    94.13   129.35  507.48   4.04 100.00 -- здесь
sdn5              9.00     0.00  67.00 178.50 19376.00  6608.00   105.84     2.67   10.85   1.64  40.25
sdl5              7.00     0.00 104.50 191.00 14384.00  7376.00    73.64     2.13    7.22   1.38  40.90
sdf5             15.50     6.00  66.50 145.00 18272.00  5616.00   112.95   137.51  404.19   4.73 100.00 -- и здесь
...

(more…)

06.10.2013

Скрипты для сравнения планов выполнения

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

Копия в интернет-журнале «Форс»

Периодически появляется необходимость сравнить / найти различия в планах выполнения запроса, для последующих глубокомысленных умозаключений и выводов

Пакет DBMS_XPLAN, как я понимаю, вплоть до последних версий такую возможность не реализовал (несмотря на сделанную в 11.2 недокументированную заявку в виде DBMS_XPLAN.DIFF_PLAN_AWR — см.легковоспроизводимый на 12.1.0.1 пример на morganslibrary.org)

А поскольку планы (и запросы) встречаются весьма объёмные и сравнивать их на маленьком экране ноутбука не всегда удобно, написал пару скриптов:

  • PLAN_OL_DIFF_AWR.SQL — для выявления отличий в секции Outline (т.е. в наборах подсказок, собственно, и формирующих сравниваемые планы)
  • PLAN_QB_DIFF_AWR.SQL — для удобства просмотра / анализа отличий планов по конкретным блокам (Query Block)

Далее — пример использования (more…)

24.09.2013

Подсказка PARALLEL в тексте обзора

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

Итак, порядка 1/4 DB Time, как обычно, «вдруг» стало расходоваться на пустое ожидание, связанное с конкуренцией за курсоры в library cache:

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time  Wait Class
------------------------------ ------------ ----------- ------ ------ -----------
DB CPU                                           17,090          39.1
cursor: pin S wait on X               6,590      12,110   1838   27.7 Concurrency -- here be dragonz
db file sequential read             816,817       6,573      8   15.1 User I/O
direct path read                     37,219       1,910     51    4.4 User I/O
log file sync                        43,174       1,140     26    2.6 Commit    

Для начала выясняю какого типа процессы висят на ожидании? (more…)

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

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