Foreword
Over the past decade, Oracle has become one of the most professional databases in the world. For IT experts, it is to ensure that the powerful feature of Oracle is used to improve their company's productivity. One of the most effective methods is through Oracle tuning. It has a lot of adjustment parameters and technologies to improve your performance of your Oracle database.
Oracle Tuning is a complex topic. About tuning can write a whole book, however, in order to improve the performance of the Oracle database, some basic concepts are that every Oracle DBA should follow.
In this profile, we will briefly introduce the following Oracle topics:
-
External adjustment: We should remember that Oracle is not running separately. So we will see a high performance by adjusting the Oracle server.
-
Row Re-Sequencing to reduce disk I / O: We should understand that Oracle Tuning is the most important goal to reduce I / O.
-
Oracle SQL Adjustment. Oracle SQL Adjustment is one of the most important fields in Oracle adjustment, as long as some simple SQL tuning rules can greatly enhance the performance of SQL statements, this is not surprising.
-
Adjust Oracle Sort: Sorting For Oracle performance is also affected.
-
Adjusting Oracle's competition: The parameter settings of the tables and indexes have a big impact on the performance of Update and Insert.
We first start from adjusting the outside of Oracle. If the memory and CPU have insufficient resources, no Oracle adjustment is unheeditive.
External performance problem
Oracle is not running separately. The performance of the Oracle database and the external environment have a big relationship. These external conditions include:
. CPU - The insufficient query of CPU resources is 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 available for Oralce can also affect the performance of SQL, especially in data buffer and memory sorting.
. Network - a large number of Net8 communication makes SQL performance slows down.
Many newcomers are wrong thinking should first adjust the Oracle database, rather than first confirm that external resources are sufficient. In fact, if the bottleneck in the external environment, more Oracle adjustments are not helpful.
There are two aspects that need attention when checking Oracle's external environment:
1. When the number of run queues exceeds the number of CPUs of the server, the performance of the server is limited by the CPU. The remedial method is to add additional CPUs to the server or closes components that require many processing resources, such as Oracle Parallel Query.
2, memory paging. When the memory pagin is paged, the memory capacity is insufficient, and the memory page is interacting with the switching area on the disk. The remedial method is to increase more memory, reduce the size of Oracle SGA, or turn off the multi-threaded server of Oracle.
Various standard server tools can be used to get statistics of the server, such as VMSTAT, Glance, Top, and SAR. The DBA goal is to ensure that the database server has sufficient CPU and memory resources to process Oracle's request.
Let's take a look at how oracle's Row-Resequencing can greatly reduce disk I / O.
Row-Resequencing (reorder line)
Just like the above mentioned, experienced Oracle DBA knows that I / O is the maximum component of the response time. Among them, disk I / O is particularly powerful, because when Oracle gets a data file by a data file on the disk, the read process must wait for physical I / O operation. Disk operation is 10,000 times slower than the data. Therefore, if I / O can minimize I / O, or reduce the bottleneck brought by the file competition on the disk, the performance of the Oracle database can be greatly improved.
If the system responds very slow, there is a very fast improvement by reducing disk I / O. If you search for a PRIMARY-KEY index in a transaction to access the table, re-re-CTAS's methodological organizational table will be the primary policy you reduce I / O. The speed of obtaining data can be accelerated by physically sorting the line as the primary-key index. Like the load balance of the disk, the reordering of the line is also very simple, and it is also very fast. By working with other DBA management skills, the time can be greatly reduced in the high I / O system.
In the high-capacity online transaction processing environment (ONLINE TRANSACESSING, OLTP), the data is obtained by a primary index, the row of reordering tables can make the order of the continuous blocks, like their primary index, which can be indexed During the driver table query, reduce physical I / O and improve response time. This technique is only useful when applying multiple lines, or if you use an index range search and app to issue multiple queries to get continuous Key. Accessing to a random unique primary-key (primary key) will not be benefited from row reorder.
Let's take a look at how it works. Consider the following SQL query, it uses an index to get 100 lines:
SelectSalaryFromeMPloyewhereLast_name Like 'b%';
This query will use last_name_index to search each of the rows to get the target row. This query will use at least 100 physical disks because Employee's row is stored in different data blocks. However, if the rows in the table have been replaced as the same as LAST_NAME_INDEX, what will the same inquiry? We can see that this query only needs three disk I / O to read the data of 100 employees (one read for indexing, two readings used as data blocks), reduce 97 block reads . The degree of performance improvement brought by reordering is how many lines of rows in the time of you, and how many rows you need to access from the sequence. As for the degree of matching of rows and indexes in a table, you can view DBA_INDEXES and DBA_TABLES views in the data dictionary. In the view of DBA_Indexes, view the Clustering_Factor column. If the value of Clustering_Factor and 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, it indicates that the order in the table and index in the table are different. The role of reordering is not to be understood. In a large number of large-scale index search, rows reordering can increase the performance of the query three times. Once you have decided to rearrange the row in the table, you can use one of the following tools to reorganize the form. . Use the Oracle's Create Table As SELECT (CTAS) syntax to copy the table. Oracle9i comes with the table to reform tools, let's see the tuning of the following SQL statements.
SQL Tuning Oracle's SQL Tuning is a complex topic, or even a whole book to introduce the fine difference of Oracle SQL tuning. However, some basic rules are that every Oracle DBA needs to be followed, and these rules can improve their system performance. The goal of SQL tuning is simple:. Eliminate unnecessary big table full table search: Unnecessary full table search results in a lot of unnecessary I / O, thereby saving slowing down the performance of the entire database. Tuning experts first evaluate SQL according to the number of rows returned by the query. In an ordered table, if the query returns less than 40% of rows, or in a disorderly table, return less than 7% of rows, then this query can be adjusted to use a claim to search for full table search. . For unnecessary full table searches, the most common tuning method is to increase the index. You can add a standard B tree index in the table, or you can join BitMap and function-based indexes. To decide whether to eliminate a full table search, you can carefully check the I / O overhead of the index search and the overhead of the full table search, and their overhead and data block reads and possible parallel execution are related to comparison. In some cases, some unnecessary full table search cancellation can be reached by forcing an index, just adding a suggestion of an index in the SQL statement. When the full-Test Search is a fastest access method, put the full table search of the small table in the cache, and the tuning expert should ensure a special data buffer work buffer. In Oracle7, you can use the ALTER TABLE XXX Cache statement, in Oracle8 or more, the small table can be enforced to put it in the KEEP pool. Ensure optimal indexing: This is especially important for improving the query. Sometimes Oracle can select multiple indexes to check for queries, and TME experts must check each index and ensure that Oracle uses the correct index. It also includes Bitmap and the use of functions based on functions. Make sure the best Join action: Some queries use nested loop Join fast, some is Hash Join! Other Sort-Merge Join faster. These rules look simple, but they account for 90% of SQL tuning tasks, and they do not need to fully understand the internal operation of Oracle SQL. Let's take a brief overview Optimization of Oracle SQL. We first briefly view Oracle's sorting and see how the sort operation affects performance. Adjusting Oracle Sort Operation Sort is a small aspect in the SQL syntax, but it is important that in Oracle's adjustment, it often ignores. When using Create Index, ORDER BY or Group By, the Oracle database will automatically perform sorted operations. Typically, Oracle is sorted in the following cases: When using the SQL statement of the Order By, use the Group By SQL statement to perform Table Join when creating an index, causes the SQL optimizer to call Merge Sort due to the shortcomings of existing indexes. When a session is established with Oracle, a private sort area is assigned to the session in memory. If the connection is a dedicated connection, a Program Global Area (PGA) is allocated in memory according to the size of the sort_AREA_SIZE parameter in INIT.ORA. If the connection is established by multi-threaded servers, the sorted space is assigned in Large_Pool. Unfortunately, for all sessions, it must be the same, we can't assign an additional sorting area for more sorted operations.
Therefore, the designer must make a balance, while disk sorts are disk sorts, while disk sorts are disk sorting, there will be some waste on those who do not need a large sorting task. . Of course, when the sorted space needs exceeds the size of sort_area_size, the disk sort will be made in the TEMP tablespace. Disk sorting is about 14,000 times more slower than memory. Above we have mentioned that 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 by the sort_AREA_RETAINED_SIZE parameter in INIT.ORA. When the sort cannot be completed in the assigned space, the way the disk sorted, that is, in the temporary table space in the Oracle instance. The overhead of disk sorting is large, there are several reasons. First, compared with memory sorting, they are particularly slow; and disk sorting will consume resources in temporary table spaces. Oracle must also assign buffer pool blocks to keep blocks in the temporary table space. Whenever, memory sorting is better than the disk, the disk sort will make the task slower and affect the current task of the Oracle instance. Also, too much disk sort will make the value of Free Buffer Waits to be high, so that the data blocks of other tasks are removed from the buffer. Next, let's take a look at Oracle's competition and see how the settings of the store's storage parameters affect the performance of SQL Update and Insert statements. Adjusting one of the advantages of Oracle's competition Oracle that it can manage free space in each table space. Oracle is responsible for handling space management of the table and index, so we can make us do not need to understand the internal operation of Oracle's tables and indexes. However, for experienced Oracle tuning experts, he needs to know how Oracle is how to manage the extent and idle data blocks. This is very important for the system that adjusts high INSERT or UPDATE. To be proficient in the adjustment, you need to understand the behavior of the Freelists and FreeList groups, they are related to the value of the PCTFree and PCTUSED parameters. These knowledge is particularly important for the application of the Enterprise Resource Program (ERP), because in these applications, incorrect table settings are usually the reasons for the DML statement to perform slow. For beginners, the most common mistake is that the default Oracle parameter is best for all objects. Unless the disk's consumption is not a problem, otherwise the PCTFREE and PCTUSED parameters of the table must consider the block size of the database, which empty blocks are effectively placed in FreeElists. When these settings are incorrect, those get Freelists are also "dead" blocks because they do not have enough space to store a row, which will result in significant processing delay. Freelists is important to effectively reuse spaces in Oracle tablespace, which is directly related to the settings of the two storage parameters of PCTFree and PCTUSED. The database will reuse the blocks as soon as possible by setting the PCTUSED to a high value. However, the blocks of high performance and effectively reuse tables are opposite. When adjusting Oracle's forms and indexes, you need to carefully consider the need for high performance or valid space reuse, and therefore set the parameters of the table. Let's take a look at how these FreeELists affect the performance of Oracle. When there is a request to insert a row into the table, Oracle will find a block with enough space to accommodate a line in FreeList. You may know that the Freelist string is placed in the first block of the form or index, and this block is also called a segment header.
The unique purpose of the PCTFree and PCTUSED parameters is to control how the blocks are available in Freelists. Although FreeElist Link and Unlink are simple Oracle features, it is true that Freelist Link (Pctused) and UnLink (PCTFREE) do have indeed effect on Oracle. The basic knowledge of DBA knows that the PCTFree parameter is to control freelist un-links (the block is removed from FreELISTS). Setting PCTFree = 10 means that each block retains 10% of space for row expansion. The PCTUSED parameter is to control freeelist re-links. Setting PCTUSED = 40 means that only when the block is used below 40%, it will return to Freelists in the table. Many newcomers have some misunderstandings for the process after returning to FreElists. In fact, once a deleted operation, the block is rejoined into the freeelist, it will always be retained even if the space is used in FreeList, which is only dried by the data block by Freelist when the PCTFree is reached. . The requirements for tables and index storage parameters settings summarize that some of the following rules are used to set FreELists, FreeList Groups, PCTFREE, and PCTUSED storage parameters. You also know that the value of PCTUSED and PCTFREE can be easily modified through the alter table command, a good DBA should know how to set the best value for these parameters. Effective use between space and high performance is contradictory, and the store's storage parameters are contradictions in this area:. For the need to effectively reuse space, you can set a high PCTUSED value, but side effects need additional i / O. A high PCTUSED value means that relatively full blocks are placed in FreeElist. Therefore, these blocks can only be accepted by only one line of records before it is full, resulting in more I / O. Pursue high performance, you can set the PCTUSED to a low value, which means that Oracle does not put the data block in Freeelists until it is almost empty. Then the block will receive more rows before full, so I / O can be reduced. Remember that the performance of the Oracle extension new block is higher than the rendering of existing blocks. For Oracle, extending an expression than managing Freelists consume fewer resources. Let us review some common rules that set the object storage parameters:. Pctused is often set to receive a new row. Free blocks that cannot accept a row is useless for us. If this is made, the performance of Oracle will slow down because Oracle will try to read 5 "dead" Free block before the extension table gets an empty block. . The appearance of Chained Rows in the form means that PCTFREE is too low or too small to db_block_size. In many cases, the Raw and Long Raw columns are huge, so that the size of the largest block of Oracle is exceeded. At this time, CHAINED ROWS is not avoidable. . If a table has a SQL statement inserted simultaneously, it needs to have a statement that deletes simultaneously. Work running a single clear will put all free blocks in a FreeElist without other FreeLists that contain any empty blocks. . The Freelist parameter should be set to the maximum update of the table. For example, if at any time, a table has up to 20 users perform inserted operations, then the parameters of the table should be set to freeLists = 20. What should be remembered is that the value of the Freelist Groups is only useful for Oracle Parallel Server and Real Application Clusters.
