【IO】Oracle数据库配置使用db_writer_processes或dbwr_io_slaves增加IO吞吐量

1 ，作用

实施db_io_slaves 或 db_writer_processes 会带来系统开销成本，多个writer进程和IO 从属进程是高级特征，意味着高IO吞吐量

 

2，相关初始化参数
 DBWR_IO_SLAVES
==============
在Oracle7内，多个DBWR 进程是单个从属进程；即不能执行异步I/O调用。在Oracle8中，true异步I/O（如果可用）被提供给从属进程。通过设置初始化参数dbwr_io_slave，仍有一个主DBWR进程和它的从属进程。该特征非常类似于Oracle7内的db_writers，除了IO从属进程现在可以支持异步I/O（在提供native异步I/0的系统上），因此有更好的IO吞吐量，由于从属进程在I/O调用后不被阻塞。

DBWR进程的I/O从属进程在数据库打开后，当第一次I/O请求时立即被分配给DBWR进程


DB_WRITER_PROCESSES
===================
多个DBWR进程通过设置初始化参数db_writer_processes来实施。该特征在Oracle8.0.4内被启用，允许true DBWR；即无主-从关系。每个DBWR进程被分配一个LRU latch集。因此，推荐设置db_writer_processes等于LRU latches的数(db_block_lru_latches或_db_block_lru_latches)并且不超过系统上的CPU数目。

3，使用DB_WRITER_PROCESSES 还是DBWR_IO_SLAVES?

=====================================================
尽管DBWR进程的这两种实施方法都有益，使用哪个选项，一般规则，依赖于下列：

1)       写活动数量；

2)       CPU数目(CPU数目也不直接地同LRU latch sets相关联)

3)       Buffer cache的尺寸

4)       异步I/O的可用性(OS上)

对该问题无明确的答案，但这是一些当做选择时需要考虑的事。请注意推荐在系统上实验两者(非同时)以决定哪个最好地适合环境。

--如果buffer cache非常大(100000个甚至更多的buffers)并且应用程序是写密集，那么db_writer_processes或许有益。

注意：dbwr进程的数量不应超过CPU的数目。

 

--如果应用程序不是写密集(或者是DSS系统)并且异步I/O可用，那么考虑单个dbwr进程;  
如果异步I/O不可用，那么使用dbwr_io_slaves.

--如果系统只有一个CPU，那么可以考虑使用dbwr_io_slaves.

4，需要知道的事情和留意什么.
====================================

-- 1.  多个DBWRs 进程和多个DBWR IO 从属进程不能并存。如果同时启用，则会产生如下错误信息: ksdwra("Cannot start multiple dbwrs when using I/O slaves.\n";);而且，如果两个参数都启用，dbwr_io_slaves优先。

-- 2.  DBWRs 进程的数目不能超过LRU latche sets的数(db_block_lru_latches或_db_block_lru_latches).  如果超过了，DBWRs 进程的数目将等于LRU latche sets的数(db_block_lru_latches或_db_block_lru_latches)并且启动期间在alert.log内生成下列消息：
("Cannot start more dbwrs than db_block_lru_latches.\n";);然而LRU latche sets的数(db_block_lru_latches或_db_block_lru_latches)可以超过DBWRs 进程的数目。

-- 3. dbwr_io_slaves 不受限于LRU latche sets的数(db_block_lru_latches或_db_block_lru_latches); 即可以dbwr_io_slaves >= db_block_lru_latches.


5，调优DBWR进程的其它方式

=================================
可以容易看到减少buffer操作对DBWR来说是直接的益处并且也帮助全面的数据库性能。

可以通过下列方法减少buffer操作:
1) 使用专用的临时表空间

2) 直接sort 读
3) 直接sql load
4) 执行直接export

另外保持高buffer cache命中率将有益于应用程序响应时间，也有益于DBWR进程。

 

6，如何查看调整DB_WRITER_PROCESSES 、DBWR_IO_SLAVES、DB_BLOCK_LRU_LATCHES或_DB_BLOCK_LRU_LATCHES参数

 

