Oracle query performance optimization

The most basic task of a database is to store and manage data. The only database feature that an end user can see is its performance: the speed at which the database processes the results of a specified query, and return the result to the tools and applications used by the user.Program. From the perspective of Application Instances of most systems, query operations occupy the largest proportion in various database operations, view news, view files, and query statistics. Therefore, the efficiency of database query operations is a key factor affecting the response time of an application system. With the dynamic growth of data in an application system, the data size increases, the database query efficiency decreases, and the response speed of the application system slows down, especially for the management and query of massive data, Oracle query optimization is particularly important.

Currently, there are many common database products. Oracle databases support high-performance transaction processing for large databases and multiple users, support various industrial standards in the industry, and provide complete security and integrity control, the Distributed Database Service supports distributed processing with outstanding advantages such as portability, compatibility, and connectivity. It is widely used, as a leading Internet database platform, its spatial technology can manage geographical information more effectively and store and manage massive spatial information. Based on Oracle database application experience, this article describes Oracle query optimization experience and methods from four aspects, including hit rate improvement, multi-Table query optimization, large table query optimization, and SQL optimization.

Oracle query optimization: Improves the hit rate of Oracle Data Query 

"Hit rate (hitratio) refers to the ratio to retrieve data directly from the memory instead of from the disk, that is, the percentage of the number of times the data block of the query request has been in the memory ". There are four factors that affect the hit rate: dictionary table activity, temporary segment activity, rollback segment activity, and table scan. The DBA can analyze these four factors, find out the crux of the low hit rate of the database.

1) dictionary table Activity

When an SQL statement arrives at the Oracle kernel for the first time, the database analyzes the SQL statement. The data dictionary objects contained in the query are decomposed to generate the SQL Execution path. If the SQL statement points to an object not in SGA ?? For tables or views, Oracle executes SQL statements to query information about objects in the Data Book. Data blocks are read from the data dictionary table to the SGA data cache. Since each data dictionary is small, we can cache these tables to increase the hit rate of these tables. However, because data blocks in data dictionary tables occupy space in SGA, when the hit rate is increased to all, they will reduce the available space of table data blocks, therefore, if the time dictionary information required for the query is already in the SGA cache, there is no need for recursive calling.

2) Activities in the temporary segment

When you execute a query that requires sorting, Oracle tries to sort all rows in the memory sorting area. The size of the sorting area is determined by the number of init. ora files in the database. If the sorting area is not large enough, the database opens up a temporary segment during the sorting operation. The temporary segment will artificially reduce the hit rate of OLTP (online transaction processing) applications, and also reduce the query performance in sorting. If you can complete all sorting operations in the memory, you can eliminate the overhead of writing data to the temporary segment. Therefore, set sort_area_size to be large enough to avoid the need for temporary segments. The specific adjustment method of this parameter is: query the relevant data to determine the adjustment of this parameter. 
Select * from V $ sysstat where name = 'sorts (Disk) 'or name = 'sorts (memory );

Most sorting is performed in the memory, but a small part occurs in the temporary segment. You need to adjust the value and check the init. the sort_area_size value of the ora file. The parameter is sort_area_size = 65536. adjust it to sort_area_size = 131072. After this value is adjusted, restart the Oracle database to take effect.

3) rollback segments

Rollback segments are divided into rollback activities and rollback segment header activities. Access to the header block of the rollback segment reduces the application hit rate, which has the greatest impact on the OLTP system hit rate. To check whether the hit rate is affected by the rollback segment, you can view the statistical value of "data block compatibility read record-Heavy Application" in the monitoring output report, these statistical values are used to determine the number of times a user accesses data from a rollback segment.

4) Table Scan

Blocks read through a large scan are not kept in the data block cache for a long time, so table scan reduces the hit rate. To avoid unnecessary full table scans, you must first create an index as needed. A reasonable index design should be based on the analysis and prediction of various queries, I will discuss it in detail in SQL optimization. The second is to put frequently used tables in the memory to reduce the number of disk reads and writes. For example, alter table your_table_name cathe.

