Oracle mechanics

07.06.2014

Dynamic Sampling для индексов с отсутствующей статистикой

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

План удаления 80+ млн.строк радовал простотой и доступностью:

11.2.0.3.@ SQL> explain plan for
  2  DELETE FROM SYSTEM.DB_AUDIT_TRAIL_STORE
  3  /

------------------------------------------------------------------------------------------------
| Id  | Operation        | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT |                             |    83M|  2792M|    26   (0)| 00:00:01 |
|   1 |  DELETE          | DB_AUDIT_TRAIL_STORE        |       |       |            |          |
|   2 |   INDEX FULL SCAN| DB_AUDIT_TRAIL_STORE_ACTION |    83M|  2792M|    26   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

Получение более реалистичного плана через сбор/установку реальной статистики индекса очевидно, однако, возникает вопрос: почему в таком случае не используются разного рода динамические технологии, типа Dynamic Sampling/Dynamic Statistics (DS), к примеру, и можно ли заставить их работать?

Итак, после создания тестовой таблицы на версии 11.2.0.3:

drop table lock_table_stats
/
create table lock_table_stats(id, lvalue)
as
select level, rpad('x',4000,level)  -- одна строка на 8к блок
from dual connect by level < 100000
/
exec dbms_stats.gather_table_stats('','LOCK_TABLE_STATS')
exec dbms_stats.lock_table_stats('','LOCK_TABLE_STATS')
create unique index LOCK_TABLE_STATS_ID on LOCK_TABLE_STATS(id)
/

получаем тестовую табличку с фиксированной статистикой и индекс без статистики:

SQL> select stattype_locked, num_rows, blocks, avg_row_len, last_analyzed from user_tab_statistics where table_name = 'LOCK_TABLE_STATS'
  2  /

STATT   NUM_ROWS     BLOCKS AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ----------- ---------
ALL        99999     100498        4006 06-JUN-14

SQL> select stattype_locked, num_rows, distinct_keys, leaf_blocks, blevel, clustering_factor from user_ind_statistics where index_name = 'LOCK_TABLE_STATS_ID'
  2  /

STATT   NUM_ROWS DISTINCT_KEYS LEAF_BLOCKS     BLEVEL CLUSTERING_FACTOR
----- ---------- ------------- ----------- ---------- -----------------
ALL

Как и в случае DML:

SQL> explain plan for delete from LOCK_TABLE_STATS where id > 0;

Explained.

SQL> select * from table(dbms_xplan.display);

-----------------------------------------------------------------------------------------
| Id  | Operation         | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |                     | 99999 |   488K|    26   (0)| 00:00:01 | -- магическое число
|   1 |  DELETE           | LOCK_TABLE_STATS    |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| LOCK_TABLE_STATS_ID | 99999 |   488K|    26   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

, запрос использует медленный индексный план:

SQL> select max(length(lvalue)) from LOCK_TABLE_STATS where id > 0
  2  /

MAX(LENGTH(LVALUE))
-------------------
               4000

Elapsed: 00:00:18.72

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |       |       |   826 (100)|          | -- дважды магический Cost, абсолютно не зависящий от размера таблицы
|   1 |  SORT AGGREGATE              |                     |     1 |  4006 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| LOCK_TABLE_STATS    | 99999 |   382M|   826   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | LOCK_TABLE_STATS_ID | 99999 |       |    26   (0)| 00:00:01 | -- магическое число
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ID">0)

— несмотря на optimizer_dynamic_sampling=2 (по умолчанию), dynamic sampling для индекса не применяется — ибо на таблице статистика присутствует

Трейс отражает hardcode-ные значения статистик «сферического индекса в вакууме»:

Index Stats::
  Index: LOCK_TABLE_STATS_ID  Col#: 1    (NOT ANALYZED)
    LVLS: 1  #LB: 25  #DK: 100  LB/K: 1.00  DB/K: 1.00  CLUF: 800.00 -- default

— из которых и получаются все расчёты стоимости вышеприведённых тестовых запросов:

  • для DELETE стоимость просто равна сумме блоков индекса: 25(LB) + 1(HB) = 26
  • для SELECT к блокам индекса добавляется дефолтное значение clustering factor: 25(LB) + 1(HB) + 800(CLUF) = 826

— т.е. запросы получились замечательные в том смысле, что их стоимость никак не связана с реальными характеристиками таблицы

На практике мониторинг INDEX RANGE SCAN со стоимостью 26 может помочь в поиске индексов, непреднамеренно оставшихся без статистики, например, на продакшене:

11.2.0.3.PROD@SYS SQL> with bad_lines as
  2   (select /*+ MATERIALIZE*/
  3    distinct object_owner, object_name
  4      from gv$sql_plan
  5     where operation = 'INDEX'
  6       and options = 'RANGE SCAN'
  7       and cost = 26)
  8  select owner,
  9         index_name,
 10         stattype_locked,
 11         num_rows,
 12         distinct_keys,
 13         leaf_blocks,
 14         blevel,
 15         clustering_factor
 16    from dba_ind_statistics, bad_lines b
 17   where owner = b.object_owner
 18     and index_name = b.object_name
 19     and stattype_locked is not null
 20  /

