Some key points in organizing data query optimization in Oracle database _oracle

The basic task of a database is to store and manage data, and the only database feature that an end-user can see is its performance: how quickly the database processes the results of a given query, and returns the results to the tools and applications used by the user. From the application examples of most systems, query operations occupy the largest proportion in various database operations, access to news, view files, query statistics and so on. Therefore, the efficiency of database query operation is the key factor that affects the response time of an application system. With the dynamic growth of data in an application system, the data is large, the efficiency of database query will be reduced, and the response speed of application system slows down, especially for the management and query of massive data, Oracle query optimization is especially important.

At present, there are many kinds of common database products, in which Oracle database supports large database, multi-user high performance transaction processing, support to industry standard, complete security and integrality control, support distributed database benefit distributed processing has portability, The advantages of compatibility and connectivity are much favored by users, widely used in Internet database platform, its spatial technology can manage geographical information more effectively and realize the storage and management of massive spatial information. Based on the experience of Oracle database application, this paper expounds the experience and method of Oracle query optimization in four aspects, such as hit ratio improvement, multiple table query optimization, large table query optimization and SQL optimization.

Oracle Query Optimization The first aspect: Oracle data query hit rate increase

The hit Ratio (hitratio) is the rate at which data is obtained directly from memory without obtaining data from the disk, i.e. the percentage of the data block that the query requested is already in memory. There are four factors affecting the hit Rate: Dictionary table activity, temporary segment activity, rollback segment activity, table scan, application DBA can analyze these four factors to find out the crux of low database hit rate.

1 Dictionary table activity

When an SQL statement arrives at the Oracle kernel for the first time, the database analyzes the SQL statement, and the data Dictionary object contained in the query is decomposed to produce the SQL execution path. If the SQL statement points to an object that is not in the SGA?? Tables or views, Oracle executes SQL statements to query information about objects in the data dictionary. The data block is read from the Data dictionary table to the SGA's data cache. Because each data dictionary is small, we can cache these tables to increase the hit rate on those tables. However, because the data dictionary data block in the SGA occupy space, when increasing the total hit rate, they will reduce the amount of space available in the table block, so if the query required time dictionary information is already in the SGA cache, then there is no need for recursive calls.

2. Activities in the temporary segment

When the user executes a query that needs to be sorted, Oracle manages to sort all the rows in the sorted area in memory, which is determined by the number of Init.ora files in the database. If the sorting area is not large enough, the database opens a temporary segment during the sort operation. Temporary segments artificially reduce the hit rate of the OLTP (online transaction processing) application, and also reduce the performance of the query sorting. If you can complete the sort operation in memory, you can eliminate the overhead of writing data to a temporary segment. Therefore, the sort_area_size should be set large enough to avoid the need for temporary segments. The specific adjustment method of this parameter is: querying the relevant data to determine the adjustment of this parameter.

SELECT * from V$sysstat where name= ' sorts (disk) ' or name= ' sorts (memory);

Most of the sorting is done in memory, but there is a small part in the temporary segment, you need to adjust the value, view the Sort_area_size value of the Init.ora file, the parameter is: sort_area_size=65536, adjust it to Sort_area_ size=131072, after this value is adjusted, restarting the Oracle database will take effect.

3) rolling back the activity of the segment

The rollback segment activity is divided into rollback activity and rollback segment header activity. Access to the header block of the rollback segment lowers the hit rate of the application and has the most impact on the OLTP system hit rate. To confirm that the hit ratio is affected by the rollback segment, you can view the statistics for the "block compatibility read-write rewrite record application" in the monitor output report, which is used to determine how many times the user accesses data from the rollback segment.

4) Table Scan

Blocks that are read through a large scan do not remain in the block cache for a long time, so table scans reduce the hit rate. In order to avoid unnecessary full table scan, the first is to build the index according to the need, reasonable index design to build people's analysis and prediction of various queries, the author will discuss in detail in SQL optimization; the second is to put frequently used tables in memory to reduce the number of disk reads and writes. For example, Alter table Your_table_name cathe.

Oracle Query Optimization The second aspect: Optimization of multi-table query

The database may take a mergejoins, NESTED LOOP, HASH JOIN when making a multiple-table federated query. In this case, the hash join is less expensive than the other two connections.

We can use hash joins instead of Mergejoins, NESTED Loop joins, so in applications, you can add settings that allow the database to use hash joins when there is a multiple-coalition query. The method is to 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 the modifications are completed, restart the database so that the parameter values take effect.

Oracle Query Optimization The third aspect: Large table query optimization

Some of the tables in the database are growing very fast, with a lot of logging, and the benefits of indexing are minimal when you access the tables, and there are usually two ways to optimize large table access.

1) The large table is established in the hash cluster

Create cluster Trade_cluster (Vuserid integer)

storage (initial 50M next 50M)

hash is vuserid

size Hashkeys 10000000;/*hashkeys Specifies the number of rows expected 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) to build the partition table

Separate a large table in several logical partitions or divide a large table into smaller tables, either by querying the small tables individually or by union all.

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 '));

Also, set up a few other tables that are divided by the quarterly transactions. Then create a view that performs the four-table union;

CREATE VIEW Trade_detail as SELECT * FROM Trade_detail_1

UNION ALL SELECT * to trade_detail_2

UNION ALL SELECT * From Trade_detail_3

UNION ALL SELECT * from Trade_detail_4;

This allows you to access only the small tables when querying for data in a certain period of time, and you can also conduct federated queries when needed.

Oracle Query Optimization Four aspects: SQL optimization

The execution of the application ultimately boils down to the execution of SQL statements in the database, which consumes 70% to 90% of the database resources. Therefore, the efficiency of the execution of the SQL statement ultimately determines the performance of the Oracle database. Many programmers think that query optimization is the task of DBMS (database management System), which is not related to the SQL statements written by programmers, which is wrong. A good query plan can often improve the performance of the program by dozens of times times. In addition, the SQL statement is independent of program design logic, compared to the optimization of program source code, the cost of optimizing SQL statement is very low in time cost and risk.

The main approaches to SQL optimization are:

A. Establishment of effective indexes. An index is established on a column that is frequently connected but not specified as a foreign key; Index on a column that is frequently sorted or grouped (that is, a group by or order by operation), a retrieval on a column that is often used in a conditional expression, and no index on a column with fewer values ; If there are multiple columns to be sorted, you can set up a composite index on those columns (compound index).

To reduce I/O contention, the index is built in an index space that is not on the same disk as the user's table space. Indexes are divided into: Partitioned index, full index, unique index, bitmap index and other types, before indexing, you should measure the selectivity of the index, index is the number of different values in the index column compared to the number of records in the table.

B. Consider setting up a clustered index if you have a large number of duplicate values and often have a range of queries (for example, between, "" =, "=") or a column with an order by or group by;

C. To frequent simultaneous access to multiple columns, each column contains duplicate values to consider the establishment of a composite index

D. Optimize expressions to use the range index as much as possible when using a range query, rather than "like", because wildcard matching supported by the LIKE keyword can be particularly time-consuming.

F. Use the Oracle Statement Optimizer (Oracle optimizer) and the row lock manager (Row-level Manager) to adjust the optimized SQL statements.