Oracle Segment Advisor for Individual Segments

oracle_segment_advisor_for_individual_segments.sql

--
-- Segment Advisor for Individual Segments
--

-- 1. Replace all occurances of [OWNER] with the owner of the segments.
-- 2. Replace [TABLE_NAME] or [INDEX_NAME] with the segment to be analyzed.

set echo off
set feedback off
set verify off
set linesize 80
set serveroutput on size unlimited

declare
    v_task_name   varchar2(100);
    v_task_desc   varchar2(500);
    v_objid       number;
begin
    begin
        v_task_name := 'SEGMENT_ADVISOR_RUN';
        v_task_desc := 'MANUAL SEGMENT ADVISOR RUN';

        -- Create Segment Advisor task.
        dbms_advisor.create_task
        (
            advisor_name => 'Segment Advisor',
            task_name    => v_task_name,
            task_desc    => v_task_desc
        );

        -- Add Table to Segment Advisor task.
        -- Call create_object for each segment being analyzed.
        dbms_advisor.create_object
        (
            task_name   => v_task_name,
            object_type => 'TABLE',
            attr1       => 'OPENTEXT',
            attr2       => 'LLCACHE',
            attr3       => null,
            attr4       => null,
            attr5       => null,
            object_id   => v_objid
        );

        -- Set task parameter to recommend all.
        dbms_advisor.set_task_parameter
        (
            task_name => v_task_name,
            parameter => 'RECOMMEND_ALL',
            value     => 'TRUE'
        );

        -- Run Segment Advisor.
        dbms_advisor.reset_task(v_task_name);
        dbms_advisor.execute_task(v_task_name);

    exception when others then
        dbms_output.put_line('Exception: ' || SQLERRM);
    end;

    -- Output findings.
    dbms_output.put_line(chr(10));
    dbms_output.put_line('Segment Advisor Recommendations');
    dbms_output.put_line('--------------------------------------------------------------------------------');

    for r in (select segment_owner, segment_name, segment_type, partition_name,
                     tablespace_name, allocated_space, used_space,
                     reclaimable_space, chain_rowexcess, recommendations, c1, c2, c3
              from table(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE'))
              where segment_owner = 'OPENTEXT'
              order by reclaimable_space desc)
    loop
        dbms_output.put_line('');
        dbms_output.put_line('Owner              : ' || r.segment_owner);
        dbms_output.put_line('Segment            : ' || r.segment_name);
        dbms_output.put_line('Segment Type       : ' || r.segment_type);
        dbms_output.put_line('Partition Name     : ' || r.partition_name);
        dbms_output.put_line('Tablespace         : ' || r.tablespace_name);
        dbms_output.put_line('Allocated Space    : ' || r.allocated_space);
        dbms_output.put_line('Used Space         : ' || r.used_space);
        dbms_output.put_line('Reclaimable Space  : ' || r.reclaimable_space);
        dbms_output.put_line('Chain Rowexcess    : ' || r.chain_rowexcess);
        dbms_output.put_line('Recommendations    : ' || r.recommendations);
        dbms_output.put_line('Run First          : ' || r.c3);
        dbms_output.put_line('Run Second         : ' || r.c2);
        dbms_output.put_line('Run Third          : ' || r.c1);
        dbms_output.put_line('--------------------------------------------------------------------------------');
    end loop;

    -- Remove Segment Advisor task.
    dbms_advisor.delete_task(v_task_name);
end;
/