Oracle mechanics

07.03.2011

Ошибочная оценка NDV столбцов с параметром DBMS_STATS.AUTO_SAMPLE_SIZE в версии 10.2

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

При, как обычно, «внезапном» изменении плана выполнения запроса в сторону использования медленного (в этом случае) hash join вместо быстрого индексного доступа (index range/unique scan), в трейсе 10053 event обнаружил любопытные вещи:

1) С одной стороны, при вычислении кол-ва неповторяющихся значений (NDV) столбца CLIENT_ID, оптимизатор использует значение 7902

***************************************
SINGLE TABLE ACCESS PATH
 ...
Column (#1): CLIENT_ID(NUMBER)
 AvgLen: 6.00 NDV: 7902 Nulls: 0 Density: 1.2655e-04 Min: 0 Max: 1540056
 Table: MV_2  Alias: MV_2
 Card: Original: 13808680  Rounded: 1747  Computed: 1747.49  Non Adjusted: 1747.49

, и из этого оценивает кол-во возвращаемых строк (Cardinality) по условию CLIENT_ID = :id(в отсутствии гистограммы по столбцу), как

кол-во строк в таблице (Original [Cardinality]) / кол-во неповторяющихся значений (NDV) столбца CLIENT_ID
13808680 / 7902 = 1747.49

Впоследствии на основе этого расчёта оптимизатор строит «плохой» дорогой и медленный план выполнения (MAT_VIEW здесь используется просто как таблица):

-----------------------------------------------------------------------------------------------
| Id  | Operation                             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |     1 |    64 |       |   202K  (1)| 00:37:04 |
|   1 |  SORT AGGREGATE                       |     1 |    64 |       |            |          |
|*  2 |   HASH JOIN                           | 30300 |  1893K|  3904K|   202K  (1)| 00:37:04 |
|*  3 |    HASH JOIN                          | 67704 |  3107K|  2716K|   137K  (1)| 00:25:17 |
|   4 |     TABLE ACCESS BY INDEX ROWID       |    13 |   156 |       |    15   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                     | 59108 |  2020K|       | 59140   (1)| 00:10:51 |
|   6 |       NESTED LOOPS                    |  4423 |    99K|       |  4376   (1)| 00:00:49 |
|   7 |        SORT UNIQUE                    |  1747 | 19217 |       |  1752   (1)| 00:00:20 |
|   8 |         MAT_VIEW ACCESS BY INDEX ROWID|  1747 | 19217 |       |  1752   (1)| 00:00:20 |
|*  9 |          INDEX RANGE SCAN             |  1747 |       |       |    12   (0)| 00:00:01 |
|  10 |        TABLE ACCESS BY INDEX ROWID    |     3 |    36 |       |     3   (0)| 00:00:01 |
|* 11 |         INDEX RANGE SCAN              |     3 |       |       |     0   (0)| 00:00:01 |
|* 12 |       INDEX RANGE SCAN                |    16 |       |       |     0   (0)| 00:00:01 |
|  13 |     TABLE ACCESS FULL                 |    18M|   207M|       | 55918   (1)| 00:10:16 |
|* 14 |    TABLE ACCESS FULL                  |  4141K|    67M|       | 57830   (1)| 00:10:37 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 9 - access("MV_2"."CLIENT_ID"=1197261)

2) С другой стороны, в том же файле трейса есть другие (как дальше будет видно, правильные) данные об NDV столбца CLIENT_ID, точнее о кол-ве неповторяющихся значений (Number of Distinct Keys — #DK) индекса по этому столбцу

***********************
Table Stats::
Table: MV_2  Alias: MV_2
#Rows: 13808680  #Blks:  65442  AvgRowLen:  11.00
...
Index Stats::
Index: MV_CLIENT_ID_IDX  Col#: 1
LVLS: 3  #LB: 67082  #DK: 103353 LB/K: 1.00  DB/K: 133.00  CLUF: 13747920.00
***************************************

Очевидно, Oracle всё это видит (и записывает в трейс), но поступает (строит план) в точном соответствии с описанным в документации правилом:

«Статистика оптимизатора, используемая для выбора лучшего плана выполнения для каждого SQL … включает:

Table statistics

Number of rows
Number of blocks
Average row length

Column statistics

Number of distinct values (NDV) in column
Number of nulls in column
Data distribution (histogram)

Index statistics

Number of leaf blocks
Levels
Clustering factor

…»

- т.е. кол-во неповторяющихся значений (NDV) столбца таблицы определяется на основании статистики столбца, а статистика индекса, даже если этот индекс построен на одном столбце, при этом в расчёт не берётся.

Смотрим статистику по столбцу:

SQL> select column_name, num_distinct from dba_tab_col_statistics where table_name = 'MV_2' and column_name = 'CLIENT_ID';

COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------
CLIENT_ID                          7902

в то время как в действительности имеем:

SQL> select count(distinct CLIENT_ID) from MV_2;

COUNT(DISTINCT CLIENT_ID)
 ----------------------------
103287

- отличие используемых оптимизатором данных от реальных более, чем в 10 раз (!), и как следствие — «плохой» план и «слёзы страждущих» :(

При условии, что статистика собирается периодически, рекомендованным способом (dbms_scheduler GATHER_STATS_JOB) со всеми рекомендованными Oracle параметрами:

SQL> select
 2  DBMS_STATS.get_param('NO_INVALIDATE') "NO_INVALIDATE",
 3  DBMS_STATS.get_param('CASCADE') "CASCADE",
 4  DBMS_STATS.get_param('ESTIMATE_PERCENT') "ESTIMATE_PERCENT",
 5  DBMS_STATS.get_param('METHOD_OPT') "METHOD_OPT"
 6  from dual
 7  /

NO_INVALIDATE               CASCADE                  ESTIMATE_PERCENT             METHOD_OPT
--------------------------  -----------------------  ---------------------------  -------------------------
DBMS_STATS.AUTO_INVALIDATE  DBMS_STATS.AUTO_CASCADE  DBMS_STATS.AUTO_SAMPLE_SIZE  FOR ALL COLUMNS SIZE AUTO

- результат получается неправильным печальный! Основное влияние на ошибочную статистику в части NDV в этом случае оказывает параметр ESTIMATE_PERCENT, c автоматически установливаемым значением DBMS_STATS.AUTO_SAMPLE_SIZE, который для проблемной таблицы составлял ~10% :

SQL> select sample_size/num_rows from dba_tab_statistics where owner = 'TEST' and table_name = 'MV_2';

Баг — не баг, но проблема — на стороне Oracle (точнее, это — проблема версии 10.2, premier support которой закончился в июле 2010). Следующие workaround’ы могут быть использованы (до обновления на Oracle 11 ;)

1) после сбора статистики устанавливать NDV для проблемных столбцов вручную

SQL> exec DBMS_STATS.set_column_stats(ownname => 'TEST',tabname  => 'MV_2',colname =>  'CLIENT_ID',distcnt => 100000, no_invalidate => false);

2) вместо оценки (estimate statistics), собирать статистику для проблемных таблиц (или схем) в полном объёме (compute statistics, параметр estimate_percent = null или 100)

