Export/ImportPL/SQL
DBA's DictionaryКогда ошибается CBO?
Когда ошибается Cost Based Optimizer? Не так уж и часто, но бывает. Вот например интересный на мой взгляд случай.
SQL> SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production PL/SQL Release 8.1.6.0.0 - Production CORE 8.1.6.0.0 Production TNS for Solaris: Version 8.1.6.0.0 - Production NLSRTL Version 3.4.0.0.0 - ProductionИсходный запрос:
SELECT MAX(SAL.START_DATE)
FROM SAL, SAL_SERVICES
WHERE SAL.ID = SAL_SERVICES.SAL_ID AND
SAL_SERVICES.OLD_CONTRA_SERVICE_ID = :b1 AND
SAL_SERVICES.OPERATION = :b2 AND
SAL.START_DATE IS NOT NULL
где SAL - родительская таблица, SAL_SERVICES - дочерняя таблица. Поле SAL_SERVICES.SAL_ID смотрит на SAL.ID через foreign key.
SQL>
SQL> col table_name format a15
SQL> col column_name format a25
SQL> col data_type format a9
SQL>
SQL> select table_name, column_name, data_type
2 from user_tab_columns
3 where table_name in ('SAL', 'SAL_SERVICES')
4 order by table_name, column_id;
TABLE_NAME COLUMN_NAME DATA_TYPE
--------------- ------------------------- ---------
SAL ID NUMBER
SAL SAL_NUMBER VARCHAR2
SAL CREATION_DATE DATE
SAL START_DATE DATE
SAL CLIENT_ID NUMBER
SAL BILL_ID NUMBER
SAL PERSON_ID NUMBER
SAL STATUS CHAR
SAL REFERENCE_ID NUMBER
SAL END_DATE DATE
SAL PI_BILL_ID NUMBER
SAL SALE_PERSON_ID NUMBER
SAL_SERVICES SAL_ID NUMBER
SAL_SERVICES PART_NUMBER NUMBER
SAL_SERVICES STATUS CHAR
SAL_SERVICES OLD_CONTRA_SERVICE_ID NUMBER
SAL_SERVICES NEW_SERVICE_ID NUMBER
SAL_SERVICES PRICE FLOAT
SAL_SERVICES ADVANCED_PAYMENT FLOAT
SAL_SERVICES OPERATION CHAR
SAL_SERVICES CHARGE_STRATEGY_ID NUMBER
SAL_SERVICES T_TYPE CHAR
SAL_SERVICES INVENTORY_ID NUMBER
SAL_SERVICES IS_LOCKED CHAR
SAL_SERVICES CHANNEL_CODE NUMBER
SAL_SERVICES DAYS_VIOLATION NUMBER
SAL_SERVICES OLD_DYNAMIC_SET_ID NUMBER
SAL_SERVICES NEW_DYNAMIC_SET_ID NUMBER
SQL>
SQL> analyze table sal compute statistics;
Table analyzed.
SQL> analyze table sal_services compute statistics;
Table analyzed.
SQL>
SQL> select table_name, num_rows, blocks, empty_blocks,
2 avg_space, avg_row_len avg_row_len, last_analyzed
3 from user_tables
4 where table_name in ('SAL', 'SAL_SERVICES');
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANAL
--------------- --------- --------- ------------ --------- ----------- ---------
SAL 1052319 11884 255 2040 66 13-MAR-01
SAL_SERVICES 3151151 20684 310 2048 38 13-MAR-01
Из всех существующих индексов на этих таблицах оптимизатор с учетом вида кляузы WHERE
запроса может потенциально использовать лишь следующие (выделены цветом):
SQL>
SQL> select table_name, index_name, column_name, column_position col_pos
2 from user_ind_columns
3 where table_name in ('SAL', 'SAL_SERVICES')
4 order by table_name, index_name, column_position;
TABLE_NAME INDEX_NAME COLUMN_NAME COL_POS
--------------- ------------------------------ ------------------------- -------
SAL FK_SUBS_CRM CLIENT_ID 1
SAL FK_SUBS_CRM STATUS 2
SAL IDX_SAL_BILL_ID BILL_ID 1
SAL IDX_SAL_PERSON_ID PERSON_ID 1
SAL IDX_SAL_REFERENCE_ID REFERENCE_ID 1
SAL IND_SAL_PI_BILLS PI_BILL_ID 1
SAL I_SALSALEPERSON_ID SALE_PERSON_ID 1
SAL PK_SAL ID 1
SAL_SERVICES IDX_SAL_SERVICES_CHARGE_STRATE CHARGE_STRATEGY_ID 1
SAL_SERVICES IDX_SAL_SERVICES_NEW_SERVICEID NEW_SERVICE_ID 1
SAL_SERVICES IND_SALSERV_INVENTORY INVENTORY_ID 1
SAL_SERVICES IND_SALSERV_OLDCONTRSERV OLD_CONTRA_SERVICE_ID 1
SAL_SERVICES PK_SAL_SERVICES SAL_ID 1
SAL_SERVICES PK_SAL_SERVICES PART_NUMBER 2
SAL_SERVICES SAL_ID_IDX SAL_ID 1
Наличие двух индексов PK_SAL_SERVICES, SAL_ID_IDX с одинаковым лидирующим столбцом оставим за рамками этой статьи.
Рассматриваем ситуацию 'как есть'.
Индексы PK_SAL, SAL_ID_IDX или индекс PK_SAL_SERVICES (по лидирующему столбцу) могут
быть потенциально использованы стоимостным оптимизатором для соединения таблиц. Индекс IND_SALSERV_OLDCONTRSERV может быть
выбран по условию равенства в кляузе WHERE SAL_SERVICES.OLD_CONTRA_SERVICE_ID = :b1.
OPERATION OPTIONS OBJECT_NAME ID PAR_ID COST CARDINALITY ---------------- -------------- ------------------------ -- ------ ---- ----------- SELECT STATEMENT 0 7122 1 SORT AGGREGATE 1 0 1 MERGE JOIN 2 1 7122 5323 SORT JOIN 3 2 17 5323 TABLE ACCESS BY INDEX ROWID SAL_SERVICES 4 3 2 5323 INDEX RANGE SCAN IND_SALSERV_OLDCONTRSERV 5 4 1 5323 SORT JOIN 6 2 7105 1052207 TABLE ACCESS FULL SAL 7 6 1804 1052207
Непонятно почему оптимизатор выбирает полное сканирование таблицы SAL? Казалось бы, что может быть лучше, чем доступ к inner таблице (в нашем случае таблица SAL, более миллиона строк) по первичному ключу PK_SAL. Но вместо ожидаемого поведения CBO выбирает полное сканирование таблицы.
Ответ на этот вопрос заключается в следующем:
SQL> select count(*), count(old_contra_service_id) from sal_services; COUNT(*) COUNT(OLD_CONTRA_SERVICE_ID) --------- ---------------------------- 3151151 149
NULL'ы составляют более 99.999% всех значений столбца OLD_CONTRA_SERVICE_ID! Поскольку значения NULL не хранятся в индексе, размер IND_SALSERV_OLDCONTRSERV равен всего лишь одному блоку. Отличный индекс!
SQL> SQL> select index_name, 2 blevel, 3 leaf_blocks leaf_blks, 4 distinct_keys dist_keys, 5 avg_leaf_blocks_per_key avg_leaf, 6 avg_data_blocks_per_key avg_data, 7 clustering_factor clust_factor 8 from user_indexes 9 where index_name = 'IND_SALSERV_OLDCONTRSERV'; INDEX_NAME BLEVEL LEAF_BLKS DIST_KEYS AVG_LEAF AVG_DATA CLUST_FACTOR ------------------------ ------ --------- --------- -------- -------- ------------ IND_SALSERV_OLDCONTRSERV 0 1 148 1 1 16
Хорошо, но при чем здесь индекс по таблице SAL_SERVICES, который и так подхватывается оптимизатором, ведь мы имеем FULL SCAN по таблице SAL? Попробуем слегка видоизменить запрос. Вместо bind-переменной :b1 подставим литерал и снова посмотрим план:
SELECT MAX(SAL.START_DATE)
FROM SAL, SAL_SERVICES
WHERE SAL.ID = SAL_SERVICES.SAL_ID AND
SAL_SERVICES.OLD_CONTRA_SERVICE_ID = 400292531 AND
SAL_SERVICES.OPERATION = :b2 AND
SAL.START_DATE IS NOT NULL;
Explain plan:
OPERATION OPTIONS OBJECT_NAME ID PAR_ID COST CARDINALITY
---------------- -------------- ------------------------ -- ------ ---- -----------
SELECT STATEMENT 0 4 1
SORT AGGREGATE 1 0 1
NESTED LOOPS 2 1 4 1
TABLE ACCESS BY INDEX ROWID SAL_SERVICES 3 2 2 1
INDEX RANGE SCAN IND_SALSERV_OLDCONTRSERV 4 3 1 1
TABLE ACCESS BY INDEX ROWID SAL 5 2 2 1052207
INDEX UNIQUE SCAN PK_SAL 6 5 1 1052207
В чем принципиальная разница между двумя планами? Cуть даже не в том, что изменился метод соединения таблиц (сначала
использовался SORT-MERGE, теперь N-L) - это вторичный результат. Существенно то, что для таблицы SAL_SERVICES изменилось
значение CARDINALITY с исходных 5323 до 1. Это означает, что оцениваемое оптимизатором число возвращаемых строк для данной
операции уменьшилось более чем в 5000 раз! Это уже больше похоже на истину, поскольку имея в столбце всего 149 не NULL
значений, я по определению не могу получить на выходе 5323 строки через
Любопытно, что для данной ситуации Oracle расчитывает CARDINALITY с понижающим коэффициентом 0.25, то есть:
CARDINALITY = CEIL(<Число строк таблицы>/<Число distinct-значений>)*0.25 = CEIL(3151151/148)*0.25 = 5323
Теперь причина понятна, при использовании предиката
Проверим предположение, добавив в кляузу WHERE явное указание OLD_CONTRA_SERVICE_ID IS NOT NULL.
SELECT MAX(SAL.START_DATE)
FROM SAL, SAL_SERVICES
WHERE SAL.ID = SAL_SERVICES.SAL_ID AND
SAL_SERVICES.OLD_CONTRA_SERVICE_ID = :b1 AND
SAL_SERVICES.OPERATION = :b2 AND
SAL.START_DATE IS NOT NULL AND
SAL_SERVICES.OLD_CONTRA_SERVICE_ID IS NOT NULL
Explain plan:
OPERATION OPTIONS OBJECT_NAME ID PAR_ID COST CARDINALITY
---------------- -------------- ------------------------ -- ------ ---- -----------
SELECT STATEMENT 0 4 1
SORT AGGREGATE 1 0 1
NESTED LOOPS 2 1 4 1
TABLE ACCESS BY INDEX ROWID SAL_SERVICES 3 2 2 1
INDEX RANGE SCAN IND_SALSERV_OLDCONTRSERV 4 3 1 1
TABLE ACCESS BY INDEX ROWID SAL 5 2 2 1052207
INDEX UNIQUE SCAN PK_SAL 6 5 1 1052207
Похоже, что предположение оказалось верным. Вторичный результат - стоимость запроса упала с 7122 до 4. Теперь осталось понять, почему оптимизатор в исходном запросе игнорировал доступ к таблице SAL по первичному ключу, предпочитая FULL TABLE SCAN. Здесь нужно считать c калькулятором. :-)
SQL> SQL> col name format a40 SQL> col value format a5 SQL> select name, value 2 from v$parameter 3 where name like 'db_file_multi%'; NAME VALUE ---------------------------------------- ----- db_file_multiblock_read_count 8 SQL> SQL> select index_name, 2 blevel, 3 leaf_blocks leaf_blks, 4 distinct_keys, 5 avg_leaf_blocks_per_key avg_leaf_perkey, 6 avg_data_blocks_per_key avg_data_per_key, 7 clustering_factor 8 from user_indexes 9 where index_name = 'PK_SAL'; INDEX_NAME BLEVEL LEAF_BLKS DIST_KEYS AVG_LEAF AVG_DATA CLUST_FACTOR ---------- ------ --------- --------- -------- -------- ------------ PK_SAL 2 3803 1052319 1 1 34758 SQL>Полное сканирование:
<Число операций I/O> = CEIL(<Число блоков таблицы>/<db_file_multiblock_read_count>) = CEIL(11884/8) = 1486
Доступ через первичный ключ (индекс PK_SAL):
<Число операций I/O> =
Можно ли было в этой ситуации использовать хинт /*+ RULE */? Ответ: можно, но не нужно.
Вся разница в подходе. Добавив
в запрос дополнительную проверку
Добавляя хинт, Вы получите тот же результат на текущий момент, но есть ли у Вас как у Разработчика гарантии,
что у Заказчика будет такое же распределение данных в таблице, что через год в новой версии Вашей Системы не будут
добавлены новые индексы, а старые не будут модифицированы? Впрочем это уже тема для отдельного разговора.