Friday, September 16, 2011

Manually Displaying Automated Segment Advisor Advice

Problem

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

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'));

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_SUMMARYview:


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:

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;

2 comments:

  1. Hi Bilal Ashraf,
    This article is very useful to me. But when I ran the last sql posted, I got error about dba_advisor_executions table not existing. I am running 10.2.0.4. Any reason for that ? Please let me know. My mail_id is "svennam@mlgw.org"

    ReplyDelete