Oracle IO Optimization Insights

A lot of the time, we do Oracle DBA, when the application administrator informs us that now the application is very slow, the database is very slow, when we go to the database to do a few examples of select also found the same problem, some time we will not know, Because we believe that the various seed rates of the database are the recommendations that meet Oracle documentation. In fact, today's optimization has been to optimize the waiting (Waits) transformation, the real performance optimization of the most fundamental point of focus on the IO, which is the most important aspect of performance, from the system waiting to find out the shortcomings of Oracle Library, operating system some of the resource utilization is a better way to Here are some of my practical experience to share with you, this article is measured in the UNIX environment.

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

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

Rp1#sar-u 2 10

That is, every 2 seconds, a total of 20 executions, 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 look at redhat under 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:09am 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

The%usr refers to the percentage of CPU resources that the user process uses,%sys refers to the percentage of CPU resources that the system resource uses, and%wio refers to the percentage of waiting for IO to complete, which is an item worth our attention, and%idle is the percentage of idle. If the value of the Wio column is large, as above 35% indicates that your system has a bottleneck in Io, your CPU spends a lot of time waiting for IO to complete. Idle is a small indication that the system CPU is busy. Like my example, you can see that the Wio average of 11 indicates that IO has no particular problem, and my idle value is zero, indicating that my CPU is running at full capacity.

When your system has an IO problem, you can solve it from the following aspects

* Contact the appropriate operating system for technical support to optimize this aspect, such as the stripe of HP-UX in the delineation of the volume group.

* Find unreasonable SQL statements in Oracle and optimize them

* In the Oracle of the frequent access to the table in addition to reasonable index, and then put these table sub-table space storage to avoid access to generate hot spots, and then there is reasonable partitioning of the table.
2, focus on memory.

Commonly used tools are vmstat, for Hp-unix can use the Glance,aix can be used Topas, when you find Vmstat in the Pi column nonzero, memory of the value of the free column is very small, glance,topas in the utilization of more than 80% of RAM, This indicates that your memory aspects should be adjusted, the method is generally the following several items.

* The memory allocated to Oracle does not exceed 1/2 of the system memory and is generally guaranteed to be 40% of the system's memory.

Add memory to the system

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

* Play a full patch to prevent memory leaks.

3. How to find the session of Oracle with a special resource for the system and the statements it executes.
　
Hp-unix can be used Glance,top

IBM Aix can be used Topas

You can also use the PS command.

By using these programs we can find the process numbers of these processes that are particularly large with system resources, and we can find out which SQL the PID is executing by using the following SQL statement, which is best performed in software such as PL/SQL Developer,toad, <> You can replace the SPID 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 resulting SQL to see if its execution plan is indexed, and optimize it to avoid full table scans to reduce IO waits and thus speed up the execution of the statement.

Tip: When I'm doing optimized SQL, I often run into a statement that uses in, and we'll have to replace it with exists, because Oracle does it by or, when it's working in, even if the index is used very slowly.

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 NOT EXISTS

(SELECT ' x ' from table2 b

WHERE a.col1=b.col1)

4. Another useful script: find the first 10 poor performance 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 the performance problem of Oracle server, we can turn to v$session_wait this view to see what these sessions in the system are waiting for and how much IO to use. Here are the reference scripts I provide:

Script Description: View the running session with the larger 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 the order of the physical reads that have occurred on each waiting session because it is related to the actual IO.

2, you can look at these waiting processes are busy what, statement is reasonable?

Select sql_address from V$session where sid=;

Select * from V$sqltext where address=;

Execute the above two statements to get the session statement.

You also use the ALTER system kill session ' sid,serial# '; kill the session.

3, should be a note of the event this column, which is the key to our tuning column, the following for the frequently occurring event to do with a brief description:
A, buffer busy Waits,free buffer waits these two parameters are identified is the problem of DBWR adequacy, and Io is very large related, when the v$session_wait in the free buffer wait entry is small or not, Explain the DBWR process of your system is enough, do not adjust; free buffer wait for a lot of entries, your system will feel very slow, this means that your DBWR is not enough, it produced Wio has become your database performance bottleneck, then the solution is as follows:

A.1 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 asynchronous IO,IBM This is much simpler, and HP is a bit more troublesome to contact HP engineers.

b, DB file sequential read, refers to the sequential read, that is, the full table scan, which is the part we should minimize, 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, this parameter refers to the index to read, the same can be increased by adding db_file_multiblock_read_count this parameter to improve performance.

D, latch free, related to the plug, need to be specifically adjusted.

E, other parameters can not be special note.

Knot: Write this article in haste, hoping to offer help for your Oracle tuning practice

Oracle IO Optimization Insights