Table of contents
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]
Damir Vadas says:
What is the content of ” show_recm” procedure?
Yannick Jaquier says:
Hello,
Content is available in Oracle official documentation:
SQL Access Advisor