ASSM

Automatic Segment Space Management
Auto Space Advisor / Segment Advisor / Compression Advisor

Check

SELECT client_name,

       status 

  FROM dba_autotask_client

 WHERE client_name = 'auto space advisor';

History

SET LINESIZE 200

SELECT window_name,

       jobs_created,

       jobs_started,

       jobs_completed

  FROM dba_autotask_client_history

 WHERE client_name='auto space advisor'

   AND window_start_time >= SYSDATE -7

 ORDER BY window_start_time DESC;

Run this to see the status of the Auto Space Advisor job over the last 7 days...

SET LINESIZE 200 

SET PAGESIZE 100

COLUMN job_name FORMAT a30

COLUMN window_name FORMAT a30

SELECT window_name,

       job_name,

       job_start_time,

       job_status,

       job_duration

  FROM dba_autotask_job_history

 WHERE client_name='auto space advisor'

   AND job_start_time >= SYSDATE -7

 ORDER BY job_start_time DESC;

Tasks

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

SET LINESIZE 200 

SET PAGESIZE 100

COLUMN status FORMAT a30

SELECT task_name,

       advisor_name,

       execution_start,

       execution_end,

       status,

       recommendation_count

  FROM dba_advisor_tasks

 WHERE execution_end > sysdate-1

   AND description = 'Auto Space Advisor';


TASK_NAME                      ADVISOR_NAME                   EXECUTION_START     EXECUTION_END       STATUS                         RECOMMENDATION_COUNT------------------------------ ------------------------------ ------------------- ------------------- ------------------------------ --------------------SYS_AUTO_SPCADV_37010608082021 Segment Advisor                2021-08-08 06:01:38 2021-08-08 06:08:43 COMPLETED                                         0SYS_AUTO_SPCADV_44080608082021 Segment Advisor                2021-08-08 06:08:44 2021-08-08 06:13:37 COMPLETED                                         0SYS_AUTO_SPCADV_38130608082021 Segment Advisor                2021-08-08 06:13:39 2021-08-08 07:29:36 COMPLETED                                         0

Only tasks with recommendations...

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

SET LINESIZE 200 

SET PAGESIZE 100

COLUMN status FORMAT a30

SELECT task_name,

       advisor_name,

       execution_start,

       execution_end,

       status,

       recommendation_count

  FROM dba_advisor_tasks

 WHERE description = 'Auto Space Advisor'

   AND recommendation_count > 0

 ORDER BY execution_start;

Jobs

To check job status...

COLUMN owner FORMAT a10

COLUMN comments FORMAT a60

SET LINESIZE 132

SELECT owner,

       job_name,

       enabled,

       comments

  FROM dba_scheduler_jobs

 WHERE job_name = 'AUTO_SPACE_ADVISOR_JOB';

To check duration...

SELECT *

  FROM (SELECT log_date,

               job_name,

               status,

               actual_start_date,

               run_duration

          FROM dba_scheduler_job_run_details

         WHERE job_name='AUTO_SPACE_ADVISOR_JOB'

         ORDER BY log_id DESC)

WHERE rownum<=2;

To check schedule...

COLUMN schedule_name FORMAT a30

SELECT job_name,

       schedule_type,

       schedule_name

  FROM dba_scheduler_jobs

 WHERE job_name = 'AUTO_SPACE_ADVISOR_JOB';


SELECT * 

  FROM dba_scheduler_wingroup_members;


SELECT window_name,

       repeat_interval

  FROM dba_scheduler_windows;


COLUMN last_start_date FORMAT a40

COLUMN window_name FORMAT a17

COLUMN next_start_date FORMAT a40

COLUMN duration FORMAT a15

SET LINESIZE 132

SELECT window_name,

       next_start_date,

       last_start_date,

       duration

  FROM dba_scheduler_windows;

Enable

BEGIN

  DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto space advisor',

                              operation   => NULL,

                              window_name => NULL);

END;

/

Disable

BEGIN

  DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto space advisor',

                               operation   => NULL,

                               window_name => NULL);

END;

/

Or, alternatively, at DBMS Scheduler level (recommended for 10GR2 and earlier only)...

EXECUTE DBMS_SCHEDULER.DISABLE('AUTO_SPACE_ADVISOR_JOB');

Run Segment Advisor Manually

You may wish to do this to analyze a tablespace or segment that was not selected by the Automatic Segment Advisor or because you want more up to date recommendations.

TABLESPACE

VARIABLE id NUMBER;

BEGIN

  DECLARE

    name     VARCHAR2(100);

    descr    VARCHAR2(500);

    obj_id   NUMBER;

  BEGIN

    name:='&MySegmentAdvisorRun';

    descr:='&MySegmentAdvisorRunDesc';


    dbms_advisor.create_task (

      advisor_name     => 'Segment Advisor',

      task_id          => :id,

      task_name        => name,

      task_desc        => descr);


    dbms_advisor.create_object (

      task_name        => name,

      object_type      => 'TABLESPACE',

      attr1            => '&MyTablespace',

      attr2            => NULL,

      attr3            => NULL,

      attr4            => NULL,

      attr5            => NULL,

      object_id        => obj_id);


    dbms_advisor.set_task_parameter(

      task_name        => name,

      parameter        => 'recommend_all',

      value            => 'TRUE');


    dbms_advisor.execute_task(name);

  END;

