Some experiences in the practice of Oracle Performance tuning

Oracle| Experience | performance
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
18:26:34 80 9 12 0
18:26:36 78 11 11 0
18:26:38 78 9 13 1
18:26:40 81 10 9 1
18:26:42 75 10 14 0
18:26:44 76 8 15 0
18:26:46 80 9 10 1
18:26:48 78 11 11 0
18:26:50 79 10 10 0
18:26:52 81 10 9 0

Average 79 10 11 0

%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 your system has an IO problem, you can solve it from the following aspects

♀ the technical support of the corresponding operating system to optimize this aspect, such as HP-UX in the delineation of the volume group, such as Stripe.
♀ to find unreasonable SQL statements in Oracle and optimize them
In addition to the reasonable index of the table with frequent visits in Oracle, the ♀ table space is stored in order to avoid the hot spot on the visit, and then the table is partitioned reasonably.


2, pay attention to memory.
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.
♀ to Oracle to use less than 1/2 of the memory of the system, generally guaranteed in the system memory 40% of the benefit.
♀ adds memory to the system
♀ If you have a lot of connections, you can use the MTS method
♀ 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 can use Topas
Some other commands that can be used with PS.
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=<spid>
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 I do optimization sql, I often run into statements that use in, and then 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 NOT 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: Viewing a running session that takes up 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 look at these waiting processes are busy what, the statement is reasonable?
Select sql_address from V$session where sid=<sid>;
Select * from V$sqltext where address=<sql_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 is much simpler, HP is troublesome, can contact HP engineers.
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.

Knot: In a hurry to write down this article, hope to be able to give you the Oracle tuning practice to help.