12.1 Oracle Database Architecture and the Physical Layer

---

The architecture of an Oracle installation could be called the physical layer of Oracle Database. Architecturally Oracle Database is made up of an Oracle Instance and an Oracle database. The Instance contains the running Oracle binary software and the database contains the data for the Oracle Instance to access. As already stated the database itself is made of files resident in the file system of the operating system.

12.1.1 The Oracle Instance

The Oracle Instance is a part of Oracle Database sitting resident in memory when an Oracle database is active and available for use. That instance or instantiation of Oracle Database is made up of processes or programs and reserved memory buffer cache areas.

Figure 12.1: Oracle Instance Memory Cache Buffers

Buffers

Most of the aspects of tuning memory buffer cache areas are discussed in Chapter 15. The different buffers are shown in Figure 12.1.

Processes

There is nothing that can be done to speed up a process itself by changing anything in Oracle Database. However, Oracle processes can be examined at the operating system level to perhaps help determine the source of other problems. For instance, enormous amounts of I/O or CPU time could show various potential bottlenecks. In other words a process itself is not tunable, only factors the process is affecting or being affected by. Building on Figure 12.1 the diagram in Figure 12.2 shows some of the Oracle processes plus the previous picture of the Oracle Instance.

Figure 12.2: Oracle Instance Process and Memory Layers

There are a multitude of utilities in operating systems such as Unix and Linux which can be used to detect problems with processes. Some of those utilities available on a Solaris flavor Unix box are as follows:

• top.   This utility shows a real-time picture of processes, CPU, and memory usage.

  last pid: 15713; load averages: 0.01, 0.01, 0.01  58 processes: 57 sleeping, 1 on cpu CPU states: 99.7% idle,0.0% user,0.3% kernel,0.0%   iowait,0.0% swap Memory: 1024M real, 34M free, 598M swap in use, 2263M swap   free     PID    USERNAME  THR  PRI  NICE   SIZE    RES   STATE  TIME   CPU    COMMAND 15713    oracle    1   58     0  2136K  1664K    cpu0  0:00  0.08%       top 1732      build   26   58     0   195M    60M   sleep  5:45  0.05%      java 14478     user1   25   58     0   182M    40M   sleep  1:08  0.01%      java 15711      root    1   58     0  2152K  1728K   sleep  0:00  0.00%  sendmail 127        root    1   58     0  3208K  2672K   sleep  0:38  0.00%  in.named 235        root    1   58     0  1752K  1056K   sleep  0:20  0.00%     sshd1 9259     oracle    1   58     0  9144K  4544K   sleep  0:18  0.00%   tnslsnr 14829     user2   22   58     0    50M    25M   sleep  0:03  0.00%      java 14950     user2   21    0     0    46M    19M   sleep  0:01  0.00%      java 1609      build   20    0     0   167M    18M   sleep  0:01  0.00%      java 179        root    8   52     0  2376K  1944K   sleep  0:01  0.00%      nscd 143      daemon    4   44     0  2184K  1568K   sleep  0:00  0.00%     statd 138        root    1   48     0  2008K  1584K   sleep  0:00  0.00%     inetd 231        root    1   48     0  2096K  1552K   sleep  0:00  0.00%  sendmail

• sar.   The System Activity Reporter examines CPU, swapping, paging, memory, and buffer usage for specified time periods.

• sadc.   Server activity statistics collection examining CPU, buffers, I/O, switching, system calls, file access, queues, IPCs, amongst other things.

• vmstat.   Virtual memory statistics.

• iostat.   I/O statistics.

There are numerous other Unix and Linux utilities and tools for doing all sorts of things to inspect hardware resource usage at the operating system level. Most of these utilities can provide clues as to performance problems within both an Oracle Instance and an Oracle database. What can be done on a Windows server to examine processing in the way that Unix utilities can? Windows Performance Manager can be used.

Within the Oracle database itself operating system-level statistics can be found in the V$SYSSTAT and V$SESSSTAT views. The V$SYSTEM_EVENT and numerous other related views can be used to track bottlenecks through analysis of wait events: the Oracle Database Wait Event Interface. This subject is covered in detail in later chapters.

12.1.2 The Oracle Database or File System Layer

An Oracle database is effectively the file system layer in the operating system plus the Oracle Instance which comprises processes and memory buffers. An active and accessible Oracle database is an Oracle database using the Oracle Instance to access Oracle database files as shown in Figure 12.3.

Figure 12.3: The Oracle Database and the Oracle Instance

Figure 12.3 also shows different types of Oracle database files. These files are as follows:

• Datafiles.   Contain all physical data such as tables, indexes, database metadata, procedural code and anything stored in the database as accessible or usable by an Oracle database user.

• Redo Logs.   Transaction records of all database changes.

• Archive Logs.   Historical copies of cycled redo logs maintaining a complete history of all database change activity.

• Control File.   Contains pointers to all datafiles and log files used for synchronization between all of those files.

• Parameter File.   The configuration parameter file is discussed extensively in Chapter 11 and throughout this book.

