CC BY
135
УДК 004.658.2 ББК 32.972.34 М 42
Медведев Юрий Станиславович
Профессор, доктор технических наук, профессор Северо-Кавказского филиала Российского государственного университета правосудия, Краснодар, e-mail: [email protected] Пиотровский Дмитрий Леонидович
Доктор технических наук, профессор, зав. кафедрой автоматизации производственных процессов Кубанского государственного технологического университета, Краснодар, тел. (861) 2559392, е-mail: [email protected]
Мониторинг производительности базы данных Oracle
(Рецензирована)
Аннотация. Для построения корпоративных информационных систем в настоящее время широко используют системы управления базами данных (СУБД) Oracle, которые характеризуются высокой производительностью, надежностью и отказоустойчивостью и занимают лидирующее положение на рынке СУБД. По мере увеличения числа пользователей и объема данных неизменно возрастает вероятность снижения производительности базы данных (БД). Нередко обнаружить причину замедления работы системы бывает весьма сложно. Поэтому значительная часть рабочего времени администратора тратится на мониторинг работы БД и выявление проблем, причин их возникновения и поиск решений еще до того, как эти проблемы станут заметны пользователям. Основной целью настройки является достижение адекватного времени отклика системы без снижения производительности работы других пользователей или приложений. Таким образом, для того, чтобы обеспечить высокую производительность работы пользователей и приложений, БД должна находиться под постоянным наблюдением. СУБД Oracle располагает значительным арсеналом инструментальных средств для предупреждения администратора БД о возникающих проблемах. В статье рассматриваются инструменты мониторинга СУБД Oracle. Такая диагностика позволяет выявить причины нарушения функциональности, обеспечить высокую производительность БД.
Ключевые слова: система управления базами данных, база данных, SQL, Oracle.
Medvedev Yuriy Stanislavovich
Professor, Doctor of Technical Sciences, Professor of North-Caucasian Branch of Russian State University of Justice, Krasnodar, e-mail: [email protected] Piotrovsky Dmitriy Leonidovich
Doctor of Technical Sciences, Professor, Head of Department of Automatization of Production Processes, Kuban State University of Technology, Krasnodar, ph. (861) 2559392, е-mail: [email protected]
Performance monitoring of the Oracle database
Abstract. To create corporate information systems, we use at present the Oracle database management systems (DBMS), which are characterized by high productivity, reliability and resiliency and occupy a leading position in the market of DBMS. With increasing number of users and volume of data, the probability ofperformance degradation of the database (DB) invariably increases. Often it is difficult to detect the reason of slow work of system. Therefore, a significant portion of administrator time is spent for monitoring databases and identifying problems, their reasons and finding .solutions before these problems become noticeable to users. The main purpose of tuning is to achieve adequate response time of the system without reducing performance of other users or applications. Thus, in order to ensure high productivity of users and application, the database needs to be under constant surveillance. Oracle has a large arsenal of tools to alert the database administrator of emerging problems. This article discusses the monitoring tools of the Oracle DBMS. Such diagnostics is in the ability to identify the causes of dysfunction, and to provide a high DB performance.
Keywords: the database management system, database, SQL, Oracle.
В настоящее время СУБД Oracle, характеризующаяся высокой производительностью, надежностью и отказоустойчивостью, занимает лидирующее положение на рынке СУБД. К несомненным достоинствам СУБД Oracle относится возможность работы на большинстве операционных систем (Windows, Unix, Linux, MacOS), кроссплатформенность - возможность переноса на иную программную платформу, практически полное отсутствие ограничений в применении, в том числе возможность реализации корпоративного хранилища данных с использованием алгоритмов Data Mining и технологии OLAP.
С ростом числа пользователей и объема данных работа базы данных может замедлять-
ся. Общеизвестно, что важнейшим показателем производительности компьютерной прикладной системы является ее способность адекватно обслуживать пользователей, не снижая производительности их работы. Поэтому значительная часть рабочего времени администратора тратится на мониторинг работы БД и выявление проблем, причин их возникновения и поиск решений еще до того, как эти проблемы станут заметны пользователям [1]. Основной целью настройки является достижение адекватного времени отклика системы без снижения производительности работы других пользователей или приложений.
Однако на вопрос, от чего зависит быстродействие БД, невозможно дать однозначный ответ, потому что у каждой БД «узкие места» весьма индивидуальны.
Причинами снижения производительности работы пользователей могут быть проблемы, связанные с приложениями, такие как неудачно написанные SQL-команды [2], большие таблицы без соответствующих индексов и нерационально спроектированные транзакции.
Прежде всего, источниками замедления работы БД могут быть сами запросы. Зачастую запросы, которые удовлетворительно выполняются на этапе разработки, со временем начинают работать так медленно, что для их выполнения требуется несколько минут. Как правило, запрос может быть написан различными способами, и заранее неизвестно, какой из них обеспечит наилучший результат.
Следующим этапом мероприятий по повышению производительности системы, который может быть проведен после проверки того факта, что SQL-команды приложения выполняются достаточно эффективно, является настройка экземпляра БД Oracle [3]. Существуют многочисленные настраиваемые параметры Oracle, обеспечивающие высокую доступность ресурсов [4], включая динамически изменяемые параметры экземпляра, зеркальное отражение онлайновых журнальных групп и управляющего файла, контрольные точки и установки допустимого среднего времени восстановления (mean time to recover) MTTR, онлайновое резервное копирование и многие другие.
СУБД Oracle располагает значительным арсеналом инструментальных средств для предупреждения администратора БД о возникающих проблемах.
Диагностический инструмент Automatic Database Diagnostic Monitor (ADDM) на основании анализа собранной статистики выдает рекомендации по устранению выявленных проблем, настройке БД и повышению производительности. Информация представляется web-приложением Oracle Enterprise Manager (EM) в виде диаграмм. Характерные проблемы, выявляемые ADDM:
- «конкуренция блокировок», в результате которых один пользователь блокирует данные, заставляя других пользователей ждать их освобождения;
- нехватка ресурсов системы;
- неудовлетворительное управление соединениями, при котором приложение создает новые соединения с БД вместо того, чтобы использовать повторно существующие.
На основе стандартных метрик генерируются служебные сообщения. Предусмотрена также возможность создания собственных метрик. Сообщения могут отправляться по почте, а также на мобильный телефон. Конфигурируя систему, целесообразно ограничивать количество сообщений, в особенности приходящих на мобильный телефон. В противном случае неизбежно придется игнорировать приходящие сообщения.
Когда для работы того или иного компонента недостаточно дискового пространства, СУБД Oracle генерирует соответствующее сообщение. Следует обратить самое пристально внимание на такие предупреждающие сообщения EM, как "Table Space Usage", "Resumable Session Suspended", "Snapshot Too Old", "Recovery Area Low on Free Space". Так, например, сообщение "Table Space Usage" выводится, когда 85% доступного пространства занято. При достижении этой величины 97% генерируется сообщение о серьезной ошибке. В случае если установлено ограничение на общий размер табличных пространств, то для решения проблемы необходимо добавить файлы данных. При работе системы в режиме автоматического расширения табличного пространства, очевидно, потребуется дополнительный диск.
В комплекте с СУБД Oracle поставляется утилита Statspack, представляющая собой на-
бор сценариев SQL, PL/SQL и SQL*Plus. Она служит для более эффективной настройки Oracle. Данная программа собирает и хранит в виде моментальных снимков состояния системы, статистические данные о производительности системы, помогает выявлять «узкие места» в работе Oracle. В зависимости от настроек возможен сбор статистики с большей или меньшей степенью подробности.
С увеличением размера БД запросы могут серьезно замедлять работу системы. СУБД Oracle автоматически анализирует выполнение запросов и оптимизатор на основании статистической информации о таблицах выбирает приемлемое решение для конкретного запроса [5].
Для поиска путей решения проблем с производительностью можно воспользоваться инструментами типа Advisor.
ADDM Advisor анализирует работу системы, выявляет проблемы и выдает рекомендации по их устранению. Для эффективной работы данного инструмента требуется анализ данных в течение достаточно длительного времени, в особенности в периоды пиковой нагрузки.
SQL Tuning Advisor анализирует SQL-выражения и выдает рекомендации по повышению эффективности обработки конкретного запроса. Создаваемый в процессе анализа запроса SQL Profile содержит основные сгенерированные решения. Enterprise Manager определит, какие запросы занимают основную часть времени, проанализирует производительность системы в различные интервалы времени. На основании предложенного плана выполнения запроса SQL Tuning Advisor способен решить проблему самостоятельно.
Query Access Advisor выполняет анализ SQL-выражений, использования памяти и производительности запроса и генерирует рекомендации по использованию индексов и представлений.
Memory Advisor (Shared Pool Advisor, Buffer Cashe Advisor, PGA Advisor) анализируют использование системной памяти и могут автоматически изменять конфигурацию системы для достижения оптимальной производительности. Следует отметить, что Shared Pool Advisor и PGA Advisor можно запускать вручную.
Segment Advisor анализирует сегменты и решает, следует ли сократить их для экономии пространства. Поддерживает отчеты и применяется при планировании.
Undo Advisor выявляет проблемы, связанные с табличными пространствами Undo. Помогает установить оптимальный размер, пороговые значения и время хранения.
Для анализа и устранения причин, вызывающих замедление работы системы, оптимизатором (optimizer) Oracle генерируется план выполнения (execution plan) - набор операций, используемых для выполнения конкретного оператора SQL. Чтобы сгенерировать оптимальный план выполнения оператора, Oracle автоматически собирает статистику о данных из таблиц и индексов; этот процесс принято называть стоимостной оптимизацией (cost-based optimization). Предусмотрена возможность передачи оптимизатору директив -подсказок (hint) - для оказания влияния на генерацию плана выполнения. Подсказка оказывает влияние на выбор оптимизатором плана выполнения и может повысить производительность оператора SQL.
Помимо штатных средств Oracle, существует значительное количество способных дополнить их систем мониторинга других разработчиков, например, системы Cacti, Zabbix [6].
Для более полной и объективной оценки производительности системы необходимо проводить анализ событий ожидания - блокирующих факторов, в результате которых работа системы замедляется. Разработчиками СУБД Oracle определены списки таких событий ожидания, в версии Oracle 11g их определено более 1000. Они категорируются в зависимости от причин возникновения событий ожидания следующим образом [4]:
- Cluster (кластер);
- Network (сеть);
- Administration (администрирование);
- Configuration (конфигурация);
- Commit (фиксация);
- Application (приложение);
- Concurrency (параллелизм);
- System I / O (системный ввод / вывод);
- User I / O (пользовательский ввод / вывод);
- CPU (процессор).
Для анализа событий ожидания используют интерфейс событий ожидания EM, который удобно отображает состояние БД в графическом виде с предоставлением подробного аналитического отчета об операторах SQL, связанных с событиями ожидания. Сведения, полученные с помощью интерфейса событий ожидания, всесторонне охватывают многочисленные аспекты функционирования БД, такие, как системный ввод / вывод, параллельную обработку, контрольные точки, защелки, сетевой трафик, блокировки на уровне строки. Для повышения производительности БД целесообразно ограничить применение операторов wait.
Следует помнить, что интерфейс событий ожидания не в состоянии обеспечить следующую информацию [4]:
- время, затраченное на использование центрального процессора;
- время, затраченное на ожидание доступа к центральному процессору;
- время, затраченное на загрузку требуемых страниц виртуальной памяти в оперативную память.
Опытные администраторы для сбора статистики событий ожидания используют shell-сценарии, которые задействуют встроенные системные утилиты. Для этого, как правило, оперируют следующими представлениями [4]:
- V$ACTIVE_SESSION_HISTORY (ежесекундные снимки активного сеанса БД);
- V$EVENT_NAME (информация о событиях ожидания);
- V$SQLAREA (статистика по SQL-операторам, которые находятся в памяти, проанализированы и готовы к выполнению).
Таким образом, для того, чтобы обеспечить высокую производительность работы пользователей и приложений, база данных должна находиться под постоянным наблюдением. Параметры, на которых, вероятнее всего, следует сосредоточить внимание: время отклика и пропускная способность. Важнейшим количественным показателем является число транзакций, выполняемых за одну секунду. Оно должно составлять порядка 1000 транзакций в секунду на каждый процессор [4]. Целесообразно при первичной настройке системы определиться с целями мониторинга, выделить метрики, которые необходимо отслеживать для решения поставленных задач, затем выбрать необходимые инструментальные средства.
Примечания:
1. Перри Д., Пост Д. Введение в Oracle 10g: пер. с англ. М.: И. Д. Вильямс, 2013. 704 с.
2. Прайс Д. Oracle 10g SQL: пер. с англ. М.: Лори, 2013. 566 с.
3. Миллсап К., Хольт Д. Oracle. Оптимизация производительности: пер. с англ. СПб.: Символ Плюс, 2016. 464 с.
4. Бобровский С. Oracle Database 10g XE для Windows. Эффективное использование: пер. с англ. М.: Лори, 2014. 486 с.
5. Кириллов В.В., Громов Г.Ю. Введение в реляционные базы данных. СПб.: БХВ-Петербург, 2016. 464 с.
6. Беленькая М.Н., Малиновский С.Т., Яковенко Н.В. Администрирование в информационных системах. М.: Горячая линия-Телеком, 2013. 400 с.
References:
1. Perry D., Post D. Introduction to Oracle 10g: transl. from English. M.: I.D. Williams, 2013. 704 pp.
2. Price D. Oracle 10g SQL: transl. from English. M.: Lori, 2013. 566 pp.
3. Millsap K., Holt D. Optimizing Oracle Performance: transl. from English. SPb.: Symbol Plus, 2016. 464 pp.
4. Bobrovsky S. Oracle Database 10g XE for Windows. Effective use: transl. from English. M.: Lori, 2014. 486 pp.
5. Kirillov V.V., Gromov G.Yu. Introduction to relational databases. SPb.: BKhV-Petersburg, 2016. 464 pp.
6. Belenkaya M.N., Malinovsky S.T., Yakovenko N.V. Administration in information systems. M.: Gorya-chaya Liniya-Telecom, 2013. 400 pp.
