Reclaiming Wasted Space

Running the Segment Advisor Manually with Enterprise Manager

You must have the OEM_ADVISOR role to run the Segment Advisor manually with Enterprise Manager. There are two ways to run the Segment Advisor:

• Using the Segment Advisor Wizard

  This method enables you to request advice at the tablespace level or object level. At the object level, you can request advice on tables, indexes, table partitions, and index partitions. Dependent objects such as LOB segments cannot be included in the analysis.

• Using the Run Segment Advisor command on a schema object page.

  For example, if you display a list of tables on the Tables page (accessible from the Schema page), you can select a table and then select the Run Segment Advisor command from the Actions menu.

  Figure 18-1 Tables page

  
  Description of "Figure 18-1 Tables page"
  
  

  This method enables you to include the schema object's dependent objects in the Segment Advisor run. For example, if you select a table and select the Run Segment Advisor command, Enterprise Manager displays the table's dependent objects, such as partitions, index segments, LOB segments, and so on. You can then select dependent objects to include in the run.

In both cases, Enterprise Manager creates the Segment Advisor task as an Oracle Database Scheduler job. You can schedule the job to run immediately, or can take advantage of advanced scheduling features offered by the Scheduler.

To run the Segment Advisor manually with the Segment Advisor Wizard:

1. From the database Home page, under Related Links, click Advisor Central.

   The Advisor Central page appears. (See Figure 18-2.)

2. Under Advisors, click Segment Advisor.

   The first page of the Segment Advisor wizard appears.

3. Follow the wizard steps to schedule the Segment Advisor job, and then click Submit on the final wizard page.

   The Advisor Central page reappears, with the new Segment Advisor job at the top of the list under the Results heading. The job status is SCHEDULED or RUNNING. (If you do not see your job, use the search fields above the list to display it.)

