использование autotrace в sqlplus / using autotrace in sqlplus

this article in English

Самый простой и доступный способ понять в первом приближении, что из себя представляет тот или иной запрос -- это включить autotrace в sqlplus. При этом вы сможете увидеть план выполнения запроса и небольшую статистику по нему.

Хотя autotrace и не даст вам полной картины, но им очень легко пользоваться и в большинстве случаев его диагностики достаточно.

Чтобы начать использовать autotrace, DBA предварительно должен выполнить несколько шагов. Мы считаем, что эти шаги стоит выполнять при создании базы и, возможно, при установке патчей. Вот они:

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus system
SQL> @utlxplan
SQL> create public synonym plan_table for plan_table;
SQL> grant select, insert, update, delete on plan_table to public;
SQL> exit
$ cd $ORACLE_HOME/sqlplus/admin
$ sqlplus / as sysdba
SQL> @plustrce
SQL> grant plustrace to public;

Всё. Теперь каждый из разработчиков может в sqlplus выполнять команду 'set autotrace ...'

Вот какие ключи можно использовать в команде autotrace:
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

ON | OFF, понятное дело, включает/выключает autotrace

TRACEONLY не показывает результат выполнения запроса. Очень удобно, когда надо проанализировать запрос, выбирающий большое количество данных.

EXPLAIN показывает только план выполнения запроса

STATISTICS показывает только статистику выполнения запроса

Вот простейший пример использования autotrace:

SQL> set autot trace
SQL> select count(*) from dual;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     FAST DUAL (Cost=2 Card=1)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        393  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autot off

(отвлекаясь, заметим, что это Oracle 10g, поэтому, в плане выполнения фигурирует FAST DUAL и select не приводит ни к одному consistent gets/phisical reads)

Что касается минусов autotrace, то можно упомянуть парочку.

Во-первых, план выполнения получается путем выполнения команды 'explain plan...', что в редких (или просто очень редких) случаях может показывать отличающийся от реального план выполения запроса.
Во-вторых, в статистике не показываются данные по CPU и нет разбития на parse/exec/fetch стадии. Если такая точность нужна, то стоит прибегнуть к трассировке сессии.

Напоследок, обмолвимся о том, как же реализован autotrace в sqlplus. В принципе, очень просто -- sqlplus между делом посылает на сервер дополнительные запросы. К примеру, если у нас стоит autotrace on explain, то sqlplus после выполнения основного запроса, втихомолку посылает на сервер запрос вида 'explain plan for <наш запрос здесь>' и затем выбирает получившиеся данные из plan_table. Нам представляется, что более правильным здесь было бы делать выборку из v$sql_plan, но эта view появилась только с версии 9i, поэтому, наверное, разработчики решили оставить всё как есть (что, в общем-то, и не смертельно).

А вот если мы просим sqlplus выводить статистику, то сразу после такой просьбы (т.е. после получения команды типа 'set autotrace on stat'), он шлет на сервер такой запрос:

SELECT STATISTIC# S, NAME FROM SYS.V_$STATNAME WHERE NAME IN 
('recursive calls','db block gets','consistent gets','physical reads',
'redo size','bytes sent via SQL*Net to client',
'bytes received via SQL*Net from client',
'SQL*Net roundtrips to/from client','sorts (memory)','sorts (disk)') 
ORDER BY S

А перед тем как выполнить и непосредственно после выполнения трассируемого запроса, sqlplus делает выборки из v$sesstat для соответствующих statistic#. Причем, надо отметить, что для этих выборок sqlplus отдельно открывает другую сессию. Разумеется, это делается для того, чтобы этими выборками к v$sesstat не испортить статистику трассируемого запроса (своеобразная попытка 'забороть' принцип неопределенности Гейзенберга).