| About the Shared Global Area (SGA) |
|
SGA_MAX_SIZE : specifies the maximum size of the SGA for the lifetime of the instance.
- If not specified, defaults to the sum of all components specified or defaulted at initialization time.
SQL> show parameter sga_max_size
NAME TYPE VALUE
-------------- ----------- ------
sga_max_size big integer 1216M
- You enable automatic shared memory management by setting
SGA_TARGET to a nonzero value.
STATISTICS_LEVEL must be set to TYPICAL or ALL
|
- The entire SGA should fit in real memory. If virtual memory needs to be used performance decreases dramatically.
- Use
V$SGA_TARGET_ADVICE
to help decide on a value for SGA_TARGET
To set the value for SGA_TARGET:
(1) find out how much space the SGA is using by:
SQL> select (
2 ( select sum(value) from v$sga) -
3 ( select current_size from v$sga_dynamic_free_memory)
4 ) "sga_target"
5 from dual;
sga_target
----------
785674240
(2) Edit the text initialization parameter file and restart the database, or issue the statements below:
(a) SQL> ALTER SYSTEM SET SGA_TARGET=value [SCOPE={SPFILE|MEMORY|BOTH}]
(b) SQL> ALTER SYSTEM SET <sga_component_parameter> = 0; -- for every component.
Database Buffer Cache
- Optimizes physical I/O:
- After a COMMIT: writes redo to disk
- DBWn performs lazy writes in the background (writes cold, dirty blocks to disk)
- Buffer states can be:
- Unused - buffer is available for use
- Clean - Used earlier and now contains a read-consistent version of a block as of a point in time. Does not need to be checkpointed. Can be pinned to the cache so it does not age out of memory.
- Dirty - Contains modified data not yet written to disk. Must be checkpointed befor reusing.
- Buffer Modes
- Current mode get - same as db block get: read block as is in buffer, including when contains uncommitted changes
- Consistent mode - provides a read-consistent version of a block (read undo data if needed)
- Buffer reads
- Cache hit - block is on buffer cache. Logical read occurs. If flash cache list is used, an optimized physical read takes place.
- Cache miss - block is not in memory. A physical read is required.
- Buffer caceh hit ratio - The relative frequency of logical versus physical reads
.
- Buffer pools
- Default pool - where blocks are normally cached. (only pool by default)
- Keep pool - To keep frequently accessed blocks that would have to be aged out for lack of space. Pinned blocks stay here.
- Recycle pool - for infrequently used blocks. Full table scan blocks go here.
- non-default block size pools - tablespaces with different block sizes have their blocks stored on size-specific pools.
Log Buffer
- LWGR writes redo sequentially to disk.
- DBWn performs scattered writes of data blocks to disk.
- LOG_BUFFER parameter specifies the amount of memory used for the Redo log Buffer.
- This value is taken out of the the area specified in SGA_TARGET
Shared Pool
- Stores parsed SQL, PL/SQL code, system parameters and data dictionary information
- Library Cache - Stores executable SQL, parsed PL/SQL code, locks and library cache handles and private sql areas (in shared server environment).
- Library cache hit - also known as soft parse: Occurs when a parsed representation of a SQL statements exists in the library cache and is reused.
- Libray cache miss (hard parse): Parsed representation is not found in the library cache and has to be recreated.
- Use
ALTER SYSTEM FLUSH SHARED_POOL
to manually remove all information in the shared pool
- Use
RESULT_CACHE
hint to indicate that the results of a SQL query should be stored in the result cache.
RESULT_CACHE_MODE
parameter specify whether the SQL query result cache is used for all queries or only for annontated queries.
Large Pool
- Optional area intended for large memory allocations (i.e. buffers for RMAN I/O slaves)
Java Pool
- Stores all session-sepcific Java code and data within the Java Virtual Machine (JVM).
- It includes java objects that are migrated to the Java session space at end-of-call.
- Includes the share part of each java class (methods an dread-only memory such as code vectors). (dedicated server connections only).
Streams Pool
- Stores buffered queue messages and provides memory for Oracle Streams capture and apply processes