Home » Infrastructure » Unix » How to identify Asynchronous I/O?
How to identify Asynchronous I/O? [message #287721] Thu, 13 December 2007 04:48 Go to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Hi all!
I am newbie on Solaris.
With the server has 4 CPU, 8GB, Solaris 10, SPARC V890, I want to use db_writer_process initialization parameter.

However, according to the Mr.Tom's guide
Quote:


Normally, DBWR would do the IO. If the OS supports async io, it would use that. Async
IO just means that DBWR would make a request to the OS to write something to disk and
instead of waiting for it to be written, the write call would return immediate.
However, DBWR does not consider the write completed yet -- rather, DBWR will wait for the
OS to asyncronously signal it (call back) to say "write completed"

You can get much better throughput this way. DBWR might have 100 blocks to write out and
instead of write/wait/write/wait..... it will write write write.... and then wait for all
of the writes to complete -- the OS will notify it.

Now, some OS's/configurations don't support that -- so you can use IO slaves to emulate
it. DBWR will ask an IO slave to do the IO for it and the slave will notify DBWR upon
success (or failure of course) of the write just like the OS would have.


the number of slaves is more directly related to the number of disks, not cpus



and, according to the Oracle document..
Quote:


Database writer (DBWn)

The database writer writes modified blocks from the database buffer cache to the datafiles. Oracle Database allows a maximum of 20 database writer processes (DBW0-DBW9 and DBWa-DBWj). The DB_WRITER_PROCESSES initialization parameter specifies the number of DBWn processes. The database selects an appropriate default setting for this initialization parameter or adjusts a user-specified setting based on the number of CPUs and the number of processor groups.



Quote:


The DB_WRITER_PROCESSES initialization parameter lets you configure multiple database writer processes (from DBW0 to DBW9 and from DBWa to DBWj). Configuring multiple DBWR processes distributes the work required to identify buffers to be written, and it also distributes the I/O load over these processes. Multiple db writer processes are highly recommended for systems with multiple CPUs (at least one db writer for every 8 CPUs) or multiple processor groups (at least as many db writers as processor groups).

Based upon the number of CPUs and the number of processor groups, Oracle either selects an appropriate default setting for DB_WRITER_PROCESSES or adjusts a user-specified setting.



And would you like to answer my question:

- Does the server SPARC V890 with Solaris 10, 4CPU support Asyn IO?

- If it does, can I alter the parameter db_writer_processes=4 (<=CPUs) or I do nothing?

There is brief of my STATSPACK
Quote:


Load Profile Per Second Per Transaction
~~~~~~~~~~~~ --------------- ---------------
Redo size: 20,000.49 69,871.28
Logical reads: 1,139.38 3,980.41
Block changes: 82.66 288.79
Physical reads: 0.93 3.24
Physical writes: 2.67 9.34
User calls: 23.71 82.83
Parses: 7.10 24.79
Hard parses: 2.15 7.50
Sorts: 1.93 6.74
Logons: 0.05 0.19
Executes: 6.77 23.64
Transactions: 0.29

% Blocks changed per Read: 7.26 Recursive Call %: 59.72
Rollback per transaction %: 1.13 Rows per Sort: 479.96



and, especially
Quote:


Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 24.57
% of busy CPU for Instance: 95.94
%DB time waiting for CPU - Resource Mgr:

Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 8,129.7 8,129.7
SGA use (MB): 4,336.0 4,336.0
PGA use (MB): 125.9 170.3
% Host Mem used for SGA+PGA: 54.9 55.4
-------------------------------------------------------------

[Updated on: Thu, 13 December 2007 04:50]

Report message to a moderator

Re: How to identify Asynchronous I/O? [message #287727 is a reply to message #287721] Thu, 13 December 2007 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Does the server SPARC V890 with Solaris 10,

Yes

Quote:

can I alter the parameter db_writer_processes=4

You can but most of the time 1 dbwr is sufficient sometimes 2. Just see if you have DBWR wait events or some "write complete" waits or "local write" waits.

Regards
Michel
Re: How to identify Asynchronous I/O? [message #287736 is a reply to message #287727] Thu, 13 December 2007 05:24 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you, Michel!

If the OS support ASYN IO, Do I not need to chance the parameter dbwr_writer_process, because the OS does it?

Quote:


Just see if you have DBWR wait events or some "write complete" waits or "local write" waits



Is it the tacit talking that "Recommend the user client often commit"?

                                                                    Avg
                                                %Time Total Wait   wait    Waits
Event                                    Waits  -outs   Time (s)   (ms)     /txn
--------------------------------- ------------ ------ ---------- ------ --------
log file parallel write                 60,121      0      3,608     60      1.2
db file parallel write                  63,648      0      3,006     47      1.2
log buffer space                         2,690     74      2,274    845      0.1
log file sync                           43,125      4      2,174     50      0.8
SQL*Net message from dblink          1,408,863      0      2,081      1     27.2
control file parallel write             60,849      0      1,226     20      1.2
os thread startup                        2,993      9        430    144      0.1
log file switch completion                 519     50        350    674      0.0
buffer busy waits                          755     28        221    293      0.0


I really do not understand "buffer busy waits's values". Would you like to explain?

Thank for your time!

[Updated on: Thu, 13 December 2007 05:27]

Report message to a moderator

Re: How to identify Asynchronous I/O? [message #287738 is a reply to message #287736] Thu, 13 December 2007 05:27 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Is it the tacit talking that "Recommend the user client often commit"?

I don't understand this sentence.
Client must commit ONLY when a transaction is over, no more.
Client should never have to commit because of other thing than its functional need.

Regards
Michel
Previous Topic: how to write shell script to transfer the file to the server using SFTP
Next Topic: ls
Goto Forum:
  


Current Time: Thu Mar 28 09:37:38 CDT 2024