A couple of points in-line.
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Iceland__November (tbc)
____Belgium__November (EOUG event)
____UK_______December (UKOUG conference)
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA__October
____UK___November
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
"Robert" <grobert_at_azet.sk> wrote in message
news:a8497ec7.0310152339.40ba3dc2_at_posting.google.com...
> Hi everybody
>
> On ORA-01555 I found workaround which I did not found on any other
> places. Here it is.
>
> --------------------------------------------------------------------
-----------
> ...
> ...
> ...
> There are also a few things that you can do to minimize the
> probability of such a block being flushed out without a commit (and
> with that avoid a delayed block cleanout of having to happen).
>
> a. The main trigger for blocks being flushed out is checkpoints.
Checkpoints do not 'flush' blocks from the buffer, they
write them to disc and mark them as clean in memory.
The blocks are still in the buffer and can still be marked
with a nologging commit.
Note also, that there is a limit on how many blocks can
actually be marked on commit, so even if no blocks are
'flushed' out of buffer there may still be many blocks that
will still be subject to delayed block cleanout.
> Make sure the checkpointing frequency is not too big. Usually the
> size of the redologfiles should be set up so that log switches
> happen about every 4 minutes during peak load time. If
> logswitches happen more frequently, consider increasing the size
> of the redologfiles.
>
> b. Avoid incremental checkpointing
>
Generally a bad suggestion. Incremental checkpointing
helps to avoid peaks in I/O activity.
> c. Avoid incremental checkpointing. Incremental checkpointing is
> forced by the following parameters
> - log_checkpoint_timeout -->set it to 0 to disable it
> - log_checkpoint_interval-->set it to >= 3000000
> - fast_start_io_target -->set it to 0 or the number of
> db_block_buffers
> - fast_start_mttr_target -->set it to 0
> ...
I doubt if setting the log_checkpoint_interval to a high value
disables incremental checkpointing - although all your redo log
files are larger than the number of blocks you've declared in
log_checkpoint_interval then it might appear to have stopped it.
Note that one of the suggestions contains the explanation of
why you might not want to follow the advice.
mttr = mean time to recover.
By setting fast_start_mttr_target to zero, you disable the
feature that tries to give you a fast recovery.
As a quick check on how relevant this advice may be, there
is a statistic called something like
transaction tables consistent read - undo records applied.
and another one which tells you how many transactions
tables CR blocks have been created.
If you see high numbers for the former, then you may get
some performance benefit by dealing with the issue of
dirty blocks being subject to cleanout by long running transactions
a long time after the blocks were made dirty. Typically the
solution is a tablescan and associated index scans immediately
after a massive load/update.
> ...
> ...
> --------------------------------------------------------------------
-----------
> I would like to know opinion of some expirienced DBA.
>
> Thanks
> Robert
Received on Fri Oct 17 2003 - 14:35:42 CDT
