Oracle 10g DBCA Library Four options------General purpose Transactional processing custom database Data Warehouse

General Purpose = Take account of both OLTP and OLAP
Transaction processing =OLTP
Data Warehouse =olap
Custom DATABASE = Custom

Gold dividing line (deep analysis) *******************************

Data processing can be roughly divided into two main categories: online transaction processing OLTP (on-line transaction processing), online analytical processing OLAP (On-line Analytical Processing). OLTP is the main application of the traditional relational database, mainly basic and daily transaction processing, such as bank transaction. OLAP is the main application of Data Warehouse system, support complex analysis operation, focus on decision support, and provide intuitive and understandable query results.
The OLTP system emphasizes the efficiency of database memory, emphasizes the command rate of various indexes of memory, emphasizes the binding variables, emphasizes the concurrency operation, and the OLAP system emphasizes the data analysis, emphasizes the SQL execution market, emphasizes disk I/O, emphasizes partitioning, and so on.
comparison between OLTP and OLAP:What is OLTP OLTP, also called on-line transaction processing (online Transaction processing), indicates that a highly transactional system, typically a highly available online system, evaluates its systems with small transactions and small queries, The number of transaction executed per second and execute SQL is generally seen. In such a system, a single database often handles more than hundreds of transaction per second, or thousands of, with a Select statement of thousands of or even tens of thousands of executions per second. Typical OLTP systems include e-commerce systems, banks, securities, etc., such as the U.S. ebay Business database, which is a typical OLTP database. The most likely bottleneck of an OLTP system is the CPU and disk subsystem. (1) CPU bottlenecks often appear in the total logical reading and computational function or is the process, the amount of logical reading equals the logical reads multiplied by the number of executions of a single statement, and if a single statement executes quickly, but with a very high number of executions, it can also result in a large amount of logical reads. The method of design and optimization is to reduce the logical reading of individual statements, or to reduce their execution times. In addition, some computational functions, such as custom functions, decode, etc. frequently used, will also consume a lot of CPU time, causing the system load, the correct design method or optimization method, need to avoid the calculation process, such as saving the results to statistics is a good way. (2) disk subsystem in an OLTP environment, its load capacity is generally dependent on its IOPS processing power. Because in an OLTP environment, disk physical reads are generally DB file sequential read, which is a single block read, but this is a very frequent read. Large performance problems can occur if the disk subsystem is frequently unable to host its iops. 


The common design and optimization methods for OLTP are cache technology and B-tree indexing 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 OLTP systems. In addition, in terms of index usage, the simpler the statement, the better the execution plan, and the need to use binding variables, reduce statement parsing, minimize the table association, minimize the distributed transaction, basically do not use partitioning technology, MV Technology, parallel technology and bitmap indexing. Because of the high concurrency, batch updates should be submitted quickly in batches to avoid blocking. an OLTP system is a system in which data blocks change very frequently and SQL statements are submitted very frequently. For data blocks, you should keep chunks of data in memory as much as possible, and for SQL, use variable binding techniques as much as possible to achieve SQL reuse, reduce physical I/O, and repeat SQL parsing, thereby greatly improving database performance. This affects performance in addition to binding variables, and possibly hot blocks. When a block is read at the same time by multiple users, Oracle needs to use latch to serialize the user's operations in order to maintain data consistency. When a user gets a latch, other users can only wait, the more users get the data block, the more obvious the wait. This is the problem of hot and fast. This heat may be a block of data, or it may be a rollback end block. For the data block, usually the data distribution of the database is uneven, if it is an index of data blocks, you can consider creating a reverse so as to achieve the purpose of redistribution of data, for the rollback segment data block, you can add a few rollback segment appropriate to avoid this contention. 


What is OLAP OLAP, also called On-line Analytical processing (online analytical processing) system, sometimes called DSS decision support system, is the data warehouse that we say. In such a system, the execution of a statement is not an evaluation criterion, 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 how many MB/s traffic can be achieved. 


The disk subsystem throughput often depends on the number of disks, at this time, the cache is basically ineffective, the database read and write type is basically db file scattered read and direct path read/write. As far as possible, the number of disk and relatively large bandwidth, such as 4Gb fiber interface. In OLAP systems, partitioning techniques and parallel technologies are often used. 


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


In addition to the integration of partitioning technology, parallel technology, in Oracle 10g, and RAC in conjunction with the simultaneous scanning of multiple nodes, the effect is very good, can be a task, such as Select a full table scan, the average allocation to multiple RAC nodes. 


in an OLAP system, you do not need to use BIND (BIND) variables because the overall system is small, the analysis time is negligible for execution time, and the wrong execution plan can be avoided. But OLAP can use a lot of bitmap indexing, materialized views, for large transactions, as far as possible to seek speed optimization, there is no need for fast submission like OLTP requirements, or even deliberately slow down the speed of execution. 


The real purpose of binding variables is that in an OLTP system, the system usually has the characteristics of a large number of concurrent users, a very intensive user request, and that most of the SQL for these requests can be reused. 


for OLAP systems, most of the time the database is running on a report job, which is basically an aggregation class of SQL operations, such as group by, which is appropriate to set the optimizer mode to All_rows. And for some paging operations more Web site class database, set to First_rows will be better. But sometimes for OLAP systems where we have pagination, we can consider using hint in every SQL. such as:Select a.* from table A;




separate design and optimization


pay special attention to design, such as in highly available OLTP environments, do not blindly bring OLAP technology to use. 


such as zoning technology, assuming that the partitioning keyword is not used on a large scale, and that other fields are used as the where condition, then if it is a local index, it will have to scan more than one index, and performance becomes even more low. If it is a global index, it also loses the meaning of the partition. 


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


The same is true for bitmap indexing, which can easily result in blocking and deadlock if used in an OLTP environment. However, in an OLAP environment, the query speed of OLAP may be increased because of its unique characteristics. MV is basically the same, including triggers and so on, in DML frequent OLTP system, it is easy to become a bottleneck, or even the library cache waiting, and in the OLAP environment, may be due to the use of appropriate and improve the query speed. for OLAP systems, there is little room for optimization on memory, and increasing CPU processing speed and disk I/O speed is the most direct way to improve database performance, which also means increased system costs. For example, we want to aggregate hundreds of millions of or billions of data, this massive data, all in memory operation is difficult, but also not necessary, because these data quickly very little reuse, caching does not have practical significance, but also can cause physical I/O is quite large. So the bottleneck of this system is often disk I/O. for OLAP systems, the optimization of SQL is very important because it has a large amount of data, and it is very different for the performance of the whole table scan and index.