Concepts and optimizations for OLTP (online transactional processing) and OLAP (Online analytical Processing)

OLTP, also called online transaction processing ( Online Transaction Processing ), represents a very high-transactional system, typically a highly available online system, with small transactions and small queries as the primary evaluation of their systems, generally looking at the number of executions per second Transaction Execute SQL . In such a system, a single database tends to process Transaction more than hundreds of, or thousands of, of statements per second, with Select thousands of or even tens of thousands of executions per second. Typical OLTP systems are e-commerce systems, banks, securities, etc., such as eBay the U.S. business database, is a typical OLTP database.
OLTPThe most likely bottleneck in the system is CPU with the disk subsystem.

(1) CPU bottlenecks often appear in the total amount of logical reads and computational functions or processes, the total amount of logical reading equals the logical read of a single statement multiplied by the number of executions, if a single statement execution speed is very fast, but the number of executions is very high, it can also lead to a large amount of logical reading. The method of design and optimization is to reduce the logical reading of individual statements, or to reduce their number of executions. In addition, some computational functions, such as custom functions, such as decode the frequent use of a lot of CPU time, resulting in increased system load, the correct design method or optimization method, the need to avoid the calculation process, such as saving the results of the calculation to the table is a good way.

(2) disk subsystem in the OLTP environment, its load-carrying capacity generally depends on its IOPS processing capacity. Because in the OLTP environment, disk physical reading is generally, that is, a db file sequential read single block of reading, but the frequency of this read is very frequent. Large performance issues can occur if the disk subsystem is frequently not able to host it IOPS .

OLTPThe more commonly used design and optimization methods are Cache technology and B-tree indexing technology, which Cache determines that many statements do not need data from the disk subsystem, so it is important to the Web cache Oracle data buffer OLTP system. In addition, in terms of index usage, the simpler the better, the more stable the execution plan, and the need to use bound variables, reduce statement resolution, minimize Table association, minimize distributed transactions, and not use partitioning technology, MV technology, parallel technology, and bitmap indexing. Because of the high concurrency, batch updates are quickly submitted in batches to avoid blocking.
OLTPA system is a system in which data blocks change very frequently and SQL statements are submitted very frequently. For data blocks, the data block should be kept in memory as much as possible, for SQL example, using variable binding technology to achieve SQL reuse, reduce physical I/O and repetitive SQL parsing, and greatly improve the performance of the database.

This affects performance in addition to binding variables, and may also be hot fast ( hot block ). When a block is read by multiple users at the same time, Oracle in order to maintain the consistency of the data, it is necessary Latch to serialize the user's operation. When a user is acquired latch , the other user can only wait, the more users get this chunk of data, the more waiting is more obvious. This is the problem of hot and fast. This heat can be either a block of data or a rollback end block. For data blocks, usually the data distribution of the database is not uniform, if it is the data block of the index, you can consider creating a reverse index to achieve the purpose of redistribution of data, for the rollback segment data block, you can add a few more rollback segments to avoid this contention.

OLAP, also called the Online Analysis Processing ( Online Analytical Processing ) system, sometimes called DSS Decision Support System, is what we say the Data Warehouse. In such a system, the execution amount of the statement is not an assessment, because the execution time of a statement can be very long, and the data read is very much . Therefore, in such a system, the standard of assessment is often the disk subsystem throughput (bandwidth), such as the amount MB/s of traffic can be reached.

Disk subsystem throughput is often dependent on the number of disks, this time, Cache basically no effect, the database read-write type is basically db file scattered read with direct path read/write . Should try to use more than the number of disks and relatively large bandwidth, such as 4Gb the fiber interface.

In the OLAP system, partitioning technology and parallel technology are often used.

The importance of partitioning technology in the OLAP system is mainly embodied in the database management, such as database loading, can be implemented by partition Exchange, backup can be implemented by the Backup partition table space, delete data can be deleted by partition, as for the performance of the partition effect, It can make scanning of some large tables very fast (scan only a single partition). In addition, if the partition is combined in parallel, it can also make the whole table scan become very fast. In short, the main function of the partition is the convenience of management, it does not guarantee the improvement of query performance, sometimes the partition will bring performance improvement, sometimes reduce.

Parallel technology In addition to the partition technology, in Oracle 10g , and RAC combined with the implementation of multi-node simultaneous scanning, the effect is very good, can be a task, such as select the full table scan, the average allocation to a number RAC of nodes up.

In the OLAP system, binding () variables are not required because the execution of the BIND entire system is small, the analysis time can be ignored for execution time, and the wrong execution plan can be avoided. However, OLAP you can use a large number of bitmap indexes, materialized views, for large transactions, as far as possible to seek speed optimization, there is no need to like OLTP requirements for fast submission, or even deliberately slow down the speed of execution.

The real purpose of the binding variable is in the OLTP system, which usually has this characteristic, the user concurrency is very large, the user's request is very dense, and the majority of these requests SQL can be reused.

For the OLAP system, most of the time the database is running a report job, the execution is basically an aggregation class SQL operation, for example, at group by this point, the optimizer mode is set to all_rows be appropriate.

And for some of the more paging operations of the site class database, set to first_rows be better. But sometimes for OLAP systems, we have pagination, and we can consider using each one SQL hint . Such as:

    Select  a.* from table a;

Separate design and optimization

In the design of special attention, such as in a highly available OLTP environment, do not blindly put OLAP the technology to use.

such as partitioning technology, assuming that the partitioning keyword is not used in a wide range, and that other fields are used as where criteria, then, in the case of local indexes, multiple indexes will have to be scanned, and performance becomes much lower. If it is a global index, it loses the meaning of the partition.

Parallel technology is the same, generally in the completion of large-scale tasks to use, such as in real life, translation of a book, you can first arrange for more than one person, each person to translate different chapters, so that can improve the speed of translation. If you only translate a page of books, but also to assign different people to translate different lines, and then combine, there is no need, because in the time allotted work, a person may have been translated.

Bitmap indexing is also the same, if used in the OLTP environment, it is easy to cause blocking and deadlock. However, in the OLAP environment, the query speed may be increased because of its unique characteristics OLAP . It MV is also basically the same, including triggers, which, on DML frequent OLTP systems, can easily become bottlenecks or even Library Cache wait, while in the OLAP environment, it may be possible to improve the query speed by using it appropriately.

For the OLAP system, there is little room to optimize in memory, increasing CPU processing speed and disk I/O speed is the most direct way to improve database performance, which also means the increase of system cost.
For example, we want to aggregate hundreds of millions of or billions of data, this massive amount of data, all in memory operation is very difficult, but also not necessary, because the data is seldom reused, cache it is not practical, but also cause I/O considerable physical. So the bottleneck of this system is often the disk I/O above.

For the OLAP system, SQL the optimization is very important, because it has a large amount of data, do a full table scan and index performance difference is very large.

Concepts and optimizations for OLTP (online transactional processing) and OLAP (Online analytical Processing)