The Basics of Oracle Tuning

Different types of systems have differing requirements. Online transaction processing (OLTP) applications need rapid response in terms of large numbers of small concurrent transactions. Concurrently available data causes contention between resources. The longer something waits for something else the longer the wait time. Read-only data warehouse applications require high volume throughput. Batch processing requires small numbers of large transactions. Resources requiring tuning to avoid contention include CPUs, memory, I/O and network speed (bandwidth). Note that systems can be tuned differently at different times of the day to allow for varying peak usage periods. NEVER allow performance requirements to override that of the ability to recover from failure of any sort.

It is much better to tune during development rather than to tune after development in production. Tuning in development will lengthen the life-cycle of any system. The diagram below shows the most productive of tuning methods in decreasing order of usefulness.

Application Types

OLTP (Online Transaction Processing)

The items below will affect OLTP performance tuning most significantly.

Data Warehouses

Data warehouses are generally intended as read-only type environments where updates can occur periodically. Thus more complex structures can be utilised where alteration of data is unimportant in relation to the end-user.

Combination of OLTP and Data Warehousing

Multi-purpose applications can consist of databases which require both read and write at the same time or read in certain periods and read/write in other periods. These applications can simply be stored in a single database. Also these applications can be provide for both requirements using replication or distributed systems (spreading of multiple databases on multiple machines) or parallel server configurations (multiple instances sharing a common set of datafiles). Even archive log driven standby or clone databases can be used to accomplish this same mixture however, this would involve more physical DBA involvement. Requirements for batch processing and backups can also affect how databases are created and managed. In general, one should always attempt to understand the orginal intention of a specific type of technology. The reason a technology or methodology was originally created for is probably its most efficient and effective use.

Different Oracle Configurations

Replication was intended for the distribution of data to multiple remotely located sites thus allowing quick access to data mostly used at each remote site. Replication simply copies data between separate databases. Replication is not intended for maaintenance of multiple databases in a single location where those two databases have different functions. It is thus probably risky to utilise replication for backup provision. Corruption can always be replicated and there is heavy overhead in use of automated data replication.

Distribution of systems originally stemmed from distributed processing technologies. This implies the spreading of processing amongst a number of machines over a network. This is effectively what Oracle Distributed Systems is all about. With this in mind Oracle distribution of databases on multiple machines is effectively a form of clustering, something which would allow use of less powerful machines but could possibly remind one of SQL-Server. Unix will overcome the need for clustering when it comes to scaleability.

Oracle Parallel Server architecture allows the sharing of a single set of datafiles amongst multiple instances. An Oracle instance effectively defines the way database access behaves. Thus one instance could provide data warehousing capability and another could provide for OLTP data access. I/O contention could be a problem in this case. Partitioning of data is a very neccessary requirement in this case, specifically partitioning between read-only and read/write data sets.

How and Where to use Different Oracle Configurations

Always try to use something for that which it was originally built and intended.

It is always important to attempt to distribute processing between the various levels of multi-tiered systems architectures. Note that tuning is not all about the database. Also do not overuse or under-utilise the database. A database is not simply a repository for data. A database is also a very powerful and internally, highly adapted, developed and efficient data processing engine.

Replication, Distributed Systems, and Oracle Parallel Server configurations are absolutely no substitute for backup and recovery capability. Even standby or clone databases should never be substitute for physical operating system backups and export/import meta-data and raw data backups.

Tuning Performance Views