How Oracle Database Files Fit Together

As already discussed there are a number of different types of files at the file system level that a running Oracle Instance uses to control and gain access to data in the database. Datafiles contain all table and index data. Redo log files contain a record of all transactions or metadata database changes to the database. Redo logs are cyclic such that they are reused. When a redo log file is full it can be copied to an archive log file. An archive log file is a historical redo log file. The copied redo log file is once again ready for reuse to contain new database activity records.

The most important file in the file system structure of Oracle database files is a file called the control file. The control file contains pointers to all datafiles, redo logs, and the archived redo logs. The control file stores the current time state of a particular datafile by matching up what are called system change numbers (SCN) between datafiles and log files. An SCN is a sequential number used to synchronize changes between datafiles and redo logs. If SCN values are higher in redo logs than datafiles then the control file knows that datafiles are older versions than they should be: either the datafiles are recovered backups or the redo log entries, always written before datafiles, are updated and the datafiles have not as yet been synchronized with the latest changes.

Special Types of Datafiles

Three special types of datafiles are the SYSTEM datafile, rollback datafiles, and temporary datafiles. The SYSTEM datafile stores all the metadata or the data about the data.

Note 

 Oracle Database 10 Grid   The SYSAUX tablespace is created automatically along with the SYSTEM tablespace when a database is created. The SYSAUX tablespace contains options, Oracle tools, repositories, and even some types of metadata previously stored in the SYSTEM tablespace.

Rollback datafiles allow for undoing of certain types of previously processed but uncommitted transactions. Rollbacks also cater for the multi-user ability of sessions to "snapshot" data at a specific point in time regardless of changes made by other sessions.

Tip 

Automatic undo, a more sophisticated type of rollback released in Oracle9i Database, allows retention of uncommitted as well as already committed data, permitting queries which can "flashback" providing a snapshot of data at a previous point in time.

Temporary datafiles allow for large sorts to be sorted using disk space when memory resident sort space is used up. Figure 12.4 shows a general map of Oracle database file system structure plus pointer links and flow of data between them.

Figure 12.4: Relationships Between Oracle Database Files

Tuning Datafiles

There is little which can be done to tune datafiles. Most Oracle Database physical tuning with datafiles is performed from the table-spaces overlaying those datafiles. A datafile can have its extent growth parameter changed using the ALTER DATABASE command and Oracle Partitioning can be implemented.

Tip 

Any changes to datafiles such as an extent size change using the ALTER DATABASE command will only affect new extents not existing extent sizes. The same applies to any block changes. Block level tuning will be covered in Chapter 14.

Datafiles can be tuned much more effectively at the file system level in the operating system. Factors such as striping and RAID arrays can help to tune datafile I/O access rates enormously.

Oracle Partitioning allows the separation of large sets of data into separate datafiles, perhaps placing different partition datafiles on to different disks.

Manual striping is a primitive implementation of the functionality of RAID arrays. Striping includes various methods of splitting datafiles into parts and spreading them across separate storage areas, or even within the same storage area, for rapid random concurrent access.

Tip 

Separation of table and index tablespaces onto separate disks or even the same disk is a form of striping since the two datafiles underlying the tablespaces are likely to be accessed concurrently due to the nature of their content.

Modern RAID arrays have all sorts of bells and whistles allowing striping, mirroring, Oracle Partitioning, and other aids to faster I/O such as dissimilation between sequential and random access. Indexes and log files should be accessed and written sequentially. Tables should be accessed and written randomly since index scans help to pinpoint table access. This is especially true of OLTP databases.

As already mentioned Oracle Partitioning allows the splitting of datafile content into separate datafiles. What this means is that a very large table and its associated indexes could be separated into separate datafiles. As regards reading data from the datafile on a heavily striped RAID array partitioning of datafiles might be irrelevant. Oracle Partitioning certainly is irrelevant as far as striping of datafiles onto an already striped RAID array is concerned. However, partitioning of datafiles still has two uses, which can have a distinct effect on I/O and processing of datafiles, even on a striped RAID array. Firstly, separate partitions using separate datafiles can be unplugged and plugged into a set of datafiles in a partition set with the speed of a rename command. What this means is that a datafile partition within a large table can be added or removed without accessing any other datafiles in the partition. This leads us to the second datafile I/O benefit of using Oracle Partitioning: SQL code can be executed within the range of a partition datafile with physical access to only the datafile required to satisfy the SQL code. So SQL code reading partitioned datafiles can read single partitions in a set of partitions, reducing I/O rates drastically.

Control Files

The only thing to mention with respect to tuning of control files is that creation of multiple control files is a very sensible practice. Multiple control files on separate disks assures that a copy of the control file can be recovered should one be lost or become corrupted. The performance effect of multiple control files is negligible. However, utilizing modern RAID arrays the chances of corruption are very remote.

Tuning Redo Logs and Archive Logs

Tuning of redo logs and archive logs is important to Oracle Database efficiency. The size of redo logs must be specified when created by a database administrator. Both the number and size of redo logs are important.

