Re: Slow performance on index creation
Date: 1996/03/27
Message-ID: <4jac3s$b9_at_inet-nntp-gw-1.us.oracle.com>
ghp_at_infosoft.be (Gerard H. Pille) wrote:
>In article <4it36t$fu8_at_inet-nntp-gw-1.us.oracle.com>, Thomas J Kyte
>(tkyte_at_us.oracle.com) says...
[snip]
>!>megs logs myself. The goal is to remove the 'Cannot allocate new log' messages
>!>from your system.
>!>
>Thomas,
>Could you provide some explanation about the Oracle message? When do you get it
>precisely? And what do you think about 0racle7 Server Administrator's Guide
>saying that the "online redo log files should be 'relatively' small"? (my quotes).
>Or do you consider 25Mb small?
>--
>Kind reGards
> \ / |
> X |
> / \ s
> Gerard
As for 'relatively small' the admin guide is concerned with reducing the time to recovery. Assuming that checkpoints only occurr on log switches, if your log files are really big (hundreds of megs or gigabytes in size), it would take a really long time to restart after a system failure. I would say relatively small is something < 100 meg depending on your system. On Windows, it would take a really long time to roll forward through 100 meg of log and then rollback to recover. On a larger machine, 100 meg of log might not be very noticable. On really big systems I recommend 100meg logs. For example I worked on a 125gig system in which we used 10-100 meg log files (1 gig of log in 10 files). Relatively small is a function of your machine. On the HP in question, 25meg would not be out of question (I run a 2 cpu Sparc 20 using such a configuration).
Time to recover is constantly decreasing however. For example, 7.1 introduces parallel recovery. 7.3 introduces 'deferred' recovery (database starts right up, tablespaces come online as they are recovered. Some of the database will be ready right away, other parts come on line as they are recovered).
As for the checkpoint not complete it is due to the way Oracle handles log (redo) and rollback (undo) and dirty database blocks.
Lets say you have 2 512k logs. When you fill the first log a checkpoint will be started. The checkpoint will flush all dirty blocks in the SGA to disk. When this checkpoint completes, the first log file is no longer needed for recovery since the log only contains REDO and the blocks flushed no longer need REDO but only need UNDO. UNDO is stored in the rollback segment and would be flushed as part of the checkpoint.
Lets say though that the logs are filling so fast that when that before the first checkpoint above can complete, the second log fills up as well (they are small and will fill up FAST....). We now need to switch back to the first log file. But we can't cause when we finished log 2, we initiated a new checkpoint that cancelled the first and now we have to flush all dirty blocks including the ones dirtied in log 2. Thats when the database will issue 'Checkpoint not complete' and everything will freeze, queries, inserts, everything... Until the checkpoint completes.
Then we can reuse the first log and all will be well until we get to the end of the second log again........
opinions and statements are mine and do not necessarily reflect the opinions of Oracle Corporation. Received on Wed Mar 27 1996 - 00:00:00 CET