Oracle Tuning Overview __oracle

Over the past decade, Oracle has become one of the most professional databases in the world. For IT professionals, it is important to ensure that Oracle's powerful features are used to improve the productivity of their companies. One of the most effective ways to do this is through Oracle tuning. It has a large number of tuning parameters and techniques to improve the performance of your Oracle database.

Oracle tuning is a complex subject. You can write a whole book about tuning, but in order to improve the performance of Oracle databases, there are some basic concepts that every Oracle DBA should follow.

In this brief introduction, we will briefly describe the following Oracle topics:

External adjustment: We should remember that Oracle is not running alone. So we'll look at this by tuning the Oracle server to get high performance.

Row re-sequencing to reduce disk I/O: We should understand that the most important goal of Oracle tuning is to reduce I/O.

Oracle SQL tuning. Oracle SQL Tuning is one of the most important areas of Oracle tuning, and it's not surprising that you can dramatically improve the performance of SQL statements with a few simple SQL tuning rules.

Adjust Oracle sorting: Sorting also has a significant impact on Oracle performance.

Adjust Oracle's competition: Table and index parameter settings have a significant impact on the performance of UPDATE and INSERT.

We begin by adjusting the environment outside of Oracle. Any Oracle tuning is not helpful if the memory and CPU resources are low.

External Performance Issues

Oracle is not running alone. The performance of Oracle databases has a lot to do with external environments. These external conditions include the following:

． The lack of CPU--CPU resources slows down queries. When queries exceed the CPU performance of an Oracle server, your database performance is limited by the CPU.

． Memory-The amount of memory available for Oralce can also affect SQL performance, especially in data buffering and memory sequencing.

． Network--A large number of NET8 traffic slows down the performance of SQL.

Many beginners mistakenly believe that the Oracle database should be tuned first, rather than confirming that external resources are sufficient. In fact, any number of Oracle tweaks are not helpful if there is a bottleneck in the external environment.

When examining Oracle's external environment, there are two areas to be aware of:

1. When the number of running queues exceeds the number of CPUs in the server, the performance of the server is limited by the CPU. The remedy is to add extra CPUs to the server or to shut down components that require a lot of processing resources, such as Oracle Parallel Query.

2, memory paging. When memory is paged out, the memory capacity is not sufficient, and the memory page interacts with the swap area on the disk. The remedy is to add more memory, reduce the size of the Oracle SGA, or turn off Oracle's multithreaded servers.

You can use a variety of standard server tools to get statistics on your servers, such as Vmstat,glance,top and SAR. The goal of the DBA is to ensure that the database server has sufficient CPU and memory resources to handle Oracle requests.

Let's take a look at how Oracle's row-resequencing can dramatically reduce disk I/O.

row-resequencing (reordering of rows)

As we mentioned above, experienced Oracle DBAs know I/O is the biggest component of response time. Disk I/O is particularly powerful because when Oracle gets a block of data from a file on 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 can minimize I/O, or reduce bottlenecks caused by file competition on disk, the performance of Oracle databases can be greatly improved.

If the system responds very slowly, there can be a quick improvement by reducing disk I/O. If you are accessing a table in a transaction by searching the Primary-key index in a certain range, then the CTAS method of organizing the table will be your primary strategy for reducing I/O. You can speed up the acquisition of data by physically sorting the rows into the same order as the Primary-key index.

As with disk load balancing, the reordering of rows is simple and fast. By working with other DBA management techniques, you can dramatically reduce response time in highly I/O systems.

In a high-volume online transaction processing environment (on-line transaction processing, OLTP), data is obtained from a primary index, and reordering the rows of a table allows sequential blocks to be the same order as their primary index, This allows you to reduce physical I/O and improve response time in index-driven table queries. This technique works only when the application selects multiple rows, or when multiple queries are issued using the index range search and application to obtain a sequential key. Access to a random, unique Primary-key (primary key) will not benefit from row reordering.

Let's take a look at how it works. Consider one of the following SQL queries, which uses a lasso to get 100 rows:

Selectsalaryfromemployeewherelast_name like ' b% ';

This query will use Last_name_index to search each row to get the target row. This query will use at least 100 reads from the physical disk, because the rows of the employee are stored in different blocks of data.

However, if the rows in the table have been reordered as Last_name_index, how will the same query be handled? We can see that this query only requires three disk I/O to read all 100 employees (one read for the index at a time, two for the reading of the Block), and 97 less chunk reads.

