Сбор статистики и создание гистограмм

По материалам Тома Кайта
Если не указывать параметр method_opt, то Oracle соберет гистограммы в режиме auto. Количество баскетов в гистограмме в режиме auto определяется по использованию колонок в запросах в таблице SYS.COL_USAGE$ и распределению данных.

Данная таблица заполняется SMON (если установлен параметр _column_traking_level).

auto: сбора гистограмм также собирается указанием параметра method_opt => 'for all indexed columns size auto'

SIZE 1: при указании параметра method_opt=>'for all columns size 1' в гистограммы попадет бакет с наименьшим и наибольшим значением и общим количеством строк.

Для запрещения сбора гистограмм вообще, необходимо собирать статистику по таблице с параметром method_opt=>'for columns '

gather_stale - собирает гистограммы по таблицам, для которых данные бурно изменялись. Изменения определяются по вьюхам *_tab_modifications. Вьюхи автоматически заполняются при значении параметра STATISTICS_LEVEL = Typical или All.

Bind peeking и гистограммы тестирование

Тесты скриптом
DROP TABLE a;

CREATE TABLE a(a NUMBER, b CHAR);

CREATE INDEX ix_a ON a(a);

INSERT INTO a SELECT 3, 'x' FROM all_objects WHERE ROWNUM <= 1;
INSERT INTO a SELECT 2, 'x' FROM all_objects WHERE ROWNUM <= 1;

INSERT INTO a SELECT 1, 'x' FROM all_objects WHERE ROWNUM <= 1000;

--EXEC dbms_stats.gather_table_stats('sps', 'a', CASCADE => TRUE)
EXEC dbms_stats.gather_table_stats('sps', 'a', method_opt => 'FOR ALL INDEXED COLUMNS SIZE repeat', CASCADE => TRUE)
--EXEC dbms_stats.gather_table_stats('sps', 'a', method_opt => 'FOR ALL INDEXED COLUMNS SIZE 254', CASCADE => TRUE)

COMMIT;

var sa NUMBER
var ea NUMBER

EXEC :sa := 0;
EXEC :ea := 1;

ALTER SESSION SET tracefile_identifier = 'size_repeat';
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

SELECT * FROM a WHERE a BETWEEN :sa AND :ea;

EXEC :sa := 2;
EXEC :ea := 3;

SELECT * FROM a WHERE a BETWEEN :sa AND :ea;

DISCONNECT;

Дали следующие результаты:

БЕЗ УКАЗАНИЯ ГИСТОГРАММ (size=auto)
1. Bind peeking во время hard parse: +
2. План первого запроса: индексный (неправильно)
3. Ожидаемая кардинальность 334 строки
4. Bind Peeking второго запроса: - (взял разобранный курсор)

ГИСТОГРАММЫ 254
1. Bind peeking во время hard parse: +
2. План первого запроса: полный (правильно)
3. Ожидаемая кардинальность 1000 строк
4. Bind Peeking второго запроса: - (взял разобранный курсор)

ГИСТОГРАММЫ repeat
(в документации не читал, но Кайт считает, что это без статистики )
1. Bind peeking во время hard parse: + (_|_)
2. План первого запроса: индексный (неправильно)
3. Ожидаемая кардинальность 334 строки
4. Bind Peeking второго запроса: -

Выводы:
1. Bind Peeking работает вне зависимости от наличия гистограмм
2. Bind Peeking работает только при Hard Parse. Заставить работать его просто так мы не можем.
3. Гистограммы помогли (при этом стоит учитывать, что количество уникальных значений меньше, чем количество бакетов)

bind peeking и гистограммы

Oracle обещает

There are two cases where the optimizer would peek at the actual bindings of a bind variable and where the actual bindings therefore could make a difference for what plan would get generated.
Range predicates. Example:
sales_date between :1 and :2 and price > :3.
Equality predicates when the column has histograms. Example:
order_status = :4
assuming that order_status has histograms.

Проверка показывает:
CREATE TABLE a(a NUMBER, b CHAR);

CREATE INDEX ix_a ON a(a);

INSERT INTO a SELECT 2, 'x' FROM all_objects WHERE ROWNUM <= 1;

