Hi,
what will be the remedy if the waits r 2 high..???

Hi Tom,

I am a developer and learing oracle DBA stuff.
I am really confused with SALVE word. What is that?
As per above question DBWR SLAVES,what is that?

I know there is DBWR process that will write blocks from buffer cache to disk whenever checkpoint(CKPT) occur.

Please explan about SLAVE process.

Thanks

Hi Tom,

Now i understood about DBWR SLAVE process.
another question.

Accouring to your reply DBWR process is coordinator and SALVES are the processes which are doing real work.

1)Does that mean there is always DBWR SLAVE process in the database for DBWR process?

2)How many DBWR SLAVE process we can have in database?

I know there is DBWRnnn Parameter in init.ora file.

3)MULTIPLE DBWR SALVE process will distribute dirty buffers write to disk. Am i right?


Thanks

Lets see if I can answer your questions correctly.

1) No, if your server supports asynch IO then
there would be no point having slaves for the DBWR
process since slaves are used to simulate asynch IO

2) There is an init ora parameter db_writer_processes
which controls the number of DBWR processes.
The maximum number of DBWR processes is 10, most systems
have 1 DBWR but on large multi CPU machines it is set
higher.

If you change DBWR_PROCESSES you should also configure
DB_BLOCK_LRU_LATCHES. This controls the number of
LRU (least recently used) list latches. It is recommended
each DBWR process have it's own list.
There is a seperate parameter for SLAVES called
DBWn_IO_SLAVES.

3) Yes the DBWR or it's SLAVE writes
dirty blocks from the buffer cache to disk.

Multiple DBWR are more efficient then SLAVES because the buffer cache is effectively partitioned such that each database writer process maintains a disjoint set of buffers. The allows the buffer cache processing to be parallelized across the available database writer processes.
DBWR IO slaves do not parallelize buffer processing because a single DBWR is used to allocate work to the I/O slaves. IO slaves I suspect will have a higher overhead
then multiple DBWRs since there is IPC communication between the Master DBWR and it's slaves which results in
context switches.

Multi DBWR I believe divide the buffer cache into equal sized groups of buffers (working sets).

The working sets are assigned to the DBWRs in a round robin
fashion which ensures each receives a share of the different pools such as keep, recycle default etc.
The number of working sets is defined by the number of
LRU latches.

I have noticed that a recovery after a shutdown abort is
faster with a single DBWR then with multiple DBWRs
and I haven't worked out why yet. There is no appreciable
difference when starting the database with multi DBWR
after normal or immediate shutdown.

Since I don't work for Oracle I could be completely wrong
but hopefully Tom will comment on what I have said




Hi Tom,

Now i understood about DBWR SLAVE process.
another question.

Accouring to your reply DBWR process is coordinator and SALVES are the
processes which are doing real work.

1)Does that mean there is always DBWR SLAVE process in the database for DBWR
process?

2)How many DBWR SLAVE process we can have in database?

I know there is DBWRnnn Parameter in init.ora file.

3)MULTIPLE DBWR SALVE process will distribute dirty buffers write to disk. Am i
right?


Thanks

Hi Jim
Your explanation regarding DBWR SLAVE is really helpful to understand the process.

It is now very clar to me.

Another question.

What is the role of DB_BLOCK_LUR_LATCHES in the database?

Is this parameter(DB_BLOCK_LUR_LATCHES) protect db buffer cache? (According to latche defination(Protect shared data structure)).

I know it is set to multiple of no. of CPU.

Can you please explain in depth.

I would appricate any help.

Thanks for good work Tom!
Could you provide comment on Jim's follow up, as whether
his comments are accurate.

Thanks.

Jim ...
Very clear explanation

</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/iodesign.htm#PFGRF015 <code>

"With Oracle9i Release 2 (9.2), you can use the FILESYSTEMIO_OPTIONS initialization parameter to enable or disable asynchronous I/O or direct I/O on file system files. This parameter is platform-specific and has a default value that is best for a particular platform. It can be dynamically changed to update the default setting."

Tom, If my system does not support async io, can i force it using the aforementioned parameter? Could you please clarify what the doc says on using this parameter? Thanks.

"That would be like trying to force me to play the piano."

I enjoyed reading your answer. I don't know how to either!

