Oracle System Design-determining Application Types

When designing a database system, it is important to find out which type of application you are facing. Currently, popular application types include: * online transaction processing (OLTP = online transaction processing) Applications Program * 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 transaction * data block size * buffer cache size * Dynamic Allocation of table and rollback segment space * transaction processing monitoring and multithreading server * variable use * shared pool * partition * optimized SQL statement * integrity constraints * Client/Server Architecture * initialization parameters that can be dynamically changed * process, component, and function 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 * optimizer * use the query prompt * PL/SQL function * partition in the SQL statement 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.