— живой и интересный!
Несколько иллюстраций к рассказанному Александром: (more…)
Разочаровал способ реализации в 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> -- - но в этом случае дедубликация действительно работает!
Вопрос: как максимально корректно изменить план выполнения уже хинтованного запроса? Корректно, в смысле не меняя текста запроса (само собой) и, желательно, не меняя параметров сессии, т.е. используя, например, безконтактный 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: (more…)
Спустя 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 дисками
На продакшн системе версии 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: (more…)
Копия в интернет-журнале «Форс»
Периодически появляется необходимость сравнить / найти различия в планах выполнения запроса, для последующих глубокомысленных умозаключений и выводов
Пакет DBMS_XPLAN, как я понимаю, вплоть до последних версий такую возможность не реализовал (несмотря на сделанную в 11.2 недокументированную заявку в виде DBMS_XPLAN.DIFF_PLAN_AWR — см.легковоспроизводимый на 12.1.0.1 пример на morganslibrary.org)
А поскольку планы (и запросы) встречаются весьма объёмные и сравнивать их на маленьком экране ноутбука не всегда удобно, написал пару скриптов:
Далее — пример использования (more…)
Практический запрос запрос (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 по какой-то причине не склонны использовать/стимулировать параллельное выполнение: (more…)
По словам очевидцев, почти безобидный запрос периодически выполняется дольше таймаута приложения (магическим образом установленным ровно в 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): (more…)
Начиная с 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 при полном сканировании таблиц (more…)