The more redo logs created for a database the less likely writing of the redo logs is going to catch up with the archiving process. Let's say for example your database has three small redo logs. Heavy transactional activity will rapidly switch the redo logs but the archive process which copies redo logs to the archived log files may not be fast enough to keep up. What happens then is that all database transactional change activity halts until a redo log can be archived and recycled. A redo log will not be recycled if it has not as yet been archived. A halt will occur and effectively stop all database activity until all necessary archiving has completed and a redo log is cleared for reuse.

The same problem can occur if redo logs are too small. With high DML activity small redo logs can result in excessive switching. A redo log file is switched from CURRENT to ACTIVE or INACTIVE when it has filled. If redo log files are too small and a lot of switching is occurring with high transactional activity, once again the archive process may not be able to keep up, potentially temporarily halting the database.

A similar problem can occur with redo log and archiving performance when redo log files are too large. The archive process copies redo log files to archived log files. Sometimes, especially on Windows systems, very large redo log files can take so long to copy that transactional activity will once again catch up with the archive process and once again produce a temporary availability halt. I have seen this large-file copying problem occurring on a large Unix-based system, but only once. The redo log files were sized at 500 Mb. Yes they were half a gigabyte. The system was highly active and concurrent. It had a mixture of OLTP and data warehouse activity (Yoiks!) and it had enormous amounts of CPU time and space wastage as a result of terrible SQL code and bitmap indexes on transactional tables. This particular system had all sorts of problems, which I managed to resolve partially before I decided that the company was about to run out of money. I gladly accepted a better offer elsewhere. This particular company also had a history of database administrators dropping entire production databases by mistake, amongst other scary things. There was not really much that could be done in this situation. The fact is this. Someone had resized the redo logs up to a ridiculous amount in a vague trial and error approach of attempting to solve other problems. The result of this kind of experimentation is often further problems. Be warned! Be careful and do not change things without understanding potential effects first. Additionally it is also wise to record the state of a database before making changes. Simply writing down changes and making frequent backups can often help one to recover from critical situations rapidly without too much downtime.

Let's get back to logging. So what is the best recipe for efficient log writing and archiving of redo logs to prevent a database from halting, or perhaps worse? There is no hard and fast solution. Every database is different. Additionally the size of the redo log buffer can have a profound effect on redo log file writes and archiving performance. There is one important thing to remember. The redo log buffer should not be of the same size as the redo log files and should generally be a very small percentage of redo log file size. Redo log buffer tuning is discussed in Chapters 11 and 15. A number of things are important to remember. The redo log buffer is flushed or written to the current redo log file whenever a COMMIT or ROLLBACK is issued; the redo log buffer is partially filled to one-third full, every 3 s or if the redo log reaches 1 Mb.

Tip 

A checkpoint is a process of flushing dirty buffers from database buffer cache to disk storage. The redo log buffer is not flushed to disk when a checkpoint occurs. This is the consensus of opinion from a number of database administrators and contrary to many Oracle software texts.

So how does the size of log buffer affect the redo log files? The most common and most successful sizes for the redo log buffer range from 32K to 512K and occasionally as much as 1M. I have seen many databases with problems when the redo log buffer is larger than 512K. Multiple archive processes can help to speed up archiving and alleviate pressure from elsewhere on highly active databases.

So what do we do with redo log files? How large should they be and how many should be created? Should redo logs be duplexed? What is duplexing? Duplexing is creation of multiple redo logs, which are written from the log buffer to multiple redo log files at the same time in parallel. Duplexed redo logs are used for the sake of recoverability. Redo log duplexing is not really necessary with the more sophisticated types of RAID arrays but is extra insurance. Generally flushing of the redo log buffer to duplexed redo logs on servers with more than a single CPU has a negligible impact on performance.

How large should redo log files be and how many should be created? The more transactional activity, the more redo logs you should have. If redo logs are continually switching then make the redo log files larger. Do not make redo log files so large that you get a performance bottleneck when redo logs are switched and copied to archive logs. If redo logs are getting too large then add more redo logs and perhaps try to tune other factors such as the log buffer. In short, turning off logging is a juggling process between multiple factors.

Another potential problem with very large redo log files is when using standby databases and passing log files over SQL*Net on a slow connection between two machines. The answer in this case would be to do something about your network connection. Changing the SQL*Net packet size can help your standby database log file transfers but may slow network performance for small transactions. If you are operating a fast-response OLTP database through to your customers, changing packet size is not a solution.

This leads us to the networking layer.

12.1.3 The Networking Layer

Another part of the architecture of Oracle software is the networking layer. Oracle Database has a proprietary networking software layer. This layer of software has multiple names in different versions of Oracle Database. They all do and are the same thing. SQL*Net sits on top of a protocol such as TCP/IP and allows for easy formatting and access for connections between client machines and an Oracle database server.

Network tuning will be covered in Chapter 16. There is much more to Oracle networking software than just SQL*Net. There is also much that can be done to tune Oracle networking software.

---