Oracle mechanics

21.03.2012

Про фиксацию статистики таблиц с частоменяющимся содержимым

Filed under: commonplace,Oracle,statistics — Игорь Усольцев @ 01:02
Tags: ,

Стандартная проблема: «хороший» MERGE, заливающий данные из вспомогательной таблицы (периодически наполняется и truncate-ится) неожиданно начинает выполняться крайне медленно, при этом команда EXPLAIN PLAN показывает хороший, быстрый план выполнения:

11.1.0.7.@SQL> explain plan for merge...

Explained.

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

----------------------------------------------------------------------------------
| Id  | Operation                              | Name       | Rows  | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                        |            |     1 |    35   (3)|
|   1 |  MERGE                                 | SHOWS      |       |            |
|   2 |   VIEW                                 |            |       |            |
|   3 |    NESTED LOOPS OUTER                  |            |     1 |    35   (3)|
|   4 |     VIEW                               |            |     1 |     3  (34)|
|   5 |      SORT GROUP BY                     |            |     1 |     3  (34)|
|   6 |       TABLE ACCESS FULL                | TEMP_SHOWS |     1 |     2   (0)| -- вспомогательная таблица
|   7 |     PARTITION RANGE ITERATOR           |            |     1 |    32   (0)|
|   8 |      VIEW                              |            |     1 |    32   (0)|
|*  9 |       TABLE ACCESS BY LOCAL INDEX ROWID| SHOWS      |     1 |    32   (0)|
|* 10 |        INDEX RANGE SCAN                | IDX_SHOWS  |     1 |    32   (0)| -- правильный индексный доступ к основной таблице
----------------------------------------------------------------------------------

С планом — понятно, EXPLAIN PLAN не в первый раз показывает неактуальный план (при этом никакие связанные переменные на процесс генерации не влияют — их просто нет), правильный запрос к V$SQL_PLAN или dbms_xplan.display_cursor показывают как в действительности выполняется запрос:

11.1.0.7.BILLING@SYS SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor( '376buhksd88tj',0,'basic allstats +cost -predicate -note'));

-----------------------------------------------------------------------------------
| Id  | Operation                              | Name       | E-Rows | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                        |            |        |    11 (100)|
|   1 |  MERGE                                 | SHOWS      |        |            |
|   2 |   VIEW                                 |            |        |            |
|   3 |    NESTED LOOPS OUTER                  |            |      1 |    11  (10)|
|   4 |     VIEW                               |            |      1 |     3  (34)|
|   5 |      SORT GROUP BY                     |            |      1 |     3  (34)|
|   6 |       TABLE ACCESS FULL                | TEMP_SHOWS |      1 |     2   (0)|
|   7 |     PARTITION RANGE ITERATOR           |            |      1 |     8   (0)|
|   8 |      VIEW                              |            |      1 |     8   (0)|
|   9 |       TABLE ACCESS FULL                | SHOWS      |      1 |     8   (0)| -- проблема
-----------------------------------------------------------------------------------

— понятно, что NESTED LOOPS с TABLE ACCESS FULL доступом к внутренней таблице соединения (SHOWS) — не самое быстрое развлечение)

Также из обоих планов хорошо видно, что в основе проблемы лежит ошибка в оценке количества строк вспомогательной таблицы TEMP_SHOWS E-Rows=1, источником которой является статистика таблицы:

11.1.0.7.@SQL> select num_rows, blocks, last_analyzed from dba_tables where table_name like 'TEMP_SHOWS';

 NUM_ROWS BLOCKS LAST_ANALYZED
 -------- ------ ---------------
 0             0 19-MAR-12 22:00

— для периодически очищаемой таблицы это типичная ситуация, просто статистика собралась в неудачный момент — сразу после truncate)

Как вариант фиксации хорошего плана/статистики можно:

  • собирать и блокировать правильную с точки зрения планов выполнения статистику, например, перед truncate
  • установить и навсегда заблокировать реальные расчётные (или заведомо большие, приводящие к выбору правильного плана) значения в статистике:
SQL> begin
 2   dbms_stats.set_table_stats('&&table_owner','TEMP_SHOWS', numrows => 100000000, numblks => 2000000, avgrlen => 24);
 3   dbms_stats.lock_table_stats('&&table_owner','TEMP_SHOWS');
 4  end;
 5  /

PL/SQL procedure successfully completed

