Oracle увеличить shared pool
What is shared pool ?
Oracle keeps SQL statements, packages, object information and many other items in an area in the SGA known as the shared pool.
Terminologies
Literal SQL
A literal SQL statement is considered as one which uses literals in the predicate/s rather than bind variables where the value of the literal is likely to differ between various executions of the statement.
is used by the application instead of:
Hard Parse
If a new SQL statement is issued which does not exist in the shared pool then this has to be parsed fully. Eg: Oracle has to allocate memory for the statement from the shared pool, check the statement syntactically and semantically etc… This is referred to as a hard parse and is very expensive.
Soft Parse
If a session issues a SQL statement which is already in the shared pool AND it can use an existing version of that statement then this is known as a ‘soft parse’.
Sharable SQL
There are many things that determine if two identical SQL strings are truely the same statement (and hence can be shared) including:
- All object names must resolve to the same actual objects
- The optimizer goal of the sessions issuing the statement should be the same
- The types and lengths of any bind variables should be “similar”.
- The NLS (National Language Support) environment which applies to the statement must be the same.
Versions of a statement
As described in ‘Sharable SQL’ if two statements are textually identical but cannot be shared then these are called ‘versions’ of the same statement. If Oracle matches to a statement with many versions it has to check each version in turn to see if it is truely identical to the statement currently being parsed.
Library Cache and Shared Pool latches
The shared pool latch is used to protect critical operations when allocating and freeing memory in the shared pool.
The library cache latches protect operations within the library cache itself.
Shared pool size
It is defined by the shared_pool_size and shared_pool_reserved_size parameters in the Oracle database. If we are using Automatic Memory management like SGA_TARGET or MEMORY_TARGET, then we dont need to specify these parameters.Oracle automatically sets the memory size for the shared pool depending on the requirement.
If we are not using Automatic Memory management , we can use the below two parameter to control the size of the shared pool in oracle database
SHARED_POOL_RESERVED_SIZE specifies (in bytes) the shared pool space that is reserved for large contiguous requests for shared pool memory.
You can use this parameter to avoid performance degradation in the shared pool in situations where pool fragmentation forces Oracle to search for and free chunks of unused pool to satisfy the current request.
SHARED_POOL_SIZE specifies (in bytes) the shared pool space that is allocated for shared pool memory.
These parameter can be altered using alter system set command
We will need to bounce the oracle database to modified
Useful Queries for Shared Pool
1. View the Amount of Free Memory in the Shared Pool
Shared_pool flush
We can flush the shared pool using the command
It takes out all unusable SQL statement from it. The used ones are not flushed. The pinned one are also not flushed
shared_pool flush improves the performance if application issues a large amount of non-reusable SQL statements. The library cache becomes floored with non-reusable SQL and there will be significant slowdowns as Oracle futilely parses incoming SQL looking for a pre-parsed matching statement.
This operation shared_pool flush can be done online
In case of Oracle RAC,it has to be executed on all the nodes
How to Flush a Single SQL Statement from the Shared Pool
Useful SQL for looking at Shared Pool problems
Finding literal SQL
This helps find commonly used literal SQL which are not using bind variables.
Another way you might find these is to use the “plan_hash_value” column to group the examples ie:
Finding the Library Cache hit ratio
If the ratio of misses to executions is more than 1%, then try to reduce the library cache misses
Checking hash chain lengths:
This should usually return no rows. If there are any HASH_VALUES with high counts (double figures) then you may be seeing the effects of a bug, or an unusual form of literal SQL statement. It is advisable to drill down and list out all the statements mapping to the same HASH_VALUE.
Eg:SELECT sql_text FROM v$sqlarea WHERE hash_value= ;
and if these look the same get the full statements from V$SQLTEXT. It is possible for many literals to map to the same hash value.
Checking for high version counts:
“Versions” of a statement occur where the SQL is character for character identical but the underlying objects or binds etc.. are different as described in “Sharable SQL” above. High version counts can be disabled by setting SQLEXEC_PROGRESSION_COST to ‘0’.
Finding statement/s which use lots of shared pool memory:
where MEMSIZE is about 10% of the shared pool size in bytes. This should show if there are similar literal statements, or multiple versions of a statements which account for a large portion of the memory in the shared pool.
Если Вы движетесь в сторону интерактивного бизнеса, постоянно в Вашем внимании должны быть эти наиболее общие направления настройки.
В традиционной архитектуре клиент/сервер на плохо настроенной базе данных прикладные программы могут выполняться медленно, но в среде Web эти же приложения могут не выполняться вовсе. Представьте, например, что вы разрешили заказчикам или клиентам делать запросы к базе данных через ваш Web-сайт. Приложение, которое ранее было доступно всего лишь немногим пользователям вашего учреждения, теперь внезапно стало доступно, скажем, тысячам ваших заказчиков. Тем не менее, когда вы переводите ваши деловые действия на Web, основные принципы настройки базы данных остаются теми же самыми, независимо от того, как будет расти число запросов к вашей базе данных (пусть даже по экспоненте). Естественно, много связанных с эффективностью проблем являются характерными только для каждой конкретной системы, но я обнаружил, что если при настройке постоянно иметь в виду следующие четыре направления, это поможет быстро и просто улучшить производительность системы:
- Выделение экземпляру Oracle достаточного объема оперативной памяти.
- Хранение необходимых данных и других объектов в оперативной памяти.
- Выделение запросов, вызывающих проблемы.
- Настройка проблемных запросов.
В этой статье будут обсуждаться первые три направления. Настройка проблемных запросов - это отдельная тема, и о ней пойдет речь в следующейs статье.
Правило 1. Выделение экземпляру Oracle достаточного объема оперативной памяти
Выделение экземпляру Oracle достаточного объема памяти является весьма критичным. Необходимо иметь достаточное количество памяти, чтобы из-за ее нехватки не приходилось очищать буферный кэш от используемых данных, но все-таки не настолько много, чтобы это снижало общую производительность системы. В файле init.ora имеется много параметров, некоторые из которых можно использовать для распределения памяти вашей базе данных. Ниже приведены наиболее важные для управления распределением памяти параметры:
Используйте приведенный в листинге 1 запрос, чтобы найти текущие значения этих параметров. Увидеть значения этих параметров вы можете также, используя для этого средства Oracle Enterprise Manager (OEM).
Листинг 1. Нахождение установленных значений основных параметров настройки
DB_BLOCK_BUFFERS и DB_BLOCK_SIZE
Параметр DB_BLOCK_BUFFERS управляет системной глобальной областью (SGA), которую сервер базы данных Oracle использует для хранения и обработки данных в памяти. Когда потребители запрашивают данные, сервер помещает их в память, так, чтобы при последующих запросах пользователя (или пользовательского процесса) обеспечить более быстрый доступ к ним. Если значение параметра DB_BLOCK_BUFFERS слишком мало, то сервер слишком рано сбросит на диск самые старые из имеющихся в памяти данных. А это означает, что когда эти данные потребуются в следующий раз, серверу придется считывать их с диска, а не брать в оперативной памяти. Если же значение параметра DB_BLOCK_BUFFERS слишком велико, вашей системе может не хватить памяти для эффективного функционирования.
Вы можете выяснить, насколько эффективна настройка параметра DB_BLOCK_BUFFERS, измеряя коэффициент попаданий (hit ratio), значение которого говорит вам, какая часть данных, к которым осуществляют доступ потребители, находится в памяти. Чтобы найти его, вы можете использовать запрос, приведенный в листинге 2 . (Для графического представления этих данных, вы можете использовать модуль OEM Performance Manager.)
Листинг 2. Нахождение коэффициента попадания по чтению (read hit ratio).
select 1-(sum(decode(name, 'physical reads', value,0))/
(sum(decode(name, 'db block gets', value,0)) +
(sum(decode(name, 'consistent gets', value,0))))) * 100 "Read Hit Ratio"
Я считаю, что для коэффициента попаданий вполне приемлемо значение 95 процентов или выше. Если получившееся у вас значение ниже, чем 95 процентов, вам стоит скорректировать значение DB_BLOCK_BUFFERS. Хорошее эмпирическое правило для определения этого значения - назначить для DB_BLOCK_BUFFERS 25 процентов доступной оперативной памяти, хотя конкретный выбор может зависеть от размера вашей системы, объема доступной памяти и числа потребителей.
В дополнение к корректировке числа блоков данных, которые сервер хранит в памяти, вы можете изменить размер этих блоков, регулируя настройку параметра DB_BLOCK_SIZE. Этот параметр управляет количеством данных, которые база данных может считать в память в течение одной транзакции ввода/вывода. При работе с Oracle8, вы можете установить это значение равным 32Кбайт (в предыдущих версиях Oracle только 16Кбайт), но вы должны реорганизовать базу данных, чтобы значение параметра было изменено. Прежде, чем изменить этот параметр, загляните в руководства по настройке и администрированию базы данных Oracle.
[Прим. редактора: Обратите особое внимание, что автор все же говорит о необходимости реорганизовать базу данных, если Вы захотите изменить параметр DB_BLOCK_SIZE.]
Параметр SHARED_POOL_ SIZE управляет объемом памяти, распределенным для кэширования библиотеки и словаря данных. Вы можете выяснить, насколько эффективна настройка этого параметра, таким же образом, как и для параметра DB_BLOCK_BUFFER_SIZE - измеряя коэффициент попаданий. Коэффициент попаданий можно измерять отдельно для библиотеки и для словаря данных.
Коэффициент попаданий для библиотеки указывает, какой процент памяти сервер использует для операторов и объектов PL/SQL, например, процедур, пакетов, и триггеров. В дополнение к вычислению процента удач (см. листинг 2 ), вы должны также исследовать значение столбец RELOAD в представлении v$librarycache.
Перезагрузками называются операторы, которые были размещены в памяти, но которые пришлось перезагрузить после того, как сервер удалил их оттуда. Если у вас коэффициент попаданий меньше, чем 95 процентов, или число перезагрузок больше нуля, вам следует увеличить значение SHARED_POOL_SIZE.
Коэффициент попаданий для словаря данных указывает на распределение памяти для словаря данных Oracle. Естественно, полный словарь не может постоянно находиться в памяти, но размер словарного кэша важен, потому что база данных многократно обращается к словарю во время обработки операторов SQL. Чтобы найти процент удач для словаря, вы можете использовать следующий запрос:
коэффициент попаданий ---------- 95.40 %
Напомню еще раз: хорошо, если полученное значение 95 или выше. Если полученное вами значение меньше 95 процентов, стоит подумать об увеличении значения SHARED_POOL_SIZE.
Вы можете использовать приведенный в листинге 1 запрос, чтобы выяснить, сколько свободной памяти становится доступной для используемого значения SHARED_POOL_SIZE. Количество свободной памяти - еще один индикатор того, является ли значение SHARED_POOL_SIZE оптимальным.
Параметр SORT_AREA_SIZE распределяет память для сортировки. Число байтов, которое вы распределяете для этого параметра, управляет объемом памяти, выделяемой для сортировки каждому пользователю. Если сервер не может выполнить сортировку в памяти, он распределяет на диске временные сегменты для хранения промежуточных результатов выполнения, что увеличивает число операций ввода/вывода. Вы должны установить достаточно высокое значение этого параметра, чтобы предотвратить постоянное порождение временных сегментов, и в то же самое время оставить достаточно памяти для других процессов.
В представлении V$systat содержатся сведения о проценте сортировок, которые выполняются сервером в оперативной памяти и с использованием дисковой памяти. Чтобы найти процент сортировок, выполняемых в памяти, я использую запрос из листинга 2 . Если этот процент меньше, чем 90, вы должны рассмотреть вопрос об увеличении значения SORT_AREA_SIZE.
Правило 2. Хранение в оперативной памяти нужных данных
После того, как вы распределили экземпляру сервера Oracle оптимальный объем памяти, вы должны быть уверены, что наиболее важные данные остаются в памяти. Вы можете "закрепить" в памяти (pin) основные таблицы, объекты PL/SQL и пакеты, чтобы избежать сбрасывания их сервером на диск.
Если вы последовательно выполните один и тот же запрос два или более раз, вы обратите внимание, что второй (и последующие) запросы выполняются быстрее, чем первый. Дело в том, что начальный запрос перемещает данные таблицы в буфера данных в памяти, где они остаются, пока не будут вытолкнуты другими данными из других таблиц, используемых для ответа на другие запросы. Чтобы сохранить конкретную таблицу в памяти, вы должны закрепить ее в кэше. Это стоит делать только с малыми таблицами, которые находятся в постоянном использовании.
Если вы обнаружили, что сервер выталкивает основные таблицы из памяти, вы можете закрепить их в памяти, используя параметр CACHE оператора CREATE/ALTER TABLE:
Этот параметр гарантирует, что данные из таблицы после полного ее сканирования находятся в списке самых недавно использованных (most recently used - MRU) данных, а не в списке самых давно использованных (least recently used - LRU) данных, в результате чего они будут сохранены в памяти для последующего использования. При создании таблицы значение параметра по умолчанию - NOCACHE. Поэтому для того, чтобы кэшировать таблицу при первом же к ней доступе, нужно использовать следующий синтаксис:
Можете также использовать в запросе подсказку CACHE, чтобы закрепить таблицу и сохранять ее в кэше, начиная с первого ее использования, а именно:
Перед закреплением таблиц в памяти следует выяснить, сколько памяти все еще остается свободной, чтобы можно было учесть непредвиденные запросы. Полезно проводить проверку того, достаточно ли памяти распределено для данных после того, как система проработает большую часть дня. Чтобы выяснять, сколько памяти доступно для данных в любой момент времени, выполните следующий запрос к таблице x$bh (чтобы иметь возможность обратиться к таблицам x$, вы должны войти в систему как SYS, или создать представления таблиц, а затем создать привилегии для этих представлений):
Если в первые 30 минут после запуска системы окажется, что нет свободных буферов, вам, может быть, придется возвратиться к первому правилу и увеличить значение DB_BLOCK_BUFFERS.
Закрепление в памяти объектов и пакетов
Если Вы не можете поддерживать удовлетворительную настройку для SHARED_POOL_SIZE, может оказаться важно сохранять закрепленными в памяти наиболее часто используемые объекты. Вы можете закреплять операторы объекта PL/SQL в памяти, используя процедуру DBMS_SHARED_POOL.KEEP, например, следующим образом:
Вы можете также закрепить некоторые или все пакеты, используя некоторые встроенные пакеты Oracle типа STANDARD, DBMS_STANDARD и DIUTIL.
Чтобы закрепить все пакеты вашей системы, вы можете использовать скрипт из листинга 3 .
Листинг 3. Закрепление всех пакетов в кеше.
Правило 3. Нахождение проблемных запросов
Один-единственный индекс или запрос могут затормозить или даже вовсе прекратить работу всей системы. Запрашивая v$sqlarea, вы можете выявить запросы, которые создают проблемы для системы. Проблемные запросы - это такие запросы, для которых требуется наибольшее количество физических или логических операций чтения с диска.
Обнаружение запросов, для которых требуется наибольшее количество физических операций чтения с диска
В листинге 4 приводится запрос для выявления таких запросов, для выполнения которых требуется более 10000 операций чтения с диска. Для систем большего размера вам может потребоваться увеличить этот порог.
Листинг 4. Поиск запросов, которые выыполняют более 10,000 дисковых чтений.
Обнаружение запросов, для которых требуется наибольшее количество логических операций чтения с диска
В листинге 5 приводится запрос для выявления таких запросов, для выполнения которых требуется более 200000 операций чтения в оперативной памяти. И снова, если ваша система велика, вам может потребоваться увеличить этот порог.
Листинг 5. Поиск запросов, которые выполнят более чем 200,000 логических чтений.
Выходные данные примера указывают на то, что проблему создает индекс по столбцу division, когда в компании имеется только два отдела. Чтобы улучшить производительность, нужно либо отказаться от этого индекса, либо создать его, как двоичный (bitmap) - это должно помочь.
Заметьте, что представление v$sqltext отображает только ограниченную часть SQL_TEXT. В случае примеров в листингах 4 и 5 , удается увидеть полный запрос, но в других случаях запрос может быть слишком длинным, чтобы быть отображенным полностью. В таких случаях, чтобы получить полный текст запроса, вы можете выполнить для представления v$sqltext оператор JOIN.
Использование оператора AUTOTRACE
Лучший способ измерять эффективность запросов (применяя SQL*PLUS 3.3 и более поздние версии) состоит в том, чтобы использовать команду AUTOTRACE. Для реализации свойства AUTOTRACE вы можете использовать SQL-операторы из листинга 6 . (Помните, что вы должны заранее создать таблицу PLAN_TABLE, и что вы должны иметь соответствующие привилегии доступа, разрешенные для представлений V$.) Чтобы создать роль PLUSTRACE, выполните скрипт plustrce.sql как потребитель с именем SYS. (Подробности см. в документации Oracle.)
Листинг 6. Реализация возможности AUTOTRACE
Настройка вашей базы данных для оптимальной производительности является итерационным и аналитическим процессом. Как можно заметить из этого быстрого обзора ключевых целей, наряду с некоторыми подсказками и методами, сюда относятся и сбор данных, и тонкая настройка параметров или установок, и оценка влияния, а затем повторный старт процесса для системы. Обнаруживаемые вами проблемные области обычно требуют дальнейшего анализа. В следующей статье будут обсуждены некоторые методы обработки проблемных запросов, идентифицированных вами.
Ричард Дж. Немец (Richard J. Niemiec)- исполнительный вице-президент компании TUSC. Он работает с технологиями Oracle уже более десяти лет и является автором трех вышедших в издательстве Oracle Press книг. Сейчас он выполняет обязанности исполнительного вице-президента группы пользователей IOUG-A. Ричард Дж. Немец включен в Зал Славы предпринимателей (Entrepreneur Hall of Fame).
Память Oracle можно разделить на глобальную область системы и глобальную область обработки в соответствии с общими и частными аспектами, то есть SGA и PGA (глобальная область обработки или частная глобальная область). Для памяти в области SGA она является общей глобальной. В UNIX сегмент общей памяти должен быть установлен для oracle (которое может быть одним или несколькими), потому что oracle является многопроцессорным в UNIX, oracle в WINDOWS - это Один процесс (несколько потоков), поэтому нет необходимости устанавливать сегмент общей памяти. PGA - это приватная область процесса (потока). В Oracle, использующем режим общего сервера (MTS), часть PGA, то есть UGA, будет помещена в общую память large_pool_size.
Архитектура памяти оракула состоит из картинки, и ключевые параметры и имена параметров можно увидеть с первого взгляда в соответствии с дисплеем над картинкой:
Для части SGA, мы можем видеть через запрос в sqlplus:
Fixed Size:
Разные платформы Oracle и разные версии могут отличаться, но это фиксированное значение для определения среды, в которой хранится информация о различных компонентах SGA, которую можно рассматривать как область, определяющую создание SGA.
Variable Size :
Содержит настройки памяти, такие как shared_pool_size, java_pool_size, large_pool_size и т. Д.
Database Buffers :
Индекс по буферной зоне:
В 8i он содержит три части памяти: db_block_buffer * db_block_size, buffer_pool_keep, buffer_pool_recycle.
В 9i он включает db_cache_size, db_keep_cache_size, db_recycle_cache_size, db_nk_cache_size.
Redo Buffers :
Относится к буферу журналов, log_buffer. Дополнительным моментом здесь является то, что значения запроса для параметров v $, v $ sgastat и v $ sga могут отличаться. Значение в параметре v $ относится к начальному пользователю
Значение, установленное в файле параметров инициализации, v $ sgastat - это фактический размер буфера журнала, выделенный Oracle (поскольку значение выделения буфера фактически дискретно, и оно не выделяется блоком в качестве минимальной единицы),
Значение, запрашиваемое в v $ sga, указывается после того, как oracle выделил буфер журнала. Чтобы защитить буфер журнала, установите несколько страниц защиты, обычно мы обнаруживаем, что размер страницы защиты составляет около 11 КБ (может отличаться в разных средах).
2. Параметры и настройки в SGA:
2.1 Log_buffer
Что касается настройки размера буфера журналов, я обычно не думаю, что есть слишком много предложений, потому что после обращения к условиям триггера, написанным LGWR, мы обнаружим, что обычно оно незначительно и превышает 3M. Как формальная система,
Может потребоваться установить для этой части значение log_buffer = 3-5M, а затем настроить его в соответствии с конкретной ситуацией.
log_buffer - это буфер журнала повторов.
2.2 Large_pool_size
Для настройки большого буферного пула, если MTS не используется, рекомендуется, чтобы 20-30M было достаточно. Эта часть в основном используется для сохранения некоторой информации во время параллельного запроса, а RMAN может использоваться во время резервного копирования.
Если MTS установлен, так как часть UGA будет перемещена сюда, вам необходимо рассмотреть настройку этой части в соответствии с числом процессов сервера и настройками связанных параметров памяти сеанса.
2.3 Java_pool_size
2.4 Shared_pool_size
Накладные расходы на Shared_pool_size обычно должны поддерживаться в пределах 300M. Если система не использует много хранимых процедур, функций, пакетов,
Например, приложения, такие как oracle erp, могут достигать 500M или даже выше. Итак, мы предполагаем систему памяти 1G, мы можем рассмотреть
Установите этот параметр равным 100M, система 2G считает установленной на 150M, система 8G считает установленной на 200-300M
2.5SGA_MAX_SIZE
Область SGA включает в себя различные буферы и пулы памяти, и большинство из них могут указывать свои размеры через определенные параметры. Однако, как дорогой ресурс, объем физической памяти системы ограничен.
Хотя для адресации памяти ЦП нет необходимости учитывать фактический объем физической памяти (об этом будет подробно рассказано позже), но чрезмерное использование виртуальной памяти приводит к вводу / выводу страницы
Это сильно повлияет на производительность системы и может даже привести к сбою системы. Следовательно, есть параметр для управления максимальным размером виртуальной памяти, используемой SGA. Этот параметр - SGA_MAX_SIZE. Когда экземпляр запускается,
Каждой области памяти выделяется только минимальный размер, требуемый экземпляром, и в последующем рабочем процессе их размер увеличивается по мере необходимости, а их общий размер ограничивается SGA_MAX_SIZE.
Для систем OLTP, обратитесь к:
Системная память
Значение SGA_MAX_SIZE
Когда запускается экземпляр oracle, он загружает только наименьший размер каждой области памяти. Другая память SGA выделяется только как виртуальная память, и только когда процесс касается соответствующей страницы, она заменяется физической памятью. Но мы можем захотеть, чтобы все SGA выделялись физической памяти после запуска экземпляра. В настоящее время вы можете достичь цели, установив параметр PRE_PAGE_SGA. Значением этого параметра по умолчанию является FALSE, что означает, что не все SGA помещаются в физическую память. Если установлено значение TRUE, запуск экземпляра поместит все SGA в физическую память. Это может заставить экземпляр начать достигать своего максимального состояния производительности, но время запуска также будет больше (потому что для того, чтобы поместить весь SGA в физическую память, процесс оракула должен коснуться всех страниц SGA).
Чтобы гарантировать, что SGA заблокирован в физической памяти без необходимости постраничного ввода / вывода страницы, им можно управлять с помощью параметра LOCK_SGA. Значением по умолчанию для этого параметра является FALSE. Когда указано TRUE, все SGA могут быть заблокированы в физической памяти. Конечно, некоторые системы не поддерживают блокировку памяти, этот параметр недопустим.
Вот очень важный параметр, представленный в Oracle10g. До 10g размер каждой области памяти SGA должен быть указан их соответствующими параметрами, и они не могут превышать значение указанного размера параметра, хотя их сумма может не достигать максимального предела SGA. Кроме того, после выделения память каждой области может использоваться только для этой области и не может использоваться совместно. Возьмем две наиболее важные области памяти в SGA, Buffer Cache и Shared Pool, которые оказывают наибольшее влияние на производительность экземпляра, но есть такое противоречие: в случае ограниченных ресурсов памяти иногда данные кэшируются Требование очень велико, чтобы улучшить попадание в буфер, вам необходимо увеличить буферный кэш, но из-за ограниченного SGA вы можете «захватывать» только другие области, такие как сокращение общего пула, увеличение буферного кэша, а иногда и большие блоки кода PLSQL. Он разрешается и хранится в памяти, что приводит к недостаточному общему пулу или даже к ошибке 4031, а также к необходимости расширения общего пула, что может потребовать вмешательства человека для восстановления памяти из буферного кеша.
С помощью этой новой функции этот конфликт памяти в SGA разрешается. Эта функция называется автоматическим управлением общей памятью (ASMM). И только этот параметр SGA_TARGE управляет этой функцией. После установки этого параметра вам не нужно указывать размер для каждой области памяти. SGA_TARGET задает максимальный объем памяти, который может использовать SGA, а размер каждой памяти в SGA контролируется самой Oracle и не требует указания вручную. Oracle может в любой момент настроить размер каждой области, чтобы достичь наиболее приемлемого размера системы с наилучшей производительностью, и контролировать их сумму в пределах значения, указанного в SGA_TARGET. Если для SGA_TARGET указано значение (по умолчанию 0, т. Е. ASMM не запущен), функция ASMM запускается автоматически.
Три, метод настройки памяти оракула
Когда возникают проблемы с производительностью в производственной среде проекта, как мы можем определить, какие параметры необходимо настроить?
3.1 Проверьте частоту попаданий в библиотечный кеш экземпляра ORACLE:
3.2 Проверьте частоту попаданий в буфер данных экземпляра ORACLE:
3.3 Проверьте частоту попаданий в кэш словаря экземпляра ORACLE:
3.4 Проверьте частоту попаданий в буфер журнала экземпляра ORACLE:
3.5 Проверьте undo_retention:
32-битные и 64-битные проблемы
Для оракула есть проблемы 32-битные и 64-битные. Эта проблема в основном влияет на размер SGA. В 32-битной базе данных Oracle обычно может использовать не более 1,7 ГБ памяти. Даже если у нас 12 ГБ памяти, мы можем использовать только 1,7 ГБ, что является большим сожалением. Если мы установим 64-битную базу данных, мы сможем использовать много памяти, и для нас почти невозможно достичь верхнего предела. Однако 64-битная база данных должна быть установлена в 64-битной операционной системе, но, к сожалению, в Windows может быть установлена только 32-битная база данных. Мы можем проверить, является ли база данных 32-битной или 64-битной, следующим образом.
Однако в некоторых операционных системах это может обеспечить некоторые средства, позволяющие нам использовать более 1,7 ГБ памяти, достигая более 2 ГБ или даже больше.
Вопрос 1. Значение sga_target / pga_aggregate_target изменено, поэтому их сумма больше, чем memory_target;
Вопрос 2. Измените значение memory_target так, чтобы оно было меньше суммы sga_target / pga_aggregate_target, и будет сообщено об ошибке;
SQL> запуск для запуска базы данных Ошибка:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 4016M
Краткое описание проблемы: Memory_Target меньше суммы SGA_Target и pga_aggregate_target
Информация о конфигурации указанных выше параметров хранится в файле $ ORACLE_HOME / dbs / spfile.ora, но этот файл является двоичным файлом и не может быть изменен напрямую: только его файл pfile для копирования можно создать с помощью операторов SQL для изменения vi / vim Просто замени это. (В это время БД была закрыта и не может быть запущена, но pfile все еще может быть создан spfile)
Первое решение: восстановить предыдущее состояние без изменений.
1)SQL>create pfile from spfile;
2)[[email protected] dbs]$ vim $ORACLE_HOME/dbs/initorcl.ora
3) Удалить строку * .sga_target = *****
4)SYS> create spfile from pfile;
Второй метод - изменить sga_target на правильное значение:
1) Создайте документ инициализации pfile;
2) Изменить * .sga_target = X (X равно значению MEMORY_TARGET минус значение PGA (больше 10M, минимум PGA));
3) Создать SPFILE из модифицированного PFILE;
4) Просто запустите БД;
Третий метод заключается в изменении и увеличении значения * .memory_target,
1) Подобно второму способу, создайте документ инициализации pfile;
2) Изменить и увеличить * .memory_target = Y (значение Y не может быть больше, чем размер общей файловой системы / dev / shm tmpfs, в противном случае будет сообщено об ошибке в случае 2)
Случай 2: проблема: значение Memory_Target слишком велико
SQL> запуск для запуска базы данных Ошибка:
ORA-00845: MEMORY_TARGET not supported on this system
Причина проблемы: размер физической памяти> = настройка размера целевого элемента памяти> = / dev / shm tmpfs размер общей файловой системы
Способ 1: обратитесь к способу 3 в случае 1, чтобы изменить размер Memory_Target;
Метод 2: измените размер / dev / shm, есть два способа изменить:
Это статическая модификация, для которой требуется перезагрузка системы.
Это для изменения его размера путем повторного подключения без перезапуска.
1. Объяснение терминов
(1) SGA: системная глобальная область является базовым компонентом экземпляра Oracle, который выделяется при запуске экземпляра, а системный глобальный домен SGA состоит в основном из трех частей: общего пула, буфера данных и буфера журнала.
(2) Общий пул: Общий пул используется для кэширования недавно выполненных операторов SQL и недавно использованных определений данных, в основном, включая: библиотечный кеш (область общего SQL) и кэш словаря данных (буфер словаря данных). Общая область SQL - это область, в которой хранятся пользовательские команды SQL, а в буфере словаря данных хранится динамическая информация об операциях базы данных.
(3) Буферный кеш: Буферный кеш базы данных используется для кэширования блоков данных, извлеченных из файлов данных, что может значительно повысить производительность запросов и обновления данных.
(4) Большой пул: Большой пул - это дополнительная область памяти в SGA, которая используется только в среде общего сервера.
(5) Пул Java: Пул Java предоставляет услуги для анализа синтаксиса команд Java.
(6) PGA: глобальная область процесса - это память, зарезервированная для каждого пользовательского процесса, подключенного к базе данных Oracle.
Во-вторых, анализ и корректировка
(1) Системный глобальный домен:
SGA относится к операционной системе, объему памяти, процессору и количеству пользователей, вошедших в систему одновременно. Он может занимать от 1/3 до 1/2 физической памяти системы ОС.
А. Общий пул:
Просмотрите уровень использования общей области SQL:
- select ( sum (pins-reloads))/ sum (pins) "Library cache" from v$librarycache; --Динамическая таблица производительности
Этот коэффициент использования должен быть выше 90%, в противном случае размер общего пула необходимо увеличить.
Посмотрите уровень использования буфера словаря данных:
- select ( sum (gets-getmisses-usage-fixed))/ sum (gets) "Data dictionary cache" from v$rowcache; --Динамическая таблица производительности
Этот коэффициент использования также должен быть выше 90%, в противном случае размер общего пула необходимо увеличить.
Измените размер общего пула:
б) буферный кеш:
Просмотр использования буфера данных базы данных:
- SELECT name ,value FROM v$sysstat order by name WHERE name IN ( '' DB BLOCK GETS '' , '' CONSISTENT GETS '' , '' PHYSICAL READS '' );
Рассчитанная частота попадания в буфер данных = 1- (физическое чтение / (блок дБ получает + постоянное получение)), этот коэффициент должен быть выше 90%, в противном случае необходимо увеличить размер буфера данных.
c) буфер журнала
Просмотрите использование буфера журнала:
- SELECT name , value FROM v$sysstat WHERE name IN ( '' redo entries '' , '' redo log space requests '' );
Результат запроса может вычислить частоту сбоев приложения буфера журнала:
Частота сбоев приложения = количество запросов / записей, частота сбоев приложения должна быть близка к 0, в противном случае буфер журнала слишком мал, а буфер журнала базы данных ORACLE необходимо увеличить.
г. Большой бассейн:
Это может снизить нагрузку на общий пул и может использоваться для резервного копирования, восстановления и других операций без использования алгоритма LRU для управления. Размер определяется базой данных «совместно используемый режим / режим БД». Если это общий режим, он будет больше.
Укажите размер Большого пула:
д. пул Java:
Используется, когда Java установлена и используется.
(2) регулировка PGA
А. PGA_AGGREGATE_TARGET настройки инициализации
Значение PGA_AGGREGATE_TARGET должно быть установлено на основе общего объема доступной памяти экземпляра Oracle. Этот параметр может быть изменен динамически. Предположим, что экземпляр Oracle может выделить 4 ГБ физической памяти, а оставшаяся память - операционной системе и другим приложениям. Вы можете выделить 80% доступной памяти для экземпляра Oracle, что составляет 3,2 ГБ. Области SGA и PGA теперь должны быть разделены в памяти.
В системе OLTP (оперативная обработка транзакций) типичная настройка памяти PGA должна составлять небольшую часть общего объема памяти (например, 20%), а оставшиеся 80% выделяются для SGA.
OLTP:PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
В системе DSS (набор данных) типичная память PGA выделяется до 70% памяти из-за большого количества запросов, которые будут выполняться.
DSS:PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%
В этом примере общий объем памяти составляет 4 ГБ. Для систем DSS вы можете установить для PGA_AGGREGATE_TARGET значение 1600 МБ, а для OLTP - 655 МБ.
б) настроить автоматическое управление PGA
Не нужно перезагружать БД, напрямую изменять онлайн.
SQL> alter system set workarea_size_policy=auto scope=both;
SQL> alter system set pga_aggregate_target=512m scope=both;
SQL> show parameter workarea
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy строка рабочей строки AUTO - для нее установлено значение AUTO
SQL> show parameter pga
c. Отслеживать производительность автоматического управления памятью PGA
V $ PGASTAT: это представление дает статистику на уровне экземпляра использования памяти PGA и автоматического распределения.
SQL> set lines 256
SQL> set pages 42
SQL> SELECT * FROM V$PGASTAT;
NAME VALUE UNIT
---------------------------------------------------------------- ---------- ------------
совокупный целевой параметр PGA: 536870912 текущее значение байта PGA_AGGREGATE_TARGET
агрегированная автоматическая цель PGA 477379584 байт-размер PGA, доступный в настоящее время для автоматического выделения, должен быть меньше, чем PGA_AGGREGATE_TARGET
привязка к глобальной памяти привязка к глобальной памяти 26843136 байт - максимальный размер рабочей области в автоматическом режиме, Oracle автоматически подстраивается в соответствии с рабочей нагрузкой.
total PGA inuse 6448128 bytes
всего выделено PGA
maximum PGA allocated 166175744 bytes
общая свободная память PGA, память PGA, 393 216 байт, максимальный свободный размер PGA
PGA memory freed back to OS 69074944 bytes
Всего PGA используется для авто рабочих площадей
maximum PGA used for auto workareas 1049600 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 530432 bytes
число перерасходов 1 количество перерасчетов 1118 - После запуска экземпляра количество распределений, которое происходит после запуска экземпляра, если это значение больше 0, следует рассмотреть возможность увеличения значения pga
bytes processed 114895872 bytes
extra bytes read/written 4608000 bytes
процент попаданий в кеш…
16 rows selected.
SQL> SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM v$pga_target_advice;
The output of this query might look like the following:
TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
63 23 367
125 24 30
250 30 3
375 39 0
500 58 0
600 59 0
700 59 0
800 60 0
900 60 0
1000 61 0
1500 67 0
2000 76 0
3000 83 0
4000 85 0
Можно видеть, что когда TARGET_MB составляет 375M, ESTD_OVERALLOC_COUNT = 0, поэтому вы можете установить PGA_AGGREGATE_TARGET на 375M.
Приложение: Разница между оракулом SGA и PGA:
SGA: область памяти, используемая для хранения информации базы данных, которая используется процессами базы данных. Он содержит данные и управляющую информацию сервера Oracle, он распределяется в фактической памяти компьютера, на котором находится сервер Oracle. Если фактической памяти недостаточно, он записывается в виртуальную память.
PGA: содержит данные и управляющую информацию одного серверного процесса или одного фонового процесса. В отличие от SGA, совместно используемого несколькими процессами, PGA является областью, используемой только одним процессом. PGA выделяется, когда процесс создается и перерабатывается после его завершения.
Shared Pool in Oracle Decoded
Shared pool in Oracle database is the place where Oracle caches the SQL , PL/SQL and dictionary data.
Whenever we parse a query, the parsed representation is cached there and it is shared also.Oracle check for the query first in the shared pool before parsing an entire query, if it finds the parse copy cached, it does not perform that work again and this is significant saving for the CPU and time.
Similarly PL/SQL code that you run is cached in the shared pool, so the next time you run it, Oracle doesn’t have to read it in from disk again. PL/SQL code is not only cached here, it is shared here as well. So if we have multiple session executing the same PL/SQL program, it uses the same copy cached in the pool
Oracle also stores the system parameters in the shared pool.
The data dictionary cache (cached information about database objects) is stored here.
it manages memory on an LRU basis, similar to buffer cache, which is perfect for caching and reusing data.
In short, it has these areas
-Text of the SQL or PL/SQL statement
-Parsed form of the SQL or PL/SQL statement
-Execution plan for the SQL or PL/SQL statements
-Data dictionary cache containing rows of data
dictionary information
It consists of Library cache and Dictionary cache primarily
Library Cache
-shared SQL area
-private SQL area
-PL/SQL procedures and package:Executable representation of PL/SQL packages ,procedures and functions that may be used repeatedly by many sessions.It also holds stored trigger code.
-control structures : lock and library cache handles
Dictionary Cache
-names of all tables and views in the database
-names and datatypes of columns in database tables
-privileges of all Oracle users
-Oracle Database uses this metadata when parsing SQL cursors or during the compilation of PL/SQL programs
Tuning the Shared Pool and Library Cache
SESSION_CACHED_CURSORS parameter
It is a numeric parameter which can be set at instance level or at session level using the command:
The value NNN determines how many ‘cached’ cursors there can be in your session. Whenever a statement is parsed Oracle first looks at the statements pointed to by your private session cache – if a sharable version of the statement exists it can be used. This provides a shortcut access to frequently parsed statements that uses less CPU and uses far fewer latch gets than a soft or hard parse.
SHARED_POOL_SIZE parameter
See Document 1012046.6 to calculate the SHARED_POOL_SIZE requirements based on your current workload.
DBMS_SHARED_POOL.KEEP
This procedure (defined in the DBMSPOOL.SQL script in the RDBMS/ADMIN directory) can be used to KEEP objects in the shared pool. DBMS_SHARED_POOL.KEEP allows one to ‘KEEP’ packages, procedures, functions, triggers (7.3+) and sequences (7.3.3.1+) and is fully described in Document 61760.1
Pinning Cursors in the Shared Pool
Another way to alleviate library cache latch is to pin curors in the shared pool. Please refer to following note on how to do this:Document 130699.1 How to Reduce ‘LIBRARY CACHE LATCH’ Contention Using a Procedure to KEEP Cursors Executed> 10 times
Flushing the SHARED POOL
On systems which use a lot of literal SQL the shared pool is likely to fragment over time such that the degree of concurrency which can be achieved diminishes. Flushing the shared pool will often restore performance for a while as it can cause many small chunks of memory to be coalesced.
ALTER SYSTEM FLUSH SHARED_POOL;
CURSOR_SHARING parameter
If this parameter is set to FORCE then literals will be replaced by system generated bind variables where possible. For multiple similar statements which differ only in the literals used this allows the cursors to be shared even though the application supplied SQL uses literals. The parameter can be set dynamically at the system or session level thus:
set CURSOR_SHARING=SIMILAR. SIMILAR causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized. This enhancement improves the usability of the parameter for situations where FORCE would normally cause a different, undesired execution plan. With CURSOR_SHARING=SIMILAR, Oracle determines which literals are “safe” for substitution with bind variables. This will result in some SQL not being shared in an attempt
Eliminating Literal SQL
The statements below is intended to list example of statements that may be suitable for replacing literals with bind variables.
The following queries shows SQL in the SGA where there are a large number of similar statements.The values 40,5 and 30 are example values so this query is looking for different statements whose first 40 characters are the same which have only been executed a few times each and there are at least 30 different occurrences in the shared pool.
Читайте также: