orawin.info

Just over 9 years ago now, before usenet died , I posted a list of common myths regarding the Oracle RDBMS. You can find that list and the ensuing discussion here. Number 2 in my list was

Free Space in an index is never reused.

At the time it was commonly thought that indexes needed regular rebuilding to avoid fragmentation. The best work I am aware of that describes why this is not the case is Richard Foote’s Index Internals: Rebuilding the Truth paper. Over time more and more people have come to quote this work and it is often cited in discussions on the forums at OTN and elsewhere.  Recently I have noticed a tendency for people to produce answers like the one in this answer. Namely

In Enterprise Manager, the section named “Segment Advisor” can to help you <decide what indexes need rebuilding>

This it seems to me is a fundamental misunderstanding of what the segment advisor is showing you, and sadly is my second myth rising anew from the dead. The official documentation on the segment advisor describes it as

Over time, updates and deletes on objects within a tablespace can create pockets of empty space that individually are not large enough to be reused for new data. This type of empty space is referred to as fragmented free space.

Objects with fragmented free space can result in much wasted space, and can impact database performance. The preferred way to defragment and reclaim this space is to perform an online segment shrink. This process consolidates fragmented free space below the high water mark and compacts the segment. After compaction, the high water mark is moved, resulting in new free space above the high water mark. That space above the high water mark is then deallocated. The segment remains available for queries and DML during most of the operation, and no extra disk space need be allocated.

You use the Segment Advisor to identify segments that would benefit from online segment shrink. Only segments in locally managed tablespaces with automatic segment space management (ASSM) are eligible. Other restrictions on segment type exist. For more information, see “Shrinking Database Segments Online”.

If a table with reclaimable space is not eligible for online segment shrink, or if you want to make changes to logical or physical attributes of the table while reclaiming space, then you can use online table redefinition as an alternative to segment shrink. Online redefinition is also referred to as reorganization. Unlike online segment shrink, it requires extra disk space to be allocated. See “Redefining Tables Online” for more information.

In other words the segment advisor is all about reclaiming space. But a B*-Tree index will always tend to have free space within it – and will nearly always be able to reuse that space. Fragmentation as described above, that is unusable free space, does not apply to B*-Tree indexes in general in Oracle (the point of my previous post was to show one situation where it can be an issue) . To run the segment advisor on an index then is fundamentally misguided. As the next section describes the advisor is really intended for objects that actually store data (most commonly heap tables).

The Segment Advisor generates the following types of advice:

• If the Segment Advisor determines that an object has a significant amount of free space, it recommends online segment shrink. If the object is a table that is not eligible for shrinking, as in the case of a table in a tablespace without automatic segment space management, the Segment Advisor recommends online table redefinition.
• If the Segment Advisor determines that a table could benefit from compression with the OLTP compression method, it makes a recommendation to that effect. (Automatic Segment Advisor only. See “Automatic Segment Advisor”.)
• If the Segment Advisor encounters a table with row chaining above a certain threshold, it records that fact that the table has an excess of chained rows.

Please don’t lets reinvent the old myths – there’s plenty of room for new ones out there.

Possibly Related Posts:

• Monotonically Increasing Indexes
• When is a Health Check not a Health Check

It is highly recommended to have at least two copies of the controlfile. This can be done by mirroring the controlfile, strongly recommended on different physical disks. If a controlfile is lost, due to a disk crash for example, then you can use the mirrored file to startup the database. In this way fast and easy recovery from controlfile loss is obtained.

connect as sysdba
SQL> select status, name from v$controlfile;

STATUS NAME

------- ---------------------------------

/u01/oradata/L102/control01.ctl

/u02/oradata/L102/control02.ctl

The location and the number of controlfiles can be controlled by the
‘control_files’ initialization parameter.

I’m rather a pedant so would rather the document be described as a “Best Practices” or “Guidelines “document than a how to. A How to document would reword the above as

Ensure that the database has at least 2 controlfiles located on different disks by reviewing the ‘control_files’ initialisation parameter.

I.E it told you How to do something, hence the name. But my beef isn’t really that this is yet another Best Practices document, but rather with what it leaves out. This is perhaps best illustrated by referring you to this forum thread. A health check that doesn’t ensure recoverability is no health check.

Possibly Related Posts:

• Index Rebuilds – A new Myth arrives