Segment Advisor Recommendations

Overview

In Oracle 12c and later it is easy to perform segment optimization via the Auto Segment Advisor. The task auto_space_advisor_prog is run routinely to create the recommendations.

Actions

-- Get Recommendations (Verbose)

COL tablespace_owner FORMAT a15
COL segment_owner    FORMAT a15
COL segment_name     FORMAT a20
COL segment_type     FORMAT a15
COL recommendations  FORMAT a55
COL c1               FORMAT a55
SELECT tablespace_name, segment_owner, segment_name, segment_type,recommendations, c1 
FROM table( dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE') ); 

-- Get Recommended Optimization Commands

 COL cmd FORMAT a75
 SELECT c1 "cmd" FROM table( dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE') );

 cmd
 ---------------------------------------------------
 alter table "SCOTT"."TABLE1" compress for oltp
 alter table "SCOTT"."TABLE2" shrink space
 alter index "SCOTT"."INDEX1" shrink space

-- When was the Task Last Run?

 COL task_name      FORMAT a25
 COL last_good_date FORMAT a45
 SELECT task_name, 
        to_char(last_good_date,'YYYY-MON-DD HH24:MI:SS') "Last", 
        round(mean_good_duration,2) "Seconds"  
 FROM DBA_AUTOTASK_TASK;

 TASK_NAME                 Last                    Seconds
 ------------------------- -------------------- ----------
 auto_space_advisor_prog   2017-DEC-19 22:01:18      94.36
 ...

-- Task History

 SELECT to_char(job_start_time, 'YYYY-MON-DD HH24:MI:SS') FROM 
 DBA_AUTOTASK_JOB_HISTORY 
 WHERE client_name = 'auto space advisor'
 AND job_start_time > sysdate - 7
 ORDER BY job_start_time DESC;

 2017-DEC-19 22:00:04
 2017-DEC-18 22:00:04
 2017-DEC-17 06:00:03
 2017-DEC-16 22:01:35
 ...

-- Check Job Status

 SELECT client_name, status FROM dba_autotask_client;

 CLIENT_NAME                                       STATUS
 -------------------------------------------------- --------
 auto optimizer stats collection                   ENABLED
 auto space advisor                                ENABLED
 sql tuning advisor                                ENABLED