SharedPool (SP) это область памяти которая используется для кэширования таких структур как объекты data dictionary, sql код, планы. SP состоит из двух основных областей: library cache для кэширования кода and data dictionary cache для кэширования информации об объектах базы данных. Когда пользователь запускает sql то информация об объектах, которые этот sql задействует, выбирается из таблиц описывающих все объекты базы – data dictionary. Так определяется структура таблиц, проверяются имена колонок и их типы, проверяется есть ли у вас права на запрашиваемые операции. Такая операция называется парсингом (parsing). После парсинга, оптимизатор должен определить самый лучший план выполнения запроса. Это происходит путем анализа статистики (которая тоже хранится в dd) и составлением многих вариантов планов с их стоимостью, на основе которой выбирается лучший план выполнения. Такой план выполнения для каждого sql хранится в Library Cache. Как вы видите, эти операции требуют значительных ресурсов – это в первую очередь CPU и управляющие структуры памяти Эти ресурсы можно сэкономить, если всю эту информацию хранить в памяти и в случае повторного запроса, он выполнится значительно быстрее.
Как сразу видно, кэширование такой информации имеет смысл для сред в которых очень часто выполняются одинаковый, стандартизированный код, те OLTP (Online Transaction Processing). Пример OLTP запросов может быть следующий. Пользователь заполняет информацию о клиенте, и выбирает нужный счет из списка всех счетов конкретного клиента. Чтобы получить этот список пользователь направляет запрос в базу данных, где существует таблица со списком счетов. Такой запрос должен выполнится в доли секунды иначе пользователь не сможет нормально работать, потому что таких списков на одной форме может быть с десяток. Как видим, в таких системах очень критично время отклика, те то как быстро клиент получит ответ на свой запрос. Так как сами запросы часто короткие, то львиную долю времени может занимать парсинг, до 90% от общего времени выполнения. Поэтому, в таких системах, оптимизация кэширования имеет очень большое значение.
Однако существует другой тип систем, где кэширование посредством sp не так важно. Это системы отчетности или DWH. Время выполнения запросов в этих системах может быть очень большим, это может быть несколько минут, или даже часы. А время подготовки самого запроса относительно его выполнения очень небольшое и может достигать несколько секунд, что приемлемо. Если пользователь готов ждать минуты, то несколько секунд он просто не заметит. Поэтому улучшать и настраивать эту область кэширования просто не целесообразно. Кроме того, запросы в таких системах сложно стандартизировать.
Основная задача настройки SP это уменьшение парсинга. Когда объект не найден в кеше, нужно потратить очень большое количество ресурсов, на один простой sql может приходится 20-30 latch локировок и значительное количество CPU. В многопользовательских системах конкуренция за эти ресурсы может вызвать сильные замедления работы. Поэтому первая задача настройки SP, это уменьшение hard parsing..
Основные области настройки:
1. Повышение кооперативного использования объектов в памяти
a. Приведение стиля написания sql к одному стандарту. Это связано с тем, что два одинаковых по смыслу запроса, но отличающихся, допустим пробелом, oracle будет воспринимать как два разных запроса. Так происходит потому, что для поиска одинакового запроса используется hash функция на основе текста запроса.. Если текст отличается, то и соответственно результат hash функции будет различным.
b. Использование bind variables. Здесь нужно заметить, что Bind variables хорошо применимы для oltp систем, где большинство sql запросов стандартизированы, однако для DWH приложений их применение может оказаться невыгодным, тк отсутствие конкретных значений аргументов, влияет отрицательно на вычисление корректного плана выполнения.
2. Создание условий для хранения (кэширования) SQL в памяти
a. Выделение необходимого объема памяти. Это предотвращает удаление объектов из памяти по причине нехватки памяти. Однако слишком много памяти может привести к снижению производительности, по причине слишком длинного списка объектов (LRU).
b. Проведение обслуживающих работ в периоды низкой активности. DDL операции и такие как сбор статистики, truncate, grant вызывают инвалидацию объектов, что приводит к hard pars запросов использующих эти объекты.
3. Уменьшение фрагментации. SP рассчитан на кэширование небольших блоков, которые могут понадобится снова. Такие процессы как MTS, кэширование backupа и параллельного выполнения, используют большие блоки, причем эта информация используется только единожды. Это не соотв. назначению SP и поэтому снижает ее эффективность.
a. Использование LARGE_POOL для shared серверов, backup и parallel execution
b. Использование резервирования памяти
c. Уменьшение кол-ва анонимных PLSQL блоков
d. Закрепление (PIN) в памяти больших объектов
Что не помогает:
Простое увеличение памяти не помогает. Если просто увеличить размер памяти для SP и не решать проблемы с запросами – не приводить их к совместно использоваемому виду, то память рано или поздно заполнится снова, и при увеличенном объеме ситуация только усложнится, так как на работу с большим кешем требуется больше ресурсов.
Проблемы с Shared Pool можно чаще всего ожидать сразу после входа системы в production, когда bind variables не используются. Это связано с тем, что на тестовой среде, в однопользовательском режиме, все может прекрасно работать. Однако при повышении количества пользователей, наблюдается значительная конкуренция за ресурсы sp, время выполнения запросов увеличивается в разы и система практически останавливается.
Как видим, SP, является ключевой областью памяти в SGA, неоптимальная работа с которой может привести к значительному замедлению работы всей системы. Поэтому очень важно корректно использовать механизмы кэширования запросов как на стадии разработки, так и в ‘боевом’ режиме работы системы.
erudans.info 