Oracle mechanics

26.06.2010

Параметр пакета DBMS_STATS.NO_INVALIDATE

Filed under: CBO,Oracle,statistics — Игорь Усольцев @ 00:17
Tags: ,

В документации Oracle (10-11.2) можно найти на первый взгляд достаточно очевидные вещи:

После обновления статистики объекта бд, Oracle «инвалидирует» любые подготовленные к выполнению (parsed) SQL предложения, которые используют этот объект. При следующем выполнении запроса (statement), план выполнения генерируется заново (statement is re-parsed) и оптимизатор автоматически выбирает новый план выполнения на основании обновлённой статистики…

В действительности описано поведение Oracle только при одном значении параметра NO_INVALIDATE=FALSE процедур DBMS_STATS.GATHER_*_STATS, в то время как по умолчанию этот параметр установлен в значение AUTO_INVALIDATE (Oracle сам решает когда перегенерировать планы выполнения) и если забыть об этом можно не увидеть ожидаемых изменений в плане выполнения запроса после изменений статистики объектов

Простой пример с комментариями:

Тестовая схема

SQL*Plus: Release 11.1.0.7.0 - Production
SQL> alter session set statistics_level = all;
SQL> create table t1
as
select * from all_objects
where rownum < 10001
/
SQL> create index t1_object_type on t1(object_type);
SQL> create index t1_created on t1(created);
SQL> alter table t1 add constraint t1_pk primary key (object_id);
SQL> exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'T1',method_opt => 'for all columns size 1' , cascade => true);
PL/SQL procedure successfully completed.
SQL> select distinct histogram from user_tab_col_statistics where table_name = 'T1';
HISTOGRAM
---------------
NONE

— для таблицы T1 и индексов собрана статистика без гистограмм, тестовый запрос:

SQL> SELECT --Test_Query2
t12.status,
COUNT(t12.object_id) as CNT
FROM t1 t11,
t1 t12
WHERE t11.object_id = t12.object_id
and t11.created >= TO_DATE('29.12.2008 00:00:00', 'DD.MM.YYYY HH24:MI:SS')
AND t12.object_type = 'VIEW'
GROUP BY t12.status  2    3    4    5    6    7    8    9
10  /

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALL IOSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7h2h94qf4m6w3, child number 0
Plan hash value: 4181465596
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |      1 |        |       |    37 (100)|          |      1 |00:00:00.02 |     128 |
|   1 |  HASH GROUP BY                |                |      1 |      1 |    31 |    37   (6)| 00:00:01 |      1 |00:00:00.02 |     128 |
|*  2 |   HASH JOIN                   |                |      1 |     39 |  1209 |    36   (3)| 00:00:01 |      4 |00:00:00.02 |     128 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1             |      1 |     39 |   507 |     3   (0)| 00:00:01 |     93 |00:00:00.01 |      14 |
|*  4 |     INDEX RANGE SCAN          | T1_CREATED     |      1 |     39 |       |     2   (0)| 00:00:01 |     93 |00:00:00.01 |       2 |
|   5 |    TABLE ACCESS BY INDEX ROWID| T1             |      1 |    556 | 10008 |    32   (0)| 00:00:01 |   3280 |00:00:00.01 |     114 |
|*  6 |     INDEX RANGE SCAN          | T1_OBJECT_TYPE |      1 |    556 |       |     2   (0)| 00:00:01 |   3280 |00:00:00.01 |       9 |
------------------------------------------------------------------------------------------------------------------------------------------

По плану выполнения заметно, что, например, при доступе по индексу T1_OBJECT_TYPE ожидаемое количество строк (E-Rows=556) значительно отличается от реального полученного (A-Rows=3280), что говорит о неравномерном распределении строк таблицы по значениям столбца OBJECT_TYPE (skewed column).  В отсутствии информации о гистограммах распределения по значениям столбцов план запроса формируется на основе простых оценок избирательности условий запроса (query predicates), описанных в книге Jonathan Lewis «Cost-Based Optimizer Fundamentals». Например, для условия

t12.object_type = 'VIEW'

при предполагаемом равномерном распределении значений ожидаемое количество строк вычисляется по формуле

SQL> select count(*)/count(distinct OBJECT_TYPE)  CARD_BY_OBJECT_TYPE from t1;
-------------------
555.555556

Соберём более подробную статистику и повторим запрос

SQL> exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'T1',method_opt => 'for all columns size auto', cascade => true);
SQL> select column_name, histogram from user_tab_col_statistics where table_name = 'T1';
COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
...
DATA_OBJECT_ID                 NONE
OBJECT_TYPE                    FREQUENCY
CREATED                        HEIGHT BALANCED
LAST_DDL_TIME                  NONE
...
SQL> SELECT --Test_Query2
...
10  /
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALL IOSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7h2h94qf4m6w3, child number 0
-------------------------------------
Plan hash value: 4181465596
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |      1 |        |       |    37 (100)|          |      1 |00:00:00.01 |     128 |
|   1 |  HASH GROUP BY                |                |      1 |      1 |    31 |    37   (6)| 00:00:01 |      1 |00:00:00.01 |     128 |
|*  2 |   HASH JOIN                   |                |      1 |     39 |  1209 |    36   (3)| 00:00:01 |      4 |00:00:00.01 |     128 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1             |      1 |     39 |   507 |     3   (0)| 00:00:01 |     93 |00:00:00.01 |      14 |
|*  4 |     INDEX RANGE SCAN          | T1_CREATED     |      1 |     39 |       |     2   (0)| 00:00:01 |     93 |00:00:00.01 |       2 |
|   5 |    TABLE ACCESS BY INDEX ROWID| T1             |      1 |    556 | 10008 |    32   (0)| 00:00:01 |   3280 |00:00:00.01 |     114 |
|*  6 |     INDEX RANGE SCAN          | T1_OBJECT_TYPE |      1 |    556 |       |     2   (0)| 00:00:01 |   3280 |00:00:00.01 |       9 |
------------------------------------------------------------------------------------------------------------------------------------------

Статистика обновлена и улучшена, для интересующих нас столбцов появились гистограммы, однако план выполнения не изменился по причине использования значения параметра NO_INVALIDATE по умолчанию

SQL> select dbms_stats.get_param('NO_INVALIDATE') from dual;
DBMS_STATS.GET_PARAM('NO_INVALIDATE')
-------------------------------------
DBMS_STATS.AUTO_INVALIDATE

«Скорость» инвалидации старого курсора зависит от значения параметра _optimizer_invalidation_period, алгоритм rolling invalidation подробно описан в документе Rolling Cursor Invalidations with DBMS_STATS in Oracle10g [ID 557661.1] и предназначен для того, чтобы избежать «hard parse storm» в случае, если множество курсоров зависят от объекта с обновлённой статистикой : Oracle старается равномерно распределить моменты перекомпиляции зависимых курсоров в течение времени, ограниченного параметром _optimizer_invalidation_period; при этом отсчёт начинается после первого (после изменения статистики) использования курсора — чтобы напрасно не тратить ресурсы (на неиспользуемые курсоры)

Для уменьшения времени инвалидации курсора после обновления статистики уменьшим значение параметра _optimizer_invalidation_period (значение по умолчанию 18000 секунд, или 5 часов)

SQL> select a.ksppinm name,
2  b.ksppstvl value,
3  b.ksppstdf deflt,
4  decode
5  (a.ksppity, 1,
6  'boolean', 2,
7  'string', 3,
8  'number', 4,
9  'file', a.ksppity) type,
10  a.ksppdesc description
11  from
12  sys.x$ksppi a,
13  sys.x$ksppcv b
14  where
15  a.indx = b.indx
16  and
17  a.ksppinm like '\_optimizer_invalidation_period' escape '\'
18  /
NAME                               VALUE     DEFLT     TYPE     DESCRIPTION
_optimizer_invalidation_period     18000     TRUE      number   time window for invalidation of cursors of analyzed objects

SQL> alter system set "_optimizer_invalidation_period" = 10;
System altered.

После второго (с момента последнего обновления статистики) выполнения с интервалом >= 10 секунд запрос использует новый курсор (child number 1) с новым планом выполнения

SQL> SELECT --Test_Query2
...
10  /
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALL IOSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7h2h94qf4m6w3, child number 1
-------------------------------------
Plan hash value: 2556455432
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |      1 |        |       |    47 (100)|          |      1 |00:00:00.01 |     151 |
|   1 |  HASH GROUP BY                |            |      1 |      1 |    31 |    47   (5)| 00:00:01 |      1 |00:00:00.01 |     151 |
|*  2 |   HASH JOIN                   |            |      1 |     79 |  2449 |    46   (3)| 00:00:01 |      4 |00:00:00.01 |     151 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1         |      1 |     79 |  1027 |     4   (0)| 00:00:01 |     93 |00:00:00.01 |      14 |
|*  4 |     INDEX RANGE SCAN          | T1_CREATED |      1 |     79 |       |     2   (0)| 00:00:01 |     93 |00:00:00.01 |       2 |
|*  5 |    TABLE ACCESS FULL          | T1         |      1 |   3280 | 59040 |    41   (0)| 00:00:01 |   3280 |00:00:00.01 |     137 |
--------------------------------------------------------------------------------------------------------------------------------------

, точно отражающий избирательность по условию t12.object_type = ‘VIEW’. Ожидаемое и фактическое количество строк (cardinality) совпадают

E-Rows=A-Rows=3280

Из обзора V$SQL_SHARED_CURSOR можно видеть, что в качестве причины создания нового child cursor (или невозможности использования старого курсора) указана ROLL_INVALID_MISMATCH=Y (rolling invalidation and invalidation window exceeded)

SQL> select * from V$SQL_SHARED_CURSOR where sql_id = 'fcgjk8f3njx72';

SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER U S O O S L F E B P I S T A B D L T R I I R L I O E M U T N F A I T D L D B P C S C P T M B M R O P M F L P L A F L R L H
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
7h2h94qf4m6w3 000000009D5CE448 00000000B3151D38            0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
7h2h94qf4m6w3 000000009D5CE448 00000000ABE90DF8            1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N

Из обзора V$SQL можно видеть, что после запрос был дважды выполнен с использованием первого курсора (child_number=0) — после первого и второго обновлений статистики, и один раз — с использованием курсора (child_number=1), т.е. старый курсор был инвалидирован и заново выполнена операция разбора (hard parse)

SQL> select child_number,parse_calls,executions
from v$sql where sql_id='fcgjk8f3njx72';
CHILD_NUMBER PARSE_CALLS EXECUTIONS
------------ ----------- ----------
 0           2          2
 1           1          1

Добавить комментарий »

Комментариев нет.

RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Создайте бесплатный сайт или блог на WordPress.com.

%d такие блоггеры, как: