Experience of Oracle DBA in optimizing database performance (1)

In many cases, when the application administrator notifies us that the application is very slow and the database is very slow, we also find the same problem when performing the Select statements in the database for several examples, sometimes we will not be able to get started, because we believe that the various life-saving rates of the database meet the recommendations of the Oracle documentation. In fact, today's optimization has been transformed to optimization wait (waits). In reality, the most fundamental appearance of performance optimization is also focused 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. I will share some of my practical experience with you below, this document focuses on Unix environments.

1. Check the status of the system through some Operating System ToolsFor example, CPU, memory, swap, and disk utilization, based on experience or compared with the normal state of the system, sometimes the system looks idle on the surface, which may not be a normal state, 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 %wio %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. In my example, we can see that the average wio value is 11, which means there is no special problem with io, and my idle value is zero, which means that my CPU is running at full capacity.

2. 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.
◆ For tables with frequent accesses to Oracle, apart from reasonably creating indexes, we should store these tables into tablespaces to avoid hotspot access, and then 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 allocated to Oracle should not exceed 1/2 of the system memory, which is generally guaranteed as 40% of the system memory.
◆ Add memory for the system.
◆ If you have many connections, you can use MTS.
◆ Complete 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$session a,v$process b, v$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: I often encounter an in statement when optimizing SQL statements. At this time, we must replace it with exists, because Oracle processes In by Or, even if an index is used, it will be slow.

For example:

SELECT col1,col2,col3 FROM table1 a WHERE a.col1 not in (SELECT col1 FROM table2)

Can be changed:

SELECT col1,col2,col3 FROM table1 a WHERE not exists (SELECT 'x' FROM table2 b WHERE a.col1=b.col1)

4. Another useful Script: Find the top 10 SQL statements with poor performance.

SELECT ◆ FROM (select PARSING_USER_ID,EXECUTIONS,SORTS, COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea order BY disk_reads DESC )where ROWNUM<10 ;