Oracle architectural design tips
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.