SQL Tuning Oracle's SQL Tuning is a complex topic, or even a whole book to introduce the fine difference of Oracle SQL tuning. However, some basic rules are that every Oracle DBA needs to be followed, and these rules can improve their system performance. The goal of SQL tuning is simple:. Eliminate unnecessary big table full table search: Unnecessary full table search results in a lot of unnecessary I / O, thereby saving slowing down the performance of the entire database. Tuning experts first evaluate SQL according to the number of rows returned by the query. In an ordered table, if the query returns less than 40% of rows, or in a disorderly table, return less than 7% of rows, then this query can be adjusted to use a claim to search for full table search. . For unnecessary full table searches, the most common tuning method is to increase the index. You can add a standard B tree index in the table, or you can join BitMap and function-based indexes. To decide whether to eliminate a full table search, you can carefully check the I / O overhead of the index search and the overhead of the full table search, and their overhead and data block reads and possible parallel execution are related to comparison. In some cases, some unnecessary full table search cancellation can be reached by forcing an index, just adding a suggestion of an index in the SQL statement. When the full table search is a fastest access method, put the full table of the small table to the university 饔 ω 繁 繁 鲎 鲎 诺 氖 莼 莼 鲎 鲎 鲎 鲎 鲎 餍 鲎 鲎T 贠 RACLE7, you can use the Alter Table XXX Cache statement, in Oracle8 or more, the small table can be enforced into a KEEP pool buffer. Ensure optimal indexing: This is especially important for improving the query. Sometimes Oracle can select multiple indexes to check for queries, and TME experts must check each index and ensure that Oracle uses the correct index. It also includes Bitmap and the use of functions based on functions. Make sure the best Join action: Some queries use nested loop Join fast, some is Hash Join! Other Sort-Merge Join faster. These rules look simple, but they account for 90% of SQL tuning tasks, and they do not need to fully understand the internal operation of Oracle SQL. Let's take a brief overview Optimization of Oracle SQL. We first briefly view Oracle's sorting and see how the sort operation affects performance. Adjusting Oracle Sort Operation Sort is a small aspect in the SQL syntax, but it is important that in Oracle's adjustment, it often ignores. When using Create Index, ORDER BY or Group By, the Oracle database will automatically perform sorted operations. Typically, Oracle is sorted in the following cases: When using the SQL statement of the Order By, use the Group By SQL statement to perform Table Join when creating an index, causes the SQL optimizer to call Merge Sort due to the shortcomings of existing indexes. When a session is established with Oracle, a private sort area is assigned to the session in memory. If the connection is a dedicated connection, a Program Global Area (PGA) is allocated in memory according to the size of the sort_AREA_SIZE parameter in INIT.ORA. If the connection is established by multi-threaded servers, the sorted space is assigned in Large_Pool. Unfortunately, for all sessions, it must be the same, we can't assign an additional sorting area for more sorted operations.
