Introduction to OLTP and OLAP

What is OLTP?

OLTP, also known as online transaction processing (Online Transaction Processing), Indicating that the transaction is very high. Generally, it is a high-availability online system, which focuses on small transactions and small queries. When evaluating its system, generally, it depends on the number of transactions executed per second and the number of Execute SQL statements. In such a system, a single database usually processes several hundred or thousands of transactions per second, and the number of select statements executed is several thousand or even tens of thousands per second. Typical OLTP systems include e-commerce systems, banks, and securities.

The OLTP system emphasizes the database memory efficiency, the command rate of various memory indicators, the variable binding, and concurrent operations.

CPU and disk subsystems are the most prone to bottlenecks in the OLTP system.

(1) CPU bottleneck. A regular table shows the total number of logical reads and computing functions or processes. The total number of logical reads equals to the number of logical reads of a single statement multiplied by the number of executions. If the execution speed of a single statement is fast, however, a large number of executions may also lead to a large amount of logical reads. The design method and optimization method are to reduce the logical reads of a single statement or reduce the number of execution times. In addition, the frequent use of computing functions, such as user-defined functions and decode, also consumes a lot of CPU time, resulting in an increase in the system load, the correct design method or optimization method should be avoided as much as possible. For example, it is a good method to save the calculation results to the statistical table.

(2) In the OLTP environment, the carrying capacity of the Disk Subsystem generally depends on its iops processing capability. in the OLTP environment, physical reads on a disk are generally dB file sequential read, that is, a single read, but this read frequency is very frequent. If the disk subsystem is unable to carry its iops frequently, a high performance problem may occur.

The cache and B-tree indexes are commonly used in OLTP design and optimization.

(1) cache determines that many statements do not need to obtain data from the disk subsystem. Therefore, Web Cache and ORACLE data buffer are very important to the OLTP system. For data blocks, data blocks should be stored in the memory as much as possible. For SQL statements, variable binding technology should be used as much as possible to achieve SQL reuse and reduce physical I/O and repeated SQL parsing, this greatly improves the database performance.

(2) In terms of index usage, the simpler the Statement, the better. In this way, the execution plan is stable, and variable binding must be used to reduce statement parsing, table joining, and distributed transactions, partition technology, MV technology, parallel technology, and bitmap indexing are basically not used. Because of the high concurrency, batch update must be submitted in batches quickly to avoid blocking.

(3) In addition to binding variables, the impact on performance may also be caused by hot block ). When a block is read by multiple users at the same time, to maintain data consistency, Oracle needs to use latch to serialize user operations. When a user obtains latch, other users can only wait. The more users get the data block, the more obvious the waiting. This is the hot issue. This kind of hot speed may be data blocks or rollback blocks. Data blocks are usually caused by uneven data distribution in the database. If the data blocks are indexed, you can create reverse data blocks to achieve data redistribution, for data blocks in a rollback segment, you can add more rollback segments to avoid such contention.

What is OLAP
OLAP, also known as Online Analytical Processing (Online Analytical ProcessingSystem, sometimes called DSS Decision Support System, is what we call data warehouse. In such a system, the execution of a statement is not an assessment standard, because the execution time of a statement may be very long, and a lot of data is read. Therefore, in such a system, the evaluation standard is usually the throughput (bandwidth) of the disk subsystem, such as the amount of Mbit/s of traffic. The throughput of the disk subsystem is usually determined by the number of disks. At this time, the cache is basically ineffective, and the database read and write types are basically dB file scattered read and direct path read/write. Use a large number of disks and a large bandwidth, such as a 4 GB optical fiber interface.

OLAP systems emphasize data analysis, SQL Execution market, disk I/O, and partition.