June 20, 2012

How to view Segment Advisor recommendations

ASA_RECOMMENDATIONS Function

Here's a quick note on how to view Segment Advisor recommendations using the ASA_RECOMMENDATIONS function of the DBMS_SPACE package.

If a segment would benefit from a segment shrink, reorganization, or compression, the Segment Advisor generates a recommendation for the segment.

You can view results in the following ways:
  • With Enterprise Manager
  • By querying the DBA_ADVISOR_* views
  • By calling the DBMS_SPACE.ASA_RECOMMENDATIONS procedure

The ASA_RECOMMENDATIONS function returns recommendations using the stored results of the auto segment advisor. This function returns results from the latest run on any given object.




Syntax

DBMS_SPACE.ASA_RECOMMENDATIONS (
   all_runs        IN    VARCHAR2 DEFAULT := TRUE,
   show_manual     IN    VARCHAR2 DEFAULT := TRUE,
   show_findings   IN    VARCHAR2 DEFAULT := FALSE)
 RETURN ASA_RECO_ROW_TB PIPELINED;

Parameters

ASA_RECOMMENDATIONS Procedure Parameters

Parameter Description
all_runs If TRUE, returns recommendations/findings for all runs of auto segment advisor. If FALSE, returns the results of the LATEST run only. LATEST does not make sense for manual invocation of segment advisor. This is applicable only for auto advisor.
show_manual If TRUE, we show the results of manual invocations only. The auto advisor results are excluded. If FALSE, results of manual invocation of segment advisor are not returned.
show_findings Show only the findings instead of the recommendations



Usage example

Here is an example of how to use the DBMS_SPACE.ASA_RECOMMENDATIONS subprogram to view the recommendations for all runs of auto segment advisor.

   SELECT recommendations, c1, c2, c3 
   FROM table(dbms_space.asa_recommendations('TRUE', 'FALSE', 'FALSE'));

   c1
   -----------------------------------------------
   c2
   -----------------------------------------------
   c3
   -----------------------------------------------
   recommendations   
   -----------------------------------------------
   alter table "U"."T1" shrink space
   alter table "U"."T1" shrink space COMPACT
   alter table "U"."T1" enable row movement
   Enable row movement of the table U.T1 and perform shrink, estimated
   savings is 351890073 bytes.

   Compress object U.T2, estimated savings is 6998196224 bytes.
   alter table "U"."T2" compress for oltp
   alter table "U"."T2" move
   <null> 


Columns of the ASA_RECOMMENDATIONS function

These are the columns that can be selected when using the function. This information is useful when building scripts to automatically act on the recommendations. It can be more convenient to get the information from this function instead of joining the different DBA_ADVISOR_* views together.

  • TABLESPACE_NAME
  • SEGMENT_OWNER
  • SEGMENT_NAME
  •  SEGMENT_TYPE
  • PARTITION_NAME
  • ALLOCATED_SPACE
  • USED_SPACE
  • RECLAIMABLE_SPACE
  • CHAIN_ROWEXCESS
  • IOREQPM
  • IOWAITPM
  • IOWAITPR
  • RECOMMENDATIONS
  • C1
  • C2
  • C3
  • TASK_ID
  • MESG_ID


No comments:

Post a Comment