SQL> exec DBMS_STATS.gather_table_stats(ownname =>  'TEST',tabname => 'MV_2', estimate_percent  => null , no_invalidate => false);
SQL> exec DBMS_STATS.gather_schema_stats(ownname =>   'TEST', estimate_percent  => null ,  no_invalidate => false);

3) фиксировать «хорошую» статистику процедурами DBMS_STATS.lock_table_stats, DBMS_STATS.lock_schema_stats

Применение любого из перечисленных способов тут же даёт «правильный» быстрый план, в котором благодаря уточнённому значению NDV более чем на порядок уменьшено количество предположительно возвращаемых по условию строк (Rows — с 1747 до 134), и следовательно заметно понизившейся стоимостью (Cost — с 20200 до 17962). И, конечно же, заметно уменьшившимся предсказанным (и реальным) временем выполнения (Time)

--------------------------------------------------------------------------------------
| Id  | Operation                            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |     1 |    64 | 17962   (1)| 00:03:18 |
|   1 |  SORT AGGREGATE                      |     1 |    64 |         |             |
|   2 |   NESTED LOOPS                       |  2318 |   144K| 17962   (1)| 00:03:18 |
|   3 |    NESTED LOOPS                      |  5180 |   237K| 12779   (1)| 00:02:21 |
|   4 |     NESTED LOOPS                     |  4522 |   154K|  4525   (1)| 00:00:50 |
|   5 |      NESTED LOOPS                    |   338 |  7774 |   340   (1)| 00:00:04 |
|   6 |       SORT UNIQUE                    |   134 |  1474 |   138   (0)| 00:00:02 |
|   7 |        MAT_VIEW ACCESS BY INDEX ROWID|   134 |  1474 |   138   (0)| 00:00:02 |
|*  8 |         INDEX RANGE SCAN             |   134 |       |     4   (0)| 00:00:01 |
|   9 |       TABLE ACCESS BY INDEX ROWID    |     3 |    36 |     3   (0)| 00:00:01 |
|* 10 |        INDEX RANGE SCAN              |     3 |       |     0   (0)| 00:00:01 |
|  11 |      TABLE ACCESS BY INDEX ROWID     |    13 |   156 |    15   (0)| 00:00:01 |
|* 12 |       INDEX RANGE SCAN               |    16 |       |     0   (0)| 00:00:01 |
|  13 |     TABLE ACCESS BY INDEX ROWID      |     1 |    12 |     2   (0)| 00:00:01 |
|* 14 |      INDEX RANGE SCAN                |     1 |       |     0   (0)| 00:00:01 |
|* 15 |    TABLE ACCESS BY INDEX ROWID       |     1 |    17 |     1   (0)| 00:00:01 |
|* 16 |     INDEX UNIQUE SCAN                |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Тот же неправильный NDV был причиной «неожиданного» изменения плана выполнения: стоимости «быстрого» плана с индексным доступом и ошибочным NDV (кот. можно грубо оценить, умножив стоимость последнего плана на коэффициент ошибки оценки NDV ~ 17962*1747/134 = 229k) и «медленного» с использованием hash join (202k) были сравнимы и в зависимости от незначительных изменений статистики эти планы могли меняться местами по стоимости с точки зрения оптимизатора

