Linux Maximus: Modern Tablespace Paradigms
Bert Scalzo, PhD, Quest Software

In Part 1 of this article, "Gladiator Like Oracle Performance", I offered a simple assortment of tips, tricks, and techniques for maximizing the performance of Oracle on Linux. The idea was to capitalize upon Oracle and Linux low-hanging fruits – relatively simple tuning ideas so obvious as not to require benchmarking in order to be accepted.

In Part 2 of this article, “The RAW Facts on File Systems”, I focused upon basic IO concepts for the Linux world: number and type of disk drives, logical volume managers and file systems. The idea was to establish that certain hardware and software configurations are the only real alternatives for true enterprise scaled implementations.

In this installment, I look at adopting modern tablespace paradigms as yet another simple way to achieve optimal performance. For although Oracle DBA's are good at optimizing their databases using time proven tuning tools and techniques, sometimes a very simple and quite effective answer lies just beneath the surface. Sometimes simply by embracing the newer tablespace management paradigms of more recent Oracle versions, DBA's can obtain 10-20% improvements in data loading throughput, index creations and sustainable transactions per second for little or no cost.

Finally, these exact same issues will apply regardless of the underlying hardware and software infrastructure – including a Real Application Cluster (RAC) built upon the Oracle Cluster File System (OCFS). See the Oracle Cluster File System for Linux sidebar for more information about Oracle’s file system designed from the ground up for Oracle RAC clusters.

Doing Things The Proven Way

Let’s face it; we all do things the way we’re most comfortable with. That’s not us being lazy; it’s just human nature to “not fix what isn’t broken”. So it’s not unusual to find an Oracle 9i database with tablespaces whose design is reminiscent of an earlier version. It could be that the database was originally created in an earlier release and migrated to its current version (e.g. created under 8i and migrated to 9i). Remember that the migration process does not implement or otherwise convert to newer tablespace design paradigms.

Another potential problem is that many DBA’s often have scripts they’ve accumulated over time for doing many tasks, such as new database creation. And while these DBA’s are often highly experienced and their scripts accepted as infallible, sometimes even the best DBA cannot keep every script they have up to date with every little Oracle nuance. Yet these same DBA’s balk at the notion of using GUI tools like the Oracle’s Database Configuration Assistant or TOAD’s new database wizard to create their databases.

And finally it could be that the database creation is part of your application setup, and thus not in your control. For example a large ERP or CRM application might actually perform the database creation for you – in which case you are simply relying on their expert DBA’s and collections of scripts. And while these software vendors strive very hard to produce world-class designs, it’s still not unusual for them to have conservative new database feature adoption policies. And while they have the DBA expertise in house to potentially provide adaptive tablespace designs, they very often assume that the person doing the install is not a high-powered DBA in order to provide a faultless install process.

Thus it’s not all that unusual to look at the tablespaces in an Oracle 9i database and find that there is room for improvement. To begin, we simply need to very clearly understand exactly where we are. Then we can simply decide where we might potentially want to go. It’s that easy.

Lets Call It What It Is

There is a multitude of ways to implement your database’s tablespaces, and numerous options within those choices as well. But among all that and regardless of whether you’re using Oracle Managed Files (OMF) or not, there are nonetheless some very recognizable patterns of tablespace designs that we can identify as a paradigm particular to a specific Oracle version. Thus we can use this approach to identify a database’s current tablespace design paradigm and plan what modifications would be required to modernize it.

Below is a table that quickly defines those version specific tablespace design paradigms. By simply examining a few key tablespaces and whether the database utilizes automatic undo management (i.e. UNDO tablespace vs. manual rollback segments) we can easily identify the tablespace design paradigm.

If we find that all the key tablespaces are dictionary managed and that the database uses rollback segments, we can label this as an Oracle 8.0 tablespace paradigm. Note that the CREATE TEMPORAY TABLESPACE command did not exist yet in Oracle 8.0, so the TEMP tablespace is permanent – even though it may well have both NOLOGGING and TEMPORARY clauses specified. And for those unlucky people stuck in the distant past, I’d label an Oracle 7.X database as generally being an Oracle 8.0 paradigm as well.

With Oracle 8i, two major tablespace paradigm changes occurred. First and foremost, locally managed tablespaces debuted. Now a tablespace could manage its own extents via a bitmap in each data file. This would eliminate the need to access the data dictionary for extent management operations. And second, the CREATE TEMPORAY TABLESPACE command arrived. It simply creates a locally managed tablespace with uniform extents for non-persistent database objects.

With Oracle 9i Release 1 (i.e. 9.0), once again two major tablespace paradigm changes occurred. First, permanent locally managed tablespaces now can have automatic segment space management via the SEGMENT SPACE MANAGEMENT AUTO clause. If using this, then Oracle manages the free space of segments in the tablespace using a bitmap. So storage specification for PCTUSED, FREELIST, and FREELIST GROUPS are no longer necessary. And second, rollback segments can be completely replaced by automatic undo management (i.e. UNDO tablespace). Now you can simply manage undo data much like temporary data, simply assign a tablespace with sufficient space and let Oracle sort it all out (no pun intended).

Finally with Oracle 9i Release 2 (i.e. 9.2), the final missing piece is provided in making the switch to locally managed tablespace complete – you can now create the SYSTEM tablespace locally managed. Of course doing so also means that all your tablespace must be managed locally. Clearly Oracle is making a statement about which way to go. In a few years, I genuinely suspect that you’ll be hard pressed to locate anyone still using dictionary-managed tablespaces.

