| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Index size growing abnormally .....Oracle bug ?
You should use DELAYED_LOGGING_BLOCK_CLEANOUTS in init.ora. From the online manual:
"
DELAYED_LOGGING_BLOCK_CLEANOUTS
Parameter type:
boolean
Parameter class:
static
Default value:
TRUE
Range of values:
TRUE/FALSE
OK to change:
yes
Multiple instances:
need not be
identical
DELAYED_LOGGING_BLOCK_CLEANOUTS turns the delayed block cleanout feature on or
off. This reduces pinging in an Oracle Parallel Server. Keeping
this feature set to TRUE sets a fast path, no logging block cleanout at commit
time. Logging the block cleanout occurs at the time of a subsequent change to
the
block. This generally improves Oracle Parallel Server performance, particularly
if block pings are a problem.
When Oracle commits a transaction, each block that the transaction changed is not immediately marked with the commit time. This is done later, on demand, when the block is read or updated. This is called block cleanout.
When block cleanout is performed during an update to a current block, the
cleanout changes and the redo records are appended with those of the update. In
previous releases, when block cleanout was needed during a read to a current
block, extra cleanout redo records were generated and the block was dirtied.
This
has been changed.
When a transaction commits, all blocks changed by the transaction are cleaned
out immediately. This cleanout performed at commit time is a "fast version"
which
does not generate redo log records (delayed logging) and does not re-ping the
block. Most blocks will be cleaned out in this way, with the exception of blocks
changed by long running transactions.
During queries, therefore, the data block's transaction information is normally
up-to-date and the frequency of needing block cleanout is greatly reduced.
Regular
block cleanouts are still needed when querying a block where the transactions
are still truly active, or when querying a block which was not cleaned out
during
commit.
Note: In long-running transactions, block cleanouts will not be performed during the transaction. If the transaction is not long running, block cleanout will be performed and the block cleanout is logged at the change of block.
During changes (INSERT, DELETE, UPDATE), the cleanout redo log records are generated and appended with the redo of the changes. "
Martin
DNP wrote:
>
> Hello all --
>
> Question - which optimizer hint would be the best at getting Oracle to
> do some sort of scan on the entire index (to touch all the index blocks,
> branch and leaf) to solve the delayed block cleanout problem after many
> deletes?
>
> David P.
>
> -------------------------------------------------------------------
>
> Jonathan Lewis wrote:
> >
> > Just tested it.
> >
> > The answer is yes.
> >
> > As a side effect,
> >
> > update test_reuse set idx_col = idx_col + 0.5
> > where idx_col = 100;
> >
> > can cause a block to split if the block is full and
> > the update entry is supposed to stay in that block.
> >
> > Then I found that things could be worse when
> > the consequences of fast commit and
> > delayed logging block cleanout apply.
> >
> > More details when I have investigated fully.
> >
> > --
> >
> > Jonathan Lewis
> > Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
> >
> > Connor McDonald wrote in message <38C9BF80.7D82_at_yahoo.com>...
> > >
> > >I've always thought that if you deleted (even all of the) entries from
> > >an index block then it only becomes available for re-use for insert
> > >after the delete has committed.
> > >
> > >Is this true ?
> > >
Received on Tue Mar 21 2000 - 00:00:00 CST
 |
 |