Oracle Data Optimization

In the past decade, Oracle has become one of the world's most professional databases. For IT experts, it is to ensure that Oracle
To improve the productivity of their company. 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:
DBAs 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.
Tuning rules can greatly improve the performance of SQL statements, which is not surprising at all.
-- 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, there will be more Oracle
Adjustments are not helpful.
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 CPU for the server.
You can also disable components that need to process 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 and reduce the Oracle SGA
Or shut down 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
The goal is to ensure that the database server has enough 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. Disk I/O is particularly powerful, because when Oracle
When a data block is obtained from a data file on the disk, the read process must wait for the physical I/O operation to complete. Disk operations are 10,000 times slower than data buffering. Therefore, if I/O can be
Minimize, or reduce the bottleneck caused by file competition on the disk, it can greatly improve the performance of the Oracle database.
If the system response is slow, there will be a rapid improvement by reducing disk I/O. If you search for the primary-key in a transaction in a certain range
To access the table by indexing, reorganizing the table using the CTAs method will be your primary strategy to reduce I/O. By physically sorting rows as and primary-Key
The same order of indexes can speed up data acquisition.
Just like the load balancing on the disk, the re-sorting of rows is also very simple and fast. You can use it with other DBA management skills in high I/O
The system greatly reduces the response time.
In a high-capacity online transaction processing environment (online transaction processing, OLTP ),
After the index is obtained, you can re-sort the rows of the table to make the order of the continuous blocks the same as their primary index, so that you can reduce the physical I/O in the index-driven Table query.
And improve the response time. 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. Random uniqueness
Primary-Key (primary key) access will not be 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 requires three disks.
I/O reads the data of all 100 employees (one index and two data blocks), reducing the number of block reads by 97.

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 matching degree between rows in a table and the index's sorting key.
The dba_indexes and dba_tables views are obtained.
In the dba_indexes view, view the clustering_factor column. If clustering_factor
The value is roughly the same as the number of blocks in the table, so the order of your table and index is the same. However, if clustering_factor
The value is close to the number of rows in the table, which indicates that 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, some basic rules are as follows:
Oracle DBAs must follow these rules to improve the performance of their systems. The goal of SQL optimization is simple:
. Eliminate unnecessary full table search for large tables: Unnecessary full table search results in a large number of unnecessary I/O operations, which slows down the performance of the entire database. Tuning experts will first evaluate the results based on the number of rows returned by the query.
SQL. 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 to replace the full table search. For unnecessary full table search, the most common optimization method is to increase the index. You can add standard B to the table.
Tree indexes can also be added with bitmap and function-based indexes. To determine whether to eliminate a full table search, you can carefully check the index search I/O
Overhead: full table search overhead. Their overhead is related to the reading of data blocks and possible parallel execution, and the two are compared. In some cases, you can forcibly use one
To achieve the index, you only need to add an index prompt in the SQL statement.
When full table search is the fastest way to access a table, the full table search for small tables is placed 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 later versions, small tables can be forced to be buffered in the keep pool.
To ensure optimal index usage: this is particularly important for improving the query speed. Sometimes oracle can select multiple indexes for query. tuning experts must check each index and ensure
Oracle uses the correct index. It also includes the use of Bitmap and function-based indexes.
. To ensure optimal Join Operation: some queries use nested loop join faster, some are hash join faster, and others are
Sort-merge join is 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. Here is a brief overview of the following:
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 you use create index, order
Or the group by statement, the Oracle database will automatically perform the sorting operation. 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),
Program global area (PGA ). If the connection is established through a multi-threaded server, the sorting space is 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) at the same time, for those tasks that do not require large sorting, there will be some waste. Of course, when the demand for sorting space exceeds sort_area_size
The disk is 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 occupied by each sort is determined
The sort_area_retained_size parameter in init. ora is determined. When sorting cannot be completed in the allocated space, the disk sorting method is used, that is
Temporary tablespace in 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
You must also allocate buffer pool blocks to maintain blocks in the temporary tablespace. No matter when the memory sorting is better than the disk sorting, the disk sorting will slow down the task and affect the Oracle
The execution of the current task of the 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
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 behavior of freelists and freelist groups, they and pctfree and pctused
Parameter value. 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 the disk consumption is not a problem
When the pctfree and pctused parameters are used, the average length of the row and the size of the database block must be taken into account so that the empty block can be 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 important to effectively reuse the space in the Oracle tablespace. It works with pctfree and pctused.
These two storage parameter settings are directly related. Pctused
Set it to a high value, and the database will re-use the block as soon as possible. However, high-performance and effective reuse of table blocks are opposite. In Oracle Adjustment
You must carefully consider whether high performance or effective space reuse is required, and set table parameters accordingly. Let's take a look at how these freelists are affected.
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. You may know,
The freelist string is placed in the first block of a table or index. This block is also called a segment header ). Pctfree and pctused
The unique purpose of the parameter is to control how the block is imported and exported in freelists. Although freelist link and unlink are simple Oracle
However, 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 ). Set
Pctfree = 10 means that each block retains 10% of the space for Row Expansion. The pctused parameter controls freelist re-links. Set
Pctused = 40 means that the freelists In the table will be returned only when the block usage is lower than 40%.
Many new users have some misunderstandings about how to process a block that is returned to freelists. In fact, once a block is readded to freelist due to a delete operation
It will remain in the freelist. Even if the space usage exceeds 60%, the data block will be removed from the freelist only when the pctfree space 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 know,
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 extra I/O. A high pctused
The value indicates that all the relatively full blocks are 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 Oracle will not place data blocks to 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 Oracle
The performance of new 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
Oracle's performance slows down because Oracle will attempt to read five "dead" Free blocks before an empty block is obtained from the extended table.
The appearance of chained rows in the table means that pctfree is too low or db_block_size is too small. In many cases, raw and
The long raw column is huge and exceeds the maximum block size of Oracle, so 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 parts will be placed in a freelist
.
The. freelist parameter should be set to the maximum value updated at the same time in the table. For example, if a table has a maximum of 20
Users perform 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 for Oracle Parallel Server and real
Application Clusters is useful. For this type of Oracle, freelist groups should be set to access
Number of Oracle Parallel Server instances.