You Asked

Hi Tom,

My question is,

Does segment advisor in 12cR1, advise the use of online table redefination for tables in both dictionary managed and locally managed tablespace?

In addition,
What about the use of segment shrink for free list managed tables? Will segment advisor advice on it?

Thanks,
Sonika Maharjan

and Connor said...

There's an interesting conflict in the docs:

https://docs.oracle.com/database/122/ADMIN/managing-space-for-schema-objects.htm#ADMIN11600

"Only segments in locally managed tablespaces with automatic segment space management (ASSM) are eligible"

but also:

"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."

Check the reviews for some additional clarification

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Any update?

Andrew, October 03, 2018 - 8:25 pm UTC

This answer is awaiting update.

October 04, 2018 - 2:34 am UTC

OK, here's where I think the conflicting messages come into play. Segment advisor will look at *any* segment, but its going to give differing advice based on the tablespace management, eg

Segment space management auto
=============================

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;

4129500 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          : Enable row movement of the table SCOTT.DEMO_TABLE and perform shrink, estimated savings is 594530643 bytes.
More info        : Allocated Space:1342177280: Used Space:747646637: Reclaimable Space :594530643:

PL/SQL procedure successfully completed.

Segment space management manual
=============================

SQL> select * from dba_tablespaces where tablespace_name = 'NO_ASSM'
  2  @pr
==============================
TABLESPACE_NAME               : NO_ASSM
BLOCK_SIZE                    : 8192
INITIAL_EXTENT                : 1048576
NEXT_EXTENT                   : 1048576
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
MAX_SIZE                      : 2147483645
PCT_INCREASE                  : 0
MIN_EXTLEN                    : 1048576
STATUS                        : ONLINE
CONTENTS                      : PERMANENT
LOGGING                       : LOGGING
FORCE_LOGGING                 : NO
EXTENT_MANAGEMENT             : LOCAL
ALLOCATION_TYPE               : UNIFORM
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;

4129500 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 564585313 bytes.
More info        : Allocated Space:1307574272: Used Space:742988959: Reclaimable Space :564585313:

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL>

Shout out to Tim Hall - I borrowed one of his scripts

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Any update?

More to Explore

Administration

advise given by segment advisor 12c

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Any update?

More to Explore

Administration