Базы данных: Oracle

или Как изменить план запроса, не имея доступа к исходникам.

Author : Igor Lemeshko (lem_i@mail.ru )

Newsgroups: relcom.comp.dbms.oracle

Для реализации сабжа будет использоваться Oracle 8.1.7(работает с 8.1.*) и outline.

Кому-то это будет скучно и не интересно, но кому-то будет очень полезно, я знаю, что эта фишка многим не известна. Очень часто надо настроить тормознутый запрос, но, к сожалению доступа и исходникам нет (основная масса коммерческого софта).

Outline предназначен для фиксации плана запроса, т.е. если тот же самый запрос выполняется повторно, и он НЕ найден в шаред-пуле, то при включенном режиме использования OUTLINE сервер сначала будет искать запрос в dba_outlines, и строить план в соответствии с найденной информацией (Оракл использует хинты для записи плана, можно посмотреть в ol$hints для каждого шага плана).

An outline consists primarily of a set of hints that is equivalent to the optimizer's results for the execution plan generation of a particular SQL statement. (хинты=план, точнее outline-хинты, там ведь еще есть поле OL$HINTS.stage#.)

Таким образом, при использовании outline, согласно документации, план не может поменятся, если не менялась структура базы. Более того, утверждается, что даже переход на новую версию, не поменяет план(трудно поверить и проверить).

Раз сервер берет информацию для построения плана из otline'овых таблиц, значит есть возможность подменить эту информацию. Хотя документация не поощряет (You cannot modify an outline. The OL$ and OL$HINTS... ) изменение системных outline'овых таблиц, мы увидим, что все проходит без проблем.

Демонстрация такая - выполняю наш грешный запрос для поиска первых 10 (1) максимальных значений без хинтов (запрос всегда выдает правильный результат, независимо от наличия/отсутствия хинтов, просто быстрее или медленнее).

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

SELECT * FROM ( select empno from scott.emp1 e

where e.empno > 0

order by e.empno desc

)

WHERE rownum <=1

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 COUNT (STOPKEY)

2 1 VIEW

3 2 SORT (ORDER BY STOPKEY)

4 3 INDEX (RANGE SCAN) OF 'EMPNO_IDX' (NON-UNIQUE)

По такому плану/ :

1 0 COUNT (STOPKEY)

2 1 VIEW (Cost=2 Card=21927 Bytes=285051)

3 2 INDEX (RANGE SCAN DESCENDING) OF 'EMPNO_IDX' (NON-UNIQU

E) (Cost=2 Card=21927 Bytes=285051)

Строим outline для этого медленного запроса с именем SLOWSQL. Затем выполняем этот же запрос с хинтами, и тоже создаем outline - HINTSQL.

Далее в таблице ol$hints подменяем строки медленного плана на "хинтованый" план. Т.е. SLOWPLAN даем хинты от "быстрого" плана. Все работает! Бесхинтовый запрос начинает работать по НОВОМУ плану, как-будто этому запросу подставили хинт.

Включаем режим:

ALTER SYSTEM (или session) SET USE_STORED_OUTLINES = TRUE

Создаем "безхинтовый" outline:

CREATE or REPLACE OUTLINE SLOWSQL

on

SELECT * FROM ( select empno from scott.emp1 e where e.empno > 0 order by e.empno desc )

WHERE rownum <=10 --(1)

ЧИСТИМ shared_pool, это ОЧЕНЬ важно, без этого ничего не увидим, год назад я пробовал это проделать и не получилось, по-моему забывал чистить shared_pool.

alter system flush shared_pool

Выполняем наш запрос, и наслаждаемся тормозами (SORT) :

SELECT * FROM ( select empno from scott.emp1 e where e.empno > 0 order by e.empno desc )

WHERE rownum <=1

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 COUNT (STOPKEY)

2 1 VIEW

3 2 SORT (ORDER BY STOPKEY)

