Introduction to Oracle OLAP and OLTP

Data processing can be roughly divided into two categories:Online Transaction ProcessingOLTP(On-line transaction processing),Online Analytical ProcessingOLAP(On-Line Analytical Processing).OLTPIs the main application of traditional relational databases., Mainly for basic and daily transaction processing, such as bank transactions.OLAPIs the main application of the data warehouse system., Supports complex analysis operations, focuses on decision-making support, and provides intuitive and easy-to-understand query results.

OLTPThe system emphasizes database memory efficiency, command rate of various memory indicators, variable binding, and concurrent operations;

OLAPThe system emphasizes data analysis andSQLMarket execution, emphasizing DisksI/OAnd partitions.

OLTPAndOLAPComparison:

 

 

 

 

What isOLTP

OLTP, Also called online transaction processing (Online Transaction Processing), Indicating a system with a very high level of transactionGenerally, it is a high-availability online system, which focuses on small transactions and small queries. When evaluating its system, it generally depends onTransactionAndExecute SQL. In such a system, a single database ProcessesTransactionUsually more than several hundred, or thousands,SelectThe number of statements executed is several thousand or even tens of thousands per second. TypicalOLTPSystems include e-commerce systems, banks, and securities, such as the United States.EBayIs a typical business database.OLTPDatabase.

 

OLTPThe most likely cause of system bottlenecks isCPUAnd the disk subsystem.

(1)CPUBottleneck often occurs when the total number of logical reads and computational functions or processes exist.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 but the number of executions is large, it 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, some compute functions, such as user-defined functions,DecodeAnd so on, it will consume a lotCPUTime, causing the system load to rise, the correct design method or optimization method, you need to avoid the calculation process as much as possible, such as saving the calculation results to the statistical table is a good method.

(2)The disk subsystem is inOLTPIn the environment, its carrying capacity generally depends on itsIOPSProcessing capability.Because inOLTPIn the environment, Disk Physical reads are generallyDb file sequential read, That is, a single read, but this read frequency is very frequent. If the disk subsystem is unable to carry itsIOPSIt will cause high performance problems.

 

OLTPThe commonly used design and optimization methods are:CacheTechnology andB-treeIndexing Technology,CacheIt is determined that many statements do not need to obtain data from the disk subsystem,So,Web cacheAndOracle data bufferPairOLTPThe system is very important. 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,MVTechnology, parallel technology, and bitmap indexing. Because of the high concurrency, batch update must be submitted in batches quickly to avoid blocking.

OLTPThe system is a data block that changes frequently,SQLSystems that frequently submit statements. Data blocks should be stored in the memory as much as possible.SQLFor example, try to use the variable binding technology to achieveSQLReuse to reduce physicalI/OAnd duplicateSQLTo greatly improve the database performance.

In addition to binding variables, the impact on performance may also be hot and fast (Hot block).When a block is read by multiple users at the same time,OracleTo maintain data consistency, you must useLatchTo serialize user operations. When a user obtainsLatchOther users can only wait. The more users get the data block, the more obvious they wait. 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 reverse data blocks to achieve data redistribution, for data blocks in a rollback segment, you can add more rollback segments to avoid such contention.

1 2 Next Page