Query Segment Advisor Recommendations with DBA_ADVISOR_FINDINGS

Query Segment Advisor Recommendations with DBA_ADVISOR_FINDINGS

Purpose

Which segments are actually worth shrinking right now, and which recommendation is safe to act on without second-guessing it? Oracle already ran that analysis — usually overnight, during the default automatic maintenance window — and wrote the answer into the same view every advisor in the database shares: DBA_ADVISOR_FINDINGS. Oracle's own reference describes it plainly: the view "displays the findings discovered by all advisors in the database." Segment Advisor, SQL Access Advisor, SQL Tuning Advisor, and the Automatic Database Diagnostic Monitor all land their output in the same rows, distinguished only by TASK_NAME and the finding's TYPE.

That shared design is also what makes Segment Advisor output confusing the first time a DBA goes looking for it. There are two legitimate, documented paths to the same recommendation: a packaged table function built specifically for segment-level advice, or a direct query against the general advisor framework views, filtered down to the segment-space task. Both approaches are laid out side by side in a documented Q&A addressing this exact question — DBMS_SPACE.ASA_RECOMMENDATIONS for the direct, segment-scoped answer, and a join across DBA_ADVISOR_FINDINGS and DBA_ADVISOR_TASKS for the audit trail of everything the advisor framework has ever found. Each answers a different question. The automatic nightly run that produces most of what a DBA finds in these views is named with a recognizable prefix — SYS_AUTO_SPC% — which is the filter that turns the general-purpose findings view into a Segment Advisor report specifically.

This post covers both paths: pulling the current recommendation set straight from DBMS_SPACE.ASA_RECOMMENDATIONS, auditing the automatic Segment Advisor task's history through DBA_ADVISOR_FINDINGS, ranking every advisor's findings by impact, walking a finding's parent/symptom chain, and pulling the matching DBA_ADVISOR_RECOMMENDATIONS and DBA_ADVISOR_ACTIONS rows.

Code

 1-- Query 1: the direct, packaged answer -- current Segment Advisor recommendations,
 2-- no advisor-framework views involved at all
 3SELECT tablespace_name, segment_name, segment_type, partition_name,
 4       recommendations, c1
 5FROM   TABLE(DBMS_SPACE.ASA_RECOMMENDATIONS('FALSE', 'FALSE', 'FALSE'));
 6
 7-- Query 2: the advisor-framework answer -- Segment Advisor's own task history,
 8-- newest first
 9SELECT a.message, b.created
10FROM   dba_advisor_findings a, dba_advisor_tasks b
11WHERE  a.task_id = b.task_id
12AND    a.task_name LIKE 'SYS_AUTO_SPC%'
13ORDER  BY b.created DESC;
14
15-- Query 3: rank findings from every advisor by their share of total impact
16SELECT *
17FROM (
18  SELECT ROUND((RATIO_TO_REPORT(MAX(impact)) OVER () * 100)) AS pct_impact_overall,
19         finding_name, type, MIN(impact) min_impact, MAX(impact) max_impact,
20         impact_type, COUNT(*)
21  FROM   dba_advisor_findings
22  WHERE  impact_type IS NOT NULL
23  GROUP  BY impact_type, finding_name, type
24)
25WHERE  pct_impact_overall >= 5
26ORDER  BY pct_impact_overall DESC;
27
28-- Query 4: only findings a directive has NOT filtered out of the report
29SELECT task_name, finding_name, type, message
30FROM   dba_advisor_findings
31WHERE  filtered = 'N'
32AND    task_name LIKE 'SYS_AUTO_SPC%'
33ORDER  BY task_name;
34
35-- Query 5: walk a finding's parent/symptom chain within one task
36SELECT child.finding_name  AS child_finding,
37       child.type          AS child_type,
38       parent.finding_name AS parent_finding,
39       parent.type         AS parent_type
40FROM   dba_advisor_findings child, dba_advisor_findings parent
41WHERE  child.parent  = parent.finding_id
42AND    child.task_id = parent.task_id
43AND    child.task_name LIKE 'SYS_AUTO_SPC%';
44
45-- Query 6: the same finding set, scoped to the current user's own tasks
46SELECT finding_name, type, impact_type, impact, message
47FROM   user_advisor_findings
48WHERE  type = 'PROBLEM'
49ORDER  BY impact DESC;
50
51-- Query 7: recommendations ranked by benefit across every recommendation type
52-- the advisor framework has logged
53SELECT ROUND((RATIO_TO_REPORT(MAX(benefit)) OVER () * 100)) AS overall_benefit_pct,
54       type, MIN(benefit) min_benefit, MAX(benefit) max_benefit, COUNT(*) cnt
55FROM   dba_advisor_recommendations
56WHERE  type IS NOT NULL
57GROUP  BY type
58ORDER  BY 1 DESC;
59
60-- Query 8: the specific actions an advisor task actually logged
61SELECT command, message, COUNT(*)
62FROM   dba_advisor_actions
63GROUP  BY command, message;

