Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about the role of OLAP and OLTP in Oracle. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

What is OLTP?

OLTP, also known as online transaction processing (Online Transaction Processing), refers to highly transactional systems, generally highly available online systems, mainly small transactions and small queries. When evaluating its system, it generally depends on the number of Transaction and Execute SQL executed per second. In such a system, a single database often processes more than hundreds or thousands of Transaction per second, and the execution of Select statements is thousands or even tens of thousands per second. Typical OLTP systems include e-commerce systems, banks, securities and so on, such as the business database of eBay in the United States, which is a typical OLTP database.

OLTP system is most prone to bottlenecks are CPU and disk subsystem.

(1) the bottleneck of CPU is often shown in the total amount of logical reads and computational functions or processes. The total amount of logical reads is equal to the number of logical reads multiplied by the number of execution times of a single statement. If the execution speed of a single statement is very fast, but the number of times of execution is very large, it may also lead to a large amount of logical reads. The method of design and optimization is to reduce the logical reading of individual statements, or to reduce the number of times they are executed. In addition, the frequent use of some computational functions, such as custom functions and decode, will also consume a lot of CPU time, resulting in an increase in the load of the system. The correct design method or optimization method needs to avoid the calculation process as far as possible, such as saving the calculation results to the statistical table is a good method.

(2) the carrying capacity of disk subsystem in OLTP environment generally depends on its IOPS processing capacity. Because in an OLTP environment, disk physical reads are usually db file sequential read, that is, single-block reads, but these reads are very frequent. If it is so frequent that the disk subsystem cannot carry its IOPS, there will be major performance problems.

The commonly used design and optimization methods of OLTP are Cache technology and B-tree index technology. Cache determines that many statements do not need to obtain data from the disk subsystem, so Web cache and Oracle data buffer are very important to the OLTP system. In addition, in terms of index use, the simpler the statement, the better, so that the execution plan is stable, and be sure to use binding variables, reduce statement parsing, minimize table association, minimize distributed transactions, and basically do not use partitioning technology, MV technology, parallel technology and bitmap index. Because of the high concurrency, batch updates should be submitted quickly in batches to avoid blocking.

OLTP system is a system in which data blocks change frequently and SQL statements are submitted very frequently. For data blocks, data blocks should be kept in memory as much as possible. For SQL, variable binding technology should be used as far as possible to achieve SQL reuse, reducing physical SQL O and repeated SQL parsing, thus greatly improving the performance of the database.

In addition to binding variables, it may also be hot block that affects performance. When a block is read by multiple users at the same time, Oracle needs to use Latch to serialize the user's operations in order to maintain data consistency. When a user gets the latch, other users have to wait, and the more users get the block, the more obvious the wait becomes. This is the problem of hot speed. This thermal fast may be a data block or a rollback end block. For data blocks, it is usually caused by the uneven distribution of data in the database. If it is an indexed data block, you can consider creating reverse directions to achieve the purpose of redistributing data. For rollback segment data blocks, several more rollback segments can be added appropriately to avoid this kind of contention.

What is OLAP?

OLAP, also known as online analytical processing (Online Analytical Processing) system, sometimes also called DSS decision support system, is what we call data warehouse. In such a system, the amount of statement execution is not the assessment standard, because the execution time of a statement may be very long and a lot of data can be read. Therefore, in such a system, the criterion is often the throughput (bandwidth) of the disk subsystem, such as how much MB/s of traffic can be achieved.

The throughput of the disk subsystem often depends on the number of disks. At this time, Cache is basically ineffective, and the read and write types of the database are basically db file scattered read and direct path read/write. Try to use a large number of disks and large bandwidth, such as 4Gb's optical fiber interface.

In OLAP system, partition technology and parallel technology are often used.

The importance of partition technology in OLAP system is mainly reflected in database management, such as database loading, which can be realized by partition exchange, backup can be achieved by backing up partition table space, deleted data can be deleted through partition, as for the impact of partition on performance, it can make some large tables scan quickly (only scan a single partition). In addition, if partitioning is combined with parallelism, it can also make the scan of the entire table very fast. In short, the main function of partition is the convenience of management, it can not absolutely guarantee the improvement of query performance, sometimes partition will bring performance improvement, sometimes it will be reduced.

In addition to the combination of parallel technology and partition technology, in Oracle 10g, combined with RAC to achieve multi-node simultaneous scanning, the effect is also very good. A task, such as the full table scan of select, can be evenly distributed to multiple RAC nodes.

In the OLAP system, there is no need to use BIND variables, because the execution amount of the whole system is very small, the analysis time can be ignored for the execution time, and the wrong execution plan can be avoided. However, bitmap indexes and materialized views can be widely used in OLAP. For large transactions, try to optimize the speed. There is no need to commit quickly as required by OLTP, or even deliberately slow down the execution speed.

The real use of binding variables is in the OLTP system, which usually has the characteristics of a large number of user concurrency, intensive user requests, and most of the SQL of these requests can be reused.

For OLAP systems, most of the time there are report jobs running on the database, performing basically aggregate SQL operations, such as group by, so it is appropriate to set the optimizer mode to all_rows. For some website databases with more paging operations, it would be better to set it to first_rows. But sometimes for OLAP systems, when we have paging, we can consider using hint in each SQL. Such as:

Select / * + first_rows (10) * / a.* from table a

Separate design and optimization

Special attention should be paid to the design, such as in the highly available OLTP environment, do not blindly bring the OLAP technology to use.

Such as partitioning technology, assuming that partitioning keywords are not widely used, but other fields are used as where conditions, then if it is a local index, multiple indexes will have to be scanned, and the performance will become even worse. If it is a global index, it loses the meaning of partitioning.

The same is true of parallel technology, which is generally used only when completing large tasks, such as in real life, when translating a book, you can arrange multiple people to translate different chapters first, which can improve the speed of translation. If you just translate a page of a book, assign different people to translate different lines, and then combine them, it will not be necessary, because in the time assigned to work, a person may have already finished the translation.

The same is true of bitmap indexes, which can easily cause blocking and deadlocks if used in an OLTP environment. However, in the OLAP environment, the query speed of OLAP may be improved because of its unique characteristics. MV is basically the same, including triggers, etc., in DML frequent OLTP systems, it is easy to become a bottleneck, or even Library Cache waiting, while in OLAP environment, it may improve the query speed because of proper use.

For OLAP systems, there is little room for memory optimization. Increasing the CPU processing speed and disk I / O speed is the most direct way to improve database performance, of course, it also means the increase of system cost.

For example, if we want to aggregate hundreds of millions or billions of pieces of data, it is difficult to operate all of this huge amount of data in memory, and it is not necessary, because these data are rarely reused, and there is no practical significance to cache them. and it will cause the physical Ibind O to be quite large. Therefore, the bottleneck of this kind of system is often the one above the disk Iripple O.

For OLAP systems, SQL optimization is very important because it has a large amount of data, and doing full table scans and indexes makes a great difference in performance.

This is what the role of OLAP and OLTP in Oracle shared by Xiaobian is. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.