Oracle Data Warehouse OLTP Oracle Data Warehouse Tips by Burleson Consulting

On-Line Transaction Processing (OLTP) Description and Use

OLTP Stands for On-Line Transaction Processing. In an OLTP system the transaction size is generally small affecting single or few rows at a time. OLTP systems generally have large numbers of users that are generally not skilled in query usage and access the system through an application interface. Generally OLTP systems are designed as normalized where every column in a tuple is related to the unique identifier and only the unique identifier.

OLTP systems use the primary-secondary key relationship to relate entities (tables) to each other. 

OLTP systems are usually created for a specific use such as order processing, ticket tracking, or personnel file systems. Sometimes multiple related functions a re performed in a single unified OLTP structure such as with Oracle Financials.

OLTP Tuning

OLTP tuning is usually based around a few key transactions. Small range queries or single item queries are the norm and tuning is to speed retrieval of single rows. The major tuning methods consist of indexing at the database level and using pre-tuned queries at the application level. Disk sorts are minimized and shared code is maximized. In many cases closely related tables may be merged (denormalized) for performance reasons.

A fully normalized database usually doesn't perform as well as a slightly de-normalized system. Usually if tables are constantly accessed together they are denormalized into a single table. While denormalization may require careful application construction to avoid insert/update/delete anomalies, usually the performance gain is worth the effort.

OLAP Description and Use

An OLAP database, which is an On-line Analytical Processing database, is used to perform data analysis. An OLAP database is based on dimensions a dimension is a single detail record about a data item. For example, a product can have a quantity, a price, a time of sale and a place sold. These four items are the dimensions of the item product in this example. Where the dimensions of an object intersect is a single data item, for example, the sales of all apples in Atlanta Georgia for the month of May, 1999 at a price greater than 59 cents a pound. One problem with OLAP databases is that the cubes formed by the relations between items and their dimensions can be sparse, that is, not all intersections contain data. This can lead to performance problems. There are two versions of OLAP at last count, MOLAP and ROLAP. MOLAP stands for Multidimensional OLAP and ROLAP stands for Relational OLAP.

The problem with MOLAP is that there is a physical limit on the size of data cube which can be easily specified. ROLAP allows the structure to be extended almost to infinity (petabytes in Oracle8i). In addition to the space issues a MOLAP uses mathematical processes to load the data cube, which can be quite time intensive. The time to load a MOLAP varies with the amount of data and number of dimensions. In the situation where a data set can be broken into small pieces a MOLAP database can perform quite well, but the larger and more complex the data set, the poorer the performance. MOLAPs are generally restricted to just a few types of aggregation.

In a ROLAP the same performance limits that apply to a large OLTP come into play.  ROLAP is a good choice for large data sets with complex relations. Data loads in a ROLAP can be done in parallel so they can be done quickly in comparison to a MOLAP which performs the same function.

Some applications, such as Oracle Express use a combination of ROLAP and MOLAP.

The primary purpose of OLAP architecture is to allow analysis of data whether comes from OLTP, DSS or Data warehouse sources.

OLAP Tuning

OLAP tuning involves pre-building the most used aggregations and then tuning for large sorts (combination of disk and memory sorts) as well as spreading data across as many physical drives as possible so you get as many disk heads searching data as is possible. Oracle parallel query technology is key to obtaining the best performance from an OLAP database. Most OLAP queries will be ad-hoc in nature, this makes tuning problematic in that shared code use is minimized and indexing may be difficult to optimize.

Decision Support System (DSS) Description and Use

In a DSS system (Decision Support System) the process of normalization is abandoned. The reason normalization is abandoned in a DSS system is that data is loaded and not updated. The major problem with non-normalized data is maintaining data consistency throughout the data model. An example would be a person's name that is stored in 4 places, you have to update all storage locations or the database soon becomes unusable. DSS systems are LOUM systems (Load Once ? Use Many) any refresh of data is usually global in nature or is done incrementally a full record set at a time.

The benefits of an DSS database is that a single retrieval operation brings back all data about an item. This allows rapid retrieval and reporting of records, as long as the design is identical to what the user wants to see. Usually DSS systems are used for specific reporting or analysis needs such as sales rollup reporting.

The key success factor in a DSS is its ability to provide the data needed by its users, if the data record denormalization isn't right the users won't get the data they desire. A DSS system is never complete, users data requirements are always evolving over time.

DSS Tuning

Generally speaking DSS systems require tuning to allow for full table scans and range scans. The DSS system is not generally used to slice and dice data (that is the OLAP databases strength) but only for bulk rollup such as in a datamart situation. DSS systems are usually refreshed in their entirety or via bulk loads of data that correlate to specific time periods (daily, weekly, monthly, by the quarter, etc.). Indexing will usually be by dates or types of data. Data in a DSS system is generally summarized over a specific period for a specific area of a company such as monthly by division. This partitioning of data by discrete time and geographic locale leads to the ability to make full use of partition by range provided by Oracle8 as a tuning method.

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.