Re: performance problems after increasing redolog size

On Wed, 05 Mar 2003 13:53:25 +0100, Bud Socks wrote:

>> You should have 8. I hope you have multiplexed.

If I said to you "I don't need to do backups, because I use RAID 5"... would you think that was very sensible? (And it's not a daft question, because I've met people who stated precisely that, and with a totally straight face).

It is of course a ludicrous proposition. RAID of whatever flavour (apart from 0) gives you tolerance against hardware failure. It does not protect you against user error or software corruption. Only multiplexing can do that. So yes, you still need to multiplex. I'm still lobbying Oracle to make multiplexing compulsory, because it ought to be.

As for performance woes, I really don't have much objection these days to anyone using RAID 5 for their datafiles, because Oracle delays the writes to those anyway, and the impact on performance, or the user perception or performance, is therefore usually pretty minimal.

But under no circumstances should RAID 5 be anywhere near your redo logs. You are artificially slowing down LGWR, and that's directly observable by users, and translates directly into poor performance.

By the way: get the terminology correct. You have 4 log GROUPS. You have 1 member per group. Multiplexing means having two or more members per group.

That aside, let's just summarise. You had an import run in 2 hours when redo logs were just 1MB big. You had too many log switches, though, and increased the size of all your logs to 10MB. Now the import runs in 24hrs.

How many log switches were you getting, and how many are you getting now? What's the size of your LOG_BUFFER parameter (it shouldn't be bigger than 6MB at tops)? What does the alert log look like when the logs switch these days? Have you run statspack (or utlbstat/utlestat) to analyze what waits are happening on your database during the import process? What version of Oracle is this? Do you have multiple archiver processes spawned, or have you set a limit on the number that can be spawned? What else is sitting on the disks where the redo logs are located?

Lots of questions, and these are just the start. Point is, there's not really enough information to go on. It just can't be the case, though, that bigger logs cause a performance drop. Something else is going on, and I'd recommend statspack as your first tool to diagnose what.

Alternatively, here's something you could try: put the redo logs back to 1MB and see how long the import takes then. My bet is that it will still take 24 hours.

Regards
HJR
>
> A server consolidation will be accomplished within the next 3 month. On the
> new platform i will follow the Oracle file distribution as recommended but
> now
> i have to deal with that situation and as you can imagine i am not very
> exited.
>
>
> Thanks for help
>
> Bud
Received on Wed Mar 05 2003 - 13:36:11 CST