Understanding Oracle’s underlying architecture
Posted: September 23rd, 2008 | Author: TnT Admin | Filed under: Concepts | Tags: Architecture, Oracle | No Comments »
Before we are able to analyze and determine tuning areas, we need to understand Oracle Server Architecture and it’s underlying mechanism. Oracle Server Architecture consist of two main categories: logical (memory) structures and physical (or file) structures. The Oracle memory structure is collectively known as Oracle Instance. While the physical file structures are collectively known as Oracle database. Together, they are call an Oracle Server.
Oracle Instance
The Oracle Instance consist of the Oracle’s main memory structure, System Global Area (SGA) and the Oracle background processes. The SGA is made up of the following components: (a) Shared Pool, (b) Database Buffer Cache, (c) Java Pool and (d) Redo Log Buffer. In Oracle 9.2 and later, (e) Streams Pool was introduced into the SGA. Other components such as (f) Large Pool may also exist as it is dependent on the optional features from Oracle. The background processes, included (a) System Monitor (SMON), (b) Process Monitor (PMON), (c) Database Writer (DBW0), (d) Log Writer (LGWR) and the (e) Checkpoint Process (CKPT). Other optional processes maybe running as well such as Recoverer (RECO).
The following illustrates the entire System Global Area Components:
The components of the instance – the SGA and the required background processes – acquired space in the server’s memory immediately upon startup of the database. While the required background processes will always acquire the same amount of memory for their purposes, there are four init.ora parameters are the primary determinants of SGA’s memory requirements. They are:
- DB_CACHE_SIZE
- SHARED_POOL_SIZE
- LOG_BUFFER
- LARGE_POOL_SIZE
However, there is still one parameter in init.ora that determines the overall size of the System Global Area, which is SGA_MAX_SIZE. Within this parameter contains two component, Shared Pool and Database Buffer Cache that can be dynamically resized using ALTER SYSTEM command. If the value for SGA_MAX_SIZE is less that the sum of the component at instance startup, Oracle will ignore the setting for SGA_MAX_SIZE and use the sum of the components as the value instead.
Oracle Database
An Oracle database is made up of physical files called (a) control files, (b) datafiles and (c) redo logs. Additional files associated with the database are (d) init.ora, trace and alert log files, password file, and any archived redo log files. The usage of the files are as followed:
| File Name |
Information Contained in File |
| Control File | Locations of other physical files, database name, database block size, database character set, and recovery information. |
| Datafile | Data blocks from all database segments including tables and indexes |
| Redo Log | A record of all changes made to the database which is used for recovery |
| init.ora | A listing of configuration parameters for the instance and database. |
| Archived log file | Copy of the contents of previous online Redo log, used for recovery |
| Password file | Users who have been granted the SYSDBA and SYSOPER privilege |
| Alert log file | Informatioal messages and error messages generated by database activity. |
| Trace files | Informational messages and error messages generated by users or background processes |
Of all the files mentioned, the init.ora file have great importance in Oracle Server performance tuning with many parameters associated to improve the performance. The default location of init.ora on Windows is %ORACLE_HOME%\database while Unix is $ORACLE_HOME/dbs.
(Source: OCP Oracle 9i Performance Tuning Study Guide)
Following this article, we will be covering the following sections on the individual components that can be tuned in the Oracle Instance and the Database itself.
Leave a Reply