Oracle DBA Optimized Database Performance Insights

Today's optimization has been to optimize the waiting (Waits) transformation, the actual performance optimization of the most fundamental point of focus on Io, which is the most important aspect of performance, from the system waiting to discover the shortcomings of the Oracle library, the operating system some of the resources used by the unreasonable is a better way.

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: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

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.

When your system has an IO problem, it can be resolved in the following ways:

* 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 make them excellent.

* 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.

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 with GLANCE,TOP,IBM Aix topas, and PS commands are also available. 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 isn't 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 that accounted for the larger IO.

SELECT SE.SID,SE.SERIAL#,PR. Spid,se.username,se.status, Se.terminal,se.program,se. 　　MODULE, and 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, according to each 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, 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.

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