Figuring out your business type--OLTP or OLAP

In Oracle database systems, many people are not aware of what their business type is, and are beginning to blindly seek optimization methods, often using OLAP methods in OLTP, or OLTP methods used in OLAP. Such use, sometimes, performance without any improvement, or even greatly affect the performance, get counterproductive effect. So, before you optimize your system, figure out your business type.

1. What is OLTP
OLTP, also called online transaction processing (online Transaction processing), represents a highly transactional system, typically a highly available online system, with small transactions and small queries as the primary evaluation of their systems, It generally looks at the number of transaction and execute SQL executed per second. In such a system, the number of transaction processed per second is often more than hundreds of, or thousands of, and the execution of a SELECT statement is thousands of or even tens of thousands of per second. Typical OLTP systems, such as e-commerce systems, banks, certificates, etc., such as the business database of ebay in the United States, are typical OLTP databases.
The most likely bottleneck for an OLTP system is the CPU and disk subsystem. The CPU depends on logical reads as well as internal calls, such as functions and so on. A SQL statement that executes frequently, if each statement can be reduced by a small amount of logical reads, is also equivalent to optimizing some large statements that are poorly read logically. Many people do not feel the role here, think of a statement dozens of logical reading, the execution time is basically 0, do not need optimization, in fact, as long as his execution times are very frequent, and there is room for optimization, it must be optimized, such as reducing the number of logical read or reduce execution times, are optimization methods.
In addition, some computational functions, such as Sum,count,decode, are used very frequently and are very CPU-intensive, and I encounter a system, because a SQL statement, a large number of use of sum and decode for the row and column conversion, As a result, this statement consumes more than half the CPU of the entire machine.
So, in a general OLTP system, if you do not consider the function I said above, then the logical read multiplied by the number of executions, determines the CPU consumption, such as a statement, the number of executions per second is 500 times, each logic read 15, but through optimization, can let each statement of logical reading from 15 to 10, then , the logical reading per second can reduce 500*5=2500, in fact, it is equivalent to optimize an execution frequency of 1 times per second, each logical read 2,500 statements (note, 2,500 logical read, in the OLTP system is very poor statement). Again, assuming a 1GHZ CPU per second can normally handle the logical read is 100,000, if it is 10 logical read a statement, can handle 10,000 per second, and 1000 logical read a statement, each second can only handle 100.
In the same way, the physical reads multiplied by the number of executions, determines the processing power of the storage subsystem, in an OLTP environment, the physical reading is generally the DB file sequential read decision, that is, a single block read, a typical OLTP system, db file Sequential Read should be basically equal to the reading IOPS of the disk subsystem. The IOPS processing power of the disk subsystem is very much related to the cache hit rate and the number of disks. Some of my articles also analyzed these issues, such as a 15K rpm disk, up to 150 iops per second, the basic is the limit, if the cache is not hit, then 100 disks, the maximum can handle the IOPS is only 15,000 (but, in fact, And it's basically not up to this value).
The most common technology for OLTP is the cache technology and the Btree index, and the cache determines that many statements do not require data from the disk subsystem, so the Web cache and Oracle data buffer are important to the 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 the parsing of statements, and minimize the association. In other respects, the basic use of partitioning technology, MV Technology, parallel technology, and bitmap indexing, because of the high concurrency, the bulk update may want to submit as fast as possible to avoid blocking occurrence.
One important point in ebay's database design is that the database is only responsible for storing the data, and the business logic is implemented at the business level as much as possible because the database extension is difficult, and the application server extension is simple. In fact, that is, in a highly available OLTP environment, the simpler the database is used, the better the functionality.

2. What is OLAP
OLAP, also called online analysis (Online analytical processing), sometimes called DSS decision support system, is what we say the Data Warehouse. In such a system, the execution of a 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 determined by the disk subsystem throughput. Disk subsystem throughput is directly dependent on the number of disks, this time, the cache basically has no effect, this time the database read and write is basically the db file scattered read and direct path read/write. As described in some of my previous articles, if a 15K disk has an IO volume of 13M per second, then 100 disks, up to the maximum throughput, can be 1300m/s (in fact, this value is basically not the same). If the number of disks is sufficient, you also need to consider the use of larger bandwidth, such as the 4GB fiber interface.
In the OLAP system, the technology commonly used is partitioning technology and parallel technology. such as partitioning technology can make some large table scan quickly (only a single partition), and easy to manage. In addition, if the partition is combined in parallel, it can also make the whole table scan become very fast. Parallel technology In addition to the partitioning technology, in the Oracle 10g, in conjunction with RAC to achieve multi-node simultaneous scanning, the effect is very good, a task, such as select Full table scan, the average allocation to multiple RAC nodes up.
In an OLAP system, you do not need to use bound variables, because the entire system executes very little, the analysis time can be ignored for execution time, and the wrong execution plan is avoided. However, OLAP can use a lot of bitmap index, materialized view, for large transactions, as far as possible to seek speed optimization, there is no need to like OLTP need to submit quickly, or even deliberately slow down the speed of execution.

3. Summary
Especially in highly available OLTP environments, do not blindly use OLAP technology, such as partitioning technology, if the partition is not a wide range of keywords as a where condition, and other fields as the where condition, then, if it is a local index, you will have to scan multiple indexes, And the performance becomes even lower. If it is a global index, then what is the meaning of the partition, just a license of partitioning technology.
Parallel technology is the same, generally in large tasks when used, like saying, in real life, a relatively large-scale work, such as translating a book, you can first arrange for more than one person, everyone translates different chapters, so that can improve the speed of translation, but, you are now just translation one page, You also go to assign different people to translate different lines, and then combine them, this time, you are a person or already translated.
Bitmap indexing in my previous articles, I have explained that if used in an OLTP environment, it may be too large to block and deadlock, but in an OLAP environment, it is possible to improve OLAP query speed because of its unique characteristics. MV is also basically the same, including triggers, and so on, on DML frequent OLTP systems, it is easy to become a bottleneck, and in an OLAP environment, it may be appropriate to improve the query speed.

More differences and technology, detail down too much, some things, is to rely on everyone slowly to understand, I do not say much here, we can usually in their own business more experience.

OLTP data	OLAP data
Raw data	Exporting data
Details of the data	Comprehensive and refined data
Current Value data	Historical data
Can be updated	Not updatable, but periodically refreshed
Small amount of data processed at one time	Large amount of data processed at one time
Application-oriented, transaction-driven	Analysis-oriented, analysis-driven
For operators, support daily operations	For decision makers, support management needs

Figuring out your business type--OLTP or OLAP