The extent to which the reordering brings about performance is how well you start the row, and how many rows you need to access from the sequence. As to how the rows in a table match the index's sort key, you can view the dba_indexes and Dba_tables views in the data dictionary.

In the Dba_indexes view, view the Clustering_factor column. If the value of the clustering_factor is roughly the same as the number of blocks in the table, then the order of your table and index is the same. However, if the value of the Clustering_factor is close to the number of rows in the table, it indicates that the order of rows and indexes in the table is not the same.

The role of row reordering is not to be underestimated. In large tables that require a wide range of index searches, row reordering can increase the performance of the query by three times times.

Once you have decided to reorder the rows in the table, you can use one of the following tools to rearrange the tables.

. Copy a table using the Oracle Create table as Select (CTAS) syntax

. Oracle9i the table with the form to rearrange the tools

Below, let's look at the tuning of the following SQL statements

SQL Tuning

Oracle's SQL tuning is a complex topic, and even a whole book is needed to introduce the nuances of Oracle SQL tuning. But there are some basic rules that every Oracle DBA needs to follow, and these rules can improve the performance of their systems. The goal of SQL tuning is simple:

. Eliminate unnecessary large table full table searches: Unnecessary full-table searches result in a large amount of unnecessary I/O, slowing down the performance of the entire database. The tuning expert first evaluates SQL based on the number of rows returned by the query. In an ordered table, if the query returns less than 40% rows, or returns less than 7% rows in an unordered table, the query can be adjusted to use an index instead of a full table search. The most common tuning method for unnecessary full table searches is to increase the index. You can add a standard B-tree index to a table, or you can add bitmap and a function-based index. To decide whether to eliminate a full table search, you can carefully examine the I/O cost of the index search and the cost of the full table search, their cost and the reading of the block and possible parallel execution, and compare the two. In some cases, the elimination of unnecessary full table searches can be achieved by forcing the use of a single index, with the addition of an indexed hint in the SQL statement.

. When a full table search is one of the fastest access methods, place the entire table search of the small table in the cache, and the tuning expert should ensure that a dedicated data buffer is used as a row buffer. In Oracle7, you can use the ALTER TABLE XXX cache statement, in Oracle8 or above, where a small table can be forced to be buffered in a KEEP pool.

. Ensure optimal indexing use: This is particularly important for improving the speed of queries. Sometimes Oracle can select multiple indexes to query, and the tuning expert must check each index and make sure that Oracle uses the correct index. It also includes the use of bitmap and function based indexes.

. Ensure optimal Join operations: Some queries use NESTED LOOP JOIN faster, some are HASH join faster, others are sort-merge join faster.

These rules seem simple, but they account for 90% of the SQL Tuning task, and they do not need to fully understand the internal workings of Oracle SQL. Here's a quick overview of the following Oracle SQL optimizations.

Let's start with a brief look at Oracle's sort, and see how the sort operation affects performance.

to adjust Oracle sorting operations

Sorting is a small aspect of the SQL syntax, but it is important that, in Oracle's tuning, it is often overlooked. When you use a statement with CREATE INDEX, order by, or GROUP by, the Oracle database automatically performs the sort operation. In general, Oracle will sort the operations in the following situations:

SQL statement with ORDER BY

SQL statement using Group by

When you create an index

When a table join is made, the SQL optimizer calls the MERGE SORT because of insufficient existing indexes

When a session is established with Oracle, a private sort area is allocated in memory for the sessions. If the connection is a dedicated connection (dedicated connection), a program Global area (PGA) is allocated in memory based on the size of the Sort_area_size parameter in Init.ora. If the connection is built through a multithreaded server, the sorted space is allocated in the Large_pool. Unfortunately, for all sessions, the amount of memory used for sorting must be the same, and we can't assign additional sorting areas for operations that require a larger sort. As a result, the designer must strike a balance, and there will be some waste in tasks that do not require a lot of sorting, while allocating enough sorting areas to avoid disk sorting when large sort tasks occur (sorts). Of course, when the space requirement for the sort exceeds the size of the sort_area_size, the disk sort is paginated in the TEMP table space. The disk sort is about 14,000 times slower than the memory sort.

As we have mentioned above, the size of the private sort area is determined by the Sort_area_size parameter in the Init.ora. The size that each sort occupies is determined by the Sort_area_retained_size parameter in Init.ora. When sorting cannot be completed in the allocated space, disk sorting is used, which is done in the temporary tablespace in the Oracle instance.

