Thanks for the question, John.
Asked: February 25, 2021 - 8:52 pm UTC
Last updated: March 03, 2021 - 9:22 am UTC
Viewed 100+ times
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."
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.