Oracle mechanics

30.06.2010

dynamic sampling при индексном доступе к данным

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

При использовании dynamic sampling Oracle оценивает количество блоков в таблицах и применяемых индексах, количество строк таблицы (table cardinalities) и статистику столбцов… На основе этих уточнённых данных оптимизатор рассчитывает стоимость и строит [уточнённый, улучшенный] план запроса.

Однако, что при использовании в плане выполнения индексного доступа к данным уточнённые данные по избирательности условий запроса (predicate selectivity) учитываются оптимизатором при расчёте количества ожидаемых строк таблицы, но не используются при расчёте количества и стоимости операций с индексом

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

optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.2.0.4
optimizer_mode                       string      ALL_ROWS
cursor_sharing                       string      EXACT
SQL> alter session set statistics_level=all;
SQL> drop table t1
2  /
SQL> create table t1
as
select * from all_objects
where rownum < 10001
2    3    4    5
SQL> /
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);

Строки тестовой таблицы распределены по значениям двух столбцов очень неравномерно — типичный случай skewed columns — часто встречается в практических задачах, иначе применение dynamic sampling было бы просто не нужно :)

SQL> select object_type, count(*) from t1
group by object_type
order by 2 desc
2    3    4  /
OBJECT_TYPE           COUNT(*)
------------------- ----------
VIEW                      2956
SYNONYM                   2563
TYPE                       979
INDEX                      961
TABLE                      901
PACKAGE                    488
PACKAGE BODY               473
LIBRARY                    113
...

SQL> select trunc(created), count(*) from t1 group by trunc(created) order by 1
2  /
TRUNC(CRE   COUNT(*)
--------- ----------
12-MAR-08       9947
21-JUL-08         53

Собираем статистику без гистограмм

SQL> exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'T1',method_opt => 'for all columns size 1', cascade => true);
2  /
PL/SQL procedure successfully completed.
SQL> select distinct histogram from user_tab_col_statistics where table_name = 'T1'
2  /
HISTOGRAM
---------------
NONE

Тестовый запрос и план выполнения:

