Four major ways to optimize Oracle queries

The most basic task of a database is to store and manage data. The only database that an end user can see is its performance: the speed at which the database processes the results of a specified query and returns the results to the tools and applications used by the user. 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. Www.2cto.com currently has many common database products. Oracle databases support large databases and multi-user high-performance transaction processing, support various industrial standards in the industry, and 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. The first aspect of Oracle query optimization: the increase in the tb hit rate of Oracle Data Query "HITRATIO" refers to the ratio of directly retrieving data from the memory instead of from the disk, that is, the percentage of times the requested data block has been in 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 www.2cto.com 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) Temporary segment activity when a user executes a query that requires sorting, Oracle tries to sort all the rows in the memory sorting area. The size of the sorting area is determined by the init of the database. determine the number of ora files. 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 of the sorting is performed in the memory, however, there are still a small part of the temporary segment. You need to adjust the value to view 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) The activity www.2cto.com of the rollback segment is divided into the rollback activity and the rollback segment header activity. 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) The data block cache does not last for a long time in the table scan. Therefore, 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. Author chen11-1