Оптимизация приложений

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

Способ номер 1, очень простой

Итак. Берем top и смотрим кто у нас использует процессор наиболее сильно. Если это процесс пользователя oracle надо бы убедиться что это не фоновый (background)  процесс (SMON, PMON, DBWR), а серверный. Потому что это совсем другая песня. Как их отличать  можно почитать здесь. Правило такое, если процесс потребляет более нескольких процентов (2-3%) cpu  на машине класса Sun 280-420 c одним процессором, то это потенциальный кандидат на тюнинг.

Дальше можно поступить просто. Берем  скрипт sinner.sql  Посмотрим что за запросы там идут. Но много это нам не скажет. Все равно придется смотреть план выполнения.  А это значит:

sh% sqlplus smap/smap

sql> set autotrace on;

sql> Ваш запрос здесь

Появиться план выполнения.

Анализируем это план. Но: это мы смогли поймать только тот запрос который оказался текущим для нашего процесса. К тому же этот замечательный скрипт работает не для всех версий Oracle. 

Есть следующие варианты команды autotrace:

SET AUTOTRACE OFF           - No AUTOTRACE report is generated. This is the
                              default.  
SET AUTOTRACE ON EXPLAIN    - The AUTOTRACE report shows only the optimizer
                              execution path. 
SET AUTOTRACE ON STATISTICS - The AUTOTRACE report shows only the SQL
                              statement execution statistics.  
SET AUTOTRACE ON            - The AUTOTRACE report includes both the
                              optimizer execution path and the SQL
                              statement execution statistics.  
SET AUTOTRACE TRACEONLY     - Like SET AUTOTRACE ON, but suppresses the
                              printing of the user's query output, if any. 

См. также описание Tom Kyte

Для того чтобы план выполнения заработал нужно чтобы была доступна таблица plan_table. Ее нужно создать скриптом $ORACLE_HOME/rdbms/admin/utlxplan.sql Под тем пользователем под которым вы собираетесь получать план запроса. Также нужно выдать пользователю роль plustrace

sh% sqlplus sys/manager

sql> grant plustrace to <username>

Если такой роли в БД не оказалось то код ее создания можно найти в каталоге $ORACLE_HOME/sqlplus/admin/plustrce.sql

Способ номер 2, несложный

Если хотим знать больше про запрос или скажем их у нас сразу много (в процедуре), то вместо autotrace мы можем получить dump статистики работы нашего запроса в файле и позднее обработать ее программой tkprof (см. ниже) 

Чтобы включить в этот dump файл времена выполнения, установим на уровне сервера сбор этой статистики:

sh% sqlplus sys/manager

sql> alter system set timed_statistics=true;

Это нужно сделать один раз из любой, не обязательно текущей сессии. Это установка действует на инстанс целиком. 

Теперь дадим команду на сбор статистики. 

sql>alter session set sql_trace=true

или 

sql>ALTER SESSION set events '10046 trace name context forever, level 8'

Цифра означает следующие:

Это приведет к тому, что на сервере начнет формироваться файл, который можно обработать утилитой tkprof (описано чуть ниже). Файл будет находиться в директории udump и иметь вид ora_<pid>.trc

Следим за чужими сессиями

В предыдущих разделах подразумевалось что мы исследуем запрос в нашей же сессии. Но что если нам нужно проследить за чужой сессией ?

Вообще-то я все оформил в виде perl скрипта. Собственно, далее я вольно его пересказываю :) Если у Вас есть IAS, то значит perl и все необходимые модули у Вас также есть.

Смотрим pid процесса и на всякий случай его parent pid или child pid. Запускаем их в запрос вида: 

select s.sid, s.serial#, p.spid from v$session s, v$process p
where p.spid = ? or p.spid = ? and s.paddr = p.addr

Получаем важную вещь: sid и serial. 

Тогда под пользователем sys в sqlplus запускаем пакет dbms_system

sql> sys.dbms_system.set_sql_trace_in_session(<SID>, <SERIAL>, TRUE);

где sid и serial получены на предыдущем шаге. 

Даем нашему процессу поработать. Все что он сделает начиная с этого момента будет сложено в trace файл на сервере БД. Trace файл находится в директории, задаваемой параметром user_dump_dest в init.ora. 

Нашли файл, скажем отсортировав их по времени, теперь пришло время обработки. 

Обработка данных

Запускаем утилиту tkprof

sh% tkprof <file_to_trace> < outfile> explain=user/passwd sort=exedsk sys=no

Где user/passwd имя пользователя владельца схемы по которой выполнялся запрос. Тогда в получившемся файле будут планы выполнения. Полный синтаксис tkprof описан в документации в разделе Using SQL Trace and Tkprof.

Давайте на примере посмотрим что как. В качестве примера взят реальный файл одного из клиентов. 

Хотите все сами ?

http://technet.oracle.com/doc/oracle8i_816/server.816/a76992/ch14_str.htm#1018

Здесь формально написано даже лучше чем у меня: http://www.oreview.com/9604har.htm Рекомендую к прочтению!

Оказывается, что для определенных случаев удобно пользоваться даже не tkprof, а trcanlzr. Почитайте, это может быть любопытно.