SQL> SELECT --TEST_QUERY
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('21.07.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
 11  /

STATUS         CNT
------- ----------
VALID            3

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALL IOSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4zkdg0bva3gwx, child number 0
-------------------------------------
Plan hash value: 4181465596
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY                |                |      1 |      1 |    31 |    27   (8)| 00:00:01 |      1 |00:00:00.03 |     111 |
|*  2 |   HASH JOIN                   |                |      1 |     67 |  2077 |    26   (4)| 00:00:01 |      3 |00:00:00.03 |     111 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1             |      1 |     67 |   871 |     3   (0)| 00:00:01 |     53 |00:00:00.01 |       3 |
|*  4 |     INDEX RANGE SCAN          | T1_CREATED     |      1 |     67 |       |     2   (0)| 00:00:01 |     53 |00:00:00.01 |       2 |
|   5 |    TABLE ACCESS BY INDEX ROWID| T1             |      1 |    333 |  5994 |    22   (0)| 00:00:01 |   2956 |00:00:00.02 |     108 |
|*  6 |     INDEX RANGE SCAN          | T1_OBJECT_TYPE |      1 |    333 |       |     1   (0)| 00:00:01 |   2956 |00:00:00.01 |       8 |
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

 2 - access("T11"."OBJECT_ID"="T12"."OBJECT_ID")
 4 - access("T11"."CREATED">=TO_DATE(' 2008-07-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
 6 - access("T12"."OBJECT_TYPE"='VIEW')

С планом всё более-менее понятно, ожидаемое количество строк (E-Rows для доступа по индексам T1_OBJECT_TYPE (333) и T1_CREATED (67)) вычисляются оптимизатором исходя из предположения о равномерном распределении строк по значениям столбцов:

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

SQL> with q as
2  (select min(created) min_created
3  , max(created) max_created
4  , count(distinct created) count_distinct
5  , count(*) count_rows
6    from t1)
7  select
8  ((max_created - TO_DATE('21.07.2008 00:00:00', 'DD.MM.YYYY HH24:MI:SS'))/(max_created - min_created)
9   + 1/count_distinct)*count_rows
10  from q
11  /
((MAX_CREATED-TO_DATE('21.07.200800:00:00','DD.MM.YYYYHH24:MI:SS'))/(MAX_CREATED-MIN_CREATED)+1/COUNT_DISTINCT)*COUNT_ROWS
--------------------------------------------------------------------------------------------------------------------------
66.7197042

, а поскольку распределение подготовлено специально неравномерное (skewed columns), ожидаемое и фактическое кол-во строк не совпадают — на этом этапе всё нормально, оптимизатор исходит из имеющихся данных

Статистика выполнения запроса (statistics_level=all) показывает, что для доступа по индексу T1_CREATED ждали 67 (E-Rows=67), получили 53 (A-Rows=53), что не так страшно ;) а вот по индексу T1_OBJECT_TYPE ждали 333 строки (E-Rows=333), получили 2956 (A-Rows=2956) — на основании имеющейся при подготовке плана информации оптимизатор ожидал почти на порядок меньшее число строк, что без сомнения повлияло на выбор плана и оценку стоимости выполнения запроса

Пробуем улучшить ситуацию, используя в форсированном подсказкой режиме механизм on-line оценки статистики (dynamic sampling)

SQL> SELECT --+ dynamic_sampling(t11 4) dynamic_sampling(t12 4)
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('21.07.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
 11  /

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALL IOSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  534jgyjhm8f1d, child number 0
-------------------------------------
Plan hash value: 4181465596
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY                |                |      1 |      1 |    31 |    27   (8)| 00:00:01 |      1 |00:00:00.03 |     111 |
|*  2 |   HASH JOIN                   |                |      1 |     53 |  1643 |    26   (4)| 00:00:01 |      3 |00:00:00.03 |     111 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1             |      1 |     53 |   689 |     3   (0)| 00:00:01 |     53 |00:00:00.01 |       3 |
|*  4 |     INDEX RANGE SCAN          | T1_CREATED     |      1 |     67 |       |     2   (0)| 00:00:01 |     53 |00:00:00.01 |       2 |
|   5 |    TABLE ACCESS BY INDEX ROWID| T1             |      1 |   2956 | 53208 |    22   (0)| 00:00:01 |   2956 |00:00:00.02 |     108 |
|*  6 |     INDEX RANGE SCAN          | T1_OBJECT_TYPE |      1 |    333 |       |     1   (0)| 00:00:01 |   2956 |00:00:00.01 |       8 |
------------------------------------------------------------------------------------------------------------------------------------------
...
Note
-----
 - dynamic sampling used for this statement

Что интересно:

  • механизм dynamic sampling успешно сработал, что заметно и по уточнению значений E-Rows плана выполнения, и по замечанию (Note), и по 10053 трейсу (см.ниже)
  • ожидаемое кол-во строк для доступа к таблицам совпадает с фактически полученным в запросе (строки 3 и 5 плана) — очевидно, что такая высокая точность достижима только для небольших тестовых объектов. В этом тесте таблица занимает 137 блоков бд при максимальном количестве оценочных блоков для 4-го уровня dynamic sampling — 256, т.е. таблица была прочитана (проанализирована) полностью  (см. трейс 10053 ниже)
  • не изменилось ожидаемое количество строк для операций INDEX RANGE SCAN (строки 4 и 6 плана), что похоже на ошибку оптимизатора*
  • план выполнения не изменился (PLAN_HASH_VALUE одинаков для обоих запросов), не изменилась и стоимость выполнения запроса — что неожиданно, т.к. при изменении количества обрабатываемых строк по крайней мере стоимость выполнения запроса обязана была измениться. Вероятно, неизменившаяся стоимость является следствием предыдущей ошибки

Из трейса 10053 последнего выполнения запроса (dynamic_sampling, статистика без гистограмм)

SINGLE TABLE ACCESS PATH
 -----------------------------------------
 BEGIN Single Table Cardinality Estimation
 -----------------------------------------
** Performing dynamic sampling initial checks. **
 Column (#6): OBJECT_TYPE(VARCHAR2)
 AvgLen: 7.00 NDV: 30 Nulls: 0 Density: 0.033333
** Dynamic sampling initial checks returning TRUE (level = 4).
** Executed dynamic sampling query:
 level : 4
 sample pct. : 100.000000
 actual sample size : 10000
 filtered sample card. : 2956
 orig. card. : 10000
 block cnt. table stat. : 137
 block cnt. for sampling: 137
 max. sample block cnt. : 256
 sample block cnt. : 137
 min. sel. est. : 0.03333333
** Using single table dynamic sel. est. : 0.29560000
 Table: T1  Alias: T12
 Card: Original: 10000  Rounded: 2956  Computed: 2956.00  Non Adjusted: 2956.00
 -----------------------------------------
 END   Single Table Cardinality Estimation
 -----------------------------------------
 Access Path: TableScan
 Cost:  32.15  Resp: 32.15  Degree: 0
 Cost_io: 32.00  Cost_cpu: 4212117
 Resp_io: 32.00  Resp_cpu: 4212117
 Access Path: index (AllEqRange)
 Index: T1_OBJECT_TYPE
 resc_io: 22.00  resc_cpu: 334542
 ix_sel: 0.033333  ix_sel_with_filters: 0.033333
 Cost: 22.01  Resp: 22.01  Degree: 1
 Best:: AccessPath: IndexRange  Index: T1_OBJECT_TYPE
 Cost: 22.01  Degree: 1  Resp: 22.01  Card: 2956.00  Bytes: 0

можно увидеть, что несмотря на то, что ожидаемое количество строк таблицы, удовлетворяющих условия запроса после динамической оценки (Card:… 2956.00) подсчитано точно, для дальнейшей оценки доступа по индексу (секция Access Path: index (AllEqRange) T1_OBJECT_TYPE) используются неизменённые, вычисленные на основании имеющейся статистики (без учета результатов dynamic sampling) данные по избирательности (ix_sel_with_filters: 0.033333) и стоимости (Cost: 22.01) индексного доступа

Для сравнения можно посмотреть план выполнения при автоматически полностью собранной статистике (с гистограммами, estimate_percent = 100%)

SQL> exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'T1',method_opt => 'for all columns size auto', cascade => true, estimate_percent => null);
SQL> SELECT --TEST_QUERY
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('21.07.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
11  /
...
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALL IOSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4zkdg0bva3gwx, child number 1
-------------------------------------
Plan hash value: 2556455432
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY                |            |      1 |      1 |    31 |    37   (6)| 00:00:01 |      1 |00:00:00.01 |     132 |
|*  2 |   HASH JOIN                   |            |      1 |     39 |  1209 |    36   (3)| 00:00:01 |      3 |00:00:00.01 |     132 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1         |      1 |     39 |   507 |     3   (0)| 00:00:01 |     53 |00:00:00.01 |       3 |
|*  4 |     INDEX RANGE SCAN          | T1_CREATED |      1 |     39 |       |     2   (0)| 00:00:01 |     53 |00:00:00.01 |       2 |
|*  5 |    TABLE ACCESS FULL          | T1         |      1 |   2956 | 53208 |    32   (0)| 00:00:01 |   2956 |00:00:00.01 |     129 |
--------------------------------------------------------------------------------------------------------------------------------------

Как следствие более точных данных о распределении строк по значениям столбцов OBJECT_TYPE и CREATED по оценке оптимизатора количество строк, полученных при сканировании по индексу (шаг 4, INDEX RANGE SCAN) и при последующем извленении из таблицы (шаг 3, TABLE ACCESS BY INDEX ROWID) совпадают, чего не было при использовании dynamic_sampling.

Что более важно, изменилась оценочная стоимость и план выполнения, причины изменений можно увидеть из 10053 трейса

SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#6): OBJECT_TYPE(VARCHAR2)
AvgLen: 7.00 NDV: 30 Nulls: 0 Density: 5.0000e-05
Histogram: Freq  #Bkts: 30  UncompBkts: 10000  EndPtVals: 30
Table: T1  Alias: T12
Card: Original: 10000  Rounded: 2956  Computed: 2956.00  Non Adjusted: 2956.00
-----------------------------------------
END   Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost:  32.15  Resp: 32.15  Degree: 0
Cost_io: 32.00  Cost_cpu: 4212117
Resp_io: 32.00  Resp_cpu: 4212117
Access Path: index (AllEqRange)
Index: T1_OBJECT_TYPE
resc_io: 194.00  resc_cpu: 2949089
ix_sel: 0.2956  ix_sel_with_filters: 0.2956
Cost: 194.10  Resp: 194.10  Degree: 1
Best:: AccessPath: TableScan
Cost: 32.15  Degree: 1  Resp: 32.15  Card: 2956.00  Bytes: 0

На основе уточнённого количества строк (Card:… 2956.00), удовлетворяющих условию «T12».»OBJECT_TYPE»=’VIEW’, оптимизатор значительно уточнил избирательность индекса (ix_sel_with_filters: 0.2956 против ix_sel_with_filters: 0.033333 при использовании dynamic sampling в первом трейсе) и, как следствие, увеличил стоимость индексного доступа почти в 9 раз! (Cost: 194.10 против Cost: 22.01 в секции Access Path: index (AllEqRange) Index: T1_OBJECT_TYPE). В результате этих расчётов выбран наиболее дешёвый (Cost:  32.15) метод доступа — FULL TABLE SCAN (Best:: AccessPath: TableScan)

Получается, что, в отличие от данных статистики Oracle, данные об объектах бд, полученные в результате применения механизма dynamic sampling не полностью используются оптимизатором для корректного расчёта стоимости и построения оптимального плана для запросов, в которых может быть использован индексный доступ

Проверено на версиях Oracle 10.2.0.4 и 11.1.0.7

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

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

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 такие блоггеры, как: