Optimizing Database Performance Oracle DBA experience Experience

Jan 27 posted in Oracle (RSS), 225 views.

Hou a lot of time to do Oracle DBA of us, when the application of the administrator is now applied to our notice a very slow, very slow when the database Hou, we went to the database to do a few examples of the Select also found the same problems, some time Hou We will be unable to start with, because we believe that the rate of the database of various kinds of life are to meet the Oracle documentation recommendations. In fact today has been optimized to optimize the wait (waits) transformation, and performance optimization in practice the emergence of the most fundamental points are also concentrated in the IO, which is the most important aspects affecting the performance from the system to wait to discover Oracle database less than, the operating system some of the irrational use of resources is a relatively good way, the following point of my experience to share with you, this article re-testing environment on Unix.
1, through the operating system some of the tool to check the status of the system, such as CPU, memory, exchange, disk utilization, based on experience, or with the system normal state of comparison, and sometimes the system may appear to look at idle it may not be a normal state, because the cpu may be waiting for IO completion. In addition we should also note that occupy system resources view (cpu, memory) process.

1, how to check the operating system, the existence of IO problem? Tools used by sar, this is a relatively common tool.

Rp1 # sar-u 2 10

That prosecutors once every 2 seconds, a total of 20 times, of course, you have these by the decision.

Sample Return:

HP-UX hpn2 B.11.00 U 9000/800 08/05/03

18:26:32% usr% sys% wio% idle

Note: I have redhat to view this is the result, I do not know% system is known as the% 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 which refers to the user process using the percentage of cpu resources,% sys is the system resource use refers to the percentage of cpu resources,% wio referring to wait for io completion percentage, which is worthy of our concept of a note, % idle or idle percentage. If the wio column value is large, such as in more than 35%, indicating the existence of your system, IO bottleneck, your CPU to spend a great deal of time to wait for IO completion. Idle a small help system CPU busy. Like me, this example, you can see wio average of 11 shows io no particular problems, and my idle value is zero, to illustrate my cpu has been running at full capacity.

When your system IO problems exist can be resolved from the following aspects:

* Contact the appropriate operating system technical support to optimize in this area, such as hp-ux in the designated volume group when the striping and so on.

* Oracle in an unreasonable search sql statements and to carry out excellent.

* Frequent visits to the Oracle tables in addition to a reasonable construction of index, then there are these tables meters to prevent access to storage space to generate hot spots, and then there is a reasonable partition of the table.

Commonly used tool is the vmstat, for hp-unix it can glance, Aix who can use topas, when you find out vmstat in pi non-zero, memory in the free column value small, glance, topas in memory utilization rate of more than 80%, then state your areas of memory should be some adjustment, and methods generally have the following.

* Oracle to use the memory allocated to the system memory should not exceed 1 / 2, the general security in the system memory, 40% of the benefit.

* For the system to increase the memory.

* If you have a particularly large number of connections, you can use the MTS approach.

* To play the whole patch to prevent memory leaks.

3, how to find points, particularly with the Department of the use of resources and its implementation of Oracle's session statements.

Hp-unix can glance, top, IBM AIX can use topas, also you can use the ps command. Through these programs we can find points with the system resources, particularly the process of a large number of these processes, we can through the following sql statement to find the pid is running which sql, the sql is best pl / sql developer, toad software such as the implementation of the put "" in the spid replace your spid on it.

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 get this sql analyze, look at its implementation plan whether to take the index to its optimized to avoid full table scans to reduce the IO wait, thus speeding up the pace of implementation statement.

Tip: optimized sql I was doing, we often encountered in the use of the statement, then we must use to replace it exists, because Oracle is based on when the deal In the way Or do, even if the use of the index will be slowly.

For example:

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 poor performance of the former 10 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, Oracle Server to quickly detect the causes of performance problems, we can turn to this view, v $ session_wait to see if the system waiting for any of these session, using the number of IO. The following is information I have provided the script:

Script Description: View a larger total io running session.

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

The results of the retrieved points Description:

1, I was waiting for the session for each of the physical reading of rows that have taken place the order, because it is associated with the actual IO.

2, you can see how these processes are busy waiting for what the statement is reasonable?

Select sql_address from v $ session where sid =;

Select * from v $ sqltext where address =;

The implementation of the above two statements will be able to get this session statements. You also use alter system kill session 'sid, serial #'; to kill this session.

3, should view event this note about the column, this is our key to a tuning, the following event appears on the frequently made to a brief description:

a, buffer busy waits, free buffer waits identity of these two parameters is dbwr adequacy issues associated with the IO big, when the v $ session_wait in the free buffer wait for little or no time for Hou, indicating Your system dbwr process can right enough, do not adjust; free buffer wait a lot of entries, your system feels must be very slow, then state your dbwr have not enough, and it has been generated by your database wio performance bottleneck, then the solution is as follows:

a.1 increase in write process, while db_block_lru_latches parameters to be adjusted.

Example: modify or add the following two parameters

db_writer_processes = 4

db_block_lru_latches = 8

a, 2 open asynchronous IO, IBM in this regard is much simpler, hp is trouble, we could have with the Hp engineers.

b, db file sequential read, referring to the order of reading, that is, full table scans, which is that we should try to reduce some, the solution is to use the index, sql tuning at the same time can increase the db_file_multiblock_read_count this parameter.

c, db file scattered read, this parameter refers to the index to read through the same by increasing the db_file_multiblock_read_count this parameter to improve performance.

d, latch free, with the bolt-related, and require special regulation.

e, other parameters may not be particularly concept note.

Other optimization methods appears to be mainly concentrated in the SQL query above, Oracle itself provides the optimizer. It seems a lot of DBA's knowledge ah.