4 3 INDEX (RANGE SCAN) OF 'EMPNO_IDX' (NON-UNIQUE)

 Создаем "хинтованый"(богатый и могучий, блин, русский язык) outline:

CREATE or REPLACE OUTLINE HINTSQL

on

SELECT /*+ INDEX_DESC (e, EMPNO_IDX) */

FROM ( select empno from scott.emp1 e where e.empno > 0 order by e.empno desc )

WHERE rownum <=10 --(1)

У данного запроса план уже без SORT, просто выборка по индексу со стоп-ключом.

Смотрим, что мы на создавали, dba_outline.used - показывает использовался ли outline:

select * from outln.ol$hints -- интересная таблица

select * from dba_outlines where name in ('SLOWSQL', 'HINTSQL')

Заменяем в ol$hints планы:

UPDATE outln.ol$hints

SET ol_name=decode(ol_name,'SLOWSQL','HINTSQL', 'HINTSQL','SLOWSQL')

WHERE

ol_name IN ('SLOWSQL', 'HINTSQL' );

commit;

Чистим пулл (обязательно):

alter system flush shared_pool

И опять выполняем наш тормозной запрос:

SELECT *

FROM ( select empno

from scott.emp1 e

where e.empno > 0

order by e.empno desc

)

WHERE rownum <=1

Получаем план без SORT (ORDER BY STOPKEY), просто desc-выборку по индексу! К чему и стремились.

Циклически выполняя приведенный выше update, с последующей ЧИСТКОЙ шаредпула и повторным выполнением одного и того же запроса, получаем меняющийся план нашего "безхинтового" запроса. Можно также добавлять или удалять пробел - план меняется.

Второй запрос, с хинтами, при этом переодически (после update) работает по безхинтовому плану, вообще впечатляет.

Использовалась информация (в частности update) из главы Oracle High-Performance SQL Tuning:

http://www.oracle.com/oramag/webcolumns/2001/opress/index.html?ch13-1.html

Статья очень мне понравилась, особенно:

Remember, there are many DBAs who feel that for any SQL statement there exists only one optimal execution plan, and once located, it should never change. If you are one of these DBAs, then stored outlines can greatly aid your SQL tuning effort.

Только один оптимальный план. В большинстве случаев так оно и должно быть. Если кому интересно - исходные данные:

SQL> desc scott.emp1

Name Null? Type

----------------------------------------- -------- ----------------------------

EMPNO NOT NULL NUMBER(4)

ENAME VARCHAR2(10)

JOB VARCHAR2(9)

MGR NUMBER(4)

HIREDATE DATE

SAL NUMBER(7,2)

COMM NUMBER(7,2)

DEPTNO NUMBER(2)

EMPNO_IDX - неуникальный индекс на emp1.empno, в таблице около лимона строк, статистика НЕ собрана. Emp1 создан из scott.emp с помощьюю create as select.., с последующим многократным insert into emp1 select * from emp1.

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production

PL/SQL Release 8.1.7.3.0 - Production

CORE 8.1.7.0.0 Production

TNS for Linux: Version 8.1.7.3.0 - Production

NLSRTL Version 3.4.1.0.0 - Production

В документации указано, что должны быть установлены параметры(я ничего не менял):

* QUERY_REWRITE_ENABLED

* STAR_TRANSFORMATION_ENABLED

* OPTIMIZER_FEATURES_ENABLE

Запускаемый запрос должен в точности повторять сохраненный в outline, Версия Oracle и для запроса и для outline >= 8i.

В девятке есть Outline Editor(не смотрел, но интересно), позволяющий :

Outline Editor is an advanced Oracle9i application that allows the user to control the optimizer behavior by modifying the optimizer

mode, join order, or index usage without having to change the statement in the application code.

Но коммерческий софт в основном пока еще использует 8.1.*, и потому часто нужна автоматизация задач(т.е. и для девятки этот подход приемлем).

С уважением, Игорь Лемешко.