Выбор плана на выполнение

Вступление

Не так часто, но все же бывает что оптимизатор выбирает не тот план выполнения, который хотелось бы нам, а другой. Оказывается можно посмотреть, почему это происходит и даже немного поменять настройки  оптимизатора. 

Почему не просто поставить hint ? Наверно можно поставить hint. Но, что если такое странное поведение случается со многими запросами ? А что если потом несколько измениться структура данных ? В общем, мое мнение такое - hint нужно ставить при дефиците времени, или когда больше ничего не помогает.

Для общего понимая разницы между RBO и CBO, некоторых механизмов работы субд, может быть полезна статья Tim Gorman "The Search for Intelligent Life in the Cost-Based Optimizer " найти ее можно по адресу http://www.evdbt.com/papers.htm или здесь, а здесь есть ее русский перевод Эдуарда Шевцова. 

Другие полезные ссылки:

http://www.ixora.com.au/scripts/dumps.htm - набор скриптов для включения выключения трассировок, опеределения имени файла и т.д.

Замечательная, но сходу непонятная презентация Wolfgang Breitling. Более понятный его же документ с сервера hotsos.com (требуется регистрация)

Лабораторная работа

Итак, получаем trace файл и изучаем его:

smap13@DVP8.1.7> ALTER SESSION SET EVENTS '10053 trace name context forever, level 1'; 

Session altered. 

smap13@DVP8.1.7> EXPLAIN PLAN FOR select a.personal, a.mailbox, a.host, a.adl, 

a.error, h.msg, hn.value as name from readable_rfc822_address a, mail_addr_hdr h,

 mail_header_name hn where h.addr = a.id and hn.id = h.name and h.msg = 1631;

Explained

Полученный в результате файл оказался в директории bdump, хотя все утверждают что должен он быть в udump. Вначале он похож на обычный trc файл (описание его формата), но дальше со слов  "PARAMETERS USED BY THE OPTIMIZER" начинается самое интересное. 

Общая структура файла trc

• секция  PARAMETERS USED BY THE OPTIMIZER содержащая параметры, которые используются оптимизатором
• секция "BASE STATISTICAL INFORMATION", содержащая статистическую информацию о таблице и ее индексах
• секция SINGLE TABLE ACCESS PATH, содержащая возможные пути обхода отдельных таблиц с оценками их стоимости
• секция OPTIMIZER STATISTICS AND COMPUTATIONS содержащая уже различные варианты выполнения с их оценками

Для понимания информации в файле рекомендую глоссарий

IXSEL	index selectivity
TBSEL	table selectivity
ORIG CDN	число записей в таблице
CMPTD CDN	число записей которые удовлетворяют условию предиката связываемой таблицы
DENS	плотность колонки
LVLS	BLEVEL, высота индекса
CDN	кол-во записей в таблице
NBLKS	число блоков under high watermark
TABLE_SCAN_CST	стоимость full table scan
AVG_ROW_LEN	средняя длина записи
NDV	число уникальных значение колонки
NULLS	число записей с NULL значением для колонки
#LB	число leaf bloks
#DK	число уникальных ключей индекса
LB/K	среднее число блоков для ключа (AVG_LEAF_BLOCKS_PER_KEY)
DB/K	средне число блоков данных для ключа
CLUF	CLUSTERING_FACTOR
index#, col#	object# и column_id из dba_indexes

PARAMETERS USED BY THE OPTIMIZER

Во первых, среди параметров которые использует оптимизатор больше скрытых (hidden) чем открытых :((. Из открытых можно упомянуть:  

OPTIMIZER_MAX_PERMUTATIONS = 80000 OPTIMIZER_SEARCH_LIMIT = 5	Статья metalink об этих параметрах. Кажется рекомендуется их изменить (уменьшить)  для 8.1.7.
OPTIMIZER_INDEX_CACHING = 0
OPTIMIZER_INDEX_COST_ADJ = 100	Чтобы узнать во что его поставить можно сделать следующий запрос  select event, average_wait from v$system_event where event like 'db_file s%' и оценить во сколько раз на вашей системе времена ожиданий для full scan и индексов отличаются.
SORT_AREA*
HASH_AREA*

BASE STATISTICAL INFORMATION

Эту секцию следует просмотреть на предмет проверки, насколько Вы хорошо представляете себе распределение данных в данной системе.

SINGLE TABLE ACCESS PATH

Вот тут уже начинаются интересные места. Wolfgang Breitling отмечает, что стоимость полного сканирования таблицы оказывается не есть кол-во блоков таблицы / DB_FILE_MULTIBLOCK_READ_COUNT. Так у меня, для таблицы MAIL_ADDR_HDR получается 1759 / 268 = 6.56 <> 8. Далее он приводит весьма занятный график, показывающий что при увеличении параметра DB_FILE_MULTIBLOCK_READ_COUNT стоимость падает не пропорционально, а по экпоненте. Вроде как из этого следует что если менять этот параметр, то уж не мелочиться :)) 

***********************
Table stats Table: MAIL_ADDR_HDR Alias: H
TOTAL :: CDN: 471046 NBLKS: 1759 TABLE_SCAN_CST: 268 AVG_ROW_LEN: 25
-- Index stats
INDEX#: 43083 COL#: 2 
TOTAL :: LVLS: 2 #LB: 1531 #DK: 4485 LB/K: 1 DB/K: 29 CLUF: 130624
INDEX#: 43085 COL#: 4 
TOTAL :: LVLS: 2 #LB: 1327 #DK: 102409 LB/K: 1 DB/K: 1 CLUF: 4642
INDEX#: 43084 COL#: 3 
TOTAL :: LVLS: 2 #LB: 1501 #DK: 7 LB/K: 214 DB/K: 1793 CLUF: 12557
INDEX#: 71603 COL#: 1 
TOTAL :: LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF: 800