Using Segment Advisor

For each segment, Oracle will put a boundary (i.e. Highwater Mark) that will separate between used blocks and free blocks. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. In normal DB operations, the high water mark only moves upwards, not downwards. If there is a lot of free space below the high water mark, one might consider to use alter table move statements. In another words, we have to shrink or compact the segment to move HWM downward. This condition will make full scan segment more faster.
We will try to use segment advisor to make our segment can be optimized based on the statistical data of our segment.

SQL> create table uji2 (id number(4), nilai varchar(1000));

Table created.

SQL> begin
2 for i in 1..10000 loop
3 insert into uji2 values (i, lpad('*', 1000, '*'));
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> update uji2 set data = null where mod(id, 5) = 0;

200 rows updated.

SQL> commit;

Commit complete.

SQL> set autotrace traceonly
(if you have any problems with this command, please refer to http://www.samoratech.com/tips/swenableautotrace.htm)

SQL> select count(*) from uji2;
(Summary)

SQL> alter table uji2 enable row movement;

Table altered.

SQL> alter table uji2 shrink space;

Table altered.

SQL> select count(*) from uji2;
(Summary)

Based on our practice, you can see that after shrink space, the amount of blocks that was reading have decreased, because the HWM was downwarded.

Now, we will use Segment Advisor from Enterprise Manager to get an advice about our segment.

SQL> create table uji2 (id int, data char(1000));

Table created.

SQL> insert into uji2 select object_id, lpad('*', 1000, '*')
2 from all_objects;

SQL> update uji2 set data = null where mod(id, 5) = 0;

Go to Enterprise Manager, and click Performance tab. In bottom section, click Advisor Central, and then click Segment Advisor. In Segment Advisor page, click Schema Objects, and click Next. Click Add button to add our table, i.e. uji2.

Click Next, and you can click Submit button directly. Here is example of the result :
Click the name of latest Segment Advisor task. In the page of Segment Advisor task, click Recomendataion Details.


Now, you can click Shrink button to make segment compact.