, и получить удовлетворительный (быстрый) план с фантастическими фиксированными оценками Cardinality (Rows) и стоимости:

 -----------------------------------------------------------------------------------------------------
 | Id  | Operation                              | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
 -----------------------------------------------------------------------------------------------------
 |   0 | MERGE STATEMENT                        |            |   100M|    10G|  3230M  (1)|999:59:59 |
 |   1 |  MERGE                                 | SHOWS      |       |       |            |          |
 |   2 |   VIEW                                 |            |       |       |            |          |
 |   3 |    NESTED LOOPS OUTER                  |            |   100M|    12G|  3230M  (1)|999:59:59 |
 |   4 |     VIEW                               |            |   100M|  5817M|   118K  (7)| 00:18:23 |
 |   5 |      SORT GROUP BY                     |            |   100M|  2288M|   118K  (7)| 00:18:23 |
 |   6 |       TABLE ACCESS FULL                | TEMP_SHOWS |   100M|  2288M|   113K  (2)| 00:17:30 |
 |   7 |     PARTITION RANGE ITERATOR           |            |     1 |    73 |    32   (0)| 00:00:01 |
 |   8 |      VIEW                              |            |     1 |    73 |    32   (0)| 00:00:01 |
 |*  9 |       TABLE ACCESS BY LOCAL INDEX ROWID| SHOWS      |     1 |    25 |    32   (0)| 00:00:01 |
 |* 10 |        INDEX RANGE SCAN                | IDX_SHOWS  |     1 |       |    32   (0)| 00:00:01 |
 -----------------------------------------------------------------------------------------------------

Кроме этого в документации даётся ещё одна, более гибкая рекомендация:

Для высоковолатильных таблиц…:

  • Статистика может быть удалена. Если Oracle сталкивается с таблицей без статистики, необходимая статистика собирается динамически как часть процесса оптимизации запроса. Динамический сбор статистики контролируется параметром OPTIMIZER_DYNAMIC_SAMPLING

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

После удаления+блокирования статистики:

11.1.0.7.@SQL> BEGIN
 2    DBMS_STATS.DELETE_TABLE_STATS('&&table_owner','TEMP_SHOWS', force => TRUE);
 3    DBMS_STATS.LOCK_TABLE_STATS('&&table_owner','TEMP_SHOWS');
 4  END;
 5  /

PL/SQL procedure successfully completed.

план, построенный с использованием dynamic sampling, становится более правдоподобным (за исключением оценки Time, близкой к бесконечности):

 -----------------------------------------------------------------------------------------------------
 | Id  | Operation                              | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
 -----------------------------------------------------------------------------------------------------
 |   0 | MERGE STATEMENT                        |            |    16M|  1761M|   532M  (1)|999:59:59 |
 |   1 |  MERGE                                 | SHOWS      |       |       |            |          |
 |   2 |   VIEW                                 |            |       |       |            |          |
 |   3 |    NESTED LOOPS OUTER                  |            |    16M|  2107M|   532M  (1)|999:59:59 |
 |   4 |     VIEW                               |            |    16M|   959M| 14919   (8)| 00:02:19 |
 |   5 |      SORT GROUP BY                     |            |    16M|   959M| 14919   (8)| 00:02:19 |
 |   6 |       TABLE ACCESS FULL                | TEMP_SHOWS |    16M|   959M| 14059   (2)| 00:02:11 |
 |   7 |     PARTITION RANGE ITERATOR           |            |     1 |    73 |    32   (0)| 00:00:01 |
 |   8 |      VIEW                              |            |     1 |    73 |    32   (0)| 00:00:01 |
 |*  9 |       TABLE ACCESS BY LOCAL INDEX ROWID| SHOWS      |     1 |    25 |    32   (0)| 00:00:01 |
 |* 10 |        INDEX RANGE SCAN                | IDX_SHOWS  |     1 |       |    32   (0)| 00:00:01 |
 -----------------------------------------------------------------------------------------------------

Note
 -----
 - dynamic sampling used for this statement

Интересно оценить погрешность, с которой dynamic sampling рассчитала объём вспомогательной таблички:

11.1.0.7.@SQL> select count(*) from &table_owner.TEMP_SHOWS;

COUNT(*)
 ----------
 14480035

11.1.0.7.@SQL> select 'Dynamic Sampling Accuracy ~ '||round(((16491374-14480035)/16491374)*100)||'%' as RESUME from dual;

RESUME
 -------------------------------
 Dynamic Sampling Accuracy ~ 12%

— вполне удовлетворительная точность

Данные для оценки (numrows) в отсутствии статистики таблицы, имхо, берутся при чтении избранных блоков таблицы во время сэмплинга (avgrlen) и из имеющейся статистики сегмента (numblks):

11.1.0.7.@SQL> select 'DBA_TABLES' as data_source, num_rows as "NUM_ROWS | BYTES", blocks from dba_tables where table_name = 'TEMP_SHOWS'
 2  union all
 3  select 'DBA_SEGMENTS' as data_source, bytes as "NUM_ROWS | BYTES", blocks from dba_segments where segment_name = 'TEMP_SHOWS'
 4  /

DATA_SOURCE  NUM_ROWS | BYTES     BLOCKS
------------ ---------------- ----------
DBA_TABLES
DBA_SEGMENTS        602931200     294400

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

  1. В принципе можно же было воткнуть хинт /*+ dynamic_sampling(SHOWS N) */ или прибить профиль с ним, тогда погрешность можно было бы гибко варьировать для конкретной таблицы, не меняя параметра OPTIMIZER_DYNAMIC_SAMPLING

    комментарий от xtender — 23.03.2012 @ 00:18 | Ответить

    • совершенно правильно, можно добавить хинт — но интересна была возможность именно «безконтактного» влияния на план, без изменений в тексте, только изменениями в статистике, за которыми не нужно было бы следить в дальнейшем

      создать profile | outline | baseline — отличный вариант для которого dynamic sampling не очень то и нужен в таком простом случае, индексный доступ к таблице SHOWS будет фиксироваться / стимулироваться подсказками в baseline, например

      интересно заметить, что подсказка dynamic_sampling и параметр OPTIMIZER_DYNAMIC_SAMPLING при одинаковых значениях делают разные оценки:

      SQL> select count(*) from temp_shows;
      
        COUNT(*)
      ----------
         1652629
      
      SQL> @param optimizer_dynamic_sampling
      
      NAME                        VALUE
      --------------------------- -----
      optimizer_dynamic_sampling  2
      
      SQL> explain plan for merge ...
      
      -----------------------------------------------------------------------------
      | Id  | Operation                              | Name               | Rows  |
      -----------------------------------------------------------------------------
      |   0 | MERGE STATEMENT                        |                    |  1309K|
      |   1 |  MERGE                                 | SHOWS              |       |
      |   2 |   VIEW                                 |                    |       |
      |   3 |    NESTED LOOPS OUTER                  |                    |  1309K|
      |   4 |     VIEW                               |                    |  1309K|
      |   5 |      SORT GROUP BY                     |                    |  1309K|
      |   6 |       TABLE ACCESS FULL                | TEMP_SHOWS         |  1309K|
      |   7 |     PARTITION RANGE ITERATOR           |                    |     1 |
      |   8 |      VIEW                              |                    |     1 |
      |*  9 |       TABLE ACCESS BY LOCAL INDEX ROWID| SHOWS              |     1 |
      |* 10 |        INDEX RANGE SCAN                | IDX_SHOWS          |     1 |
      -----------------------------------------------------------------------------

      — при default’ном сэмплинге точность невысокая ~ 20%

      SQL> explain plan for merge /*+ dynamic_sampling(SRC.TEMP_SHOWS 6) */ ...
      
      -----------------------------------------------------------------------------
      | Id  | Operation                              | Name               | Rows  |
      -----------------------------------------------------------------------------
      |   0 | MERGE STATEMENT                        |                    |  1688K|
      |   1 |  MERGE                                 | SHOWS              |       |
      |   2 |   VIEW                                 |                    |       |
      |   3 |    NESTED LOOPS OUTER                  |                    |  1688K|
      |   4 |     VIEW                               |                    |  1688K|
      |   5 |      SORT GROUP BY                     |                    |  1688K|
      |   6 |       TABLE ACCESS FULL                | TEMP_SHOWS         |  1688K|
      |   7 |     PARTITION RANGE ITERATOR           |                    |     1 |
      |   8 |      VIEW                              |                    |     1 |
      |*  9 |       TABLE ACCESS BY LOCAL INDEX ROWID| SHOWS              |     1 |
      |* 10 |        INDEX RANGE SCAN                | IDX_SHOWS          |     1 |
      -----------------------------------------------------------------------------
      
      SQL> alter session set optimizer_dynamic_sampling = 6;
      
      Session altered.
      
      SQL> explain plan for merge ...
      
      -----------------------------------------------------------------------------
      | Id  | Operation                              | Name               | Rows  |
      -----------------------------------------------------------------------------
      |   0 | MERGE STATEMENT                        |                    |  1656K|
      |   1 |  MERGE                                 | SHOWS              |       |
      |   2 |   VIEW                                 |                    |       |
      |   3 |    NESTED LOOPS OUTER                  |                    |  1656K|
      |   4 |     VIEW                               |                    |  1656K|
      |   5 |      SORT GROUP BY                     |                    |  1656K|
      |   6 |       TABLE ACCESS FULL                | TEMP_SHOWS         |  1656K|
      |   7 |     PARTITION RANGE ITERATOR           |                    |     1 |
      |   8 |      VIEW                              |                    |     1 |
      |*  9 |       TABLE ACCESS BY LOCAL INDEX ROWID| SHOWS              |     1 |
      |* 10 |        INDEX RANGE SCAN                | IDX_SHOWS          |     1 |
      -----------------------------------------------------------------------------

      — параметр OPTIMIZER_DYNAMIC_SAMPLING даёт большую точность, чем хинт

      комментарий от Igor Usoltsev — 23.03.2012 @ 13:39 | Ответить


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