Complete optimization of Oracle Database in UNIX environment

Today's optimization has been transformed to the optimization Wait (waits), in fact, the most fundamental point of performance optimization is also focused on Io, which is the most important aspect of performance, it is a good idea to wait in the system to discover the deficiencies of Oracle Library and the unreasonable utilization of some resources of the operating system. This paper focuses on the UNIX environment.

One, through some of the operating system tools to check the state of the system, such as CPU, memory, Exchange, disk utilization, based on experience or compared to the normal state of the system, sometimes the system appears to look idle this may not be a normal state, because the CPU may be waiting for the completion of IO. In addition, we should take note of those processes that occupy system resources (CPU, memory).

1, how to check the operating system has IO problem? The tools used are SAR, which is a more general tool.

Rp1#sar-u 2 10

That is, every 2 seconds of the inspection, the total execution of 20 times, of course, it is up to you to decide.

Example returns:

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

18:26:32%usr%sys%wio%idle

Note: I redhat under the view is this result, I do not know%system is 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 refers to the percentage of CPU resources used by the user process,%sys refers to the percentage of CPU resources used by system resources,%wio refers to the percentage of waiting for IO completion, which is worth noting,%idle is the percentage of idle. If the value of the Wio column is large, as above 35%, your system's IO has a bottleneck, and your CPU spends a lot of time waiting for IO to complete. Idle is very small to indicate that the system CPU is busy. Like my example, I can see that the Wio average of 11 indicates that IO is nothing special, and my idle value is zero, which means my CPU is running at full capacity.

When you have an IO problem with your system, you can solve it in the following ways:

* The technical support of the corresponding operating system to optimize this, such as HP-UX in the delineation of the volume group, such as Stripe.

* Find unreasonable SQL statements in Oracle and make them excellent.

* For the frequent access to the table in Oracle, in addition to a reasonable index, and then put these table partitioned space to avoid the access to generate hot spots, and then the table is reasonable zoning.