PMON
SMON
MMAN
LARGE POOL IO Buffer Area
Shared
Cache
CJQn
SYSTEM GLOBAL AREA
SHARED POOL
Library
MMON
M
SQL Area
MidPt inter secti on
R U
Data Dictionary Cache
L R U
Response Queue
Result Cache Enqueue
Fixed Area
RECYC
CACH
LE
E SIZE
CACH
CAC HE_S
E SIZE
Latches
DB_
IZE
Other
PROGRAM GLOBAL AREA Stack Space
User Session Data
Cursor Statistics
Free Memory
Backup/Recovery Operations
UGA for shared Servers
Sort Extent Pool Streams Pool
Flashback Buffer
Redo Log Buffer
User Process
Used Memory
Request Queue Oracle XA Interface
KEEP
JAVA POOL
Sort Area DBWR
User Global Area
ARCn LGWR CKPT
Control Files Server Process
Database and Temporary files
Online Redo Log
Archive d Log
USER PROCESSES
ORACLE NET SERVICES
LISTENER.ORA
TNSNAMES.ORA
SQLNET.ORA
SYSTEM GLOBAL AREA a) LIBRARY CACHE b) SHARED POOL c) DICTIONARY CACHE d) SHARED SQL AREA
e) ENQUEUES f) LATCHES g) RESULT CACHE h) OTHER i)
FIXED AREA
DATABASE BUFFER CACHE
db_recycle_cache_size
db_keep_cache_size
REDO LOG BUFFER LARGE POOL JAVA POOL a) SORT EXTENT POOL b) STREAMS POOL c) FLASHBACK MEMORY DATABASE WRITER REDO LOG WRITER ARCHIVER LOG WRITER CHECKPOINT
USER PROCESSES When a user runs an application program (such as a Pro*C program) or an Oracle tool (such as Oracle Enterprise Manager or SQL*Plus), Oracle Database creates a user process to run the user's application.
ORACLE NET SERVICES supports network communication between a client application and a remote or local database running on a variety of operating systems. Oracle Net Services allows the database servers and the client applications (or servers acting as clients) that access it to run on separate machines, and provides a means for moving data between the nodes on a network. Oracle Net Services is also used for Inter Process Communication Communication if clients and servers are running on the same machine.
LISTENER.ORA is a SQL*Net configuration file used to configure Oracle Database Database Listeners (required to accept remote connection requests). This file normally resides in the ORACLE HOME\NETWORK\ADMIN HOME\NETWORK\ADMIN directory or in the directory define by the $TNS_ADMIN variable.
TNSNAMES.ORA is a SQL*Net configuration file that defines databases addresses for establishing connections to them. This file normally resides in the ORACLE HOME\NETWORK\ADMIN HOME\NETWORK\ADMIN directory.
SQLNET.ORA is a text file that provides SQL*Net with basic configuration details like tracing options, default domain, encryption, etc. This file can be found in the ORACLE HOME\NETWORK\ADMIN HOME\NETWORK\ADMIN directory.
SYSTEM GLOBAL AREA - System Global Area (SGA) is a group of shared memory areas that are dedicated to an Oracle “instance” . It consists of
Shared Pool Database Buffer Cache
Redo Log buffer
Large Pool
Java Pool
SHARED POOL is a RAM area within the RAM heap that is created at startup time. This is Since it is not possible to dedicate separate regions of memory for the shared pool components, the shared pool is usually the t he second-largest SGA memory memory area (depending on the size of the db_cache_size parameter).
All of the sub-areas in shared pool are controlled by the single shared_pool_size parameter. parameter. The shared pool is like a buffer for SQL statements. Oracle's parsing algorithm algorithm ensures that identical SQL statements statements do not have to to be parsed each each time they're they're executed. The shared pool pool is used to store SQL statements. The shared pool contains RAM memory regions that serve the following purposes: purposes:
LIBRARY CACHE Responsible for collecting, parsing, interpreting, and executing all of the SQL statements that go against the Oracle database. Contains the current SQL execution plan information. It also holds stored procedures and trigger code.
SHARED SQL AREA - The shared SQL area stores each SQL statement executed in the database. database. This area allows SQL execution plans to be reused by many users.
DICTIONARY CACHE The dictionary cache stores environmental information, which includes referential integrity, table definitions, indexing information, and other metadata stored within Oracle's internal tables.
RESULT CACHE is an area in the shared pool and contains the end results of a query execution.
LATCHES Background process needs one of the data structure to satisfy its purpose, it acquires a latch while it manipulates or looks for a shared resource. Latches are simple types of a lock that can be very quickly acquired and freed, low-level serialization mechanisms to protect shared data structures in the system global area (SGA). For example, latches protect the list of users currently accessing the database and protect the data structures describing the blocks in the buffer cache. A server or background process acquires a latch for a very short time while manipulating or looking at one of these structures. The implementation of latches is operating system dependent, particularly in regard to whether and how long a process will wait for a latch.
Advantages of Latches
It can be very quickly acquired and freed. There is a cleanup procedure that will be called if process dies while holding a latch. Synchronization of levels of latching - Process acquires a latch at a certain level and it cannot acquire another latch subsequently that is equal to or less than that level.
Prevents more than one process from executing the same piece of code at a given time. Latches have an associated level that is used to prevent deadlocks.
Limitations
Low-level of serialization (one at a time)
The implementation of latches is operating system dependent, particularly in regard to
whether and how long a process will wait for a latch. A server or background process acquires a latch for a very short time ti me while manipulating or looking at one of these structures. If it acquires a latch for more time, there is a possibility process may die. There is no ordered queue of waiters like in enqueues. Latch waiters may either use timers to wakeup and retry or spin (only in multiprocessors). Since all waiters are concurrently retrying (depending on the scheduler), anyone might get the latch and conceivably the first one to try might be the last one to get.
ENQUEUES Enqueues are another type of locking mechanism used in Oracle. Any object (Not only data structures) which can be concurrently(non serially) used, can be protected with enqueues. A good example is of locks on tables. We allow varying levels of sharing on tables e.g. two processes can lock a table in share mode(Read) or in share update mode(Read and Write) etc. Enqueue is obtained using an OS specific(not dependent) locking mechanism. mechanism. An enqueue allows the user to store a value in the lock, i.e the mode in which we are requesting it. The OS lock manager keeps track of the resources locked. If a process cannot be granted the lock because it is incompatible with the mode requested and the lock is requested with wait, the OS puts the requesting process on a wait queue which is serviced in FIFO. Advantages
Allows several concurrent processes to have varying degree of known resources.
Enqueue is obtained using an OS specific(not dependent) locking mechanism. mechanism. If a process cannot be granted lock then it puts the process in wait queue.
There is ordered queue of waiters.
Differences between Latches and Enqueues Latches - Low level of serialization. Enqueue - Concurrency
Latches - Process acquires latch for a short time and there is no ordered queue of waiters. Enqueue Obtained using OS Specific locking mechanism, there are waiters.
FIXED AREA contains several thousand atomic variables. These are small data structures, such as latches and pointers, which refer to other areas of the SGA. The size of the fixed area is static. It also contains general information about the state of the database and the instance which the background processes need to access.
OTHER The following table lists the different areas stored in the shared pool and their purpose: * PRIVATE SQL AREA - Private SQL areas are non-shared memory memory areas assigned to unique user sessions. * PL/SQL AREA - Used to hold parsed and compiled PL/SQL program units, allowing the execution plans to be shared by many users. * CONTROL STRUCTURES - Common control structure information, for example, lock information
DATABASE BUFFER CACHE
The SGA is used to store incoming data (the data buffers as defined by the db_cache_size parameter), and internal control information that is needed by the database. The amount of memory to be allocated to the SGA include db_cache_size, shared_pool_size and log_buffer . When Oracle receives a request to retrieve data, it will first check the internal memory structures to see if the data is already in the buffer. Following are its Tasks
Storage for data blocks that have been retrieved from data files.
Provides optimization boost for DML operations (UPDATES)
Managed via the LRU L RU algorithm
db_keep_cache_size db_keep_cache_size - Segments that contain frequently accessed blocks should be assigned to the keep buffer pool so that the blocks of those segments will not be inadvertently removed, thus impacting performance. performance. db_recycle_cache_size db_recycle_cache_size - Any segments whose blocks tend to be accessed with less frequency should be assigned to the recycle pool so that it does not flush the other segments, either in the default cache or the keep pool. REDO LOG BUFFER
The redo log buffer is a RAM area (defined by the initialization parameter log_buffer) log_buffer) that works to save changes to data, in case something fails and Oracle has to put it back into its original state.
When Oracle SQL updates a table (a process called Data Manipulation Language, Language, or DML), redo images are created and stored in the redo log buffer
Serves for assistance with database recovery tasks Records all changes made to database blocks
Places changes recorded to redo entries for redo logs
Oracle will eventually flush the redo log buffer to disk, only after a commit operation occurs.
LARGE POOL This area is only used if shared server architecture, also called multi-threaded server (MTS), is used, or if parallel query is utilized. The large pool holds the user global areas when MTS is used and holds the parallel query execution message queues for parallel query
The large pool is optional memory component and it provides RAM for
UGA holds session based information for the a shared server
Oracle XA Interface is involved in case of distributed transactions
I/O Server Processes Parallel Query Buffers
Oracle Backup and Restore Operations using RMAN.
JAVA POOL
Caching parsed Java programs, used for java objects, Java methods and other java execution memory. memory. java_pool_size parameter controls the amount amount of memory memory for this area.
The JAVA Pool holds the JAVA execution code in a similar manner t o the PL/SQL cache in the shared pool. The JAVA pool is used by many internal int ernal routines, such as import and a nd export, and should be sized at approxi mately 60 megabytes if no other JAVA will be utilized in the user applications.
SORT EXTENT POOL
The SEP tracks extents either in the shared pool area, ie. the UGA in MTS instances and in the PGA user's process area in non-MTS instances. Thus the SEP is always in the SGA since it tracks sort extents but the actual sort extents themselves are in several areas. STREAMS POOL
Cache Oracle Streams objects. Oracle streams allows data propagation between Oracle Databases Databases (homogeneous) (homogeneous) and between Oracle and non-Oracle databases (heterogeneous environment). Oracle Streams can be used for:
Replication
Message Queuing Loading data into a Data Warehouse Event Notification Notification Data Protection
FLASHBACK MEMORY
Flashback lets you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery The flashback memory buffer area is used with the flashback features which were first introduced i ntroduced in Oracle 10g such as the ability to perform flashback transaction query(retrieves data from a past point in time), flashback table(recovers a table to its state at a past point in time, without having to perform a point in time recovery), flashback flashback database(restore the whole database back to a point in time) and flashback versions(shows you different versions of data rows, plus start and end times of a particular transaction that created that row query), flashback drop( allows you to reverse the effects of a drop table statement, statement, without resorting to a point-in-time recovery). There are a number of flashback flashback levels
row level - flashback query, flashback versions query, flashback transaction transaction query
table level -flashback -flashback table, flashback drop
database level - flashback database database
DATABASE WRITER
Process that writes the contents of Database buffer cache to Data files. REDO LOG WRITER
Process that writes the contents of the Redo Log buffer to Redo Log Writer ARCHIVER LOG WRITER
Process that copies Redo log files to a designated storage device after a log switch occurs CHECKPOINT
Process that timestamps all datafiles and control files to indicate that a checkpoint has occurred. Checkpoint is the event – when occurs, DBWR process writes all the modified buffers in SGA to the Data files and control file.
REFERENCES
http://www.dba-oracle.com/
https://asktom.oracle.com/ http://www.toadworld.com/ http://parthokonar.wordpress.co http://parthokonar.wordpress.com/2009 m/2009/09/01/la /09/01/latches-and-enqueu tches-and-enqueues/ es/ http://jonathanlewis.wordpress.co http://jonathanlewis.wordpress.com/2007 m/2007/01/21/s /01/21/shared-sql/ hared-sql/ http://oracledba.ezpowell.co http://oracledba.ezpowell.com/oracle/in m/oracle/instanceArchite stanceArchitectureInternalInstance ctureInternalInstanceMemory.htm Memory.htmll http://arup.blogspot.in/2013 http://arup.blogspot.in/2013/04/stream /04/streams-pool-is-only s-pool-is-only-for-streams-for-streams-think.html think.html http://www.datadisk.co.uk/html http://www.datad isk.co.uk/html_docs/orac _docs/oracle/flashba le/flashback.htm ck.htm
