Complete optimization of Oracle Database in UNIX environment

Complete optimization of Oracle Database in UNIX environment2007-04-19 12:54:02 Author: changelive Browse Number: 14 Text size: " large "" in the "" Small "into the forum

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.1 3 99.75 10:36:17 am All 0.00 0.00 0.00 100.00 10:36:17 am CPUs%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 AL L 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.

3, how to find the point with a particularly large resources of Oracle's session and its execution statements.

Hp-unix can use GLANCE,TOP,IBM Aix to use Topas, in addition to the PS command. Through these procedures we can find the point of the system resources particularly large process number of these processes, we can use the following SQL statement to discover this PID is executing which SQL, this SQL is best in Pl/sql developer,toad and other software implementation, the <> You can change the SPID in the to 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 resulting SQL, see if its execution plan is indexed, and optimize to avoid full table scans to reduce IO waiting, thus speeding up the execution of the statement.

Tip: When you do SQL optimization, you often encounter statements that use in, and we have to replace it with exists, because Oracle does it in the same way as or, even if you use an index, it's slow.

Like what:

Select Col1,col2,col3 from table1 a WHERE a.col1 not in (select col1 from table2)

Can be replaced by:

Select Col1,col2,col3 from table1 a where does exists (SELECT ' x ' from table2 b where a.col1=b.col1)

4, another useful script: find the first 10 poor performance of the SQL.

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;

Second, quickly discover the cause of Oracle server performance problems, we can turn to v$session_wait this view, see the system of these sessions are waiting for, how much IO used. Here's a reference script that I've provided:

Script Description: View the large running session that occupies IO.

SELECT SE.SID,SE.SERIAL#,PR. Spid,se.username,se.status, Se.terminal,se.program,se. MODULE, se.sql_address,st.event,st. P1text,si.physical_reads, si.block_changes from V$session se,v$session_wait St, v$sess_io si,v$process PR WHERE St.sid=se.sid and St. Sid=si.sid and SE. Paddr=pr. ADDR and Se.sid>6 and St. Wait_time=0 and st.event not like '%sql% ' ORDER by Physical_reads DESC

A few notes on the retrieved results:

1, I am in accordance with the waiting session has occurred in the physical reading order, because it is related to the actual IO.

2, you can determine whether these waiting statements are reasonable.

Select sql_address from V$session where sid=; Select * from V$sqltext where address=;

The statement of this session can be obtained by executing the two statements above. You also use ALTER system kill session ' sid,serial# ';

3, should take a look at the event this column, this is our key to tune the column, the following on the occurrence of the event to do a brief description:

A, buffer busy Waits,free buffer waits these two parameters are identified as the question of whether the DBWR is sufficient, and Io is very relevant, when the free buffer waiting in the v$session_wait of the entry is very small or not, Explain the DBWR process of your system will be enough, do not need to adjust, there are many items of free buffer wait, your system must feel very slow, then your DBWR is not enough, the resulting wio has become your database performance bottlenecks, the solution is as follows:

A.1 to increase the write process while adjusting the db_block_lru_latches parameters.

Example: Modify or add the following two parameters

Db_writer_processes=4 db_block_lru_latches=8

A, 2 open asynchronous IO,IBM This aspect is much simpler, HP is troublesome, can contact HP engineer.

b, DB file sequential read, refers to sequential read, that is, full table scan, which we should minimize the part, the solution is to use the index, SQL tuning, while you can increase the Db_file_multiblock_read_count this parameter.

C, DB file scattered read, which refers to reading through an index, can also improve performance by adding Db_file_multiblock_read_count to this parameter.

D, latch free, related to the bolt, need special regulation.

E, other parameters can not be special note.

Other optimizations seem to focus on SQL query statements, and Oracle itself provides the optimizer.