We previously discussed the logical and physical components of the database and identified that objects such as tables and indexes are stored logically in units called Segments.

A Segment is composed of a collection of smaller logical units called Extents. Segments grow automatically, such as when rows are added to Table Segments

or Index keys are added to Index Segments. Oracle adds new Extents to Segments as needed when the Segment becomes full.

When performing DML operations on rows, sometimes deletion and modification operations on the rows occur, which can result in some empty blocks within the Segments due to the deletion of some rows, for example. These gaps will be reused later through INSERT statements, but it is possible that the table will use more space than required

for its current contents. Additionally, DML operations on the rows impact the Index Segments associated with those rows, and the effects could be worse on the Index Segments than on the Table Segments. The algorithm that Oracle relies on for reusing empty spaces in the Index Segments leads to the creation of a large amount

of wasted space that cannot be reused.

The Segment Advisor will examine the Segments and determine whether the amount of unused allocated space is large enough to justify applying shrink operations.

The algorithms used do not entirely depend on the amount of wasted space; focus will also be placed on the activity of the Segments. The advisor can access the AWR to retrieve historical information about the movement of the Segment, as there might be significant activity on the table involving insert and delete operations. Therefore, the advisor, in such cases, will not recommend shrinking this Segment.