Five aspects of Oracle optimization

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.

Rowre-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 simple SQL tuning rules.

Adjust Oracle sorting: Sorting also 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.