Relationship between Oracle adjustment and SQL statement Optimization

In Oracle adjustment, we will also involve SQL statement optimization. Next we will look at the optimization of the following SQL statements. We all know that SQL Optimization in Oracle is a complicated topic, and even requires a whole book to introduce the nuances of Oracle SQL optimization.

However, there are some basic rules that every Oracle DBA must follow. These rules can improve the performance of their systems. The goal of SQL optimization is simple:

Eliminate unnecessary full-Table searches for large tables: Unnecessary full-Table searches lead to a large number of unnecessary I/O operations, slowing down the performance of the entire database. Tuning experts first evaluate the SQL statement based on the number of rows returned by the query. In an ordered table, if the query returns less than 40% rows, or in an unordered table, less than 7% rows are returned, therefore, this query can be adjusted from Oracle to an index instead of full table search.

For unnecessary full table search, the most common optimization method is to increase the index. You can add standard B-tree indexes to a table, bitmap indexes, and function-based indexes. To determine whether to eliminate a full table search, you can carefully check the I/O overhead of index search and the overhead of full table search, their overhead is related to the reading of data blocks and the possible parallel execution, and the two are compared.

In some cases, the elimination of unnecessary full table search can be achieved by forcing an index. You only need to add an index prompt to the SQL statement.

When full table search is the fastest way to access a table, you can store all table searches for small tables in the cache. tuning experts should ensure that there is a dedicated data buffer used as the row buffer. In Oracle7, you can use the alter table xxx cache statement. In Oracle8 or above, small tables can be forced to be buffered in the KEEP pool.

Optimal Index usage: this is especially important for improving the query speed. Sometimes Oracle can select multiple indexes for query. tuning experts must check each index and ensure that Oracle uses the correct index. It also includes the use of bitmap and function-based indexes.

Optimal JOIN Operation: some queries use nested loop join faster, some use HASH join faster, and some use sort-merge join faster.

These rules seem simple, but they account for 90% of SQL tuning tasks, and they do not need to fully understand the internal operations of Oracle SQL. The following is a brief overview of Oracle SQL optimization.

First, let's take a brief look at Oracle sorting and see how sorting affects performance.

Oracle sorting Adjustment

Sorting is a small aspect of SQL syntax, but it is very important that it is often ignored in Oracle adjustment. When the create index, order by, or group by statement is used, the Oracle database performs the sorting operation automatically. Oracle performs sorting in the following cases:

SQL statement using Order

SQL statement using Group

When creating an index

When performing table join, the SQL optimizer calls MERGE SORT due to the deficiency of existing indexes.

When a session is created with Oracle, a private sorting area is allocated to the session in the memory. If the connection is a dedicated connection (dedicated connection), a Program Global Area (PGA) is allocated in the memory according to the sort_area_size parameter in init. ora ). If the connection is established through a multi-threaded server, the sorting space is allocated in large_pool.

Unfortunately, for all sessions, the memory used for sorting must be the same. We cannot allocate additional sorting areas for operations requiring greater sorting. Therefore, the designer must make a balance and assign enough sorting areas to avoid disk sorting disk sorts when a large sorting task occurs, for tasks that do not need to be sorted in large order, there will be some waste.

Of course, when the size of the sorting space exceeds the size of sort_area_size, the disk will be sorted by page in the TEMP tablespace. The disk sorting is about 14,000 times slower than the memory sorting.

As mentioned above, the size of the private sorting area is determined by the sort_area_size parameter in init. ora. The size of each sort is determined by the sort_area_retained_size parameter in init. ora. When sorting cannot be completed in the allocated space, the disk sorting method is used, that is, in the temporary tablespace of the Oracle instance.

There are several reasons for the overhead of disk sorting. First, they are particularly slow compared with memory sorting, and disk sorting will consume resources in the temporary tablespace. Oracle must also allocate buffer pool blocks to maintain blocks in the temporary tablespace. At any time, the memory sorting is better than the disk sorting, and the disk sorting will slow down the task and affect the execution of the current task of the Oracle instance.

In addition, excessive disk sorting will increase the value of free buffer waits, so that data blocks of other tasks are moved from the buffer.

Next, let's take a look at the competition in Oracle and how the table's storage parameter settings affect the performance of SQL UPDATE and INSERT statements.

Article by: http://database.csdn.net/page/96f66c75-55bb-42a7-9bdf-e402705c85e5