Segment Advisor

Preamble

In a previous post I have already suggested to deactivate Segment Advisor as it is consuming lots of resource for almost nothing as most of the time you do not regularly check results. So better keep it off and execute it when needed, let’s say on big objects.

On my 10.2.0.3 Documentum database (running on HPUX PA Risc 64 bits) it is not very impressive, but I have seen it running for multiple hours on a terabyte DataWareHouse database:

SQL> col EXECUTION_START FOR a25
SQL> col EXECUTION_END FOR a25
SQL> SELECT * FROM (SELECT task_name,TO_CHAR(execution_start,'dd-mon-yyyy hh24:mi:ss') AS execution_start,
     TO_CHAR(execution_end,'dd-mon-yyyy hh24:mi:ss') AS execution_end,status,
     ROUND((execution_end-execution_start)*24*60,1) AS "Duration (min)"
     FROM dba_advisor_tasks WHERE advisor_name='Segment Advisor' ORDER BY task_id DESC) WHERE rownum<=10;
 
TASK_NAME                      EXECUTION_START           EXECUTION_END             STATUS      Duration (MIN)
------------------------------ ------------------------- ------------------------- ----------- --------------
SYS_AUTO_SPCADV_1602027102011  27-oct-2011 22:00:18      27-oct-2011 22:05:41      COMPLETED              5.4
SYS_AUTO_SPCADV_1402026102011  26-oct-2011 22:00:16      26-oct-2011 22:05:54      COMPLETED              5.6
SYS_AUTO_SPCADV_1202025102011  25-oct-2011 22:00:13      25-oct-2011 22:06:00      COMPLETED              5.8
SYS_AUTO_SPCADV_1702024102011  24-oct-2011 22:00:20      24-oct-2011 22:07:54      COMPLETED              7.6
SYS_AUTO_SPCADV_190422102011   22-oct-2011 06:00:22      22-oct-2011 06:08:19      COMPLETED                8
SYS_AUTO_SPCADV_2302021102011  21-oct-2011 22:00:26      21-oct-2011 22:07:52      COMPLETED              7.4
SYS_AUTO_SPCADV_2102020102011  20-oct-2011 22:00:24      20-oct-2011 22:07:08      COMPLETED              6.7
SYS_AUTO_SPCADV_1302019102011  19-oct-2011 22:00:15      19-oct-2011 22:07:04      COMPLETED              6.8
SYS_AUTO_SPCADV_1602018102011  18-oct-2011 22:00:18      18-oct-2011 22:06:37      COMPLETED              6.3
SYS_AUTO_SPCADV_2002017102011  17-oct-2011 22:00:27      17-oct-2011 22:08:17      COMPLETED              7.8
 
10 ROWS selected.

Segment Advisor task

Example will be done on a famous table:

SQL> SELECT num_rows,blocks,empty_blocks FROM dba_tables a
     WHERE a.owner='WCMS' AND table_name='DM_SYSOBJECT_R';
 
  NUM_ROWS     BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
   3247365      19149          175
 
SQL> SELECT bytes,blocks,extents FROM dba_segments
     WHERE owner='WCMS' AND segment_name='DM_SYSOBJECT_R';
 
     BYTES     BLOCKS    EXTENTS
---------- ---------- ----------
 158334976      19328         90

Task creation is same as for SQL Access Advisor and you have to use DBMS_ADVISOR.CREATE_OBJECT to add objects to analyze to your task. Refer to Oracle Database Administrator’s Guide for a complete description of the attr1 to attr5 parameters:

DECLARE
  task_id NUMBER;
  task_name VARCHAR2(30);
  obj_id NUMBER;
BEGIN
  task_name := 'Task01';
 
  DBMS_ADVISOR.CREATE_TASK('Segment Advisor', task_id, task_name, 'Task for table WCMS.DM_SYSOBJECT_R');
  DBMS_ADVISOR.CREATE_OBJECT(task_name,'TABLE','WCMS','DM_SYSOBJECT_R',NULL,NULL,obj_id);
  DBMS_ADVISOR.SET_TASK_PARAMETER(task_name, 'MODE', 'COMPREHENSIVE');
  DBMS_ADVISOR.SET_TASK_PARAMETER(task_name, 'RECOMMEND_ALL', 'TRUE');
  DBMS_ADVISOR.EXECUTE_TASK(task_name);
END;
/

Remark
Even if RECOMMEND_ALL parameter default value is TRUE, if you don’t set it the task will not generate any recommendations…

You can get objects associated with a task with:

SQL> col ATTR1 FOR a30
SQL> col attr2 FOR a30
SQL> SELECT TYPE,attr1,attr2 FROM DBA_ADVISOR_OBJECTS WHERE task_name='Task01';
 
TYPE                                                             ATTR1                          ATTR2
---------------------------------------------------------------- ------------------------------ ------------------------------
TABLE                                                            WCMS                           DM_SYSOBJECT_R

Result

Using either DBMS_ADVISOR.GET_TASK_SCRIPT or DBMS_ADVISOR.GET_TASK_REPORT does not work and produce the famous Oracle error:

ORA-13613: The requested operation IS NOT supported FOR this advisor object.
ORA-06512: AT "SYS.PRVT_ADVISOR", line 2043
ORA-06512: AT "SYS.DBMS_ADVISOR", line 560
ORA-06512: AT line 1

So as suggested in MOS you can use:

SQL> col MESSAGE FOR a100
SQL> col MORE_INFO FOR a100
SQL> SELECT message,more_info FROM DBA_ADVISOR_FINDINGS WHERE task_name='Task01';
 