4. Check the status of the job. If it is not COMPLETED, click the Refresh button at the top of the page repeatedly. (Do not use your browser's Refresh icon.)

   When the job status changes to COMPLETED, select the job by clicking in the Select column, and then click View Result.

   Figure 18-2 Advisor Central page

   
   Description of "Figure 18-2 Advisor Central page"
   
   

Running the Segment Advisor Manually with PL/SQL

You can also run the Segment Advisor with the DBMS_ADVISOR package. You use package procedures to create a Segment Advisor task, set task arguments, and then execute the task. You must have the ADVISOR privilege. Table 18-1 shows the procedures that are relevant for the Segment Advisor. Please refer to Oracle Database PL/SQL Packages and Types Reference for more details on these procedures.

select table_name, iot_name, iot_type from dba_tables;

Example The example that follows shows how to use the DBMS_ADVISOR procedures to run the Segment Advisor for the sample table hr.employees. The user executing these package procedures must have the EXECUTE object privilege on the package or the ADVISOR system privilege.

Note that passing an object type of TABLE to DBMS_ADVISOR.CREATE_OBJECT amounts to an object level request. If the table is not partitioned, the table segment is analyzed (without any dependent segments like index or LOB segments). If the table is partitioned, the Segment Advisor analyzes all table partitions and generates separate findings and recommendations for each.

variable id number;
begin
  declare
  name varchar2(100);
  descr varchar2(500);
  obj_id number;
  begin
  name:='Manual_Employees';
  descr:='Segment Advisor Example';

  dbms_advisor.create_task (
    advisor_name     => 'Segment Advisor',
    task_id          => :id,
    task_name        => name,
    task_desc        => descr);

  dbms_advisor.create_object (
    task_name        => name,
    object_type      => 'TABLE',
    attr1            => 'HR',
    attr2            => 'EMPLOYEES',
    attr3            => NULL,
    attr4            => NULL,
    attr5            => NULL,
    object_id        => obj_id);

  dbms_advisor.set_task_parameter(
    task_name        => name,
    parameter        => 'recommend_all',
    value            => 'TRUE');

  dbms_advisor.execute_task(name);
  end;
end; 
/

Viewing Segment Advisor Results with Enterprise Manager

With Enterprise Manager (EM), you can view Segment Advisor results for both Automatic Segment Advisor runs and manual Segment Advisor runs. You can view the following types of results:

• All recommendations (multiple automatic and manual Segment Advisor runs)

• Recommendations from the last Automatic Segment Advisor run

• Recommendations from a specific run

• Row chaining findings

You can also view a list of the segments that were analyzed by the last Automatic Segment Advisor run.

To view Segment Advisor results with EM—All runs:

1. On the database Home page, under the Space Summary heading, click the numeric link next to the title Segment Advisor Recommendations.

   
   Description of the illustration space_summary_seg_advisor.gif
   
   

   The Segment Advisor Recommendations page appears. Recommendations are organized by tablespace.

   Figure 18-3 Segment Advisor Recommendations page

   
   Description of "Figure 18-3 Segment Advisor Recommendations page"
   
   

2. If any recommendations are present, select a tablespace, and then click Recommendation Details.

   The Recommendation Details page appears. You can initiate the recommended activity from this page (shrink or reorganize).

   Figure 18-4 Recommendation Details page

   
   Description of "Figure 18-4 Recommendation Details page"
   
   

   Tip:

   The list entries are sorted in descending order by reclaimable space. You can click column headings to change the sort order or to change from ascending to descending order.

To view Segment Advisor results with EM—Last Automatic Segment Advisor run:

1. On the database Home page, under the Space Summary heading, click the numeric link next to the title Segment Advisor Recommendations.

   The Segment Advisor Recommendations page appears. (See Figure 18-3.)

2. In the View drop-down list, select Recommendations from Last Automatic Run.

3. If any recommendations are present, click in the Select column to select a tablespace, and then click Recommendation Details.

   The Recommendation Details page appears. (See Figure 18-4.) You can initiate the recommended activity from this page (shrink or reorganize).

To view Segment Advisor results with EM—Specific run:

1. Start at the Advisor Central page.

   If you ran the Segment Advisor with the Enterprise Manager wizard, the Advisor Central page appears after you submit the Segment Advisor task. Otherwise, to get to this page, on the database Home page, under Related Links, click Advisor Central.

2. Check that your task appears in the list under the Results heading. If it does not, complete these steps (See Figure 18-2):

   1. In the Search section of the page, under Advisor Tasks, select Segment Advisor in the Advisory Type list.

   2. In the Advisor Runs list, select All or the desired time period.

   3. (Optional) Enter a task name.

   4. Click Go.

      Your Segment Advisor task appears in the Results section.

3. Check the status of the job. If it is not COMPLETED, click the Refresh button at the top of the page until your task status shows COMPLETED. (Do not use your browser's refresh icon.)

4. Click the task name.

   The Segment Advisor Task page appears, with recommendations organized by tablespace.

5. Select a tablespace in the list, and then click Recommendation Details.

   The Recommendation Details page appears. (See Figure 18-4.) You can initiate the recommended activity from this page (shrink or reorganize).

To view row chaining findings:

1. On the database Home page, under the Space Summary heading, click the numeric link next to the title Segment Advisor Recommendations.

   The Segment Advisor Recommendations page appears. (See Figure 18-3.)

2. Under the Related Links heading, click Chained Row Analysis.

   The Chained Row Analysis page appears, showing all segments that have chained rows, with a chained rows percentage for each.

Viewing Segment Advisor Results by Querying the DBA_ADVISOR_* Views

The headings of Table 18-5 show the columns in the DBA_ADVISOR_* views that contain output from the Segment Advisor. Refer to Oracle Database Reference for a description of these views. The table contents summarize the possible outcomes. In addition, Table 18-2 defines the columns in the DBA_ADVISOR_OBJECTS view that contain information on the analyzed segments.

Before querying the DBA_ADVISOR_* views, you can check that the Segment Advisor task is complete by querying the STATUS column in DBA_ADVISOR_TASKS.

select task_name, status from dba_advisor_tasks
   where owner = 'STEVE' and advisor_name = 'Segment Advisor';
 
TASK_NAME                      STATUS
------------------------------ -----------
Manual Employees               COMPLETED

The following example shows how to query the DBA_ADVISOR_* views to retrieve findings from all Segment Advisor runs submitted by user STEVE:

select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message 
  from dba_advisor_findings af, dba_advisor_objects ao
  where ao.task_id = af.task_id
  and ao.object_id = af.object_id
  and ao.owner = 'STEVE';

TASK_NAME          SEGNAME      PARTITION       TYPE             MESSAGE
------------------ ------------ --------------- ---------------- --------------------------
Manual_Employees   EMPLOYEES                    TABLE            The free space in the obje
                                                                 ct is less than 10MB.
 
Manual_Salestable4 SALESTABLE4  SALESTABLE4_P1  TABLE PARTITION  Perform shrink, estimated
                                                                 savings is 74444154 bytes.
 
Manual_Salestable4 SALESTABLE4  SALESTABLE4_P2  TABLE PARTITION  The free space in the obje
                                                                 ct is less than 10MB.

Viewing Segment Advisor Results with DBMS_SPACE.ASA_RECOMMENDATIONS

The ASA_RECOMMENDATIONS procedure in the DBMS_SPACE package returns a nested table object that contains findings or recommendations for Automatic Segment Advisor runs and, optionally, manual Segment Advisor runs. Calling this procedure may be easier than working with the DBA_ADVISOR_* views, because the procedure performs all the required joins for you and returns information in an easily consumable format.

The following query returns recommendations by the most recent run of the Auto Segment Advisor, with the suggested command to run to follow the recommendations:

select tablespace_name, segment_name, segment_type, partition_name,
recommendations, c1 from
table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));


TABLESPACE_NAME                SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------------------ --------------
PARTITION_NAME
------------------------------
RECOMMENDATIONS
-----------------------------------------------------------------------------
C1
-----------------------------------------------------------------------------
TVMDS_ASSM                     ORDERS1                        TABLE PARTITION
ORDERS1_P2
Perform shrink, estimated savings is 57666422 bytes.
alter table "STEVE"."ORDERS1" modify partition "ORDERS1_P2" shrink space
 
TVMDS_ASSM                     ORDERS1                        TABLE PARTITION
ORDERS1_P1
Perform shrink, estimated savings is 45083514 bytes.
alter table "STEVE"."ORDERS1" modify partition "ORDERS1_P1" shrink space
 
TVMDS_ASSM_NEW                 ORDERS_NEW                     TABLE
 
Perform shrink, estimated savings is 155398992 bytes.
alter table "STEVE"."ORDERS_NEW" shrink space
 
TVMDS_ASSM_NEW                 ORDERS_NEW_INDEX               INDEX
 
Perform shrink, estimated savings is 102759445 bytes.
alter index "STEVE"."ORDERS_NEW_INDEX" shrink space

See Oracle Database PL/SQL Packages and Types Reference for details on DBMS_SPACE.ASA_RECOMMENDATIONS.