Oracle mechanics

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

15.04.2016

Рекурсивно-адаптивный SQL_ID «frjd8zfy2jfdq» версии 12.1.0.2

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

Максим Филатов обратил внимание на заметно High Version Count запрос:

SQL ordered by Version Count         DB/Inst: ORCL/ORCL1  Snaps: 101565-101567
-> Only Statements with Version Count greater than 20 are displayed

 Version                           
  Count   Executions     SQL Id    
-------- ------------ -------------
     916          N/A frjd8zfy2jfdq
...

, судя по множеству источников / MODULE-й:

SQL> select decode(session_type, 'BACKGROUND', REGEXP_SUBSTR(program, '\([^\)]+\)'), nvl2(qc_session_id, 'PX', 'USER')) as PROGRAM_TYPE,
  2         count(*),
  3         count(distinct module)
  4    from gv$active_session_history
  5   where sql_id = 'frjd8zfy2jfdq'
  6   group by decode(session_type, 'BACKGROUND', REGEXP_SUBSTR(program, '\([^\)]+\)'), nvl2(qc_session_id, 'PX', 'USER'))
  7  /
 
PROGRAM_TYPE                                       COUNT(*) COUNT(DISTINCTMODULE)
------------------------------------------------ ---------- ---------------------
PX                                                       66                    43

— похожий на рекурсивный, выполняемый, в основном, PX-slave процессами

Как оказалось, запрос этот удостоен отдельной ноты Frequent Execution of SQL_ID «frjd8zfy2jfdq» in 12.1.0.2 (Doc ID 2059121.1): (more…)

06.04.2016

Изменение параметров SPMBaseline с помощью SQL Patch

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

Предположим запрос, периодически потребляющий TEMPORARY TABLESPACE в недопустимых объёмах:

SQL> @v$sqlstats fxgzfhx4fr9rv
 
INST EXECS SQL_ID              PLAN ELA_PER_EXEC CPU_PER_EXEC GETS_PER_EXEC CONC_PER_EXEC CLU_PER_EXEC UIO_PER_EXEC READ_MB_PER_EXEC READS_PER_EXEC DISK_READS_PER_EXEC WRITES_MB_PER_EXEC WRITES_PER_EXEC DIRECT_WRITES_PER_EXEC ROWS_PER_EXEC PX_PER_EXEC
---- ----- ------------- ---------- ------------ ------------ ------------- ------------- ------------ ------------ ---------------- -------------- ------------------- ------------------ --------------- ---------------------- ------------- -----------
   2     5 fxgzfhx4fr9rv 1662599281      9728601      6012200        163289         43900       107579      3759166              228           5265                   0                  0               0                      0            13           0
   1     1 fxgzfhx4fr9rv 1494936070  27639543205   3917421000     769437420    2212170898   2758482394   3583471835             1029          17400                   0             125171         1068134               16019535          1888           0
 
SQL_EXEC_ID SQL_PLAN_HASH_VALUE ACTION             MAX_TEMP_GB MAX_PGA_GB MAX_PX_USED
----------- ------------------- ------------------ ----------- ---------- -----------
   16777225           792237607 Concurrent Request           0      0,029           0
   16777226          1494936070 Concurrent Request           0      0,225           0
   16777227          1494936070 Concurrent Request      72,007       0,19           0
   16777228          1494936070 Concurrent Request      39,393      0,879           0
   16777229          1494936070 Concurrent Request      14,435      0,704           0
   16777230          1494936070 Concurrent Request       2,726      0,878           0
   16777231          1494936070 Concurrent Request       1,894      0,879           0
   33554442          1662599281 Concurrent Request           0      0,054           0
   33554443          1662599281 Concurrent Request           0       0,05           0
   33554444          1662599281 Concurrent Request           0      0,042           0
   33554445          1662599281 Concurrent Request           0      0,039           0
   33554440          3996693969 Concurrent Request       3,127       1,13           0
   33554441          3996693969 Concurrent Request       3,142      1,145           0

— при этом повышенное потребление TEMP (и DIRECT_WRITES_PER_EXEC, соотв-но) было характерно для использования плана 1494936070, и не отмечается для плана 1662599281, т.е. зависело, в основном, от выбора плана выполнения и слабо зависело от прочих исходных данных и значений bind-ов — судя по BIND_SENSE(V$SQL.IS_BIND_SENSITIVE) и BIND_AWARE(V$SQL.IS_BIND_AWARE): (more…)

04.04.2016

12c: 2 x FULL_PLAN_HASH_VALUE для одного плана выполнения / PLAN_HASH_VALUE и скрипт ASH_SQLMON12.SQL

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

При интенсивном выполнении (конец месяца, подсчёт итогов,..) запроса типа SELECT наблюдал картину:

