Большой архив статей, книг, документации по программированию, вебдизайну, компьютерной графике, сетям, операционным системам и многому другому
 
<Добавить в Избранное>    <Сделать стартовой>    <Реклама на сайте>    <Контакты>
  Главная Документация Программы Обои   Экспорт RSS E-Books
 
 

   Базы данных -> Oracle -> Использование OutLine


Использование OutLine В ORACLE 8.1.7

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

Author : Igor Lemeshko (lem_i@mail.ru )

Newsgroups: relcom.comp.dbms.oracle

Dear ALL,

Для реализации сабжа будет использоваться 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.*, и потому часто нужна автоматизация задач(т.е. и для девятки этот подход приемлем).

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



 

 
Интересное в сети
 
10 новых программ
CodeLobster PHP Edition 3.7.2
WinToFlash 0.7.0008
Free Video to Flash Converter 4.7.24
Total Commander v7.55
aTunes 2.0.1
Process Explorer v12.04
Backup42 v3.0
Predator 2.0.1
FastStone Image Viewer 4.1
Process Lasso 3.70.4
FastStone Image Viewer 4.0
Xion Audio Player 1.0.125
Notepad GNU v.2.2.8.7.7
K-Lite Codec Pack 5.3.0 Full


Наши сервисы
Рассылка новостей. Подпишитесь на рассылку сейчас и вы всегда будете в курсе последних событий в мире информационных технологий.
Новостные информеры. Поставьте наши информеры к себе и у вас на сайте появится дополнительный постоянно обновляемый раздел.
Добавление статей. Если вы являетесь автором статьи или обзора на тему ИТ присылайте материал нам, мы с удовольствием опубликуем его у себя на сайте.
Реклама на сайте. Размещая рекламу у нас, вы получите новых посетителей, которые могут стать вашими клиентами.
 
Это интересно
 

Copyright © CompDoc.Ru
При цитировании и перепечатке ссылка на www.compdoc.ru обязательна. Карта сайта.