Средства оптимизации "проблемных" SQL-запросов
Средства оптимизации "проблемных" SQL-запросов
После написания программы и появления "живых" данных выясняется, что реакция программы на тестовые наборы порой сильно отличается от ее поведения в реальных условиях. Тем более, что программисты, как правило, мало внимания уделяют формированию большого количества этих самых тестовых наборов данных. А на небольших проектах, когда проектировщик, программист и тестировщик — это одно и то же лицо, на этапе создания программы практически невозможно выявить все ошибки и узкие места. И когда спустя несколько месяцев после внедрения программы пользователи начинают жаловаться, что программа "еле шевелится", приходится напряженно искать "бутылочное горлышко", тормозящее программу.
Практика показывает, что анализируя и перестраивая SQL-запросы можно снизить время их выполнения в десятки, а иногда и в сотни раз. После разработки нескольких проектов у программистов вырабатываются навыки написания более "быстрых" запросов. Но все равно полезно выполнять периодический анализ затрат ресурсов сервера при работе вашего творения. И хотя по большому счету анализ использования ресурсов сервера — это работа системного администратора, иметь навыки по оптимизации программ никому не помешает. Существует ряд программ, позволяющих автоматизировать и упростить эту нелегкую на первый взгляд задачу. Данный материал ориентирован на работу с сервером Oracle, но я думаю, что есть аналогичные средства и для других СУБД. Первым нашим помощником станет программа мониторинга работы сервера Oracle под названием Spotlight on Oracle фирмы Quest software (http://www.quest.com). Это очень мощный инструмент, предназначенный для контроля функционирования вашего сервера. Данная программа выполнена в необычной цветовой палитре, что резко выделяет ее среди других продуктов. После запуска этой программы необходимо создать учетную запись пользователя, для чего потребуется учетная запись SYS или запись с системными привилегиями DBA. Помощник создания новой учетной записи вызывается из меню File -> User Wizard.
После создания учетной записи пользователя и соединения с сервером Oracle нам представляется визуальная картинка, которая отображает компоненты и процессы работы сервера. Причем, если какой-нибудь компонент работает не оптимально или с перегрузкой, то изменяется его цвет от зеленого до красного в зависимости от степени перегрузки. Возможен мониторинг сразу нескольких серверов, список которых отображается в левой панели и также меняет цвет. Иконка в панели задач меняет цвет синхронно с программой, что позволяет оперативно реагировать при "свернутом" приложении. Пример мониторинга показан на рис. 1.
Очень полезной особенностью данной программы является система авторекомендаций решения проблемных вопросов. Достаточно кликнуть мышкой по красному участку изображения, чтобы получить развернутое описание проблемы и возможные методы ее устранения. Если же все нормально, то благодаря данной программе можно подстроить параметры запуска сервера для уменьшения используемых им системных ресурсов. Например, по рис. 1 можно сделать вывод, что размер табличного пространства файла базы данных можно смело уменьшить в 2 раза.
Но это все проблемы системного администратора. Разработчиков больше волнует, как работают их творения и сколько ресурсов "кушают" запросы к базе данных. Для этого вызываем пункт меню Navigator -> Top Sessions. После заполнения параметров фильтра отбора данных нам будет показан список текущих запросов к серверу базы данных. Предварительно отсортировав запросы по требованиям к ресурсам, можно выделить самые "прожорливые". В этом же окне можно посмотреть план выполнения запроса, пример которого показан на рис. 2. Причем план возможно представить в виде графа, дерева или словесного описания. Здесь также используется цветовая маркировка проблемных участков.
После выявления проблемных SQL-запросов настал черед их оптимизации. Для автоматизации этого процесса воспользуемся программой SQL Expert фирмы LECCO (http://www.leccotech.com). Вызываем окно SQL-редактора и добавляем в него проблемный запрос. Здесь также можно посмотреть план выполнения запроса. Но нас больше всего интересует функция меню SQL -> Optimize, которая генерирует список альтернативных вариантов построения заданного запроса. А функция SQL -> Butch Run позволяет проанализировать время выполнения всех запросов на "живых" данных и вывести результирующую таблицу, которую можно отсортировать по требуемому параметру. Выбрав наиболее оптимальный запрос, его можно сравнить с оригиналом и принять решение о возможности дальнейшего его использования в своем приложении. Пример работы по оптимизации запроса показан на рис. 3.
Таким образом, используя предложенную методику, можно не затрагивая код основной программы порой существенно повысить производительность приложений. При этом вы получите неоценимый опыт для написания высокопроизводительных запросов. Анализируя предложенные программой варианты SQL-кода, вы со временем вырабатываете привычку сразу писать "оптимально", что также повышает ваш имидж как хорошего специалиста.
Сергей Бердачук, Berdachuk@tut.by
http://bs_elbis.at.tut.by
После написания программы и появления "живых" данных выясняется, что реакция программы на тестовые наборы порой сильно отличается от ее поведения в реальных условиях. Тем более, что программисты, как правило, мало внимания уделяют формированию большого количества этих самых тестовых наборов данных. А на небольших проектах, когда проектировщик, программист и тестировщик — это одно и то же лицо, на этапе создания программы практически невозможно выявить все ошибки и узкие места. И когда спустя несколько месяцев после внедрения программы пользователи начинают жаловаться, что программа "еле шевелится", приходится напряженно искать "бутылочное горлышко", тормозящее программу.
Практика показывает, что анализируя и перестраивая SQL-запросы можно снизить время их выполнения в десятки, а иногда и в сотни раз. После разработки нескольких проектов у программистов вырабатываются навыки написания более "быстрых" запросов. Но все равно полезно выполнять периодический анализ затрат ресурсов сервера при работе вашего творения. И хотя по большому счету анализ использования ресурсов сервера — это работа системного администратора, иметь навыки по оптимизации программ никому не помешает. Существует ряд программ, позволяющих автоматизировать и упростить эту нелегкую на первый взгляд задачу. Данный материал ориентирован на работу с сервером Oracle, но я думаю, что есть аналогичные средства и для других СУБД. Первым нашим помощником станет программа мониторинга работы сервера Oracle под названием Spotlight on Oracle фирмы Quest software (http://www.quest.com). Это очень мощный инструмент, предназначенный для контроля функционирования вашего сервера. Данная программа выполнена в необычной цветовой палитре, что резко выделяет ее среди других продуктов. После запуска этой программы необходимо создать учетную запись пользователя, для чего потребуется учетная запись SYS или запись с системными привилегиями DBA. Помощник создания новой учетной записи вызывается из меню File -> User Wizard.
После создания учетной записи пользователя и соединения с сервером Oracle нам представляется визуальная картинка, которая отображает компоненты и процессы работы сервера. Причем, если какой-нибудь компонент работает не оптимально или с перегрузкой, то изменяется его цвет от зеленого до красного в зависимости от степени перегрузки. Возможен мониторинг сразу нескольких серверов, список которых отображается в левой панели и также меняет цвет. Иконка в панели задач меняет цвет синхронно с программой, что позволяет оперативно реагировать при "свернутом" приложении. Пример мониторинга показан на рис. 1.
Очень полезной особенностью данной программы является система авторекомендаций решения проблемных вопросов. Достаточно кликнуть мышкой по красному участку изображения, чтобы получить развернутое описание проблемы и возможные методы ее устранения. Если же все нормально, то благодаря данной программе можно подстроить параметры запуска сервера для уменьшения используемых им системных ресурсов. Например, по рис. 1 можно сделать вывод, что размер табличного пространства файла базы данных можно смело уменьшить в 2 раза.
Но это все проблемы системного администратора. Разработчиков больше волнует, как работают их творения и сколько ресурсов "кушают" запросы к базе данных. Для этого вызываем пункт меню Navigator -> Top Sessions. После заполнения параметров фильтра отбора данных нам будет показан список текущих запросов к серверу базы данных. Предварительно отсортировав запросы по требованиям к ресурсам, можно выделить самые "прожорливые". В этом же окне можно посмотреть план выполнения запроса, пример которого показан на рис. 2. Причем план возможно представить в виде графа, дерева или словесного описания. Здесь также используется цветовая маркировка проблемных участков.
После выявления проблемных SQL-запросов настал черед их оптимизации. Для автоматизации этого процесса воспользуемся программой SQL Expert фирмы LECCO (http://www.leccotech.com). Вызываем окно SQL-редактора и добавляем в него проблемный запрос. Здесь также можно посмотреть план выполнения запроса. Но нас больше всего интересует функция меню SQL -> Optimize, которая генерирует список альтернативных вариантов построения заданного запроса. А функция SQL -> Butch Run позволяет проанализировать время выполнения всех запросов на "живых" данных и вывести результирующую таблицу, которую можно отсортировать по требуемому параметру. Выбрав наиболее оптимальный запрос, его можно сравнить с оригиналом и принять решение о возможности дальнейшего его использования в своем приложении. Пример работы по оптимизации запроса показан на рис. 3.
Таким образом, используя предложенную методику, можно не затрагивая код основной программы порой существенно повысить производительность приложений. При этом вы получите неоценимый опыт для написания высокопроизводительных запросов. Анализируя предложенные программой варианты SQL-кода, вы со временем вырабатываете привычку сразу писать "оптимально", что также повышает ваш имидж как хорошего специалиста.
Сергей Бердачук, Berdachuk@tut.by
http://bs_elbis.at.tut.by
Компьютерная газета. Статья была опубликована в номере 03 за 2004 год в рубрике программирование :: разное
