Re: intermittent very high waits in LGWR on Linux?

bugbear apparently said,on my timestamp of 30/06/2005 10:50 PM:

>>
>> strace?  This is Linux? 

<note to self: Self, READ the subject lines!!!> <blush>

> Selected goodies from dmesg:
> Linux version 2.4.22-32mdkenterprise (qateam_at_updates.mandrakesoft.com)
> (gcc version 3.3.1 (Mandrake Linux 9.2 3.3.1-2mdk)) #1 SMP Tue May 18
> 03:15:26 MDT 2004
> Calibrating delay loop... 3643.80 BogoMIPS
> Memory: 1266644k/1294272k available (1643k kernel code, 18924k reserved,
> -2288k data, 176k init, 376768k highmem,
> CPU0: AMD Athlon(tm) XP 2500+ stepping 00

Kewl, no apparent problem there.

> filesys EXT3

Content of /etc/fstab?

> There are lots of "*init*ora", but I think
> I've found the right ones

Sorry, I should have provided more info. 9.2/dbs/initscan.ora sounds like the right one: it usually is in either $ORACLE_HOME/dbs or a symbolic link to it is there. Make sure the database is not using a SPFILE (binary) in which case this file is irrelevant.
To check, log on to sqlplus as sysdba and do:

select * from v$spparameter
where value is not null;

If that returns no rows you are using a pfile instead of a spfile and the following is valid. Otherwise, you have to do all the work using Enterprise Manager (and that is another pain...)

> (9.2/dbs/initscan.ora)
> (decommented for space)
> db_block_size=8192

***bzzt> db_cache_size=33554432
make it =50M at least
> db_file_multiblock_read_count=16
> open_cursors=300
> db_domain=################### (not shown on USENET)
> db_name=scan
> background_dump_dest=/home/oracle/admin/scan/bdump
> core_dump_dest=/home/oracle/admin/scan/cdump
> timed_statistics=TRUE
> user_dump_dest=/home/oracle/admin/scan/udump
***bzzt> control_files=("/home/oracle/oradata/scan/control01.ctl",
> "/home/oracle/oradata/scan/control02.ctl",
> "/home/oracle/oradata/scan/control03.ctl")
if you are putting all your control files in the same directory, there is no need to triplicate them, one only is plenty! Multiple control files is for multiplexing to avoid data loss. Right now you are doing a "tobesure" thrice...
> instance_name=scan
> job_queue_processes=10
> dispatchers="(PROTOCOL=TCP) (SERVICE=scanXDB)"
> aq_tm_processes=1
> compatible=9.2.0.0.0
> hash_join_enabled=TRUE
> query_rewrite_enabled=FALSE
> star_transformation_enabled=FALSE

***bzzt> java_pool_size=83886080
if your java client is not inside the database, drop this to 0
> large_pool_size=16777216

***bzzt> shared_pool_size=83886080
get in the habit of specifying these sizes as M, in this case = 80M ***bzzt> processes=150
a bit too high for a small test? Drop down to 50?
> fast_start_mttr_target=300
> remote_login_passwordfile=EXCLUSIVE
> pga_aggregate_target=25165824

***bzzt> sort_area_size=524288
when you use pga_aggregate_target, this one is redundant: drop it.
> undo_management=AUTO
> undo_retention=10800
> undo_tablespace=UNDOTBS1

and now add these:
log_buffer=1048576
log_checkpoint_interval=10000
db_writer_processes=2

and make triple sure you have "noatime" as one of the mount parameters for file system where all dbfiles are.

Check the size of your redo log files. If they are anything less than 100Mb, make them that size (you need a min of 3).

You can do that by allocating additional ones of the right size then dropping the old ones, check the SQL manual for that (ALTER DATABASE): http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/toc.htm (you will need an email to register, it's ok: they don't spam) If it doesn't let you drop a logfile in use, ALTER SYSTEM SWITCH LOGFILE toggles to the next one. Of course: you need dba powers for all this.

While you are at it go to tahiti.oracle.com, it's a front-end for all manuals. Look for 9i ones, Concepts manual: it's a leisure read for the weekend and it will 'splain a lot. Same for Application Developer's Guide and the JDBC User's Guide. All the params above are on Reference manual if you feel curious.

Anything else I can help with ping me on the email, I'm nursing databases this weekend and cannot be on Usenet.

> Note - I don't expect "great" performance; we're just using machines
> we "have around" to amke sure we get the "right answers" under testing.
> For actual deployment, we'll be using rather bigger hardware,
> with rather more spindles than the current 1 ;-)

Yup, 1 f/s is a killer for 9i. You definitely should put redo logs somewhere else. 10g does all sorts of default magic to get around this problem, hence why you are likely to not feel it. 9i just needs a bit more TLC.

> But we would like our test to finish!!

Fair enough! :)

-- 
Cheers
Nuno Souto
in wet Sydney, Australia
wizofoz2k_at_yahoo.com.au.nospam