Oracle Concepts - Physical Oracle Architecture Oracle Tips by Burleson Consulting

2007 Update:  Please see these 2007 updates to Oracle hardware architectures and the costs and benefits of server deconsolidation.  

At Oracle Openworld 2007, Oracle officially embraced the 2^nd age of mainframe computing and the rise of the virtual machine, via their commitment to vmware for Oracle.

Andrew Holdsworth (Senior Director of the Oracle Real World Performance Group) gave a presentation titled Current Trends in database tuning, (user cboracle, password=oraclec6), Andrew notes that 99% of OLTP applications have multiple instances per host machine, and that it’s easy to keep an instance from hogging the CPU by using the cpu_count as a processor fence (you can set cpu_count=4 on a 32 CPU machine, and the instance will only use two processors, and that you can use the resource_manager_cpu_allocation to control processing resources.  At the OS level, you can also control resources with the CPU affinity features, and you can adjust the priority of individual processes with the Linux/UNIX “nice” command, which changes the dispatching priority for server tasks.

The Physical Oracle Architecture

All buildings, from the home you live in to the place you work, have an architecture that keeps the building together. This architecture (if it’s well designed), will keep the building from falling apart, keep you nice and warm, and provide you with easy access to facilities like water fountains and restrooms.

A well-crafted architecture will also help you work more efficiently. The same is true for databases.  Despite the name “Oracle”, there is no magic!

The architecture that Oracle has built its database around is designed to perform quickly, efficiently and without errors.

In this section we will introduce you to the Oracle architecture. This architecture includes the following components:

* The System Global Area (SGA) and other memory areas that utilize RAM

* Database related background processes

* Tablespaces and Datafiles

* Database related files

* The instance and the database

Next we will look at each of these components in more detail. Finally we put the components together into a single “big picture” view, and see how all these pieces fit together to complete the Oracle puzzle. Keep in mind that the discussion in this chapter is limited to the overall database architecture. In later chapters we will get into the finer details of managing these structures.

With this in mind, let’s drill down a bit deeper into the architecture of Oracle and learn more about memory structures, the part of Oracle that utilizes your system’s RAM.

Diving into the RAM Pools

All computers have memory. Memory should not be confused with disk storage. Memory is volatile, which means its content is lost after the power is removed. Memory is also very fast. RAM memory is expressed in nanoseconds (billionths of a second) and disk speed is in milliseconds (thousandths of a second).  In Oracle, RAM speed is hundreds of times faster than disks.  Don’t get burned by tiny pools.  See the Google search ”oracle cache disk speed” for details.

Disk storage is non-volatile. This means that the data stored on a disk will remain after the power is turned off. Disks are always slower than RAM, but disks are hundreds of times cheaper than RAM.

There is a trade-off between memory and disks: Memory is fast but expensive (about $1,000 per gigabyte), whereas disks are slower but very cheap. Thus, memory is used for short-term storage of information that is frequently needed and disks are used for long-term storage of information.

Oracle has a number of memory areas that it uses to store information. In this section we will address the main Oracle memory areas. They are called:

* The System Global Area (SGA) – RAM areas for the Oracle programs.

* The Program Global Area (PGA) – Private RAM areas for individual client connections to Oracle

The Instance and the Database

We are almost at the end of our introduction to the Oracle database architecture. We can’t complete this journey, however, until we define two more terms, instance and database. The Oracle instance is the combination of the Oracle SGA and the related background processes (the programs, PMON, SMON, etc.). When the SGA RAM memory is successfully allocated and the Oracle database processes are running normally, the instance is said to be “up”.

However, and instance is sometimes different from a database. You can have the instance running, but the database might not be mounted or open. The Oracle Database includes the physical files we have discussed: the datafiles, the control file, and the redo log files.  When the Oracle instance is running, it can attach itself to a database, “mount” the control file, and finally “open” the datafiles and redo log files. This is an important distinction because many Oracle operations must be done with the instance started but the database is not open.

Oracle Architecture Concepts

In the previous section we discussed the Oracle physical architecture. Things like files, programs and hardware are all considered physical pieces (or physical properties) of the Oracle database. In this section we are concerned with the logical pieces of the Oracle database.

Oracle segregates “physical components” (the .dbf files on disk) my mapping them into “logical” containers called tablespaces.  In turn, we allocate our tables and indexes inside these tablespace, and Oracle takes-care of the interface to the physical disk files.

In this section we will look at the following logical database elements.

* Tablespaces (not completely a logical thing actually!)

* Blocks

* Extents

* Segments

We will then give you a big-picture summary of the relationships between these logical objects. Note that this section just provides you with some basic concepts. We will get into much more detail in the chapters to come!

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Need an Oracle Health Check? Do you have bad performance after an upgrade?   Need to certify that your database follows best practices? BC Oracle performance gurus can quickly certify every aspect of your Oracle database and provide a complete verification that your database is fully optimized.