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: Boaz Laufer <>
Date: 30 Mar 2004 23:45:10 -0800
Message-ID: <>

"Howard J. Rogers" <> wrote in message news:<4069d2b3$0$16966$>...
> "Boaz Laufer" <> wrote in message
> > >
> > > Which is not necessarily such a brilliant move if your table is about to
> be
> > > subjected to new inserts such that it has to re-acquire the space that
> your
> > > 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
> database.
> > > 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.

I do know the difference,
you dont understand what I'm writing to you maybe you need to take a course in English and not Oracle both courses would help you a lot

> >
> > >
> > > > * 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.

Sorry I ment PCTFREE and not FREELISTS

> > >
> > > > * Rebuild the indexes - makes them more balances
> > >
> > > Oh God. Indexes in Oracle are ALWAYS ALWAYS ALWAYS balanced.
> Automatically.
> > > 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.

I did the following test:
* Created a table
* Created an index on the table
did a loop that does this:
-> insert 10000 rows into the table (and also in the index) -> delete the 10000 rows (from table & index)

Every time after I run the Pl/SQL block I checked the High watermark of the Table and the Index.


the HWM of the table was the same
the HWM of the Index was higher after every run

why does this happen????

because unused spaces inside Index Blocks dont get reused allways (the chance that they'd get reused is smaller than this chance in a Table-block)

check it out for yourself

> > 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
> object
> >
> > 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.

Oh boy,
You have a big problem man
Maybe you have some knowledge about Oracle but you are a real sociopath
'Crummy tools'?
Maybe you need to see the 'crummy tool' before you decide that it's a 'crummy tool'
Actually maybe not a good idea…
I prefer it if you don't
Stay away from my ‘crummy tool'!

> > > 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".

Actually I developed the product 'Space Tuner' (for Database Reorg) when Oracle version was 8.0.5 (and did not have the 'ALTER TABLE MOVE' command)
On this time Reorg was very hard thing to do The program creates a script that does CREATE AS SELECT to all the tables
and than rebuilds the indexes, and re-creates the constraints, and recompiles the dependencies in the right order, and supports LONG and LONG RAW as PL/SQL blocks to do the copy and more and more features.

On that time Quest developed the 'SPACE MANAGER' program that did the same thing (create the reorg scripts) only their product cost 10,000$ per instance more or less and mine cost 100$

a lot of people that used my product gave me a very good feedback on it and liked it more than Quest's Space Manager Received on Wed Mar 31 2004 - 01:45:10 CST

Original text of this message