Manually Displaying Automated Segment Advisor Advice

Segment Advisor Views
Applies to : Oracle 10g & more Enterprise Edition

Why ?
~~~~~~
You have a poorly performing query accessing a table. Upon further investigation, you discover the table
has only a few rows in it. You wonder why the query is taking so long when there are so few rows. You
want to examine the output of the Segment Advisor to see if there are any space-related
recommendations that might help with performance in this situation.

Solution :
~~~~~~
Use the Segment Advisor to display information regarding tables that may have space allocated to
them (that was once used) but now the space is empty (due to a large number of deleted rows).
Tables with large amounts of unused space can cause full table scan queries to perform poorly. This is
because Oracle is scanning every block beneath the high-water mark, regardless of whether the blocks
contain data.

There are three different tools for retrieving the Segments Advisor's output

1. Executing DBMS_SPACE.ASA_RECOMMENDATIONS
2. Manually querying DBA_ADVISOR_* views
3. Viewing Enterprise Manager's graphical screens

This solution focuses on option 1 as below
-- If you are running in console screen:

set lines 120;
set pages 500;
SELECT
                   'Segment Advice --------------------------'|| chr(10) ||
                   'TABLESPACE_NAME           : ' || tablespace_name              || chr(10) ||
                   'SEGMENT_OWNER             : ' || segment_owner                || chr(10) ||
                   'SEGMENT_NAME              : ' || segment_name                 || chr(10) ||
                   'ALLOCATED_SPACE           : ' || allocated_space              || chr(10) ||
                   'RECLAIMABLE_SPACE: ' || reclaimable_space || chr(10) ||
                   'RECOMMENDATIONS           : ' || recommendations              || chr(10) ||
                   'SOLUTION 1                : ' || c1                           || chr(10) ||
                   'SOLUTION 2                : ' || c2                           || chr(10) ||
                   'SOLUTION 3                : ' || c3 Advice
FROM
                   TABLE(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));


--If you are running throgh Pl/SQl tools like plsql Dev/ Toad etc.
SELECT tablespace_name,
       segment_owner,
       segment_name,
       round(allocated_space / 1024 / 1024, 1) allocated_space_MB,
       round(reclaimable_space / 1024 / 1024, 1) reclaimable_space_MB,
       recommendations,
       c1 "Solution_1",
       c2 "Solution_2",
       c3 "Solution_3"
FROM TABLE(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));

How It Works ?
~~~~~~~~~~~~

In Oracle Database 10g R2 and later, Oracle automatically schedules and runs a Segment Advisor job.
This job analyzes segments in the database and stores its findings in internal tables. The output of the
Segment Advisor contains findings (issues that may need to be resolved) and recommendations (actions
to resolve the findings). Findings from the Segment Advisor are of the following types:

>> Segments that are good candidates for shrink operations
>> Segments that have significant row chaining
>> Segments that might benefit from OLTP compression

When viewing the Segment Advisor’s findings and recommendations, it’s important to understand
several aspects of this tool. First, the Segment Advisor regularly calculates advice via an automatically
scheduled DBMS_SCHEDULERjob. You can verify the last time the automatic job ran by querying the
DBA_AUTO_SEGADV_SUMMARY view:

SQL> select * from DBA_AUTO_SEGADV_SUMMARY order by end_time desc;

You can also directly query the data dictionary views to view the advice of the Segment Advisor.
Here’s a query that displays Segment Advisor advice generated within the last day:

SQL>
SELECT
 'Task Name        : ' || f.task_name  || chr(10) ||
 'Start Run Time   : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || chr (10) ||
 'Segment Name     : ' || o.attr2    || chr(10) ||
 'Segment Type     : ' || o.type       || chr(10) ||
 'Partition Name   : ' || o.attr3      || chr(10) ||
 'Message          : ' || f.message    || chr(10) ||
 'More Info        : ' || f.more_info  || chr(10) ||
 '------------------------------------------------------' Advice
FROM
dba_advisor_findings   f
    ,dba_advisor_objects    o
    ,dba_advisor_executions e
WHERE o.task_id   = f.task_id
AND   o.object_id = f.object_id
AND   f.task_id   = e.task_id
AND   e. execution_start > sysdate - 1
AND   e.advisor_name = 'Segment Advisor'
ORDER BY f.task_name;


Try all these and hope it will help.