Oracle performance misunderstanding-MTS, RAC, partitioning, and parallel query

Before learning a new thing, you need to understand its purpose. Article This section describes the advantages and disadvantages of Oracle RAC and other four components for future reference. The full text is as follows:

To improve performance, we have made a lot of trials on the methods or solutions provided by the Oracle database itself.
It mainly includes:
Shared Server mode (MTS)
Cluster Technology (RAC tering), RAC
Partition
Parallel Processing (mainly parallel query)

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

Before analysis, let's clarify our application features.
Database applications can be divided into OLAP and OLTP, that is, online transaction analysis (data warehouse) and online transaction processing (transaction application)
Our application system is mainly used for online transaction processing and contains a small amount of data warehouse features.

1. Shared Server (MTS)
Oracle uses the dedicated server mode by default, that is, a user connects to a process corresponding to a server process.
I remember that when a large hospital was launched, we tried MTS. I heard that MTS is connected to more clients without adding memory and CPU. The result is not what we expected.
Is there a problem with MTS? No, it is because we do not know MTS, but it is not used to do this in this case.

Generally, MTS is recommended only when the number of concurrent connections exceeds the support of the operating system. Otherwise, the default dedicated server mode should be used.
That is to say, in dedicated server mode, because one more connection consumes more than one operating system process, MTS is necessary only when the concurrent application demand exceeds the number of allowed connections of the operating system.
If the existing system supports 100 dedicated server databases physically, the Shared Server mode may support 1000 connections, but there may be only 100 active connections at the same time.
Generally, it is sufficient for two to four CPU servers to deal with 200 to 400 concurrent connections. If the connection increases, the CPU and memory can be increased.

MTS has the following Disadvantages:
1. Shared ServerCodeThe path is longer than the dedicated server, so it is inherently slower than the dedicated server.
2. There is a possibility of human deadlock, because it is serial, and all sharing servers are bound together (a process), as long as a connection is blocked, all users are blocked, and it is very likely to be deadlocked.
3. There is a possibility of exclusive transactions, because if a session's transaction runs for too long, it exclusively shares resources, and other users can only wait. (dedicated server, each client is a session)
4. The Shared Server Mode limits some database features, such:
The instance cannot be started or shut down independently, and media cannot be restored. Log miner cannot be used, and SQL _trace is meaningless (because it is shared rather than the current session ).

The memory reduced by MTS is actually the memory required by each user to connect to the operating system process in dedicated server mode. However, it uses the large_pool of SGA to allocate UGA and remove the east wall to complement the west wall, the reduced memory is very small.
If user sessions are frequently connected and disconnected, the overhead of database Process Creation and deletion will be very high. In this case, it is best to use the Shared Server mode (otherwise, the connection pool technology should be used ).
Fortunately, our product design may have taken this factor into consideration, using a connection for Life (within the session lifecycle) to avoid this situation.

Therefore, we recommend that you use the default dedicated server mode for our products. If the connection is insufficient, you can add hardware instead of using MTS.
In addition, Oracle supports both the Shared Server and dedicated server modes. You can specify a session to use a dedicated server, and another session to use a Shared Server.

2. Cluster Technology (RAC)
Oracle RAC (Real Application Clusters), we say that dual-host fault tolerance is a type of RAC.
The advantage of cluster technology lies in horizontal scalability and high availability.
32-bit operating systems have 4 GB memory limit, and some UNIX systems (and non-advanced versions of Windows) have a limit on the number of CPUs.
The cluster technology breaks this restriction horizontally by integrating multiple machines to work collaboratively.
Through RAC, one server and one instance, multiple machines constitute an instance service set, and the client connects to it.
This technology is sometimes called Server Load balancer for our customers. In fact, it is one-sided. RAC mainly targets Server Load balancer for CPU and memory,
Disk I/O load balancing is not implemented. (Of course, disk I/O can be achieved through raid or NAS)

