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

Advertisements
Extended statistics и invalid objects

5 thoughts on “Extended statistics и invalid objects

  1. […] Вот с полгода назад столкнулись, обследовали / нашли workaround, ты создание патча 19450314 у бусурман доброжелательных сотрудников MOS истребовал и всю проблему изучил/расписал в подробностях — Extended statistics и invalid objects […]

  2. Привет Леня! Посмоти еще вот этот документ Doc ID 21171382.8 Тех поддержка нас заставила, ставить его, т.к. 19450314 нам не помог, инвалидация продолжилась, только значительно реже.

  3. Bruno says:

    Very good article

    A question: What is the meaning of “BITAND (CU.FLAGS, 8)”?

    Please excuse my English (Google)

    Many thanks and happy feast days Bruno

    1. CU.FLAGS is just used to save the status bits.

      so BITAND (CU.FLAGS, 8) checks whether the fourth bit set or not, see for example http://www.orafaq.com/wiki/Bit

      As to the meaning of the fourth bit in CU.FLAGS field, I cannot say with full confidence but it seems to show whether group of columns are been processed or not. And BITAND (CU.FLAGS, 8) = 0 shows the list of groups that will be processed next dbms_stats launch.

      There are constants in dbms_stats_internal package described possible values:

      DSC_CG_SNGLTAB CONSTANT NUMBER := 1;
      DSC_CG_JOIN CONSTANT NUMBER := 2;
      DSC_CG_GBY CONSTANT NUMBER := 4;
      DSC_CG_CREATED CONSTANT NUMBER := 8;
      DSC_CG_SRC_DIR CONSTANT NUMBER := 16;
      DSC_CG_SRC_SEED CONSTANT NUMBER := 32;

      But it’s internal values and one could only guess about the real usage.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s