Oracle Expert Tuning Secrets (II.)

Oracle SQL Tuning

Oracle's SQL tuning is a complex topic, and even a whole book is needed to introduce the nuances of Oracle SQL tuning. But there are some basic rules that every Oracle DBA needs to follow, and these rules can improve the performance of their systems. The goal of SQL tuning is simple:

. Eliminate unnecessary large table full table searches: Unnecessary full-table searches result in a large amount of unnecessary I/O, slowing down the performance of the entire database. The tuning expert first evaluates SQL based on the number of rows returned by the query. In an ordered table, if the query returns less than 40% rows, or returns less than 7% rows in an unordered table, the query can be adjusted to use an index instead of a full table search. The most common tuning method for unnecessary full table searches is to increase the index. You can add a standard B-tree index to a table, or you can add bitmap and a function-based index. To decide whether to eliminate a full table search, you can carefully examine the I/O cost of the index search and the cost of the full table search, their cost and the reading of the block and possible parallel execution, and compare the two. In some cases, the elimination of unnecessary full table searches can be achieved by forcing the use of a single index, with the addition of an indexed hint in the SQL statement.

. When a full table search is one of the fastest access methods, put the full table search of the small table to defend the school  饔 throw  to the  of the "Omega" and the  Ulva to shake the Kuimo, Missie, I shake Sui yun, you can use ALTER TABLE XXX cache statement, in Racle7 or above, A small table can be forced to be buffered in a keep pool.

. Ensure optimal indexing use: This is particularly important for improving the speed of queries. Sometimes Oracle can select multiple indexes to query, and the tuning expert must check each index and make sure that Oracle uses the correct index. It also includes the use of bitmap and function based indexes.

. Ensure optimal Join operations: Some queries use nested LOOP join faster, some are hash join faster, others are sort-merge join faster.

These rules seem simple, but they account for 90% of the SQL Tuning task, and they do not need to fully understand the internal workings of Oracle SQL. Here's a quick overview of the following Oracle SQL optimizations.

Let's start with a brief look at Oracle's sort, and see how the sort operation affects performance.

To adjust Oracle sorting operations

Sorting is a small aspect of the SQL syntax, but it is important that, in Oracle's tuning, it is often overlooked. When you use a statement with CREATE INDEX, order by, or group by, the Oracle database automatically performs the sort operation. In general, Oracle will sort the operations in the following situations:

SQL statement with ORDER BY

SQL statement using GROUP BY

When you create an index

When a table join is made, the SQL optimizer calls the merge SORT because of insufficient existing indexes

When a session is established with Oracle, a private sort area is allocated in memory for the sessions. If the connection is a dedicated connection (dedicated connection), a program Global area (PGA) is allocated in memory based on the size of the Sort_area_size parameter in Init.ora. If the connection is built through a multithreaded server, the sorted space is allocated in the Large_pool. Unfortunately, for all sessions, the amount of memory used for sorting must be the same, and we can't assign additional sorting areas for operations that require a larger sort. As a result, the designer must strike a balance, and there will be some waste in tasks that do not require a lot of sorting, while allocating enough sorting areas to avoid disk sorting when large sort tasks occur (sorts). Of course, when the space requirement for the sort exceeds the size of the sort_area_size, the disk sort is paginated in the temp table space. The disk sort is about 14,000 times slower than the memory sort.

As we have mentioned above, the size of the private sort area is determined by the Sort_area_size parameter in the Init.ora. The size that each sort occupies is determined by the Sort_area_retained_size parameter in Init.ora. When sorting cannot be completed in the allocated space, disk sorting is used, which is done in the temporary tablespace in the Oracle instance.

The cost of disk sorting is large, for several reasons. First, they are particularly slow compared to the memory sort, and disk sorting consumes resources in the temporary tablespace. Oracle must also allocate buffer pool blocks to hold blocks in the temporary table space. Any time a memory sort is better than a disk sort, disk sorting slows the task down and affects the execution of the current task of the Oracle instance. Also, excessive disk sorting will make the value of free buffer waits higher so that the data blocks of other tasks are removed from the buffer.

Next, let's look at Oracle's competition and see how the settings for the table's storage parameters affect the performance of SQL UPDATE and INSERT statements.