SQL> @shared_cu12_noxml 35hnzb2q6xrkc
 
  EXECS LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC CPU_PER_EXEC GETS_PER_EXEC READS_PER_EXEC UIO_PER_EXEC CLU_PER_EXEC CURSOR_STATUS PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS REOPT REOPT_HINTS ADAPT SPD_Valid SPD_Used  DS_LEVEL BIND_EQ_FAILURE REASON#1          SQL_PLAN_BASELINE SQL_PATCH OUTLINE_CATEGORY SQL_PROFILE IS_OBSOLETE
------- -------------------- ------------------- ------------ ------------ ------------- -------------- ------------ ------------ ------------- --------------- -------------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- --------------- ----------------- ----------------- --------- ---------------- ----------- -----------
   1015 2016-04-01/10:10:12  01.04.2016 10:16:44       470644        74060         24316            462       303651       107095 VALID               434062702           3150513221         194928     3 Y          Y          N          N                  N                 Y     5         2         2        Y               Bind mismatch(33)                                                          N
    384 2016-04-01/10:16:47  01.04.2016 10:19:05       482506        66008         20388            401       309564       118371 VALID               434062702           3150513221          11350     5 Y          N          N          N                  N                 Y     5         2         2        N               Bind mismatch(25)                                                          N
    481 2016-04-01/10:19:05  01.04.2016 10:23:02       440066        44582         13717            295       214687       188648 VALID               434062702           3150513221             72     6 Y          Y          N          N                  N                 Y     5         2         2        N               Bind mismatch(33)                                                          N
    538 2016-04-01/10:22:06  01.04.2016 10:27:07       622140        69344         24159            422       305390       258859 VALID               434062702           3150513221         315650     7 Y          Y          N          N                  N                 Y     6         1         2        Y               Bind mismatch(33)                                                          N
   2426 2016-04-01/10:27:04  01.04.2016 10:35:02       324502        49090         15276            266       209320        73840 VALID               434062702           3150513221            345     8 Y          Y          N          N                  N                 Y     5         2         2        Y               Bind mismatch(33)                                                          N
  18369 2016-04-01/10:34:44  01.04.2016 10:54:36       212043        24282          6648            142       121101        69878 VALID               434062702           3150513221          11390     2 Y          Y          N          N                  N                 Y     5         2         2        Y               Bind mismatch(33)                                                          N
  11009 2016-04-01/10:54:26  01.04.2016 11:00:00       103472        16452          4501             87        70872        18074 VALID               434062702           3150513221          42424     9 Y          Y          N          N                  N                 Y     5         2         2        Y               Bind mismatch(33)                                                          N
  15439 2016-04-01/10:59:58  01.04.2016 11:07:58       157499        16593          4644             78       127386        14786 VALID               434062702           1196564125         385981    10 Y          Y          N          N                  N                       5         2         2        Y               Bind mismatch(33)                                                          N
 148072 2016-04-01/11:07:57  01.04.2016 12:01:52        53123         9772          2884             50        41752         2766 VALID               434062702           3150513221           8859    11 Y          Y          Y          N                  N                 Y     5         2         2        Y                                                                                          N
 
10 rows selected
 
--------------------------------------------------------------
SQL_ID=35hnzb2q6xrkc Shared Pool statistics by PLAN_HASH_VALUE
--------------------------------------------------------------
 
  EXECS ELA_PER_EXEC CPU_PER_EXEC GETS_PER_EXEC READS_PER_EXEC UIO_PER_EXEC CONC_PER_EXEC CLU_PER_EXEC FETCH_PER_EXEC ROWS_PER_EXEC PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE AVG_CBO_COST     CHILDS BIND_SENSE BIND_AWARE REOPT ADAPT SQL_PLAN_BASELINE SQL_PATCH OUTLINE_CATEGORY SQL_PROFILE
------- ------------ ------------ ------------- -------------- ------------ ------------- ------------ -------------- ------------- --------------- -------------------- ------------ ---------- ---------- ---------- ----- ----- ----------------- --------- ---------------- -----------
  15439       157499        16593          4644             78       127386            16        14786              1             1       434062702           1196564125       385981          1 Y          Y          N     N                                                  
 182294        81718        12905          3774             69        56993            32        13470              1             1       434062702           3150513221        73127          8 Y          Y          N     Y

— любопытную прежде всего тем, что по причине BIND_AWARE / Bind mismatch(33) Oracle периодически перегенерировал один и тот же курсор с неизменным рез-татом в виде плана PLAN_HASH_VALUE = 434062702, но с использованием разных FULL_PLAN_HASH_VALUE — см. CHILD 9-11, например (more…)

12c: данные SPM Baseline в SYS.SQLOBJ$PLAN, необходимое уточнение

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

