неповторяющихся значений при идентичных планах выполнения (10gR2).
463860.1 Higher Library Cache Latch contention in 10g than 9i, High Loaded_Versions Count for SQL In V$SQLAREA despite using bind variables, High Version Count with CURSOR_SHARING = SIMILAR or FORCE
*) Использование механизма cursor_sharing (независимо от значения параметра force или similar) интересно влияет на использование оптимизатором функциональных индексов (function based index), созданных с использованием функций с литералами (например, substr(vc1,4,2)). CBO не использует такие индексы, поскольку преобразует условия запроса для использования связанных переменных к виду
SUBSTR("VC1",:SYS_B_0,:SYS_B_1)=:SYS_B_2
Кроме очевидного метода решения этой проблемы (отключение механизма cursor_sharing с помощью параметра cursor_sharing=exact или подсказки CURSOR_SHARING_EXACT), по вышеприведённой ссылке Jonathan Lewis описывает рабочий метод использования function based индексов через «сокрытие» function-based выражений в обзор
create view v1
as select
...
substr(vc1,4,2) vc_short,
...
Использование CURSOR_SHARING = SIMILAR настоятельно не рекомендуется производителем, начиная с 11g будет исключено из доступных значений параметра в Oracle 12 —ANNOUNCEMENT: Deprecating the cursor_sharing = ‘SIMILAR’ setting [ID 1169017.1]
_disable_cursor_sharing
Доступен с 11.2.0.3
Позволяет форсировать создание новых курсоров (hard parse) для каждого выполнения запросов со связанными переменными
Как форсировать разбор SQL при каждом выполнении…
db_file_multiblock_read_count
максимально допустимое количество блоков Oracle, читаемых из БД при последовательном сканировании объектов (FULL TABLE SCAN, INDEX FAST FULL SCAN).
optimizer_dynamic_sampling
Параметр регулирует использование оптимизатором (CBO) механизма dynamic sampling.
«Идея механизма dynamic sampling — улучшить производительность сервера СУБД [в части времени выполнения запросов] с помощью более точной оценки избирательности условий запроса (predicate selectivity) и статистики таблиц и индексов [во времявыполнения запросов]. Под статистикой таблиц и индексов подразумевается количество блоков в таблицах и применяемых индексах, количество
строк таблицы (table cardinalities) и статистика столбцов, используемых в операциях соединения…
dynamic sampling можно использовать для:
•оценки избирательности условий запроса одиночной таблицы в случаях, когда собранная статистика не может быть использована или, возможно, приводит к значительным ошибкам в оценке избирательности.
•оценки статистики для таблиц и соответствующих индексов в случае отсутствия… [или] недостоверности»
«Уровни dynamic sampling [значения параметра OPTIMIZER_DYNAMIC_SAMPLING]:
•Уровень 0: Не использовать use dynamic sampling.
•Уровень 1: Оценивать таблицы без статистики (неанализированная) при выполнении условий: (1) в запросе есть по крайней мере одна таблица без статистики; (2) эта таблица в запросе соединяется с другой таблице или включена в подзапрос или non-mergeable view; (3) эта таблица не имеет индексов; (4) в этой таблице блоков больше, чем количество блоков, которое будет использоваться механизмом dynamic sampling для динамического анализа этой таблицы. Количество таких оценочных блоков по умолчанию (dynamic sampling blocks) 32 [определяется значением параметра _optimizer_dyn_smp_blks]
•Уровень 2: Применить механизм dynamic sampling ко всем неанализированным таблицам. Количество блоков для динамического анализа вдвое больше значения по умолчанию.
•Уровень 3: Механизм dynamic sampling примяется ко всем таблицам, удовлетворяющим критериям уровня 2 плюс ко всем таблицам, для которых стандартная оценка избирательности по условиям запроса (selectivity estimation) использует предположения, т.е. есть условия, для которых подходит
механизм dynamic sampling (potential dynamic sampling predicate). Используется количество оценочных (sampled) блоков по умолчанию. Для неанализированных таблиц оценивается вдвое больше блоков.
•Уровень 4: Механизм dynamic sampling примяется ко всем таблицам, удовлетворяющим критериям уровня 3 плюс ко всем таблицам, для которых в запросе присутствуют условия, относящиеся только к одной таблице (single-table predicates) и включающие условия для 2-х и более столбцов этой таблицы. Используется количество оценочных (sampled) блоков по умолчанию. Для неанализированных таблиц оценивается вдвое больше блоков.
•Уровни 5, 6, 7, 8, и 9: Механизм dynamic sampling примяется ко всем таблицам, удовлетворяющим критериям предыдущих уровней с использованием 2, 4, 8, 32, или 128 кратного количества оценочных (sampled) блоков по умолчанию соответственно.
•Уровень 10: Механизм dynamic sampling примяется ко всем таблицам, удовлетворяющим критериям уровня 9 с использованием всех блоков таблицы»
10. (7) Отключите использование динамической выборки, изменив значение параметра optimizer_dynamic_sampling для текущей сессии.
Проверьте внесённые изменения в представлении v$parameter.
11. Снова выполните трассировку (autotrace) запросов из скриптов literal9.sql и literal10.sql.
(8) Зафиксируйте и прокомментируйте результат
(сравните стоимость запроса, определённую оптимизатором и реальную статистику выполнения с предыдущими результатами трассировки).
literal9.sql
SELECT
COUNT(*),
MAX(empno)
FROM emp WHERE deptno =
9;
literal10.sql
SELECT
COUNT(*),
MAX(empno)
FROM emp WHERE deptno =
10;
12.
(9) Верните первоначальное значение параметра optimizer_dynamic_sampling.
13. С помощью процедуры SET_TABLE_STATS пакета DBMS_STATS назначьте неверные значения для параметров статистики по таблице EMP:
a. |
количество |
|
|
|
|
строк |
- |
10 |
|
b. количество блоков – 5 |
|
|
|
'EMP', |
|
|
|
|
|
EXEC |
dbms_stats.set_table_stats |
( |
'AGS' |
, |
numrows=>10,numblks=>5); |
|
|
||
14.(10) Проверьте, что статистика была изменена (см. п.7).
15.Снова выполните трассировку (autotrace) запросов из скриптов literal9.sql и literal10.sql.
(11) Зафиксируйте и прокомментируйте результат (в частности отметьте время выполнения, стоимость и кол-во логических чтений).
literal9.sql
SELECT
COUNT(*),
MAX(empno)
FROM emp WHERE deptno =
9;
literal10.sql
SELECT
COUNT(*),
MAX(empno)
FROM emp WHERE deptno =
10;