END; 

/

TABLE

VARIABLE id NUMBER;

BEGIN

  DECLARE

    name     VARCHAR2(100);

    descr    VARCHAR2(500);

    obj_id   NUMBER;

  BEGIN

    name:='&MySegmentAdvisorRun';

    descr:='&MySegmentAdvisorRunDesc';


    dbms_advisor.create_task (

      advisor_name     => 'Segment Advisor',

      task_id          => :id,

      task_name        => name,

      task_desc        => descr);


    dbms_advisor.create_object (

      task_name        => name,

      object_type      => 'TABLE',

      attr1            => '&MySchema',

      attr2            => '&MyTable',

      attr3            => NULL,

      attr4            => NULL,

      attr5            => NULL,

      object_id        => obj_id);


    dbms_advisor.set_task_parameter(

      task_name        => name,

      parameter        => 'recommend_all',

      value            => 'TRUE');


    dbms_advisor.execute_task(name);

  END;

END; 

/

OBJECT_TYPE


Other options for OBJECT_TYPE are:
  • INDEX
  • TABLE PARTITION
  • INDEX PARTITION
  • TABLE SUBPARTITION
  • INDEX SUBPARTITION
  • LOB
  • LOB PARTITION
  • LOB SUBPARTITION

"attr3" is used for Partition/Subpartition names
"attr4" and "attr5" don't seem to be used.

PARAMETER


When setting "recommend_all" to FALSE - Findings are generated only for those objects that generate recommendations for space reclamation. Default is TRUE
A"time_limit" parameter is also available. Default is unlimited. If you set this to a number of seconds then the Segment Advisor will be terminated when the run exceeds that limit.

Viewing Results

Remember to check that you are licensed to use the actions recommended (e.g. "compress for oltp" requires the advanced compression option).

SELECT tablespace_name, 

       segment_name,

       segment_type,

       partition_name,

       recommendations,

       c1 

  FROM TABLE(DBMS_SPACE.asa_recommendations(

               all_runs      => 'TRUE',

               show_manual   => 'TRUE',

               show_findings => 'FALSE'));

all_runs
  • TRUE returns recommendations or findings for all runs of auto segment advisor. 
  • FALSE returns the results of the LATEST run only. FALSE does not make sense for manual invocation of segment advisor.
show_manual
  • TRUE shows results of manual invocations only. Auto advisor results are excluded. 
  • FALSE does the opposite.
show_findings
  • TRUE shows only the findings instead of the recommendations.
  • FALSE show recommendations

To see findings...

SELECT dao.type AS object_type,

       dao.attr1 AS owner,

       dao.attr2 AS object_name,

       dao.attr4 AS tablespace,

       daf.message,

       daf.more_info

  FROM dba_advisor_findings daf,

       dba_advisor_objects dao

 WHERE daf.task_name = 'SYS_AUTO_SPCADV_38130608082021'

   AND daf.task_id = dao.task_id

   AND daf.object_id = dao.object_id;

To see findings with actions...

SELECT dao.type AS object_type,

       dao.attr1 AS owner,

       dao.attr2 AS object_name,

       dao.attr4 AS tablespace,

       daf.message,

       daf.more_info,

       daa.command,

       daa.attr1,

       daa.attr2,

       daa.attr3,

       daa.attr4

  FROM dba_advisor_findings daf,

       dba_advisor_objects dao,

       dba_advisor_actions daa

 WHERE daf.task_name = 'SYS_AUTO_SPCADV_38130608082021'

   AND daf.task_id = dao.task_id

   AND daf.object_id = dao.object_id

   AND dao.task_id = daa.task_id

   AND dao.object_id = daa.object_id;

Troubleshooting

The "Compression Advisor" functionality of the Segment Advisor creates temporary tables to evaluate the benefits of compression. If a tablespace fills up overnight but is fine by morning it could be that it is the temprary existence of these tables consuming the space.

If you run an AWR report covering the window where the Segment Advisor was running it is usually possible to isolate the SQL to blame. For example, in the "Complete List of SQL Text" section of my AWR report I can pick out a few things...

create table "MYSCHEMA".DBMS_TABCOMP_TEMP_UNCMP tablespace "MYTABLESPACE" nologging as select /*+ DYNAMIC_SAMPLING(0) FULL("MYSCHEMA"."MYTABLE") */ * from "MYSCHEMA"."MYTABLE" sample block( 19.53) mytab 
create table "MYSCHEMA".DBMS_TABCOMP_TEMP_CMP organization heap tablespace "MYTABLESPACE" compress for all operations nologging as select /*+ DYNAMIC_SAMPLING(0) */ * from "MYSCHEMA".DBMS_TABCOMP_TEMP_UNCMP mytab
call dbms_space.auto_space_advisor_job_proc ( )
@?/rdbms/admin/awrrpt.sql

Bibliography