1)       查看参数

set linesize 132

column name format a30

column value format a25

sys@EXAM> show parameter db_writer

 

NAME                                 TYPE

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

VALUE

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

db_writer_processes                  integer

1

sys@EXAM> show parameter dbwr_io

 

NAME                                 TYPE

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

VALUE

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

dbwr_io_slaves                       integer

0

select

  x.ksppinm  name,

  y.ksppstvl  value,

  y.ksppstdf  isdefault,

  decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE')  ismod,

  decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE')  isadj

from

  sys.x$ksppi x,

  sys.x$ksppcv y

where

  x.inst_id = userenv('Instance') and

  y.inst_id = userenv('Instance') and

  x.indx = y.indx and

  x.ksppinm like '%lru%'

order by

     translate(x.ksppinm, ' _', ' ')

   ;

 

NAME

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

VALUE

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

ISDEFAULT          ISMOD

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

ISADJ

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

_db_block_lru_latches

8

TRUE               FALSE

FALSE

 

_gc_global_lru

AUTO

TRUE               FALSE

FALSE

 

_simulator_lru_rebalance_sizth

r

2

TRUE               FALSE

FALSE

 

_simulator_lru_rebalance_thres

h

10240

TRUE               FALSE

FALSE

 

_simulator_lru_scan_count

8

TRUE               FALSE

FALSE

 

 

2） 修改参数db_writer_processes

sys@EXAM> alter system set db_writer_processes=8 scope=spfile;

 

System altered.

 

sys@EXAM> startup force;

ORACLE instance started.

 

Total System Global Area  272629760 bytes

Fixed Size                  1218944 bytes

Variable Size              83887744 bytes

Database Buffers          184549376 bytes

Redo Buffers                2973696 bytes

Database mounted.

Database opened.

sys@EXAM> show parameter db_writer

NAME                                 TYPE

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

VALUE

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

db_writer_processes                  integer

8

 

 

 

 

3）修改参数db_writer_processes后，修改参数dbwr_io_slaves

sys@EXAM> alter system set dbwr_io_slaves=8 scope=spfile;

 

System altered.

 

sys@EXAM> startup force

ORACLE instance started.

 

Total System Global Area  272629760 bytes

Fixed Size                  1218944 bytes

Variable Size              83887744 bytes

Database Buffers          184549376 bytes

Redo Buffers                2973696 bytes

Database mounted.

Database opened.

sys@EXAM> show parameter dbwr_io

 

NAME                                 TYPE

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

VALUE

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

dbwr_io_slaves                       integer

8

sys@EXAM> show parameter db_writer_

 

NAME                                 TYPE

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

VALUE

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

db_writer_processes                  integer

1

sys@EXAM>

 

 

4）修改参数_db_block_lru_latches

sys@EXAM> alter system set "_db_block_lru_latches"=16 scope=spfile;

 

System altered.

 

sys@EXAM> startup force

ORACLE instance started.

 

Total System Global Area  272629760 bytes

Fixed Size                  1218944 bytes

Variable Size              83887744 bytes

Database Buffers          184549376 bytes

Redo Buffers                2973696 bytes

Database mounted.

Database opened.

 

 

 

 

Article-ID:         <97291.1>
Circulation:        PUBLISHED (EXTERNAL)
Folder:             server.Performance.Database
Topic:              Tuning Buffer Cache
Title:              DB_WRITER_PROCESSES or DBWR_IO_SLAVES?
Document-Type:      FAQ
Impact:             LOW
Skill-Level:        NOVICE
Server-Version:     07.00 to 08.01
Updated-Date:       26-JUN-2001 13:40:10
References:         
Attachments:        NONE
Content-Type:       TEXT/PLAIN
Keywords:           PARAMETERINFO;
Products:           5/RDBMS (7.0.X to 8.1.X);
Platforms:          GENERIC;  

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

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14359/viewspace-733971/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14359/viewspace-733971/