Re: [?] no logging mode

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Fri, 30 Jan 2004 09:05:31 +1100
Message-ID: <401983ac$0$4259$afc38c87@news.optusnet.com.au>

"gp" <gieppetto_at_tiscali.it> wrote in message news:bvbagj$da9$1_at_carabinieri.cs.interbusiness.it...
> ok,
> excuse me;
>
> i have increased the log_buffer parameter because i have 'redo log space
> requests = 381'
> and i have read that the solution for this problem is to increase
> log_buffer;
> i have set nologging mode for my tablespaces to increase performance
because
> i have very lot transactions;
>
> thanks.

Nothing to excuse. It's not your fault if these things happen. But have a read of Sybrand's post. He's quite right. Take the size back down to something sensible (like 512K - 1MB), and then start collecting meaningful statistics. You can't just query from a v$ and say you have a problem, because they are cumulative since instance startup. 381 might be really bad news if you only started your database 2 minutes ago. But if it's been up for a week, then it is quite probably totally insignificant. You need to collect statistics *over a defined period of time*, which is where statspack or utlb- and utlestat scripts come in.

As a general point, whenever you are trying to tune an Oracle database, try to think of memory as they did chocolate during the war: it's extremely scarce, and you don't want to use too much of it up. Too many people throw memory at the problem first, in the hope that it will go away all on its own. Log buffer space events *can* be cured by increasing the size of the log buffer *if the log buffer is too small*. But if it's already a reasonable size, then a further increase will not help. It also happens to be the case that all sorts of other things can contribute to log buffer events: stick all of your redo logs onto one hard disk, the one shared by all your data files, and one which happens to be the c:\ of the company's only print server, and you'll also get them, because when LGWR doesn't get a chance to write to the drive, it can't clear the buffer quickly enough, and therefore it fills up, and people start waiting. You also get these wait events because *DBWR* is having a hard time flushing things to disk and/or your physical redo logs are too small -if DBWR can't flush, checkpoints take for ages; but you can't over-write an earlier online redo log until it's beencheckpointed; so if you switch logs too quickly, LGWR will be unable to flush, meaning the log buffer gets full, and people start waiting. It can even be that a lazy ARCH process causes log buffer events, for the same reason: in archivelog mode, you can't over-write an online log until it has been archived, but if ARCH is lazy you might switch round your online logs fast than ARCH can archive them, meaning there will come a point where LGWR can't flush any further, the log buffer fills up, and people start waiting.

So, if you get these events, it could be too small a log buffer. But it is

quite likely, too, to be poor I/O performance generally, misconfiguration
slowing down DBWR, misconfiguration slowing down LGWR, misconfiguration
slowing down ARCH, too small redo logs... you get the idea. It could be

anything. You have, however, successfully proved that the size of the log buffer itself is not the issue, because if you're still getting the problem with a 16MB log buffer, it clearly isn't a sizing issue.

Use of nologging to reduce the amount of redo generated by transactions was a good idea, in the sense that yes -one of the things you can do to help alleviate the pressue on the log buffer is to generate less redo in the first place. The trouble is, as I said before, nologging is completely ignored by regular transactions. So, nice idea, but that's not going to work.

So: back to the real problem. Tune your database. Read up on statspack, or -if you're running something earlier than 8i- utlbstat and utlestat. Visit www.oraperf.com when you have a statspack report, and have them analyze it for free. But look to your I/O generally: it sounds like you have an I/O contention problem somewhere.

Regards
HJR

-- 
------------------------------------
Oracle insights at www.dizwell.com
------------------------------------

Received on Thu Jan 29 2004 - 16:05:31 CST