Oracle query optimization: Multi-Table query optimization

When performing multi-table join queries, the database may adopt mergejoins, nested loop, and hash join. In this example, the overhead of hash join is smaller than that of the other two types of join.

We can use Hash joins to replace mergejoins and nested loop joins. Therefore, you can add some settings in the application to enable hash joins when a database has many Union queries. The method is as follows: log on to the database server as an oracle user and add the following in the initosid. ora file:
Hash_join_enabled = true
Hasj_area_size = 26000
After modification, restart the database to make these parameter values take effect.

Oracle query optimization: large table Query Optimization

Some tables in the database grow very fast and have a large amount of records. when accessing such tables, the indexing benefits are minimal. Generally, two methods are used to optimize access to large tables.

1) create a large table in a hash cluster.

Create cluster trade_cluster (vuserid integer) 
Storage (initial 50 m next 50 m)
Hash is vuserid
Size 60 hashkeys 10000000;/* hashkeys specifies the expected number of rows in the hash table. */CREATE TABLE 
Trade_detail_new as select * From trade_detail Cluster 
Trade_cluster (userid );
Drop table trade_detail;
Rename trade_detail_new to trade_detail;

2) create a partition table

Place a large table separately in several logical partitions or divide a large table into several small tables. This means that you can query these small tables separately or query them together.

For example, split a table that records transaction details:
Create trade_detail_1 as select * From trade_detail
Where trade_time between to_date ('Mm-dd', '01-01 ') and to_date ('Mm-dd', '03-31 ');
Alter table trade_detail_1 add constraint check_trade_detail_1
Check (trade_time between to_date ('Mm-dd', '01-01 ') and to_date ('Mm-dd', '03-31 '));

Similarly, several other tables are created based on the quarter of the transaction. Then, create a view for executing four table joins;
Create view trade_detail as select * From trade_detail_1
Union all select * From trade_detail_2
Union all select * From trade_detail_3
Union all select * From trade_detail_4;

In this way, you can only access small tables when querying data within a certain period of time. You can also perform joint queries when necessary.

Oracle query optimization: SQL Optimization

The execution of the application is ultimately attributed to the execution of SQL statements in the database, which consumes 70% to 90% of database resources. Therefore, the execution efficiency of SQL statements determines the performance of the Oracle database. Many Programmers think that query optimization is a task of DBMS (Database Management System). It has little to do with the SQL statements compiled by the programmers. This is wrong. A good query plan can often increase the program performance by dozens of times. In addition, SQL statements are independent of the program design logic.Source codeOptimization of SQL statements at low cost of time and risk.

The main ways to optimize SQL are:

A. Create a valid index. Indexes are created on columns that are frequently connected but not specified as foreign keys. indexes are created on columns that are frequently sorted or grouped (that is, group by or order; you can create searches for columns with different values that are frequently used in conditional expressions. Do not create indexes for columns with fewer values. If there are multiple columns to be sorted, compound index can be created on these columns ).

To reduce the I/O competition, the index should be created in an index space that is not on the same disk as the user tablespace. Indexes can be divided into partition indexes, full indexes, unique indexes, bitmap indexes, and Other types. Before creating an index, you should measure the selectivity of this index, the index selectivity refers to the ratio of the number of different values in the index column to the number of records in the table.

B. columns with a large number of duplicate values and frequent range queries (such as between, >,<>=, <=) or columns using order by and group, you can consider creating a cluster index;

C. You must frequently access multiple columns at the same time, and each column contains duplicate values. You can consider creating a composite index.

D. Optimize the expression. Use the range index whenever possible when you can use the range query, instead of using the "like" keyword, because the wildcard matching supported by the "like" keyword is particularly time-consuming.

F. Use Oracle optimizer and row-level manager to adjust and optimize SQL statements.

 

ArticleFrom: http://database.51cto.com/art/201004/196516_1.htm