После миграции на 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 и пока что проблем с инвалидацией не наблюдаем.