Note – For those wondering, Oracle 10i will offer two powerful new tablespace paradigm related features:

• You can now define more than one default temporary tablespace, and a single SQL operation can use more than one temporary tablespace for sorting. This feature lets you create indexes on very large tables without being limited by the size of one tablespace, because the sort operation during index creation can be distributed across multiple tablespaces.
   
• Automated Storage Management automates and simplifies the optimal layout of data files, control files, and log files. Database files are automatically distributed across all available disks, and database storage is rebalanced whenever the storage configuration changes. This feature also provides redundancy through the mirroring of database files.

Meaningfully Compare Apples and Oranges

Accepting these four version specific tablespace paradigms as genuine, what are their ramifications? Do they make all that much of a difference? And given their differences, how do I quantitatively and accurately compare apples and oranges?

The approach is actually quite simple. I merely created an Oracle 9.2 database using each of the different tablespace design paradigms. So database #1 was Oracle 9.2 using an 8.0 paradigm. Database #2 was Oracle 9.2 using an 8.1 paradigm. Database #3 was Oracle 9.2 using a 9.0 paradigm. And database #4 was Oracle 9.2 using a 9.2 paradigm. Other than the tablespace paradigms, the databases were otherwise identical. All the database initialization (i.e. INIT.ORA) parameters were the same, and chosen using basic DBA tuning guidelines or recommendations from my other tuning Oracle on Linux papers.

As for reliably and authoritatively comparing the relative performance characteristics of such diverse paradigms, I believe that demonstrating performance improvements using a standard benchmark whose very nature is concurrent transactions lends credence to my conclusions. Thus I chose the granddaddy of well-known and widely accepted database benchmarks, the TPC-C benchmark. TPC-C is an online transaction processing (OLTP) benchmark that involves a mix of five concurrent transactions of various types and either executes completely online or queries for deferred execution. The database comprises nine types of tables, having a wide range of record and population sizes. This benchmark measures the number of transactions per second.

Figure 1

For each database test, I used a 200 MB database (created and populated automatically by Benchmark Factory) and simulated 50 concurrent users. I purposefully chose a relatively small concurrent user load for two reasons. First, the database server hardware tested on was approximately what sells as a small departmental size these days – which are often designed or rated for about 50 concurrent users. And second, I did not want to create the appearance of “stacking the deck” by overloading the system. Few servers run at or near their peak transaction rates, so the benchmarking should approximate typical user loads.

And the Winner Is

The goal was to verify whether the more current database versions’ tablespace paradigms permitted increased sustainable transactions per second. In short, the definitive answer is an overwhelming yes. The table below shows how the actual improvements played out.

Note – All benchmark tests were performed at least four times in order to verify that any relatively small performance gains were not just one-time execution anomalies.

While the results may not seem worth getting overly excited about, keep in mind that you can obtain this extra 10% of database performance improvement at little or no cost by simply modernizing your database’s tablespace paradigm. In essence, this is yet another database low hanging fruit (i.e. an improvement so obvious as to be accepted without extensive testing requirements). You don’t need to perform costly and extensive database monitoring or diagnostics in order to identify and thus reap the benefits. You simply need to modernize your database’s tablespace paradigm. That’s it.

Furthermore, this technique’s performance results scale extremely well. As we increase the concurrent user load, the results get even better (which is good, since we call cannot manage little 50 user databases). In other words, Oracle’s newer tablespace and segment space management features really do deliver improved performance. Using an enterprise sized Linux server (running Redhat Advanced Server), results shown in the table below were obtained. See – as I mentioned at the start of this article, performance improvements from 10-20% are not hard to get. And once again, such results are achievable with a no-brainer and easy to implement change.

Of course the astute reader will probably already have rationalized that these benchmarks and their results apply more generally than just Oracle running on Linux. The exact same tablespace paradigms and comparable results exist for Oracle running on any platform. Of course that makes sense since tablespaces are purely an Oracle technology. But since these tests were performed as part of the tuning Oracle on Linux series, they were thus presented in this context. So as you design your databases’ tablespaces on any platform, remember that adopting a more modern tablespace paradigm can translate into an easy extra 10%-20% performance for little or no cost to implement.

Bert Scalzo (Bert.Scalzo@Quest.com) is a product architect for Quest Software, an Irvine, California based company whose products increase the performance and uptime of business-critical applications while enabling IT professionals to do more with fewer resources. Bert has a B.S., M.S., and Ph.D. in computer science, has worked as an Oracle DBA for over 18 years, and designed much of the DBA module for TOAD. Bert has also just published two books: “Oracle DBA Guide to Data Warehousing and Star Schemas” (Prentice Hall) and “TOAD Handbook” (Sam's Publishing).

Sidebar: Oracle Cluster File System for Linux

This article has focused on a single machine running Linux using the existing file system types supported by an Oracle9i database. With the release of Oracle9i Real Applications Clusters (RAC), several machines can be combined, providing scalability and improved performance, and fail-over support. Oracle RAC requires that each instance of the Oracle database be able to access shared storage, and on many platforms. For UNIX, this typically means providing a set of unformatted disks-raw devices-on a shared disk subsystem; the raw devices enable the disk sharing.

However, if the operating system supports an Oracle-certified cluster file system, the cluster file system can be used instead. A cluster file system is a file system that may be accessed (read from and written to) by all members in a cluster at the same time. Oracle certifies several cluster file systems for many different operating systems.