The cost of disk sorting is large, for several reasons. First, they are particularly slow compared to the memory sort, and disk sorting consumes resources in the temporary tablespace. Oracle must also allocate buffer pool blocks to hold blocks in the temporary table space. Any time a memory sort is better than a disk sort, disk sorting slows the task down and affects the execution of the current task of the Oracle instance. Also, excessive disk sorting will make the value of free buffer waits higher so that the data blocks of other tasks are removed from the buffer.

Next, let's look at Oracle's competition and see how the settings for the table's storage parameters affect the performance of SQL UPDATE and INSERT statements.

aligning Oracle's competition

One of the benefits of Oracle is that it manages the free space in each table space. Oracle handles the space management of tables and indexes so that we don't have to understand the internal workings of Oracle's tables and indexes. However, for an experienced Oracle tuning expert, he needs to understand how Oracle manages the extent and free blocks of data for a table. This is important for systems that have a high insert or update to adjust.

To master the adjustment of objects, you need to understand the behavior of the freelists and freelist groups, which are related to the values of Pctfree and pctused parameters. This knowledge is particularly important for enterprise resource planning (ERP) applications, where incorrect table settings are often the reason for the slow execution of DML statements.

The most common mistake for beginners is to assume that the default Oracle parameters are best for all objects. Unless disk consumption is not a problem, when you set the table's Pctfree and pctused parameters, you must consider the average length of the president and the database, so that the empty blocks are effectively placed in the 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 can result in significant processing delays.

Freelists is important for the efficient reuse of space in an Oracle tablespace, and it is directly related to the Pctfree and pctused settings for these two storage parameters. By setting the pctused to a high value, the database will reuse the block as quickly as possible. However, the high performance and efficient reuse of the table blocks is antagonistic. When you adjust Oracle tables and indexes, you need to carefully consider the need for high performance or efficient space reuse, and set the table parameters accordingly. Here's a look at how these freelists affect Oracle's performance.

When a request needs to be inserted into a table, Oracle will go to the freelist to find a block that has enough space to hold a row. As you may know, the freelist string is placed in the first block of a table or index, and this block is also called the segment Header (segment header). The sole purpose of the Pctfree and pctused parameters is to control how blocks are in and out of the freelists. Although Freelist link and unlink are simple Oracle features, setting freelist link (pctused) and unlink (Pctfree) does have an impact on Oracle performance.

The basic knowledge of the DBA is that the Pctfree parameter controls Freelist Un-links (The block is removed from the freelists). Setting pctfree=10 means that each block retains 10% of the space used as a row extension. pctused parameters are controlled by Freelist Re-links. Setting pctused=40 means that the block is returned to the freelists of the table only if its use is less than 40%.

Many novices have misunderstood the handling of a block after returning to Freelists. In fact, once the block is added to the freelist as a result of a delete operation, it will remain in freelist even if the space is used more than 60%, the block is removed from the freelist only when the Pctfree is reached.

Summary of requirements for table and index storage parameter settings

Some of the following rules are used to set freelists, freelist groups, Pctfree, and pctused storage parameters. As you know, the values of pctused and Pctfree can easily be modified by the ALTER TABLE command, and a good DBA should know how to set the best values for these parameters.

There is a contradiction between the efficient use of space and high performance, and the table storage parameter is the control of this aspect of the conflict:

. For efficient reuse of space, you can set a high pctused value, although side effects require additional I/O. A high pctused value means that the relatively full block will be placed in the freelist. As a result, these blocks can only accept a few rows of records before they are full again, resulting in more I/O.

. In pursuit of high performance, you can set pctused to a low value, which means that Oracle does not place blocks of data into freelists until it is almost empty. The block will be able to receive more rows before it is full, thereby reducing I/O to the insert operation. Keep in mind that the Oracle extension new block is more performance-efficient than the existing blocks are reused. For Oracle, extending 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 no use for us to accept free blocks for a single line. Doing so will slow Oracle's performance because Oracle will attempt to read 5 "dead" free blocks before extending the table to an empty block.

． The presence of chained rows in the table means that Pctfree is too low or db_block_size too little. In many cases, raw and long raw columns are large enough to exceed the size of the largest Oracle block, when chained rows cannot be avoided.

． If a table has an SQL statement inserted at the same time, it needs to have a statement that is deleted at the same time. Running a single purge of the work will put all the free blocks in a freelist, and no other freelists that contains any free blocks appears.

