Concepts and optimization of OLTP (online transaction processing) and OLAP (Online Analytical Processing)

Concepts and optimization of OLTP (online transaction processing) and OLAP (Online Analytical Processing)

OLTP, also known as Online Transaction Processing (Processing), indicates a highly transactional system. Generally, it is a highly available Online system, dominated by small transactions and small queries, when evaluating the system, it generally depends on the number of transactions executed per second and Execute SQL. 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. For example, eBay's business database is a typical OLTP database.
The 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 common design and optimization methods of OLTP are Cache and B-tree Indexing. 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. In addition, 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.
The OLTP system is a system where data blocks change frequently and SQL statements are submitted frequently. 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.

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 a reverse index to re-distribute the data, for data blocks in a rollback segment, you can add more rollback segments to avoid such contention.

OLAP is also called the Online Analytical Processing System. Sometimes it is also called the DSS decision support system, which is what we call the data warehouse. In such a system, the statement execution volume is not an assessment standard,Because the execution time of a statement may be very long, and the amount of data read is also very large.. Therefore, in such a system, the assessment criteria are usuallyThroughput of the Disk Subsystem(Bandwidth), such as how many Mb/s traffic can be reached.

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.

Partition and parallel technologies are often used in OLAP systems.

The importance of partition technology in OLAP systems is mainly reflected in database management. For example, Database loading can be achieved through partition exchange, and backup can be achieved through backup of Partition Table space, data can be deleted through partitions. As to the performance impact of partitions, it can quickly scan some large tables (only scan a single partition ). In addition, if partitions are combined with parallelism, the entire table can be quickly scanned. In short, the main function of a partition is ease of management. It cannot definitely improve the query performance. Sometimes the partition will improve the performance and sometimes decrease.

In addition to combining with the partitioning technology, the parallel technology achieves multi-node scanning while implementing multi-node scanning in Oracle 10 Gb with RAC, and the effect is also very good. You can take a task, for example, the select full table scan is evenly distributed to multiple RAC nodes.

In OLAP systems, you do not need to use BIND variables. Because the execution volume of the entire system is small, the analysis time can be ignored for the execution time, in addition, you can avoid incorrect execution plans. However, in OLAP, bitmap indexes and materialized views can be used in a large amount. For large transactions, we try our best to seek Speed Optimization. There is no need to request fast commit like OLTP, or even deliberately slow down the execution speed.

The real purpose of variable binding is in the OLTP system. This system usually features a large number of user concurrency and intensive user requests, most of these request SQL statements can be reused.

For OLAP systems, most of the time the database runs a report job, the execution is basically an aggregate SQL operation, such as group by. At this time, it is appropriate to set the optimizer mode to all_rows.

It is better to set first_rows for website-type databases with many paging operations. However, sometimes we have paging pages for the OLAP system, we can consider using hint in each SQL statement. For example:

    Select  a.* from table a;

Separate Design and Optimization

Pay special attention to the design. For example, in highly available OLTP environments, do not blindly use OLAP technology.

For example, in partition technology, if the partition keyword is not used in a wide range and other fields are used as the where condition, if the local index is used, Multiple indexes will have to be scanned, the performance becomes lower. If it is a global index, the meaning of the partition is lost.

This is also true of parallel technology, which is generally used when a large task is completed. For example, if you translate a book in real life, you can arrange multiple people to translate different chapters for each person, this improves the translation speed. If you only translate one page of books and assign different people to translate different rows and combine them, it is unnecessary because during the time of assignment, A person may have already translated the text.

Bitmap indexes are the same. If used in an OLTP environment, blocking and deadlock are easily caused. However, in the OLAP environment, the query speed of OLAP may be improved due to its unique features. The same is true for music videos, including triggers. In dml oltp systems that frequently occur, music videos can easily become bottlenecks, or even Library Cache waits. In OLAP environments, the query speed may be improved due to proper use.

For OLAP systems, there is little room for optimization in the memory. Increasing the CPU processing speed and disk I/O speed is the most direct way to improve database performance, of course, this also means an increase in system costs.
For example, if we want to aggregate hundreds of millions or billions of data records, it is very difficult to store all such massive data in the memory, and there is no need at the same time, because the data is rarely reused quickly, it does not make practical sense to cache it, and it also causes a large physical I/O. Therefore, the bottleneck of such a system is usually the disk I/O.

For OLAP systems, SQL optimization is very important because it has a large amount of data. The performance difference between full table scanning and indexing is very large.