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.
OLTP
The 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
.
OLTP
The 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.
OLTP
A 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)