- Oracle Architecture Components
- Describe the Oracle Server Architecture and its main components.
- An instance: Which is basically a set of memory structures and background processes that cache the most used data, and serve as an exchange point between the users(clients) and the datafiles that have the information on disks.
- Datafiles : These datafiles are located on hard disks, and they compose all the data that is stored in Oracle,control files,tablespaces, redo-log files,etc.
- Oracle-Software : This contains the Oracle-kernel which is much like a sub-operating system on top of the host system , because it coordinates the memory-structures and processes (Instance) and the file updates (Datafiles) on the hard drives.
- List the Structures involved in connecting a user to an Oracle Instance
Oracle is basically formed from :
The Oracle server has a set of memory structures and operating process called "The Instance" which control the actions that occur between the end user and the database,it main components are:
- Database Buffer Cache(depends on DB_BLOCK_SIZE & DB_BLOCK_BUFFERS parameters)
- Redo Log Buffer (depends on LOG_BUFFER , monitored V$SYSSTAT )
- Shared Pool (depends on SHARED_POOL_SIZE )
- Library Cache
- Shared SQL Areas (Contains the parse tree and execution plan for a single SQL statement)
- Private SQL Areas (Contains data such as bind information and runtime buffers,each session that issues a SQL statement has a private SQL area;many private SQL areas can be associated with the same shared SQL area, if a session is connected via a dedicated server private SQL areas are in the users PGA. If the sessionis connected via the multi-threaded server, the private area is kept in the SGA )
- Persistent (Contains bind information that persists accorss executions,code for datatype conversion and other state information, in contrast to Runtime,the persistent area remains waiting after the statement completes, the open cursors that will not be used shoudld be closed to free the persistant area and to minize the amount of memory required for the application)
- Runtime ( Contains information used while the SQL statement is being executed, it is the first step in an execute REQUEST, it releases this memory when the statement finishes)
- Dictonary Cache (Also known as the row cache, because it holds data as rows instead of buffers)
- Control Structures
- Large Pool (Optional,used for large memory allocations)
- PGA : A PGA (Process Global area ) is a memory region containing data and control information for a single process. One PGA is allocate for each server process.
- Stack Space: A PGA always contains stack space, which is memory allocate to hold a sessions variables,arrays, and other information.
- Session Information : If the instance is in single-serer mode, the PGA also contains information about the users session, such a private SQL areas. If the instane is in multi-threaded server, this session information is not in the PGA, but is instead allocated in the SGA. The PGA's initial size is fixed and OS specific, however the parameters OPEN_LINKS and DB_FILES affect the sizes of PGAs.
- Sort Areas : Use memory from the PGA of the Oracle server process that performs the sort on behalf of the user process. However, a part of the sort area ( up to SORT_AREA_RETAINED) exists in the runtime area of the process's private SQL area. The sort area can grow up to limit SORT_AREA_SIZE.
- Software Code Areas : Portions of memory used to store Oracle code
- Background Processes:
- PMON (Process Monitor, performs process recovery when a user processes fails, it is reponsible for cleaning up the database buffer cache and freein resources that the user process was using )
- SMON (System Monitor, Performs crash recovery , if necessary at instance startup, it is also responsible for coalescing contiguos free space within tablespaces, and cleaning up temporary segments that are no longer in use)
- DBWR (Writes the modified (dirty) buffers in the database buffer cache to disk)
- LGWR (Writes the redo log buffer contents to a redo log file on disk, it write of the buffer to disk when : A user process commits a transaction, every three seconds, when the redo-log buffer is one-third full, when a DBWn process writes modified buffers to disk)
- ARCH : Copies online redo log files to a designated storage device once they become ful or when the ALTER SYSTEM SWITCH LOGFILE command forces a log switch.
- CKPT (when a checkpoint ocurrs , Oracle must update the headers of all datafiles to record the details of the checkpoint.)
- RECO : Used with distributed database configurations to automatically resolve failed distributed transactions
- SNPn ( Job Queue/Snapshot Process): With the distributed database configuration, up to 36 job queues can automatically refresh table snapshots.
- LCKn : In Oracle Parallel Server, a lock process provides inter-instance locking
- QMN ( Queue Monitor) (Optional for Oracle Advanced Queuing which monitors the message queues)
- Snn (shared Server Processes serve multiple client requests in the multi-threaded server configuration )
- Dnn (Dispatcher Processes, suppor multi-threaded configuration by allowing user processes to share limited number of server processes.
- Pnnn (Parallel query processes)
- List the Stages in processing:queries,DML statements, COMMITS.
- The user commits, this signals the LGWR to flush the redo-log buffer to the online redo-log file.
- The redo information that was generated causes de redo-log buffer to become one-third full. This triggers a redo-log buffer flush by LGRW
- The number of dirty blocks reached a threshold length. This triggers DBWR to flush all the dirty blocks in the database buffer cache to the data files, which in turn also causes LGWR to flush the redo-log buffers to the online-redo log files.
- A database checkpoint occurs. This triggers the database buffer cache ( with DBWR) as well as the redo-log buffers ( with LGRW) to flush.
- The number of available free buffers in the buffer cache drops below the threshold value. This also causes the database buffer cache to flush
- An unrecoverable error ocurrs. This forces the transaction to be terminated and rolled back and an error reported back to the server session.
Queries : The server session hashes de SQL statement passed to it and compares that hash number with the hash numbers of statements already saved in the Shared SQL area. If an exact duplicate of the statement is found in the shared pool, the parsed form of the statement and the execution plan that are already stored are used. If a match is not found in the shared pool, the server session parses de statement.
Next, the server checks to see wheter the data blocks necessary to complete the transaction (query) are already stored in the database buffer cache. If the block are not in the buffer, the server reads the necessary blocks from the data files and copies them into the cache. NOW it returns the information.
DML Statements : It performs the same steps up to when the data is copied into the buffer (data block buffer cache), before it returns the infromation (as is the case with a query),the blocks in memory (the cache) are modified accordingly. Once they are modified in memory these blocks are marked as dirty, and are placed on the dirty list. Redo log information is also generated on this transaction and placed in the redo-log cache. Up to this point any of the following can occur:
NOTE: The transaction never records as successful until the redo-log buffer successfully writes to the online redo-log files. This demonstrates the importance of the LGRW and ARCH processes.