При, как обычно, “внезапном” изменении плана выполнения запроса в сторону использования медленного (в этом случае) 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;
Баян то определенно старый, но нарвались мы на него совсем недавно (10.2.0.5). Запросы, выполняющиеся достаточно неплохо, в какое то время переставали работать – причем план показывался хороший. DBMS_SQLTUNE посоветовал использование индекса который вроде был, но оптимизатор упорно не хотел его брать и показывал запредельную стоимость запроса при его использовании через хинт, при этом время выполнения было действительно лучше. Тут попалась ваша заметка, которая помогла разрулить ситуацию – нашлась разница в NDV столбца (1154 против реальных 133447). Использовал первый вариант – установил значение вручную, хотя остался вопрос по sample_size/num_rows – запрос показал 1, странно, как это так получилось. Спасибо.
комментарий от Владимир — 26.11.2012 @ 09:45 |