Misconceptions about Oracle Performance Tuning __oracle

To improve performance, we have tried a number of ways or scenarios that the Oracle database itself has provided, mainly including:

Shared server mode (MTS)
Cluster technology (clustering) RAC
Partition
Parallel processing (mainly parallel queries)

These features provided by Oracle are indeed used for performance improvement, but we often ignore the analysis of our own application characteristics, whether they are suitable for us. Recently, through a deep understanding of this knowledge, we found that we had some misconceptions before. I think it is necessary for us all to change this misunderstanding.

Before we analyze, let's be clear about our application features. database application can be divided into OLAP and OLTP two main categories, namely: Online transaction analysis (Data Warehouse) and online transaction processing (transaction application) Our application system, its application characteristics are mainly online transaction processing, but also contains a small number of data warehouse characteristics.

1. Shared Server (MTS)

Oracle defaults to a dedicated server mode, that is, a process that corresponds to one server for a user connection process. Remember when a major hospital was opened, we tried MTS. Because I heard that MTS is connecting more clients without adding memory and CPU, the result is not what we expected. Is there a problem with MTS? No, because we don't know about MTS, it's not that it has a problem, but it's not used to do it in this situation.

General situation, MTS is recommended only when the number of concurrent connections exceeds the operating system's support, otherwise the default dedicated server mode should be used. That is, in dedicated server mode, because more than one connection will consume more than one operating system process, only when concurrent application requirements exceed the operating system's allowed number of connections , it is only necessary to consider MTS.
If the existing system, a dedicated server database that supports 100 connections physically, uses shared server mode instead, and may support 1000 connections, but may have only 100 active connections. Typically 2 to 4 CPU servers, which should be sufficient for 200 to 400 concurrent connections, if the connection is increased, Can increase CPU and memory.

MTS has some of the following drawbacks:

1. The code path for a shared server is longer than a dedicated server, so it is inherently slower than a dedicated server.
2. There is the possibility of an artificial deadlock, because it is serial, all shared servers are bound together (a process), as long as one connection is blocked, all users are blocked and most likely to deadlock.
3. There is a possibility of an exclusive transaction, because if a session is running for too long, it exclusively shares resources and other users can only wait. (and dedicated servers, each client is a session)
4. The shared server model restricts certain database features, such as the inability to start and close the instance individually, cannot perform media recovery, cannot use log Miner, cannot be used, and sql_trace is meaningless (because it is shared rather than the current session).

The reduced memory of MTS is actually the memory required for each user to connect to the operating system process in the dedicated server mode, but it uses the large_pool of the SGA to allocate the UGA, and the reduced memory is very small. If the user session is connected and disconnected frequently, The cost of creating and deleting a database process can be very large, this is best done in a shared server mode (otherwise, connection pooling technology should be used). Fortunately, the design of our product may have taken this factor into account, using a connection lifetime use (during the session lifecycle) to avoid this situation.

So, to sum up, for our products, we recommend using the default dedicated server mode, when the connection is not enough, by adding hardware to resolve, rather than using MTS. In addition, Oracle can support both shared and dedicated server mode, and can specify a session to use a dedicated server. Another session uses a shared server.

2. Cluster Technology (RAC)

Oracle RAC (real application clusters), we say that dual-computer fault tolerance is a kind of RAC. The advantage of cluster technology lies in the lateral expansion of performance, and provides high availability. 32-bit operating systems have 4G of memory limitations, and some UNIX systems (and non-advanced versions of Windows) have limits on the number of CPUs. While cluster technology works together by assembling multiple machines, this restriction is broken horizontally. Through the RAC , a single instance of the server, multiple machines form an instance service set, and the client connects to it. This technology, we sometimes say to the customer is load balanced, actually this is one-sided, RAC is mainly for CPU and memory load balancing, and does not achieve disk IO load balancing. (Of course, disk IO can be implemented via RAID or NAS)

One advantage of RAC is that increased availability, that is, a server is broken (note: Not a data storage medium), does not affect normal use. Like load balancing, it increases the availability above the data tier, but not all of it, because the data is broken and there is no way to do it. (data layer, that's the Oracle data Guard, or simply say it's storage hardware.)

However, the benefits of RAC also have a performance impact. Because it wants to globally coordinate the data cache to ensure that the cached data that is connected to each instance is consistent, the following three contradictions are magnified:

1. Cache contention
2. Excessive I/O
3. Lock

That is, if there is a problem with the RAC, the problem will be much greater, for example, if the SQL does not use binding variables to cause cache contention, it is more serious to use RAC.
All in all, if your server's CPU is plugged in and your memory is up to the limit, and concurrent users are growing, or you're asking for a very high downtime, RAC is definitely your choice.

3. Zoning

The purpose of Oracle partitioning is to divide large tables or indexes into small fragments. To make it easier to manage. We may have mistakenly thought that zoning is fast=true, can improve speed, and has done experiments in oncology and pediatrics. In fact, partitions generally do not speed up query speed in transaction processing systems ( In some cases, contention for shared resources may be reduced. Oracle partitioning features, mainly for the data warehouse to design, that is, if you have a table of 100G size, preferably using partitions, the advantages of the following three aspects:

1. Improve Usability

Partitioning is the principle of divide and conquer, if a table is divided into multiple partitions, one of the partitions in the media out of the problem, does not affect the entire table of other partitioned data access.

2. Easy to manage

In the Data Warehouse, the table is divided into small pieces, easier to bulk Delete, defragment, and some parallel processing.

3. Improve performance

In this regard, it is most difficult to achieve by partitioning, and it must be carefully calculated to arrange the partition data.

Zoning planning is complex, take our product application, the general query involves more than one table, multiple indexes, assuming that we put the patient expense records, drug delivery records, patient orders records such large table to establish a partition. Obviously, the range partitioning is not very useful for our performance, and the hash partitioning is our query requirement, However, most data hashes are not concentrated. Plus, there are so many indexes on these tables, commonly used IDs, time-class indexes, and few people can do all of them globally or accurately range partitioning (in fact, it might not be possible to have multiple indexed ranges on demand). If the query often involves multiple indexes, how to ensure that every index used is on a partition, if not, must scan multiple partitions, increase logical I/O and CPU time, thus increasing the query time. (data is distributed across different physical storage media, discussed in parallel processing below)

Again, in some cases, it may be possible to reduce contention for shared resources, meaning that parallel modifications and updates are faster. What is the principle of our zoning? The most common possibility is to partition the range by time, so that most of the modifications and updates are made on the same partition, So the contention for reducing shared resources is basically ineffective. (Is there a unique application requirement for a hash partition by Department ID?) is there a unique application requirement based on a list partition (typical eigenvalue)? basically no. Partitioning mainly improves performance from a parallel perspective, but the application of the transaction processing system itself determines that it is not suitable for this technique. That is to say, there is no need to adopt zoning technology for the transaction application characteristics of our products.

4. Parallel processing

According to our application characteristics, the main analysis of parallel query. General requirements with partition characteristics, multiple CPU hardware. Since Oracle 8.1.6, an option to automatically adjust parallel queries is added: parallel_automatic_tuning= True to set the parallel parameter on the corresponding table, Oracle automatically parallelization the actions on that table at the appropriate time. Parallel queries are largely useless for transactional systems. Because the design of parallel queries is done for a single user in the data warehouse that consumes 100 of the resources completely. In transaction processing, There are often a lot of concurrent users who compete for shared resources, so it's counterproductive to try to get a user to take all the resources.