Techniques for increasing Oracle scalability

Oracle Tips by Burleson Consulting

Updated: August 18, 1014

Update: Best practices for scaling Oracle VLDB systems

Creating a scalable Oracle architecture that can manage transaction rates over a thousand per second is a challenge for even the seasoned Oracle architect. Of course, adding hardware can be expensive, so you will probably have to start small and increase processing power as transaction demands require. Here are some tips on increasing Oracle scalability for highly available systems.

Install the Oracle multithreaded server

By using a multithreaded server, the Oracle database can utilize internal memory within the Oracle region called a large pool. The large pool is a RAM area inside the Oracle System Global Area (SGA) that manages connections to the Oracle Multi Threaded Server (MTS) and also holds the Java Virtual Machine (JVM) memory. The multithreaded server also allows for many thousands of end users to connect through response dispatchers. Each dispatcher within the Oracle database can spawn many subtasks to handle high volumes of incoming connections.

Use very large RAM data buffers

Most Oracle databases utilize very large buffer pool storage and cache much of the database information. The goal is to minimize disk input and output (I/O) by having as much of the salient information stored in RAM as possible. With Oracle's new scalability features, it's not uncommon to see data buffers grow anywhere from 10 GB up to 50 GB or more, effectively caching the most important and most frequently referenced information from the Web server. Using this approach, the Oracle database does not need to do any unnecessary I/O, and incoming Internet requests for information simply perform in memory transfers from the Oracle data buffers, then go to the Web servers, and finally back across the Internet to the originating site.

Pre-summarize aggregate information

Any Oracle e-commerce systems that are required to summarize your aggregate information use the Oracle facility called materialized views. With materialized views, it is possible to pre-calculate aggregate information and store it in intermediate tables, which are transparent to the Oracle SQL. Whenever an incoming request desires an aggregation calculation, Oracle rewrites the query to the precalculated aggregates instead of recomputing the information. This provides the e-commerce system with the illusion of extremely fast aggregation capabilities.

Store complex objects within the Oracle database

Many high-volume Oracle database-driven e-commerce systems choose to store preformatted HTML text in one of Oracle's numerous data types that support large objects. These data types include character large objects (CLOBS). Oracle takes these large objects and stores them either offline within the Oracle data files or offline using file linkages. By using this technique, the Web server is relieved of the tedious chore of having to redefine the entire outgoing HTML upon a request from the end user. The preformatted HTML makes its way from the Oracle data buffers to the Web server cache, where all variables are parameterized. When incoming data requests a specific HTML page, symbolic substitution takes place within the cached version of the HTML page on the Web server and a complete HTML page with the Oracle data embedded into the HTML is shipped back across the Internet.

Achieve scalability through replication

With Oracle9i, Oracle introduced Real Application Clusters (RACs). As I mentioned in an earlier article, RACs provide a highly available architecture and lightening-fast recovery but are very expensive. Many of the companies using Oracle databases adopt one of the following failover methods:

Cross-database link approach - All Oracle databases have encapsulated stored procedure logic that cross-populates all databases whenever a change is made. Within each stored procedure, distributed transactions are set up so that transactions are automatically sent to all of the replicated databases via a two-phase commit transaction. Doing updates as a two-phase commit transaction ensures that all of the databases are either updated or rolled back as a single unit within the database.
Using redo log replication - The redo log replication has become increasingly popular since Oracle replication provided methods for transmitting data directly from the log_buffer. This approach is incorporated into Oracle Streams and Oracle GoldenGate..

Build your own scalable Oracle architecture

Although many large corporations are embracing the Oracle9i tools for scalability, some choose to enhance scalability by creating a customized replicated architecture. By having multiple copies of the Oracle database, the application servers can direct incoming data requests to the replicated systems, doubling the throughput to the data repository.

The techniques vary by company, but many architects use the following approaches:

Place all SQL inside stored procedures- -This technique encapsulates the process logic and stores it inside the database engine.
Create back-and-forth database links for updates- -This approach allows all database updates to propagate between the copies of the database. The stored procedures call both databases as a two-phase commit, ensuring that all replicated databases remain synchronized.
Add replicated databases as the system load increases - By carefully monitoring the Oracle load, you can add new replicated databases, ensuring that the end users enjoy fast response time.

Cross-database link approach to replication

Conclusion

There are far too many variables to cover in this short article, but suffice it to say that the use of replicated Oracle databases with scalable architectures can allow the Oracle architect to manage all growth of a system. By using Oracle advanced tuning features such as the multithreaded server, Oracle9i large objects, and materialized views, you can ensure that your system will provide adequate response time and trustworthy reliability.

��