P.S. Баян, естественно, старый (всё-таки 10.2), давно описанный и исправленный в версии 11 — см. Oracle 11g: Enhancements to DBMS_STATS by Greg Rahn, где экспериментально показано, что в Oracle 11g не только значительно увеличена скорость сбора статистики, но и заметно увеличена точность вычисления кол-ва неповторяющихся значений (NDV) столбцов при использовании ESTIMATE_PERCENT = DBMS_STATS.AUTO_SAMPLE_SIZE (практически до 100%-ного совпадения с результатами COMPUTE STATISTICS), что не может не радовать :)

P.P.S. Для грубой оценки качества сбора статистики в Oracle 10.2 ( по несовпадению NDV в статистиках таблиц и #DK для индексов по одному столбцу) можно использовать запрос типа:

select table_name, column_name, diff * 100 as "diff %"
 from (select i.table_name,
 i.index_name,
 tcs.column_name,
 i.distinct_keys / tcs.num_distinct as diff
 from dba_indexes i, dba_ind_columns ic, dba_tab_col_statistics tcs
 where i.owner = '&owner'
 and tcs.owner = i.owner
 and tcs.table_name = i.table_name
 and (select count(*)
 from dba_ind_columns ic2
 where i.table_name = ic2.table_name
 and i.index_name = ic.index_name
 and ic2.index_owner = i.owner) = 1
 and i.index_name = ic.index_name
 and i.table_name = ic.table_name
 and tcs.column_name = ic.column_name
 and i.distinct_keys - tcs.num_distinct <> 0
 and tcs.num_distinct > 0)
where diff > 3/2 or diff < 2/3;
About these ads

1 комментарий »

  1. Баян то определенно старый, но нарвались мы на него совсем недавно (10.2.0.5). Запросы, выполняющиеся достаточно неплохо, в какое то время переставали работать — причем план показывался хороший. DBMS_SQLTUNE посоветовал использование индекса который вроде был, но оптимизатор упорно не хотел его брать и показывал запредельную стоимость запроса при его использовании через хинт, при этом время выполнения было действительно лучше. Тут попалась ваша заметка, которая помогла разрулить ситуацию — нашлась разница в NDV столбца (1154 против реальных 133447). Использовал первый вариант — установил значение вручную, хотя остался вопрос по sample_size/num_rows — запрос показал 1, странно, как это так получилось. Спасибо.

    комментарий от Владимир — 26.11.2012 @ 09:45 | Ответить


RSS-лента комментариев к этой записи. TrackBack URI

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

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

Логотип WordPress.com

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

Фотография Twitter

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

Фотография Facebook

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

Google+ photo

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

Connecting to %s

Тема: Rubric. Блог на WordPress.com.

Отслеживать

Get every new post delivered to your Inbox.

Join 142 other followers

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