Чтение планов выполнения в Oracle

Oracle использует основанный на SQL подход к созданию и отображению планов выполнения. При помощи SQL вы помещаете данные плана в таблицу, после чего можете просмотреть их, используя обычный SQL-запрос. Поначалу этот процесс может казаться неудобным, особенно если делать все вручную. SQL Server по запросу выводит описания планов выполнения на экран, но основанный на SQL подход Oracle, то есть запись плана в таблицу, обладает большей гибкостью, если вы хотите автоматизировать процесс одновременного анализа целых наборов планов выполнения.

Подготовка

Oracle помещает данные плана выполнения в таблицу, которая обычно называется PLAN TABLE. Если в схеме, которую вы используете для изучения планов выполнения, еще нет PLANTABLE, создайте такую таблицу. Можно создать соответствующую текущим данным таблицу PLAN TABLE при помощи сценария utlxplan.sql в каталоге rdbms/admin в ORACLE HOME. Если ORACLE HOME вам недоступна, то вполне подойдет PLAN TABLE, созданная следующим сценарием: CREATE TABLE PLAN TABLE(

Процесс, лежащий в основе отображения планов выполнения

Для создания и отображения планов выполнения в Oracle с минимальным вмешательством в работу других пользователей, которые также могут работать с таблицей плана, используется 4-шаговый процесс из утилиты SQL*Plus.

1. Удалите все строки из специальной таблицы Oracle для планов вьшолнения PLANTABLE в схеме, которую вы используете для создания планов выполнения. Создать план вьшолнения для оператора SQL можно только под именем пользователя, у которого есть права выполнять операторы SQL. Таким образом, обыч-

но вы создаете планы выполнения, когда подключены к той же схеме, в которой выполняется предназначенный для настройки SQL.

ВНИМАНИЕ

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

2. Создайте записи плана выполнения в PLANTABLE при помощи оператора SQL EXPLAIN PLAN FOR <НдСтрдивдемый опердтор>:.

3. Выведите на экран план выполнения при помощи подобного оператора:

SELECT LPADC .2*(LEVEL-1))CPERATICN OPTIONS DECCDE(CBJECT INSTANCE. NULL. CBJECT NAME.

TC CHAR(CBJECT INSTANCE)r* CBJECTJAME) PLAN FROM PLAN TABLE START WITH ID=0

CONNECT BY PRIOR ID - PARENT ID ORDER BY ID:

4. Очистите результаты ващей работы при помощи ROLLBACK:.

Выполним эту последовательность действий и проанализируем план выполнения для простого запроса:

SELECT Last Name. Flrst Name. Salary FROM Employees

WHERE Manager ID=137

ORDER BY Lastjame. FirstJame:

Далее вы видите реальное содержимое сеанса SQL*Plus для ручного определения плана выполнения нашего запроса: SQL> delete from plan tab1e;

О rows deleted.

SQL> EXPLAIN PLAN FOR SELECT Lastjame. First Jame. Salary FROM Employees

2 WHERE Manager ID=137

3 ORDER BY Lastjame. First Jame:

Explained.

SQL> SELECT LPADC .2*(LEVEL-1))OPERATION OPTIONS

2 DECODE(OBJECT INSTANCE. NULL. OBJECT JAME.

3 TO CHAR(OBJECT INSTANCE)* OBJECT JAME) PLAN

4 FROM PLAN TABLE

5 START WITH 10=0

6 CONNECT BY PRIOR ID = PARENT IO

7 ORDER BY ID:

PLAN

SELECT STATEMENT SORT ORDER BY TABLE ACCESS BY INDEX ROWID 1*EMPL0YEES

INDEX RANGE SCAN EMPLOYEES MANAGER ID 4 rows selected. SQL> rollback: Rollback complete.

Здесь показан план выполнения, который находит диапазон индекса (по индексу Employees Manager ID), включающий данные о сотрудниках, подчиненных менеджеру с идентификатором 137. Сканирование этого диапазона индекса (см. последнюю строку выхода перед отчетом 4 rows selected ) приводит к получению списка идентификаторов строк, указывающих на определенные строки в определенных блоках таблицы Employees. Для каждого из этих идентификаторов строк Oracle выполняет операцию логического ввода-вывода и, если необходимо, физического ввода-вывода в соответствующем блоке таблицы, где и находит нужную указанную строку. После операций считывания из таблицы Oracle сортирует строки в порядке возрастания по столбцам, указанным после выражения ORDER BY.

Практический процесс отображения планов выполнения

Для новичка процесс отображения планов выполнения в Oracle выглядит неудобным, но вы можете автоматизировать основные шаги этого процесса, написав небольшой и простой сценарий. Если вы работаете в Unix, создайте следующие файлы:

-- File called head.sql: set pagesize 999 set feedback off

DELETE FROM PLANJABLE WHERE STATEMENT ID = <Your name>: EXPLAIN PLAN SET STATEMENT ID = <Your name> FOR

-- File called tail.sql:

SELECT LPADC .2*(LEVEL-1))OPERATION OPTIONS 11 DECODE(OBJECT INSTANCE. NULL. OBJECT NAME.

TO CHAR(OBJECT INSTANCE) Г* 11 OBJECTJAME) PLAN

FROM PLANTABLE

START UITH 10=0 AND STATEMENTID = <Your nanie>

CONNECT BY PRIOR ID = PARENTID AND STATEMENT ID = <Your name>

ORDER BY ID:

ROLLBACK:

-- File called ex.sql:

!cat head.sql tmp.sql tail.sql > tnip2.sql

spool tmp.out

@trnp2

spool off

После этого вы сможете последовательно обрабатывать планы выполнения. Сохраните рассматриваемый SQL-код (вместе с завершающей символом точки с запятой (;)) в файле tmp.sql в любом текстовом редакторе в одном окне. В другом окне запустите сеанс SQL*Plus из каталога, в котором находятся файлы head.sql, tail .sql, ex.sql и tmp.sql. Создавайте новые планы выполнения для текущей версии tmp. sql (после того, как сохраните этот файл!), вводя команду @ех в строке с при-