MESSAGE
----------------------------------------------------------------------------------------------------
MORE_INFO
----------------------------------------------------------------------------------------------------
Enable ROW movement OF the TABLE WCMS.DM_SYSOBJECT_R AND perform shrink, estimated savings IS 112150
31 bytes.
Allocated SPACE:158334976: Used SPACE:147119945: Reclaimable SPACE :11215031:

Or

SQL> col BENEFIT_TYPE FOR a120
SQL> SELECT benefit_type FROM DBA_ADVISOR_RECOMMENDATIONS WHERE task_name='Task01';
 
BENEFIT_TYPE
------------------------------------------------------------------------------------------------------------------------
Enable ROW movement OF the TABLE WCMS.DM_SYSOBJECT_R AND perform shrink, estimated savings IS 11215031 bytes.

To display results of DBA_ADVISOR_ACTIONS table we will use the modified show_recm procedure, v_attrx variables displayed with longer size:

SQL> EXECUTE show_recm('Task01');
=========================================
Task_name = Task01
Action ID: 1
Command : SHRINK SPACE
Attr1 (name)      : ALTER TABLE "WCMS"."DM_SYSOBJECT_R" shrink SPACE
Attr2 (TABLESPACE): ALTER TABLE "WCMS"."DM_SYSOBJECT_R" shrink SPACE COMPACT
Attr3             : ALTER TABLE "WCMS"."DM_SYSOBJECT_R" enable ROW movement
Attr4             :
Attr5             :
----------------------------------------
=========END RECOMMENDATIONS============
 
PL/SQL PROCEDURE successfully completed.

Then DBMS_SPACE package also provide two interesting procedures, DBMS_SPACE.ASA_RECOMMENDATIONS:

SQL> SELECT
     segment_owner,
     segment_name,
     ROUND(allocated_space/(1024*1024)) AS "Allocated (MB)",
     ROUND(used_space/(1024*1024)) AS "Used (MB)",
     ROUND(reclaimable_space/(1024*1024)) AS "Reclaimable (MB)",
     ROUND(reclaimable_space*100/allocated_space) AS "Percentage gain"
     FROM TABLE(dbms_space.asa_recommendations())
     WHERE task_id = (SELECT task_id FROM dba_advisor_tasks WHERE task_name='Task01');
 
SEGMENT_OWNER                  SEGMENT_NAME                   Allocated (MB)  Used (MB) Reclaimable (MB) Percentage gain
------------------------------ ------------------------------ -------------- ---------- ---------------- ---------------
WCMS                           DM_SYSOBJECT_R                            151        140               11               7

With all above queries we see we can expect to gain around 11MB on a 151MB objects… Keeping in mind how extents are working, this long operation would not be worth the poor result…

SQL> SET pages 50
SQL> SELECT recommendations,c3,c2,c1 
     FROM TABLE(dbms_space.asa_recommendations())
     WHERE task_id = (SELECT task_id FROM dba_advisor_tasks WHERE task_name='Task01');
 
RECOMMENDATIONS
--------------------------------------------------------------------------------
C3
--------------------------------------------------------------------------------
C2
--------------------------------------------------------------------------------
C1
--------------------------------------------------------------------------------
Enable ROW movement OF the TABLE WCMS.DM_SYSOBJECT_R AND perform shrink, estimat
ed savings IS 11215031 bytes.
ALTER TABLE "WCMS"."DM_SYSOBJECT_R" enable ROW movement
ALTER TABLE "WCMS"."DM_SYSOBJECT_R" shrink SPACE COMPACT
ALTER TABLE "WCMS"."DM_SYSOBJECT_R" shrink SPACE

And DBMS_SPACE.OBJECT_GROWTH_TREND. My example clearly show that objects has not evolved over the past 30 days:

SQL> SET pages 100
SQL> SET lines 150
SQL> SELECT * FROM TABLE(dbms_space.object_growth_trend('WCMS','DM_SYSOBJECT_R','TABLE'))
     ORDER BY timepoint;
 
TIMEPOINT                                                                   SPACE_USAGE SPACE_ALLOC QUALITY
--------------------------------------------------------------------------- ----------- ----------- --------------------
04-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
05-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
06-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
07-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
08-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
09-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
10-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
11-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
12-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
13-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
14-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
15-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
16-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
17-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
18-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
19-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
20-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
21-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
22-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
23-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
24-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
25-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
26-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
27-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
28-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
29-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
30-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
31-OCT-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
01-NOV-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
02-NOV-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
03-NOV-11 05.13.43.463095 PM                                                  147371941   158334976 INTERPOLATED
04-NOV-11 05.13.43.463095 PM                                                  147371941   158334976 PROJECTED
05-NOV-11 05.13.43.463095 PM                                                  147371941   158334976 PROJECTED
06-NOV-11 05.13.43.463095 PM                                                  147371941   158334976 PROJECTED
07-NOV-11 05.13.43.463095 PM                                                  147371941   158334976 PROJECTED
08-NOV-11 05.13.43.463095 PM                                                  147371941   158334976 PROJECTED
 
36 ROWS selected.

Remark
You don’t need to issue a segment advisor task to be able to use DBMS_SPACE.OBJECT_GROWTH_TREND procedure.

You can then clean all with:

SQL> EXEC DBMS_ADVISOR.DELETE_TASK('Task01');
 
PL/SQL PROCEDURE successfully completed.

References

  • 10g and above SEGMENT ADVISOR [ID 242736.1]
  • Is There a Way To Run The Segment Advisor Manually From SQL*Plus? [ID 854234.1]

About Post Author

3 thoughts on “Segment Advisor

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>