Too slow oracle db-writer activities.

[Janghyegyung]

I had the problem of oracle 7.3.4 about disk I/O performance on HP-UX 11.00. After discussing with DBA, we took actions that we placed on separate disks oracle redo log files, index data files.

But still the problem isn't clear. When oracle is working on batch program, for instance, deleting 10000 data rows, I/O workload is too much. Even though we expect performance is not fast as normal time when doing heavy activities, this case is seemed to be abnormal, we think.

As a comment by DBA, it takes too much time for oracle db-writer to write on disk and it is likely to have no problems about rollback segment and archive file writing.

Where do I have to start?
patche? kernel parameter? Confused.

I'd be really appreciate if you give me any hints or thoughts.

[Animesh Chakraborty]

hi,
If you using raw filesystem then you may consider about configuring async I/O
For more details see the link
http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0xb1e8660142b2d5118ff10090279cd0f9,00.html

[Steven Sim Kok Leong]

Hi,

One kernel parameter which you might want to look at for filesystem I/O performance at the risk of inconsistency is fs_async.

fs_async specifies whether or not asynchronous writing of file-system data structures to disk is allowed. By default, synchronous writes are used.

Asynchronous writes to disk can improve file system performance significantly. However, asynchronous writes can leave file system data structures in an inconsistent state in the event of a system crash.

In addition, are you using HFS or VXFS (JFS)? JFS performs faster than HFS in terms of I/O. Of course if you can look at using RAW devices for your database, that will be even more efficient than JFS.

Hope this helps. Regards.

Steven Sim Kok Leong

[Printaporn_1]

did DBA try to increase database buffer cache in SGA or enable multiple database writer process.

[Andreas D. Skjervold]

Hi

You might look into configuration of the dbwriter:
1: It is possible to configure multiple I/O slaves to a dbw process using the parameter DBWR_IO_SLAVES. This only applies if you only got ONE dbwriter (dbw0).
2: It is possible to set up several dbwriters using parameter DB_WRITER_PROCESSES.

I think both these was introduced with Oracle8, but I think you have somthing like it in Oracle7, but not beeing able to do async I/O, called DB_WRITERS

If you use DBWR_IO_SLAVES, only ONE database writer process will be used, regardless of the setting for DB_WRITER_PROCESSES.

Andreas

[Stefan Farrelly]

If you run a sar command like; sar 1 100
while the db writing is going on, whats the wio% ? If its >10 then your disk subsystem simply cant keep up - you will need to investigate some hardware upgrade as your strictly I/O bound - mucking around with the dangerous async i/o and oher oracle/hp-ux parameters wont help much.

[Jack Fan]

1. Separate data and index file on different disk. Also please ensure those disk on same performance disk.
2. Turn off archive log mode.

Probably have help to you!

Jack Fan

[Alexander M. Ermes]

Hi there.
You have several opportunities about your setup. Try to keep away from each otehr on the disks ( each on separate disk ) :
data tablespaces
index tablespaces
online log files
rollback tablespace

If any two of these are on the same disk, they might have influence on the i/o performance.

Next :
How often are your logfiles changing ?
If the average is less than five minutes, enhance the size.

Did you try to spread your tablespaces on different disks / controlers ( only if you have enough ) ?

The database buffer parameter should be checked as well ( db_buffers ).

db_writers have already been mentioned.

Try to setup multithreaded servers, if your machine capacity is not that much.
I have attached an init.ora file, that we used for a 7.3.4 database.
Rgds
Alexander M. Ermes

[Volker Borowski]

Hi,

if this occurs only with a job that does a lot of updates, I doubt, that async-io and more db-writers are a way out. I would take this appoach, if you have an overall impression of bad performance.

First I would like to say: review the program code. Even updates may perform much better, if statistics are up to date, or if updates are done with where clauses other that the primary key, additional indexes may help.

Second: check your redologs. In 7.3.4 each logswitch forces a checkpoint, which means the entire list of dirty blocks is flushed to disk. Now if you have a big SGA, this might take some time. A way out would be to enlarge online redologs. I.e. if you have 1MB logfiles and you have 10 logswitches a minute during this job, enlarging the redologs to 10MB would save 9 checkpoints each minute !

Third: If this does not work, you should consider to SQL-Trace the job, to find out which is the bad statement.

Hope this helps
Volker

[Dennis J Robinson]

We need to see the following during your time in crisis:

Need init.ora settings for this instance.

output from a 'sar -d 5 100 > outfile.txt', mail the outfile.txt to me at djrobins@flash.net.

Then do the utlbstat.sql and utlestat.sql and mail report.txt output over a 2 hr span of your performance issue.

Without looking at your configuration, you need to make sure that:

db_block_buffers is not one of the Oracle default values.

That you utilize multiple db_writers, try at least one db_writer per CPU.

db_block_lru_latches should = 2*db_writers

log_checkpoint_interval and log_checkpoint_timeout should be set so that log switches occur at a minimum of 15 minutes.

log_buffer should not be larger than the redo log size.

Some mount parameters have shown to help with writer performance.

A mount line as such:

/dev/vgoracle_DB1 /dba/u02 vxfs delaylog,mincache=direct,convosync=direct,largefiles 0 2

WIll help with filesystems that have high write rates, not too many table scans. Filesystem performance such as backups, cp, gzip will suffer though.

A mount line such as:

/dev/vgoracle_DB1 /dba/u02 vxfs delaylog,convosync=dsync,largefiles 0 2

WIll help for heavy write filesystems, where filesystem level performanc will not suffer dramatically. convosync=dsync turns syncronous writes into data syncronous writes, which in english means the inode can be updated at a later more convenient time.