． The freelist parameter should be set to the maximum value that the table updates at the same time. For example, if at any time a table has a maximum of 20 users performing an insert operation, the table's parameters should be set to freelists=20.

It should be remembered that the value of the freelist groups parameter is only useful for Oracle Parallel Server and real application clusters. For such Oracle, the freelist groups should be set to the number of Oracle Parallel Server instances that access the table.

SQL Tuning

Oracle's SQL tuning is a complex topic, and even a whole book is needed to introduce the nuances of Oracle SQL tuning. But there are some basic rules that every Oracle DBA needs to follow, and these rules can improve the performance of their systems. The goal of SQL tuning is simple:

. Eliminate unnecessary large table full table searches: Unnecessary full-table searches result in a large amount of unnecessary I/O, slowing down the performance of the entire database. The tuning expert first evaluates SQL based on the number of rows returned by the query. In an ordered table, if the query returns less than 40% rows, or returns less than 7% rows in an unordered table, the query can be adjusted to use an index instead of a full table search. The most common tuning method for unnecessary full table searches is to increase the index. You can add a standard B-tree index to a table, or you can add bitmap and a function-based index. To decide whether to eliminate a full table search, you can carefully examine the I/O cost of the index search and the cost of the full table search, their cost and the reading of the block and possible parallel execution, and compare the two. In some cases, the elimination of unnecessary full table searches can be achieved by forcing the use of a single index, with the addition of an indexed hint in the SQL statement.

. When a full table search is one of the fastest access methods, place the entire table search of the small table in the cache, and the tuning expert should ensure that a dedicated data buffer is used as a row buffer. In Oracle7, you can use the ALTER TABLE XXX cache statement, in Oracle8 or above, where a small table can be forced to be buffered in a KEEP pool.

. Ensure optimal indexing use: This is particularly important for improving the speed of queries. Sometimes Oracle can select multiple indexes to query, and the tuning expert must check each index and make sure that Oracle uses the correct index. It also includes the use of bitmap and function based indexes.

. Ensure optimal Join operations: Some queries use NESTED LOOP JOIN faster, some are HASH join faster, others are sort-merge join faster.

These rules seem simple, but they account for 90% of the SQL Tuning task, and they do not need to fully understand the internal workings of Oracle SQL. Here's a quick overview of the following Oracle SQL optimizations.

Let's start with a brief look at Oracle's sort, and see how the sort operation affects performance.

to adjust Oracle sorting operations

Sorting is a small aspect of the SQL syntax, but it is important that, in Oracle's tuning, it is often overlooked. When you use a statement with CREATE INDEX, order by, or GROUP by, the Oracle database automatically performs the sort operation. In general, Oracle will sort the operations in the following situations:

SQL statement with ORDER BY

SQL statement using Group by

When you create an index

When a table join is made, the SQL optimizer calls the MERGE SORT because of insufficient existing indexes

When a session is established with Oracle, a private sort area is allocated in memory for the sessions. If the connection is a dedicated connection (dedicated connection), a program Global area (PGA) is allocated in memory based on the size of the Sort_area_size parameter in Init.ora. If the connection is built through a multithreaded server, the sorted space is allocated in the Large_pool. Unfortunately, for all sessions, the amount of memory used for sorting must be the same, and we can't assign additional sorting areas for operations that require a larger sort. As a result, the designer must strike a balance, and there will be some waste in tasks that do not require a lot of sorting, while allocating enough sorting areas to avoid disk sorting when large sort tasks occur (sorts). Of course, when the space requirement for the sort exceeds the size of the sort_area_size, the disk sort is paginated in the TEMP table space. The disk sort is about 14,000 times slower than the memory sort.

As we have mentioned above, the size of the private sort area is determined by the Sort_area_size parameter in the Init.ora. The size that each sort occupies is determined by the Sort_area_retained_size parameter in Init.ora. When sorting cannot be completed in the allocated space, disk sorting is used, which is done in the temporary tablespace in the Oracle instance.

The cost of disk sorting is large, for several reasons. First, they are particularly slow compared to the memory sort, and disk sorting consumes resources in the temporary tablespace. Oracle must also allocate buffer pool blocks to hold blocks in the temporary table space. Any time a memory sort is better than a disk sort, disk sorting slows the task down and affects the execution of the current task of the Oracle instance. Also, excessive disk sorting will make the value of free buffer waits higher so that the data blocks of other tasks are removed from the buffer.

