Introduction to Oracle Database Optimization in Unix

Oracle DatabaseOfOptimizationIt is one of the most important tasks in Oracle Database Operations. The optimization of Oracle Database can ensure that the Oracle database works normally.Unix environmentOracle Database optimization. I hope this will help you.

In many cases, when the application administrator notifies us that the application is slow and the database is slow, when we go to the database, we do a few examples of Select and find the same problem, sometimes we will not be able to start, because we believe that the various life-saving rates of the database are all to meet the recommendations of the Oracle documentation. In fact, today's optimization has been transformed to optimization wait (waits). In reality, the most fundamental appearance of performance optimization is also focused on IO, which is the most important aspect that affects performance, waiting in the system to discover the deficiencies in the Oracle database and the unreasonable utilization of some resources in the operating system is a good method. I will share some of my practical experience with you below, this document focuses on Unix environments.

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

1. How can I check whether there is an I/O problem in the operating system? The sar tool is used, which is a common tool.

Rp1 # sar-u 2 10

That is to say, you can perform the inspection once every two seconds for a total of 20 times. Of course, this is up to you.

Sample Return:

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

18:26:32% usr % sys % wi/o % idle

Note: This is the result under redhat. I do not know that % system is the so-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

% Usr indicates the percentage of cpu resources used by user processes, % sys indicates the percentage of cpu resources used by system resources, and % wio indicates the percentage of cpu resources waiting for io completion, this is worth noting. % idle is the percentage of idle space. If the value of the wio column is large, for example, more than 35%, it indicates that your system's IO has a bottleneck, and your CPU has spent a lot of time waiting for the completion of IO. If the Idle is small, the CPU usage is very busy. As shown in my example, if the average wio value is 11, I/O is normal, and my idle value is zero, it means that my cpu is running at full capacity.

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

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

Find and optimize unreasonable SQL statements in Oracle.

In addition to creating a proper index 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.

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.

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

Add memory for the system.

If you have many connections, you can use MTS.

Install patches to prevent memory vulnerabilities.

3. How to Find the session and statements executed by Oracle with extremely large resources for point usage.

Hp-unix can use glance, top, ibm aix can use topas, and ps commands. 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. Replace the spid in <> 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 obtained SQL to see if its execution plan is indexed, optimize it to avoid full table scanning to reduce IO waits, to speed up statement execution.

Tip: I often encounter an in statement when optimizing SQL statements. At this time, we must replace it with exists, because Oracle processes In by Or, even if an index is used, it will be slow.

For example:

SELECT col1, col2, col3 FROM table1

WHERE a. col1 not in (SELECT col1 FROM table2)

Can be changed:

SELECT col1, col2, col3 FROM table1

WHERE not exists

(SELECT 'x' FROM table2 B

WHERE a. col1 = B. col1)

4. Another useful Script: 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;

Ii. 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 and 2 enable asynchronous I/O. IBM is much simpler in this aspect, while 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.

E. Other parameters can be ignored.

Other optimization methods seem to be mainly focused on SQL query statements, and Oracle itself provides an optimizer.

The Oracle Database optimization in the Unix environment is explained here. I believe that after the above learning, you will be able to master the Oracle database optimization in the Unix environment, this is very helpful for your future work. I hope the content mentioned above will be helpful to you.