oracle 提升 异步io,aix中Oracle数据库异步io的系统调用信息

aix操作系统下Oracle数据库使用异步io(async)的系统调用信息类似如下：

AIX aio

listio64(0x0000000010000004, 0x000000000FFFFFFF,

0x00000000FFFFCB80, 0x000000000000007F, 0x0000000000000000,

0x0000000000000000, 0x12E0BE826D694B2F, 0x000000011068EE30) =

0x0000000000000000

aio_nwait_timeout(0x0000000000001000, 0x000000000000007F,

0x0FFFFFFFFFFF54A0, 0x00000009FFFFFFF6, 0x0000000000000000,

0x00000000FFFFFFFF, 0x0000000104978370, 0x00000000D3B8D277) =

0x000000000000007F

该信息使用truss工具生成，一般的我们可以通过跟踪dbwn进程的系统调用了确认Oracle是否启用了异步io特性。

以下为Oracle中异步io或其他替代方式的说明：

FREQUENTLY ASKED QUESTIONS

--------------------------

You have just upgraded to 8.0 or 8.1 and have found that there are

2 new

parameters regarding DBWR. You are wondering what

the differences are and

which one you should use.

DBWR_IO_SLAVES

==============

In Oracle7, the multiple DBWR processes were simple slave

processes; i.e.,

unable to perform async I/O calls. In Oracle80,

true asynchronous I/O is

provided to the slave processes, if available. This feature is implemented

via the init.ora parameter dbwr_io_slaves. With

dbwr_io_slaves, there is still

a master DBWR process and its slave processes. This feature is very similar to

the db_writers in Oracle7, except the IO slaves are now capable of

asynchronous

I/O on systems that provide native async I/O, thus allowing for

much better

throughput as slaves are not blocked after the I/O call.

I/O slaves for DBWR are allocated immediately following database

open when the

first I/O request is made.

DB_WRITER_PROCESSES

===================

Multiple database writers is implemented via the init.ora

parameter

db_writer_processes. This feature was enabled in

Oracle8.0.4, and allows true

database writers; i.e., no master-slave

relationship. With Oracle8

db_writer_processes, each writer process is assigned to a LRU latch

set. Thus,

it is recommended to set db_writer_processes equal to the number of

LRU latches

(db_block_lru_latches) and not exceed the number of CPUs on the

system. For

example, if db_writer_processes was set to four and db_lru_latches=4, then

each writer process will manage its corresponding set.

Things to know and watch out for....

====================================

-- 1. Multiple DBWRs and DBWR IO slaves cannot

coexist. If both are enabled,

then the following error message is produced: ksdwra("Cannot start

multiple

dbwrs when using I/O slaves.\n"; Moreover, if

both parameters are enabled,

dbwr_io_slaves will take precedence.

-- 2. The number of DBWRs cannot exceed the

number of db_block_lru_latches. If it does, then the number of DBWRs will be minimized to equal the

number

of db_block_lru_latches and the following message is produced in

the

alert.log during startup:

("Cannot start more dbwrs than db_block_lru_latches.\n";

However, the number of lru latches can exceed the number of

DBWRs.

-- 3. dbwr_io_slaves are not restricted to the

db_block_lru_latches;

i.e., dbwr_io_slaves >= db_block_lru_latches.

Should you use DB_WRITER_PROCESSES or DBWR_IO_SLAVES?

=====================================================

Although both implementations of DBWR processes may be

beneficial, the general

rule, on which option to use, depends on the following

: 1) the amount write activity;

2) the number of CPUs (the number of CPUs is also indirectly

related to the

number LRU

latch sets);

3) the size of the buffer cache;

4) the availability of asynchronous I/O (from the

OS).

There is NOT a definite answer to this question but here are

some considerations

to have when making your choice. Please note that

it is recommended to try

BOTH (not simultaneously) against your system to determine which

best fits the

environment.

-- If the buffer cache is very large (100,000 buffers and up)

and the

application is write intensive, then db_writer_processes may be

beneficial.

Note, the number of writer processes should not exceed the number

of CPUs.

-- If the application is not very write intensive (or even a DSS

system) and

async I/O is available, then consider a single DBWR writer

process; If async I/O is not available then use dbwr_io_slaves.

-- If the system is a uniprocessor(1 CPU) then implement may

want to use

dbwr_io_slaves.

Implementing db_io_slaves or db_writer_processes comes with some

overhead cost.

Multiple writer processes and IO slaves are advanced features,

meant for high

IO throughput. Implement this

feature only if the database environment

requires such IO throughput. In some cases, it

may be acceptable to disable

I/O slaves and run with a single DBWR process.

Other Ways to Tune DBWR Processes

=================================

It can be easily seen that reducing buffer operations will be a

direct benefit

to DBWR and also help overall database

performance. Buffer operations

can be

reduced by:

1) using dedicated temporary tablespaces

2) direct sort reads

3) direct Sqlloads

4) performing direct exports.

In addition, keeping a high buffer cache hit ratio will be

extremely beneficial

not only to the response time of applications, but the DBWR as

well.