INSERT INTO a SELECT 1, 'x' FROM all_objects WHERE ROWNUM <= 1000;

BEGIN
dbms_stats.gather_table_stats(
'sps',
'a',
CASCADE => TRUE,
method_opt => 'for all indexed columns size 50');
END;
/

COMMIT;

var a NUMBER

EXEC :a := 1;

ALTER SESSION SET tracefile_identifier = 'hist_test';
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

SELECT * FROM a WHERE a = :a;

EXEC :a := 2;

SELECT * FROM a WHERE a = :a;

DISCONNECT;


По трейсу 10053 имеем только одно вхождение строки
*******************************************
Peeked values of the binds in SQL statement
*******************************************

и имеем следующие планы выполнения:
SELECT *
FROM
a WHERE a = :a


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.34 0.38 0 0 0 0
Fetch 11 0.00 0.00 0 16 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13 0.34 0.38 0 16 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 781

Rows Row Source Operation
------- ---------------------------------------------------
1000 TABLE ACCESS FULL A (cr=16 pr=0 pw=0 time=1054 us)


SELECT *
FROM
a WHERE a = :a

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 7 0 1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 781

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL A (cr=7 pr=0 pw=0 time=128 us)

Результаты получаемы через explain plan:

SQL> SELECT * FROM a WHERE a = 2;

Execution Plan
----------------------------------------------------------

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| A | 1 | 5 | 2 (0)|
| 2 | INDEX RANGE SCAN | IX_A | 1 | | 1 (0)|
-------------------------------------------------------------------------

Это показывает, что для значения переменной :a=2 никакого дополнительного считывания переменной не было и в помине.

Bind Peeking бывает только при hard parse, судя по всему не зависимо от наличия гистограмм

Для отключения bind peeking можно воспользоваться скрытым параметром

ALTER SESSION SET "_optim_peek_user_binds"=FALSE;

Разница планов tkprof и set autotrace

По мотивам блога Тома Кайта

По сути дела set autotrace не что иное, как встроенный в SQL*Plus Explain Plan For со всеми вытекающими отсюда последствиями.

Помимо набившей оскомину: "...ваша статистика могла поменяться между реальным выполнением и explain'ом" существуют следующие особенности Explain Plan:

1. Explain Plan делает hard parse, который потом никогда ничем не используется
2. Explain Plan ничего не умеет делать с Bind-переменными, ничего не знает про bind peeking. Все предположения делаются судя по всему из селективности, посчитанной по num distincts
3. Explain plan считает, что все bind переменные имеют строковое значение, поэтому может показывать неправильный план, ввиду отсутствия неявного преобразования типов.

TKPROF при первом выполнении запроса ВСЕГДА делает hard parse, после чего использует сгенерированный план.

TKPROF

1. Row Source Operation

Показывает, сколько строк ВЫШЛО из этой операции за ОДНО выполнение запроса. Подробнее

2. Настройка aggregate=YES собирает все выполненные запросы в одну кучку и агрегирует их результаты

3. Для определения размера коллекции для выборки (array fetch size) нужно поделить количество выбранных строк на количество fetch. Если количество fetch превышает количество строк, то происходит что-то подобное:
open
execute
fetch
fetch
fetch (а строчек уже и нету)

4. БАГ ДОКУМЕНТАЦИИ Табличка, начиная с 9.2. НЕ ВКЛЮЧАЕТ в себя все операции (+ рекурсивные SQL, например в триггерах). Но в документации написано:

In ch 10 of the Oracle 9i Performance Tuning Guide, it says:
"The resources reported for a statement include those for all of the SQL issued while the statement
was being processed. Therefore, they include any resources used within a trigger, along with the
resources used by any other recursive SQL (such as that used in space allocation). With the SQL
Trace facility enabled, TKPROF reports these resources twice. Avoid trying to tune the DML
statement if the resource is actually being consumed at a lower level of recursion."

Пример

5. CR, R, W в файлах и плане показывает число логических чтений на фазе выполнения и fetch данных. Не затрагивает фазу парсинга. Вывод этих чисел включается через
ALTER SYSTEM SET statistics_level=all(хотя это надо еще проверить)