no rows selected                        -- всё хорошо

Пробуем стимулировать dynamic sampling подсказкой:

SQL> select/*+ DYNAMIC_SAMPLING(10) */ max(length(lvalue)) from LOCK_TABLE_STATS where id > 0
  2  /

Elapsed: 00:00:18.87

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |       |       |   826 (100)|          |
|   1 |  SORT AGGREGATE              |                     |     1 |  4006 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| LOCK_TABLE_STATS    | 99999 |   382M|   826   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | LOCK_TABLE_STATS_ID | 99999 |       |    26   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

— не работает, в трейсе:

** Performing dynamic sampling initial checks. **
  Column (#1): ID(
    AvgLen: 5 NDV: 99999 Nulls: 0 Density: 0.000010 Min: 1 Max: 99999
** Dynamic sampling initial checks returning FALSE.                   -- наличие статистики на таблице оказалось важнее отсутствия для индекса + хинта,
...
Dumping Hints
=============
  atom_hint=(@=00000000145618A8 err=5 resol=1 used=1 token=832 org=1 lvl=2 txt=DYNAMIC_SAMPLING ()) -- который был использован, но, похоже, с ошибкой

Конкретизируем подсказку:

SQL> select/*+ DYNAMIC_SAMPLING(LOCK_TABLE_STATS 10) */ max(length(lvalue)) from LOCK_TABLE_STATS where id > 0
  2  /

Elapsed: 00:00:22.90

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |       |       |  1022 (100)|          |
|   1 |  SORT AGGREGATE              |                     |     1 |  4006 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| LOCK_TABLE_STATS    | 99999 |   382M|  1022   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | LOCK_TABLE_STATS_ID | 99999 |       |   222   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2) -- применяется, но только 2-го уровня

Трейс показывает:

** Performing dynamic sampling initial checks. **
  Column (#1): ID(
    AvgLen: 5 NDV: 99999 Nulls: 0 Density: 0.000010 Min: 1 Max: 99999
** Dynamic sampling initial checks returning TRUE (level = 10).
** Dynamic sampling updated index stats.: LOCK_TABLE_STATS_ID, blocks=221 -- *

*** 2014-06-03 17:23:13.688
** Generated dynamic sampling query: -- DS запрос выполняется только к таблице, без использования индекса
...
Dumping Hints
=============
  atom_hint=(@=000000001456C508 err=0 resol=1 used=1 token=832 org=1 lvl=3 txt=DYNAMIC_SAMPLING ()) -- хинт использовался без ошибок

*) что для индекса было установлено только кол-во LEAF BLOCKS = 221 (**), точно так же как это делалось до версии 11.2.0.3 при выполнении dynamic sampling для таблицы без статистики — Randolf Geist.Dynamic Sampling And Indexes

CLUSTERING FACTOR для индекса в этом случае не устанавливается несмотря на успешно работающий:

SQL> @fix 12399886

SESSION_   BUGNO VALUE SQL_FEATURE                      DESCRIPTION                                                      OPTIMIZER_FEATURE_ENABLE
---------------- ----- -------------------------------- ---------------------------------------------------------------- ------------------------
        12399886     1 QKSFM_DYNAMIC_SAMPLING_12399886  update the index clustering factor (DS) if statistics do not exi 11.2.0.3

— устанавливающий CLUF в значение [кол-во блоков таблицы]*8 (что также отметил Randolf Geist) только при применении DS для таблицы без статистики:

SQL> exec dbms_stats.delete_table_stats('','LOCK_TABLE_STATS', force=>true, no_invalidate=>false)

PL/SQL procedure successfully completed.

SQL> select/*+ index(LOCK_TABLE_STATS LOCK_TABLE_STATS_ID)*/ max(length(lvalue)) from LOCK_TABLE_STATS where id > 0;

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |       |       |   804K(100)|          | -- Cost = blocks x 8
|   1 |  SORT AGGREGATE              |                     |     1 |  2015 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| LOCK_TABLE_STATS    |   118K|   226M|   804K  (1)| 00:00:42 |
|*  3 |    INDEX RANGE SCAN          | LOCK_TABLE_STATS_ID |   118K|       |   222   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

**) к слову, как видно из статистики выполнения, кол-во LEAF BLOCKS было подсчитано весьма неточно:

-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |      1 |        |       |   826 (100)|       |         1 |00:00:11.47 |     100K|    100K|
|   1 |  SORT AGGREGATE              |                     |      1 |      1 |  4006 |            |       |         1 |00:00:11.47 |     100K|    100K|
|   2 |   TABLE ACCESS BY INDEX ROWID| LOCK_TABLE_STATS    |      1 |  99999 |   382M|   826   (0)| 00:00:01 |  99999 |00:00:09.81 |     100K|    100K|
|*  3 |    INDEX RANGE SCAN          | LOCK_TABLE_STATS_ID |      1 |  99999 |       |    26   (0)| 00:00:01 |  99999 |00:00:00.09 |     209 |    131 | -- 208 LB
-------------------------------------------------------------------------------------------------------------------------------------------------------

— ввиду применения невысокого уровня dynamic sampling (level=2), видимо

Для завершения стоит заметить, что установка на уровне сессии параметра:

SQL> alter session set optimizer_dynamic_sampling=10;

Session altered.

SQL> select max(length(lvalue)) from LOCK_TABLE_STATS where id > 0
  2  /

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |       |       |   826 (100)|          |
|   1 |  SORT AGGREGATE              |                     |     1 |  4006 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| LOCK_TABLE_STATS    | 99999 |   382M|   826   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | LOCK_TABLE_STATS_ID | 99999 |       |    26   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

— не стимулирует dynamic sampling точно так же, как и подсказка без указания конкретной таблицы:

** Dynamic sampling initial checks returning FALSE.

В версии 12.1.0.1 на том же кейсе интерес представляет автоматическое значение параметра OPTIMIZER_DYNAMIC_SAMPLING = 11:

SQL> alter session set optimizer_dynamic_sampling=11;

Session altered.

SQL> select max(length(lvalue)) from LOCK_TABLE_STATS where id > 0
  2  /

------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                     |       |       |   826 (100)|       |
|   1 |  SORT AGGREGATE                      |                     |     1 |  4006 |            |       |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| LOCK_TABLE_STATS    | 99999 |   382M|   826   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | LOCK_TABLE_STATS_ID | 99999 |       |    26   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=AUTO)

— автоматическое использование DS декларировано, но, как видно из плана, не используется

Аналогично выполняется запрос с подсказкой /*+ DYNAMIC_SAMPLING(11) */, за исключением противоречивого замечания:

Note
-----
   - dynamic statistics used: dynamic sampling (level=0)

Подсказка с указанием таблицы /*+ DYNAMIC_SAMPLING(LOCK_TABLE_STATS 11) */ генерирует план, аналогичный /*+ DYNAMIC_SAMPLING(LOCK_TABLE_STATS 10) */ в версии 11.2, но при выполнении DS не выполняется бессмысленный рекурсивный запрос к таблице, что грамотно

Неплохим workaround-ом может стать использование устаревшего синтаксиса при создании индекса:

SQL> create unique index LOCK_TABLE_STATS_ID on LOCK_TABLE_STATS(id) compute statistics
  2  /
create unique index LOCK_TABLE_STATS_ID on LOCK_TABLE_STATS(id) compute statistics
                                           *
ERROR at line 1:
ORA-38029: object statistics are locked

, сигнализирующего о потенциальной проблеме

4 комментария »

  1. Игорь, подскажите, пожалуйста, что нужно сделать в следующем случае:
    ежедневный сбор статистик нормально срабатывает,
    но в течение дня после внесения данных меняются планы запросов,
    я выявляю такие запросы, собираю по таблицам, входящим в них статистики, всё планы запросов нормализуются.
    Посоветуйте, пожалуйста, как избежать этих действий по самостоятельному сбору статистик?

    комментарий от Валерий — 09.06.2014 @ 08:58 | Ответить

    • есть несколько способов оптимизации запросов к таким таблицам с динамическим содержимым, Валерий:
      — зафиксировать статистику таких таблиц в «наполненном» состоянии с помощью DBMS_STATS.LOCK_TABLE_STATS, если при этом будут генерироваться удовлетворительные планы когда таблицы «пустые»
      — либо зафиксировать планы выполнения проблемных запросов, опять же, если эти фиксированные планы будут нормально отрабатывать и на пустых данных
      — либо стимулировать тот же Dynamic Sampling на уровне сессии (параметром) или запросов (подсказкой), вплоть до удаления статистики и фиксации такой пустой статистики для таблиц с частоизменяющимся содержимым, что также стимулирует Dynamic Sampling при каждом разборе (hard parse)

      комментарий от Игорь Усольцев — 09.06.2014 @ 12:09 | Ответить

      • спасибо. 11g r2 версия

        Больше всего волнует то, что в таблице изменилось в процентном отношении совсем немного (в разы меньше stale_percent), но — бах — и длительное время исполнения.

        1. У части слишком уж совсем часто меняющихся таблиц сделана фиксация статистики (кое-где таблицы пустые, кое-где с данными, кое-где с имитирующими реальность данными)
        2. Буду пробовать фиксацию планов.
        3. С Dynamic Sampling на уровне сессии (на уровне БД =2, т.е. не менял) тоже были попытки, по моим наблюдениям, те пользователи, с которыми таким образом пытался решить перестали беспокоить, но до конца не уверен.

        комментарий от Валерий — 09.06.2014 @ 13:20 | Ответить


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