Some tricks to ensure that Oracle architecture
designs perform at optimal levels while making a design that is easy
to maintain and extend are offered.
As an Oracle performance-tuning expert, I frequently visit Oracle
shops to improve system performance. Although Oracle offers many
performance-tuning techniques, you can't tune away a poor database
design—especially a poor architectural design. So it is imperative
that the Oracle database designer understand (from the initial
inception of the project) how to create robust Oracle data
architectures that can retrieve information as rapidly as possible
while preserving maintainability and extensibility.
If you strip away all the complex methodology and jargon
surrounding the Oracle database, one simple factor remains: disk
I/O. Disk I/O is the single most expensive Oracle database
operation. Oracle design professionals should always remember to
design with data architectures to retrieve the desired information
with a minimal amount of disk access.
This article shares some of the tricks I use to ensure Oracle
architecture designs perform at optimal levels while making a design
that is easy to maintain and extend.
Architectural design tips
Oracle provides numerous tools that let you minimize disk I/O for
SQL queries. Here are a few architectural design issues that will
greatly improve the performance of your Oracle system.
Use multiple block sizes
You can deliberately map different tables and index structures to
tablespaces of different sizes. This allocation is based on the
average row length of the data within the table, as well as access
patterns within the whole database. Oracle9i lets you choose between
2K, 4K, 16K, and even 32K tablespaces. The trick involves allocating
the tables and indexes to retrieve all related row information in a
single disk I/O operation.
Precompute complex SQL queries
Oracle offers materialized views and VARRAY tables, which can
prebuild complex queries and aggregations into single-row tables
that can be retrieved instantly. This illusion of instantaneous
aggregation is the hallmark of a superior Oracle design.
Use RAM data caching
You must be aware that Oracle9i allows very large memory regions in
order to cache frequently referenced row information. The caching of
frequently referenced information should be a major design goal
primarily because RAM access is two orders of magnitude (more than
10,000 times) faster than row access from disk. The larger the
Oracle data block buffer cache, the faster the SQL queries will
execute. The size of the RAM data buffers will have a direct impact
on Oracle performance, and all systems run fastest when fully cached
in the data buffers.
Buy fast processors
The CPU speed of the Oracle database server has a direct impact on
performance. High-performance 64-bit CPUs will often perform 10
times faster than 32-bit processors. The 64-bit processors are
available on all major platforms and include:
- Windows—Intel Itanium processor
- HP—PA-8000 processor
- Solaris—500-MHz Ultrasparc-iie processor
- IBM AIX—RS/6000 PowerPC processor
Use a 64-bit version of Oracle
It is highly recommended that Oracle systems exist on a dedicated
database server with a 64-bit CPU architecture and a 64-bit version
of Oracle. The 64-bit version of Oracle lets you create large SGA
regions, and large projects commonly require more than 20 gigabytes
of RAM data buffers. A serious shortcoming of 32-bit Oracle is the
1.7-gigabyte size limitation for the SGA.
Use large data blocks on indexes to minimise disk I/O
Oracle index access performs best with 16K and 32K Oracle block
sizes. You should consult your application-specific OS documentation
and create index tablespaces of the largest value for your
Use Oracle Parallel Query
All table access should be tuned to avoid large-table full-table
scans, but often, such scans are required. Make certain all
full-table scans fully utilize Oracle Parallel Query to improve
Choose proper SQL optimisation
The choice of optimiser mode is critical to Oracle SQL performance.
In Oracle9i, approximately half of all queries run faster with
rule-based optimisation; the other half run fastest with cost-based
Always design for package pinning
All frequently referenced PL/SQL packages should be pinned into the
shared pool by using the dbms_shared_pool.keep procedure. Doing so
will greatly speed Oracle PL/SQL execution.
Design all data access inside stored procedures
One of the single most important design issues is to place all
database access code inside PL/SQL stored procedures (more about
this topic in the next section).
Procedural design tips
One of your most important Oracle design goals should be the
encapsulation of all Oracle process code into stored procedures.
Doing so offers many compelling benefits, mainly related to
performance and maintainability. Your primary focus will include the
Coupling data with behavior
Many database administrators use Oracle8 member methods to tightly
couple stored procedures with database objects. Others employ naming
conventions. For example, if all behaviors associated with the
customer table are prefixed with the table name (customer.hire,
customer.give_raise), then you can query the data dictionary to list
all behaviors associated with a table (select * from dba_objects
where owner = 'CUSTOMER'), and you can easily identify and reuse
Isolation of code
Because all SQL is moved out of external programs and into stored
procedures, application programs become nothing more than calls to
stored procedures. As such, it's simple to swap out one database and
swap in another.
Faster SGA access
One of the foremost reasons stored procedures and triggers function
faster than traditional code is related to the Oracle SGA. After a
procedure has been loaded into the shared pool of the SGA, it
remains until it is paged out of memory to make room for other
stored procedures. Items are paged out based on a least recently
used (LRU) algorithm. Once loaded into the RAM memory of the shared
pool, procedures will execute quickly—the trick is to prevent pool
thrashing, as many procedures compete for a limited amount of
shared-pool memory. Stored procedures load once into the shared pool
and remain there unless they become paged out. Subsequent executions
of the stored procedure are far faster than executions of external
One of the trademarks of a superior Oracle designer is the ability
to create an overall architecture that is robust, maintainable, and
efficient. Today's Oracle design professionals are required to
design systems that may support thousands of transactions per second
while at the same time delivering subsecond response time, easy
maintenance, and extensibility. With a thorough understanding of
Oracle9i database features, and the help of the tips presented in
this article, you can build an appropriate data model architecture
that supports the requirements of end users.