Over the past decade, Oracle has become one of the most professional databases in the world. For IT professionals, it is important to ensure that Oracle's powerful features are used to improve the productivity of their companies. One of the most effective ways to do this is through Oracle tuning. It has a large number of tuning parameters and techniques to improve the performance of your Oracle database.
Oracle tuning is a complex subject. You can write a whole book about tuning, but in order to improve the performance of Oracle databases, there are some basic concepts that every Oracle DBA should follow.
In this brief introduction, we will briefly describe the following Oracle topics:
--
External Adjustment: We should remember that Oracle is not running alone. So we'll look at this by tuning the Oracle server to get high performance.
--
Row re-sequencing to reduce disk I/O: We should understand that the most important goal of Oracle tuning is to reduce I/O.
--
Oracle SQL Tuning。 Oracle SQL Tuning is one of the most important areas of Oracle tuning, and it's not surprising that you can dramatically improve the performance of SQL statements with a few simple SQL tuning rules.
--
Adjust Oracle Sorting: Sorting has a significant impact on Oracle performance as well.
--
aligning Oracle's competition: Table and index parameter settings have a significant impact on the performance of update and insert.
We begin by adjusting the environment outside of Oracle. Any Oracle tuning is not helpful if the memory and CPU resources are low.
External Performance Issues
Oracle is not running alone. The performance of Oracle databases has a lot to do with external environments. These external conditions include the following:
． The lack of CPU--CPU resources slows down queries. When queries exceed the CPU performance of an Oracle server, your database performance is limited by the CPU.
． Memory-The amount of memory available for ORALCE can also affect SQL performance, especially in data buffering and memory sequencing.
． Network--A large number of NET8 traffic slows down the performance of SQL.
Many beginners mistakenly believe that the Oracle database should be tuned first, rather than confirming that external resources are sufficient. In fact, any number of Oracle tweaks are not helpful if there is a bottleneck in the external environment.
When examining Oracle's external environment, there are two areas to be aware of:
1. When the number of running queues exceeds the number of CPUs in the server, the performance of the server is limited by the CPU. The remedy is to add extra CPUs to the server or to shut down components that require a lot of processing resources, such as Oracle Parallel Query.
2, memory paging. When memory is paged out, the memory capacity is not sufficient, and the memory page interacts with the swap area on the disk. The remedy is to add more memory, reduce the size of the Oracle SGA, or turn off Oracle's multithreaded servers.
You can use a variety of standard server tools to get statistics on your servers, such as Vmstat,glance,top and SAR. The goal of the DBA is to ensure that the database server has sufficient CPU and memory resources to handle Oracle requests.
Let's take a look at how Oracle's row-resequencing can dramatically reduce disk I/O.
row-resequencing (reordering of rows)
As we mentioned above, experienced Oracle DBAs know I/O is the biggest component of response time. Disk I/O is particularly powerful because when Oracle gets a block of data from a file on disk, the read process must wait for the physical I/O operation to complete. Disk operations are 10,000 times slower than data buffering. Therefore, if I can minimize I/O, or reduce bottlenecks caused by file competition on disk, the performance of Oracle databases can be greatly improved.
If the system responds very slowly, there can be a quick improvement by reducing disk I/O. If you are accessing a table in a transaction by searching the Primary-key index in a certain range, then the CTAs method of organizing the table will be your primary strategy for reducing I/O. You can speed up the acquisition of data by physically sorting the rows into the same order as the Primary-key index.
As with disk load balancing, the reordering of rows is simple and fast. By working with other DBA management techniques, you can dramatically reduce response time in highly I/O systems.
In a high-volume online transaction processing environment (on-line transaction PROCESSING,OLTP), the data is obtained by a primary index, and reordering the rows of the table can make the sequence of contiguous blocks the same as their primary index, so that you can Drive table query, reduce physical I/O and improve response time. This technique works only when the application selects multiple rows, or when multiple queries are issued using the index range search and application to obtain a sequential key. Access to a random, unique Primary-key (primary key) will not benefit from row reordering.
Let's take a look at how it works. Consider one of the following SQL queries, which uses a lasso to get 100 rows:
Select
Salary
From
Employee
where
Last_Name like ' b% '; |
This query will use Last_name_index to search each row to get the target row. This query will use at least 100 reads from the physical disk, because the rows of the employee are stored in different blocks of data.
However, if the rows in the table have been reordered as Last_name_index, how will the same query be handled? We can see that this query only requires three disk I/O to read all 100 employees (one read for the index at a time, two for the reading of the Block), and 97 less chunk reads.
The extent to which the reordering brings about performance is how well you start the row, and how many rows you need to access from the sequence. As to how the rows in a table match the index's sort key, you can view the dba_indexes and Dba_tables views in the data dictionary.
In the Dba_indexes view, view the Clustering_factor column. If the value of the clustering_factor is roughly the same as the number of blocks in the table, then the order of your table and index is the same. However, if the value of the Clustering_factor is close to the number of rows in the table, it indicates that the order of rows and indexes in the table is not the same.
The role of row reordering is not to be underestimated. In large tables that require a wide range of index searches, row reordering can increase the performance of the query by three times times.
Once you have decided to reorder the rows in the table, you can use one of the following tools to rearrange the tables.
. Copy a table using the Oracle CREATE TABLE as Select (CTAS) syntax
. Oracle9i the table with the form to rearrange the tools
Below, let's look at the tuning of the following SQL statement.
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, place the entire table search of the small table in the cache, and the tuning expert should ensure that a dedicated data buffer is used as a row buffer. In Oracle7, you can use the ALTER TABLE XXX cache statement, in Oracle8 or above, where 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.
