Introduction to Oracle optimization and Improvement

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 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 adjustments:

We should remember that Oracle does 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.

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.

Copy a Table using the Create Table As Select (CTAS) syntax of Oracle

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 using Order

SQL statement using Group

When creating an index

When performing table join, 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 initora ). 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 sorts in case of large sorting tasks, 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 initora. The size of each sort is determined by the sort_area_retained_size parameter in initora. 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.

Next, let's take a look at the competition in Oracle and how the table's storage parameter settings affect the performance of SQL UPDATE and INSERT statements.

Adjust Oracle Competition

One of Oracle's advantages is that it can manage free space in each tablespace. Oracle is responsible for table and index space management, so that we do not need to understand the internal operations of Oracle tables and indexes. However, for experienced Oracle tuning experts, he needs to understand how Oracle manages table extent and idle data blocks. This is very important for systems with high insert or update values.

To be proficient in object adjustment, you need to understand the behaviors of freelists and freelist groups. They are related to the values of pctfree and pctused parameters. This knowledge is especially important for the application of enterprise resource planning (ERP), because incorrect table settings in these applications are usually the cause of slow execution of DML statements.

For beginners, the most common error is that the default Oracle parameter is the best for all objects. Unless disk consumption is not a problem, when you set the pctfree and pctused parameters of the table, you must consider the average length of rows and the size of the database blocks, in this way, empty blocks are effectively put into freelists. When these settings are incorrect, the resulting freelists are also "dead" blocks, because they do not have enough space to store a row, which will lead to a significant processing delay.

Freelists is very important to effectively reuse the space in the Oracle tablespace. It is directly related to the settings of the pctfree and pctused storage parameters. By setting pctused to a high value, the database will re-use the block as soon as possible. However, high-performance and effective reuse of table blocks are opposite. When adjusting Oracle tables and indexes, you must carefully consider whether high performance or effective space reuse is required, and set table parameters accordingly. The following describes how freelists affect Oracle performance.

When a request needs to insert a row into the table, Oracle will go to freelist to find a block with enough space to accommodate a row. As you may know, the freelist string is placed in the first block of a table or index, which is also called the segment header ). The unique purpose of pctfree and pctused parameters is to control how a block is imported and exported in freelists. Although freelist link and unlink are simple Oracle functions, setting freelist link (pctused) and unlink (pctfree) does have an impact on Oracle performance.

According to the DBA's basic knowledge, the pctfree parameter controls freelist un-links (to remove a block from freelists ). Setting pctfree = 10 means that each block retains 10% of the space for Row Expansion. The pctused parameter controls freelist re-links. Setting pctused = 40 means that only when the block usage is lower than 40% will the data be returned to freelists in the table.

Many new users have some misunderstandings about how to process a block that is returned to freelists. In fact, once a block is re-added to freelist due to a delete operation, it will remain in freelist even if the space usage exceeds 60%, data blocks are moved from freelist only when pctfree is reached.

Summary of requirements for table and index storage parameter settings

The following rules are used to set storage parameters for freelists, freelist groups, pctfree, and pctused. You also know that the values of pctused and pctfree can be easily modified using the alter table command. A good DBA should know how to set the optimal values of these parameters.

There is a conflict between the effective use of space and high performance, and the table Storage parameters control this contradiction:

To effectively reuse the space, you can set a high pctused value, but the side effect is that additional I/O is required. A high pctused value means that all the relatively full blocks will be placed in freelist. Therefore, these blocks can accept only a few rows of records before they are full again, resulting in more I/O.

To achieve high performance, you can set pctused to a low value, which means that Oracle will not place data blocks in freelists until it is almost empty. The block can receive more rows before it is full, so it can reduce the I/O of the insert operation. Remember that the performance of new Oracle expansion blocks is higher than that of existing ones. For Oracle, scaling a table consumes less resources than managing freelists.

Let's review some common rules for setting object storage parameters:

Pctused is often set to receive a new line. It is useless for us to accept the free blocks of a row. If this is done, the performance of Oracle will be slowed down, because Oracle will attempt to read five "dead" free blocks before the extended table gets an empty block.

The appearance of chained rows in the table means that pctfree is too low or db_block_size is too small. In many cases, the RAW and long raw columns are huge and exceed the maximum block size of Oracle. In this case, chained rows cannot be avoided.

If a table has an SQL statement inserted at the same time, it must have a statement deleted at the same time. All idle blocks are put in a freelist, and no freelists contains any idle blocks.

The freelist parameter should be set to the maximum value updated at the same time in the table. For example, if at any time a table has a maximum of 20 Users performing the insert operation, the table parameter should be set to freelists = 20.

It should be noted that the value of the freelist groups parameter is only useful for Oracle Parallel Server and Real Application Clusters. For this type of Oracle, freelist groups should be set to the number of Oracle Parallel Server instances that access this table.

Oracle Database optimization is an important part of Oracle Database optimization and one of the ways to improve the performance of Oracle databases. I hope the content mentioned above will help you.