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 alone. So we will see a high performance by adjusting the Oracle server.
--Row Re-Sequencing to reduce disk I / O: We should know 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 very affected.
- Adjust 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:
SELECT
Salry
From
EMPLOYEE
WHERE
Last_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 Oracle's Create Table as SELECT (CTAS) syntax to copy table
Oracle9i comes with a table recommissible tool
Here, let's take the tuning of the following SQL statements.
