DBMS Cost-Based Optimizers: Back Story and State of the Art

Query optimizers for relational databases have come a long evolutionary way to grow into complicated algorithms for assessing the costs of various options. However, the new generation databases pay almost zero attention to a query cost. What is it, a step back or two steps ahead? Do we even need new cost optimizers for the big data world at all?

В журнале Открытые системы №1, 2016 вышла моя статья Стоимостные оптимизаторы для СУБД: вчера и сегодня

В ней я пытаюсь рассуждать о стоимостной оптимизации в Oracle, Vertica и SparkSQL. Кажется, в области стоимостной оптимизации предстоит решить ещё множество интересных задач.

DBMS Cost-Based Optimizers: Back Story and State of the Art

Extended statistics и invalid objects

После миграции на 12с c удивлением обнаружили, что у нас стали появляться столбцы с расширенной статистикой


select count(*) from dba_tab_col_statistics where column_name like 'SYS_ST%';

COUNT(*)
----------
432

Функционал в принципе известный, проблема только в том, что мы не запускали DBMS_STATS.CREATE_EXTENDED_STATS.

После внимательного исследования архивных журналов выяснилось, что столбцы создаются в моменты сбора статистика по таблице. Чтение кода пакета DBMS_STATS показало, что в новой версии безусловно вызывается процедура создания расширенной статистики по объектам. Эта процедура сканирует таблицу COL_GROUP_USAGE$ и, если там есть новые записи, создаёт по ним расширенную статистику. Для создания расширенной статистики достаточно собрать статистику на уровне таблицы.

Посмотреть объекты, по которым создаётся статистика, можно запросом:


SELECT CU.OBJ# OBJN, CU.COLS,
(CASE WHEN BITAND(CU.FLAGS, 1) = 1
THEN 'FILTER ' ELSE '' END) ||
(CASE WHEN BITAND(CU.FLAGS, 2) = 2
THEN 'JOIN ' ELSE '' END) ||
(CASE WHEN BITAND(CU.FLAGS, 4) = 4
THEN 'GROUP_BY ' ELSE '' END) USAGE,
CU.FLAGS USAGEFLG, timestamp
FROM SYS.COL_GROUP_USAGE$ CU
WHERE
(BITAND(CU.FLAGS, 8) = 0)
order by timestamp desc;

Стало интересно: а кто и как помещает записи в таблицу COL_GROUP_USAGE$ ? Опять помог logminer. Записи создаются рекурсивными транзакциями пользовательских сессий. Например, в моём исследовании для таблицы CSI_I_PARTIES была обновлена запись о столбцах PARTY_SOURCE_TABLE, RELATIONSHIP_TYPE_CODE, CONTACT_FLAG, ACTIVE_END_DATE. Сделано это было на этапе hard parse запроса:


SELECT parties.PARTY_ID PartySourceId ,
parties.RELATIONSHIP_NAME CsietParty_RelaTypeName ,

Plan hash value: 502729463

--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 21750 (100)| |
| 1 | HASH GROUP BY | | 1 | 469 | 21750 (1)| 00:00:01 |
...
|* 20 | TABLE ACCESS FULL | CSI_I_PARTIES | 21 | 735 | 97 (0)| 00:00:01 |
...
|* 78 | INDEX RANGE SCAN | PER_PEOPLE_F_N55 | 2 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
20 - filter(("CIP"."PARTY_SOURCE_TABLE"='PO_VENDORS' AND "CIP"."RELATIONSHIP_TYPE_CODE":SYS_B_07 AND
NVL("CIP"."ACTIVE_END_DATE",SYSDATE@!+:SYS_B_08)>SYSDATE@! AND "CIP"."CONTACT_FLAG"=:SYS_B_06))

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
- 5 Sql Plan Directives used for this statement

Краткое описание процесса также можно найти в документе Are Extended Statistics Collected Automatically on Oracle 12c? (Doc ID 1964223.1)

А также читаем в документе Optimizer with Oracle Database 12c:

SQL plan directives are also used by Oracle to determine if extended statistics, specifically column
groups, are missing and would resolve the cardinality misestimates. After a SQL directive is used the optimizer decides if the cardinality misestimate could be resolved with a column group. If so, it will
automatically create that column group the next time statistics are gathered on the appropriate table.

Директива плана выполнения, которая послужила источником изменений, находится запросом:


SELECT TO_CHAR(d.directive_id) dir_id,
o.subobject_name col_name, o.object_type, d.type, d.state, d.reason, d.created, d.last_used
FROM dba_sql_plan_directives d, dba_sql_plan_dir_objects o
WHERE d.directive_id=o.directive_id
AND o.object_name = 'CSI_I_PARTIES'
ORDER BY reason, created, object_type, col_name, dir_id;

...
4865599916191841433 ACTIVE_END_DATE COLUMN DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE 14.05.15 22.05.15

4865599916191841433 CONTACT_FLAG COLUMN DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE 14.05.15 22.05.15

4865599916191841433 PARTY_SOURCE_TABLE COLUMN DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE 14.05.15 22.05.15

4865599916191841433 RELATIONSHIP_TYPE_CODE COLUMN DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE 14.05.15 22.05.15
...

