Comparison between OLTP system and DSS System

When designing a database system, it is important to find out which type of application you are facing. Currently, the following popular application types are available:

* Online transaction processing (OLTP = online transaction processing) Application

* Decision Support System (DSS = demo-suport System) Applications

* Multi-purpose applications

　　Online Transaction Processing (OLTP)

Online Transaction Processing (OLTP = online transaction processing) applications have high throughput and are update and insert intensive. Such as the ticket booking system and ordering system. During OLTP design, concurrent users and system performance issues should be considered, because indexes and clusters will reduce the insert and update speeds. Therefore, you must avoid excessive use of these two structures.

For OLTP systems, the following factors are critical:

* Rollback segment

* Index, cluster, and hash

* Discrete transactions

* Data Block Size

* Buffer cache size

* Dynamic table and rollback segment space allocation

* Transaction processing monitoring and multi-threaded servers

* Variable usage

* Shared Pool

* Partition

* Optimized SQL statements

* Integrity constraints

* Customer/Server Architecture

* Initialization parameters that can be dynamically changed

* Processes, components, and functions

　　Decision Support System (DSS)

In typical cases, a decision-making system converts a large amount of information into user-defined reports. DSS queries a large amount of data collected from OLTP applications.

The key objectives of the decision support system are as follows: 1) response time; 2) accuracy; 3) availability. When designing a DSS System, make sure that the query of a large amount of data is completed within a reasonable period of time. For example, during the day, the decision maker needs to read the report, so make sure that the required data statistics are completed the night before.

The following factors are crucial to DSS optimization:

* Materialized View

* Index (B * tree and bitmap)

* Cluster and hash

* Data Block Size

* Star Query

* Optimization program

* Query prompt

* PL/SQL functions in SQL statements

* Partition

　　Multi-purpose applications

A typical combination of OLTP and data warehouse systems is a multi-purpose application. The OLTP application collects data and then delivers the data to the data warehouse system.

If the OLTP and data warehouse systems use the same database, there will be a target conflict, which can affect performance. To solve this problem. First, the OLTP database copies the collected data into another database. This database is queried by the data warehouse. If only one copy is made every day, this configuration may slightly compromise the accuracy of the data warehouse application. However, the two systems can achieve better performance.

Source: http://www.cnblogs.com/yan5lang/archive/2010/05/19/1738992.html