Oracle DBA Optimization Database performance Experience

A lot of the time, do Oracle DBA us, when the application administrator to inform us that the application is now slow, the database is very slow, when we go to the database to do a few examples of select also found the same problem, sometimes we can not start, Because we believe that the various life rates of the database are recommendations for meeting Oracle documents. In fact, today's optimization has been transformed into 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. Here's a little bit of practical experience to share with you, this article weighs 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.

Commonly used tools is vmstat, for Hp-unix can be used Glance,aix can be used for Topas, when you find vmstat pi column Non-zero, memory in the free column value is very small, glance,topas memory utilization more than 80%, This means that your memory should be adjusted, the method is generally the following items.

* The memory allocated to Oracle is not more than 1/2 of the system memory, generally guaranteed to 40% of the system's memory benefits.

* Increase memory for the system.

* If you have a lot of connections, you can use the MTS method.

* Make a full patch to prevent memory leaks.