How does the parameter DISK_ASYNCH_IO different from the new 9i parameter.

thanks again.

I read the support note, to my surprise, it is suggesting to set both to true as follows. Is this true only with linux? Is this 9i parameter 'filesystemio_options' applicable only for linux? Thanks.

Parameter settings in init.ora for filesystem files:
a) Make sure that all Oracle datafiles reside on filesystems that support asynchronous I/O. (For example, ext2)

b) set 'disk_asynch_io=true' (note default value is true)

c) set 'filesystemio_options=asynch'

Why do we have to set both 'disk_asynch_io=true' and also 'filesystemio_options=asynch'?

Is there any special with 9i parameter filesystemio_options?

(1) If I have asynch io turned on for the server, is it possible to to mount a particular file system on direct io as other file system can continue to be asynch io? Thanks.

(2) If above is possible, do you recommend the file system containing redo logs be on direct io?

Does direct io mean "write through cache"? I thought LGWR already does "write through cache" and if so why do we need to mount the file system containing redologs on direct io? May be I am confused with direct io concept? could you help me to understand? Thanks.

Is there a way DBA can find out if the file system is mounted with direct IO or not using some "special" unix command? Thanks again.

Very Interesting information.

How do I know if my operating system is using Sync IO or Async IO. Is there a command in Unix. How do we check in Windows NT.

noel.

Thanks to Ravi for bringing this up in this forum. Thanks to you Tom for your explanation with an example.

I am referring to your answer here.

" for i in 1 .. 100
loop
write block X to disk and wait for the OS to tell
us the operation is totally complete (the block is
not buffered by the OS or anything, it is on disk)
end loop

As you can imagine, that would be slow."

From Database how would I know the cause of slow ?

Thanks
-Gururaj

Tom,

Is it what some books call as blocking and non-blocking I/O. ie.

Synch I/O = Blocking I/O where a process waits (does not return) till the O/S completes the I/O
Asynch I/O = Non-Blocking I/O where a process does not wait for the O/S to complete the I/O, but rather continues with its processing and the O/s sends back a signal to the process to indicate the I/O completion.

Please confirm

I am confused about this parameter. What should a dba set this parameter to achieve asynch io or not? Does a dBA need to be concerned about this parameter? It was not there prior to 9i. why it is so now in 9i? Thanks.

Hi Tom,
1) I don't have metalink access, could you please tellme if aync io can be set in my database please

2) Is there a problem if I increase as Don Burleston advices
"
db_block_lru_latches (undocumented starting in Oracle9i)

A too-small value for db_block_lru_latches normally manifests itself with a high LRU latch contention waits in your STATSPACK report. The default value is one-half the cpu_count on your dedicated server. Oracle recommends that db_block_lru_latches never exceed (cpu_count * 2 * 3) or (db_block_buffers / 50), whichever is higher.

There is a problem with this computation whenever you have multiple buffer pools (KEEP, RECYCLE) because you cannot govern the number of latches assigned to each data buffer pool. This default value for this may be too small if your db_writers is greater than 1. Many savvy Oracle9i DBAs running multiple data buffers (e.g. db_32k_cache_size) recommend re-setting this undocumented parameter to the recommended maximum value.
"
Thanks Tom

Thanks Tom

Tom,

In a 30 minutes window - 9ir1 db, showed me the follow

Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (s) Wt Time
-------------------------------------------- ------------ ----------- -------
library cache lock 1,636 4,917 40.59
async disk IO 801,883 1,564 12.91
db file parallel write 3,946 908 7.49
direct path read 315,528 735 6.07
db file sequential read 233,897 477 3.93
-------------------------------------------------------------

1.for library cache contention ....

reduce contention by increasing the shared pool

how about
async disk IO and direct path read ?

Please get your inputs on this.

Thanks a lot

If we let OS write the blocks, after dbwr places the blocks in os cache is corrupt for some reason, what happens? Thanks.

Looks like above reader wants to know what would happen if the blocks in os cache did not get written to the disk by OS processes for some reason? Do we get an error message of some sort? Thanks.

Hi Tom,

I am using oracle 9i rel 2 on solaris ver 9. It is purely data warehouse environment where we use cdc. Currently the value of parameter "filesystemio_options" is "asynch".
Now if i use following command

/nas/oradata/DAS/redo> mount | grep DAS

