Oracle Databases - Part I, General Database structures 

An Oracle database can roughly, be divided into 3 main categories;

 1. Memory structures

2. Database files, control files and the parameter file.

3. Processes

This article takes its starting point; for users with little knowledge about Oracle systems, and/or developers who looks for the Oracle database basic structures. All content below - is based upon an Oracle Dedicated server configuration, other server configurations, are at this time, not discussed. Primary focus for the article are; Database Memory structures.

DML, Data Manipulation language, INSERT, UPDATE, DELETE inclusive SELECT. DCL, Data Control Language, GRANT, REVOKE - and DDL, Data Definition Language, CREATE TABLE, CREATE INDEX, will be described in an later article.

To execute SQL against my Oracle Database, I am using TOAD - you will find a freeware version visiting Oracle Konsulenter. On the same address, you will find SQL Developer as well. A typical Oracle installation, also comes with SQL*Plus, and that will work fine too.

Lets get started.

1. Memory structures

The Memory structure, is generally refered to as the SGA (System Global Area). The SGA consists of 3 units, in overall terms.

1. Shared pool  is as the name suggests, a place where things are shared - Talking Oracle, its not hard to imagine, the system serves several requests, for fetching data and manipulating  data, some of  which are identical statements. And because of identical statements - a natural need for "re-cycling" exists.

These things are saved in Shared Pool;

Optimized query plans, security checks, parsed sql statements, packages and object informations. 

Lets imagine, we wanted to fetch some data, from our company's main employee table - we would use SQL to issue an;

SELECT * FROM EMPLOYEES;

Maybe you don't directly, yourself, issue the SQL statement - but through an application, developed for your company. Maybe using Oracle Forms, PHP and something else. However that particular statement, will be executed each time you press the button; "Get Employees".

As you can imagine - each time that statement is executed through an application - each time, it will be the same 100% identical statement.

Instead of having to re-parse and re-evaluate a 100% identical statement - Oracle will try to use the parsed version of the statement - saving time for us. The shared pool - consist of more units in overall terms.

Library Cache and Data Dictionary Cache.

The Dictionary Cache, is where Oracle checks for existence, of an given object, and if the user requesting that object, have the proper database rights, to view that object. The Dictionary Cache is a heavy loaded cache, and used so often - that its actually located in 2 places. The first location of the Dictionary cache, is often refered to as the Row Cache and the other The Library Cache. The Oracle User processes uses, these two areas to access dictionary information.

Lets imagine a user - call her Zara. She is sitting by her local PC and doing her work as a developer. To work on an Oracle database you need a user account. A user account don't have any rights, when created - Rights to create objects, manipulate data are typically administered, by the local database administrator. He and She, usually manages this, using either, individual privileges or roles (containing several individual privileges). Users, when given rights to manipulated their own objects, can act as their own administrators and give (GRANT) another user the right to see, execute, manipulate with that users objects.   

Zara is using TOAD, as her favorite developer tool. In this minute she's logged on the Oracle database as Zara/Zara, using TOAD. In fact, right now she executes the following statement, to view some data in the account table.

SELECT SUM(amount), change_date, account_type, account_ FROM ACCOUNT WHERE account_='2345657-3345' GROUP BY account_type,account_,change_date

When Zara press "execute" to that statement - several things happen. 1) is this a valid SQL statement 2) does ACCOUNT exist 3) Do Zara have access rights to select from ACCOUNT etc. is checked by Oracle.

If everything is in order, the statement will be parsed. Thats; Oracle will try to find the best execution path.

When all this have been checked, data will be returned to Zara. 

Executing the same 100% statement again, will use the same, but parsed representation (assuming that there are not 2000 users on the system - in that case depending on the time gap between execution - her parse tree, might have aged out and re-parse will be necessary).

What about the data ? Well its easy to imagine that data will be faster read if in memory. Thats also what Oracle is doing. If you take Zara's SELECT from before. Oracle will try to locate data - identical statements - BEFORE - performing parse and any physical reads. So if Zara is the only one, wanting that data - Oracle will located data though the SQL MANAGER, fetch the data, by reading physically from disk, and place the result in the Data Buffer Cache. The data will then, be send to Zara. Executing the same statement - the first place to look for the data, performing a logical read, is the data buffer cache then disk.

The Library Cache is divided into

- Shared SQL Area

This area in memory contains parse trees, and execution path, for a given SQL expression. If an expression are used often - maybe from a package or an forms application - its likely the execution path for that statement is to be found here, along with the parse tree. If an expression ain't used often - it will age out from the list - LRU (least recently used).

