Oracle expert tuning secret

In the past decade,OracleIt has become one of the most professional databases in the world. For IT experts, IT is to ensure that their company's productivity is improved by leveraging the powerful features of Oracle. One of the most effective methods isOracle Optimization. It has a lot of parameters and technologies to improve the performance of your Oracle database. The following describes how to optimize Oracle experts.

Oracle tuning is a complex topic. You can write a whole book on optimization. However, to improve the performance of Oracle databases, there are some basic concepts that every Oracle DBA should follow.

In this introduction, we will briefly introduce the following Oracle topics:

External adjustment: we should remember that Oracle is not run independently. Therefore, we will look at how to adjust the Oracle server to achieve high performance.

Row re-sequencing to reduce disk I/O: we should understand that the most important goal of Oracle optimization is to reduce I/O.

Oracle SQL adjustment. Oracle SQL adjustment is one of the most important areas of Oracle adjustment. It is not surprising that some simple SQL optimization rules can greatly improve the performance of SQL statements.

Adjusting Oracle sorting: Sorting has a great impact on Oracle performance.

Adjust Oracle competition: the table and index parameter settings have a great impact on the performance of UPDATE and INSERT.

First, we start by adjusting the external environment of Oracle. If the memory and CPU resources are insufficient, any Oracle adjustment is not helpful.

External performance problems

Oracle does not run independently. The performance of the Oracle database is closely related to the external environment. These external conditions include:

Insufficient CPU resources make queries slow. When the query exceeds the CPU performance of the Oracle server, your database performance is limited by the CPU.

The memory size that can be used for Oralce may also affect SQL performance, especially in data buffering and memory sorting.

Massive Net8 network communication slows down SQL Performance.

Many new users mistakenly think that the Oracle database should be adjusted first, rather than checking whether external resources are sufficient. In fact, if there is a bottleneck in the external environment, more Oracle adjustments will not help.

When checking the external environment of Oracle, you need to pay attention to the following two aspects:

1. When the number of running queues exceeds the number of CPUs of the server, the performance of the server will be limited by the CPU. The remedy is to add additional CPUs to the server or disable components that need to process many resources, such as Oracle Parallel Query.

2. Memory paging. When the memory is paged, the memory capacity is insufficient, and the memory page interacts with the swap zone on the disk. The remedy is to increase more memory, reduce the size of the Oracle SGA, or disable the multi-thread server of Oracle.

Various standard server tools can be used to obtain server statistics, such as vmstat, glance, top, and sar. DBA's goal is to ensure that the database server has sufficient CPU and memory resources to process Oracle requests.

Let's take a look at how Oracle's row-resequencing can greatly reduce disk I/O.

Row-resequencing)

As we mentioned above, experienced Oracle DBAs Know That I/O is the largest component of response time. Among them, disk I/O is very powerful, because when Oracle gets a data block from a data file on the disk, the read process must wait for the completion of physical I/O operations. Disk operations are 10,000 times slower than data buffering. Therefore, if I/O can be minimized, or the bottleneck caused by file competition on the disk can be reduced, the performance of the Oracle database can be greatly improved.

If the system response is slow, there will be a rapid improvement by reducing disk I/O. If a transaction accesses the table by searching for the primary-key index in a certain range, reorganizing the table using the CTAS method will be your primary strategy for reducing I/O. By physically sorting rows in the same order as the primary-key index, you can speed up data acquisition.

Just like the load balancing on the disk, the re-sorting of rows is also very simple and fast. With other DBA management skills, you can greatly reduce the response time in a high I/O system.