Доклад Александра Токарева о Result Cache на семинаре RuOUG

— живой и интересный!

Несколько иллюстраций к рассказанному Александром:

Deduplicate LOB

Разочаровал способ реализации в Oracle killer-фичи версии 11g — Deduplicate LOB через обычный странный UNIQUE индекс, который значительно ограничивает возможности транзакционного использования, в частности, конкурентного заполнения таблицы дублирующими LOB-ами

SQL> CREATE TABLE deduplicate_clob (clob_data  CLOB)
  2  LOB(clob_data) STORE AS SECUREFILE dedup_lob( DEDUPLICATE ENABLE STORAGE IN ROW )
  3  tablespace USERS
  4  /
 
Table created

SQL> -- индекс вместе с LOB-сегментом создаются сразу по созданию таблицы:
SQL> select * from dba_indexes where table_name = 'DEDUPLICATE_CLOB';
 
OWNER  INDEX_NAME                INDEX_TYPE  TABLE_NAME        UNIQUENESS COMPRESSION   LOGGING STATUS  GENERATED VISIBILITY SEGMENT_CREATED INDEXING
------ ------------------------- ----------- ----------------- ---------- ------------- ------- ------- --------- ---------- --------------- --------
SCOTT  SYS_IL0000105054C00001$$  LOB         DEDUPLICATE_CLOB  UNIQUE     DISABLED      YES     VALID   Y         VISIBLE    YES             FULL

SQL> -- , однако, для случая ENABLE STORAGE IN ROW конкурентная вставка коротких LOB-дублей допускается:
SQL> insert into deduplicate_clob (clob_data) values ('test CLOB data'); -- в 1-й сессии без COMMIT-а
 
1 row inserted

SQL> -- и во 2-й сессии:
SQL> insert into deduplicate_clob (clob_data) values ('test CLOB data');
 
1 row inserted

SQL> -- при этом обе сессии вполне успешно получают две уникальные блокировки (неожиданно на разные ресурсы):
SQL> select * from v$lock where type = 'TX';
 
  SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK     CON_ID
----- ---- ---------- ---------- ---------- ---------- ---------- ---------- ----------
  355 TX       327699       6328          6          0         46          0          0
  371 TX        65547       5571          6          0         52          0          0

SQL> -- - т.е. уникальность индекса не задействована, и, как следствие, в вышеописанном случае STORAGE IN ROW дедубликация в смысле экономии места практически не функционирует (как и продекларированная уникальность LOB-индекса)
SQL>
SQL> -- Если же при создании определить свойства LOB как DEDUPLICATE DISABLE STORAGE IN ROW, или же, не пересоздавая таблицу, попытаться одновременно в 2-х сессиях вставить в LOB длинные значения:
SQL> insert into deduplicate_clob (clob_data) values (rpad('test CLOB data',5000));
 
1 row inserted

SQL> -- получаем рудименты классического уникального индекса (проявляющиеся только при заполнении LOB-сегмента):
SQL> select * from v$lock where type = 'TX';
 
  SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK     CON_ID
----- ---- ---------- ---------- ---------- ---------- ---------- ---------- ----------
  371 TX       458758       6029          6          0         16          1          0
  355 TX       458758       6029          0          4          5          0          0

SQL> -- с предсказуемым ожиданием на этом самом индексе:
SQL> @lock_tree
 
BLOCKING_TREE     USERNAME  EVENT                          REQ_OBJECT                           SECS_IN_WAIT BLOCK_SESSTAT SQL_TEXT                                                                       P1TEXT
----------------- --------- ------------------------------ ------------------------------------ ------------ ------------- ------------------------------------------------------------------------------ -------------------
INST#1 SID#371    SCOTT     SQL*Net message from client    LOB SCOTT.DEDUP_LOB                           604 NO HOLDER                                                                                    driver id driver id
  INST#1 SID#355  SCOTT     enq: TX - row lock contention  INDEX SCOTT.SYS_IL0000105054C00001$$          593 VALID         insert into deduplicate_clob (clob_data) values (rpad('test CLOB data',5000))  name|mode TX 4

SQL> -- - но в этом случае дедубликация действительно работает!

SPM подходы к изменению плана запроса с hardcoded подсказками

Вопрос: как максимально корректно изменить план выполнения уже хинтованного запроса? Корректно, в смысле не меняя текста запроса (само собой) и, желательно, не меняя параметров сессии, т.е. используя, например, безконтактный SQL Plan Management (SPM)?

Простейшая тестовая схема:

11.2.0.3@SCOTT SQL> create table emp123
  2  as
  3  select * from emp
  4  /

Table created.

SQL> exec dbms_stats.gather_table_stats('','EMP123')

PL/SQL procedure successfully completed.

SQL> create index emp123_sal_idx on emp123(sal)
  2  /

Index created.

Простой запрос с использованием FTS:

Официальный документ про Smart Flash Cache

Спустя 5 лет после выхода версии 11.2 появился практически полезный документ от Oracle Optimizing Oracle Database Performance on Oracle Linux with Flash, где кроме традиционного маркетингового слоя, можно найти толковое техническое руководство по конфигурации Smart Flash Cache на Linux, включая установку специфичных для Flash/SSD параметров, пошаговые процедуры создания ASM и конфигурации Smart Flash Cache в бд, плюс подробные рез-ты сравнительных тестов

