Oracle Expert Tuning Secrets

Objective

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.

--Adjusting Oracle sorting: Sorting has a significant impact on Oracle performance.

--Adjust 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.