В дополнение к одной из предыдущих заметок оказалось, что в SYS.SQLOBJ$PLAN хранятся данные только «новых» SPM Plan Baseline-ов, созданных в версии 12c

Данные «унаследованных» Baseline-ов версии 11g после обновления так и остаются в табличке SYS.SQLOBJ$DATA

Т.е. если используются Baseline-ы разных версий:

12.1.0.2@ SQL> select plan_name, version from dba_sql_plan_baselines order by 2;
 
PLAN_NAME                       VERSION
------------------------------- ----------
SQL_PLAN_8yh6xn9ncndbf8f932b0a  11.2.0.3.0 -- *
...
SQL_PLAN_3yyva1p88muu4109f0d5f  11.2.0.3.0
SQL_PLAN_593vgbak4tb3409c26202  12.1.0.2.0
...

— то запрос к SYS.SQLOBJ$PLAN для Baseline-а версии 11.2 (*) ничего не даст:

SQL> @spb12 SQL_PLAN_8yh6xn9ncndbf8f932b0a

Дополненный соответствующим UNION ALL SYS.SQLOBJ$DATA скрипт SPB12.SQL правильно отображает содержимое Baseline-ов, созданных в обеих версиях:

SQL> @spb12 SQL_PLAN_8yh6xn9ncndbf8f932b0a
 
OUTLINE_HINTS
-------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
...

30.03.2016

12c: Automatic Dynamic Statistics в присутствии SPM Baseline — 2. Testcases

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

В дополнение к предыдущему могут быть полезны простые testcase-ы поведения Oracle 12.1.0.2 при разборе / hard parse запроса с применением SQL Plan Baseline

Первый case был предложен/подготовлен коллегой Русланом Бикбаевым на противопоставлении Baseline-а,фиксирующего FULL TABLE SCAN и добавленным позднее индексом:

SQL> create table large_table (c1 number, c2 varchar2 (1000))
  2  /

Table created.

SQL> insert into large_table
  2    select level, 'some fool text and number ' || level
  3      from dual
  4    connect by level < 1000000 5 / 999999 rows created. SQL> exec dbms_stats.gather_table_stats('','LARGE_TABLE')

PL/SQL procedure successfully completed.

SQL> select c2 from large_table where c1 = 402200;

C2
---------------------------------------------------------------------------------
some fool text and number 402200

1 row selected.

SQL> @xplan "" "" "-predicate +alias"

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_ID  cgrtc39b07cg4, child number 0
-------------------------------------
select c2 from large_table where c1 = 402200

Plan hash value: 1101256009

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |       |       |  1643 (100)|          |
|   1 |  TABLE ACCESS FULL| LARGE_TABLE |     1 |    38 |  1643   (1)| 00:00:01 |
---------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / LARGE_TABLE@SEL$1

SQL> -- фиксация плана 
SQL> @bl_create cgrtc39b07cg4 1101256009 "2test_baseline"

Baseline SQL_ff24385815ccded7 SQL_PLAN_gy91sb0awtrqr07ba49cd was [re]created
for SQL_ID=cgrtc39b07cg4, SQL_PLAN_HASH=1101256009

SQL> create index lt_idx on large_table(c1)
  2  /

Index created.

SQL> select c2 from large_table where c1 = 402200;

1 row selected.

SQL> @xplan "" "" "-predicate +alias"

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_ID  cgrtc39b07cg4, child number 1
-------------------------------------
select c2 from large_table where c1 = 402200

Plan hash value: 1101256009

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |       |       |  1643 (100)|          |
|   1 |  TABLE ACCESS FULL| LARGE_TABLE |     1 |    38 |  1643   (1)| 00:00:01 |
---------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / LARGE_TABLE@SEL$1

Note
-----
   - SQL plan baseline SQL_PLAN_gy91sb0awtrqr07ba49cd used for this statement

, 10053 трейс которого отчётливо показывает все 3 фазы разбора: (more…)

25.03.2016

12c: Automatic Dynamic Statistics в присутствии SPM Baseline

Восстановление default значения optimizer_dynamic_sampling = 2, последовавшее после обновления на 12c выявило краткосрочную проблему:

Top 5 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Total Wait       Wait   % DB Wait    
Event                                Waits Time (sec)    Avg(ms)   time Class   
------------------------------ ----------- ---------- ---------- ------ -----------
DB CPU                                          53.2K              55.8         
db file sequential read          1,998,398      15.5K       7.74   16.2 User I/O
cursor: pin S wait on X             29,094      11.5K     396.15   12.1 Concurrency -- тут
db file parallel read               72,823       3284      45.09    3.4 User I/O
db file scattered read             164,408       2134      12.98    2.2 User I/O

локализованную одним запросом:

12.1.0.2.@ SQL> @ash_sql_wait_tree "event = 'cursor: pin S wait on X'"
 
LVL BLOCKING_TREE  EVENT                    WAIT_CLASS   WAITS_COUNT EXECS_COUNT AVG_WA SESS_COUNT BLOCK_SID  MIN_STIME                   MAX_STIME                   SQL_ID        SQL_PLAN_HASH_VALUE SQL_TEXT
--- -------------- ------------------------ ------------ ----------- ----------- ------ ---------- ---------- --------------------------- --------------------------- ------------- ------------------- -----------------------------------------------
  1 (USER)         cursor: pin S wait on X  Concurrency         9872           0    991        214 VALID i#1  03-MAR-16 03.00.13.029 PM   03-MAR-16 03.01.36.228 PM   5rrd8z9nt7t2j          1613548637 select 1 from dual where exists (select 1  from
  1 (USER)         cursor: pin S wait on X  Concurrency          474           0    986         17 VALID i#1  03-MAR-16 03.00.16.029 PM   03-MAR-16 03.01.31.218 PM   5rrd8z9nt7t2j           633295841 select 1 from dual where exists (select 1  from
...
  1 (USER)         cursor: pin S wait on X  Concurrency          135           0    988          4 VALID i#1  03-MAR-16 03.00.19.029 PM   03-MAR-16 03.01.29.218 PM   5rrd8z9nt7t2j          2447725225 select 1 from dual where exists (select 1  from
  1 (USER)         cursor: pin S wait on X  Concurrency          104           0    981          3 VALID i#1  03-MAR-16 03.00.14.029 PM   03-MAR-16 03.01.29.218 PM   5rrd8z9nt7t2j          3045292599 select 1 from dual where exists (select 1  from
...

с одним действующим PHV=1613548637 (и несколькими переходными «фантомными» не оставившими следов планами) в течение непродолжительной фазы hard [re-]parse последовавшей сразу после модификации параметра OPTIMIZER_DYNAMIC_SAMPLING. По природе ожидания конкуренция (WAIT_CLASS=»Concurrency») естественно наблюдалась между сессиями, ожидавшими окончания parse/разбора того же самого курсора (more…)

02.03.2016

12c: данные SPM Baseline в SYS.SQLOBJ$PLAN

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

Ежели в версии 11g Oracle хранил данные всех элементов системы SQL Plan Mangement (SPM, включая SQL Patch, SQL Profile и SQL Plan Baseline (SPB)) в SYS.SQLOBJ$DATA.COMP_DATA, то, начиная с 12.1 данные, составляющие собственно Baseline, переместились в clob OTHER_XML новой таблицы SQLOBJ$PLAN, которая также содержит полный план выполнения на момент создания SPM Baseline-а

Этот сохранённый план может быть любопытен в иллюстративных целях, например, при просмотре через Enterprise Manager/Cloud Control, или через соответствующий command line скрипт SPB12.SQL представляет информацию в следующем виде: (more…)

18.02.2016

12c: эффекты Automatic Dynamic Sampling

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

Невысокая скорость выполнения запроса 8tx5kfa369rdb:

SQL> @shared_cu12_noxml 8tx5kfa369rdb 0
 
EXECS USERS_OPENING LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC CPU_PER_EXEC PARSES_PER_EXEC GETS_PER_EXEC READS_PER_EXEC UIO_PER_EXEC CONC_PER_EXEC 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 SQL_PLAN_BASELINE SQL_PATCH OUTLINE_CATEGORY SQL_PROFILE
----- ------------- -------------------- ------------------- ------------ ------------ --------------- ------------- -------------- ------------ ------------- --------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- -------- ----------------- --------- ---------------- -----------
 2374             9 2016-02-08/12:47:01  12.02.2016 18:33:56   1474675990   1032495912               1        304100           8512     43756160     340539863      4044237314          10748     6 Y          N          Y          N                  N                       12        0                                                                                   

— ср.время / ELA_PER_EXEC ~ 1474 сек., много CPU и Concurrency, что визуально ассоциируется с 9 (в данном примере) одновременно открытыми курсорами / [V$SQL.]USERS_OPENING при полном отсутствии адаптивных эффектов (столбцы USE_FEEDBACK_STATS, REOPT, REOPT_HINTS, ADAPT, SPD_Used и DS_LEVEL) и нежданное событие ожидания в топе AWR:

Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Total Wait       Wait   % DB Wait    
Event                                Waits Time (sec)    Avg(ms)   time Class   
------------------------------ ----------- ---------- ---------- ------ --------
DB CPU                                         181,6K              70.2         
latch: row cache objects        21,615,357      33,1K       1.53   12.8 Concurre -- тут
db file sequential read          4,369,250      23,5K       5.37    9.1 User I/O

судя по SQL Monitoring-у оказались связаны:) (more…)

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

Тема: Rubric. Блог на WordPress.com.

Отслеживать

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

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