then I get

/nas/oradata/DAS on bbot:/vol/oradata_dev/DAS remote/read/write/setuid/bg/vers=3/proto=tcp/hard/intr/rsize=32768/wsize=32768/llock/forcedirectio/xattr/dev=438000b on Wed Apr 6 19:26:30 2005

It means the filesystem for my database is mounted with force direct IO option.

Now I want to know following things in respect of the above mentioned scenario :

a) In this settings , is direct IO taking place ? If so how to trap that ?
b) If I change the value of filesystemio_options to "setall" then what will be the scenario ? Will there be mixture of async IO and direct IO ? if so how to trap that ?
c) If I change the value of filesystemio_options to "directio" then all the IOS will be direct IO ? If so then do we have need to change the value of dbwr_io_slaves to a value ( say x ) along with the value of disk_asynch_io as "TRUE"?

I need you clarification with example if possible ( I know that your example has been always bet in the world ).

Thanks
Deba

hi tom
os - aix

1. in Oracle database - administrator´s reference manual b15658 manual page a-7 it is recommended to put log files on jfs2 file system with concurrent i/o enabled
you though recommended using file system enabled with direct i/o ...

2. what is the recommendation then for concurrent file system usage - should all database files (datafiles, temp files, redo and archvie logs be located on file system with enabled async i/o)
thanks!

Hi Tom,
Our oracle is running on solaris with buffered I/O.And we have a EMC SANs which have a huge amount of memory to use as disk I/O cache. We want to test direct I/O performance against buffered I/O.
My question is how to benchmark this improvement?
1)Using direct I/O removes doulbe-cache,reduces cpu usage for copying buffers among SGA,filesystem cache and disk I/O cache.So I would expect a lower %sys (sar -u) compared with buffered I/O?
2)From database view, direct I/O make writes faster.So I would expect a lower AVERAGE_WAIT of some I/O wait events ?

Thanks in advance.
Steven

Hi Tom,
We are in the process os migrating our Oracle Apps instance from existing AIX server to the new server. When configuring the server, I was trying to understand Asynch io,direct io and related stuff in the light of AIX-Oracle performance guidelines set out by IBM for pSeries servers in particular.

This discussion was very useful

Hi Tom,

Need a favour from you to solve I/O related issue happening on our db box. We have a 4 CPU machine with 8 disks supported by 4 controllers with 10g database running on the machine.

The kind of wait the "top" command shows really disturbs us a lot

CPU states: cpu user nice system irq softirq iowait idle
total 4.4% 0.0% 7.6% 0.0% 0.0% 387.2% 0.0%
cpu00 0.3% 0.0% 0.9% 0.0% 0.0% 98.6% 0.0%
cpu01 0.9% 0.0% 0.7% 0.0% 0.0% 98.2% 0.0%
cpu02 1.1% 0.0% 2.5% 0.0% 0.0% 96.2% 0.0%
cpu03 1.9% 0.1% 3.5% 0.0% 0.0% 94.2% 0.0%
Mem: 16304524k av, 16023408k used, 281116k free, 0k shrd, 210740k buff
3226640k actv, 2729168k in_d, 282644k in_c
Swap: 16776952k av, 131972k used, 16644980k free 13190780k cached

I looked in the various snapshots captured by the AWR using ADDM and found most wait events are related to PX, specially PX Deq Credit: send blkd wait event when using parallel slaves.

The join across 10 million to 20 million rows just shows the wait to 90%. The init.ora parameters are somewhat :

Total System Global Area 6945767424 bytes
Fixed Size 778916 bytes
Variable Size 1177034076 bytes
Database Buffers 5734400000 bytes
Redo Buffers 33554432 bytes

disk_asynch_io boolean FALSE
filesystemio_options = directio
use_indirect_data_buffers = true
db_block_buffers = 700000
db_writer_processes = 4
#db_cache_size = 900M (Ignoring it since we need to use VLM and mahe the SGA size bigger than 1.7G)
_db_block_lru_latches = 256
_db_block_hash_latches = 32768
#buffer_pool_keep = (buffers:24576, lru_latches:24)
#buffer_pool_recycle = (buffers:16384, lru_latches:12)
java_pool_size = 8M
shared_pool_size = 800M
shared_pool_reserved_size = 48M
_shared_pool_reserved_min_alloc = 4000
large_pool_size = 300M
_px_use_large_pool = TRUE