Здесь видим несколько записей с одинаковым значением directive_id для разных столбцов, а также что эта статистика появилась в результате работы механизма Dynamic Sampling из-за неверной оценки селективности ранее.

Какие объекты расширенной статистике для таблицы уже были созданы, можно посмотреть через представление dba_stat_extensions. В нашем случае запрос показал, что такой статистики ещё нет (об этом же косвенно говорит статус ‘USABLE’ и директивы):

Во всём этом великолепном механизме есть только одна ложка дёгтя: Bug 19450314 : INVALIDATIONS IN 12C

Утром на второй день после миграции я с некоторым удивлением обнаружил в нашей системе порядка 30 000 инвалидных объектов. Пришлось всё останавливать, компилировать их скриптом utlrp, и только потом подавать пользовательскую нагрузку снова.

Баг весьма неприятный, в простых случаях не воспроизводится, однако под нагрузкой проявляет себя так: добавление столбцов для расширенной статистики в таблицу может приводить к тому, что зависимые объекты инвалидируются. Как правило затрагивает это те объекты, с которыми больше всего работают. Далее в системе появляется множествеенные library cache pin/lock, и приходится принимать экстренные меры.

С нетерпением ждём, когда же баг исправят, а пока что живём со значением параметра _optimizer_dsdir_usage_control=0 и не создаём новую расширенную статистику …

P.S. Вышел патч на Bug 19450314 “UNNECESSRAY INVALIDATIONS IN 12C” – у нас уже установлен в production и пока что проблем с инвалидацией не наблюдаем.

Extended statistics и invalid objects

Настройка OeBS с использованием SQL Profile

Некоторое время назад сообщили о возникшей в OeBS проблеме: один из concurrent процессов завершился с ошибкой, в логах

ERROR:ORA-01555: слишком старый снимок: сегмент отката номер 147 по имени “_SYSSMU147_1401594481$” слишком мал

Процесс лечения оказался нетривиален, решил поделиться его деталями

Continue reading “Настройка OeBS с использованием SQL Profile”

Настройка OeBS с использованием SQL Profile

Решение логических задач с помощью рекурсивного SQL

В 7-м номере интернет-журнала ФОРС  увидела свет моя статья Решение логических задач с помощью рекурсивного SQL

 

Краткая аннотация:

В задачах, подобных “головоломке Эйнштейна”, основная сложность заключается в пестроте и сложности формализации условий, которые зачастую попросту сбивают с толку. Как правило, формализовать их удается с помощью логики высказываний. Однако реляционные базы данных и язык SQL, в частности, оперируют отношениями и предикатами, а не высказываниями и формулами. Выходом могло бы стать использование языка Datalog, однако он по-прежнему не находит широкого применения в промышленных системах. Вместе с тем, рекурсивные расширения языка SQL стандарта SQL:92 позволяют использовать при решении основные приемы языка Datalog. Тем самым становится возможным на основе имеющегося массива данных и известных закономерностей сделать логические выводы, получив новые данные, т.е. создать так называемую дедуктиную базу данных. В работе приведен пример решения одной частной задачи с использованием алгоритма вычисления наименьшей неподвижной точки.

 

PDF-версию этой статьи можно скачать по ссылке ниже

one_example

Решение логических задач с помощью рекурсивного SQL

Одна задачка на SQL

В феврале 2013 года мне посчастливилось участвовать в жюри региональных финалов олимпиады Oracle ИТ-Планета

Все региональные этапы проходят заочно, в том числе и для жюри. В моем распоряжении были номера участников и их решения. Необходимо было оценить корректность решений и лично удостовериться, что решения, признанные неверными автоматической проверкой, действительно неверны. Впечатлений масса, но я хотел бы рассказать об одной классической задаче и ее решениях. Вернее, об ошибках, которые можно допустить при решении, казалось бы, избитой задачи.

Итак, одно из заданий было сформулировано так:

Одной командой SELECT вывести список сотрудников, которым установлен оклад больший, чем средний оклад по подразделению компании, к которому они приписаны.

Сведения о сотрудниках, для которых неизвестно к какому подразделению они приписаны, выводить не нужно.

В результат вывести 5 (пять) столбцов:
1. Идентификатор сотрудника
2. Фамилию сотрудника
3. Имя сотрудника
4. Оклад, установленный сотруднику
5. Идентификатор подразделения, к которому приписан сотрудник

Результат отсортировать:
1. По окладу, установленный сотруднику (по убыванию)
2. По фамилии сотрудника (по возрастанию)
3. По имени сотрудника (по возрастанию)
4. По идентификатору сотрудника (по возрастанию)

Решением задачи является запрос:


SELECT employee_id, last_name, first_name, salary, department_id
FROM employees E
WHERE salary > (SELECT AVG(salary) FROM employees X
WHERE E.department_id = x.department_id)
ORDER BY salary DESC, last_name, first_name, employee_id;

Вообще говоря, запрос можно сформулировать множеством способов, например, применив преобразование устранение вложенности подзапросов или используя аналитические функции. Однако интересно, сколько и какие ошибки можно допустить в решении этой набившей оскомину задачи? Я, конечно, предполагал, как можно решить эту задачу неправильно, но такого количества различных неверных решений не ожидал. 🙂

Continue reading “Одна задачка на SQL”

Одна задачка на SQL