Re: new DirectX Oracle online monitor

> I refered to 'Migrater rows',
> Oracle treats both Migrated rows and chained rows as CHAINED ROWS.

Oracle does not treat a migrated row and a chained row the same. You will find both summarized in the CHAIN_CNT column of DBA_TABLES, however, they are two different things.

> > Oh God. Indexes in Oracle are ALWAYS ALWAYS ALWAYS balanced. Automatically.
> > In real time.
>
> you have a mistake Mr.

Sorry, but it is you that is mistaken. Indexes are always balanced. What you are not guaranteed is that the leaf entries will be balanced between the left and right subtrees. But there will always be a balance in the same number of blocks.

> you are refering to the Btree that is automatically balanced but what
> about the unused space inside the blocks?

What about it? It gets used if you let it.

> 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

Yes, lowering the height of a B-tree index can improve performance. But it can also make performance worse. When you rebuild an index, you essentially remove all of the deleted leaf entries in the B-tree index. What happens when you want to insert a new row of data into one of those B-tree leaf blocks? Guess what? It is full. Therefore a block split must occur. And this block splitting can propagate up the tree. Thus causing a performance problem that didn't exist before the index was rebuilt. So in some cases you may see an improvement in performance after you rebuild an index, and in other cases, you will see a decrease in performance.

> Reorganization almost almost (99.999%) of the times can improve the
> performance of the database (especially databases with a lot of
> transactions),

I would disagree with this statement. If you have set your database up correctly, i.e. correct block sizes, correct storage parameters (PCTUSED, PCTFREE), then the simple act of performing a reorg should not make any difference to your application performance. If a reorg does help, then you need to find out why so that you can take steps to stop wasting valuable DBA time, and valuable database uptime.

> sometimes it makes no difference but it can almost almost never make
> the performance worst (unlike other ways of tunning like SQL's,
> parameters and so..)

Yes, a reorg can actually make performance worse. See rebuilding indexes above. When you reorg, you may also be recalculating stats, which can have a positive or a negative affect. You also have a chance of human error in a reorg. This can lead to unnecessary downtime. A production database that is not up and running when it should be has terrible performance!

> I'm not saying that Reorg is the ultimate solution but this is a good
> thing to do (and plan the downtime for the Reorg of course!)

IMO, regularly planned reorgs are not needed and provide little benefit. And I really don't see a need to do them. If your reorg is improving performance, then figure out *why* and address that specific issue. A periodic reorg should not be done just to improve performance. In today's 24x7 environments, or with today's VLDB's, maintenance windows for periodic reorgs just do not exist.

Cheers,
Brian

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"