Re: Sophisticated Performance Tuning: Need Wizard!

In article <4bo6m6$s6a_at_aladdin.iii.org.tw>, root_at_corolla.tatung.com.tw says...
>
>Hi Wizards,
> We recently have to tune the fat ORACLE 7.1 on DEC Server 2100(alpha)
>/OSF1 3.0 and have the following questions:
>
> 1. We currently use file system and find it's very slow to INSERT,
> any techniques found on ORACLE manuals are carried out but in vain,
> should I 'kill' the UNIX buffer cache to bypass ORACLE synchronous
> write or decrease the UNIX system flush rate?

Is your I/O balanced? This is most likely the culprit. Make sure you minmize the Unified Buffer Cache (UBC). In /etc/sysconfigtab:

	vm:
		ubc-max-percent=2
		ubc-min-percent=1

Are you using ufs or the Advanced File System? AdvFS currently pipes all file activity through CPU 0. If you're in an SMP emvironment on Digital UNIX V3.2D or less, consider using ufs. Then when this restriction with AdvFS is lifted with V4.0, move back to AdvFS.

You might consider bypassing ufs and using raw devices. Some people have seen  improvement. Some haven't.

> 2. How can I know the exact size of the System Global Area that will
> not be paged out? Is your I/O balanced?

The UNIX command ipcs -b shows shared memory and semaphore usage. Make sure you size the SGA so it doesn't page out. I don't use Informix and can't advise.

> 3. ORACLE 6.* use (db_block_write_batch/2) as a 'high water mark'
> (threshold) for db writer to write dirty database blocks to disks.
> Is this still true for ORACLE 7.1?

This parameter is obsolete. See the Oracle7 Server Migration Guide, Appendix A. Check out db_block_checkpoint_batch. Consider starting the checkpoint process (init.ora parameter checkpoint_process=true) so that the log writer is dedicated to redo entries only. Also check out log_checkpoint_interval and log_checkpoint_timeout to better manage checkpoint intervals.

>BTW, if I set db_block_write_batch
> large will Oracle automatically use asynchonos(parallel) I/O?

To get asynch I/O, set the value of the init.ora parameter asynch_write to 1.

-- 
Lisa Roderick
Applications Systems Engineering Performance Group
Digital Equipment Corporation
Nashua, NH
lisa.roderick_at_zko.mts.dec.com