Oracle Data Warehouse
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
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 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
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
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
Decision Support System (DSS) Description
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.
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
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.