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


Comments

Post a Comment

Popular posts from this blog

Set up CrashPlan on QNAP NAS using QPKG, the easy way

Image
Introduction to CrashPlan Description Here's how Wikipedia describes CrashPlan backup software: CrashPlan is backup software that allows Windows, Mac, Linux and Solaris users to back up their data to an offsite data center, computers belonging to friends and family, as well as to attached drives / shared folders. There is a free version (for non-datacenter backups) and CrashPlan+ which is for backing up to CrashPlan's servers. Both versions allow users to back up data automatically. How it can be used on your NAS If you are the owner of a NAS from QNAP, you can install CrashPlan and use it to backup the contents of your NAS to the CrashPlan data center, to other computers and/or external harddisks. You can also allow incoming backups, so that you and your friends and family can back up valuable documents and files to your NAS. Installation instructions I will not go into any more detail about what CrashPlan is. It's time to get it installed and set up on a
Read more

Samsung Account "processing failed" after firmware upgrade

How to resolve the "Processing failed" error logging in to Samsung Account after upgrade A Samsung Account can be used to remotely control your phone. From your web browser you can locate your phone on a map, trigger an alarm so that you can find it in your home, wipe the contents of the phone if it's been stolen, and more. Simply put; it may prove to be very useful when something bad has happened. After upgrading my Samsung Galaxy S2 phone to Ice Cream Sandwich (ICS), I got the error message "Processing failed" when trying to log in to my Samsung Account. Searching the web I found that I'm definitely not the only one. The solution is very easy, once you know about it.
Read more

How to remove Vlingo/Voice Command from your Android phone without rooting

Here's an easy way to remove/uninstall "Voice Command" from Vlingo, the annoying app that starts every time the home button is double-tapped on Samsung Galaxy S2. And sometimes when it's just pressed once... Since "Voice Command" is a system application, it's not easily uninstalled and used to require the phone to be rooted and thus warranty voided. Unlike the other ways of removing the app, this method works on Android phones that have been upgraded to "Ice-Cream Sandwich" (ICS) and does not require the phone to be "rooted" . Please note that there may not be a way to install it again, should you change your mind later. Maybe a factory reset brings it back, maybe it doesn't. I don't know, and have not tried to get it back. Follow these steps to remove "Voice Command" from your Galaxy S2 with ICS without root
Read more