Please suggest from where do I start to find the bottleneck causing the huge I/O waits. Sqlldr also shows significant I/O waits (70%) for the system.

We have 8 disks where I have spread all datafiles for each of the tablespace (UNDO/TEMP/DATA/INDEXES)

Please suggest!

Hi Tom,

Sorry to paste the V$system_event picture for which the I/O wait is very high.

Thanks

EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------------------------------------------------------------- ----------- ----------- ------------
rdbms ipc message 714096 200956759 281
SQL*Net message from client 22453457 140530902 6
PX Deq Credit: send blkd 923739 102477074 111
PX Deq: Execution Msg 675940 96533935 143
PX Deq: Table Q Normal 1059647 50568859 48
PX Idle Wait 192010 36945479 192
jobq slave wait 94255 24980879 265
db file sequential read 5321160 20236092 4
pmon timer 41252 11752359 285
Queue Monitor Wait 3944 11493940 2914
smon timer 1262 11375034 9013
wakeup time manager 1133 10133003 8944
queue messages 19918 9694880 487
wait for unread message on broadcast channel 99395 9672971 97
PX Deq Credit: need buffer 102527 9190427 90
PX Deq: Execute Reply 163822 8676825 53
db file scattered read 456801 7329631 16
log file sync 81090 1270307 16
log file parallel write 116479 788652 7
library cache lock 2233 653776 293
Backup: sbtwrite2 1475955 500143 0
Queue Monitor Slave Wait 122 484399 3970
PX Deq: Table Q Get Keys 2423 451952 187
log buffer space 5229 402483 77
read by other session 33198 373646 11
control file parallel write 86585 274868 3
class slave wait 552 269470 488
free buffer waits 201325 242560 1
log file switch (checkpoint incomplete) 3612 231310 64
Queue Monitor Task Wait 474 210728 445
SQL*Net break/reset to client 43483277 176094 0
db file parallel read 974 164038 168
enq: TX - row lock contention 898 151965 169
control file sequential read 365111 151138 0
buffer busy waits 175006 129355 1
Backup: sbtbackup 26 105740 4067
enq: CF - contention 282510 88084 0
log file sequential read 21875 58256 3
ARCH wait on SENDREQ 21832 56094 3
enq: TC - contention 262 54172 207
Backup: sbtclose2 26 29861 1148
rdbms ipc reply 11931 23576 2
enq: PS - contention 111 19877 179
log file switch completion 279 16684 60
enq: FB - contention 52 13681 263
latch: In memory undo latch 416 12486 30
process startup 2713 10441 4
ARCH wait on c/f tx acquire 2 102 9923 97
db file single write 4757 8090 2
latch: cache buffers chains 9439 7883 1
inactive session 62 6051 98
PX qref latch 9831 5396 1
enq: CI - contention 1352 5151 4
control file single write 21675 5078 0
SQL*Net message to client 22453492 4750 0
enq: HW - contention 23 4690 204
library cache pin 97 4452 46
SQL*Net more data from client 240201 2970 0
PL/SQL lock timer 2 1951 976
latch: session allocation 7382 1911 0
LGWR wait for redo copy 7451 1779 0
enq: JD - contention 9 1749 194
enq: RO - fast object reuse 70 1666 24
enq: SQ - contention 8 1597 200
enq: TM - contention 7 1387 198
latch: library cache 1609 1371 1
direct path read temp 3625210 1306 0
PX Deq: Signal ACK 727 1175 2
library cache load lock 27 1160 43
direct path write temp 3048009 1095 0
SQL*Net message from dblink 6555 821 0
latch free 654 791 1
Backup: sbtremove2 7 678 97
latch: parallel query alloc buffer 32 579 18
direct path read 371880 423 0
latch: messages 535 419 1
control file heartbeat 1 390 390
enq: TX - index contention 2 353 176
Backup: sbtinfo2 19 306 16
latch: library cache lock 89 241 3
latch: redo writing 777 184 0
SQL*Net more data to client 37462 180 0
row cache lock 869 174 0
PX Deq: Table Q Sample 215 158 1
enq: US - contention 786 143 0
latch: row cache objects 288 140 0
latch: library cache pin 174 138 1
latch: redo allocation 56 114 2
enq: PE - contention 2 111 55
PX Deq: Parse Reply 685 105 0
undo segment extension 1729875 101 0
latch: cache buffers lru chain 58 89 2
single-task message 33 88 3
local write wait 82 83 1
ARCH wait on ATTACH 4 59 15
PX Deq: Join ACK 1129 43 0
sort segment request 2 40 20
latch: shared pool 37 36 1
enq: TX - allocate ITL entry 78 35 0
latch: checkpoint queue latch 11 32 3
SQL*Net more data from dblink 11779 19 0
PX Deq: Table Q qref 54 12 0
ksfd: async disk IO 85942 11 0
reliable message 259 10 0
log file single write 342 9 0
Backup: sbtinit 25 6 0
enq: PR - contention 2 5 2
latch: enqueue hash chains 22 5 0
kksfbc child completion 1 4 4
wait list latch free 1 2 2
SQL*Net message to dblink 6555 1 0
Backup: sbtend 25 0 0
SQL*Net more data to dblink 9 0 0
instance state change 2 0 0
buffer deadlock 48 0 0
latch: object queue header operation 5 0 0
direct path write 696 0 0
latch: undo global data 8 0 0
ARCH wait on DETACH 4 0 0
Backup: sbtinit2 25 0 0

