Posted by If you have performed some sort of data cleanup or similar on a table, then the deleted space will be reused by future insertions. But if
- that cleanup was the last task you were performing on that table, ie, you were not expecting a lot of new data to ever come in again, or
- you are performing a lot of full scan queries on that table and you want to make sure they are as efficient as possible
then there may be benefits to performing a shrink on that table to reclaim that space. One of the cool things about the segment advisor is that it will detect if there are some benefits to be gained by shrinking a segment. Here’s an example of that. I create a large table and then delete every 2nd row.
SQL> create table scott.demo_table as
2 select d.* from dba_objects d,
3 ( select 1 from dual connect by level <= 100 ) ;
Table created.
SQL>
SQL> delete from scott.demo_table
2 where mod(object_id,2) = 0;
4125300 rows deleted.
SQL> commit;
Commit complete.
Now I run the segment advisor and I get a nice report on what can be done to reclaim that space.
SQL>
SQL>
SQL> set serveroutput on size 1000000
SQL> set linesize 200
SQL> set verify off
SQL>
SQL> declare
2 l_object_id number;
3 l_task_name varchar2(50) := 'TEST_TASK';
4 l_object_type varchar2(50) := upper('TABLE');
5 l_attr1 varchar2(50) := upper('SCOTT');
6 l_attr2 varchar2(50) := upper('DEMO_TABLE');
7 begin
8 begin DBMS_ADVISOR.delete_task(task_name => l_task_name); exception when others then null; end;
9
10 dbms_advisor.create_task (
11 advisor_name => 'Segment Advisor',
12 task_name => l_task_name);
13
14 dbms_advisor.create_object (
15 task_name => l_task_name,
16 object_type => l_object_type,
17 attr1 => l_attr1,
18 attr2 => l_attr2,
19 attr3 => NULL,
20 attr4 => 'null',
21 attr5 => NULL,
22 object_id => l_object_id);
23
24 dbms_advisor.set_task_parameter (
25 task_name => l_task_name,
26 parameter => 'RECOMMEND_ALL',
27 value => 'TRUE');
28
29 dbms_advisor.execute_task(task_name => l_task_name);
30
31
32 for cur_rec in (select f.impact,
33 o.type,
34 o.attr1,
35 o.attr2,
36 f.message,
37 f.more_info
38 from dba_advisor_findings f
39 join dba_advisor_objects o on f.object_id = o.object_id and f.task_name = o.task_name
40 where f.task_name = l_task_name
41 order by f.impact desc)
42 loop
43 dbms_output.put_line('..');
44 dbms_output.put_line('Type : ' || cur_rec.type);
45 dbms_output.put_line('Attr1 : ' || cur_rec.attr1);
46 dbms_output.put_line('Attr2 : ' || cur_rec.attr2);
47 dbms_output.put_line('Message : ' || cur_rec.message);
48 dbms_output.put_line('More info : ' || cur_rec.more_info);
49 end loop;
50
51 dbms_advisor.delete_task(task_name => l_task_name);
52 end;
53 /
..
Type : TABLE
Attr1 : SCOTT
Attr2 : DEMO_TABLE
Message : Enable row movement of the table SCOTT.DEMO_TABLE and perform shrink, estimated savings is 596868412 bytes.
More info : Allocated Space:1342177280: Used Space:745308868: Reclaimable Space :596868412:
PL/SQL procedure successfully completed.
But what if that segment sits in a manual segment space managed tablespace? We can see from the above that a shrink-style operation will yield some benefits, but there’s a problem. You cannot perform an ALTER TABLE SHRINK command unless a segment sits in an ASSM tablespace. So is the segment advisor of any use in these cases? Let’s re-run the demo to find out
SQL> select * from dba_tablespaces where tablespace_name = 'NO_ASSM'
2 @pr
==============================
TABLESPACE_NAME : NO_ASSM
BLOCK_SIZE : 8192
INITIAL_EXTENT : 65536
NEXT_EXTENT :
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
MAX_SIZE : 2147483645
PCT_INCREASE :
MIN_EXTLEN : 65536
STATUS : ONLINE
CONTENTS : PERMANENT
LOGGING : LOGGING
FORCE_LOGGING : NO
EXTENT_MANAGEMENT : LOCAL
ALLOCATION_TYPE : SYSTEM
PLUGGED_IN : NO
SEGMENT_SPACE_MANAGEMENT : MANUAL
DEF_TAB_COMPRESSION : DISABLED
RETENTION : NOT APPLY
BIGFILE : NO
PREDICATE_EVALUATION : HOST
ENCRYPTED : NO
COMPRESS_FOR :
DEF_INMEMORY : DISABLED
DEF_INMEMORY_PRIORITY :
DEF_INMEMORY_DISTRIBUTE :
DEF_INMEMORY_COMPRESSION :
DEF_INMEMORY_DUPLICATE :
SHARED : SHARED
DEF_INDEX_COMPRESSION : DISABLED
INDEX_COMPRESS_FOR :
DEF_CELLMEMORY :
DEF_INMEMORY_SERVICE :
DEF_INMEMORY_SERVICE_NAME :
LOST_WRITE_PROTECT : OFF
CHUNK_TABLESPACE : N
PL/SQL procedure successfully completed.
SQL>
SQL> create table scott.demo_table tablespace no_assm as
2 select d.* from dba_objects d,
3 ( select 1 from dual connect by level <= 100 ) ;
Table created.
SQL>
SQL> delete from scott.demo_table
2 where mod(object_id,2) = 0;
4125300 rows deleted.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> set serveroutput on size 1000000
SQL> set linesize 200
SQL> set verify off
SQL>
SQL> declare
2 l_object_id number;
3 l_task_name varchar2(50) := 'TEST_TASK';
4 l_object_type varchar2(50) := upper('TABLE');
5 l_attr1 varchar2(50) := upper('SCOTT');
6 l_attr2 varchar2(50) := upper('DEMO_TABLE');
7 begin
8 begin DBMS_ADVISOR.delete_task(task_name => l_task_name); exception when others then null; end;
9
10 dbms_advisor.create_task (
11 advisor_name => 'Segment Advisor',
12 task_name => l_task_name);
13
14 dbms_advisor.create_object (
15 task_name => l_task_name,
16 object_type => l_object_type,
17 attr1 => l_attr1,
18 attr2 => l_attr2,
19 attr3 => NULL,
20 attr4 => 'null',
21 attr5 => NULL,
22 object_id => l_object_id);
23
24 dbms_advisor.set_task_parameter (
25 task_name => l_task_name,
26 parameter => 'RECOMMEND_ALL',
27 value => 'TRUE');
28
29 dbms_advisor.execute_task(task_name => l_task_name);
30
31
32 for cur_rec in (select f.impact,
33 o.type,
34 o.attr1,
35 o.attr2,
36 f.message,
37 f.more_info
38 from dba_advisor_findings f
39 join dba_advisor_objects o on f.object_id = o.object_id and f.task_name = o.task_name
40 where f.task_name = l_task_name
41 order by f.impact desc)
42 loop
43 dbms_output.put_line('..');
44 dbms_output.put_line('Type : ' || cur_rec.type);
45 dbms_output.put_line('Attr1 : ' || cur_rec.attr1);
46 dbms_output.put_line('Attr2 : ' || cur_rec.attr2);
47 dbms_output.put_line('Message : ' || cur_rec.message);
48 dbms_output.put_line('More info : ' || cur_rec.more_info);
49 end loop;
50
51 dbms_advisor.delete_task(task_name => l_task_name);
52 end;
53 /
..
Type : TABLE
Attr1 : SCOTT
Attr2 : DEMO_TABLE
Message : Perform re-org on the object DEMO_TABLE, estimated savings is 600175966 bytes.
More info : Allocated Space:1342177280: Used Space:742001314: Reclaimable Space :600175966:
PL/SQL procedure successfully completed.
SQL>
SQL>
As you can see, the segment advisor will take that into account and adjust its recommendations accordingly. And one of the cool things with 12.2 and above, is that tables can be reorganised without an outage!
SQL> alter table scott.demo_table move online;
Table altered.
Nice!
Learning is not a spectator sport 
Hello,
WIth 12.2 MOVE ONLINE for the table, do you have to rebuild the indexes afterward?
Thanks,
Frank
No, they will kept in sync as well
Hi Connor, can you please explain (or share any resource) about the statement “the deleted space will be reused by future insertions”. It would be very helpful.
Thank you.
I’ll do so in a future post.
Hi,
My space advisor hugely encourages me to shrink tables and indexes, but how can I find out whether or not enabling row movement will have an impact on the application? Thanks!
Brgs,
It is *unlikely* to impact your app *but* the key thing to check is your app does any operations with the rowid. Some apps use a model of:
– get the data to show on screen to the user and keep track of the rowid
– when an update is needed, use the rowid to quickly find that same row
So check for anywhere where you are using/storing rowids. If you’re not, then should be fine.
But….if you *do* shrink some segments, do some benchmarking before and after of your app. If nothing gets better (or worse) then perhaps don’t bother doing it
Thanks,
I did find in the library cache frequent update where rowid = :bind but tracing a few sessions showed that it is preceeded by a select for update. Strangely, I couldn’t see where the rowid was stored.
I’ll ask editor and developers.
But, space advisor gives my gains of 50+ GB on a single table (with indexes) (and also gain on quite a few other tables) — and there has been some heavy deletes on this TBS — so, I do think it is worth it to retrieve space.
Brgs,
Frank