Skip to Main Content
  • Questions
  • Two approaches to see the Segment Advisor findings?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, John.

Asked: February 25, 2021 - 8:52 pm UTC

Last updated: March 03, 2021 - 9:22 am UTC

Version: 19c

Viewed 100+ times

You Asked

Greetings,

I have come across two approaches to view the findings of the segment advisor. Here they are:

1.) select tablespace_name, segment_name, segment_type, partition_name,recommendations, c1 from table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));
2.) select a.message, b.created from DBA_ADVISOR_FINDINGS a, DBA_ADVISOR_TASKS b where a.task_id=b.task_id and a. task_name like 'SYS_AUTO_SPC%' order by 2 desc;

Which is the one that we should use? My guess is that we should rely on the first one, right? The second one shows a history of all of the findings.

Also, I am surprised to see that it recommends shrinking a table that will free only 16MB from a table that has 112MB in size. "Enable row movement of the table RC1.JECT3 and perform shrink, estimated savings is 16349883 bytes."

Thanks,

John

and we said...

It depends on what you're looking for.

With dbms_space.asa_recommendations you either see the most recent recommendations or all (false/true for the first parameter), including/excluding manual runs (true/false for the second).

Querying the *_advisor_* views you can find details for a specific task. For example, if you've run the advisor manually, you may want to be sure you get the details of that task.

I am surprised to see that it recommends shrinking a table that will free only 16MB from a table that has 112MB in size.

The segment advisor recommends a shrink when there's a "significant" amount of free space. I don't know the exact details of the algorithm, but 16Mb of 112Mb is around 14% free space, which feels large enough to be significant to me.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.