Many time, do our Oracle DBA, when the application administrator is announced to us now, the database is very slow, and the SELECT we do when we go to the database, and when we find the same problem, sometimes We will not start because we think that the variety of nutrients in the database is suggestions to meet the Oracle documentation. In fact, today's optimization is transformed (Waits), and the most fundamental appearance points of real performance optimization are also concentrated in IO. This is the most important aspect of performance, and it is found in the system in the system. Insufficient, the unreasonable use of certain resource utilization of the operating system is a better way. Here, I share my practical experience with you, this article emphasizes the UNIX environment.
First, through some of the tools of the operating system, such as CPU, memory, exchange, disk utilization, depending on the experience or normal state of the system, sometimes it seems to be seen on the system surface. A normal state because the CPU may wait for the completion of IO. In addition, we should also follow the processes that occupy system resources (CPU, memory).
1. How do I check if the operating system has IO? The tool used is SAR, which is a relatively universal tool. RP1 # SAR -U 2 10 is checked once every 2 seconds, and 20 times are executed, of course these are determined by you. 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
The% USR refers to the percentage of the CPU resource used by the user process.% Sys refers to the percentage of system resources using CPU resources.% Wio refers to the percentage of waiting for IO, which is worthy of our attention. % IDLE is free. If the value of the WiO column is very large, if it is more than 35%, you explain that your system's IO has bottleneck, your CPU spends a lot of time to wait for the completion of IO. The IDLE is very novel. The system CPU is very busy. Like my example, you can see that the WiO average is 11 shows that IO has no special problem, and my IDLE value is zero, indicating that my CPU has been full of load.
When your system has IO, you can solve it from the following aspects.
♀ Contact the technical support of the corresponding operating system optimize this, such as the strip of the HP-UX in the demarcation group. ♀ Find an unreasonable SQL statement in Oracle, optimize it to rationally establish an indexed index of the access amount in Oracle, and then store these table sizes space to avoid hot spots on the access, and then it is reasonable Partition.
2, pay attention to memory. Commonly used tools are VMSTAT. For HP-UNIX, you can use Topas for HP-UNIX. When you find that the PI column in vmstat is non-zero, the value of the free column in the Memory is small, Glance, Topas memory When the utilization is more than 80%, then your memory should be adjusted, and the method is generally the following. ♀ ♀ 内 超................,..,. 在, 在, 在,,, ♀ To increase the memory for the system, if your connection is particularly much, you can use the MTS method to make a full patch to prevent memory vulnerabilities. 3, how to find some of the SESSION SESSION and its execution of the company's special large-scale Oracle. HP-UNIX can use Glance, TOPIBM AIX can use TOPAS to use PS commands. Through these programs, we can find the process number of these processes that are particularly large, we can find which SQL is executing by the following SQL statement, this SQL is best implemented in software, etc. , Change the SPID in <> 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 =
We can analyze the obtained SQL, look at whether its execution plan takes the index, optimizes the full table scan to reduce the IO waiting, thus speeding up the execution speed of the statement.
Tip: When I do this, I often encounter the statement using IN, then we must replace it with exists, because Oracle is done in the way in handling IN, even if the index will be used very slow. For example: select col1, col2, col3 from table1 a where a.col1 not in (select col1 from table2) can be replaced: select col1, col2, col3 from table1 a where not exists (select 'x' from table2 bwhere a.col1 = B.COL1)
4, another useful script: Find the top ten 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 of Oracle Server, we can help this view of V $ session_wait, see what the system is waiting for, how much IO is used. Here is the reference script I have:
Script Description: Viewing IO's larger running session select se.sid, se.serial, se.SPID, SE.USERNAME, S.STATUS, SE.TERMINAL, S.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 Proment Procity St.SID = S.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 Retrieving results: 1, I It is the order of physical reading rows that have occurred at each waiting Session because it is related to actual IO.
2, you can look at these waiting processes are busy, is the statement reasonable? SELECT SQL_ADDRESS from V $ session where sid =
3, you should watch the Event this column, this is the key to our tuning, the following to the Event of the Event to make a brief description: A, BUFFER BUSY WAITS, Free Buffer Waits The two parameters identified by DBWR Enough enough, with the IO, when the version of the free buffer WAIT in V $ session_wait is small or no time, indicating that your system's DBWR process is determined enough, no adjustment; free buffer wait There are a lot of entries, your system feels very slow, then your DBWR is not enough, the Wio it produces has become your database performance bottleneck, then the solution is as follows: A.1 Add a write process, At the same time, adjust the DB_BLOCK_LRU_LATCHES parameter example: Modify or add the following two parameters db_writer_processes = 4 db_block_lru_latches = 8A.2 Open IO, IBM is much simpler, HP is troublesome, can contact the HP engineer. B, DB File Sequential Read, refers to the order of reading, the full table scan, which is also part of us should try to decrease, the solution is to use indexes, SQL tuning, and increase the parameter of DB_FILE_MULTIBLOCK_READ_COUNT.
C, DB File Scattered Read, this parameter refers to reading through the index, which can also improve performance by adding DB_FILE_MULTIBLOCK_READ_COUNT parameters.
D, Latch Free, related to the tie, need to be specially adjusted.
E, other parameters can be not particularly booked.
