The tuning rules that every Oracle DBA should follow

Oracle DatabaseOfTuning LawIt is a rule that DBAs must comply with when working. DBAs can complete Oracle Database optimization only when database optimization is restricted. 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. 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. Adjusting 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.

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.

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.

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 requires three times of disk I/O to read the data of all 100 employees at a time as an index and two times as a data block ), 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.

The table reorganization tool provided by Oracle9i.

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:

Eliminate unnecessary full-Table searches for large tables: Unnecessary full-Table searches lead to a large number of unnecessary I/O operations, slowing down the performance of the entire database. Tuning experts first evaluate the SQL statement based on the number of rows returned by the query. In an ordered table, if the query returns less than 40% rows, or in an unordered table, less than 7% rows are returned, then this query can be adjusted to use an index instead of the full table search. For unnecessary full table search, the most common optimization method is to increase the index. You can add standard B-tree indexes to a table, bitmap indexes, and function-based indexes. To determine whether to eliminate a full table search, you can carefully check the I/O overhead of index search and the overhead of full table search, their overhead is related to the reading of data blocks and the possible parallel execution, and the two are compared. In some cases, the elimination of unnecessary full table search can be achieved by forcing an index. You only need to add an index prompt to the SQL statement.

When full table search is the fastest way to access a table, you can store all table searches for small tables in the cache. tuning experts should ensure that there is a dedicated data buffer used as the row buffer. In Oracle7, you can use the alter table xxx cache statement. In Oracle8 or above, small tables can be forced to be buffered in the KEEP pool.

Optimal Index usage: this is especially important for improving the query speed. Sometimes Oracle can select multiple indexes for query. tuning experts must check each index and ensure that Oracle uses the correct index. It also includes the use of bitmap and function-based indexes.

Optimal JOIN Operation: some queries use nested loop join faster, some use HASH join faster, and some use sort-merge join faster.

These rules seem simple, but they account for 90% of SQL tuning tasks, and they do not need to fully understand the internal operations of Oracle SQL. The following is a brief overview of Oracle SQL optimization.

First, let's take a brief look at Oracle sorting and see how sorting affects performance.

Oracle sorting Adjustment

Sorting is a small aspect of SQL syntax, but it is very important that it is often ignored in Oracle adjustment. When the create index, order by, or group by statement is used, the Oracle database performs the sorting operation automatically. Oracle performs sorting in the following cases:

SQL statement that uses Order.

SQL statement that uses Group.

When performing table join during index creation, the SQL optimizer calls MERGE SORT due to the deficiency of existing indexes. When a session is created with Oracle, a private sorting area is allocated to the session in the memory. If the connection is a dedicated connection (dedicated connection), a Program Global Area (PGA) is allocated in the memory according to the sort_area_size parameter in init. ora ). If the connection is established through a multi-threaded server, the sorting space is allocated in large_pool. Unfortunately, for all sessions, the memory used for sorting must be the same. We cannot allocate additional sorting areas for operations requiring greater sorting. Therefore, the designer must make a balance and assign enough sorting areas to avoid disk sorting disk sorts when a large sorting task occurs, for tasks that do not need to be sorted in large order, there will be some waste. Of course, when the size of the sorting space exceeds the size of sort_area_size, the disk will be sorted by page in the TEMP tablespace. The disk sorting is about 14,000 times slower than the memory sorting.

As mentioned above, the size of the private sorting area is determined by the sort_area_size parameter in init. ora. The size of each sort is determined by the sort_area_retained_size parameter in init. ora. When sorting cannot be completed in the allocated space, the disk sorting method is used, that is, in the temporary tablespace of the Oracle instance.

There are several reasons for the overhead of disk sorting. First, they are particularly slow compared with memory sorting, and disk sorting will consume resources in the temporary tablespace. Oracle must also allocate buffer pool blocks to maintain blocks in the temporary tablespace. At any time, the memory sorting is better than the disk sorting, and the disk sorting will slow down the task and affect the execution of the current task of the Oracle instance. In addition, excessive disk sorting will increase the value of free buffer waits, so that data blocks of other tasks are moved from the buffer.

The Optimization Method of the Oracle database has summarized so much for everyone. I hope everyone can gain some gains from the content mentioned above, for more information about Oracle Database optimization, see the preceding section.