Oracle mechanics

14.07.2017

12c CDB: особенности отображения блокировок, скрипт LOCK_TREE_LOCAL_CDB

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

В PDB snapshot copy окружении версии 12.1.0.2:

SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;

Session altered.

SQL> select con_id, name from v$containers;

CON_ID NAME
------ ---------------
     1 CDB$ROOT
     2 PDB$SEED
     3 PDBCLONE0101
...
    15 PDBCLONE0113
...

18 rows selected.

Артём Горбик показал странно отображаемую сессию-блокер:

SQL> select service_name,con_id,program from gv$session where sid=4921;

SERVICE_NAME CON_ID PROGRAM
------------ ------ ----------------------
SYS$USERS         1 oracle@hostname (J017)

— формально работающую в CDB$ROOT:

SQL> select name, con_id from v$containers where con_id=1;

NAME                     CON_ID
-------------------- ----------
CDB$ROOT                      1

, и в то же время выполняющую Scheduler Job в контейнере-клоне:

SQL> select SESSION_ID,JOB_NAME,con_id from cdb_scheduler_running_jobs where session_id = 4921;

SESSION_ID JOB_NAME               CON_ID
---------- ------------------ ----------
      4921 update_mviews              15

SQL> select name, con_id from v$containers where con_id=15;

NAME                     CON_ID
-------------------- ----------
PDBCLONE0113                 15

Модифицировал под это дело скрипт (с суффиксом _CDB), добавив к инстансу/сессии (INST#/SID#4) номер контейнера — CON#: (more…)

Реклама

29.01.2017

Проблема ограничения длины хинта SQL_PROFILE_FROM_SQL.SQL

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

Коллега Леонид Борчук в процессе практического применения скрипта sql_profile_from_sql.sql обнаружил ошибку:

 ORA-06502: PL/SQL: numeric or value error: character string buffer too small

и указал на ограничение:

TYPE sqlprof_attr AS VARRAY(2000) of VARCHAR2(500)

— т.е. нашёл-таки запрос с хинтом длиною более 500 символов! — для запросов OEBS нет пределов:)

А, поскольку, в мировом масштабе, эта проблема уже была разрешена Carlos SierraRunning «coe_xfr_sql_profile.sql» Script (Shipped with SQLT) Raises «ORA-06502: PL/SQL: numeric or value error» (Doc ID 2043600.1) в новых версиях SQLT, оставалось лишь посмотреть как это сделано:

begin
  LOOP
    l_hint := i.hint;
    WHILE NVL(LENGTH(l_hint), 0) > 0 LOOP
      IF LENGTH(l_hint) <= 500 THEN
        DBMS_OUTPUT.PUT_LINE('q''[' || l_hint || ']'',');
        l_hint := NULL;
      ELSE
        l_pos := INSTR(SUBSTR(l_hint, 1, 500), ' ', -1);
        DBMS_OUTPUT.PUT_LINE('q''[' || SUBSTR(l_hint, 1, l_pos) || ']'',');
        l_hint := ' ' || SUBSTR(l_hint, l_pos);
      END IF;
    END LOOP;
  END LOOP;
end;

— через разделение отдельного хинта по пробелу (оказывается, так можно!) и применить в sql_profile_from_sql.sql — что и было сделано

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

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

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

04.04.2016

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')
...

Блог на WordPress.com.