Oracle Optimization in Unix environment

First, check the status of the system through some tools of the operating system, such as CPU, memory, swap, and disk utilization. Based on experience or compared with the normal status of the system, sometimes the system seems idle on the surface, which may not be a normal status, because the cpu may be waiting for the completion of IO. In addition, we should also look at the processes that occupy system resources (cpu and memory.

$ sar -u 1 10 HP-UX bilut42 B.11.11 U 9000/800 10/31/06 09:50:02 %usr %sys %wio %idle 09:50:03 4 1 30 65 09:50:04 7 1 27 65 09:50:05 2 0 25 73 09:50:06 1 1 21 77 09:50:07 1 0 19 80 09:50:08 8 1 18 73 09:50:09 12 1 22 65 09:50:10 9 1 22 68 09:50:11 8 0 21 71 09:50:12 9 1 20 70 Average 6 1 23 71

Here, % usr indicates the percentage of cpu used by the user process, % sys indicates the percentage of cpu used by the system process, % wio indicates the percentage of cpu used by the system process waiting for IO completion, and % idle indicates the percentage of idle. If the value of the % wio column is large, it indicates that there is a problem with IO and optimization is required. In general, it is considered that there is a problem if % wio is greater than 35IO. If the % idle value is low, the cpu is fully loaded.

When your system has IO problems, you can solve them in the following aspects:

1. Contact the technical support of the corresponding operating system to optimize this aspect. For example, hp-ux strip the volume group.

2. Search for unreasonable SQL statements in Oracle and optimize them.

3. In addition to properly creating indexes for tables with frequent accesses to Oracle, you can store these tables into tablespaces to avoid hotspot access. In addition, you can partition the tables reasonably.

Focus on memory

The common tool is vmstat, which can be glance for hp-unix and topas for Aix. When you find that the pi column in vmstat is non-zero, the value of free column in memory is very small, and glance, when the memory usage in topas is more than 80%, it indicates that you should adjust the memory. The methods are generally as follows.

1. The memory used by Oracle should not exceed 1/2 of the system memory, which is generally guaranteed to benefit from 40% of the system memory.

2. Add memory for the System

3. If you have many connections, you can use MTS.

4. Complete patches to prevent memory Vulnerabilities

How to find the session and statements executed by Oracle databases with extremely large resources.

Hp-unix can use glance, top

Topas can be used in IBM AIX

You can use the ps command.

Through these programs, we can find the process numbers of these processes with extremely large system resources. Then we can use the following SQL statement to find the SQL statement that the pid is executing, it is best to execute this SQL statement in pl/SQL developer, toad and other software without formatting. Replace the spid in <> with your spid.

Use top to view the pid of the run process whose run time is the longest. Use the following statement to check what the pid is doing.

SELECT a.sql_text FROM v$sqltext a,v$session b WHERE a.hash_value = b.sql_hash_value AND b.SID='&sid' ORDER BY piece ASC

We can see that statements with full table scan are optimized.

Find the top 10 SQL statements with poor performance.

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 ;

Quickly discover the cause of Oracle Server performance problems. We can turn to the v $ session_wait view to see what the system's sessions are waiting for and how much I/O is used. The following is my reference script:

Script Description: view the running sessions that account for a large 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

Description of the retrieved results:

1. I am sorting the physical reads of each pending session, because it is related to the actual IO.

2. Can you check what these waiting processes are busy with? Is the statement reasonable?

Select sql_address from v$session where sid= ; Select * from v$sqltext where address= ;

Run the preceding two statements to obtain the session Statement.

You can also use alter system kill session 'sid, serial # '; to kill this session.

3. Check the event column. This is the key column for optimization. The following briefly describes common events:

The parameters a, buffer busy waits, and free buffer waits identify whether dbwr is sufficient or not, which is highly related to IO, when the number of free buffer wait entries in v $ session_wait is small or absent, it indicates that the dbwr process in your system is sufficient and does not need to be adjusted. There are many free buffer wait entries, your system may feel very slow. This shows that your dbwr is not enough. The wio generated by it has become a bottleneck in your database performance. The solution is as follows:

A.1 Add a write process and adjust the db_block_lru_latches parameter.

Example: modify or add the following two parameters

db_writer_processes=4 db_block_lru_latches=8

A.2 enable asynchronous IO, IBM is much simpler in this aspect, and hp is a little more troublesome. You can contact Hp engineers.

B. db file sequential read refers to sequential read, that is, full table scan. This is also the part we should minimize. The solution is to use indexes and SQL optimization, you can also increase the value of db_file_multiblock_read_count.

C. db file scattered read. This parameter refers to reading through indexes. You can also increase the performance by adding the db_file_multiblock_read_count parameter.

D. latch free, which is related to bolts and needs special adjustment.

(