Description of Oracle performance adjustment Solution

Oracle performance adjustment is a complex topic. The actual operations on Oracle performance adjustment can be exaggerated to write a book. 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. As long as some simple SQL optimization rules are used, the performance of SQL statements can be greatly improved, this is not surprising at all. -- Adjust Oracle sorting: Sorting has a great impact on Oracle performance.

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

Oracle performance adjustment secrets: 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--CPU resources make queries slow. When the query exceeds the CPU performance of the Oracle server, your database performance is limited by the CPU.

● Memory-the amount of memory that can be used for Oralce also affects SQL Performance, especially in data buffering and memory sorting.

● Network-massive Net8 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.

In a high-capacity online transaction processing environment, the data is obtained by a primary index, resort the rows of a table to make the order of consecutive blocks the same as their primary indexes. This way, you can reduce physical I/O and improve the response time in index-driven table queries.

This technique is only useful when the application selects multiple rows, or when the index range search is used and the application sends multiple queries to obtain consecutive keys. The access to a random and unique primary-key primary key will not benefit from reordering by rows.

Let's take a look at how it works. Consider the following SQL query, which uses an index to get 100 rows:

 
 

  
  
select  
  
  
salary  
  
  
from  
  
  
employee  
  
  
where  
  
  
last_name like 'B%';  
 
 

This query uses last_name_index to search for each row to obtain the target row. This query will use at least 100 physical disk reads, because the row of employee is stored in different data blocks.

The above content is an introduction to the secrets of Oracle performance adjustment. I hope you will get some benefits.