Code Breakdown

Query 1: DBMS_SPACE.ASA_RECOMMENDATIONS

This is the shortest path to a Segment Advisor answer — no joins, no task-name filtering, just the current recommendation set for every segment the advisor has evaluated. The three arguments are all_runs, show_manual and show_findings, per the DBMS_SPACE package reference. Passing 'FALSE' for all three returns only the latest automatic run, leaves out manual advisor runs, and returns recommendations rather than findings. The defaults are TRUE, TRUE and FALSE, so calling the function with no arguments returns every automatic and manual run.

Query 2: the advisor-framework audit trail

Filtering DBA_ADVISOR_FINDINGS.TASK_NAME to SYS_AUTO_SPC% isolates the automatic Segment Advisor's own runs from every other advisor task sharing the same view. Joining to DBA_ADVISOR_TASKS for CREATED turns a flat message list into a dated history — useful for confirming a recommendation is current rather than left over from weeks ago.

Query 3: ranking findings by impact

This ratio-to-report pattern groups by IMPACT_TYPE, FINDING_NAME and TYPE, then expresses each group's maximum impact as a percentage of the sum of every group's maximum. Only groups worth 5% or more of that total are kept. It runs across all advisors, not just Segment Advisor, because it only counts rows where IMPACT_TYPE is set, and the reference does not say which advisors set it. Read it as a way to see where any segment-space finding sits among everything the advisor framework has logged. If no Segment Advisor finding appears, that is not proof the segments are healthy; use Queries 1 and 2 for that.

Query 4: FILTERED, a column that didn't always exist

FILTERED is documented on the current 19c reference as a Y/N flag — a row marked Y was excluded from the report by a directive, N means it stood. It is absent from the 10g-era reference for this same view entirely, alongside EXECUTION_NAME, FINDING_NAME, TYPE_ID, and FLAGS — a DBA working from an older script written against the 10g column set will not find any of those five columns there to filter on.

Query 5: PARENT and the finding hierarchy

PARENT holds "the identifier of the parent finding" — Oracle's advisors build findings as a tree, where an ERROR or SYMPTOM finding can roll up into a broader PROBLEM. Self-joining the view on child.parent = parent.finding_id, scoped to the same task_id, reconstructs that hierarchy instead of reading a flat list of messages with no sense of which are root causes and which are downstream symptoms.

Query 6: USER_ADVISOR_FINDINGS

The related view in the same reference page drops the OWNER column and scopes results to findings from tasks the connected user actually owns — the version to use for a non-DBA account that should only see its own advisor history, filtered here to TYPE = 'PROBLEM' to skip the lower-severity INFORMATION rows.

Query 7: DBA_ADVISOR_RECOMMENDATIONS

Recommendations are a separate view from findings — a finding says what's wrong; a recommendation says what to do about it, each carrying a BENEFIT value. Grouping by TYPE and ranking by the same ratio-to-report technique is how a segment-tuning recommendation gets compared, on equal footing, against every other recommendation type the advisor framework has logged.

Query 8: DBA_ADVISOR_ACTIONS

Each recommendation is itself made up of one or more concrete actions — the actual COMMAND the advisor suggests running, with its MESSAGE text. Grouping by both columns together surfaces which specific commands recur most often across the recommendations already reviewed in Query 7.

Key Points

