Four misconceptions about improving Oracle database performance

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 improvements, but we tend to overlook the analysis of our own application features and whether they are appropriate for us. Recently, through an in-depth understanding of this aspect of knowledge, we found that we had some mistaken understanding. I think it is necessary for us to change this misunderstanding together.

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 the dedicated server mode, which means that a user connection process corresponds to a server process. I remember when a big hospital just opened, we have tried MTS. Because MTS was told to connect more clients without increasing the memory and CPU, the result was 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.

In general, MTS is recommended only if the number of concurrent connections exceeds the support of the operating system, otherwise the default dedicated server mode should be used. That is, in dedicated server mode, because more than one connection consumes more than one operating system process, MTS is only necessary when concurrent application requirements exceed the allowable number of connections to the operating system.

If you have an existing system that physically supports a dedicated server database with 100 connections, instead of using Shared server mode, you may support 1000 connections, but there may be only 100 active connections. Generally 2 to 4 CPU servers, 200 to 400 concurrent connections is sufficient, if the connection increased, you can increase the CPU and memory.

MTS has some of the following drawbacks:

1, the shared server's code path is longer than the dedicated server, so it is inherently slower than dedicated server.

2, there is the possibility of human deadlock, because it is serial, all shared servers are bound together (a process), as long as a connection is blocked, then all users blocked, and most likely deadlock.

3, there is the possibility of exclusive transactions, because if a session of the transaction run too long, it exclusive sharing resources, other users can only wait. (and dedicated servers, each client is a session)

4. Shared server mode restricts certain database features, such as: Cannot start and close instances 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 UGA, robbing Peter, and reducing the memory is very small. If a user session is connected and disconnected very frequently, the cost of creating and deleting the database process is very high, preferably 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 lifetime of connectivity (within the session lifecycle) to avoid this situation.

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