Oracle mechanics

27.02.2011

Статистика фиксированных таблиц Oracle

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

Несложный по логике запрос с использованием быстрой функции fast_function, которая сама по себе выполняется за миллисекунды и не обращается к системным объектам, выполняется неожиданно медленно:

SQL> select max(name)
2    from v$tablespace
3   where name = fast_function(0)
4      or name = fast_function(1)
5  /

Elapsed: 00:01:04.53

Execution Plan
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |    43 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |         |     1 |    43 |            |          |
|*  2 |   FIXED TABLE FULL| X$KCCTS |     1 |    43 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TSTSN"<>(-1) AND "INST_ID"=USERENV('INSTANCE') AND
("TSNAM"="FAST_FUNCTION"(0) OR "TSNAM"="FAST_FUNCTION"(1)))

И план выполнения (приведён выше ), и трейс 10046 (ниже, под катом) показывают, что причиной длительного выполнения является неправильный порядок соединения: полное сканирование системного обзора v$tablespace (в плане отражена подлежащая фиксированная таблица X$KCCTS) с вызовом функции fast_function для каждой строки:

SELECT ... -- запрос из fast_function, выполняемый 3440 раз!
call     count       cpu    elapsed       disk      query    current        rows
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 Parse        1      0.00       0.00          0          0          0           0
 Execute   3440      0.06       0.06          0          0          0           0
 Fetch     3440     59.22      57.83          0      34400          0        3440
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 total     6881     59.29      57.89          0      34400          0        3440

3440  выполнений (Execute) и извлечений результатов запроса (Fetch) объясняются удвоенным (по кол-ву вызовов fast_function) количеством строк в v$tablespace

SQL> select count(name) from v$tablespace;

COUNT(NAME)
 -----------
 1720

Причину выбора неоптимального плана можно найти в вышеприведённом плане выполнения — при сканировании таблицы X$KCCTS (v$tablespace) оптимизатор предполагает получить одну строку (столбец Rows плана, в то действительности имеем 1720 строк!), что является следствием отсутствия статистики по фиксированным таблицам:

SQL> select rowcnt, blkcnt, analyzetime, samplesize
2  from tab_stats$
3  where obj# in (select OBJECT_ID from V$FIXED_TABLE)
4  /
no rows selected

Автоматическими процедурами (dbms_scheduler ‘GATHER_STATS_JOB’ в 10g и dbms_auto_task_admin ‘AUTO OPTIMIZER STATS COLLECTION’ в 11g)  статистика по фиксированным таблицам (fixed tables | objects) не собирается ни в Oracle 10g, ни в Oracle 11g:

«Статистика по фиксированным объектам … должна собираться вручную с использованием процедуры DBMS_STATS.GATHER_FIXED_OBJECTS_STATS. В фиксированных таблицах отражена текущая активность бд; сбор статистики должен производиться во время репрезентативной активности бд»

Хороший пример про правильное время для сбора такой статистики можно найти в блоге Martin Nash: When to Gather Fixed Object Optimiser Statistics, или представить как будет отличаться содержимое таблиц типа v$session, v$sql, v$open_cursor,.. в зависимости от времени :)

В то же время в документе поддержки Fixed Objects Statistics Considerations [ID 798257.1] указывается, что «Начиная с версии 10.1, оптимизатор использует dynamic sampling в случае отсутствия статистики…»

В каких же случаях используется dynamic sampling для фиксированных таблиц в отсутствии статистики, и почему не был использован в запросе ?

Итак, Oracle 10g, без статистики фикс. таблиц, параметр optimizer_dynamic_sampling установлен по умолчанию (что уже предполагает применение механизма dynamic sampling ко всем неанализированным таблицам — как раз наш случай), более двухсот строк в v$tablespace:

SQL> select version from v$instance;

VERSION
-----------------
10.2.0.4.0

SQL> select rowcnt, blkcnt, analyzetime, samplesize
2  from tab_stats$
3  where obj# in (select OBJECT_ID from V$FIXED_TABLE)
4  /
no rows selected

SQL> show parameter dynamic_sampling

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
optimizer_dynamic_sampling           integer     2

SQL> select count(*) from v$tablespace
  2  /

  COUNT(*)
----------
       201

Демо-запрос не слишком логичный, но с наглядным планом выполнения:

SQL> select max(name)
2    from (select ts.name
3            from v$tablespace ts
4           where ts.name in
5                 (select 'T_' || to_char(mod(dbms_random.random + 1, 2) + 1)
6                    from dual)
7          union all
8          select ts.name
9            from v$tablespace ts
10           where ts.name in
11                 (select 'T_' || to_char(mod(dbms_random.random + 2, 2) + 2)
12                    from dual))
13  /

Execution Plan
--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    17 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE      |         |     1 |    17 |            |          |
|   2 |   VIEW               |         |     2 |    34 |     4   (0)| 00:00:01 |
|   3 |    UNION-ALL         |         |       |       |            |          |
|*  4 |     FILTER           |         |       |       |            |          |
|*  5 |      FIXED TABLE FULL| X$KCCTS |     1 |    43 |     0   (0)| 00:00:01 |
|*  6 |      FILTER          |         |       |       |            |          |
|   7 |       FAST DUAL      |         |     1 |       |     2   (0)| 00:00:01 |
|*  8 |     FILTER           |         |       |       |            |          |
|*  9 |      FIXED TABLE FULL| X$KCCTS |     1 |    43 |     0   (0)| 00:00:01 |
|* 10 |      FILTER          |         |       |       |            |          |
|  11 |       FAST DUAL      |         |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