При планировании использования Smart Flash Cache стоит иметь в виду, что фича эта может эффективно использоваться не только с брендовыми Oracle Sun Flash Accelerator PCIe Card, и даже не только со всё ещё достаточно дорогими Flash PCIe Card других производителей, но и с вполне доступными SSD дисками

JPPD в присутствии удалённой таблицы и View Merging

На продакшн системе версии 11.2.* наблюдал, как сам факт использования удалённой (remote) таблицы блокировал использование Join Predicate Push-Down (JPPD) в запросе следующего типа:

select t.char_column, analytic_view.max_id, analytic_view.max_char_column
  from t,
       t@SCOTT_LOOPBACK
         t2,
       (select id1,
               max(id2) keep(dense_rank first order by id1 desc) max_id,
               max(char_column) max_char_column
          from t
         group by id1) analytic_view
 where t.id1 = t2.id1
   and t.id2 = analytic_view.id1
   and t.id1 = 10

— несмотря на то, что удалённая таблица, казалось бы, никакими условиями запроса с inline view связана не была. Т.е. JPPD работает для локальной таблицы T2 и не работает для удалённой.
Использование аналитического запроса в inline view препятствием для применения JPPD не является

Тестовая схема для версий 11.2.0.3/12.1.0.1:

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

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

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

Пакет 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)

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

Cardinality Feedback: многочисленные версии курсоров с повторяющимся планом выполнения

Практический запрос запрос (11.2.0.3 Linunx x86_64), по причине «удачного» совпадения условий стабильно генерирует дополнительные курсоры с повторяющимся планом выполнения под влиянием Cardinality Feedback (CF):

11.2.0.3.@ SQL> alter session set statistics_level=all;

Session altered.

SQL> SELECT sum(view_sp_credit.spent) AS spent,
  2         sum(view_sp_credit.spent_currency) AS spent_currency,
  3         view_sp_credit.product_id
  4    FROM view_sp_credit
  5   WHERE view_sp_credit.contract_id = 48746
  6   GROUP BY view_sp_credit.product_id
  7  /

2 rows selected.

Elapsed: 00:00:06.17

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('','',format => 'all allstats advanced last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7a0stv191ztsb, child number 0 --при первом выполнении создаётся 0-ая версия курсора со значительными различиями в плане и статистике выполнения
-------------------------------------

Параллельное выполнение непараллельных запросов

(или какую практическую пользу можно извлечь, стимулируя параллельное выполнение комплексных запросов, использующих непараллельные функции без ключевого слова PARALLEL_ENABLE ?)

Проблема:

Fri Jun 14 14:32:11 2013
ORA-01555 caused by SQL statement below (SQL ID: 619hu75mw2770, Query Duration=152691 sec, SCN: 0x07a9.da2c32b7):
with AP_STANDARD as ...

при выполнении традиционно крупногабаритного (~ 500 строк и в тексте, и в плане) запроса в бд OEBS

Характерными причинами неспособности Oracle сгенерировать быстрый план кроме избыточной нормализации схем OEBS и, как следствие, громозкости получающихся запросов, является распостранённое использование «непараллельных», в смысле активно читающих переменные пакета, функций типа FND_PROFILE.VALUE как в явном виде — в условиях запроса, так и в составе параметризованных обзоров

Судя по тексту функций основных схем OEBS, разработчики Oracle Applications по какой-то причине не склонны использовать/стимулировать параллельное выполнение:

SQL Plan Management в практических приложениях-2

По словам очевидцев, почти безобидный запрос периодически выполняется дольше таймаута приложения (магическим образом установленным ровно в 55 секунд):

11.2.0.3.@ SQL> var st char(9)
SQL> var en char(9)
SQL> exec :st := '23-MAY-13'; :en := '23-MAY-13'

PL/SQL procedure successfully completed.

SQL> select place_id, page_id, dt,
            sum(hits) hits,
            sum(partner_bonus_wo_nds) sum1,
            sum(partner_bonus) sum2,
            max(c_id) c_id
            from v_distr
            where dt >=:st and dt <= :en  -- dt типа DATE
            group by dt, place_id, page_id
/

...
140 rows selected.

Elapsed: 00:00:55.14

Статистика DBA_HIST_SQLSTAT подтверждает присутствие нестабильности и в плане, и, что существенно, в среднем времени выполнения ELA_PER_EXEC (us):

Параметр _very_large_object_threshold

English version

Начиная с Oracle 11.2 на выбор способа чтения блоков индекса между serial direct path read и буферизованным чтением (через db buffer cache SGA) при операции INDEX FAST FULL SCAN (IFFS) влияет параметр:

SQL> @param_ _very_large_object_threshold

NAME                         VALUE IS_DEF   DSC
---------------------------- ----- -------- -----------------------------------------------------
_very_large_object_threshold 500   TRUE     upper threshold level of object size for direct reads

Правильное значение этого параметра: процент от размера буферного кэша (точнее, параметра _db_block_buffers) при превышении которого индекс считается «большим» и в процессе IFFS будет использоваться direct path read, если же размер индекса меньше — IFFS будет выполняться стандартными чтениями блоков через буферный кэш SGA — в точности как показал Саян Малакшинов в своём блоге Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats), где кроме этого параметра описывает хинт INDEX_STATS(“OWNER”.”TABLE_NAME”, “INDEX_NAME”, scale, blocks=X), частовстречаемый / применяемый в SQL Profiles

В процессе тестирования на разных платформах, о котором мы договорились с Саяном, замечено также влияние значения параметра _very_large_object_threshold на управление direct path read с использованием event 10949 при полном сканировании таблиц