Hi Tom,

About asynch i/o when you say the logic looks like
for i in 1. .100
loop
Write block x and don't wait but return immediately
end loop
Now wait for confirmation from O/S.

Is it that the block is wriiten from SGA to O/S buffers and the process is repeated for next block ? So that when say block 2 is being copied to o/s buffer, bolck 1 may be being written from o/s buffer to disk ?

As I understand , whether synch I/O is used or asynch i/o is used, when confirmation is received from O/S that the blocks are written, it guarantees that blocks are actually written to disk. Not just to i/o buffers. Am I correct ?

If that be so then, why can't LGWR employ asynch I/O on servers where asynch i/o is enabled? Is there any reason why LGWR always has to use synch i/o?

Maybe one thing that hasn't been explicitly stated is that async IO calls allow DBWR and LGWR to do other things during the time they'd be otherwise "frozen" waiting for the outstanding IO requests to complete.
In the case of DBWR, it may include identifying and pinning the next batch of dirty blocks inside that buffer cache that needs to be written to disk.
For LGWR it may include determining if the next piece of the log buffer is ready to be flushed to the logs and getting that stuff ready to go.

Hi Tom,

What oracle recommends with regard to the convosync=direct parameter with regard to oracle or the QUICKIO option of VxFS?

Thanks,
depak

Hi Tom,

One of the options for filesystemio_options is SETALL. What does that mean? Use both async IO and direct IO? How does a database make the decision? What settings in OS level, say Solaris 10, shouls be made for this to be effective?

Thank you.

Hi,
My database has asynch write enabled and one DBWR and 4 Slaves for it. I want to know if having 4 slaves will cause a lock(enqueue waits) on any table. Say during the Loading process, all the 4 slaves are trying to update a table and slave 1 has updated a record and slave 2 also wants to update that record. So will slave 2 wait till slave 1 commits? What will happen in this case?

Thanks for the reply. But have another question. In My Db, We have filesystemio_options as asynch and disk_io_asynch as false? Is it fine? Isnt it better if we have asynch operation in file as well as disk?

Hi Tom,
As you said in the previous post that i/o slaves do not lock a table. But when i was trying to load a table, in the wait events i saw a lot of enqueue waits(around 225 timeout of a total of 250). And i am sure i was the only one loading that table. Any idea what might be happening?

Hi Tom,
As per the document "If you set DISK_ASYNCH_IO to false, then you should also set DBWR_IO_SLAVES to a value other than its default of zero in order to simulate asynchronous I/O." as per my understanding if disk_asynch_io is true then the db will go for asynchronus i/o else it will go to synchronus i/o. Please correct me if i am wrong.
By the way the default value is true. SO you mean it should be set to true?

Tom "without an example of what the table is looking like and a better description of
how the load goes - not really.

