Oracle optimization (entry and improvement)

In the past decade, Oracle has become one of the world's most professional databases. 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 is to use Oracle optimization. It has a lot of parameters and technologies to improve the performance of your Oracle database. Oracle optimization is one

In the past decade, Oracle has become one of the world's most professional databases. 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 is to use Oracle optimization. It has a lot of parameters and technologies to improve the performance of your Oracle database. Oracle optimization is one

In the past decade, Oracle has become one of the world's most professional databases. 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 is to use Oracle optimization. It has a lot of parameters and technologies to improve the performance of your Oracle database.

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.

-- Adjust 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--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-a large number of Net8 communications make SQL Performance slow.

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 (online transaction processing, OLTP), 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 random and unique primary-key (primary key) access 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:

Selectsalaryfromemployeewherelast_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.

However, if the rows in the table have been reordered to be the same as last_name_index, what will happen to the same query? We can see that this query only needs three times of disk I/O to read the data of all 100 employees (one index read and two data block reads ), 97 block reads are reduced.

The performance improvement brought about by re-sorting lies in the disordered nature of the line at the beginning, and the number of rows that you need to access from the sequence. You can view the dba_indexes and dba_tables views in the data dictionary to obtain the matching degree between the rows and the index sort keys in a table.

In the dba_indexes view, view the clustering_factor column. If the value of clustering_factor is roughly the same as the number of blocks in the table, the order of your table and index is the same. However, if the value of clustering_factor is close to the number of rows in the table, the order of rows and indexes in the table is different.

The function of row re-sorting cannot be underestimated. In a large table that requires a wide range of index searches, resorting of rows can increase the query performance by three times.

Once you have decided to re-sort the rows in the table, you can use one of the following tools to re-organize the table.

. Use the Create Table As Select (CTAS) syntax of Oracle to copy tables.

. Oracle9i built-in table reorganization Tool

Below, we will look at the optimization of the following SQL statements.

SQL Optimization

Oracle SQL optimization is a complex topic, and even requires the entire book to introduce the nuances of Oracle SQL optimization. However, there are some basic rules that every Oracle DBA must follow. These rules can improve the performance of their systems. The goal of SQL optimization is simple: