Chapter 6: Monitoring Space Usage 185
Segment Advisor
Frequent inserts, updates, and deletes on a table may, over time, leave the space within a table
fragmented. Oracle can perform segment shrink on a table or index. Shrinking the segment makes
the free space in the segment available to other segments in the tablespace, with the potential to
improve future DML operations on the segment because fewer blocks may need to be retrieved
for the DML operation after the segment shrink. Segment shrink is very similar to online table
redefinition in that space in a table is reclaimed. However, segment shrink can be performed
in place without the additional space requirements of online table redefinition.
To determine which segments will benefit from segment shrink, you can invoke Segment
Advisor to perform growth trend analysis on specified segments. In this section, we??™ll invoke
Segment Advisor on some candidate segments that may be vulnerable to fragmentation.
In the example that follows, we??™ll set up Segment Advisor to monitor the HR.EMPLOYEES
table. In recent months, there has been high activity on this table; in addition, a new column,
WORK_RECORD, has been added to the table, which HR uses to maintain comments about
the employees:
SQL> alter table hr.employees add (work_record varchar2(4000));
Table altered.
SQL> alter table hr.employees enable row movement;
Table altered.
We have enabled ROW MOVEMENT in the table so that shrink operations can be performed on
the table if recommended by Segment Advisor.
Pages:
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338