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

В 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#:

ORA-00600 [kteoprpect-2]

на physical standby / ADG instance в виде:

Thu Jun 29 19:01:00 2017
Errors in file /opt/oracle/base/diag/rdbms/orcl/orcl1/trace/orcl1_mrp0_16186.trc:
ORA-10562: Error occurred while applying redo to data block (file# 4, block# 1371409)
ORA-10564: tablespace UNDOTBS
ORA-01110: data file 4: '+DGDATA/orcl/datafile/undotbs.259.885215567'
ORA-10560: block type 'EXTENT MAP BLOCK OF SYSTEM MANAGED UNDO SEGMENT'
ORA-00600: internal error code, arguments: [kteoprpect-2], [1498751285], [1498751284], [], [], [], [], [], [], [], [], []
Thu Jun 29 19:01:00 2017
MRP0: Background Media Recovery process shutdown (orcl1)

, как нам с Максимом Филатовым удалось выяснить, вызывается установкой параметра:

12.1.0.2.SYS@ SQL> ALTER SYSTEM SET "_smu_debug_mode"=2049;

System altered.

и, соответственно, лечится (по рекомендации техподдержки):

Формальный SQL тюнинг с использованием SQL Patch

Активно читающий запрос из AWR:

SQL ordered by Reads           DB/Inst: ORCL/orcl2  Snaps: 344667-344671
-> %Total - Physical Reads as a percentage of Total Disk Reads
-> %CPU   - CPU Time      as a percentage of Elapsed Time
-> %IO    - User I/O Time as a percentage of Elapsed Time
-> Total Disk Reads:     369,567,665
-> Captured SQL account for   82.3% of Total

   Physical              Reads              Elapsed                             
      Reads  Executions per Exec   %Total   Time (s)   %CPU    %IO    SQL Id    
----------- ----------- ---------- ------ ---------- ------ ------ -------------
1.38570E+08          87 1.5928E+06   37.5   17,760.1   71.3   26.3 d95aab32us9ur -- вот этот
...
 65,092,102           1 6.5092E+07   17.6   12,031.6   55.6   35.2 apm9v1umcv9sa
...
 37,186,139           0        N/A   10.1    7,222.6   66.2   27.2 b6usrg82hwsa3
...

, заметность которого, похоже, обусловлена выбором и активным использованием, по-видимому, не самого оптимального плана 2944350538:

12c: Dynamic Sampling при индексном доступе к данным

В дополнение к старому описанию особенностей DS версии 10.2 — Dynamic Sampling при индексном доступе к данным — на том же тесткейсе наблюдал как измененилось поведение Old Style Dynamic Sampling (DS) и Adaptive Dynamic Sampling (ADS) в новых версиях Oracle 12c

Итак, тестовый DDL без изменений:

alter session set statistics_level=all;
alter system flush shared_pool;
drop table t1
/
create table t1
as
select * from all_objects
where rownum < 10001
/
create index t1_object_type on t1(object_type);
create index t1_created on t1(created);
alter table t1 add constraint t1_pk primary key (object_id);
exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'T1',method_opt => 'for all columns size 1', cascade => true); -- чтобы DS было чем заняться)
select distinct histogram from user_tab_col_statistics where table_name = 'T1';

1) Old Style Dynamic Sampling, версия 12.1

Конкатенация значений столбцов при превышении VARCHAR2 лимита 4000 bytes

, т.е. в случаях, когда использование VARCHAR2-функций типа LISTAGG заканчивается

ORA-01489: result of string concatenation is too long

LISTAGG_CLOB

Использовавшаяся поначалу User-Defined Aggregate Function типа LISTAGG_CLOB в плане скорости выполнения показывала самые грустные рез-ты:

ORA-1555 / ORA-3170 on read-only standby DBBP 12.1.0.2.160719

наблюдались в виде:

ORA-01555 caused by SQL statement below (SQL ID: 7hgzff6vkrwkz, Query Duration=11 sec, SCN: 0x0000.00000001)
or
ORA-03170: deadlocked on readable physical standby (undo segment 1413)

— очень похоже на Bug 17323222 ORA-1555 / ORA-3170: deadlocked on readable physical standby (undo segment x) on ADG, т.е. ORA-3170 аналогично указывает на несуществующий SEGMENT_ID, а ORA-1555 возникает на коротком Query Duration

Похоже, лечится изменением параметра:

SQL> ALTER SYSTEM SET "_temp_undo_disable_adg"=TRUE;

— случайно в то же время применённым для исправления ORA-07445 [kteclck()+363]

Hintset Object

The plan_baseline hintset is just the list of hints SPM tried to use to reproduce the plan

В ТОПе Library Cache периодически появлялся непривычный объект:

SQL> select *
  2    from (select hash_value,
  3                 substr(name, 1, 100) as object_name,
  4                 namespace,
  5                 type,
  6                 kept,
  7                 count(*),
  8                 min(timestamp),
  9                 sum(locked_total),
 10                 sum(pinned_total),
 11                 sum(loads),
 12                 sum(executions),
 13                 sum(sharable_mem)
 14            from v$db_object_cache
 15           group by hash_value,
 16                    substr(name, 1, 100),
 17                    namespace,
 18                    type,
 19                    kept
 20           order by sum(sharable_mem) desc)
 21   where rownum <= 3
 22  /
 
HASH_VALUE OBJECT_NAME          NAMESPACE      TYPE           KEPT COUNT(*) MIN(TIMESTAMP)      SUM(LOCKED_TOTAL) SUM(PINNED_TOTAL) SUM(LOADS) SUM(EXECUTIONS) SUM(SHARABLE_MEM)
---------- -------------------- -------------- -------------- ---- -------- ------------------- ----------------- ----------------- ---------- --------------- -----------------
2209848120 6004199150836888961  HINTSET OBJECT HINTSET OBJECT NO          1                                 14872             14872          4               0         625277440
 838046317 select               SQL AREA       CURSOR         NO        459 2016-11-02/10:54:33            442985            288445      79518          180659         155325176
 269081421 select               SQL AREA       CURSOR         NO        627 2016-10-31/00:07:03            273128            218432     143717           91650         154551720
...

необычайно крупного размера SHARABLE_MEM > 600MB с цифровым наименованием, под коим числилось 2 объекта:

12c Adaptive Bitmap Pruning — особый случай адаптивного выполнения: PHV, PHV2, FPHV & Outline Hints

В процессе подготовки семинара, столкнулся с любопытным случаем, для адаптивного плана выполнения:

12.1.0.2.SCOTT@/ORCL1201 SQL> exec dbms_stats.gather_schema_stats('SH')

PL/SQL procedure successfully completed.

SQL> SELECT /*+ star_transformation(s)                                   -- star_transformation_enabled = TRUE на уровне запроса
  2             gather_plan_statistics
  3             OPT_PARAM('_optimizer_dsdir_usage_control' 0)            -- для исключения эффектов
  4  --           OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
  5             OPT_PARAM('_optimizer_gather_feedback' 'false') */       -- --//--
  6   c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
  7    FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
  8   WHERE s.time_id = t.time_id
  9     AND s.cust_id = c.cust_id
 10     AND s.channel_id = ch.channel_id
 11     AND c.cust_state_province = 'CA'
 12     AND ch.channel_desc = 'Internet'
 13     AND t.calendar_quarter_desc IN ('1999-01', '1999-02')
 14   GROUP BY c.cust_city, t.calendar_quarter_desc
 15  /

22 rows selected.

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

Коллега Леонид Борчук в процессе практического применения скрипта 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 Sierra — Running «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 — что и было сделано

12c: наблюдаемые ограничения хранения в AWR и управления адаптивными планами

Для демонстрации/проверки многообразия планов у меня есть простой скрипт 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