Инструменты для настройки производительности SQL чрезвычайно важны. Разработчики могут использовать их для изучения хороших стратегий выполнения, а в производственной базе данных они оказывают неоценимую помощь при реактивной настройке. Эти инструменты могут давать хорошее представление об используемых запросами ресурсах. В их число входят такие утилиты, как EXPLAIN PLAN, Autotrace, SQL Trace и TKPROF.
Использование инструмента EXPLAIN PLAN
Утилита EXPLAIN PLAN помогает настраивать SQL за счет того, что позволяет просматривать план выполнения, выбираемый для SQL-оператора оптимизатором Oracle. Во время настройки SQL может возникать необходимость переписывать запросы и экспериментировать с подсказами для оптимизатора. Инструмент EXPLAIN PLAN просто замечательно подходит для таких экспериментов, потому что позволяет немедленно узнавать, как будет работать запрос при каждом изменении в коде. Поскольку эта утилита дает возможность видеть план выполнения без выполнения кода, она избавляет от необходимости запускать ненастроенный код для выяснения того, принесли ли изменения какую-то выгоду. Понимание работы EXPLAIN PLAN является важным в оценке производительности запросов. Она, по сути, приоткрывает окно в логику, которой пользуется оптимизатор Oracle при выборе планов выполнения.
Вывод утилиты EXPLAIN PLAN помещается в таблицу, обычно называемую PLAN_TABLE, к которой затем могут направляться запросы для определения плана выполнения операторов. Кроме того, получить план выполнения без лишних действий также позволяют и соответствующие инструменты с графическим интерфейсом наподобие OEM или TOAD. В частности, в OEM просматривать вывод EXPLAIN PLAN можно в диаграммах Top Sessions и Top SQL.
Анализ вывода EXPLAIN PLAN позволяет видеть шаги, которые будет предпринимать CBO для выполнения данного SQL-оператора. Утилита EXPLAIN PLAN четко показывает, будет ли оптимизатор, например, использовать индекс. Вдобавок она сообщает о порядке, в котором будут соединяться таблицы, и помогает оценить производительность запросов. Если более конкретно, то в выводе этой утилиты отображается следующая информация:
- таблицы, используемые в запросе, и порядок, в котором к ним будет получен доступ;
- операции, которые будут выполняться над выводом каждого этапа плана выполнения (например, операции сортировки и агрегирования);
- конкретные методы доступа и соединения, которые будут использоваться для каждой упоминаемой в SQL-операторе таблицы;
- стоимость каждой операции.
Oracle создает PLAN_TABLE как глобальную временную таблицу, чтобы все пользователи в базе данных могли использовать ее для сохранения своего вывода EXPLAIN PLAN. Однако при желании можно создавать и локальную таблицу PLAN_TABLE в своей собственной схеме, запустив сценарий utlxplan.sql, который находится в каталоге $ORACLE_HOME/rdbms/admin. Этот сценарий, помимо прочего, создает таблицу плана, предназначенную для сохранения вывода утилиты EXPLAIN PLAN с целью его последующего просмотра. При желании эту таблицу можно переименовать. Ниже показано, как создать таблицу плана для использования утилиты EXPLAIN PLAN:
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql Table created. SQL>
Создание вывода EXPLAIN PLAN
Сгенерировать вывод EXPLAIN PLAN для любого оператора языка манипулирования данными SQL можно примерно так, как показано в листинге 2.
SQL> EXPLAIN PLAN 2 SET statement_id = 'test1' 3 INTO plan_table 4 FOR select p.product_id,i.quantity_on_hand 5 FROM oe.inventories i, 6 oe.product_descriptions p, 7 oe.warehouses w 8 WHERE p.product_id=i.product_id 9 AND i.quantity_on_hand > 250 10 AND w.warehouse_id = i.warehouse_id; Explained. SQL>
Получение вывода EXPLAIN PLAN
Просто так выбирать столбцы из таблицы PLAN_TABLE нельзя из-за иерархической природы взаимоотношений между ними. В листинге 3 приведен код, который можно использовать для того, чтобы вывод EXPLAIN PLAN распечатывался в читабельном виде и ясно показывал, как выглядит план выполнения оператора.
SQL> SELECT lpad(' ',level-1)||operation||' '||options||' '||
2 object_name "Plan"
3 FROM plan_table
4 CONNECT BY prior id = parent_id
5 AND prior statement_id = statement_id
6 START WITH id = 0 AND statement_id = '&1'
7 ORDER BY id;
Enter value for 1: test1
old 6: START WITH id = 0 AND statement_id = '&1'
new 6: START WITH id = 0 AND statement_id = 'test1'
Plan
--------------------------------------------------------
SELECT STATEMENT
HASH JOIN
NESTED LOOPS
TABLE ACCESS FULL INVENTORIES
INDEX UNIQUE SCAN WAREHOUSES_PK
INDEX FAST FULL SCAN PRD_DESC_PK
6 rows selected.
SQL>
Другие способы отображения результатов EXPLAIN PLAN
Для отображения вывода EXPLAIN PLAN в удобном для восприятия формате можно использовать пакет DBMS_XPLAN, а именно — одну из поставляемых в нем табличных функций. В частности, табличная функция DISPLAY из этого пакета позволяет отображать самый недавний вывод EXPLAIN PLAN, а табличная функция DISPLAY_AWR — вывод плана выполнения SQL-оператора из AWR. Ниже рассматривается пример применения пакета DBMS_XPLAN для получения вывода самого недавнего оператора EXPLAIN PLAN.
Сначала потребуется создать сам оператор EXPLAIN PLAN для SQL-оператора:
SQL> EXPLAIN PLAN FOR 2 SELECT * FROM persons 3 WHERE PERSONS.last_name LIKE '%ALAPATI%' 4 AND created_date < sysdate -30; Explained. SQL>
Затем необходимо задать в SQL*Plus надлежащий размер строк и страниц:
SQL> SET LINESIZE 130 SQL> SET PAGESIZE 0
Теперь можно отобразить вывод EXPLAIN PLAN:
SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY);
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 3 (0) | 00:00:01 |
|* 1 | TABLE ACCESS FULL | PERSONS | 1 | 37 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id) :
----------------------------------------------------
- filter ("ENAME" LIKE '%ALAPATI%' AND "CREATED_DATE">SYSDATE@!-30)
13 rows selected.
SQL>
Для получения удобно отформатированного вывода также можно использовать и поставляемый Oracle сценарий utlxpls.sql. Этот сценарий служит альтернативой непосредственному применению пакета DBMS_XPLAN и основан на этом же пакете. Он находится, как уже упоминалось ранее, в каталоге $ORACLE_HOME/rdbms/admin и подразумевает использование пакета DBMS_XPLAN для отображения вывода самого недавнего оператора EXPLAIN PLAN в базе данных. Конечно, прежде чем применять этот сценарий, нужно удостовериться в том, что таблица PLAN_TABLE уже существует. Запускается utlxpls.sql следующим образом:
$ @$ORACLE_HOME/rdbms/admin/utlxpls.sql
Вывод сценария utlxpls.sql в данном примере будет полностью идентичен выводу DBMS_XPLAN.DISPLAY, который был показан выше.
Интерпретирование вывода EXPLAIN PLAN
Поначалу интерпретировать вывод EXPLAIN PLAN немного сложно, поэтому не помешает запомнить перечисленные ниже простые принципы.
- Каждый шаг в плане предусматривает возврат вывода в виде набора строк родительскому шагу.
- Начинать интерпретировать план лучше снаружи со строки, содержащей наибольшее количество отступов.
- Если две операции отображаются на одном уровне в плане количества отступов, первой следует анализировать ту, что находится выше.
- Нумерация шагов в плане сбивает с толку. Начинать интерпретировать вывод EXPLAIN PLAN нужно изнутри. То есть первой следует интерпретировать ту операцию, которая сопровождается наибольшим количеством отступов.
В примере, показанном ранее в листинге 3 (где отражающий план вывод идет сразу же после кода), Oracle использует таблицу INVENTORIES в качестве управляющей таблицы и применяет следующий путь выполнения:
SELECT STATEMENT HASH JOIN NESTED LOOPS TABLE ACCESS FULL INVENTORIES INDEX UNIQUE SCAN WAREHOUSES_PK INDEX FAST FULL SCAN PRD_DESC_PK
Этот вывод говорит о следующем.
- Сначала Oracle будет выполнять полное сканирование таблицы
INVENTORIES. - Далее Oracle будет выполнять уникальное сканирование индекса таблицы
WAREHOUSESс использованием ее индекса первичного ключа. - Затем Oracle будет выполнять операцию вложенного цикла для соединения строк из шагов 1 и 2.
- Потом Oracle будет выполнять быстрое полное сканирование индекса таблицы
product_descriptionс использованием ее первичного ключаPRD_DESC_PK. - И, наконец, на последнем шаге Oracle будет выполнять хеш-соединение набора, полученного на шаге 3, и строк, полученных в результате полного сканирования индекса на шаге 4.
Используя вывод EXPLAIN PLAN, можно быстро определять, почему некоторые из запросов занимают больше времени, чем ожидалось. Обладая такими знаниями, можно легко настраивать запрос до тех пор, пока не будет достигнут приемлемый порог по производительности. Замечательным в утилите EXPLAIN PLAN является то, что в случае ее применения никогда не требуется выполнять ни одного оператора в самой базе данных для отслеживания плана его выполнения. В следующем разделе предлагаются еще некоторые примеры использования этой утилиты.
Другие примеры планов
В этом разделе приводится еще несколько примеров, показывающих, как интерпретировать различные планы выполнения, получаемые с помощью утилиты EXPLAIN PLAN. В первом примере предлагается посмотреть, что будет происходить в случае использования функции в отношении индексированного столбца. Oracle будет полностью игнорировать индекс! Как не трудно заметить, оптимизатор может делать ошибки. Хорошие программисты должны помочь оптимизатору понимать подобные вещи правильно за счет применения методов наподобие надлежащего индексирования таблиц, подсказок для оптимизатора и т.д.
SQL> EXPLAIN PLAN set statement_id = 'example_plan1' 2 FOR 3 SELECT last_name FROM hr.employees 4 WHERE upper(last_name) = 'FAY'; Explained. SQL> example_plan1 ----------------------------- SELECT STATEMENT TABLE ACCESS FULL EMPLOYEES SQL>
В следующем примере показан запрос, похожий на предыдущий, но только не предусматривающий выполнения функции upper в отношении столбца last_name. На этот раз Oracle будет использовать индекс для столбца last_name:
SQL> EXPLAIN PLAN SET statement_id = 'example_plan1' 2 FOR 3 SELECT last_name FROM hr.employees 4* WHERE last_name='FAY'; Explained. SQL> example_plan1 ------------------------------ SELECT STATEMENT INDEX RANGE SCAN EMP_NAME_IX SQL>
В третьем примере для извлечения результатов запроса осуществляется соединение двух таблиц (customers и orders):
SQL> EXPLAIN PLAN SET statement_id 'newplan1' 2 FOR 3 SELECT o.order_id, 4 o.order_total, 5 c.account_mgr_id 6 FROM customers c, 7 orders o 8 WHERE o.customer_id=c.customer_id 9 AND o.order_date > '01-JUL-05' Explained. SQL>
В листинге 4 показано, как будет выглядеть вывод EXPLAIN PLAN в таком случае.
SQL> SELECT lpad(' ',level-1)||operation||' '||options||' '||
2 object_name "newplan"
3 FROM plan_table
4 CONNECT BY prior id = parent_id
5 AND prior statement_id = statement_id
6 START WITH id = 0 AND statement_id = '&1'
7* ORDER BY id;
Enter value for 1: newplan1
old 6: START WITH id = 0 AND statement_id = '&1'
new 6: START WITH id = 0 AND statement_id = 'newplan1'
newplan
SELECT STATEMENT
HASH JOIN /* шаг 4 */
TABLE ACCESS FULL CUSTOMERS /* шаг 2 */
TABLE ACCESS BY INDEX ROWID ORDERS /* шаг 3 */
INDEX RANGE SCAN ORD_ORDER_DATE_IX /* шаг 1 */
Elapsed: 00:00:00.01
SQL>
Как видно в этом листинге, на шаге 1 для удовлетворения данного запроса сначала будет выполняться сканирование диапазона по индексу для таблицы orders с использованием индекса ORD_ORDER_DATE_IX. Зачем нужно выполнять такое сканирование? Из-за того, что данный индекс не является уникальным, в нем содержится множество строк с одинаковыми значениями данных, поэтому оптимизатору придется сканировать эти многочисленные строки для получения только тех данных, которые представляют для него интерес. Например, если индексированный столбец является первичным ключом, он будет уникальным по определению, и тогда в EXPLAIN PLAN будет отображаться обозначение UNIQUE SCAN.
На шаге 2 к таблице customers будет получаться доступ через операцию полного сканирования таблицы, потому что столбец account_manager_id в этой таблице, который являются частью конструкции WHERE, не проиндексирован.
На шаге 3 будет осуществляться доступ к таблице orders через операцию INDEX ROWID с использованием того значения ROWID, которое было получено на предыдущем шаге. В результате из этой таблицы будут возвращаться значения столбцов order_id, customer_id и order_total, соответствующие указанной дате.
На шаге 4 строки, полученные из таблицы orders на шаге 3, будут соединяться со строками, полученными из таблицы customers на шаге 2, на основании соблюдения условия WHERE o.customer_id=c.customer_id.
Как видно по предыдущим примерам, утилита EXPLAIN PLAN позволяет получать четкое представление об используемых оптимизатором методах доступа, и делать это без выполнения самого запроса. Зачастую она помогает оперативно узнать, почему SQL-код работает медленно. Вывод EXPLAIN PLAN может позволить определить, насколько избирательными являются индексы, и экспериментировать с быстрым внесением соответствующих изменений в код.
Использование утилиты Autotrace
Утилита Autotrace (Автотрассировка) позволяет получать вывод EXPLAIN PLAN автоматически при выполнении SQL-оператора в SQL*Plus. В случае входа в систему от имени пользователя SYS или SYSTEM привилегии, необходимые для использования этой утилиты, предоставляются автоматически.
Перед использованием утилиты Autotrace необходимо создать в своей схеме таблицу плана (PLAN_TABLE). Она будет применяться для всех последующих выполнений утилиты Autotrace. В случае отсутствия такой таблицы в схеме, при попытке запустить утилиту Autotrace будет появляться сообщение об ошибке:
SQL> SET AUTOTRACE ON SP2-0618: Cannot find the Session Identifier . Check PLUSTRACE role is enabled SP2-0611: Error enabling STATISTICS report SQL>
Создавать таблицу PLAN_TABLE можно как вручную с помощью оператора CREATE TABLE, вроде того, что показан в листинге 5, так и автоматически за счет выполнения сценария utlxplan.sql, как объяснялось ранее в этой статье блога.
SQL> CREATE TABLE PLAN_TABLE( 2 STATEMENT_ID VARCHAR2(30), TIMESTAMP DATE, 3 REMARKS VARCHAR2(80), OPERATION VARCHAR2(30), 4 OPTIONS VARCHAR2(30), OBJECT_NODE VARCHAR2(128), 5 OBJECT_OWNER VARCHAR2(30), OBJECT_NAME VARCHAR2(30), 6 OBJECT_INSTANCE NUMERIC, OBJECT_TYPE VARCHAR2(30), 7 OPTIMIZER VARCHAR2(255), SEARCH_COLUMNS NUMBER, 8 ID NUMERIC, PARENT_ID NUMERIC, 9 POSITION NUMERIC, COST NUMERIC, 10 CARDINALITY NUMERIC, BYTES NUMERIC, 11 OTHER_TAG VARCHAR2(255), PARTITION_START VARCHAR2(255), 12 PARTITION_STOP VARCHAR2(255), PARTITION_ID NUMERIC, 13 OTHER LONG, DISTRIBUTION VARCHAR2(30)); Table created. SQL>
Далее пользователь SYS или SYSTEM должен назначить роль PLUSTRACE, как показано ниже:
SQL> GRANT PLUSTRACE TO salapati; * ERROR at Line 1: ORA-1919: role 'PLUSTRACE' does not exist. роль 'PLUSTRACE' не существует.
Если роли PLUSTRACE еще не существует в базе данных, как в показанном выше примере, тогда пользователь SYS должен запустить сценарий plustrace.sql (см. листинг 6) для ее создания.
SQL> @ORACLE_HOME/sqlplus/admin/plustrce.sql SQL> DROP ROLE plustrace; drop role plustrace * ERROR at line 1: ORA-01919: role 'PLUSTRACE' does not exist SQL> CREATE ROLE plustrace; Role created. SQL> SQL> GRANT SELECT ON v_$sesstat TO plustrace; Grant succeeded. SQL> GRANT SELECT ON v_$statname TO plustrace; Grant succeeded. SQL> GRANT SELECT ON v_$mystat TO plustrace; Grant succeeded. SQL> GRANT plustrace TO dba WITH ADMIN OPTION; Grant succeeded. SQL>
Затем роль PLUSTRACE должна предоставляться тому пользователю, который желает использовать утилиту Autotrace:
SQL> GRANT plustrace TO salapati; Grant succeeded. SQL>
После этого данный пользователь может активизировать утилиту Autotrace и просматривать вывод EXPLAIN PLAN для любого запроса, который использует в сеансе. Утилита Autotrace может активизироваться с различными опциями.
SET AUTOTRACE ON EXPLAIN.Указывает, что требуется генерировать только план выполнения и не выполнять сам запрос.SET AUTOTRACE ON STATISTICS. Указывает, что требуется показывать только статистические данные по SQL-оператору.SET AUTOTRACE ON. Указывает, что требуется показывать как план выполнения, так и статистические данные по выполнению SQL-оператора.
Для всех SQL-операторов, выполняемых после активизации утилиты Autotrace, будут генерироваться планы выполнения (до тех пор, пока утилита Autotrace не будет отключена с помощью команды SET AUTOTRACE OFF), как показано в листинге 7.
SQL> SET AUTOTRACE ON; SQL> SELECT * FROM EMP; no rows selected Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=74) 1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 Bytes=74) Statistics 0 recursive calls (рекурсивных вызовов) 0 db block gets (операций получения блоков базы данных) 3 consistent gets (операции согласованного чтения) 0 physical reads (операций физического чтения) 0 redo size (данных повторного выполнения) 511 bytes sent via SQL*Net to client (байт отправлено через SQL*Net клиенту) 368 bytes received via SQL*Net from client (байт получено через SQL*Net от клиента) 1 SQL*Net roundtrips to/from client (операция кругового обмена данными с клиентом через SQL*Net) 0 sorts (memory) (операций сортировки (в памяти)) 0 sorts (disk) (операций сортировки (на диске)) 0 rows processed (строк обработано) SQL>
Как видно в этом листинге, после отображения плана выполнения для SQL-оператора утилита Autotrace отображает детали о количестве рекурсивных вызовов SQL, произошедших во время выполнения исходного оператора, количестве операций физического и логического чтения, количестве операций сортировки в памяти и на диске и количестве обработанных строк.
Далее предлагаются примеры оптимизации SQL-запросов с применением утилиты Autotrace. В этих примерах один и тот же запрос выполняется в отношении таблицы courses дважды: один раз без индекса, а другой — с индексом. После индексирования таблицы этот запрос выполняется перед ее анализом. Результаты говорят сами за себя.
В первом примере, вывод которого показан в листинге 8, тестовый запрос выполняется перед созданием индекса по таблице courses.
SQL> SET AUTOTRACE ON SQL> SELECT COUNT(*) FROM courses 2 WHERE course_subject='medicine' 3* AND course_title = 'fundamentals of human anatomy'; COUNT(*) 98304 Execution Plan ----------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'COURSES' Statistics ----------------------------------------------------- 0 recursive calls 0 db block gets 753 consistent gets 338 physical reads 0 redo size 381 bytes sent via SQL*Net to client 499 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL>
Как здесь видно, в запросе была использована операция полного сканирования таблицы по причине отсутствия у этой таблицы каких-либо индексов. Из-за этого всего было выполнено 338 операций физического чтения. Обратите внимание, что общее количество строк в таблице courses составляет 98 384. Из этого количества предметов по теме “медицина” оказалось 98 304. Это значит, что значения в таблице совершенно не распределяются равномерно среди предметов. Теперь давайте посмотрим, что произойдет в случае использования индекса.
В следующем примере выполняется аналогичный запрос, но с индексом. Однако никакие статистические данные ни по таблице, ни по индексу не собираются. Результаты создания индекса по таблице courses и выполнения в его отношении такого же запроса, получаются довольно интересными. Увидеть это можно в листинге 9.
SQL> CREATE INDEX title_idx ON courses (course_title); Index created. SQL> SELECT count(*) FROM courses 2 WHERE course_subject='medicine' 3 AND course_title = 'fundamentals of human anatomy'; COUNT(*) 98304 Execution Plan -------------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'COURSES' 3 2 INDEX (RANGE SCAN) OF 'TITLE_IDX' (NON-UNIQUE) Statistics -------------------------------------------------------------- 0 recursive calls 0 db block gets 1273 consistent gets 1249 physical reads 0 redo size 381 bytes sent via SQL*Net to client 499 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL>
После создания индекса количество операций физического чтения возросло с 338 до 1249! Вывод EXPLAIN PLAN показывает, что Oracle действительно использует индекс, поэтому следовало бы ожидать, что количество этих операций будет выглядеть меньше, чем в случае без индекса. Произошедшее здесь объясняется следующим образом: даже если у таблицы имеется индекс, это вовсе не означает, что его использование является хорошим вариантом во всех обстоятельствах. CBO всегда вычисляет наилучший способ для получения результатов запроса как с, так и без использования индекса. В данном случае запрос требует просмотра практически всех строк в таблице, поэтому применение индекса не является наилучшим путем. Однако никаких статистических данных ни по таблице, ни по индексу не собиралось, поэтому у Oracle не возможности узнать, как на самом деле распределяются данные в таблице courses. При отсутствии всяких статистических данных Oracle прибегает к старому подходу оптимизации по синтаксису. При оптимизации по синтаксису использование индекса имеет более низкий ранг и потому оказывается, что в данном случае оно является самым оптимальным вариантом. Давайте теперь посмотрим, как будут выглядеть результаты после анализа таблицы.
В третьем примере запрос с индексом выполняется после сбора статистических данных по таблице. Теперь у Oracle есть все необходимые статистические данные и потому на этот раз применяется оптимизатор CBO. Оптимизатор CBO принимает решение использовать индекс только в том случае, если стоимость применения индекса будет ниже стоимости полного сканирования таблицы. В данном случае принято решение не использовать индекс, потому что запросу придется считывать 98 304 из общих 98 384 строк. Oracle правильно решает выполнять вместо этого полное сканирование таблицы. Результаты показаны в листинге 10.
SQL> ANALYZE TABLE courses COMPUTE STATISTICS; Table analyzed. SQL> SELECT count(*) FROM courses 2 WHERE course_subject='medicine' 3 AND course_title = 'fundamentals of human anatomy'; COUNT(*) ----------- 98304 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=74 Card=1 Bytes=39) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'COURSES' (Cost=74 Card=24596 Bytes=959244) Statistics ---------------------------------------------------------- 290 recursive calls 0 db block gets 792 consistent gets 334 physical reads 0 redo size 381 bytes sent via SQL*Net to client 499 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 1 rows processed SQL>
В этом листинге первый элемент — recursive calls (рекурсивные вызовы) — отражает количество дополнительных операторов, которые Oracle приходится выполнять при обработке SQL-оператора пользователя. Например, такие рекурсивные вызовы (или рекурсивные SQL-операторы) приходится выполнять для выделения пространства или для выполнения запросов в таблицы словаря данных на диске. В данном примере Oracle пришлось сделать 290 подобных внутренних вызов.
Использование утилит SQL Trace и TKPROF
SQL Trace (Трассировка SQL) представляет собой утилиту Oracle, которая помогает отслеживать выполнение SQL-операторов, а TKPROF — еще одну утилиту Oracle, которая помогает преобразовывать генерируемые SQL Trace файлы трассировки в удобный для чтения формат. Если утилита EXPLAIN PLAN предоставляет ожидаемый план выполнения, то утилита SQL Trace выдает уже фактические результаты выполнения SQL-запроса. Иногда бывает невозможно идентифицировать точный код, скажем, генерируемых динамическим образом SQL-операторов. Файлы SQL Trace могут легко перехватывать такой код. Помимо всего прочего, SQL Trace позволяет отслеживать следующие переменные.
- Затраченное время ЦП и общее затраченное время.
- Количество операций синтаксического анализа и выполнения, произведенных для каждого SQL-оператора.
- Количество операций физического и логического чтения.
- План выполнения для всех SQL-операторов.
- Коэффициент попаданий в библиотечный кэш.
Совет. Если в приложении много SQL-кода генерируется динамически, утилита SQL Trace является идеальным средством для настройки SQL-операторов.
Хотя утилита EXPLAIN PLAN и важна для определения пути доступа, который будет использовать оптимизатор, SQL Trace предлагает массу чрезвычайно полезной информации о потреблении ресурсов и эффективности операторов. Она позволяет получать четкое представление о том, не подвергается ли оператор излишнему синтаксическому анализу. Показатели по количеству выполнений и выборки иллюстрируют степень его эффективности. Хорошо видно, сколько времени ЦП расходуется на обработку запросов и сколько операций ввода-вывода происходит на этапе их выполнения. Это помогает выявлять в приложении те SQL-операторы, которые отнимают больше всего ресурсов, и настраивать их. Вывод EXPLAIN PLAN, который является необязательной частью вывода SQL Trace, отражает количество строк, извлекаемых на отдельных шагах плана выполнения, и тем самым помогает выявлять шаги, на которых выполняется больше всего работы. Путем сравнения объема потребляемых ресурсов с количеством выбираемых строк можно легко определять, насколько продуктивным является конкретный оператор.
В следующих разделах показано, как использовать утилиту SQL Trace для сбора трассировочных данных и интерпретировать их с помощью утилиты TKPROF на примере простого SQL-оператора. Сначала рассказывается о настройке нескольких необходимых для этого параметров инициализации.
Установка параметров инициализации, касающихся трассировки
Сбор трассировочных статистических данных сказывается на производительности и поэтому Oracle не производит трассировку автоматически для всех сеансов. Трассировка представляет собой совершенно необязательный процесс, который активизируется на ограниченное количество времени для сбора метрических показателей по производительности критичных SQL-операторов. Чтобы процесс трассировки SQL происходил в Oracle правильно, сначала необходимо соответствующим образом установить четыре параметра инициализации и перезапустить базу данных после получения уверенности в том, что они сконфигурированы так, как надо. Три из этих параметров представляют собой динамические параметры, которые можно изменять на уровне сеанса.
Параметр STATISTICS_LEVEL
Параметр STATISTICS_LEVEL может принимать три значения. Значение этого параметра оказывает влияние на параметр TIMED_STATISTICS. Эту зависимость можно четко увидеть в следующем кратком описании.
- Если для параметра
STATISTICS_LEVELустанавливается значениеTYPICALилиALL, статистические данные по времени (TIMED_STATISTICS) собираются для базы данных автоматически. - Если для параметра
STATISTICS_LEVELустанавливается значениеBASIC, тогда для того, чтобы в базе данных осуществлялся сбор статистических данных, для параметра должно устанавливаться значениеTRUE. - Даже если для параметра
STATISTICS_LEVELустанавливается значениеTYPICALилиALL, сбор трассировочных данных в базе данных все равно может предотвращаться за счет установки для параметраTIMED_STATISTICSзначенияFALSEс помощью оператораALTER SESSION.
Параметр TIMED_STATISTICS
Для параметра TIMED_STATISTICS по умолчанию устанавливается значение FALSE, если для параметра STATISTICS_LEVEL установлено значение BASIC. В подобном случае для сбора статистических данных по производительности, наподобие показателей по времени ЦП и времени выполнения, для параметра TIMED_STATISTICS должно устанавливаться значение TRUE, либо за счет редактирования файла init.ora или SPFILE, либо путем применения оператора ALTER SYSTEM SET TIMED_STATISTICS=TRUE для активизации сбора статистики по времени на уровне экземпляра. Делать это на уровне сеанса тоже можно с помощью следующего оператора ALTER SESSION:
SQL> ALTER SESSION SET timed_statistics = true; Session altered. SQL>
Параметр USER_DUMP_DEST
Параметр USER_DUMP_DEST отражает каталог на сервере, в который будут отправляться файлы SQL Trace. По умолчанию для сбрасывания файлов трассировки SQL используется каталог $ORACLE_HOME/admin/имя_базы_данных/udump. Если необходимо, чтобы доступ к этому каталогу имели не только администраторы баз данных, его разрешения должны допускать чтение другими пользователями. В качестве альтернативного варианта, для позволения другим пользователям читать трассировочные файлы в системах UNIX можно установить параметр TRACE_FILES_PUBLIC=TRUE. В целевом каталоге, на который указывает параметр USER_DUMP_DEST, должно быть достаточно места для размещения больших трассировочных файлов. Параметр USER_DUMP_DEST является динамическим, поэтому его также можно изменять и с помощью команды ALTER SYSTEM, как показано ниже:
SQL> ALTER SYSTEM SET user_dump_dest='c:\oraclent\oradata'; System altered. SQL>
На заметку! В Oracle Database 11g в случае установки нового параметра инициализации DIAGNSOTIC_DEST база данных игнорирует значение параметра USER_DUMP_DEST. В таком случае каталог, указываемый в DIAGNOSTIC_DEST, задает то место, в котором база данных будет размещать трассировочные файлы.
Параметр MAX_DUMP_FILE_SIZE
Некоторые процессы трассировки очень быстро могут приводить к созданию больших файлов трассировки, поэтому нужно обязательно проверять, что для параметра инициализации MAX_DUMP_FILE_SIZE выбрано достаточно высокое значение. Размер, используемый в этом параметре по умолчанию, может оказаться слишком маленьким для некоторых трассировок. В случае заполнения файла дампа процесс трассировки не будет завершаться, но информация в этом файле будет усекаться.
Активизация механизма трассировки SQL
Чтобы использовать утилиту SQL Trace и TKPROF, сначала потребуется активизировать механизм трассировки. Делать это можно как на уровне сеанса с помощью оператора ALTER SESSION или пакета DBMS_SESSION, так и на уровне всего экземпляра, либо включив в файл init.ora или SPFILE строку SQL_TRACE=TRUE, либо установив SQL_TRACE в TRUE в команде ALTER SYSTEM. Активизировать механизм трассировки на уровне всего экземпляра не рекомендуется, поскольку это ведет к генерации огромного объема трассировочной информации, большая часть которой совершенно не нужна для достижения поставленной цели. Ниже приведен пример включения механизма трассировки из сеанса с помощью оператора ALTER SYSTEM:
SQL> ALTER SESSION SET sql_trace=true; Session altered. SQL>
А вот пример установки SQL_TRACE в TRUE с помощью пакета DBMS_SESSION:
SQL> EXECUTE sys.dbms_session.set_sql_trace(true); PL/SQL procedure successfully completed. SQL>
Часто пользователи просят администратора баз данных помочь со сбором трассировочных данных по их SQL-операторам.
Для активизации механизма трассировки в сеансе другого пользователя можно воспользоваться процедурой DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION. Важно обратить внимание на то, что применение пакета DBMS_SYSTEM никогда на самом деле не приветствовалось Oracle. Рекомендуемым методом для выполнения трассировки сеансов является использование пакета DBMS_MONITOR. Какой бы метод не применялся, после запуска процесса трассировки в сеансе все выполняемые в нем операторы подвергаются трассировке до тех пор, пока не механизм трассировки не будет отключен с помощью оператора ALTER SESSION или пакета DBMS_SESSION (аналогично тому, как показывались выше, но только со значением false вместо true), или пока пользователь не выйдет из сеанса, в случае чего процесс трассировки останавливается для этого пользователя автоматически.
Интерпретирование трассировочных файлов с помощью TKPROF
После включения механизма трассировки для сеанса любой выполняемый во время этого сеанса SQL-оператор подвергается процессу трассировки. Вывод этого процесса сохраняется в виде файла в каталоге (udaump), на который указывает параметр USER_DUMP_DEST в файле init.ora или SPFILE. Имя файла имеет формат имя_базы_данных_ora_nnnnn.trc, где на месте nnnnn обычно идет четырех- или пятизначное число. Например, демонстрационный трассировочный файл в нашем примере называется pasx_ora_16340.trc. Если заглянуть в целевой каталог дампа пользователя сразу же после завершения сеанса трассировки, то самый последний файл обычно и будет только что созданным файлом трассировочных данных сеанса.
Отличать трассировочный файл, созданный в результате выполнения SQL Trace, от остальных файлов в каталоге дампа можно по его размеру: такие трассировочные файлы обычно имеют гораздо больший размер по сравнению с остальными файлами в каталоге. Эти трассировочные файлы являются подробными и сложными. К счастью, есть легко запускаемая утилита TKPROF, которая умеет преобразовывать вывод таких файлов в удобный для чтения формат. Она принимает эти файлы в качестве входных данных вместе с несколькими другими параметрами.
В табл. 1 показаны основные параметры, которые можно передавать утилите TKPROF для получения желаемого формата. Введя в командной строке команду tkprof, можно получить список всех допустимых параметров утилиты TKPROF.
| Параметр | Описание |
FILENAME | Указывает входной трассировочный файл, сгенерированный утилитой SQL Trace. |
EXPLAIN | Задает вывод EXPLAIN PLAN для SQL-операторов. |
RECORD | Создает SQL-сценарий со всеми не рекурсивными SQL-операторами. |
WAITS | Создает сводку событий ожидания. |
SORT | Представляет данные сортировки на основании одного или более элементов, наподобие PRSCPU (время ЦП, затраченное на выполнение синтаксического анализа), PRSELA (общее количество времени, затраченное на выполнение синтаксического анализа) и т.д. |
TABLE | Указывает имя для таблиц, в которые утилита TKPROF будет временно помещать планы выполнения. |
SYS | Включает и отключает отображение списка SQL-операторов, выполнявшихся пользователем SYS. |
PRINT | Отображает только определенное количество SQL-операторов, а не все. |
INSERT | Создает сценарий, сохраняющий трассировочную информацию в базе данных. |
Давайте проследим сеанс пользователя, выполняющего два оператора SELECT, один из которых предусматривает использование таблиц с индексами, а другой — таблиц без индексов. В этом примере применяется только несколько параметров и выбирается вариант запуска TKPROF с принятыми по умолчанию опциями сортировки. Первым параметром является имя файла вывода SQL Trace, а вторым — имя файла, куда должен помещаться уже отформатированный TKPROF вывод. Кроме того, указывается, что никакой анализ операторов, выполняемых пользователем SYS, проводить не требуется, а также что помимо прочих статистических данных для оператора должен также отображаться вывод EXPLAIN PLAN.
Совет. Введя tkprof в приглашении операционной системы, можно быстро получить справку по использованию утилиты TKPROF.
$ tkprof finance_ora_16340.trc test.txt sys=no explain=y TKPROF: Release 11.1.0.6.0 - Production on Mon Apr 28 12:49:38 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. $
Теперь вывод SQL Trace будет содержаться в файле test.txt, уже в замечательно преобразованном утилитой TKPROF формате.
Изучение файла с отформатированным выводом
В листинге 11 показана верхняя часть файла test.txt, в которой объясняются ключевые термины, используемые утилитой.
TKPROF: Release 11.1.0.6.0 - Production on Mon Apr 28 12:49:38 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Trace file: finance_ora_16340.trc
Sort options: default
******************************************************************************
count = number of times OCI procedure was executed
количество раз, которое была выполнена процедура OCI
cpu = cpu time in seconds executing
время ЦП, затраченное на выполнение (в секундах)
elapsed = elapsed time in seconds executing
общее время, затраченное на выполнение (в секундах)
disk = number of physical reads of buffers from disk
количество физических операций чтения буферов с диска
query = number of buffers gotten for consistent read
количество буферов, полученных для последовательного чтения
current = number of buffers gotten in current mode (usually for update)
количество буферов, полученных в текущем режиме (обычно для обновления)
rows = number of rows processed by the fetch or execute call
количество строк, обработанных во время выборки или выполнения
******************************************************************************
В каждом отчете TKPROF для каждого SQL-оператора, выполнявшегося во время отслеживавшегося сеанса пользователя, отображается следующая информация:
- Сам SQL-оператор.
- Количество вызовов
parse,executeиfetch(каковыми сопровождаются операторыSELECT). - Количество обработанных строк.
- Число потраченных секунд ЦП.
- Количество произведенных операций ввода-вывода.
- Количество непопаданий в библиотечный кэш.
- План выполнения (каковой является необязательным).
- Перечень операций с источниками строк.
- Сводный отчет, анализирующий, сколько похожих и сколько различающихся операторов было обнаружено в трассировочном файле.
Давайте теперь проанализируем отформатированный вывод, созданный утилитой TKPROF. В листинге 12 ниже показаны некоторые части вывода TKPROF, отражающие количество вызовов parse, execute и fetch.
SQL> select e.last_name,e.first_name,d.department_name from teste e,testd d where e.department_id=d.department_id; 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 17322 1.82 1.85 3 136 5 259806 ------- ------ ----- ------- ----- ------- -------- -------- total 17324 1.82 1.85 3 136 5 259806 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 53
В листинге 12:
- в столбце
cpuотображается количество затраченного времени ЦП (в секундах); - в столбце
elapsed— общее количество затраченного времени (в секундах); - в столбце
disk— общее количество операций физического чтения; - в столбце
query— количество последовательных операций чтения буферов; - в столбце
current— количество операций чтения блоков базы данных; - в столбце
rows— общее количество обработанных строк для вызова каждого типа.
Следовательно, по листингу 12 можно сделать такие выводы.
- Показанный ранее SQL-оператор один раз подвергался синтаксическому анализу, а это значит, что никакая подходящая проанализированная версия не была доступна в разделяемом пуле перед его выполнением. Строка
Parseпоказывает, что эта операция заняла менее 0,01 секунды. Обратите внимание, что отсутствие операций дискового ввода-вывода и чтения буферов свидетельствует о том, что во время операции синтаксического анализа никаких непопаданий в кэш словаря данных не было. Если бы в строкеParseотображалось более высокое значение, это было бы признаком не использования переменных связывания. - Оператор выполнялся один раз, и его выполнение заняло менее 0,01 секунды. Опять-таки, ни операций ввода-вывода, ни операций чтения буфера на этом этапе выполнения не было.
- На получение результатов оператора
SELECTпотребовалось гораздо больше 0,01 секунды. СтрокаFetchраскрывает причину: она показывает, что операция выполнялась 17 324 раз и заняла 1,82 секунды времени ЦП. - Операция
Fetchвыполнялась 17 324 раз и привела к извлечению 259 806 срок. Из-за того, что количество строк значительно превышает количество операций выборки, можно сделать вывод о том, что применялись операции выборки массивом (array fetch). - На этапе выборки имело место три операции физического чтения. Большая разница между временем ЦП и общим затраченным времени может заноситься на счет операций чтения с дисков. В данном случае количество операций физического ввода-вывода составляет всего лишь 3 и соответствует незначительной разнице между затраченным временем ЦП и общим количеством затраченного времени. Эта выборка потребовала выполнения 136 операций чтения буферов в согласованном режиме и только 5 операций считывания блоков базы данных.
- Использовался CBO, потому что в строке
Optimizer goalотображается значениеCHOOSE.
Ниже показана следующая часть вывода TKPROF, отражающая план выполнения, который был запрошен явным образом при вызове утилиты TKPROF. Обратите внимание на то, что вместо показателей по стоимости, которые возвращаются при использовании утилиты EXPLAIN PLAN, в данном случае возвращается информация о количестве строк вывода на каждом этапе выполнения.
Rows Row Source Operation ------- ----------------------- 259806 MERGE JOIN 1161 SORT JOIN 1161 TABLE ACCESS FULL TESTD 259806 SORT JOIN
И, наконец, в последней части вывода TKPROF отображается сводный отчет, сообщающий, сколько всего SQL-операторов было отслежено. Ниже показано, как выглядит эта часть:
Trace file: ORA02344.TRC
Trace file compatibility: 9.00.01
Sort options: default
2 sessions in trace file.
сеанса в трассировочном файле.
18 user SQL statements in trace file.
пользовательских SQL-операторов в трассировочном файле.
104 internal SQL statements in trace file.
внутренних SQL-оператора в трассировочном файле.
72 SQL statements in trace file.
SQL-оператора в трассировочном файле.
33 unique SQL statements in trace file.
уникальных SQL-оператора в трассировочном файле.
18182 lines in trace file.
строки в трассировочном файле.
Вывод TKPROF позволяет легко выявлять неэффективные SQL-операторы. TKPROF может упорядочивать SQL-операторы по затраченному на их выполнение времени и тем самым помогать определить, какие из SQL-операторов нуждаются в оптимизации.
Утилита SQL Trace является очень мощным инструментом для настройки SQL, поскольку выходит далеко за рамки той информации, которую предоставляет утилита EXPLAIN PLAN. Она предоставляет точные сведения о количестве различных вызовов, которые выполнялись к Oracle во время выполнения оператора, а также о том, как потреблялись ресурсы на различных этапах его выполнения.
На заметку! Сеансы отдельных пользователей еще очень удобно отслеживать с помощью интерфейса OEM Database Control.