could be a unique constraint for example. could be many things - could be that
you were direct pathing without using a parallel direct path load"
Well, i am using a ETL tool (Datastage) to load the table and the strategy is Truncate and Load. We dont have any constraint on it. But we are doing direct path load. There is a stage in the tool which uses bulk loading. But the problem is Its not bulk loading. In the wait events, i was not able to see direct i/o , and direct write. The reason could be the parameter filesystemio_options is set to async. So Direct path loading is not happening. Please correct me if i am wrong here.
But what would have happened if i am using direct path load with actually any direct pathing? And in that tool, there is no parameter to parallize the direct path loading. :(

"Hi Tom,
As per the document "If you set DISK_ASYNCH_IO to false, then you should also
set DBWR_IO_SLAVES to a value other than its default of zero in order to
simulate asynchronous I/O." as per my understanding if disk_asynch_io is true
then the db will go for asynchronus i/o else it will go to synchronus i/o.
Please correct me if i am wrong.
By the way the default value is true. SO you mean it should be set to true?

Followup:
let it default. do not set it. "

Tom, you didnt answer the first question. The document says that if disk_asynch is set to false then dbwr_io_slaves should be 0 order to simulate asynchronous I/O." . Why so?
if disk_asynch is set to false, then having >0 slaves will have the asynchronus i/o??
Please clarify.

"direct pathing isn't a file system io thing, it is just a direct path.
you are not telling me sufficient details on the load here. if you have a single process loading, enqueue waits - well, they are not part of the picture. if you are doing multiple load processes with direct path AND you didn't use direct=y with sqlldr, then you will go one at a time. "

I thought setting parameter filesystemio_options =dircetio or setall will enable direct path loading. Thanks for clearing that up.
As i have told its a tool which we are using(not sqlldr) which uses direct path loading. We just pass the table names and it will internally create the control and dat file and load the table. And there is no option as parallel in that tool. So Just one process was running. Thats why i thought may be as the parameter was set to asynch and not directio, direct path loading was not happening.
But one doubt then. i tried create table as select * for a big count so that it could take some time(5000000). and then i took its sid and checked for all teh wait events it faced. Although the load completed in the 2 min, one of the wait events was Free Buffer waits. The doubt is Direct path doesnt use the buffer at all. Right? So If direct path loading was going on, why the Free Buffer waits showed up in v$session_event and v$session_event?

So you mean to say that the buffers wait were for the select * that i issued?
i gave the following command:
create table fact_poc2 as select * from fact_poc1
Now i saw Buffer wait events here. And you mean to say the buffer wait events were for select * from fact_poc1??
Please clarify

Sorry to ask about Direct Pathing here.. But isnt the whole concept of Direct pathing means it will bypass all the buffer?
As per the diagram shown in Oracle Docs, it says that for direct path it bypasses all buffers and memeory management processes and it doesnt contend for Oracle resources as well. And create table as ... definetely goes for Direct Path loading. So whats the advantage i am getting if i issue create table as select * ... Its still going for buffers...

Please clarify the same.

Hi Tom,

"Have read in an article that the DB may need to use asynchronous IO for log files and temp files and direct IO for data files".

Have few queries on this w.r.t. Solaris 9 (Oracle 9.2.0.7)

> Can both asynchronous IO and direct IO be enabled on the OS file system at the same time?

> If yes, what if we have mounted the file system using "forcedirectio" option? Can the database still use both modes of IO?

> How to enable both the IO modes? What are the parameter settings for the same?

Looking forward to your evervaluable feedback...

Tom,

I have couple of doubts regarding DBWR receiving a "block written" confirmation from the OS.

1> In view of disk failure, what is the timeout in terms of DBWR waiting for a confirmation from OS.

2> Is this timeout OS dependent or something that can be tweaked in Oracle.

3> Would DBWR crash the instance if it doesn't receive a confirmation from the OS that the block has been written, or does it retry ?
If it retries to write the block, then how many retries are there before it'd eventually fail, crashing the instance.

Feedback appreciated.

Thanks
Sanji

Hi Tom,

Kindly explain, under what conditions does Oracle decide to do a "db file parallel write".

Does it do so in the following cases?

1> When we have multiple CPUs on the server?
2> When we have multiple data files for a tablespace?

OR

3> Always, whenever it has to write to the datafiles.

Dear Tom,

We are migrating from 11.0.3 to 11.5.10 in the next 15 days and our database is 9.2.6, solaris is the server.

As solaris is having option of Direct i/o can you please suggest me , will that be better to mount file systems with Direct i/o Enabled. your response is highly appreciated.

Regards
Balu.

Hi Tom,
Pardon me for a question that is totally unrelated to this thread, but I couldn't help asking it. In our organization, we run Oracle on SCO Unixware 8.0, Linux (RHEL, and Fedora Core 6), and M$ Windows. I was obviously interested in reading up this ASYNC IO topic. I access your site on an XP laptop. Since I'm on a dialup, I usually save these individual topics (which can grow quite large) as MHT or HTML pages, so that I can go through these leisure, saving the cost of online time. Now since the time you have started "New AskTom", I find that I cannot save your pages locally. I end up with a blank file. Any reason? I'm sure there are millions of users on dialup who resort to this and who are affected.
Thanks for your time.
P.S. Is Ajax the culprit?

Hi Tom,

I have a large Oracle database; I am currently dealing with a possible DISK-CONTENTION problem. Every time I use the OS utility to check on the DISK QUEUE LENGTHS, I notice very high queue-length on one particular disk out of the ten other disk-devices. This disk is a RAID-5 device and has a mixture of DATA and INDEX files. It does not have REDO LOG FILES, UNDO or CONTROL files on it.

Could you please suggest how should I go about looking for -- what could be causing this IO contention here.., I could'nt see much in the STATSPACK reports or 10046 traces of the some program -- should I separate the INDEX from the DATA files? should I regularly montior the V$FILESTAT? Please suggest.

Thanks

Hello Tom

After reviewing the thread, I have a question:
if disk_asynch_io=true, but io slaves are set, so io slaves are ignored? That is what I think oracle will run without any io slaves.. Is that correct?
By the way, can i view the dbwr_io process under os process in unix?
Thanks!

Hi Tom ,
Thanks for the prompt answer..
I mean if io slaves are set and enable, if i use ps command , can io slaves be found and what do they look like?
In windows, I just cant find them ..

Hi Tom
thanks for the prompt answer, I got it..

But have a small confusion , as you said "Another way is to write the block to disk and not wait for the OS to tell ".

My question is , what happend if OS says I could not write the data. In this case could we loose the info.

Thank You
Adarsh Kumar

Tom,

In regards to writes when enabling asynchronous I/O, is there any risk in losing data when a write is issued and before the eventual confirmation of the write is received there is an server/storage outage? I read where Oracle doesn't consider the write complete until confirmation is received but it didn't address the risks in between submission and confirmation.

Thanks!!

Keith

Even though these set to
disk_asynch_io=TRUE
filesystemio_options=Async

but iam getting

Ioctl ASYNC_CONFIG error, errno = 1

WARNING:Could not set the asynch I/O limit to 64 for SQL direct I/O. It is set t
o 0

Any Idea?

Hi Tom,
How are you. Thanks as usual for your site/time and for university.

There is always a confusion about the term 'I/O' in Oracle, it could be logical or physical I/O. I could not interpret/understand the term I/O at various scenarios.

I would request you to explain as I know your wording and explanation clears all the air around the question.

Regards

Thanks Tom for your reply.
That was clear about IO interms of Logical and Physical.

May be I dint put the question in the right way, actually I am confused about the term IO, in general in Oracle, what exactly IO means, how to interpret it/how to understand the term IO.
Does IO mean reading a block from database to buffer or what it is. I am confused.
e.g: If some one says the IO is 440, what does it mean/ how to interpret.

Thanks for your university (The term you use in you blog)

Regards,
Pointers

Hi Tom

If RMAN backup to TSM/Tape is slow, can we set BACKUP_TAPE_IO_SLAVES=TRUE. Will it help. Please advice.

Thanks
Satheesh

graeat explanation tom, thanks for the clarification. but it's not clear that if there is a risk of data loss when using async IO. Let's say if the server crashes in the middle of writing a block and hasn't "call back" to tell that block 'X' is not written, will the data be lost ?

Thanks tom.

But In a "buffered filesystem I/O" that uses "Async IO", The data is written to the OS page cache. The file system and fsflush together write these data blocks to the stable storage at some later time. if the system crashes in the middle of writing out the dirty pages, How will oracle maintain the data integrity or will there be data loss ?