dynamic sampling не использован, оптимизатор предполагает получить одну строку из таблицы

Подсказка DYNAMIC_SAMPLING ничего не меняет, как и изменение параметра :(

SQL> alter system set optimizer_dynamic_sampling=10;

Версия 11.2, исходные условия — аналогичные:

SQL> select version from v$instance;

VERSION
-----------------
11.2.0.1.0

SQL> select rowcnt, blkcnt, analyzetime, samplesize
  2  from tab_stats$
  3  where obj# in (select OBJECT_ID from V$FIXED_TABLE)
  4  /

no rows selected

SQL> select count(*) from v$tablespace;

  COUNT(*)
----------
       201

SQL> show parameter optimizer_dynamic_sampling

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
optimizer_dynamic_sampling           integer     2

При значении параметра optimizer_dynamic_sampling=2 (по умолчанию) механизм по прежнему не работает, увеличение параметра до максимального значения (10)

SQL> alter system set optimizer_dynamic_sampling=10;

также ничего не даёт, однако механизм начинает работать при использовании подсказки с тем же уровнем 2:

SQL> select max(name)
  2    from (select/*+ DYNAMIC_SAMPLING ( ts1 2 ) */ name
  3            from v$tablespace ts1
  4           where name in (select 'T_' || to_char(mod(dbms_random.random+1, 2)+1) from dual)
  5          union all
  6          select/*+ DYNAMIC_SAMPLING ( ts2 2 ) */ name
  7            from v$tablespace ts2
  8           where name in (select 'T_' || to_char(mod(dbms_random.random+2, 2)+2) from dual))
  9  /

---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |     7 (100)|          |
|   1 |  SORT AGGREGATE      |          |     1 |    17 |            |          |
|   2 |   VIEW               |          |     2 |    34 |     7  (43)| 00:00:01 |
|   3 |    UNION-ALL         |          |       |       |            |          |
|*  4 |     HASH JOIN        |          |     1 |    66 |     4  (50)| 00:00:01 |
|   5 |      VIEW            | VW_NSO_1 |     1 |    23 |     3  (34)| 00:00:01 |
|   6 |       FAST DUAL      |          |     1 |       |     2   (0)| 00:00:01 |
|*  7 |      FIXED TABLE FULL| X$KCCTS  |   201 |  8643 |     0   (0)|          |
|*  8 |     HASH JOIN        |          |     1 |    66 |     4  (50)| 00:00:01 |
|   9 |      VIEW            | VW_NSO_2 |     1 |    23 |     3  (34)| 00:00:01 |
|  10 |       FAST DUAL      |          |     1 |       |     2   (0)| 00:00:01 |
|* 11 |      FIXED TABLE FULL| X$KCCTS  |   201 |  8643 |     0   (0)|          |
---------------------------------------------------------------------------------
...
Note
-----
   - dynamic sampling used for this statement (level=2)

с соответствующими точной оценкой кол-ва строк в фикс.таблице и изменением порядка соединения в плане на правильный, более быстрый

При  увеличении degree_of_sampling в подсказке DYNAMIC_SAMPLING «правильный» план естественно не меняется,

SQL> select max(name)
  2    from (select/*+ DYNAMIC_SAMPLING ( ts1 10 ) */ name
  3            from v$tablespace ts1
  4           where name in (select 'T_' || to_char(mod(dbms_random.random+1, 2)+1) from dual)
  5          union all
  6          select/*+ DYNAMIC_SAMPLING ( ts2 10 ) */ name
  7            from v$tablespace ts2
  8           where name in (select 'T_' || to_char(mod(dbms_random.random+2, 2)+2) from dual))
  9  /

---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |     7 (100)|          |
|   1 |  SORT AGGREGATE      |          |     1 |    17 |            |          |
|   2 |   VIEW               |          |     2 |    34 |     7  (43)| 00:00:01 |
|   3 |    UNION-ALL         |          |       |       |            |          |
|*  4 |     HASH JOIN        |          |     1 |    66 |     4  (50)| 00:00:01 |
|   5 |      VIEW            | VW_NSO_1 |     1 |    23 |     3  (34)| 00:00:01 |
|   6 |       FAST DUAL      |          |     1 |       |     2   (0)| 00:00:01 |
|*  7 |      FIXED TABLE FULL| X$KCCTS  |   201 |  8643 |     0   (0)|          |
|*  8 |     HASH JOIN        |          |     1 |    66 |     4  (50)| 00:00:01 |
|   9 |      VIEW            | VW_NSO_2 |     1 |    23 |     3  (34)| 00:00:01 |
|  10 |       FAST DUAL      |          |     1 |       |     2   (0)| 00:00:01 |
|* 11 |      FIXED TABLE FULL| X$KCCTS  |   201 |  8643 |     0   (0)|          |
---------------------------------------------------------------------------------
...
Note
-----
   - dynamic sampling used for this statement (level=2)

но не меняется и degree_of_sampling / степень dynamic sampling (level=2)

Получается, что механизм dynamic sampling для фиксированных таблиц без статистки работает только в Oracle 11g и только при форсировании с помощью подсказки

Про индексы на фиксированных таблицах — Dion Cho: Tuning query on the fixed table

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