Stumbled across a great four part video series on You Tube with a lecture on Oracle database architecture, and specifically a discussion on the Oracle SGA. For anyone interested in understanding the internals and the pure definition of an Oracle database, this lecture series is worth the time to watch. If you follow his work on the board, you can create your own cool diagram of Oracle database architecture.
Oracle SGA Architecture Lecture
High Level Diagram/Definition of an Oracle Server
It is useful to know the architectural details of the Oracle Relational Database Management System (RDBMS) such as its concurrency model, how it manages in its memory and disk, etc.
One common point of confusion with Oracle databases is the distinction between databases and instances.
A database is a collection of data files found on disk or some sort of storage device.
An instance is a set of Oracle processes (also known as background processes) are what operate on the database files, such as storing and fetching data from the database.
A collection of objects under one database user (owner). A database user can own one schema, and that same user can still have access to different, multiple schemas.
Two important schemas belong to the SYS and SYSTEM users. SYS schemas contain the data dictionary for the database. The data dictionary is a collection of read-only (query only) database tables and views containing metadata related to the objects in the database. Metadata includes definitions for all of a schema’s objects.
Incicdentally, the SYS user has the highest privileges in an Oracle database and is the equivallent to an “Administrator” or “root” account on the operating system.
A schema objects that requires physical storage must belong to tablespace, which is a logical group of functionally related schema objects.
A data block is the smallest unit of data used by ORacle during its I/O operations. Each operating system has a block size. However, Oracle works exclusively in units of Oracle data blocks… not operating system blocks.
Note that one data block may contain more than one row of a table.
Most discussions about performance measurement in Oracle almost always includes the concept of data blocks.
Come to understand two concepts of Oracle: its locking mechanism and its multiversion read consistency model. These two characteristics set it apart from other RDBMS’s. Facts about Oracle’s locking behavior:
Oracle uses row-level locking. It makes sure only one transaction can write or modify a piece of data at any given time. Read operations (query or select) do not initiate locking behavior.
Overhead for locking is really low. The lock status is stored within the data block of the row, and not through some lock row status table.
Even if you modify every row of a very large table, Oracle will put a row lock on each row.
Oracle also keeps track of how to change back any blocks of data that are changed. This is stored in an undo (or rollback) segment. This undo segment is marked with a internal timestamp or SCN (system change number) so that if for example, you are querying data while it is being updated, Oracle will look through the undo segments to know what the data looked like at the time your query initiated. This is called multiversion read consistency.