- PLSQL procedures

The same thing goes for procedures, triggers, anonymous blocks and functions - the compiled and parsed form are saved - and reused if possible.

- Control structures  (latches and locks)

Are also to be found in this area. Latches are split second locks on Oracle objects, whereas locks are table locks, row level locks - locks kept for longer than a split second.

2. Database Buffer Cache is another structure within the SGA - the cache is also quit heavy loaded - and like the shared pool, its divided into sub buffers/caches.

The main purpose with the Database buffer cache, is to reduce physical I/O. When a data block is read by Oracle, physically , Oracle places that block within the database buffer. The filosofi is; that chances are, that particular block might be used, again sometime - when that need occurs - I rather read the block from memory than from disk - thus serve my users faster. Reading from memory is more efficient, than readings from disk (measured in time).

Blocks on the database buffer cache are organized from MRU (most recently used) to LRU (least recently used) blocks. When a block is accessed - its placed in the MRU end of the list - and thereby existing blocks changes 1 down to wards the LRU end of the list.

When data are read from disk - eventually the database buffer is full - and it will be necessary to make room for more data. It will be the blocks in the LRU end - that will have to leave the database buffer cache.

The database buffer itself consists of 3 pools;

- Keep Pool (purpose is to hold objects that always needs to cached - lookup_tables see db_keep_cache_size)

- Recycle Pool (for larger objects see db_recycle_cache_size)

- Default pool (all the other see x$kcbwbpd)

Going back to Zara, she's about to update the account table - she found what she was looking for before.

UPDATE ACCOUNT SET account_lockdown=-1 WHERE account_= '2345657-3345';

First off all - the Database buffer Cache - will hold the record BEFORE the update and AFTER the update. Basically statements like this one - can't be seen by other users on the system - before Zara either COMMITs or ROLLBACKs the statement. In order to accomplish that Oracle holds the before and after record - other users read the before record and Zara - who is responsible reads the after record.

This is called READ CONSISTENCY.

These buffers gets full eventually - also, holding data, uncommitted, before and after records, and such, we need a mechanism to record what happens on our system, in order for us to use that information later, i.e recovery.

3. Redo Log Buffer - Oracle answer to a Tecnorati Tape recorder is called Redo. All DML, DCL and DLL are recorded into the redo log buffer. How about SELECT ? Well it can occur inside the redo log buffer - because of a dirty buffer cleanout - but as a general rule is normally don't.

As mentioned, will all changes made towards the Oracle database be written to the redo log buffer. They way it works is that data from the database buffer cache - since all statements will occur here, are written to the redo log buffer. However when looking for a free buffer, inside the database buffer cache, Oracle looks for x Milli sec. or 1/3 threshold of the buffer. If none are found - it signals for a cleanup - to get a free buffer.

On a default Oracle system - you have two log groups - consisting of 1 member (file) each. When data are written from the Database Buffer Cache to the Redo Log buffer - it also gets full, and starts writing to another log group - in a robin round fashion. Having only 2 groups, on a system - you will soon run out of files - and recovery information, since the redo files contains all thats happened to your system, get overwritten.

Therefore many turn on Archiving on their system. That means when a redo file i full, its archived to a destination of your choice (disk, tape etc). So basically, all that it takes to restore your system to point in time will be - a full backup and your log files. Off course there is a time issue, and many do a combination of full on line backups, incremental backups and their log files. There is also an issue on number of members in one group - the advantage by having more the one member - is that if one is lost - you can create a new one - on a valid disk - if it where a crash. The number of groups besides the minimum requirements - well there are many formulas out there to calculate that - I personally often start with 3-4, and monitor and learn the activity level on a system, and if required - i will change my strategy. 

2. Database files, Control files, and the Parameter file  

An Oracle system, contains of files on a system level. When the system is started, the initial values/settings are read from one file called the Oracle parameter file. The parameter file contains settings like the size of the shared pool, number of database buffers, control files, rollback segments and a lot more.

The central part of the system, is the database files. they contain system data, logical units, programs (packages, procedures, java, HTML), user data and more. An Oracle database file is not an text file - but is a complex file containing logical information to the Oracle system and its structures. 

The largest logical unit within an Oracle Database is called a TABLESPACE.

There are 3 types of tablespaces; Permanent, UNDO and TEMP. A feature in 10G are groups

alter tablespace ts_user tablespace group ts_grp_user;

With 10G there is a new tablespace called SYSAUX It is used to store database components that were stored in the system tablespace in prior releases of the database. Also, the tablespaces that were needed for RMAN's recovery catalog, for Ultra Search, for Data Mining, for XDP and for OLAP are going to sysaux with 10g.
Additionally, it is the place where automatic workload repository stores its information.

Data dictionary and system informations, object management, users, sessions etc. are stored in a tablespace called SYSTEM - and I properly don't have to say this, but its existence is a requirement. So is the tablespaces TEMP and UNDO. However from there on its up to you to create your tablespaces. To get the workload off SYSTEM and to be able to perform on line backups - architects, usually, creates users to the system, as well as they define which tablespace should be default for writing, and which should be default for sorting etc, as well as they set QUOTAS on each tablespace.

Another feature with 10G are BIGFILE tablespaces. It contains only ONE datafile is only supported for locally managed tablepspaces.

create bigfile tablespace b_ts data file '/o1/data/b_ts.dbf' size 2T

There are two types of tablespaces in an oracle system

Dictionary managed tablespaces

Locally managed tablespaces

As per 10g, a database cannot have more than 65536 tablespaces.

Within a tablespace you will find SEGMENTS. A segment is a container for Objects (such as tables, indexes....). A segment consists of extents.

Extents consists of one or more contiguous db blocks. Space for data on a hard disk is allocated in extents. An extent belongs to a tablespace.

3. Processes - Communication between memory and files

Processes or background processes plays an important role on an Oracle system. Some takes care of users, their processes, locks, temporary data others takes care of the system, lets have a look;

If we start with the central/default types of processes;

PMON - Process Monitor - takes care of;

 - This process is in charge to perform process recovery when a user process fails (also when killed). It then cleans up the cache and frees resources that the process used.  It also does ROLLBACK on uncommitted transactions, release locks on terminated process. It also checks server and dispatcher processes and restart those if they have failed.

SMON - System Monitor - Takes care of;

- Automatic instance recovery,- Reclaim of space used by temporary segments, who not longer use that space It also merges free space in data files. Also when starting an Oracle System, the SMON checks the SCN (system commit number) in the header of the data files, and match that with what it finds in the control files. If the SCN does not match - the database is in an inconsistent state.

RECO, LCKn, Pnnn, and SNPn processes;

- The Distributed Transaction Recovery Process finds pending (distributed) transaction and resolves them.  The LCKn process performs inner-instance locking in parallel server environments. The parallel Query (Pnnn) takes care of parallel query, parallel index creation, parallel data load and parallel CREATE TABLE AS SELECT functionality. SNPn - Snapshot process takes care of automatic refresh of snapshots (read-only replicated tables) - and is also responsible for server job ques and replication ques.

User- and Server processes;

Besides the processes above we also have the user and server processes. Made simple; this is what makes your communication with the Oracle database possible. A user process communicated with a server process for as long as you are connected.

Lets have a look and go back to Zara again. She is using TOAD on her local PC - and communicating with an Oracle system somewhere.

That somewhere is described in the file TNSNAMES.ORA - TNS stands for Transparent Network Substrate - and with out going into all the networks layers - its transparent to the user, which machine, protocol and port they connect to, when connecting to the database. However the description located in the file mentioned above might look like this;

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MOT-2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

As you see - the description entry XE, consists of details, telling any program that - it should connect to the machine MOT-2, Port 1521 and ask for XE.

That will only work if there in fact exists someone or something to pick that call up on port 1521. That something is called a LISTENER. Zaras LISTENER.ORA file looks like this;

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = MOT-2)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

We are almost ready - if you have multiple Oracle homes, it might be a good idea to set the environment variable TNS_ADMIN - so we are sure to use the right TNSNAMES.ORA file, when connecting.

Lets try to connect.

From here on - I am connected with my user process. It connected to the listener, who sends my application the address for a server process - and now I can communicated with the Oracle Database;

Oracle Instance and Oracle Database

You differentiate between an Oracle instance, and an Oracle Database. An Oracle instance consists of;

And an Oracle database consists of;

Control file(s)

The parameter file

The parameter file or Oracle settings file is the last file to mention. It consists of different parameters, like you location of control files, your rollback segments, number of database buffers, size of the shared pool etc. Typically its called INIT.ORA or INIT_SID.ORA, where SID is the Oracle Databases system identifier.

The last I want to mention is the ALERT and TRACE files, also specified in your parameter file; background_dump_destination and user_dump_destination. Please pay attention to those dump destination as they contain vital information for support organizations if your Oracle system - reports errors.;