Oracle Expert Tuning Secrets Reproduced in http://huanyue.javaeye.com/blog/430624 ? ? In the past decade, Oracle has become the world&#39;s most professional databases. For IT specialists is to ensure that the use of Oracle&#39;s powerful features to improve their productivity. One of the most effective way 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. Tuning can be written on a whole book, however, in order to improve Oracle database performance, there are some basic concepts that every Oracle DBA should follow. In this profile, we will briefly introduce the Oracle the following topics: - External adjustment: we should remember that Oracle is not a separate operation. So we will look by adjusting the Oracle server to get high performance. - Row re-sequencing in order to reduce disk I / O: Oracle tuning we should know the most important goal is to reduce the I / O. - Oracle SQL adjustment. Oracle Oracle SQL adjustment is to adjust one of the most important areas, as long as the adoption of some simple rules of SQL tuning can dramatically improve the performance of SQL statements, this is not a little strange. - Adjust Oracle Sort: Sort performance for Oracle is very much affected. - Adjust Oracle&#39;s competition: table and index parameter settings for UPDATE and INSERT performance has a great influence. We first Oracle external environment from the beginning to adjust. If insufficient memory and CPU resources, then any adjustment will not help Oracle. External performance issues Oracle is not a separate operation. Oracle database performance and external environment have a great relationship. These external conditions include: . CPU - CPU resources, slow down enough to make inquiries. When the query over the Oracle server CPU performance, your database performance by the CPU on the restrictions. . Memory - the amount of memory available for Oralce will affect SQL performance, particularly in the data buffer and memory sequencing. . Network - a large number of Net8 communication so slow SQL performance. Many novice mistake that should first adjust the Oracle database, not the first to confirm the adequacy of external resources. In fact, if the external environment, bottlenecks, no matter how much Oracle adjustment is not helpful. Oracle&#39;s external environment in check, there are two aspects to note: 1, when the number of the run queue over the number of CPU servers, the server CPU performance would be restrictions. Remedy is for the server, additional CPU processing resources on or off requires a lot of components, such as Oracle Parallel Query. 2, memory paging. When paging, the memory capacity has been inadequate, but the memory page is on the disk swap and interact. Remedy is to add more memory, reduce the size of the Oracle SGA, or shut down Oracle&#39;s multi-threaded server. You can use a variety of standard server tools to get the server statistics, such as vmstat, glance, top, and sar. DBA&#39;s goal is to ensure that the database server has enough CPU and memory resources to handle Oracle&#39;s request. Let us look at Oracle&#39;s row-resequencing is how to greatly reduce the disk I / O&#39;s. Row-resequencing (line re-order) As we mentioned above, experienced Oracle DBA know I / O is the largest component of response time. One disk I / O special smart because when the Oracle from the disk in a data file by a data block, the reading process must wait for physical I / O operation is complete. Disk operation 10,000 times slower than the data buffer. Therefore, if you can make I / O to minimize or reduce the file because the disk bottleneck caused by competition, can greatly improve the performance of Oracle databases. If the system response is slow, by reducing the disk I / O can have a quick improvement. If a transaction search through a certain range of primary-key index to access the table, then re-organized table with CTAS method will you reduce the I / O&#39;s primary strategy. By order of the physical will be OK and the same primary-key index of the order, you can accelerate the speed of access to data. Like disk load balancing, as the line is very simple reordering, but also quickly. DBA with other management techniques used together, can in high I / O systems greatly reduce the response time. In the high-volume online transaction processing environment (online transaction processing, OLTP), data is received by a primary index, re-sort the table can make a continuous line of block order and as their primary index, so that you can in the index driven table queries, the reduction of physical I / O and improve response time. Choice in the application of this technique is only useful if multiple rows, or use the index range search and application to issue multiple queries to be effective when continuous key. Only for random primary-key (primary key) of the visit will not be reordered by the line to benefit. Let&#39;s look at how it works. Consider the following as a SQL query, it uses an index to get 100 lines: This query will use last_name_index, search for them to get the goal line in each row. This query will use at least 100 physical disk reads because the employee&#39;s line of data stored in different blocks. However, if the table rows have been reordered and last_name_index for the same, the same query will be handled? We can see that this query only needs three times the disk I / O to read all 100 employees of the data (once used as an index of reading, two for data block read), a decrease of 97 times the block read . Reorder the extent of performance improvements brought about is when you start the line of how out of order, and you need to access from the sequence number of rows. As a table row with the index matching degree of the sort key, you can view data dictionary views in dba_indexes and dba_tables be. In dba_indexes&#39;s view, see clustering_factor out. If clustering_factor the value and the number of blocks the table are similar, then your tables and indexes in the order is the same. However, if clustering_factor the value of the number of rows close to the table, it shows that the index in the table rows and the order is not the same. Row re-ordering can not underestimate the role. The need for large-scale search of the big table in the index, rows can be reordered so that the performance of query triple. Once you have decided to re-sort the table rows, you can use one of the following tools to re-organize the form. . Using Oracle&#39;s Create Table As Select (CTAS) to copy the table syntax . Oracle9i Form that comes with re-organization tools The following, we look at the following SQL statement tuning. SQL Tuning Oracle&#39;s SQL tuning is a complex subject, and even need the whole book to introduce Oracle SQL tuning nuances. But there are some basic rules that every Oracle DBA needs to follow, these rules can improve their system performance. SQL tuning goal is simple: . Eliminate unnecessary large table full table search: unnecessary full table search resulted in substantial unnecessary I / O, which slow down the performance of the entire database. Tuning expert first to the number of rows the query returns to evaluate the SQL. In an ordered table, if the query returns less than 40% of the line, or a disorder of the table, returned less than 7% of the line, then this query can be adjusted to use an index instead of full table search . Unnecessary search for a full table, the most common tuning is to increase the index. Standards can be added in the table B-tree index, you can also join the bitmap and function-based index. To decide whether to remove a full table search, you can double-check the index search I / O overhead and the cost of full table search their overhead and data blocks read and the possible parallel implementation, and compare the two. In some cases, some of the unnecessary search for the elimination of all forms can be forced to use an index to achieve, only to join in the SQL statement, an index of tips on it. . In the whole table search is one of the fastest access method, to search for a small table full-table into the cache, tuning specialists should ensure that there is a dedicated data buffer for line buffer. In Oracle7, you can use the alter table xxx cache statement in Oracle8 or more, small table can be forced into the KEEP buffer pool. . To ensure that the best index to use: to improve the speed of query, which is particularly important. Sometimes Oracle may choose to query multiple indexes, tuning experts must examine each index and ensure that Oracle use the correct index. It also includes a bitmap and function-based index of the use. . To ensure optimal JOIN operation: Some queries use NESTED LOOP join faster, some are HASH join faster, some are faster sort-merge join. These rules seem simple, but they account for SQL tuning task, 90%, and they do not fully understand the inner workings of Oracle SQL. Here we come to a simple overview of the following Oracle SQL optimization. We briefly see Oracle&#39;s ranking, and take a look at the sorting operation is how to influence performance. Adjust the operation of Oracle&#39;s Sorting Sorting is the SQL syntax in a small area, it is important to the adjustment in Oracle, it is often overlooked. When using the create index, ORDER BY or GROUP BY&#39;s statement, Oracle Database will automatically sort operation. Typically, in the case of Oracle will be the following sort of operation: Use Order by the SQL statement Group by using the SQL statement When you create an index For table join, because of the shortage caused by the existing index SQL optimizer called MERGE SORT When establishing a session with Oracle, the in memory will be allocated for the session sort of a private area. If the connection is a dedicated connection (dedicated connection), it will be according to the size of init.ora parameters in sort_area_size memory allocation in a Program Global Area (PGA). If the connection is established through multi-threaded server, then the sort of space is allocated in large_pool. Unfortunately, for all of the session, the amount of memory used to sort must be the same, we can not sort of operation that require a greater allocation of additional sorting area. Therefore, the designer must make a balance in the allocation of sufficient order to avoid a major regional sorting task, a disk sort (disk sorts), while those who do not require a great sort of task, there will be a number of waste . Of course, when the demand exceeds the sort of space the size of sort_area_size, when TEMP table space will be paged to disk sort. Sort than the sort of memory disk about 14,000 times slower. We have already mentioned above, the size of the private sort area is in the sort_area_size init.ora parameters determined. The size of the space occupied by each sort of sort_area_retained_size by the init.ora parameters. When the order can not be completed in the allocated space, they will use the disk sort of way, that Oracle instance in the temporary table space for. Disk sort of overhead is huge, there are several reasons. First of all, and sort of memory compared to their particularly slow; and disk sort consumes resources in the temporary table space. Oracle must also allocate buffer pool blocks to keep the temporary table space in the block. At any time, memory, disk sort order than the good, the disk sorting will cause a slow task, and the Oracle instance will affect the implementation of the current task. Also, excessive disk sorting will cause the value of free buffer waits becomes higher, so that other tasks of the data blocks are removed from the buffer. Then, let&#39;s look at Oracle&#39;s competition and look at setting the table storage parameters affect how the SQL UPDATE and INSERT statements of performance. Oracle&#39;s competition adjustment One of the advantages of Oracle when it can manage each table space in the free space. Oracle to deal with the space management tables and indexes, so that we do not know how Oracle can table and index&#39;s internal operations. However, for experienced Oracle tuning experts, he needs to know how to manage Oracle is the extent and the idle table data blocks. The adjustment has a high insert or update the system, it is very important. To adjust the master object, you need to understand the behavior of freelists and freelist groups, they and pctfree and pctused parameter related. Knowledge for enterprise resource planning (ERP) application is particularly important, because in these applications, the incorrect table setting is usually the reason behind the implementation of DML statement. For beginners, the most common error is that the default Oracle parameters for all the objects are the best. Unless the consumption of the disk is not a problem, or set the table pctfree and pctused parameters, we must consider the average of the president and the database block size, so empty block will be effectively put freelists. When these settings are not correct, those are the freelists is &quot;dead&quot; blocks, because they do not have enough space to store the line, this will result in significant processing delays. Freelists for the effective re-use the Oracle table space, the space is very important, it pctfree and pctused these two parameters to set directly related to the storage. By pctused set to a high value, then the database will be re-used blocks as quickly as possible. However, the high performance and efficient re-use of the table block is the opposite. Oracle forms and in adjusting the index, you need to seriously consider what you really need high performance or the effective spatial reuse, and accordingly to set the table parameters. Below we look at how these freelists affect the performance of Oracle. When there is a request to insert a line to the table when, Oracle will be to the freelist to find an adequate space to accommodate the line of block. You may know, freelist string on the table or index is the first block, the block is also known as the first segment (segment header). pctfree and pctused parameters for the sole purpose is to control how freelists in and out of blocks. Although the freelist link and unlink a simple Oracle functions, but set the freelist link (pctused) and unlink (pctfree) on the exact impact on Oracle performance. By the DBA to know the basics, pctfree parameters to control the freelist un-links the (block by the freelists be removed). Set pctfree = 10 means that 10% of each block of space reserved for row expansion. pctused parameters to control the freelist re-links the. Set pctused = 40 means that only the use of less than 40% of the block will return to form when the freelists. Many novice back for a block after the treatment freelists are some misunderstandings. In fact, once due to a delete operation and that block has been re-added to the freelist, it will always remain in the freelist even the use of space in more than 60%, only when reached will block pctfree are removed from the freelist . Table and index storage parameters of the requirements set summary Here are some rules are used to set freelists, freelist groups, pctfree and pctused storage parameters. You know, pctused and pctfree values can be easily modified through the alter table command, a good DBA should know how to set these parameters, the best value. Effective use of space and there is a contradiction between the high-performance, and the table storage parameters that control this aspect of the contradiction: . The need for effective re-use of space, you can set a high pctused value, but the side effects is the need for additional I / O. A high value means that a relatively full pctused will put the block in the freelist. Therefore, these blocks filled in again a few lines before the record can only be accepted, resulting in more I / O. . The pursuit of high performance, it can be pctused set to a low value, which means that Oracle will not put freelists in block until it is almost empty. Then the block will be received in full before the more rows, it can reduce the insertion of the I / O. Oracle extension to remember than the performance of the new block to re-use the existing blocks high. For Oracle, the expansion of a forms management freelists consume less than the resources. Let&#39;s look at an object storage parameters to set some common rules: . Often pctused set to receive a new line. For not accept his party&#39;s free blocks for us, is of no use. If so, will cause slow performance of Oracle, because Oracle will extend the table to get an empty block before attempting to read the five &quot;dead&quot; in the free block. . The emergence of chained rows in the table means that pctfree too low or too little db_block_size. In many cases, RAW and LONG RAW columns are great, even more than the maximum block size of Oracle, then chained rows is avoided. . If a table has also inserted the SQL statement, it needs also delete the statement. A clear run a single job will be to all of the free block into a freelist, while no other contain any free blocks freelists appear. . freelist parameter should be set to update the table while the maximum value. For example, if at any time, a table up to 20 users to perform insert operation, then the parameters of the table should be set to freelists = 20. Should be kept in mind that freelist groups parameter is only for Oracle Parallel Server and Real Application Clusters is useful. For these Oracle, freelist groups should be set to access the table the number of Oracle Parallel Server instance.