Introduction to Oracle Database Optimization on Unix platforms

Today's optimization has been transformed to optimization wait (waits). In reality, the most fundamental appearance of performance optimization is 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 operating system resources is a good method. This article focuses on the Unix environment.

1. Check the status of the system through some tools of the operating system, such as CPU, memory, swap, and disk utilization. Based on experience or compared with the normal status of the system, sometimes the system seems idle on the surface, which may not be a normal status, because the CPU may be waiting for the completion of IO. In addition, we should also look at the processes that occupy system resources (CPU and memory.

1. How can I check whether there is an I/O problem in the operating system? The sar tool is used, which is a common tool.

Rp1 # sar-u 2 10

That is to say, you can perform the inspection once every two seconds for a total of 20 times. Of course, this is up to 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 under 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 your system's IO has a bottleneck, 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 average wio value is 11, I/O is normal, and my idle value is zero, it means that my CPU is running at full capacity.

When your system has IO problems, you can solve them in the following aspects:

* Contact the technical support of the corresponding operating system to optimize this aspect. For example, hp-ux strip the volume group.

* Search for unreasonable SQL statements 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 hotspot access. In addition, you can partition the tables reasonably.

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. The methods are generally as follows.

* The memory used by Oracle should not exceed 1/2 of the system memory, which is generally guaranteed as 40% of the system memory.

* Adds memory to the system.

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

* Install patches to prevent memory vulnerabilities.

3. How to Find the session and statements executed by Oracle with extremely large resources for point usage.

Hp-unix can use glance, top, ibm aix can use topas, and ps commands. Through these programs, we can find the process numbers of these processes with extremely large system resources. Then we can use the following SQL statement to find the SQL statement that the pid is executing, it is best to execute this SQL statement in pl/SQL developer, toad, and other software. Replace the spid in <> with your spid.

SELECT a. username, a. machine, a. program, a. sid, a. serial #,

A. status, c. piece, c. SQL _text from v sequence $ session a, v sequence $ process B,

V align $ sqltext c WHERE B. spid = 'orcl 'AND B. addr = a. paddr AND

A. SQL _address = c. address (+) order BY c. piece

We can analyze the obtained SQL to see if its execution plan is indexed, optimize it to avoid full table scanning to reduce IO waits, to speed up statement execution.

Tip: When performing SQL optimization, we often encounter the In statement. In this case, we must use exists to replace it, Because Oracle does the Or statement when processing In, even if you use an index, it will be slow.

For example:

SELECT col1, col2, col3 FROM table1

WHERE a. col1 not in (SELECT col1 FROM table2)