Please refer to Oracle documentation
Memory-->
Oracle uses memory for
- Holding on information about code(program)
- User session info (active/passive both)
- Info about what code(program) is doing --> active, executing, running..
- Information that needs to be shared among various process (ex locking)
- For caching data (redo blocks, data blocks)
Basic Memory Structures are
- SGA
- PGA
- Software Code Areas
SGA--> system global area
- contains data & control information about instance
- Data in it is shared among all users--> hence Shared Global Area
- SGA + oracle process==> Oracle instance
- Oracle allocates memory for SGA when you start instance & OS reclaims it when you shutdown instance
- Each instance has its own SGA
- SGA --> read/write
SGA consists of following data structures
- Buffer Cache
- Redo log Buffer
- Shared Pool ( Data dictionary + library cache)
- Java pool
- Large pool (optional)
- other miscellaneous pools
Fixed SGA --> contains info about state of DB/ instance, which background process need to access, no user data is stored here.
SGA also contains info about communication between process (such as locking)
PGA
- contains data & control information for a server process or background process
- non shared memory
- total memory allocated for all individual PGA"s --> instance PGA
Data structure components of SGA
Buffer Cache
- A pool which holds data blocks read from datafiles
- All users connected to instance share access to buffer cache
- Buffers here are divided into 2 lists
- write list & LRU (least recently used) list
dirty buffers-->buffers having data that has been modified but not written to disk
free buffers--> free buffers
Pinned --> which are being processed/accessed
write list --> has those buffers which contains data & not written to disk yet
DBWR will periodically move them to datafiles
LRU list--> contains free, pinned & dirty buffers.
Cache hit--> if process finds data in memory
Cache miss--> if process doesn't find it in memory & needs to get that info from datafiles
Each process (SP or Background process) when it accesses buffer it moves it to the MRU side, so on frequent access ( different queries) buffers are moved towards MRU end & dirty buffers age /go toward the LRU end of the LRU list.
So when a user process requires data it searches in buffer cache, it could be a cache hit or cache miss.
Say it was a cache miss, so before reading data into cache, it needs to find free buffers, the process searches LRU list starting at LRU end for free buffers, it either finds or it doesn't find (till a threshold limit is reached).
If the user finds a dirty buffer as it searches the LRU list, it will move the dirty buffer to write list & still continues to search.
If it finds a free buffer it will read the block into buffer and move the buffer to MRU end of LRU list
OR
If its searching & doesn't find any free buffers till it reaches a threshold limit, process stops & signals the DBWn to write the dirty buffers to the disk.
For full table scans the blocks read into memory are placed on the MRU end of the LRU list, so they age out more frequently, although this can be avoided by using "CACHE" clause in the alter/create table statement.
Good to use this with small lookup tables or static tables.
REDOLOG BUFFER (RLB)
- This buffer holds the changes made to database.
- Info is stored as redo entries
- Redo entries --> it is the info which is necessary to reconstruct/redo the changes made to database by insert, update, delete, create, alter, drop.
- Redo entries are used for recovery
Redo entries are copied from user's memory space to redolog buffer (in SGA), it is a circular buffer. Redo entries however take up continuous, sequential space in RLB.
LGWR writes then from RLB to redologfiles.
SHARED POOL
Consists of
LIBRARY CACHE, DICTIONARY CACHE, RESULTS CACHE, PARALLEL EXECUTION MESSAGES & CONTROL STUCTURES
LIBRARY CACHE
consists of
Shared & private sql areas
Oracle represents each sql statement it runs with a shared sql area & private sql area.
If multiple users are running the same sql statement then oracle recognizes it & uses the shared sql area for those users. however each user must have a seperate copy of statements in private sql area.
Shared sql area == parse tree + execution plan
When a new sql statement is parsed Oracle allocates memory from shared pool, size it allocates depends on the complexity of sql statement. If entire SP has been allocated, Oracle can de-allocate items from SP using LRU algorithm until there is enough free space for the statements shared sql area. Also the above de-allocation (of shared sql are) does happen then the sql statement must be reparsed for next execution.
PL/SQL program units
Oracle Database processes PL/SQL program units (procedures, functions, packages, anonymous blocks, and database triggers)
processes same way it processes individual SQL statements.
Oracle Database allocates a shared area to hold the parsed, compiled form of a program unit. Oracle Database 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.
when a sql statement is submitted for execution in a database, oracle checks if shared sql area exists for an identical statement, if yes===> it uses this shared sql area for subsequent executions. if NO shared sql area is allocated===> then oracle allocates shared sql area in the shared pool.
IN both the cases user's private sql area is related to shared sql area that contains the statement.
Shared sql area can be flushed out from the shared pool (even if the shared sql area corresponds to open cursor, the only change is next time open cursor is used Oracle will re-parse the statement & allocate a new shared sql area in shared pool).
When does/can the shared sql area get flushed from shared pool
Oracle Database 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.
The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users.
Oracle Database accesses the data dictionary frequently during SQL statement parsing.
This access is essential to the continuing operation of Oracle Database.
The data dictionary is accessed so often by Oracle Database that two special locations in memory are designated to hold dictionary data.
One area is called the data dictionary cache, also known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data).
The other area in memory to hold dictionary data is the library cache.
All Oracle Database user processes share these two caches for access to data dictionary information.
The result cache is composed of the SQL query result cache and PL/SQL function result cache, which share the same infrastructure.
dbms_result_cache to administer cached results, turn on & off result caching
SQL Query Result Cache-->
It contains the results of queries & caches it in memory
DB can use these cached results & improve performance for next runs
hint--> result cache can be used to indicate if the results are to be stored in sql query result cache
RESULTS_CACHE_MODE parameter helps us to control if query results needs to be cached or not on system wide basis
The database automatically invalidates a cached result whenever a transaction modifies the data or metadata of any of the database objects used to construct that cached result.
PL/SQL Function Result Cache
A PL/SQL function is sometimes used to return the result of a computation whose inputs are one or several parameterized queries issued by the function. In some cases, these queries access data that changes very infrequently compared to the frequency of calling the function. You can include syntax in the source text of a PL/SQL function to request that its results be cached and, to ensure correctness, that the cache be purged when any of a list of tables experiences DML. The look-up key for the cache is the combination of actual arguments with which the function is invoked. When a particular invocation of the result-cached function is a cache hit, then the function body is not executed; instead, the cached value is returned immediately.
Large Pool
The database administrator can configure an optional memory area called the large pool to provide large memory allocations for:
By allocating memory from the large pool for shared server, Oracle XA, or parallel query buffers, Oracle Database can use the shared pool primarily for caching shared SQL and avoid the performance overhead otherwise caused by de-allocating memory from the shared SQL cache.
In addition, the memory for Oracle Database 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.
Java Pool
This pool is used for all session-specific Java code and data within the JVM. 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
Used only when oracle streams is used. This component of sga stores queue messages & provides memory for streams capture & apply processes. Default size is 0, it grows as needed when Oracle streams is used.
PGA Program Global Area
Oracle allocates PGA for each server process/Background Process
PGA is used to process sql statements, hold logon & other session info.
instance PGA= sum of individual pga's
Only instance PGA is set & database distributes memory to individual PGA's as needed
Content of PGA
Session memory---> memory allocated to hold a session's variables (login info) & other info related to session {for a shared server the session memory is shared not private}
Private sql area--> it contains info such as bind variable values, query execution state info, query execution work areas. Each session issuing a sql statement has a private sql area. Each user that submits the same sql statement has his/her own private sql area that uses a single shared sql area===> many private sql areas are related to same shared sql area.
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.
Cursors and SQL Areas
Developer can explicitly open cursors, or handles to specific private SQL areas, and use them as a named resource throughout the execution of the program. Recursive cursors that Oracle Database issues implicitly for some SQL statements also use shared SQL areas.
The management of private SQL areas is the responsibility of the user process. The allocation and deallocation of private SQL areas depends largely on which application tool you are using, although the number of private SQL areas that a user process can allocate is always limited by the initialization parameter OPEN_CURSORS. The default value of this parameter is 50.
A private SQL area continues to exist until the corresponding cursor is closed or the statement handle is freed. Although Oracle Database frees the run-time 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.
Private SQL Area Components
The private SQL area of a cursor is itself divided into two areas whose lifetimes are different:
· The persistent area—This area contains bind variable values. It is freed only when the cursor is closed.
· The runtime area—Oracle Database creates this area as the first step of an execute request. It contains the following structures:
· Query execution state information
For example, for a full table scan, this area contains information on the progress of the scan
· SQL work areas
These areas are allocated as needed for memory-intensive operations like sorting or hash-joins.
For DML, the run-time area is freed when the statement finishes running. For queries, it is freed after all rows are fetched or the query is canceled.
SQL Work Areas
SQL work areas are allocated to support memory-intensive operators such as the following:
· Sort-based operators (order by, group-by, rollup, window function)
· Hash-join
· Bitmap merge
· Bitmap create
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, the input data is divided into smaller pieces. This enables 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. The database automatically tunes work area sizes when automatic PGA memory management is enabled.
Generally, bigger work 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 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.
if dedicated server--> private sql area is located in server process's PGA
if Shared server==> part of private sql area is kept in SGA
Memory management--> goal is to manage different structures (SGA & PGA)
Different methods
AMM--> automatic memory management (from11g can manage both sga & pga automatically). You can designate total memory size to be used by instance oracle manages dynamic exchanges between sga & pga. ( Implied==> sga components & pga components are tuned as well)
ASMM (Automatic shared memory management) --> if you need to control SGA you can disable AMM & enable ASMM ( With ASMM, you set target and maximum sizes for the SGA. The database then tunes the total size of the SGA to your designated target, and dynamically tunes the sizes of all SGA components.)
MSMM (Memory shared Memory management)--> if you want to have fine grain control over SGA, i.e. want to control the pools within SGA you can disable both AMM & ASMM. It gives the flexiblity of manually tuning SGA pools on need basis.
APMM(Automatic PGA Memory Management)--> when you disable AMM & enable ASMM or MSMM you implicitly enable Automatic PGA memory management. In this method you set a target size for instance PGA, Oracle will then tune the size of instance PGA to the target size & dynamically tunes the sizes of individual PGA's. Since APMM is the default method for instance PGA, if you do not set it explicity, Oracle will compute & configure a reasonable default size.
MPMM (Manual PGA Memory Management)
Earlier versions of Oracle Database required the DBA to manually specify the maximum work area size for each type of SQL operator (such as sort or hash-join). This proved to be very difficult, because the workload is always changing. Although the current release of Oracle Database supports this manual PGA memory management method, Oracle strongly recommends that you leave automatic PGA memory management enabled.
if you create Database using dbca then AMM is enabled when you select basic installation
if you choose advanced installation it lets you choose from
AMM OR "ASMM +APMM" OR "MSMM + APMM"
if you create database manually & omit the parameters for memory management then it sets MSMM + APMM.
