Oracle настройка производительности книги
Conventions
This section explains the conventions used in this manual including the following:
This section explains the conventions used within the text:
UPPERCASE Characters
Uppercase text is used to call attention to statement keywords, object names, parameters, filenames, and so on.
For example, "If you create a private rollback segment, then the name must be included in the ROLLBACK_SEGMENTS parameter of the parameter file".
Italicized Characters
Italicized words within text are book titles or emphasized words.
Related Documents
For more information about tuning the Oracle Application Server, see the Oracle Application Server Performance and Tuning Guide.
What's New
For release 8.1.6, this book was renamed Oracle8i Designing and Tuning for Performance to emphasize the importance of designing applications and writing SQL properly. Although the goal of the book remains the same, many chapters from release 8.1.5 have been restructured. The main changes with 8.1.6 include the following:
-
This manual has expanded information on using the cost-based optimizer (CBO), particularly Chapter 4, "The Optimizer" and Chapter 9, "Optimizing SQL Statements". Some of this information was formerly in the Oracle8i Concepts manual.
Part Five from release 8.1.5 (including information on parallel execution and partitioning) is now part of the new Oracle8i Data Warehousing Guide.
Some information from the 8.1.5 release of this manual was duplicated in other manuals in the Oracle documentation set. This includes information on using the multi-threaded server and using PL/SQL packages. You will find cross-references to those books where this information is provided.
Intended Audience
This manual is an aid for people responsible for the operation, maintenance, and performance of Oracle. To use this book, you could be a database administrator, application designer, or programmer. You should be familiar with Oracle8i, the operating system, and application design before reading this manual.
Oracle Database 11g Настройка производительности
Причины неэффективного выполнения SQL выражений
SQL выражение может выполнятся неэффективно по следующим причинам:
- Устаревшая статистика оптимизатора: Планы выполнения SQL генерируются стоимостным оптимизатором. Для того чтобы оптимизатор генерировал и использовал наиболее эффективный план выполнения, он нуждается в точной информации о хранении и распределении данных в таблицах и индексах, которые участвуют в обработке SQL выражения.
- Отсутсвие путей доступа к данным (access path): Отсутствие структур доступа к данным, таких как индексы, материализованные представления или партиций - частая причина падения производительности SQL выражений. Создание соответствующих методов доступа к данным на порядок увеличит производительность SQL выражений.
- Выбор не оптимального плана выполнения: В отдельных случаях оптимизатор может выбрать не оптимальный план исполнения для SQL выражения. Причиной выбора не оптимального плана может быть неправильно оцененная стоимость, кардинальность или селективность для предиката выражения.
- Неправильно построенный SQL: Если SQL выражение спроектировано неправильно, оптимизатор не сможет построить оптимальный план выполнения. Отсутствующее условия соединения, ведущее к декартову произведению данных, или использование наиболее ресурсоёмких операций таких как UNION вместо менее затратной UNION ALL - некоторые примеры написания неэффективных SQL выражений.
- Дополнительно на производительность выполнения SQL могут влиять проблемы с аппаратным обеспечением, таким как память, ввод/вывод CPU и.т.д.
- Запрос определяет какие продукты имеют стоимость по прайс листу менее чем на 15% выше средней стоимости продукта. В данное выражение встроен подзапрос, который выполняется для каждой строки, найденой в результате основного запроса. Этот запрос можно переписать следующим образом: SELECT COUNT(*) FROM products p,(SELECT prod_id, AVG(unit_cost) ac FROM costsGROUP BY prod_id) cWHERE p.prod_id = c.prod_id ANDp.prod_list_price < 1.15 * c.ac
- В данном запросе применяется функция к операции объединения, ограничивая тем самым возможность использования индекса для объединения. Для нормального выполнения запроса понадобится создать индекс основанный на функции.
- Запрос включает в условии неявное преобразование типов данных.
- Запрос использует функцию преобразования типов для приведения в соответствие типов данных обрабатываемых в предикате. Проблема заключается в том, что функция будет вызываться для каждой строки в таблице employees. Более оптимальным будет конвертировать условие предиката а не весь столбец: SELECT * FROM employees WHERE salary = TO_NUMBER(:sal)
- В запросе используется оператор UNION вместо UNION ALL для того чтобы отбросить дублирующиеся строки в процессе выполнения запроса. Данная операция приведет к дополнительной сортировке для исключения не уникальных значений.
Automatic Workload Repository (AWR): Собирает, обрабатывает и обслуживает статистику производительности для обнаружения проблем и задач самонастройки. Данные статистики хранятся в памяти и на диске в таблицах БД. собираемые данные могут быть просмотрены как при помощи представлений, так и в виде отчета за период.
Active Session History (ASH): Предоставляет выборочную статистику по активности сессий в экземпляре. Активные сессии выбирающиеся каждую секунду хранятся в цикличном буфере в SGA.
- Проактивный мониторинг
- Automatic Database Diagnostic Monitor (ADDM) автоматически выявляет узкие места в работе Oracle Database. Дополнительно, работая с другими компонентами управления, ADDM выдает рекомендации для устранения узких мест, используя доступные опции.
- Oracle Database 11g в дальнейшем автоматизирует процесс настройки SQL, идентифицирует проблемные SQL выражения, запускает для них SQL Tuning Advisor и применяет профиль, сгенерированный в результате работы SQL advisor к SQL выражению, повышая тем самым скорость его выполнения без вмешательства пользователя. Для этого, при помощи среды AUTOTASK создается пакетное задание для автоматического исследования производительности (Automatic SQL tuning task). Задание Automatic SQL Tuning task запускается по умолчанию каждую ночь.
- Реактивный мониторинг
- Предупреждения генерируемые сервером: База данных Oracle может автоматически определять проблемные ситуации. В качестве реакции на обнаруженную проблему, СУБД Oracle отправляет предупреждение о ней с возможными действиями для её устранения
- В качестве инструмента для реактивного мониторинга производительности БД также может использоваться Oracle Enterprise manager.
Automatic Database Diagnostic Monitor: Постоянно анализирует данные производительности, собираемые экземпляром БД.
SQL Tuning Advisor: Анализирует SQL выражения, определенные как проблемные, и пытается из настроить. По умолчанию задание на анализ производительности включено. Вы можете также вручную запустить SQL Tuning advisor для анализа производительности определенного SQL, или группы SQL используя SQL Tuning set.
SQL Tuning Sets : Служит репозиторием для хранения набора SQL выражений. Может использоваться SQL Tuning advisor для анализа производительности группы SQL выражений или например для переноса SQL из одной БД в другую для выявления потенциальных проблем с производительностью, которые могут возникнуть после миграции.
SQL Access Advisor: Анализирует SQL выражение и выдает рекомендации по созданию материализованных представлений, индексов, логов материализованного представления или секционированию таблиц и индексов.
SQL Monitoring: Позволяет в режиме реального времени наблюдать за процессом исполнения SQL и просматривать план и статистику его выполнения.
SQL Plan Management (SPM): Может быть использован для контроля и стабилизации планов исполнения SQL выражений при помощи создания опорных линий. При использовании опорной линии SQL будет использовать план исполнения с наилучшим временем выполнения, остальные планы, сгенерированные для этого SQL будут помечены как исторические, будут хранится в опорной линии и постоянно анализироваться. В случае нахождения более оптимального плана выполнения, этот план будет применен к SQL выражению, а предыдущий будет помещен в историю.
- Определение высоко нагруженных SQL является одной из наиболее важных задач которые вы должны выполнять. ADDM - идеальный инструмент для выполнения данной задачи.
- По умолчанию СУБД Oracle собирает статистику оптимизатора автоматически. Для сбора статистики работает задание планировщика, которое запускается в maintenance window.
- Статистика операционной системы предоставляет информацию и производительности основных аппаратных компонентов также как и производительность системы в целом.
- Часто перестройка индексов оказывает положительное влияние на производительность. Например удаление не селективных индексов для увеличения скорости обработки DML или добавление новых столбцов в индекс для увеличения его селективности.
- Вы можете сохранить существующие планы выполнения SQL выражений используя сохранив статистику или создав опорную линию для этого SQL.
Наиболее часто на пользовательских системах встречаются следующие ошибки:
Неправильно настроенное подключение: Приложение подключается и отключается при каждой операции взаимодействия с БД. Эта проблема часто встречается в случае работы с stateless сервером приложений.
Некорректное использование курсоров и разделяемого пула: Не используются результаты хранимые в курсоре при повторном разборе. Если не используются BIND переменные, может выполнятся жесткий разбор при каждом выполнении одного SQL. В случае использования BIND переменных, открытые ранее курсоры могут использоваться повторно необходимое количество раз. Очень часто проблемы с версионностью и большим количеством жестких разборов выражения возникают в приложениях, генерирующих SQL динамически.
Некорректно спроектированный SQL: Плохой SQL - это SQL который использует больше ресурсов, чем уместно при его выполнении. Это может быть например DSS запрос, который выполняется более чем 24 часа или запрос к онлайн приложению, который выполняется более минуты. SQL который потребляет значительную часть системных ресурсов должен также быть обследован на предмет его потенциального улучшения. ADDM идентифицирует высоко нагруженные SQL и SQL Tuning advisor может быть использован в дальнейшем для увеличения его производительности.
Использование нестандартных параметров инициализации: Установка некорректных значений параметров может быть выполнена на основании неправильного совета или решения. Большинство систем показывает приемлемую производительность с использованием стандартных параметров экземпляра. В частности, недокументированные параметры оптимизатора могут вызвать серьезные проблемы, которые потом тяжело будет определить и устранить. Также, параметры оптимизатора установленные в файле инициализации могут поменять существующие планы выполнения запросов. Исходя из этого схемы, статистика схем и настройки оптимизатора должны управляться вместе для обеспечения целостности конфигурации и производительности.
Некорректная настройка ввода/вывода: Система хранения данных должна конфигурироваться из расчета доступности, а также пропускной способности, а не легкости хранения и максимизации размера разделов.
Проблемы с настройкой Redo логов: Часто создается недостаточное количество логов Redo. Маленький размер Redo лог файлов может привести к частым операциям checkpoint, что приведет в свою очередь к высокой нагрузке на буферный кэш и систему ввода/вывода. При избыточном количестве Redo логов не сможет выполняться архивирование и БД будет ждать выполнения процесса создания архивных логов.
Избыточная сериализация ресурсов: Сериализация блоков данных в буферном кэше из за нехватки undo сегментов является частой проблемой для приложений с большим количеством активных пользователей и маленьким количеством undo сегментов. Использование автоматического управления сегментами (ASSM) и автоматического управления UNDO чаще всего решает данную проблему.
Длительное полное сканирование таблиц: Большое количество продолжительных сканирований таблиц говорит о некорректном дизайне транзакций, возможном отсутствии индексов или не оптимизированном SQL. Операция full table scan вызывает большую продолжительную нагрузку на систему ввода/вывода.
Большое количество рекурсивного SQL: Большое количество рекурсивного SQL выполняемого SYS может обозначать активность, связанную с управлением пространством, например выделением экстентов. Для исключения рекурсивного SQL рекомендуется использовать табличные пространства с локальным управлением экстентами. В этом случае рекурсивный SQL выполняется от пользователей с разными ID и нагрузка распределяется более равномерно.
Ошибки внедрения и миграции: В большинстве случаев приложение использует слишком много ресурсов, поскольку схема в которой хранятся таблицы необходимые объекты не была должным образом смигрирована из среды разработки или из более поздней версии схемы. Типичными ошибками в процессе миграции являются отсутствие индексов или неактуальная статистика. Данные ошибки являются причиной формирования неоптимальных планов выполнение и как следствие, причиной падения производительности. Когда мигрируется какой либо из разработанных или сущевствующих компонентов, для обеспечения стабильности планов исполнения, необходимо вместе с объектами переносить статистику при помощи пакета DBMS_STATS.
В книге рассматривается теория и практика управления производительностью СУБД Oracle, настройка экземпляра СУБД, операционной системы и аппаратной платформы, настройка приложений с точки зрения производительности.
Часть I
Метод
1 Введение в управление производительностью Oracle
Что такое настройка?
Почему необходима настройка?
Кто должен заниматься настройкой?
Сколько требуется вести настройку?
Не пора ли остановиться?
2 Метод, стоящий за безумием
Почему нужно заботиться о методологии настройки?
Что такое хорошая методология настройки?
Методология настройки производительности Oracle
Поставьте разумные цели настройки производительности
Измерьте и задокументируйте текущую производительность
Идентифицируйте узкие места производительности Oracle на текущий момент
Захват событий ожидания в файлы трассировки .
Идентифицируйте текущие узкие места ОС
Настраиваем требующийся компонент
Отследите и выполните процедуры контроля изменений
Измерьте и задокументируйте текущую производительность
Повторяйте шаги с 3 по 7 до тех пор, пока не будут достигнуты цели настройки
Часть II
3 Настройка приложения — вопросы, относящиеся к ведению АБД
История об оптимизаторе Oracle
Старший сын: оптимизатор, основанный на системе правил
На чем сказывалась негибкость оптимизатора, основанного на системе правил?
Оптимизатор, основанный на системе правил, и компилятор С: взгляд эксперта
Новорожденный: стоимостный оптимизатор
Процесс взросления стоимостного оптимизатора
Добрые старые времена: оптимизатор, основанный на системе правил
Возврат стоимостного оптимизатора
Стоимостный оптимизатор взрослеет
Установки параметров инициализации для использования оптимизатора Oracle
Что такое "подсказка"?
Какой оптимизатор используется?
Вычисление статистики объектов
Зачем требуется собирать статистику?
Как вычислять статистику?
Сколько требуется статистики?
Различные методы вычисления статистики объектов
Как часто нужно вычислять статистику?
Вопросы, связанные с вычислением статистики объектов
Оптимальные стратегии индексирования
Что такое индекс?
Когда использовать индексы?
Как строить оптимальные индексы
Когда необходимо перестраивать индексы?
Какими методами соединения и когда можно пользоваться
Как не стоит писать SQL
Начало оптимального SQL
Как способствовать созданию оптимальных SQL
4 Настройка приложения — отслеживание "плохих" операторов SQL
Процесс настройки операторов SQL
Как отслеживать SQL?
Где расположен нужный файл трассировки и как его найти?
Выполнение tkprof для файлов трассировки
Интерпретация выходных результатов tkprof
Oracle — каков ваш план действий?
Как получить ПД Oracle?
У нас есть план, может ли кто-нибудь помочь его прочесть?
Что такое AUTOTRACE?
Часть III
Настройка экземпляра и базы данных
5 Настройка экземпляра — область коллективного пула
Системная глобальная область
Синтаксический анализ SQL: что происходит при нажиме на клавишу ENTER?
Жесткая и мягкая разборки
Разбирать или не разбирать: вот в чем вопрос
Параметры инициализации и коллективный пул
Настраиваем экзотическую SPA
Оставьте их дома
Фрагментация коллективного пула: проактивное управление ошибкой ORA-04031 151
Что вызывает фрагментацию коллективного пула?
Ошибка ORА-04031 в Oracle 7.3 и более поздних версиях
События ожидания, влияющие на область коллективного пула
6 Настройка экземпляра — буферный кэш базы данных
Что такое пятиминутное правило кэширования или теперь уже, наверное, десятиминутное?
Как работает буферный кэш базы данных
Управление буферным кэшем базы данных до появления OracleSi
Управление буферным кэшем базы данных в Oracle9i и последующих версиях
Конфигурирование буферных пулов
Пул по умолчанию
Пул повторного использования
Назначение объектов пулу
Использование опции cache
Анализ кэша буфера базы данных
Коэффициент попадания в кэш
Что находится в буферном кэше базы данных?
События ожидания, влияющие на буферный кэш базы данных
Параметры инициализации, влияющие на буферный кэш базы данных
7 Настройка экземпляра — буфер журнала обновлений
Конфигурируем буфер журнала обновлений
Параметры инициализации, влияющие на буфер журнала обновлений
События ожидания, влияющие на буфер журнала обновлений
Решение вопросов, связанных с буфером журнала обновлений
Прочая настройка экземпляра
Файлы журнала обновлений
Параметры инициализации для прочей настройки экземпляра
Настройка оптимизатора Oracle
Параметры инициализации, настраивающие поведение оптимизатора
8 Настройка базы данных
Выбор правильного размера блока базы данных
Как размер блока базы данных влияет на производительность
Как оптимально выбрать размер блока базы данных Oracle?
Изменение размера блока базы данных: основные вопросы
Малые размеры блоков против больших:
Конфигурирование параметров хранения уровня блока
Проектирование, конфигурирование и настройка табличных пространств
Метод четырех областей памяти при конфигурировании табличных пространств
Конфигурируем временные табличные пространства
Глобальные временные таблицы и временные табличные пространства
Конфигурирование локально управляемых табличных пространств
Секционирование базы данных для достижения лучшей производительности
Функциональные преимущества секционирования
Основные соображения при секционировании базы данных
Конфигурируемые параметры инициализации
Вопросы настройки для гибридных баз данных
Вопросы настройки для баз данных хранилищ данных
Часть IV
9 Настройка параллельных запросов
Что такое параллелизм
Когда использовать параллельные запросы
Как использовать параллелизм
Операторы SQL, выигрывающие от применения параллелизма
Параметры инициализации, влияющие на параллелизм
Взаимодействие между параметрами PARALLEL_MIN_SERVERS, PARALLEL_MAX_SERVERS и PARALLEL_MIN_PERCENT
Проектирование базы данных для параллелизма
Соображения о параллельном DML
PDML и конфигурирование сегментов отката
PDML и восстановление экземпляра
Ограничения и проблемы PDML
Мониторинг параллельных запросов
10 Настройка конкуренции
Проверяем Oracle на конкуренцию
Сегменты отката: почему, как и как много?
Многоверсионная согласованность по чтению
Как работает многоверсионная совместимость?
Создание и развенчание мифа о переносах
Обнаружение конкуренции за сегмент отката
Понимание использования сегментов отката
Как конфигурировать сегменты отката
Какой размер выбрать для сегментов отката?
Как избежать ошибки "ORA-01555.- Snapshot too old"
Проективное управление конкуренцией для временных сегментов
Конкуренция временных сегментов
Мониторинг использования временных сегментов в табличных пространствах
Часть V
11 Настройка ввода/вывода
Чем RAID не является
Почему нужно заботиться о RAID
Три основные концепции RAID
Что такое страйпинг
Что такое зеркалирование
Собираем все это вместе
Основы конфигурирования дисковых массивов
Основы страйпинга дисков
Шаги по созданию томов со страйпингом, часть 1
Конфигурирование ширины полосы страйпинга
Шаги по созданию томов со страйпингом, часть 2
Конфигурирование операционной системы
"Религиозные" дебаты: неформатированные устройства против файловых систем
Конфигурируем базу данных на оптимальное размещение
Разделение объектов, к которым идут одновременные обращения
Отделите данные от ассоциированных с ними индексов
Совместное существование табличных пространств отката и временных табличных пространств
Разделение "горячих" объектов в табличном пространстве
Как нужно распределять данные?
Параметры инициализации, влияющие На производительность ввода/вывода
RAID и базы данных Oracle: основные вопросы
Примеры конфигураций RAID
12 Настройка операционной системы
Настройка ОС: общие вопросы
Конфигурируем адекватную RAM для нашей системы
Разумный метод выделения памяти
Настройка буферного кэша файловой системы
Настройка пространства свопинга настраиваемой системы
Блокировка (закрепление) SGA Oracle в памяти
Настраиваем ядро UNIX
Блокировка SGA в памяти
Настройка демона подкачки страниц
Блокировка SGA в памяти
Настройка демона подкачки страниц
Блокировка SGA в памяти
Настройка буферного кэша файловой системы
Управление процессом настройки
Настройка Windows NT
Увеличение доступной Windows NT памяти
Уменьшение приоритета приложений переднего плана
Удаление неиспользуемых сетевых протоколов и переустановка порядка связывания
You can enhance Oracle performance by adjusting database applications, the database, and the operating system. Making such adjustments is known as tuning. Proper tuning of Oracle provides the best possible database performance for your specific application and hardware configuration.
Oracle8i Designing and Tuning for Performance contains information describing the features and functionality of the Oracle8i and the Oracle8i Enterprise Edition products. Oracle8i and Oracle8i Enterprise Edition have the same basic features. However, several advanced features are available only with the Enterprise Edition, and some of these are optional. For example, to use application failover, you must have the Enterprise Edition and the Parallel Server option.
For information about the differences between Oracle8i, Oracle8i Enterprise Edition, and Oracle8i Personal Edition, see Getting to Know Oracle8i.
This preface includes the following sections:
Code Examples
SQL and SQL*Plus statements appear separated from the text of paragraphs in a monospaced font. For example:
Example statements may include punctuation, such as commas or quotation marks. All punctuation in example statements is required. All SQL example statements terminate with a semicolon (;). Depending on the application, a semicolon or other terminator may or may not be required to end a statement.
Uppercase words in example statements indicate the keywords within Oracle SQL. When you issue statements, however, keywords are not case sensitive.
Lowercase words in example statements indicate words supplied only for the context of the example. For example, lowercase words may indicate the name of a table, column, or file.
Книга, которую вы держите в руках, является первым вводным учебником по описанию усовершенствованных методов оптимизации, а не попыткой вырастить еще одно поколение аналитиков, использующих старые, основанные только на здравом смысле методы анализа, которые вот уже более 10 лет загоняют,в тупик администраторов баз данных Oracle. Книга является сильно запоздавшим введением в тему производительности Oracle, порывающим с устоявшимися мифами о настройке, которые до сих пор бытуют на рынке.
"Oracle 101: настройка производительности" полезна по нескольким причинам. Это первая из вышедших из печати книг, в которой высший приоритет отдается задачам управления производительностью, что, как я полагаю, является самым необходимым для лиц, только начинающих обучение. Кроме того, она предоставляет начинающим аналитикам производительности необходимую информацию о работе ядра Oracle и поддерживающего эту работу стека технологий. Из этой книги вы сможете узнать, почему стоит потратить время на повторное создание (rebuilding) базы данных, в результате чего удается избежать ее фрагментации. Вы увидите, что основанные на избирательности строк оценки эффективности индекса являются ненадежными, а также прочтете о том, что даже 99-процентное попадание в буферный кэш не означает, что система работает с пиковой эффективностью. Гайя Кришна Вайдьянатха предложил мне еще на подготовительной стадии рассматривать его проект "Настройка производительности Oracle 101" как составную часть тех революционных изменений в производительности Oracle, которые я надеялась стимулировать созданием hotsos.com. Мы все слишком долго культивировали понятие "случайной производительности" (performance by accident). В конце 1999 г. Hotsos, сотрудничающая с нами компания Miracle A/S в Дании и несколько наших коллег во всем мире заявили о намерении создать более высокий стандарт качества и доступности информации для менеджеров Oracle. Думаю, что книга "Oracle 101: настройка производительности" станет необходимым первым шагом в осуществлении наших замыслов.
В каталоге Download Вы можете найти другие полезные материалы - утилиты, программы, документацию, исходники, электронные книги. Если Вы обнаружили неработающую ссылку, пожалуйста, помогите другим посетителям и администратору каталога Download - сообщите об этом редактору.
Прежде чем говорить об оптимизации производительности баз данных, нужно пояснить, каким показателем эта производительность измеряется, — тем более что в памяти многих людей, связанных с базами данных, еще жив показатель «время отклика», который многие привыкли считать универсальным мерилом производительности СУБД.
Проблема с так называемым временем отклика состоит в том, что оно, увы, относительно. Для конечного пользователя, системного администратора, сетевого администратора и администратора баз данных, оно разное и зависит не только от чистого времени отклика СУБД, но и от производительности кода бизнес-логики на сервере приложений, производительности веб-интерфейса, взаимодействия компонентов сетевой инфраструктуры, работы брандмауэра, балансировщика нагрузки и т. д. Поэтому для того, чтобы адекватно выражать производительность базы данных, надо пользоваться показателем Database Time, который выражает время, потраченное СУБД на выполнение конкретного вызова (запроса), с момента его поступления в базу данных и до момента выдачи последнего фрагмента выборки результатов. Более строгое определение Database Time — общее время, проведенное пользовательскими процессами в активном выполнении или активном ожидании выполнения вызовов СУБД.
Концепцию Database Time прекрасно иллюстрирует страница Top Activity в Enterprise Manager (Рисунок 1), где мы видим количество активных сессий и, в виде разноцветного графика,- распределение потраченного времени СУБД т.е. собственно Database Time.
Раньше процесс настройки СУБД был сродни блужданию в потемках — администраторы пытались применять те или иные настроечные параметры, которые, по их мнению, могли повлиять на поведение оптимизатора, варьировали их значения, ориентируясь на отзывы конечных пользователей, которые далеко не всегда замечали изменения в лучшую или худшую сторону.
Современная методология настройки производительности, которая называется Find-Fix-Validate (Рисунок 2), позволяет точно диагностировать проблемы производительности с помощью инструментальных средств анализа производительности СУБД, решать их с использованием средств автоматической настройки, входящих в набор Tuning Pack, и проверять корректность принятых мер средствами тестирования, входящими в пакет Real Application Testing.
Новейшие версии СУБД Oracle, в том числе, конечно, Oracle Database 12c, буквально “облеплены датчиками” производительности и помимо своей основной работы (выполнения запросов, оптимизации, выдачи результатов, координации действий пользователей) постоянно сообщают о том, чем они занимаются, — публикуют события ожидания и временные характеристики вызовов. Поэтому всегда точно известно, сколько времени у СУБД ушло на ту или иную активность.
Надо сказать, что компания Oracle к реализации этой возможности подошла довольно элегантно и не стала изобретать собственный язык для доступа к диагностической информации, а оформила ее в виде специальных таблиц Базы Данных, доступ к которым можно получить с помощью языка SQL и графического интерфейса Enterprise Manager 12c. Таким образом, мониторинг, диагностика и поиск первопричин различных проблем для пользователей СУБД Oracle максимально упрощен.
Таблиц, которые содержат необходимую информацию, немало, уже несколько сотен, статистика в них довольно разрозненная и к тому же накопительная. Но, разумеется, нет необходимости сравнивать показатели вручную, поскольку для сравнительного анализа полученных данных разработан специальный диагностический репозиторий Automatic Workload Repository (AWR), который периодически (по умолчанию – ежечасно) снимает с таблиц диагностическую информацию — различные классы ожидания, метрики, основную статистику, статистику по SQL-запросам и так далее. Данные AWR сохраняются в БД и используются для диагностических отчетов. Технология AWR Baselines позволяет создавать эталонные интервалы времени, сопоставляя бизнес-операции, например закрытие операционного дня, отчетного периода, расчета зарплаты и т. п. с интервалами снимков AWR и периодически проводить сравнительный анализ производительности для выбранного интервала. Эта технология позволяет быстрее делать анализ вариаций нагрузки и облегчает диагностику производительности базы данных.
AWR-отчет по умолчанию сохраняется в формате HTML (Рисунок 3, слева). Есть и новый тип отчета – он называется Performance Hub, – который отображает статистику работы БД в удобной и наглядной графической форме (Рисунок 3, справа).
В Oracle Database 12.1.0.2 появилась еще одна новая, очень удобная форма AWR-отчета — Active-HTML-отчет. Он сочетает в себе возможности навигации и детализации Enterprise Manager для оффлайн-анализа, его можно сохранять и отправлять по почте, как другие активные отчеты, для его просмотра не требуется Enterprise Manager.
AWR Warehouse — центральный репозиторий для долговременного хранения AWR-данных. Он хранится в отдельной, выделенной Базе Данных — таким образом, можно увеличить период хранения снимков AWR хоть до бесконечности, чтобы анализировать хронологию, посмотреть, что было год назад, 2 года назад и т. д. AWR Warehouse интегрирован во все экраны производительности БД Enterprise Manager и позволяет получить сравнительный отчет за любой период времени.
Функциональность Active Session History (ASH) — средство мониторинга, которое появилось в Oracle Database 10g. ASH ежесекундно делает снимки состояния активных сессий и записывает их в специальную структуру памяти (см. Таблица 1). Практически это мониторинг в режиме реального времени. В Enterprise Manager 12c появился очень удобный графический интерфейс к Active Session History, который называется ASH Analytics.
Встроенный в базу данных советчик Automatic Database Diagnostic Monitor (ADDM) помогает интерпретировать диагностику и находить первопричину плохой производительности. Сам по себе AWR-отчет — это достаточно объемный документ, в котором легко запутаться. ADDM помогает интерпретировать статистику, сохраненную в Workload Repository и находить первопричину проблем. ADDM анализирует потребление Database Time, соотносит его с активностью сессий и создает отчет с конкретными рекомендациями. Важно, что ADDM не просто ставит вас в известность о проблемах производительности БД, а выявляет причины проблем и ранжирует их по степени влияния.
Наконец, в Oracle Database 12 появилась улучшенная версия Real-Time ADDM, которая автоматически запускается при превышениях пороговых значений ряда параметров, например: количества сессий, чрезмерном потреблении процессорного времени, конфликтах и прочих событиях, понижающих производительность базы данных. Новый Real-Time ADDM отличается возможностями автоматического обнаружения и анализа проблем в реальном времени, автоматической диагностикой серьезных проблем производительности.
ADDM-отчеты сохраняются в AWR-репозитории для исторического анализа. Real-Time ADDM – это, также, средство аварийного мониторинга, которое при невозможности обычного соединения с базой данных — если она «намертво зависла» — может выполнить специальное диагностическое соединение и снять диагностику прямо из памяти СУБД. Встроенный в базу данных советник поможет в диагностике проблем и определит их причину. Всегда лучше начинать анализ именно с ADDM-отчета, потому что он содержит информацию из AWR и из ASH в удобном для первичного анализа производительности виде.
Для детального анализа выполнения SQL-запросов в Enterprise Manager имеется окно Real-Time SQL Monitoring, которое позволяет следить за тем, как выполняется конкретный SQL-запрос, по какому он выполняется плану и на каком шаге плана выполнения тратится больше всего ресурсов. В Real-Time SQL Monitoring есть очень интересные показатели, такие как Actual Rows и Estimated Rows. С помощью Real-Time SQL Monitoring можно также контролировать выполнение PL/SQL-процедур.
Практика показывает, что большинство проблем производительности Баз Данных возникают из-за SQL-запросов — либо некорректно написанных, либо, по разным причинам, неэффективно выполняющихся. Неполная статистика, новая версия оптимизатора, неправильные параметры, конфликты — есть тысяча причин, по которым SQL-запросы могут выполняться некорректно. Инструмент Oracle SQL Tuning Advisor дает рекомендации по повышению производительности проблемных SQL-запросов, используя все тот же оптимизатор SQL-запросов Cost Based Optimizer (CBO), но в специальном настроечном режиме, давая CBO больше времени на всесторонний анализ и проверку. В процессе анализа используются реальные и исторические AWR-данные и выявляются альтернативные планы выполнения запросов. Если при использовании параллельного SQL-профиля выполнение SQL-запроса ускорится в два или более раз, SQL Tuning Advisor порекомендует и его. Также, SQL Tuning Advisor проверит различные варианты рекомендаций и вы получите в Enterprise Manager отчет о том, какой SQL-запрос проанализирован и какие рекомендации по его настройке предложены.
Новая версия инструмента SQL Access Advisor, которая появилась в Oracle Database 12c, позволяет значительно сократить время анализа для больших SQL-нагрузок. SQL Access Advisor анализирует не единичные SQL-предложения, а SQL-нагрузку за определенный период времени. Теперь он работает гораздо производительнее и в десятки раз быстрее анализирует объекты БД.
И SQL Access Advisor, и SQL Tuning Advisor имеют графический интерфейс в Enterprise Manager.
Инструмент SQL Performance Analyzer (SPA), входящий в Real Application Testing обеспечивает тестирование в Oracle Database 10.2, 11g и 12c, позволяет предсказать влияние системных изменений Базы Данных на время отклика SQL, определяет результаты производительности SQL для каждого тестового выполнения SQL-нагрузки, анализирует различия в производительности и сравнивает результаты производительности конкретных SQL-запросов. При этом он оказывает минимальное влияние на производительность рабочей системы при захвате SQL-нагрузки в SQL Tuning Set (STS).
Новая возможность SQL Performance Analyzer — SPA Quick Check в Enterprise Manager позволяет быстро проверить изменения, скажем, параметров оптимизатора, влияющих на планы запросов. Например, если у вас в наборе тысяча запросов, а планы, в результате, изменятся только у десяти из них, то SPA Quick Check сначала выявит эти десять запросов и проведет сравнительное тестирование именно для них.
Отдельно стоило бы поговорить о проактивном подходе т.е. о том, как не допускать возникновения проблем с СУБД — об управлении планами запросов, но это отдельная тема, выходящая за рамки данной статьи.
Чтобы больше узнать о настройке производительности, в первую очередь стоит изучить документацию к Oracle Database, для начала, документ «2 Day + Performance Tuning Guide», который дает обзор всего, что мы обсудили в рамках этой статьи. Руководство «Performance Tuning Guide» посвящено настройке производительности на уровне экземпляра базы данных. Настройка SQL-запросов детально описана в документе «SQL Tuning Guide». Еще один документ, «Testing Guide», представляет собой руководство по пакету Real Application Testing и различным возможностям использования SQL Performance Analyzer. Рекомендуем, также, пройти пятидневный учебный курс Oracle University, который называется «Oracle Database 12с: Performance Management and Tuning».
Syntax Diagrams and Notation
The syntax diagrams and notation in this manual show the syntax for SQL statements, functions, hints, and other elements. This section tells you how to read syntax diagrams and examples and write SQL statements based on them.
Keywords
Keywords are words that have special meanings in the SQL language. In the syntax diagrams in this manual, keywords appear in uppercase. You must use keywords in your SQL statements exactly as they appear in the syntax diagram, except that they can be either uppercase or lowercase. For example, you must use the CREATE keyword to begin your CREATE TABLE statements just as it appears in the CREATE TABLE syntax diagram.
Parameters
Parameters act as place holders in syntax diagrams. They appear in lowercase. Parameters are usually names of database objects, Oracle datatype names, or expressions. When you see a parameter in a syntax diagram, substitute an object or expression of the appropriate type in your SQL statement. For example, to write a CREATE TABLE statement, use the name of the table you want to create, such as EMP , in place of the table parameter in the syntax diagram. (Note that parameter names appear in italics in the text.)
This list shows parameters that appear in the syntax diagrams in this manual and examples of the values you might substitute for them in your statements:
The substitution value must be the name of an object of the type specified by the parameter.
The substitution value must be a character literal in single quotes.
The substitution value must be a condition that evaluates to TRUE or FALSE.
The substitution value must be a date constant or an expression of DATE datatype.
The substitution value can be an expression of any datatype.
The substitution value must be an integer.
The substitution value must be an expression of datatype ROWID.
The substitution value must be a SELECT statement contained in another SQL statement.
The substitution value must be an identifier for a SQL statement or PL/SQL block.
How This Book is Organized
Part One: Introduction to Tuning
This chapter provides an overview of tuning issues. It defines performance tuning and the roles of people involved in the process.
This chapter presents the recommended tuning method, and outlines its steps in order of priority.
Part Two: Application Design Tuning for Designers and Developers
This chapter describes the various types of application that use Oracle databases and the suggested approaches and features available when designing each.
This chapter discusses SQL processing, Oracle optimization, and how the Oracle optimizer chooses how to execute SQL statements.
This chapter shows how to use the SQL statement EXPLAIN PLAN , and format its output.
This chapter describes the use of the SQL trace facility and TKPROF , two basic performance diagnostic tools that can help you monitor and tune applications that run against the Oracle Server.
This chapter offers recommendations on how to use cost-based optimizer hints to enhance Oracle performance.
This chapter explains why statistics are important for the cost-based optimizer, and how to gather and use statistics.
This chapter describes how Oracle optimizes Structured Query Language (SQL) using the cost-based optimizer (CBO).
This chapter describes how to use plan stability (stored outlines) to preserve performance characteristics.
Part Three: Application Design Tools for Designers and DBAs
This chapter introduces the full range of diagnostic tools available for monitoring production systems and determining performance problems.
This chapter provides an overview of data access methods that can enhance performance, and warns of situations to avoid.
This chapter explains the use of shared SQL to improve performance.
This chapter provides an overview of Oracle Trace usage and describes the Oracle Trace initialization parameters.
This chapter describes views that are of the greatest use for both performance tuning and ad hoc investigation
This chapter provides an overview of performance factors in existing systems that have been properly designed.
This chapter describes the different methods in which read consistency is performed.
Part Four: Optimizing Oracle Instance Performance
This chapter describes how to identify and solve problems with CPU resources.
This chapter explains how to allocate memory to database structures. Proper sizing of these structures can greatly improve database performance.
This chapter explains how to avoid I/O bottlenecks that could prevent Oracle from performing at its maximum potential.
This chapter explains how to detect and reduce contention that affects performance.
This chapter introduces networking issues that affect tuning, and points to the use of array interfaces, out-of-band breaks, and other tuning techniques.
This chapter explains how to tune the operating system for optimal performance of Oracle.
This chapter explains how to tune recovery performance.
Читайте также: