Sga oracle что такое
Структуры Памяти Базы данных Oracle
База данных Oracle создает и использует структуры памяти для различных целей. Например, память хранит выполняемый код программы, данные, которые совместно используются пользователями, а также частные области данных для каждого подключенного пользователя. Две основных структуры памяти связаны с экземпляром:
Системная Глобальная область (SGA): Группа структур разделяемой памяти, известных как компоненты SGA, которые содержат данные и управляющую информацию для одного экземпляра Базы данных Oracle. SGA совместно используется всеми серверными и фоновыми процессами. Примеры данных, хранимых в SGA, включают кэшированные блоки данных и общие области SQL.
Разделяемый пул: Кэширует различные конструкты, которые могут совместно использоваться пользователями
Кэш буферов базы данных: Кэширует блоки данных, получаемые от базы данных
Буферный пул хранения: специализированный тип буферного кэша базы данных, который настраивается для хранения блоков данных в памяти на длительные промежутки времени
Буферный пул удаления: специализированный тип буферного кэша БД, который настраивается для быстрого повторного использования или удаления блока из памяти
Буферный кэш nK: Один из нескольких специализированных буферных кэшей БД, разработанный для хранения блоков с размером, отличающимся от размера блока базы данных по умолчанию
Буфер журнала повторов: Кэширует информацию о повторах (используемую, например, при восстановлении экземпляра), пока она не будет записана в физические файлы журнала повторов, хранимые на диске
Большой пул: Дополнительная область, которая обеспечивает выделения памяти большого объема для определенных больших процессов, таких как резервное копирование и операции восстановления Oracle, или серверные процессы ввода-вывода
Пул Java: Используется для всех специфичных для сеанса кода Java и данных в виртуальной машине Java (JVM)
Пул потоков: Используется потоками Oracle, чтобы хранить информацию, требуемую для операций захвата и применения
Когда Вы запускаете экземпляр с помощью Enterprise Manager или SQL*Plus, объем памяти, выделенный для SGA, выводится на экран.
Глобальной программной областью (PGA) является область памяти, которая содержит данные и управляющую информацию для каждого серверного процесса. Серверный процесс Oracle обслуживает запросы клиента. У каждого серверного процесса есть своя собственная частная PGA, которая выделяется при запуске серверного процесса. Доступ к PGA является монопольным для соответствующего серверного процесса, чтение и запись в PGA выполняется только кодом Oracle, действующим по запросу PGA. PGA делится на две главных области: стековое пространство и пользовательская глобальная область (UGA).
С динамичной инфраструктурой SGA размеры буферного кэша базы данных, разделяемого пула, большого пула, пула Java и пула потоков могут изменяться без необходимости завершения экземпляра.
База данных Oracle использует параметры инициализации, чтобы создать и управлять структурами памяти. Самый простой способ управлять памятью состоит в том, чтобы позволить базе данных автоматически управлять и настраивать ее для Вас. Чтобы сделать это (на большинстве платформ), необходимо только установить целевые параметры инициализации объема памяти (MEMORY_TARGET) и максимального объема памяти (MEMORY_MAX_TARGET).
14 Memory Architecture
This chapter discusses the memory architecture of a database instance.
This chapter contains the following sections:
Oracle Database Administrator’s Guide for instructions for configuring and managing memory
Introduction to Oracle Database Memory Structures
When an instance is started, Oracle Database allocates a memory area and starts background processes.
The memory area stores information such as the following:
Information needed during program execution, for example, the current state of a query from which rows are being fetched
Information such as lock data that is shared and communicated among processes
Cached data, such as data blocks and redo records, that also exists on disk
Basic Memory Structures
Oracle Database includes several memory areas, each of which contains multiple subcomponents.
The basic memory structures associated with Oracle Database include:
System global area (SGA)
Program global area (PGA)
A PGA is a nonshared memory region that contains data and control information exclusively for use by an Oracle process. Oracle Database creates the PGA when an Oracle process starts.
User global area (UGA)
The UGA is memory associated with a user session.
Software code areas
Software code areas are portions of memory used to store code that is being run or can be run. Oracle Database code is stored in a software area that is typically at a different location from user programs—a more exclusive or protected location.
The following figure illustrates the relationships among these memory structures.
Figure 14-1 Oracle Database Memory Structures
Description of «Figure 14-1 Oracle Database Memory Structures»
Oracle Database Memory Management
Memory management involves maintaining optimal sizes for the Oracle instance memory structures as demands on the database change. Oracle Database manages memory based on the settings of memory-related initialization parameters.
The basic options for memory management are as follows:
Automatic memory management
You specify the target size for the database instance memory. The instance automatically tunes to the target memory size, redistributing memory as needed between the SGA and the instance PGA.
Automatic shared memory management
This management mode is partially automated. You set a target size for the SGA and then have the option of setting an aggregate target size for the PGA or managing PGA work areas individually.
Manual memory management
Instead of setting the total memory size, you set many initialization parameters to manage components of the SGA and instance PGA individually.
If you create a database with Database Configuration Assistant (DBCA) and choose the basic installation option, then automatic memory management is the default.
«Memory Management» for more information about memory management options for DBAs
Oracle Database Administrator’s Guide to learn about memory management options
Overview of the User Global Area
The UGA is session memory, which is memory allocated for session variables, such as logon information, and other information required by a database session. Essentially, the UGA stores the session state.
The following figure depicts the UGA.
Figure 14-2 User Global Area (UGA)
Description of «Figure 14-2 User Global Area (UGA)»
The UGA must be available to a database session for the life of the session. For this reason, the UGA cannot be stored in the PGA when using a shared server connection because the PGA is specific to a single process. Therefore, the UGA is stored in the SGA when using shared server connections, enabling any shared server process access to it. When using a dedicated server connection, the UGA is stored in the PGA.
Oracle OLAP User’s Guide for an overview of Oracle OLAP
Overview of the Program Global Area (PGA)
The PGA is memory specific to an operating process or thread that is not shared by other processes or threads on the system. Because the PGA is process-specific, it is never allocated in the SGA.
The PGA is a memory heap that contains session-dependent variables required by a dedicated or shared server process. The server process allocates memory structures that it requires in the PGA.
An analogy for a PGA is a temporary countertop workspace used by a file clerk. In this analogy, the file clerk is the server process doing work on behalf of the customer (client process). The clerk clears a section of the countertop, uses the workspace to store details about the customer request and to sort the folders requested by the customer, and then gives up the space when the work is done.
The following figure shows an instance PGA (collection of all PGAs) for an instance that is not configured for shared servers. You can use an initialization parameter to set a target maximum size of the instance PGA. Individual PGAs can grow as needed up to this target size.
Figure 14-3 Instance PGA
Description of «Figure 14-3 Instance PGA»
Background processes also allocate their own PGAs. This discussion focuses on server process PGAs only.
Contents of the PGA
The PGA is subdivided into different areas, each with a different purpose.
The following figure shows the possible contents of the PGA for a dedicated server session. Not all of the PGA areas will exist in every case.
Figure 14-4 PGA Contents
Description of «Figure 14-4 PGA Contents»
Private SQL Area
A private SQL area holds information about a parsed SQL statement and other session-specific information for processing.
When a server process executes SQL or PL/SQL code, the process uses the private SQL area to store bind variable values, query execution state information, and query execution work areas.
Do not confuse a private SQL area, which is in the PGA, with the shared SQL area, which stores execution plans in the SGA. Multiple private SQL areas in the same or different sessions can point to a single execution plan in the SGA. For example, 20 executions of SELECT * FROM sales in one session and 10 executions of the same query in a different session can share the same plan. The private SQL areas for each execution are not shared and may contain different values and data.
A cursor is a name or handle to a specific private SQL area. As shown in the following graphic, you can think of a cursor as a pointer on the client side and as a state on the server side. Because cursors are closely associated with private SQL areas, the terms are sometimes used interchangeably.
Description of «Figure 14-5 Cursor»
A private SQL area is divided into the following areas:
Oracle Database creates the run-time area as the first step of an execute request. For DML statements, the run-time area is freed when the SQL statement is closed.
The persistent area
This area contains bind variable values. A bind variable value is supplied to a SQL statement at run time when the statement is executed. The persistent area is freed only when the cursor is closed.
Although most users rely on the automatic cursor handling of database utilities, the Oracle Database programmatic interfaces offer developers more control over cursors. In general, applications should close all open cursors that will not be used again to free the persistent area and to minimize the memory required for application users.
SQL Work Areas
A work area is a private allocation of PGA memory used for memory-intensive operations.
For example, a sort operator uses the sort area to sort a set of rows. Similarly, a hash join operator uses a hash area to build a hash table from its left input, whereas a bitmap merge uses the bitmap merge area to merge data retrieved from scans of multiple bitmap indexes.
The following example shows a join of employees and departments with its query plan :
In the preceding example, the run-time area tracks the progress of the full table scans. The session performs a hash join in the hash area to match rows from the two tables. The ORDER BY sort occurs in the sort area.
If the amount of data to be processed by the operators does not fit into a work area, then Oracle Database divides the input data into smaller pieces. In this way, the database processes some data pieces in memory while writing the rest to temporary disk storage for processing later.
The database automatically tunes work area sizes when automatic PGA memory management is enabled. You can also manually control and tune the size of a work area. See «Memory Management» for more information.
Generally, larger work areas can significantly improve performance of an operator at the cost of higher memory consumption. Optimally, the size of a work area is sufficient to accommodate the input data and auxiliary memory structures allocated by its associated SQL operator. If not, response time increases because part of the input data must be cached on disk. In the extreme case, if the size of a work area is too small compared to input data size, then the database must perform multiple passes over the data pieces, dramatically increasing response time.
Oracle Database Administrator’s Guide to learn how to use automatic PGA management
Экзепляр Oracle (SGA и PGA). Сущность процессов. Пользовательские процессы, проц
Здравствуйте, дорогие читатели! Очень рад очередной встрече с Вами.
На эти вопросы очень просто ответить, внимательно прочитав предыдущий выпуск.
Экземпляр Oracle состоит из процессов и разделяемой памяти, необходимой для доступа к информации в БД. Если копнуть чуть глубже, то экземпляр составляют пользовательские процессы, фоновые процессы Oracle и разделяемая область памяти, которую используют все эти процессы.
Что же представляет собой разделяемая память (shared memory)? Oracle использует разделяемую память в разных целях: как кэширование данных и индексов, так и хранение программного кода. Разделяемая память делится на несколько частей (или структур памяти). Основными структурами памяти Oracle являются Системная Глобальная Область (System Global Area или SGA) и Программная Глобальная Область (Program Global Area или PGA). Рассмотрим SGA и PGA более подробно.
Системная Глобальная Область (SGA)
Библиотечный кэш используется для хранения разделяемых SQL. Здесь для каждого уникального SQL-выражения строиться дерево разбора строк и план исполнения, которые кэшируются (т.е. сохраняются в библиотечном кэше). Если несколько приложений отправляют одинаковые SQL-выражения, то для ускорения работы используется разделяемая SQL-область (так как используются уже разобранные строки и готовый план исполнения, то происходит экономия времени).
Кэш словаря данных содержит набор таблиц и представлений, используемых в качестве ссылок к БД Oracle. Здесь храниться информация о логической и физической структуре БД. Словарь данных содержит следующую информацию:
Oracle часто обращается к словарю данных при разборе SQL-выражений. Эти обращения составляют сущность работы Oracle. Узкие места в словаре данных влияют работу всех пользователей системы Oracle. Поэтому Вы всегда должны быть уверены, что объем памяти, определенный для словаря данных, достаточно велик для кэширования данных. Если кэш словаря данных мал, то Вы заметите значительное снижение производительности. Когда под кэш словаря данных Вы определите достаточный объем памяти, существенных проблем с производительностью быть не должно.
Программная Глобальная Область (PGA)
Процессы Oracle выполняют функции для пользовательских процессов. Могут быть разбиты на две группы: серверные процессы (выполняющие функции для активных процессов) и фоновые процессы (выполняют функции СУРБД в целом).
Серверные процессы (теневые) взаимодействуют между процессами пользовательскими и Oracle, исполняя пользовательские запросы. Например, если пользовательский процесс запрашивает часть данных, которых еще нет в SGA, то теневой процесс несет ответственность за чтение блоков данных из БД в SGA. Между пользовательским и теневым процессом возникает связь один-к-одному (конфигурация выделенного сервера), хотя один теневой процесс может одновременно взаимодействовать с несколькими пользовательскими (конфигурация мультинитевого сервера), экономя системные ресурсы.
Фоновые процессы используются для выполнения разнообразных задач СУРБД Oracle. Эти задачи варьируются от взаимодействия с экземпляром Oracle до записи грязных блоков на диск. Далее представлены девять фоновых процессов Oracle:
8 Memory Architecture
This chapter discusses the memory architecture of an Oracle instance.
This chapter contains the following topics:
Introduction to Oracle Memory Structures
Oracle uses memory to store information such as the following:
Information about a connected session, even if it is not currently active
Information needed during program execution (for example, the current state of a query from which rows are being fetched)
Information that is shared and communicated among Oracle processes (for example, locking information)
Cached data that is also permanently stored on peripheral memory (for example, data blocks and redo log entries)
The basic memory structures associated with Oracle include:
System Global Area (SGA), which is shared by all server and background processes.
Program Global Areas (PGA), which is private to each server and background process; there is one PGA for each process.
Figure 8-1 illustrates the relationships among these memory structures.
Figure 8-1 Oracle Memory Structures
Description of «Figure 8-1 Oracle Memory Structures»
Software code areas are another basic memory structure.
Overview of the System Global Area
An SGA and Oracle processes constitute an Oracle instance. Oracle automatically allocates memory for an SGA when you start an instance, and the operating system reclaims the memory when you shut down the instance. Each instance has its own SGA.
The SGA is read/write. All users connected to a multiple-process database instance can read information contained within the instance’s SGA, and several processes write to the SGA during execution of Oracle.
The SGA contains the following data structures:
Database buffer cache
Large pool (optional)
Data dictionary cache
Other miscellaneous information
If the system uses shared server architecture, then the request and response queues and some contents of the PGA are in the SGA.
«Introduction to an Oracle Instance» for more information about an Oracle instance
The SGA_MAX_SIZE Initialization Parameter
Granule size is determined by total SGA size. On most platforms, the size of a granule is 4 MB if the total SGA size is less than 1 GB, and granule size is 16MB for larger SGAs. Some platform dependencies arise. For example, on 32-bit Windows, the granule size is 8 M for SGAs larger than 1 GB.
For optimal performance in most systems, the entire SGA should fit in real memory. If it does not, and if virtual memory is used to store parts of it, then overall database system performance can decrease dramatically. The reason for this is that portions of the SGA are paged (written to and read from disk) by the operating system. The amount of memory dedicated to all shared areas in the SGA also has performance impact.
The size of the SGA is determined by several initialization parameters. The following parameters have the greatest effect on SGA size:
| Parameter | Description |
|---|---|
| DB_CACHE_SIZE | The size of the cache of standard blocks. |
| LOG_BUFFER | The number of bytes allocated for the redo log buffer. |
| SHARED_POOL _SIZE | The size in bytes of the area devoted to shared SQL and PL/SQL statements. |
| LARGE_POO L_SIZE | The size of the large pool; the default is 0. |
| JAVA_POO L_SIZE | The size of the Java pool. |
Automatic Shared Memory Management
When automatic SGA memory management is enabled, the sizes of the different SGA components are flexible and can adapt to the needs of a workload without requiring any additional configuration. The database automatically distributes the available memory among the various components as required, allowing the system to maximize the use of all available SGA memory.
Consider a manual configuration in which 1 GB of memory is available for the SGA and distributed to the following initialization parameters:
If an application attempts to allocate more than 128 MB of memory from the shared pool, an error is raised that indicates that the available shared pool has been exhausted. There could be free memory in the buffer cache, but this memory is not accessible to the shared pool. You would have to manually resize the buffer cache and the shared pool to work around this problem.
Setting a single parameter greatly simplifies the administration task. You specify only the amount of SGA memory that an instance has available and forget about the sizes of individual components. No out of memory errors are generated unless the system has actually run out of memory.
Automatic SGA management can enhance workload performance without requiring any additional resources or manual tuning effort. With manual configuration of the SGA, it is possible that compiled SQL statements frequently age out of the shared pool because of its inadequate size. This can increase the frequency of hard parses, leading to reduced performance. When automatic SGA management is enabled, the internal tuning algorithm monitors the performance of the workload, increasing the shared pool if it determines the increase will reduce the number of parses required.
The SGA_TARGET Initialization Parameter
The SGA_TARGET initialization parameter reflects the total size of the SGA and includes memory for the following components:
Fixed SGA and other internal allocations needed by the Oracle Database instance
The keep and recycle buffer caches (if specified)
Nonstandard block size buffer caches (if specified)
Do not dynamically set or unset the SGA_TARGET parameter. This should be set only at startup.
Automatically Managed SGA Components
The shared pool (for SQL and PL/SQL execution)
The Java pool (for Java execution state)
The large pool (for large allocations such as RMAN backup buffers)
You need not set the size of any of these components explicitly. By default the parameters for these components will appear to have values of zero. Whenever a component needs memory, it can request that it be transferred from another component by way of the internal automatic tuning mechanism. This transfer of memory occurs transparently, without user intervention.
The performance of each of these automatically sized components is monitored by the Oracle Database instance. The instance uses internal views and statistics to determine how to distribute memory optimally among the components. As the workload changes, memory is redistributed to ensure optimal performance. To calculate the optimal distribution of memory, the database uses an algorithm that takes into consideration both long-term and short-term trends.
Manually Managed SGA Components
There are a few SGA components whose sizes are not automatically adjusted. The administrator needs to specify the sizes of these components explicitly, if needed by the application. Such components are:
Keep/Recycle buffer caches (controlled by DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE )
The sizes of these components is determined by the administrator-defined value of their corresponding parameters. These values can, of course, be changed any time either using Enterprise Manager or from the command line with an ALTER SYSTEM statement.
The memory consumed by manually sized components reduces the amount of memory available for automatic adjustment. For example, in the following configuration:
Persistence of Automatically Tuned Values
Oracle Database remembers the sizes of the automatically tuned components across instance shutdowns if you are using a server parameter file ( SPFILE ). As a result, the system does need to learn the characteristics of the workload again each time an instance is started. It can begin with information from the past instance and continue evaluating workload where it left off at the last shutdown.
Adding Granules and Tracking Component Size
If you specify a size for a component that is not a multiple of granule size, then Oracle rounds the specified size up to the nearest multiple. For example, if the granule size is 4 MB and you specify DB_CACHE_S IZE as 10 MB, you will actually be allocated 12 MB.
Oracle Database 2 Day DBA for information on showing the SGA size with Enterprise Manager
SQL*Plus User’s Guide and Reference for information on displaying the SGA size with SQL*Plus
Your Oracle installation or user’s guide for information specific to your operating system
Database Buffer Cache
The database buffer cache is the portion of the SGA that holds copies of data blocks read from datafiles. All user processes concurrently connected to the instance share access to the database buffer cache.
The database buffer cache and the shared SQL cache are logically segmented into multiple sets. This organization into multiple sets reduces contention on multiprocessor systems.
Organization of the Database Buffer Cache
When an Oracle process accesses a buffer, the process moves the buffer to the most recently used (MRU) end of the LRU list. As more buffers are continually moved to the MRU end of the LRU list, dirty buffers age toward the LRU end of the LRU list.
The first time an Oracle user process requires a particular piece of data, it searches for the data in the database buffer cache. If the process finds the data already in the cache (a cache hit ), it can read the data directly from memory. If the process cannot find the data in the cache (a cache miss ), it must copy the data block from a datafile on disk into a buffer in the cache before accessing the data. Accessing data through a cache hit is faster than data access through a cache miss.
Before reading a data block into the cache, the process must first find a free buffer. The process searches the LRU list, starting at the least recently used end of the list. The process searches either until it finds a free buffer or until it has searched the threshold limit of buffers.
If the user process finds a dirty buffer as it searches the LRU list, it moves that buffer to the write list and continues to search. When the process finds a free buffer, it reads the data block from disk into the buffer and moves the buffer to the MRU end of the LRU list.
If an Oracle user process searches the threshold limit of buffers without finding a free buffer, the process stops searching the LRU list and signals the DBW0 background process to write some of the dirty buffers to disk.
«Database Writer Process (DBW n )» for more information about DBW n processes
The LRU Algorithm and Full Table Scans
When the user process is performing a full table scan, it reads the blocks of the table into buffers and puts them on the LRU end (instead of the MRU end) of the LRU list. This is because a fully scanned table usually is needed only briefly, so the blocks should be moved out quickly to leave more frequently used blocks in the cache.
You can control this default behavior of blocks involved in table scans on a table-by-table basis. To specify that blocks of the table are to be placed at the MRU end of the list during a full table scan, use the CACHE clause when creating or altering a table or cluster. You can specify this behavior for small lookup tables or large static historical tables to avoid I/O on subsequent accesses of the table.
Oracle Database SQL Reference for information about the CACHE clause
Size of the Database Buffer Cache
«Multiple Buffer Pools» for more information about the KEEP and RECYCLE buffer pools
The sizes and numbers of non-standard block size buffers are specified by the following parameters:
Each parameter specifies the size of the cache for the corresponding block size.
Platform-specific restrictions regarding the maximum block size apply, so some of these sizes might not be allowed on some platforms.
Example of Setting Block and Cache Sizes
In the preceding example, the parameter DB_BLOCK_SIZE sets the standard block size of the database to 4K. The size of the cache of standard block size buffers is 1024MB. Additionally, 2K and 8K caches are also configured, with sizes of 256MB and 512MB, respectively.
The cache has a limited size, so not all the data on disk can fit in the cache. When the cache is full, subsequent cache misses cause Oracle to write dirty data already in the cache to disk to make room for the new data. (If a buffer is not dirty, it does not need to be written to disk before a new block can be read into the buffer.) Subsequent access to any data that was written to disk results in additional cache misses.
The size of the cache affects the likelihood that a request for data results in a cache hit. If the cache is large, it is more likely to contain the data that is requested. Increasing the size of a cache increases the percentage of data requests that result in cache hits.
You can change the size of the buffer cache while the instance is running, without having to shut down the database. Do this with the ALTER SYSTEM statement. For more information, see «Control of the SGA’s Use of Memory».
Use the fixed view V$BUFFER _POOL to track the sizes of the different cache components and any pending resize operations.
Oracle Database Performance Tuning Guide for information about tuning the buffer cache
Multiple Buffer Pools
You can configure the database buffer cache with separate buffer pools that either keep data in the buffer cache or make the buffers available for new data immediately after using the data blocks. Particular schema objects (tables, clusters, indexes, and partitions) can then be assigned to the appropriate buffer pool to control the way their data blocks age out of the cache.
The KEEP buffer pool retains the schema object’s data blocks in memory.
The RECYCLE buffer pool eliminates data blocks from memory as soon as they are no longer needed.
The DEFAULT buffer pool contains data blocks from schema objects that are not assigned to any buffer pool, as well as schema objects that are explicitly assigned to the DEFAULT pool.
Multiple buffer pools are only available for the standard block size. Non-standard block size caches have a single DEFAULT pool.
Oracle Database Performance Tuning Guide for more information about multiple buffer pools
Oracle Database SQL Reference for the syntax of the BUFFER_POOL clause of the STORAGE clause
Redo Log Buffer
Redo entries are copied by Oracle database processes from the user’s memory space to the redo log buffer in the SGA. The redo entries take up continuous, sequential space in the buffer. The background process LGWR writes the redo log buffer to the active redo log file (or group of files) on disk.
«Log Writer Process (LGWR)» for more information about how the redo log buffer is written to disk
Oracle Database Backup and Recovery Basics for information about redo log files and groups
The initialization parameter LOG_BUFFER determines the size (in bytes) of the redo log buffer. In general, larger values reduce log file I/O, particularly if transactions are long or numerous. The default setting is either 512 kilobytes (KB) or 128 KB times the setting of the CPU_COUNT parameter, whichever is greater.
Shared Pool
The shared pool portion of the SGA contains the library cache, the dictionary cache, buffers for parallel execution messages, and control structures.
Library Cache
The library cache includes the shared SQL areas, private SQL areas (in the case of a shared server configuration), PL/SQL procedures and packages, and control structures such as locks and library cache handles.
Shared SQL areas are accessible to all users, so the library cache is contained in the shared pool within the SGA.
Shared SQL Areas and Private SQL Areas
Oracle represents each SQL statement it runs with a shared SQL area and a private SQL area. Oracle recognizes when two users are executing the same SQL statement and reuses the shared SQL area for those users. However, each user must have a separate copy of the statement’s private SQL area.
Shared SQL Areas
A shared SQL area contains the parse tree and execution plan for a given SQL statement. Oracle saves memory by using one shared SQL area for SQL statements run multiple times, which often happens when many users run the same application.
Oracle allocates memory from the shared pool when a new SQL statement is parsed, to store in the shared SQL area. The size of this memory depends on the complexity of the statement. If the entire shared pool has already been allocated, Oracle can deallocate items from the pool using a modified LRU (least recently used) algorithm until there is enough free space for the new statement’s shared SQL area. If Oracle deallocates a shared SQL area, the associated SQL statement must be reparsed and reassigned to another shared SQL area at its next execution.
PL/SQL Program Units and the Shared Pool
Oracle processes PL/SQL program units (procedures, functions, packages, anonymous blocks, and database triggers) much the same way it processes individual SQL statements. Oracle allocates a shared area to hold the parsed, compiled form of a program unit. Oracle allocates a private area to hold values specific to the session that runs the program unit, including local, global, and package variables (also known as package instantiation) and buffers for executing SQL. If more than one user runs the same program unit, then a single, shared area is used by all users, while each user maintains a separate copy of his or her private SQL area, holding values specific to his or her session.
Individual SQL statements contained within a PL/SQL program unit are processed as described in the previous sections. Despite their origins within a PL/SQL program unit, these SQL statements use a shared area to hold their parsed representations and a private area for each session that runs the statement.
Dictionary Cache
The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. Oracle accesses the data dictionary frequently during SQL statement parsing. This access is essential to the continuing operation of Oracle.
Allocation and Reuse of Memory in the Shared Pool
In general, any item (shared SQL area or dictionary row) in the shared pool remains until it is flushed according to a modified LRU algorithm. The memory for items that are not being used regularly is freed if space is required for new items that must be allocated some space in the shared pool. A modified LRU algorithm allows shared pool items that are used by many sessions to remain in memory as long as they are useful, even if the process that originally created the item terminates. As a result, the overhead and processing of SQL statements associated with a multiuser Oracle system is minimized.
When a SQL statement is submitted to Oracle for execution, Oracle automatically performs the following memory allocation steps:
Oracle checks the shared pool to see if a shared SQL area already exists for an identical statement. If so, that shared SQL area is used for the execution of the subsequent new instances of the statement. Alternatively, if there is no shared SQL area for a statement, Oracle allocates a new shared SQL area in the shared pool. In either case, the user’s private SQL area is associated with the shared SQL area that contains the statement.
A shared SQL area can be flushed from the shared pool, even if the shared SQL area corresponds to an open cursor that has not been used for some time. If the open cursor is subsequently used to run its statement, Oracle reparses the statement, and a new shared SQL area is allocated in the shared pool.
Oracle allocates a private SQL area on behalf of the session. The location of the private SQL area depends on the type of connection established for the session.
Oracle also flushes a shared SQL area from the shared pool in these circumstances:
When the ANALYZE statement is used to update or delete the statistics of a table, cluster, or index, all shared SQL areas that contain statements referencing the analyzed schema object are flushed from the shared pool. The next time a flushed statement is run, the statement is parsed in a new shared SQL area to reflect the new statistics for the schema object.
If a schema object is referenced in a SQL statement and that object is later modified in any way, the shared SQL area is invalidated (marked invalid), and the statement must be reparsed the next time it is run.
If you change a database’s global database name, all information is flushed from the shared pool.
The administrator can manually flush all information in the shared pool to assess the performance (with respect to the shared pool, not the data buffer cache) that can be expected after instance startup without shutting down the current instance. The statement ALTER SYSTEM FLUSH SHARED_POOL is used to do this.
«Shared SQL Areas and Private SQL Areas» for more information about the location of the private SQL area
Chapter 6, «Dependencies Among Schema Objects» for more information about the invalidation of SQL statements and dependency issues
Oracle Database SQL Reference for information about using ALTER SYSTEM FLUSH SHARED_POOL
Oracle Database Reference for information about V$SQL and V$SQLAREA dynamic views
Large Pool
The database administrator can configure an optional memory area called the large pool to provide large memory allocations for:
Session memory for the shared server and the Oracle XA interface (used where transactions interact with more than one database)
I/O server processes
Oracle backup and restore operations
By allocating session memory from the large pool for shared server, Oracle XA, or parallel query buffers, Oracle can use the shared pool primarily for caching shared SQL and avoid the performance overhead caused by shrinking the shared SQL cache.
In addition, the memory for Oracle backup and restore operations, for I/O server processes, and for parallel buffers is allocated in buffers of a few hundred kilobytes. The large pool is better able to satisfy such large memory requests than the shared pool.
The large pool does not have an LRU list. It is different from reserved space in the shared pool, which uses the same LRU list as other memory allocated from the shared pool.
«Shared Server Architecture» for information about allocating session memory from the large pool for the shared server
Oracle Database Performance Tuning Guide for more information about the large pool, reserve space in the shared pool, and I/O server processes
«Overview of Parallel Execution» for information about allocating memory for parallel execution
Java Pool
Java pool memory is used in server memory for all session-specific Java code and data within the JVM. Java pool memory is used in different ways, depending on what mode the Oracle server is running in.
The Java Pool Advisor statistics provide information about library cache memory used for Java and predict how changes in the size of the Java pool can affect the parse rate. The Java Pool Advisor is internally turned on when statistics_level is set to TYPICAL or higher. These statistics reset when the advisor is turned off.
Streams Pool
In a single database, you can specify that Streams memory be allocated from a pool in the SGA called the Streams pool. To configure the Streams pool, specify the size of the pool in bytes using the STREAMS_POOL_SIZE initialization parameter. If a Streams pool is not defined, then one is created automatically when Streams is first used.
If SGA_TARGET is set, then the SGA memory for the Streams pool comes from the global pool of SGA. If SGA_TARGET is not set, then SGA for the Streams pool is transferred from the buffer cache. This transfer takes place only after the first use of Streams. The amount transferred is 10% of the shared pool size.
Control of the SGA’s Use of Memory
Dynamic SGA provides external controls for increasing and decreasing Oracle’s use of physical memory. Together with the dynamic buffer cache, shared pool, and large pool, dynamic SGA allows the following:
The SGA can grow in response to a database administrator statement, up to an operating system specified maximum and the SG A_MAX_SIZE specification.
The SGA can shrink in response to a database administrator statement, to an Oracle prescribed minimum, usually an operating system preferred limit.
Both the buffer cache and the SGA pools can grow and shrink at runtime according to some internal, Oracle-managed policy.
Other SGA Initialization Parameters
You can use several initialization parameters to control how the SGA uses memory.
Physical Memory
The LOCK_SGA parameter locks the SGA into physical memory.
SGA Starting Address
The SHARED_MEMORY_ADDRESS and HI_SHARED_MEMORY_ADDRESS parameters specify the SGA’s starting address at runtime. These parameters are rarely used. For 64-bit platforms, HI_SHARED_MEMORY_ADDRESS specifies the high order 32 bits of the 64-bit address.
Extended Buffer Cache Mechanism
The USE_INDIRECT_DATA_BUFFERS parameter enables the use of the extended buffer cache mechanism for 32-bit platforms that can support more than 4 GB of physical memory. On platforms that do not support this much physical memory, this parameter is ignored.
Overview of the Program Global Areas
A program global area (PGA) is a memory region that contains data and control information for a server process. It is a nonshared memory created by Oracle when a server process is started. Access to it is exclusive to that server process and is read and written only by Oracle code acting on behalf of it. The total PGA memory allocated by each server process attached to an Oracle instance is also referred to as the aggregated PGA memory allocated by the instance.
«Connections and Sessions» for information about sessions
Content of the PGA
The content of the PGA memory varies, depending on whether the instance is running the shared server option. But generally speaking, the PGA memory can be classified as follows.
Private SQL Area
A private SQL area contains data such as bind information and runtime memory structures. Each session that issues a SQL statement has a private SQL area. Each user that submits the same SQL statement has his or her own private SQL area that uses a single shared SQL area. Thus, many private SQL areas can be associated with the same shared SQL area.
The private SQL area of a cursor is itself divided into two areas whose lifetimes are different:
The persistent area, which contains, for example, bind information. It is freed only when the cursor is closed.
The run-time area, which is freed when the execution is terminated.
The location of a private SQL area depends on the type of connection established for a session. If a session is connected through a dedicated server, private SQL areas are located in the server process’s PGA. However, if a session is connected through a shared server, part of the private SQL area is kept in the SGA.
«Connections and Sessions» for more information about sessions
«SQL Work Areas» for information about SELECT runtimes during a sort, hash-join, bitmap create, or bitmap merge
Cursors and SQL Areas
A private SQL area continues to exist until the corresponding cursor is closed or the statement handle is freed. Although Oracle frees the runtime area after the statement completes, the persistent area remains waiting. Application developers close all open cursors that will not be used again to free the persistent area and to minimize the amount of memory required for users of the application.
Session Memory
Session memory is the memory allocated to hold a session’s variables (logon information) and other information related to the session. For a shared server, the session memory is shared and not private.
SQL Work Areas
For complex queries (for example, decision-support queries), a big portion of the runtime area is dedicated to work areas allocated by memory-intensive operators such as the following:
Sort-based operators (order by, group-by, rollup, window function)
For example, a sort operator uses a work area (sometimes called the sort area) to perform the in-memory sort of a set of rows. Similarly, a hash-join operator uses a work area (also called the hash area) to build a hash table from its left input. If the amount of data to be processed by these two operators does not fit into a work area, then the input data is divided into smaller pieces. This allows some data pieces to be processed in memory while the rest are spilled to temporary disk storage to be processed later. Although bitmap operators do not spill to disk when their associated work area is too small, their complexity is inversely proportional to the size of their work area. Thus, these operators run faster with larger work area.
The size of a work area can be controlled and tuned. Generally, bigger database areas can significantly improve the performance of a particular operator at the cost of higher memory consumption. Optimally, the size of a work area is big enough such to accommodate the input data and auxiliary memory structures allocated by its associated SQL operator. If not, response time increases, because part of the input data must be spilled to temporary disk storage. In the extreme case, if the size of a work area is far too small compared to the input data size, multiple passes over the data pieces must be performed. This can dramatically increase the response time of the operator.
PGA Memory Management for Dedicated Mode
There are fixed views and columns that provide PGA memory use statistics. Most of these statistics are enabled when PGA_AGGREGATE_TARGET is set.
Statistics on allocation and use of work area memory can be viewed in the following dynamic performance views:
The following three columns in the V$PROCESS view report the PGA memory allocated and used by an Oracle process:
PGA_USED_MEM PGA_ALLOCATED_MEM PGA_MAX_MEM
The automatic PGA memory management mode applies to work areas allocated by both dedicated and shared Oracle database servers.
Oracle Database Reference for information about views
Oracle Database Performance Tuning Guide for information about using these views
Dedicated and Shared Servers
Memory allocation depends, in some specifics, on whether the system uses dedicated or shared server architecture. Table 8-1 shows the differences.
Table 8-1 Differences in Memory Allocation Between Dedicated and Shared Servers