Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: new DirectX Oracle online monitor

Re: new DirectX Oracle online monitor

From: Howard J. Rogers <>
Date: Wed, 31 Mar 2004 06:03:51 +1000
Message-ID: <4069d2b3$0$16966$>

"Boaz Laufer" <> wrote in message
> >
> > Which is not necessarily such a brilliant move if your table is about to
> > subjected to new inserts such that it has to re-acquire the space that
> > rebuild caused it to lose, but which it already originally had.
> >
> > > * Fix Chained rows
> >
> > Nothing can fix a chained row except for the re-creation of the
> > You are talking about row *migration*, not row chaining.
> I refered to 'Migrater rows',
> Oracle treats both Migrated rows and chained rows as CHAINED ROWS.

No it doesn't. Both happen to be recorded under a column in dba_tables called CHAIN_CNT, but that's where the similarity ends. The fact that you don't know the difference is a worry.

> >
> > > * Compress the data inside the blocks (saves space and Cache-Memory)
> >
> > And increases the chances of user-collisions on a well-packed block and
> > thereby increase your chance of getting buffer busy waits.
> And SAVE SPACE in the Cache in case of tables that have 'select'
> statements
> (more data is in less blocks)
> To fix the problem that you're talking about b.t.w the way is to
> increase the freelists and not make a fragmented tables...
> (what are you talking about man??)

If you suffer from buffer busy waits, the thing to do is to decrease the number of rows stored inside a block. You can do that by setting PCTFREE higher. You could do it by increasing INITRANS before populating the table. But increasing the number of freelists will solve only one particular case of contention, which is contention for the head of the freelist, and is known as freelist contention, unsurprisingly enough. And define what you mean by "fragmented tables", since the term has no meaning.

> >
> > > * Rebuild the indexes - makes them more balances
> >
> > Oh God. Indexes in Oracle are ALWAYS ALWAYS ALWAYS balanced.
> > In real time.
> you have a mistake Mr.
> you are refering to the Btree that is automatically balanced but what
> about the unused space inside the blocks?

Yup, that gets re-used automatically, too.

> Unlike on a Table block, on Index blocks there's significant meaning
> to the place that the data sit do to the structure of the Btree,
> (that's why Indexes dont have PCT_USED parameter b.t.w because it
> doesn't make a difference)
> so with time, if index is updated, there are more and more empty and
> unused spaces inside the blocks,
> and that's why Rebuild of an index lowers its BLEVEL

You might care to read the chapter on indexes in "Beginning Oracle Programming" (available from Amazon at a depressingly cheap price). I wrote it.

If you are updating the key on which the index is built, then the space you free by one update is likely to be used, automatically and without intervention on your part, by subsequent updates.

> > I don't mean to be unkind, but you appear to be lacking a little in the
> > 'there's more to this than meets the eye' department. Sometimes an
> I never said that I know anything man
> (unlike you I guess)

I guess so. See, I like to trade in facts about Oracle. Not myths or half-truths. So I research a lot. You could do likewise instead of knocking up crummy tools that do bad jobs.

> > re-organisation might be beneficial. And sometimes it will make no
> > difference. But all times, there will be massive I/O costs and locking
> > issues. So you have to weigh these things up carefully on a case by case
> > basis, and you can't just blunder about with a sackfull of myths,
> > half-truths and complete misunderstandings to see you on your way.
> Reorganization almost almost (99.999%) of the times can improve the
> performance of the database (especially databases with a lot of
> transactions),

Oh well, we're clearly deep into "shut my eyes, block my ears, I'll just assert these things without any proof and pretend I didn't hear anything to the contrary" territory.

I'm afraid Boaz that your demonstrated knowledge about how Oracle actually works is in the bottom quartile. Which says something about your "product".

End of thread.
HJR Received on Tue Mar 30 2004 - 14:03:51 CST

Original text of this message