Май 2005
Профессионалу администратору
Аруп Нанда
Oracle Database 10g:
20 наиболее
привлекательных для АБД возможностей
(Oracle Database 10g: The Top 20
Features for DBAs
by Arup Nanda)
Часть VIII
Источник: журнал Oracle Magazine,
OTN Technical Articles
(
http://www.oracle.com/oramag/webcolumns/windex.html),
http://otn.oracle.com/pub/articles/10gdba/index.html
В течение 20 недель Arup Nanda, “Лучший АБД 2003 года” по
версии журнала Oracle Magazine, рассказывал о наиболее, по его мнению,
привлекательных для администраторов баз данных возможностях Oracle Database
10g.
[От редакции OM/RE: мы продолжаем публикацию
переводов этих заметно окрашенных индивидуальной авторской интонацией небольших
по объему заметок, но удивительно емких по содержаниею и точных по существу
рассматриваемых вопроcов. В каждом выпуске журнала предполагается по две-три
“недели” от Arup Nanda. В этот раз мы предлагаем вниманию читателей "Неделю 17"
и "Неделю 18".]
План публикаций
Неделя
17
Автоматическое Управление Разделяемой Памятью
(Automatic Shared Memory Management)
Оригинальный текст: http://otn.oracle.com/pub/articles/10gdba/week17_10gdba.html
Испытывали ли Вы замешательство, пытаясь точно распределить
объем памяти, требуемый для различных пулов? Механизм Automatic Shared Memory
Management (Автоматическое Управление Разделяемой Памятью) позволяет
автоматически ассигновать память тем областям, которым она нужна больше всего.
Свежеиспеченный ли Вы администратор базы данных или искушенный
АБД со стажем, почти наверняка Вы встречали не менее чем однажды примерно
следующее сообщение об ошибке: ORA-04031: unable to allocate 2216 bytes of shared memory ("shared pool"... ...
<ORA-04031: нельзя выделить 2216 байтов разделяемой памяти ("shared pool"... >
или это:
ORA-04031: unable to allocate XXXX bytes of shared memory
("large pool","unknown object","session heap","frame")
или, возможно, такое:
ORA-04031: unable to allocate bytes of shared memory ("shared pool",
"unknown object","joxlod: init h", "JOX: ioc_allocate_pal")
Причина первой ошибки очевидна: память, выделенная
разделяемому пулу, недостаточна, чтобы выполнить пользовательский запрос. (В
некоторых случаях причиной может быть не непосредственно размер пула, а, скоре,
его фрагментация, которая является результатом чрезмерного парсинга (parsing
–синтаксический анализ предложения) из-за неиспользования связываемых
переменных. Хотя это – моя излюбленная тема, сейчас давайте сосредоточимся на
проблеме, которая лежит прямо под рукой.) Другие ошибки происходят от
недостаточного пространства в большом пуле (large pool) или Java-пуле,
соответственно.
Вы должны устранить эти ошибочные ситуации без каких-либо
связанных с приложением изменений. Каковы ваши возможности? Вопрос состоит в
том, как сделать доступной память для всех пулов, требуемых экземпляром Oracle.
Как разделить пирог?
Как Вы знаете, Системная Глобальная Область (SGA) экземпляра
Oracle состоит из нескольких областей памяти, включая буферный кэш (buffer
cache), разделяемый пул (shared pool), Java-пул, большой пул (large pool) и
буфера журнала redo log. Эти пулы занимают установленные объемы памяти в
пространстве памяти операционной системы; их размеры определены АБД в файле
параметров инициализации.
Четыре пула - кэш буферов блоков (db block buffer cache),
разделяемый пул, Java-пул и большой пул - занимает почти все пространство в SGA.
(По сравнению с другими областями буфер журнала redo log не занимает много
места, и поэтому несущественен для нашего здесь обсуждения.) Вы, как АБД, должны
гарантировать, что распределение памяти по соответствующим пулам достаточно.
Предположим, Вы решаете, что объемы этих пулов должны быть
соответственно: 2GB, 1GB, 1GB, и 1GB. Тогда Вы устанавливаете следующие
параметры инициализации, чтобы для экземпляра базы данных были, безусловно,
выделены пулы, требуемых размеров.
db_cache_size = 2g
shared_pool_size = 1g
large_pool_size = 1g
java_pool_size = 1g
Теперь поближе посмотрим на эти параметры. Действительно ли
эти значения точны?
Я уверен, что Вы как бы сомневаетесь. В реальной жизни никто
не может определить эти пулы безукоризненно точно. Слишком сильно они зависят от
обработки данных в базе и время от времени от характера изменений процессов
обработки.
Вот некий примерный случай. Скажем, Вы имеете типичную, как бы
"преимущественно" OLTP базу данных и выделили для буферного кэша меньше памяти,
чем если бы база была исключительно OLTP (которых и существуют-то совсем
немного). В один прекрасный день несколько ваших пользователей независимо друг
от друга решили выполнить сканирование очень больших таблиц, чтобы получить
итоговые отчеты. Хотя база данных Oracle9i дает Вам возможность изменить
распределение интерактивно, но поскольку общая доступная физическая память
ограничена, Вы решаете кое-что прирезать из большого пула и Java-пула:
alter system set db_cache_size = 3g scope=memory;
alter
system set large_pool_size = 512m scope=memory;
alter system set
java_pool_size = 512m scope=memory;
Это решение некоторое время прекрасно работает, но когда
начинаются ночные работы RMAN, которые используют большой пул, этот пул
немедленно аварийно замыкается накоротко. И, спасая ситуацию, Вы снова
добавляете память в большой пул из кэша буферов данных.
Заканчиваются работы RMAN, но затем запускается пакетная
программа, которая экстенсивно использует Java, и, следовательно, Вы начинаете
видеть ошибки, связанные с Java-пулом. Поэтому Вы (снова) перераспределяете
пулы, чтобы согласовать требования Java-пула и кэше буферов данных:
alter system set db_cache_size = 2G scope=memory;
alter
system set large_pool_size = 512M scope=memory;
alter system set
java_pool_size = 1.5G scope=memory;
Следующим утром возобновляются оперативные работы OLTP и цикл
повторятся снова и снова!
Первой альтернативой этому порочному циклу является установка
постоянных максимальных требуемых каждого пула значений. Однако, сделав это, Вы
можете установить общий объем SGA больше, чем доступно памяти, и, таким образом,
увеличить риск свопинга (swapping – подкачка) и пейджинга (paging – листание)
памяти, когда расходуется меньше памяти, чем соответствует каждому пулу. Ручной
метод перераспределения, пусть непрактичный, выглядит все же довольно
приемлемым.
Другой альтернативой является установка приемлемых минимумов
значений. Однако когда требования растут, а память не доступна,
производительность страдает.
Обратите внимание, что во всех этих примерах общий объем
памяти, выделенной SGA, оставался тем же самым, а распределение памяти пулов
изменилось на основании немедленных приказаний. А может быть было бы лучше, если
RDBMS мог автоматически почувствовать потребность пользователей и соответственно
перераспределить выделение памяти?
Механизм Automatic Shared Memory Management (Автоматическое
Управление Разделяемой Памятью) в Oracle Database 10g именно это и
делает. Вы можете задать общий размер SGA, а затем установить параметр по имени
SGA_TARGET, который разрешает полный размер SGA. Конкретные пулы в пределах SGA
будут динамически сконфигурированы на основании рабочей нагрузки. Все, что
необходимо, - это ненулевое значение параметра SGA_TARGET, чтобы разрешить
автоматическое распределение памяти.
Установка Автоматического Управления Разделяемой Памятью
Давайте посмотрим, как это работает. Сначала определим общий
размер SGA. Это значение можно оценить, исходя из того, сколько памяти
распределено прямо сейчас. SQL> select sum(value)/1024/1024 from v$sga;
SUM(VALUE)/1024/1024
--------------------
500
Прямо сейчас текущий общий размер SGA составляет
приблизительно 500MB, что станет значением SGA_TARGET. Далее, сделаем запрос:
alter system set sga_target = 500M scope=both;
Это решение устраняет надобность установки индивидуальных
значений для пулов; соответственно, нужно выставить в нуль их значения в файле
параметров или полностью их удалить.
shared_pool_size = 0
large_pool_size = 0
java_pool_size = 0
db_cache_size = 0
Перезагрузите (recycle) базу данных, чтобы значения вступили в
силу.
Этот ручной процесс может быть реализован с помощью утилиты
Enterprise Manager 10g. На домашней странице базы данных выберите
закладку "Administration", а затем "Memory Parameters". Для сконфигурированных
вручную параметров памяти будет отображаться кнопка "Enable", наряду со
значениями всех вручную сконфигурированных пулов. Щелкните по кнопке "Enable",
чтобы включить механизм Automatic Shared Memory Management. Enterprise Manager
сделает все остальное.
После того, как сконфигурировано автоматическое распределение
памяти, следующими запросами можно проверить размеры пулов: SQL> select current_size from v$buffer_pool;
CURRENT_SIZE
------------
340SQL> select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;
POOL MBYTES
------------ ----------
java pool 4
large pool 4
shared pool 148
Как Вы можете видеть, все пулы были автоматически
конфигурированы, исходя из общего заданного размера в 500MB. (См. Рисунок 1.)
Размер буферного кэша - 340MB, Java-пула - 4 MB, большого пула - 4 MB и
разделяемого пула - 148MB. Вместе они занимают общий объем (340+4+4+148) =
496MB, что приблизительно составляет тот же объем, что и прежняя SGA - 500MB.
Рисунок 1: Начальное распределение пулов
Теперь предположим, что доступная Oracle хост-память уменьшена
с 500MB до 300МB, что вынуждает нас уменьшить размер общего пространства SGA. Мы
можем отразить это изменение, сократив размер SGA.
alter system set sga_target = 300M scope=both;
Теперь, проверяя пулы, мы видим, что:
Общий занятый объем: 240+4+4+44 = 296MB, что близко к
требуемым 300MB. Обратите внимание, что пулы были автоматически
перераспределены, когда был изменен параметр SGA_TARGET, как показано на Рисунке
2.
Рисунок 2: Перераспределение пулов после
сокращения SGA до размера 300MB
Размер пулов является динамическим. Основываясь на рабочей
нагрузке, пулы, приспосабливаясь к требованиям, расширяются или сжимаются, чтобы
сделать возможным расширение в другом пуле. Эти расширения или сокращения
происходят автоматически без вмешательства АБД, в отличие от примера в начале
этой статьи. Возвращаясь на мгновение к прежнему сценарию, предположим, что
после начального распределения стартуют работы RMAN, заявляя потребность в
увеличенном большом пуле; большой пул расширится с 4MB до 40 MB, чтобы
разместить запрос. Эти дополнительные 36MB будут отрезаны от буферов данных, и
количество буферов блоков данных станет меньше, как показано в Рисунке 3.
Рисунок 3: Перераспределенные пулы после
требования на увеличение большого пула
Измененные размеры пулов основаны на рабочей системной
нагрузке, так что пулы не могут иметь размер по сценарию самого плохого случая,
- они автоматически корректируют свой росту по требованию. Кроме того, общий
размер SGA всегда находится в пределах максимального значения, указанного
SGA_TARGET, так что нет никакого риска нарушений пропорций памяти (что ведет к
свопингу и пейджингу). Вы можете динамически увеличить SGA_TARGET до абсолютного
максимума, указанному настраиваемым параметром SGA_MAX_SIZE.
Какие Пулы Не затрагиваются?
Некоторые пулы в SGA не подчиняются динамическому изменению
размеров и должны быть определены явно. Среди них буферные пулы для
нестандартных размеров блока и те, у которых (не по умолчанию) заданы параметры
KEEP или RECYCLE. Если ваша база данных имеет размер блока 8К, и Вы хотите
сконфигурировать пулы блоков размером 2К, 4К, 16К и 32К, то их нужно
устанавливать вручную. Их размеры останутся постоянными; они не будут сжиматься
или расширяться, согласно нагрузке. Вам нужно учитывать этот фактор при
использовании буферов множественных размеров и пулов типов KEEP и RECYCLE. Кроме
того, буфер журнала не подчиняется настройке памяти. Установленное значение
параметра log_buffer является постоянным, независимо от рабочей нагрузки. (В
10g в SGA может также быть определен новый тип пула: пул Streams
(Потоки), устанавливаемый параметром streams_pool_size. Этот пул также не
подпадает под автоматическую настройку памяти.)
Это повод для интересного вопроса. Что происходит, если Вы
нуждаетесь в пулах блоков с размерами не по умолчанию, и все же хотите управлять
другими пулами автоматически?
Если Вы определяете любой из не-авто-настраиваемых параметров
(например, db_2k_cache_size), их общий размер вычитается из значения SGA_TARGET,
чтобы вычислить автоматически настраиваемые значения параметров так, чтобы
полный размер SGA остался постоянным. Например, представим, что значения заданы
следующим образом:
sga_target = 500M
db_2k_cache_size = 50M
и остальные
параметры пулов отброшены. Общий пул буферов по 2К составляет 50MB, что
оставляет 450MB для автонастраиваемых пулов: пула буферов с заданным по
умолчанию размером блока (db_cache_size), разделяемого пула, Java-пула и
большого пула. Когда ненастраиваемый параметр типа пула буферов с размером блока
2К динамически корректируется таким способом, что затрагивается размер
настраиваемой части, она, настраиваемая часть, согласовывается. Например,
увеличение значения db_2k_cache_size до 100MB с 50MB оставляет только 400MB для
настраиваемых параметров. Поэтому настраиваемые пулы: разделяемый, большой, Java
и пул буферов, заданных по умолчанию, автоматически сжимаются, чтобы уменьшить
свой совокупный объем до 400MB с 450MB, как показано в Рисунке 4.
Рисунок 4: Эффект от конфигурирования
неавтоматических буферных параметров
Но что происходит, если Вы имеете в наличии достаточно памяти
или риски, описанные выше, не столь существенны? В этом случае можно выключить
автоматическое изменение размеров, не определяя параметр SGA_TARGET в файле
параметров, устанавливая его в нуль в этом файле или динамически изменяя его в
нуль по команде ALTER SYSTEM. Когда обнуляется SGA_TARGET, текущие значения
пулов автоматически становятся их параметрами.
Использование утилиты Enterprise Manager
Для управления этими параметрами можно также использовать
утилиту Enterprise Manager 10. На домашней странице базы данных щелкните по
гиперсвязи "Memory Parameters" (Параметры Памяти), что покажет Вам экран,
подобный показанному на Рисунке 5.
Рисунок 5: Наладка Automatic Shared Memory
Management в Enterprise Manager
Обратите внимание, какие пункты окружены красными овалами:
база данных работает в режиме Automatic Shared Memory Management и общий размер
SGA - 564MB, то же самое значение указано в параметре SGA_TARGET. Вы можете
здесь его изменить и щелкнуть на кнопке Apply, чтобы принять значения;
настраиваемые параметры автоматически приспособятся.
Определение Минимума для Каждого Пула
Предположим, что SGA_TARGET установлен в 600MB и различные
пулы были распределены автоматически:
|
Пул |
Размер (MB) |
|
Buffer (Буферный) |
404 |
|
Java |
4 |
|
Large (Большой) |
4 |
|
Shared (Разделяемый) |
148 |
Глядя на это, можно было бы сказать, что заключить, что
размеры Java- и большого пулов в 4MB несколько неадекватны; эти значения будут
необходимо увеличены во времени выполнения. Но Вы можете захотеть, чтобы эти
пулы начинали с более высоких значений, по крайней мере, скажем, 8MB и 16MB,
соответственно. Это можно сделать, явно определяя значения этих пулов в файле
параметров, или же динамически, используя ALTER SYSTEM, как показано ниже.
alter system set large_pool_size = 16M;
alter system set
java_pool_size = 8M;
Проверяя пулы теперь, можно видеть: SQL> select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;
POOL MBYTES
------------ ----------
java pool 8
large pool 16
shared pool 148
SQL> select current_size from v$buffer_pool;
CURRENT_SIZE
------------
388
Перераспределение пулов показано ниже:
|
Пул |
Размер (MB) |
|
Buffer (Буферный) |
388 |
|
Java |
8 |
|
Large (Большой) |
16 |
|
Shared (Разделяемый) |
148 |
Обратите внимание, что Java- и большой пулы реконфигурированы
до 8MB и 16MB, соответственно, и что для удержания общего объема SGA менее 600MB
буферный пул уменьшился до 388MB с 404MB. Конечно, эти пулы находятся под
управлением Automatic Shared Memory Management. Поэтому они расширятся или
сожмутся, согласно требованиям. Значения, которые Вы определили явно, показывают
самый низкий уровень пула; пулы никогда не уменьшатся ниже этого предела.
Заключение
Требования к объемам памяти различных SGA пулов Oracle не
являются статическими. Скорее, они постоянно изменяются, основываясь на
системном спросе. Automatic Shared Memory Management в Oracle Database
10g позволяет АБД управлять памятью системы более эффективно, динамически
перераспределяя ресурсы туда, где они необходимы больше всего, сохраняя при этом
предписанный максимум, чтобы предотвратить пейджинг и свопинг. Более эффективное
управление памятью также ведет к меньшим требованиям к объемам памяти, что может
сделать более эффективными сравнительно скудные аппаратные средства ЭВМ.
За дополнительной информацией о Automatic Shared Memory
Management, см. Главу
7 "Oracle
Database Performance Tuning Guide" .
Неделя 18
Автоматический Монитор Диагностики Базы Данных и Советчик
Настройки SQL
(ADDM and SQL Tuning Advisor)
Оригинальный текст: http://www.oracle.com/technology/pub/articles/10gdba/week18_10gdba.html
Получите помощь по настройке SQL из первых рук:
непосредственно от Oracle Database! Заставьте запрос работать без перебоев,
используя SQL Profiles, и узнайте, как использовать ADDM, чтобы быстро и легко
решать обычные проблемы производительности.
Это был тихий день: никаких особых проблем в базе данных,
никаких причин для расстройства. Вы были почти расслаблены; наконец-то есть
время, чтобы доделать кое-какие важные задачи типа настройки параметров RMAN или
настройки множественных размеров блоков.
Внезапно в вашей каморке появляется разработчик. Его
SQL-запрос тратит на выполнение слишком много времени. “Не будете ли Вы столь
любезны” - спрашивает он – “и настроить запрос ASAP, чтобы он "вел себя" как
можно быстрее?”
Да, Вы расслабились слишком рано. Ваш первоначальный план
должен был занять некоторое время, реализовав стратегические решения, которые
сделали бы вашу базу данных лучше, быстрее и более безопасной, скажем, за счет
обеспечения восстанавливаемости базы данных, расширения основных технологий, или
исследования обновлений защиты. Вместо этого, Вы потратите весь следующий день,
фокусируясь на тактических действиях типа настройки SQL, что не оставит ни
крошки времени для выполнения стратегического плана.
Как стратегический АБД, Вы хотите полностью освободиться от
мирских хозяйственных работ и всецело сфокусироваться на будоражащих мысли
областях. А хорошо было бы обзавестись АБД-ассистентом, кто выполнит мирские
работы за Вас?
В Oracle Database 10g Вы имеете такого АБД-ассистента в
виде ADDM (Automatic Database Diagnostic Monitor – Автоматический Монитор
Диагностики Базы Данных), своего рода автоматизированного АБД, который неустанно
прокручивает статистику производительности базы данных, идентифицирует
критические параметры, анализирует SQL-предложения и в результате предлагает
различного назначения советы, как улучшить производительность, часто работая
вместе с другими "advisors" ("советчиками") типа SQL Tuning Advisor. В этой
статье Вы найдете краткий обзор того, как этот процесс работает.
ADDM (Automatic Database Diagnostic Monitor - Автоматический
Монитор Диагностики Базы данных)
В статье “Недели 6”
Вы проходили AWR (Automatic Workload Repository – Автоматизированный Репозиторий
Рабочей Нагрузки), который равномерно собирает за регулярные интервалы времени
детализированные связанные с производительностью базы данных статистические
данные (metrics – метрики), известные как снимки (snapshots). После того, как
очередной снимок сформирован, вызывается ADDM, чтобы полностью проанализировать
данные и метрики, выявить различия между снимками и затем рекомендовать
необходимые действия. Как я упоминал ранее, после обнаружения проблемы ADDM
может в свою очередь вызвать других советчиков (типа SQL Tuning Advisor), чтобы
предложить рекомендации для улучшения работы.
Вместо того, чтобы объяснять его функциональность на словах,
позвольте мне непосредственно показать Вам, как работает ADDM. Предположим, Вы
пытаетесь продиагностировать необъясненную проблему производительности. На примере
нашего вводного курса Вы узнали, что хотя SQL-предложения должны были быть настроены
и, тем не менее, что SQL-предложения были проблемой. В реальной жизни Вы,
однако, скорее всего не будете иметь столько полезной информации.
Чтобы в 10g поставить диагноз, Вы выбираете снимки в
соответствующем интервале для дальнейшего детального углубленного анализа. На
домашней странице базы данных в утилите Enterprise Manager 10g Вы
выбираете "Advisor Central" и затем нажимаете ссылку "ADDM", по которой
отображается страница, подобно приведенной на Рисунке 1.
Рисунок 1: Создание ADDM задачи
В этой странице Вы можете создать задачи, которые будут
проанализированы ADDM. Вы знаете, что проблемы производительности имели место в
11 часов, поэтому выберите снимки, которые попали в этот диапазон, обозначенный
значениями "Period Start" ("Начало Периода") и "Period End" ("Конец Периода").
Вы можете также кликнуть по иконкам, изображающим камеру, чтобы указать начало и
конец интервала снимков, как показано в красных овалах на рисунке. После выбора
интервала нажмите кнопку "OK", по которой отобразится страница, подобно
приведенной на Рисунке 2.
Рисунок 2: Полученные ADDM сведения
На этом интервале ADDM идентифицирует две критические
связанные с производительностью проблемы: некоторые SQL-предложения потребляют
существенное процессорное время, что приводит к существенному замедлению работы
базы данных. Основываясь на этих результатах, ADDM рекомендует настройку
SQL-предложений инструкций, как подсвечено в рисунке.
Если Вы кликните по конкретным строкам, ADDM отобразит еще
больше подробностей. Например, после щелчка по обнаруженной проблеме отобразится
страница, подобно приведенной на Рисунке 3.
Рисунок 3: Детализация ADDM-результатов
Здесь Вы можете видеть, что какое конкретное SQL-предложение
вызывает эту проблему. ADDM рекомендует подвергнуть это SQL-предложение полному
анализу посредством SQL Tuning Advisor, как упомянуто в секции "Action"
("Действия"). Вы можете немедленно выполнить эту рекомендацию, нажав кнопку
рядом, что вызовет SQL Tuning Advisor.
На рисунке 2 Вы, возможно, заметили кнопку с именем "View
Report" ("Просмотр отчета"). В дополнение к предоставлению рекомендаций на
конкретных web-страницах, ADDM также может создать отчеты в виде открытого
текста для более быстрого универсального анализа. Листинг 1 показывает
всеобъемлющие рекомендации, сделанные для нашего примера в виде открытого
текста. Обратите внимание, как отчет представляет соответствующие подробности
типа рассматриваемого SQL-предложения, его хеш-значения и так далее.
Идентификатор (ID) SQL может использоваться для независимого анализа посредством
SQL Tuning Advisor, вызываемого на странице в утилите Enterprise Manager или
через командную строку.
ADDM вызывается после того, как сформирован очередной
AWR-снимок, поэтому доступны для изучения рекомендации, основанные на смежных
снимках. Следовательно, Вам не надо создавать ADDM-задачу, как показано выше,
если диапазон анализа - только два смежных снимка. Если надо выполнить анализ в
диапазоне несмежных снимков, то необходимо создать ADDM-задачу.
Имейте в виду, что это не все, что может сделать ADDM; есть
еще много видов анализа и рекомендаций, применяемых для управления памятью,
управления сегментами, redo/undo (управление механизмами отката) и так далее,
как Вы видели в предыдущих статьях. Поскольку в этой отдельной краткой статье
невозможно представить полный спектр функциональных возможностей ADDM, мы здесь
сосредоточимся только на SQL Tuning Advisor. Давайте посмотрим, как он работает.
Анализ доступа посредством SQL Tuning Advisor
Во время типичной оптимизации выполнения оптимизатор
генерирует набор возможных путей доступа и выбирает среди них "наименее
затратный", основанный на статистике по объектам. Однако в этот момент он не
имеет возможности узнать, может ли предложение быть настроено, являются ли
статистические данные свежими, можно ли создать индекс и так далее. Напротив,
механизм-советчик SQL Tuning Advisor может выполнить это размышление в качестве
"системного эксперта" ("expert system"). Действительно, оптимизатор может
ответить на вопрос: "Основываясь на доступных данных, какой путь является лучшим
для достижения результата?", тогда как SQL Tuning Advisor может ответить на
вопрос: "Основываясь на пожеланиях пользователя, что еще необходимо сделать,
чтобы повысить производительность?" Как следует ожидать, это "размышление"
потребляет ресурсы, скажем, CPU; поэтому SQL Tuning Advisor работает с
SQL-предложениями в Tuning Mode (Режиме Настройки), который можно установить во
время непиковых нагрузок. Этот режим вводится установкой параметров SCOPE и TIME
в функции при создании задачи настройки. И это является правильной практикой,
поскольку установка Tuning Mode переносится на период низкой активности базы
данных и регулярные пользователи не подвергаются существенному воздействию,
оставляя анализ на попозже.
Эту концепцию лучше всего объяснить на примере. Возьмем
случай, когда разработчик предоставил Вашему вниманию запрос, показанный ниже.
select account_no from accounts where old_account_no = 11
Это предложение не трудно настроить, но сделаем это, ради
наиболее простой иллюстративности. Есть два способа запустить советчика:
использовать Enterprise Manager или просто из командной строки.
Сначала давайте посмотрим, как использовать командную строку.
Мы запускаем советчика, вызывая поставляемый пакет dbms_sqltune. declare
l_task_id varchar2(20);
l_sql varchar2(2000);
begin
l_sql := 'select account_no from accounts where old_account_no = 11';
dbms_sqltune.drop_tuning_task ('FOLIO_COUNT');
l_task_id := dbms_sqltune.create_tuning_task (
sql_text => l_sql,
user_name => 'ARUP',
scope => 'COMPREHENSIVE',
time_limit => 120,
task_name => 'FOLIO_COUNT'
);
dbms_sqltune.execute_tuning_task ('FOLIO_COUNT');
end;
/
Этот пакет создает и выполняет задачу настройки по имени
FOLIO_COUNT. Затем Вы увидите результаты выполнения задачи (то есть, увидеть
рекомендации). set serveroutput on size 999999
set long 999999
select dbms_sqltune.report_tuning_task ('FOLIO_COUNT') from dual;
Вывод показан на Листинге 2. Внимательно посмотрите на эти
рекомендации; советчик говорит, что Вы можете улучшить производительность,
создав индекс на столбце OLD_ACCOUNT_NO. И более, советчик вычислил бы стоимость
запроса, если бы индекс был создан, сделав потенциальную экономию более
определенной и конкретной.
Конечно, рассматривая такой простой пример, Вы получили бы те
же результаты и ручным способом. Однако вообразите насколько полезным будет этот
инструмент для более сложных запросов, когда ручная экспертиза невозможна или
непрактична.
Настройка Промежуточного уровня: Реструктурирование Запроса
Предположим, что запрос немного более сложен: select account_no from accounts a
where account_name = 'HARRY'
and sub_account_name not in
( select account_name from accounts
where account_no = a.old_account_no and status is not null);
Советчик рекомендует следующее:
|
Оригинальный текст |
Перевод |
|
1- Restructure SQL finding (see plan 1 in explain plans
section)
-----------------------------------------
The optimizer could not unnest the subquery at line ID 1
of the execution plan.
Recommendation
-------------------
Consider replacing "NOT IN" with "NOT EXISTS" or ensure
that columns used on both sides of the "NOT IN" operator are declared "NOT
NULL" by adding either "NOT NULL" constraints or "IS NOT NULL" predicates.
Rationale
---------
A "FILTER" operation can be very expensive because it
evaluates the subquery for each row in the parent query. The subquery,
when unnested can drastically improve the execution time because the
"FILTER" operation is converted into a join. Be aware that "NOT IN" and
"NOT EXISTS" might produce different results for "NULL" values. |
1-Реструктурирование обнаруженного SQL (см., план 1 в
секции объяснения планов)
------------------------------------------
Оптимизатор не смог вложить подзапрос в строку 1 плана
выполнения.
Рекомендация
---------------------
Рассмотрите замену "NOT IN" на "NOT EXISTS" " или
сделайте, чтобы используемые столбцы с обеих сторон оператора "NOT IN"
были объявлены "NOT NULL" при помощи добавления или ограничения "NOT
NULL", или предикатов "IS NOT NULL
Объяснение
---------
Операция "FILTER" может быть очень затратной, поскольку
она оценивает подзапрос для каждой строки в родительском запросе.
Невложенный подзапрос может существенно улучшить время выполнения, потому
что операция "FILTER" конвертируется в объединение. Знайте, что "NOT IN" и
"NOT EXISTS" могут вернуть различные результаты для значений "NULL".
|
На сей раз советчик не рекомендует никаких структурных
изменений типа индексов, а разумно предположил, что правильный способ настроить
запрос – это заменить NOT IN на NOT EXISTS. Поскольку две конструкции подобны,
но не идентичны, советчик дает объяснение изменению и оставляет выбор за АБД или
за прикладным разработчиком, чтобы кто-то из них решил, допустима ли эта
рекомендация в этой среде.
Расширенная Настройка: SQL Profiles
Как известно, оптимизатор выбирает план выполнения запроса,
исследуя текущее состояние статистики объектов, которые упоминаются в запросе, а
затем вычисляет по методу наименьшей стоимости (least-cost method). Если в
запросе используется больше одной обычной таблицы, оптимизатор вычисляет вариант
наименьшей стоимости, исследуя статистику всех упоминаемых объектов, но он не
знает взаимоотношений между ними.
Например, предположим, что учетная запись, имеющая статус
DELINQUENT (лицо, прекратившее платежи), на балансе имеет менее $1,000. Запрос,
который соединяет таблицы ACCOUNTS (УЧЕТНЫЕ ЗАПИСИ) и BALANCES (БАЛАНСЫ),
сообщит о небольшом количестве строк, если во фразе фильтрации предикат равен
только DELINQUENT. Оптимизатор не знает эти сложные взаимоотношения, но советчик
их учитывает; он "собирает" ("assembles") эти взаимоотношения по данным и хранит
их в форме SQL Profile (SQL-профиль). Получая доступ к SQL Profile, оптимизатор
знает не только распределение данных в таблицах, но также и корреляцию данных
между ними. Эта дополнительная информация позволяет оптимизатору генерировать
наилучший план выполнения, что приводит к хорошо-настроенному (well-tuned)
запросу.
SQL Profiles устраняют надобность в ручной настройке
SQL-предложений, добавляя подсказки (hints) в код запроса. Тем самым, SQL Tuning
Advisor позволяет настроить пакетные приложения, не изменяя их код, - огромная
выгода.
Здесь главная особенность в том, что в отличие от статистики
объектов SQL Profile соответствует запросу, а не объекту или объектам. Другой
запрос, работающий с теми же самыми двумя таблицами ACCOUNTS и BALANCES - может
иметь другой профиль. Используя эту метаинформацию о запросе, Oracle может
улучшить производительность.
Если профиль может быть создан, он формируется в течение
сессии SQL Tuning Advisor, когда советник генерирует профиль и рекомендует,
чтобы Вы "Accept" ("Принятие") ето. Если профиль не принят, он не связывается с
предложением. Вы можете принять профиль в любое время, выдав примерно следующий
запрос: begin
dbms_sqltune.accept_sql_profile (
task_name => 'FOLIO_COUNT',
name => 'FOLIO_COUNT_PROFILE'
description => 'Folio Count Profile',
category => 'FOLIO_COUNT');
end;
Эта команда связывает профиль по имени FOLIO_COUNT_PROFILE,
сгенерированный ранее советчиком, с предложением, ассоциированным с задачей
настройки по имени FOLIO_COUNT, описанный в более раннем примере. (Обратите
внимание, что хотя создать SQL Profile может только советчик, не АБД, только Вы
можете решить, когда его использовать.)
Вы можете увидеть созданные SQL Profiles в представлении
словаря DBA_SQL_PROFILES. Столбец SQL_TEXT показывает SQL-предложение, которому
назначен профиль; столбец STATUS указывает, разрешено ли использование профиля.
(Даже если профиль уже привязан к предложению, нужно разрешить его применение
для построения плана выполнения.)
Использование ADDM и SQL Tuning Advisor
В дополнение к трем случаям, описанным выше, SQL Tuning
Advisor также выявляет все участвующие в запросе объекты с отсутствующей
статистикой. Таким образом, советчик исполняет задачи четырех различных типов:
- Проверку, имеют ли объекты пригодную статистику, используемую для
корректный оптимизации
- Попытку перезаписать запросы для обеспечения лучшей производительности и
советы по перезаписи
- Проверку пути доступа для выявления возможности улучшения
производительности при добавлении дополнительных структур типа индексов и
материализованных представлений
- Создание SQL-профилей и присоединение их к определенным запросам.
Основываясь на этих возможностях, я могу думать о не менее
трех различных сценариях, в которых ADDM и SQL Tuning Advisor служат мощными
инструментальными средствами:
- Reactive Tuning (Реактивная Настройка)
: Ваше приложение внезапно
начинает исполняться плохо. Используя ADDM, Вы можете углубленно
детализировать проблему до SQL-предложения или набора предложений, как было
сказано в разделе о ADDM. Получив рекомендации ADDM, Вы можете запустить SQL
Tuning Advisor и устранить проблему.
- Proactive Tuning (Упреждающая Настройка
): приложение ведет себя
сравнительно приемлемо; однако, Вы хотите удостовериться, что выполнены все
необходимые задачи обслуживания, и понять, могут ли запросы быть настроены еще
больше. Вы запустили бы SQL Tuning Advisor в автономном режиме, чтобы
идентифицировать возможные альтернативные настройки.
- Development Tuning (Настройка Разработки
): есть много возможностей
настроить запрос во время тестирования кода при разработке, не откладывая это
на стадии QA (quality assurance - контроль качества) или промышленной
эксплуатации. Можно использовать вариант запуска советчика из командной
строки, чтобы настроить конкретные SQL-предложении прежде, чем они завершены в
разработке
Использование Enterprise Manager
Предыдущий пример был преднамеренно сформулирован, чтобы
проиллюстрировать использование SQL Tuning Advisor в режиме командной строки,
что является очень полезным для написания упреждающих сценариев подобных задач.
В большинстве же случаев Вы должны выполнять настройку, устраняя проблемы, о
которых сообщают конечные пользователи. В таких случаях удобно применять
Enterprise Manager 10g.
Несколько недель назад (на “Неделя 13”) Вам был представлен
обновленный интерфейс утилиты Enterprise Manager. Здесь будет представлено, как
его использовать для диагностики и настройки SQL: внизу экрана домашней страницы
базы данных щелкните по ссылке "Advisor Central", что отобразит страницу со
всеми советчиками. Затем щелкните по "SQL Tuning Advisor" наверху экрана, как
показано на рисунке 4.
Рисунок 4: Advisor Central в Enterprise Manager
Вы только что запустили SQL Tuning Advisor. Выберите со
следующей страницы "Top SQL" ("Главные SQL"), как показано на рисунке 5.
Рисунок 5: Советчики SQL Tuning Advisors
Это действие вызывает страницу, подобную показанной на рисунке
6, где прослеживается измерения по времени диаграммы, охватывающей различные
классы ожиданий.
Рисунок 6: Выбор Top SQL
Серая прямоугольная область внутри красного эллипса помещается
в центр диаграммы. Снова перемещением мыши установите прямоугольник в положение,
где высоки ожидания CPU (как показано в рисунке). В нижней части страницы
отобразятся соответствующие SQL-предложения в том же интервале, как показано в
рисунке 7.
Рисунок 7: Выбор SQL-предложений, согласно
активности
Как можно видеть, SQL-предложение, показанное наверху
(окруженное красным эллипсом), имеет самую высокую активность с максимальным
потреблением CPU. Щелкнув по ID предложения, можно увидеть детали этого
предложении, как показано на рисунке 8.
Рисунок 8: Детали SQL
На рисунке можно видеть точное SQL-предложение, которое
вызвало потребление CPU в этом периоде времени. Вы можете щелкнуть по отмеченной
в рисунке кнопке "Run SQL Tuning Advisor" ("Выполнить SQL Tuning Advisor"),
чтобы его запустить. Это действие откроет экран, подобный показанному на рисунке
9.
Рисунок 9: Планирование SQL Tuning Advisor
В планировщике советчика Вы можете определить тип задачи и
какие анализы должны быть сделаны. Например, на рисунке выше я выбрал
"comprehensive" ("всесторонний") анализ и немедленное выполнение советчика.
После завершения его работы Вы можете увидеть рекомендации советчика, как
показано на рисунке 10.
Рисунок 10: Рекомендации советчика
Этот только что описанный процесс подобен тому, что Вы видели
в варианте командной строки; однако, это более подходит к сценарию из реальной
жизни, когда Вы отреагировали на проблему, докопались до ее причины и приняли
рекомендации, как ее устранить.
Заключение
ADDM – это мощный инструмент, который снабжен "умственными
способностями" ("brains") для анализа метрик производительности и выработки
рекомендаций, основанных на лучших практиках и принятых закаленными
профессионалами Oracle методологиях, - все автоматически. Эта функциональность
может сказать АБД не только, что и почему случилось, но, самое важное, что
делать потом.
Более подробную информацию о ADDM и SQL Tuning Advisor можно
найти в:
Oracle
Database 10g: The Self-Managing Database,
The
Self-Managing Database: Guided Application & SQL Tuning,
глава
10 Oracle
Database 2 Day DBA
главы 6
и 13
руководства "Oracle
Database Performance Tuning Guide".
| 
