Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







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


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.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.