Another advantage of RAC is that it improves availability, that is, a server is broken (Note: it is not a data storage medium) and does not affect normal use.
Like Server Load balancer, it improves the availability above the data layer, but not all, because the data is broken and there is no way for it.
(Data layer, that is Oracle daTa guard, or simply say it's about storage hardware)

However, RAC brings both benefits and performance impact.
Because it needs to globally coordinate the data cache to ensure that the cached data is consistent among the users connected to each instance, the following three conflicts are amplified:
1. High-speed cache contention
2. Excessive I/O
3. Lock
That is to say, if there is a problem in these aspects, the problem will be even greater after RAC is used. For example, because SQL does not use the binding variable, the high-speed cache contention will be more serious if RAC is used.
In short, if your server's CPU is fully occupied, the memory will also be added to the limit, and concurrent users will continue to grow, or you have high requirements for downtime, RAC is definitely your choice.

3. partitions
Oracle partitioning is used to divide large tables or indexes into small fragments for easier management.
We may mistakenly think that the partition is fast = true, which can improve the speed. We have also performed experiments in oncology and pediatrics.
In fact, in transaction processing systems, partitions generally do not speed up query (in some cases, it may reduce contention for shared resources ).
The partition feature of Oracle is mainly designed for Data Warehouses. That is to say, if your table has a size of GB, it is best to use partitions. The benefits include the following three aspects:
1. Improve availability
The principle of partitioning is to divide and conquer. If a table is divided into multiple partitions, the medium where one of the partitions is located has a problem and does not affect access to data in other partitions of the table.
2. Easy to manage
In a data warehouse, tables are divided into small fragments, making it easier to delete, fragment, and some parallel processing.
3. Improve Performance
In this regard, partitioning is the most difficult to achieve, and partition data must be arranged through careful calculation.

Partition planning is complex. For our product applications, queries generally involve multiple tables and Multiple indexes,
Let's assume that we have partitioned large tables such as patient expense records, drug sending and receiving records, and patient medical order records.
Obviously, range partitioning is of little use to improve our performance. Hash partitioning is what we need to query, but most data is not concentrated.
In addition, there are so many indexes on these tables, such as IDS and time indexes,
Few people can partition all of them globally or accurately by range (in fact, they may not be able to perform multiple index range partitions as needed ).
If a query involves multiple indexes, how can we ensure that each index is used in one partition? If not, we must scan multiple partitions to increase the logical I/O and CPU time, this increases the query time.
(Data is distributed across different physical storage media, which will be discussed in the following parallel processing)

Let's take a look. In some cases, the competition for shared resources may be reduced, which means that parallel modification and updates will be faster.
What is the principle of partitioning? Generally, the most commonly used partition by time period. In this way, most of the modifications and updates are performed on the same partition,
Therefore, there is almost no effect on reducing competition for shared resources.
(Is there a unique application requirement for partitioning by department ID? Is there a unique application requirement based on the List partition (typical feature value? Basically none .)

Partitioning improves performance from the perspective of parallelism, but the application characteristics of the transaction processing system determine that it is not suitable for this technology.
That is to say, there is no need to adopt partition technology for the features of transaction processing applications of our products.

4. Parallel Processing
Based on the features of our application, we mainly analyze parallel queries. Generally, we need to combine the partition features with multiple CPU hardware.
Since Oracle 8.1.6, an option is added to automatically adjust parallel queries: parallel_automatic_tuning = true
When the parallel parameter is set for the corresponding table, Oracle will automatically perform operations on the table at the appropriate time.

Parallel query is basically useless to the transaction processing system.
Because the parallel query design is designed for a single user in the data warehouse to completely consume 100 of the resources.
In transaction processing, there are often many concurrent users who want to share resources. Therefore, it is counterproductive to try to make a user occupy all resources.

The above is my understanding of some common solutions for Oracle performance, hoping to help you understand these topics correctly.