Runing advisor without voilate license agreement.

I like the advisors introudced in 10g, but company always tries to cut cost which affects buying the tuning package license. Luckily there is back door, i.e we can call them from command line. Below is the illustration of using segment advisor.

googled and get the sample template from http://www.oracle-base.com/articles/10g/SpaceObjectTransactionManagement10g.php

cat call_segment_advisor.sql

DECLARE
l_object_id NUMBER;
BEGIN
-- Create a segment advisor task for the SCOTT.EMP table.
-- update your own owner, table name (case-sensitive)
DBMS_ADVISOR.create_task (
advisor_name => 'Segment Advisor',
task_name => 'EMP_SEGMENT_ADVISOR',
task_desc => 'Segment Advisor For EMP');

DBMS_ADVISOR.create_object (
task_name => 'EMP_SEGMENT_ADVISOR',
object_type => 'TABLE',
attr1 => 'DBAM1',
attr2 => 'M1_OUTBOUND_USAGE',
attr3 => NULL,
attr4 => 'null',
attr5 => NULL,
object_id => l_object_id);

DBMS_ADVISOR.set_task_parameter (
task_name => 'EMP_SEGMENT_ADVISOR',
parameter => 'RECOMMEND_ALL',
value => 'TRUE');

DBMS_ADVISOR.execute_task(task_name => 'EMP_SEGMENT_ADVISOR');
END;
/


-- Create a segment advisor task for the USERS tablespace.
-- DBMS_ADVISOR.create_task (
-- advisor_name => 'Segment Advisor',
-- task_name => 'USERS_SEGMENT_ADVISOR',
-- task_desc => 'Segment Advisor For USERS');

-- DBMS_ADVISOR.create_object (
-- task_name => 'USERS_SEGMENT_ADVISOR',
-- object_type => 'TABLESPACE',
-- attr1 => 'USERS',
-- attr2 => NULL,
-- attr3 => NULL,
-- attr4 => 'null',
-- attr5 => NULL,
-- object_id => l_object_id);

-- DBMS_ADVISOR.set_task_parameter (
-- task_name => 'USERS_SEGMENT_ADVISOR',
-- parameter => 'RECOMMEND_ALL',
-- value => 'TRUE');

-- DBMS_ADVISOR.execute_task(task_name => 'USERS_SEGMENT_ADVISOR');
--END;
--/

-- Display the findings.
SET LINESIZE 250
COLUMN task_name FORMAT A20
COLUMN object_type FORMAT A20
COLUMN schema FORMAT A20
COLUMN object_name FORMAT A30
COLUMN object_name FORMAT A30
COLUMN message FORMAT A40
COLUMN more_info FORMAT A40

SELECT f.task_name,
f.impact,
o.type AS object_type,
o.attr1 AS schema,
o.attr2 AS object_name,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR')
ORDER BY f.task_name, f.impact DESC;

select message from dba_advisor_findings f
WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR') ;

select more_info from dba_advisor_findings f
WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR') ;

exec dbms_advisor.delete_task('EMP_SEGMENT_ADVISOR');


SQL> @call_segment_advisor.sql


PL/SQL procedure successfully completed.


TASK_NAME IMPACT OBJECT_TYPE SCHEMA OBJECT_NAME MESSAGE MORE_INFO
-------------------- ---------- -------------------- -------------------- ------------------------------ ---------------------------------------- ----------------------------------------
EMP_SEGMENT_ADVISOR TABLE DBAM1 M1_OUTBOUND_USAGE The free space in the object is less tha Allocated Space:15398338560: Used Space:
n the size of the last extent. 14503648178: Reclaimable Space :89469038
2:


SQL> select message from dba_advisor_findings f
2 WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR');

MESSAGE
----------------------------------------
The free space in the object is less tha
n the size of the last extent.


SQL> select more_info from dba_advisor_findings f WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR');

MORE_INFO
----------------------------------------
Allocated Space:15398338560: Used Space:
14503648178: Reclaimable Space :89469038
2:

:
SQL> select more_info FROM dba_advisor_findings f WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR6');

MORE_INFO
----------------------------------------
Allocated Space:21937386023: Used Space:
21863850510: Reclaimable Space :73535513
:

--delete the task if no longer needed, thus the script can be re-used easily without changing task name

SQL> exec dbms_advisor.delete_task('EMP_SEGMENT_ADVISOR2');

PL/SQL procedure successfully completed.


Read more the Oracle document "Oracle® Database PL/SQL Packages and Types Reference", DBMS_ADVISOR allows us to call all seven advisors defined in the view DBA_ADVISOR_DEFINITIONS.

BTW, the speed is surpising with ASSM. For above case, regardless the table size is about 21Gb, it finishes within 5 seconds.

Cheers!