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

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

Oracle Architectural Design Tips
April 5, 2002
Donald Burleson

 
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:
 


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 minimize 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 environment.

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 performance.

Choose proper SQL optimization

The choice of optimizer mode is critical to Oracle SQL performance. In Oracle9i, approximately half of all queries run faster with rule-based optimization; the other half run fastest with cost-based optimization.

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 following goals.

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 code.

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 code.

Conclusion

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.

 
If you like Oracle tuning, check-out my latest book "Oracle Tuning: The Definitive Reference". 

Packed with almost 1,000 pages of Oracle performance tuning techniques, it's the foolproof way to find and correct Oracle bottlenecks.


 

 

 

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 -  2014

All rights reserved by Burleson

Oracle © is the registered trademark of Oracle Corporation.