544 Глава 10

22 end if;

23 end loop;

24 l query := translate(l query, 0123456789, @@@@@@@@@@);

25 for i in 0 .. 8 loop

26 l query := replace(l query, lpad(@,10-i,@), ©);

27 l query := replace (l query, lpad( ,10-i, ) / ) ;

28 end loop;

29 return upper(l query);

30 end;

31 /

Function created.

Для основного кода сценария мы сделаем копию представления V$SQLAREA - запросы к этому представлению выполняются долго, поэтому хотелось бы обращаться к нему только один раз. Мы копируем его во временную таблицу, чтобы можно было работать с соответствующими данными:

tkyte@TKYTE816> create global temporary table sql area tmp

2 on commit preserve rows

3 as

4 select sql text, sql text sql text wo constants

5 from v$sqlarea

6 where 1=0

Table created.

tkyte@TKYTE816> insert into sql area tmp (sql text)

2 select sql text from v$sqlarea

436 rows created.

Пройдем по всем строкам этой таблицы и определим преобразованный SQL TEXT, из которого удалены все константы:

tkyte@TKYTE816> update sql area tmp

2 set sql text wo constants = remove constants(sql text);

436 rows updated.

Теперь все готово для выявления плохих операторов SQL:

tkyte@TKYTE816> select sql text wo constants, count(*)

2 from sql area tmp

3 group by sql text wo constants

4 having count(*) > 10

5 order by 2

SQL TEXT WO CONSTANTS COUNT(*)

INSERT INTO T VALUES (6 ) 100

Стратегии и средства настройки 545

Итак, в разделяемом пуле имеется 100 операторов INSERT, отличающихся только одним числовым полем в конструкции VALUES. Это почти наверняка означает, что кто-то заб1л использовать связываемые переменные. Бывают вполне оправданные случаи наличия нескольких экземпляров SQL-оператора в разделяемом пуле (например, в базе данных может быть пять разных таблиц с именем Т). Выяснив, что разумной причины, объясняющей наличие нескольких экземпляров одного и того же оператора, нет, необходимо найти соответствующего разработчика, научить его, как делать правильно, и заставить исправить операторы. Я считаю это ошибкой в программе, а не вполне допустимым приемом, а ошибка должна быть исправлена.

Итак, в этом разделе мы обсуждали важность использования связываемых переменных в приложении, а также необходимость свести к минимуму количество разборов запросов. Было описано новое средство - параметр инициализации CURSOR SHARING, который мог показаться панацеей при решении этих проблем, но оказалось, что все не так просто. Установка параметра CURSOR SHARING может использоваться как временное, промежуточное решение определенных проблем производительности приложения, но достичь максимальной производительности и масштабируемости можно только за счет правильной реализации приложений.

Я не знаю, как еще подчеркнуть этот момент. Приходилось видеть множество систем, потерпевших неудачу из-за того, что их создатели не учитывали представленных выше фактов. Как я уже писал в самом начале книги, если бы мне пришлось писать книгу о том, как создавать немасштабируемые и медленно работающие приложения для СУБД Oracle, она содержала бы всего одну главу, где было бы сказано: не используйте связываемые переменные . Использование связываемых переменных и правильных приемов разбора операторов еще не гарантирует масштабируемость, но если их не использовать, то ее точно не будет.

SQL TRACE, TIMED STATISTICS и TKPROF

SQL TRACE, TIMED STATISTICS и TKPROF- мои самые любимые инструментальные средства. Я использовал их множество раз для выявления причин неудовлетворительной производительности системы. Во всех этих случаях приходилось настраивать системы, которые я не создавал, поэтому и не знал, где искать. Эти установки и средства дают необходимую информацию для начала работы.

Если коротко, параметр SQL TRACE включает регистрацию всех операторов SQL, выполняемых приложением, информации о производительности, полученной в ходе выполнения этих операторов SQL, и фактически использованных планов выполнения операторов. Как было показано в предыдущем разделе, посвященном параметру CURSOR SHARING, AUTOTRACE показывает неверный план, а вот параметр SQL TRACE и утилита TKPROF показывают именно тот план, который реально использован. TIMED STATISTICS - это параметр, при установке которого сервер регистрирует продолжительность выполнения каждого шага. Наконец, TKPROF - это простая программа, используемая для преобразования файла трассировки в более удобочитаемый вид. В этом разделе я продемонстрирую, как использовать установку SQL TRACE и утилиту TKPROF, и попытаюсь разъяснить значение содержимого ис-

Глава 10

пользуемых ими файлов. Я не столько буду описывать, как настроить конкретный запрос, сколько покажу, как с помощью этих средств найти запросы, требующие настройки. Более подробную информацию о настройке отдельных запросов можно найти в руководстве Oracle8i Designing and Tuningfor Performance Manual. Там описаны различные способы доступа к данным, использование подсказок оптимизатору для настройки запросов и т.д.

Параметр TIMED STATISTICS управляет тем, будет ли сервер Oracle собирать информацию о времени выполнения различных действий в базе данных. Он может иметь одно из двух значений: TRUE или FALSE. Эта возможность настолько полезна, что я часто оставляю значение TRUE, даже когда не занимаюсь настройкой - влияние этого значения на производительность СУБД, как правило, незначительно (была проблема в Oracle 8.1.5, где совместное использование операторов SQL не всегда обеспечивалось, если параметр TIMED STATISTICS имел значение TRUE). Значение этого параметра можно устанавливать как на уровне системы, так и на уровне сеанса, а также глобально, в файле параметров инициализации экземпляра. Достаточно просто добавить в файл INIT.ORA для экземпляра строку:

timed statistics=true

и при следующем перезапуске СУБД этот параметр будет включен. Для установки его на уровне сеанса выполните следующую команду:

tkyte@TKYTE816> alter session set timed statistics=true; Session altered.

А для включения учета времени во всей системе:

tkyte@TKYTE816> alter system set timed statistics=true; System altered.

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

Организация трассировки

Параметр SQL TRACE также можно устанавливать на уровне системы или сеанса. При его установке генерируется так много данных и работа системы так замедляется, что включать ею лучше избирательно (редко или вообще никогда его не устанавливают для системы в файле init.ora). Параметр SQL TRACE тоже может иметь одно из двух значений, TRUE или FALSE. Если установлено значение TRUE, в каталоге, задаваемом параметром USER DUMP DEST файла init.ora при подключении к выделенному серверу или BACKGROUND DUMP DEST - при подключении к многопотоковому (MTS) серверу, будут генерироваться трассировочные файлы. Я, однако, не рекомендую использовать SQL TRACE при подключении в режиме MTS, поскольку результаты запросов сеанса будут записываться в различные трассировочные файлы при пере-