Overview of Oracle Optimization

3. How to Find the session of Oracle with extremely large resources and the statement executed by the node. Hp-unix can use glance, topIBMAIX can use topas, and ps

3. How to Find the session of Oracle with extremely large resources and the statement executed by the Oracle node. Hp-unix can use glance, and topIBM AIX can use topas and ps.

In many cases, when we were Oracle DBAs, we were notified by our administrators that they were using very slowly and databases very slowly, when we go to the database, we do a few examples of Select and find the same problem, sometimes we will not be able to start, because we think that the various life rates of the database are satisfied with the Oracle Documentation Initiative. in fact, the current optimization has been transformed to the Optimization wait (waits). In reality, the most basic presentation points of performance optimization are also concentrated on IO, which is the most important aspect that affects performance, waiting in the system to discover the deficiencies in the Oracle database and the unreasonable utilization of some resources in the operating system is a good method. Let's share some of my practical experience with you, this document focuses on Unix environments.

1. review the status of the system through some operating system tools, such as CPU, memory, communication, and disk application rate. Based on the lessons learned or compared with the normal status of the system, sometimes the system looks idle in name. This may not be a malformed State, because the cpu may be expecting IO implementation. in addition, we should also look at the processes that occupy system resources (cpu, memory.

1. How can I check whether there is an I/O problem in the operating system? A common tool is sar.

Rp1 # sar-u 2 10

That is to say, the system performs the inspection once every two seconds for a total of 20 times. Of course, these are all decided by you.

Sample Return:

HP-UX hpn2 B .11.00 U 9000/800 08/05/03

18:26:32% usr % sys % wi/o % idle

Note: This is the result I checked in RedHat. I do not know that % system is the so-called % wio.

Linux 2.4.21-20. ELsmp (YY075) 05/19/2005

10:36:07 am cpu % user % nice % system % idle

10:36:09 AM all 0.00 0.00 0.13 99.87

10:36:11 AM all 0.00 0.00 0.00 100.00

10:36:13 AM all 0.25 0.00 0.25 99.49

10:36:15 AM all 0.13 0.00 0.13 99.75

10:36:17 AM all 0.00 0.00 0.00 100.00

10:36:17 am cpu % user % nice % system % idle

10:36:19 AM all 0.00 0.00 0.00 100.00

10:36:21 AM all 0.00 0.00 0.00 100.00

10:36:23 AM all 0.00 0.00 0.00 100.00

10:36:25 AM all 0.00 0.00 0.00 100.00

% Usr indicates the percentage of cpu resources used by user processes, % sys indicates the percentage of cpu resources used by system resources, and % wio indicates the percentage of cpu resources waiting for io completion, this is worth noting. % idle is the percentage of idle space. if the value of the wio column is large, for example, more than 35%, it indicates that there is a bottleneck in your system's IO, and your CPU has spent a lot of time waiting for the completion of IO. if the Idle is small, the CPU usage is very busy. as shown in my example, if the uniform wio value is 11, I/O is normal, and my idle value is zero, it indicates that my cpu is running at full capacity.

When your system has an I/O problem, you can solve it in the following ways:

* Contact the corresponding operating system skills to support optimization, such as hp-ux striping when specifying the volume group.

* Search for SQL statements with different processing principles in Oracle and optimize them.

* In addition to properly creating indexes for tables with frequent accesses to Oracle, you can store these tables into tablespaces to avoid hot spot visits. In addition, you can partition the tables in a fair manner.

2. Pay attention to the memory.

The common tool is vmstat, which can be glance for hp-unix and topas for Aix. When you find that the pi column in vmstat is non-zero, the value of free column in memory is very small, and glance, when the memory usage in topas is more than 80%, it indicates that you should adjust the memory. There are several methods as follows.

* The Memory allocated to Oracle should not exceed 1/2 of the system memory. It is beneficial to save 40% of the system memory.

Increase memory for the System

* If you have many connections, you can use the MTS method.

* Complete patch

,