• DBA_ADVISOR_FINDINGS is not Segment-Advisor-specific. It holds findings from "all advisors in the database," so every query against it for Segment Advisor needs a TASK_NAME filter to avoid mixing in SQL Tuning Advisor or ADDM rows.
• SYS_AUTO_SPC% is the automatic Segment Advisor task-name prefix used to isolate its runs from everything else sharing the view.
• Two documented approaches answer the same question differently. DBMS_SPACE.ASA_RECOMMENDATIONS returns the recommendations directly, with no run date; the advisor-framework views carry each task's creation date and the full history.
• The 19c and 10g column sets differ. EXECUTION_NAME, FINDING_NAME, TYPE_ID, FILTERED, and FLAGS exist on the current reference and not on the 10g-era one for the same view.
• TYPE carries four values — PROBLEM, SYMPTOM, ERROR, INFORMATION — and PARENT links them into a hierarchy rather than a flat list.
• Findings, recommendations, and actions are three separate views. A finding states a problem, a recommendation proposes a fix with a benefit value, and an action names the specific command behind that fix.

Insights and Best Practices

Treat a shrink recommendation's size as a signal, not a verdict

One documented case flagged a recommendation to shrink a table that would only free 16 MB out of 112 MB — about 14% free space — and the explanation given was that Segment Advisor flags a shrink when free space is "significant," without a published fixed percentage threshold. Reading the recommendation's estimated-savings figure alongside the segment's actual size, rather than acting on the recommendation's existence alone, is the more defensible check before running ALTER TABLE ... SHRINK SPACE.

Use the framework query when you need history, not just the current snapshot

DBMS_SPACE.ASA_RECOMMENDATIONS is faster to write and read, but it does not carry a date. Once the question becomes "has this recommendation been showing up for weeks," or "what did last month's automatic run find that this month's run didn't," the DBA_ADVISOR_FINDINGS-to-DBA_ADVISOR_TASKS join is the version that actually answers it.

Build the impact ranking before reading message text one row at a time

DBA_ADVISOR_FINDINGS can carry a long list of messages after weeks of automatic runs. Running Query 3 before reading individual MESSAGE strings shows which findings, from any advisor, carry the most recorded impact, so the reading starts with those.

Confirm the automatic run is actually enabled before trusting an empty result

Automatic Segment Advisor runs during the database's maintenance window by default, but maintenance-window jobs can be disabled at the site or database level. An empty SYS_AUTO_SPC% result set is ambiguous between "nothing needs shrinking" and "the job never ran" — checking the scheduled job's own status is the way to tell those two apart before reporting either one.

When to Use This

• Deciding whether to act on a shrink recommendation, and by how much space it's actually worth.
• Auditing what Segment Advisor has found over several automatic runs, not just the most recent one.
• Comparing a segment-space finding's impact against everything else the advisor framework has logged, to prioritize where to look first.
• Tracing a finding back through its parent/symptom chain before assuming a surface-level message is the root cause.
• Pulling the concrete command behind a recommendation, rather than just its prose description.
• Scoping advisor history to a specific non-DBA account through USER_ADVISOR_FINDINGS.

Troubleshooting Common Issues

DBA_ADVISOR_FINDINGS returns rows, but none of them look like Segment Advisor output. Check the TASK_NAME filter — without LIKE 'SYS_AUTO_SPC%', the query is pulling findings from every advisor sharing the view, not just Segment Advisor's automatic runs.

A column referenced in a newer script (FILTERED, FINDING_NAME, EXECUTION_NAME) doesn't exist on an older database. Confirm the Oracle version against the reference for that release — these columns are documented on the current 19c reference and are absent from the 10g-era reference for the same view.

DBMS_SPACE.ASA_RECOMMENDATIONS and the DBA_ADVISOR_FINDINGS query return different-looking results for what seems like the same question. This is expected. With 'FALSE' as its first argument the function returns only the latest automatic run, while the framework query returns every run's findings, newest first.

References

• DBA_ADVISOR_FINDINGS — Database Reference 19c - canonical current column reference, including FINDING_NAME, FILTERED, and the "all advisors" scope statement
• DBA_ADVISOR_FINDINGS — Database Reference 10g Release 1 - the earlier column set, for confirming which columns were added after 10g
• Two approaches to see the Segment Advisor findings? — AskTom - the direct source for both query paths in this post, including the shrink-threshold discussion
• DBMS_SPACE — PL/SQL Packages and Types Reference 19c - the ASA_RECOMMENDATIONS signature and its all_runs, show_manual and show_findings parameters
• Oracle's Advisor Framework – Part 4 – Querying the advisor framework — rogercornejo.com - the ratio-to-report ranking pattern against DBA_ADVISOR_FINDINGS, DBA_ADVISOR_RECOMMENDATIONS, and DBA_ADVISOR_ACTIONS used in Queries 3, 7, and 8