Next, let's look at Oracle's competition and see how the settings for the table's storage parameters affect the performance of SQL UPDATE and INSERT statements. aligning Oracle's competition

One of the benefits of Oracle is that it manages the free space in each table space. Oracle handles the space management of tables and indexes so that we don't have to understand the internal workings of Oracle's tables and indexes. However, for an experienced Oracle tuning expert, he needs to understand how Oracle manages the extent and free blocks of data for a table. This is important for systems that have a high insert or update to adjust.

To master the adjustment of objects, you need to understand the behavior of the freelists and freelist groups, which are related to the values of Pctfree and pctused parameters. This knowledge is particularly important for enterprise resource planning (ERP) applications, where incorrect table settings are often the reason for the slow execution of DML statements.

The most common mistake for beginners is to assume that the default Oracle parameters are best for all objects. Unless disk consumption is not a problem, when you set the table's Pctfree and pctused parameters, you must consider the average length of the president and the database, so that the empty blocks are effectively placed in the 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 can result in significant processing delays.

Freelists is important for the efficient reuse of space in an Oracle tablespace, and it is directly related to the Pctfree and pctused settings for these two storage parameters. By setting the pctused to a high value, the database will reuse the block as quickly as possible. However, the high performance and efficient reuse of the table blocks is antagonistic. When you adjust Oracle tables and indexes, you need to carefully consider the need for high performance or efficient space reuse, and set the table parameters accordingly. Here's a look at how these freelists affect Oracle's performance.

When a request needs to be inserted into a table, Oracle will go to the freelist to find a block that has enough space to hold a row. As you may know, the freelist string is placed in the first block of a table or index, and this block is also called the segment Header (segment header). The sole purpose of the Pctfree and pctused parameters is to control how blocks are in and out of the freelists. Although Freelist link and unlink are simple Oracle features, setting freelist link (pctused) and unlink (Pctfree) does have an impact on Oracle performance.

The basic knowledge of the DBA is that the Pctfree parameter controls Freelist Un-links (The block is removed from the freelists). Setting pctfree=10 means that each block retains 10% of the space used as a row extension. pctused parameters are controlled by Freelist Re-links. Setting pctused=40 means that the block is returned to the freelists of the table only if its use is less than 40%.

Many novices have misunderstood the handling of a block after returning to Freelists. In fact, once the block is added to the freelist as a result of a delete operation, it will remain in freelist even if the space is used more than 60%, the block is removed from the freelist only when the Pctfree is reached.

Summary of requirements for table and index storage parameter settings

Some of the following rules are used to set freelists, freelist groups, Pctfree, and pctused storage parameters. As you know, the values of pctused and Pctfree can easily be modified by the ALTER TABLE command, and a good DBA should know how to set the best values for these parameters.

There is a contradiction between the efficient use of space and high performance, and the table storage parameter is the control of this aspect of the conflict:

. For efficient reuse of space, you can set a high pctused value, although side effects require additional I/O. A high pctused value means that the relatively full block will be placed in the freelist. As a result, these blocks can only accept a few rows of records before they are full again, resulting in more I/O.

. In pursuit of high performance, you can set pctused to a low value, which means that Oracle does not place blocks of data into freelists until it is almost empty. The block will be able to receive more rows before it is full, thereby reducing I/O to the insert operation. Keep in mind that the Oracle extension new block is more performance-efficient than the existing blocks are reused. For Oracle, extending 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 no use for us to accept free blocks for a single line. Doing so will slow Oracle's performance because Oracle will attempt to read 5 "dead" free blocks before extending the table to an empty block.

． The presence of chained rows in the table means that Pctfree is too low or db_block_size too little. In many cases, raw and long raw columns are large enough to exceed the size of the largest Oracle block, when chained rows cannot be avoided.

． If a table has an SQL statement inserted at the same time, it needs to have a statement that is deleted at the same time. Running a single purge of the work will put all the free blocks in a freelist, and no other freelists that contains any free blocks appears.

． The freelist parameter should be set to the maximum value that the table updates at the same time. For example, if at any time a table has a maximum of 20 users performing an insert operation, the table's parameters should be set to freelists=20.

It should be remembered that the value of the freelist groups parameter is only useful for Oracle Parallel Server and real application clusters. For such Oracle, the freelist groups should be set to the number of Oracle Parallel Server instances that access the table.