Making Sense of Oracle SPM Auto Evolve Report

One of my favorite Oracle optimizer features in recent releases is the SPM Auto Evolve Job. Recently, I came up with an SPM Auto Evolve Report Summarizer script to make DBMS_SPM.REPORT_AUTO_EVOLVE_TASK output easier to review during SQL plan baseline investigations. As we know, this is a scheduler job that

• Identifies the Top SQLs from AWR
• Looks for alternative plans in all available sources
• Adds unaccepted plans to the plan history
• Tests the execution of as many plans as possible during the maintenance window
• If automatic acceptance is enabled and the alternative plan performs better, Oracle can evolve and accept the new baseline plan automatically.

Every time this job executes, the execution details – like which SQLIDs were considered for evaluation, where the captured plan failed the test, and where it succeeded. Now these reports are useful, but they are not always easy to review quickly.

To help answer the question “What did the SPM auto-evolve task actually do in its last execution?” I decided to come up with a handy script, think of it as an “SPM Report Summarizer”. It is available in the GitHub repo linked review_spm_autoevolve_report.sql

The goal is: take the DBMS_SPM.REPORT_AUTO_EVOLVE_TASK output and parse it into a cleaner DBA review summary.

The need for Oracle SPM Auto Evolve Report Summarizer

Oracle provides DBMS_SPM.REPORT_AUTO_EVOLVE_TASK to display the results of an automatic SPM evolve task execution. The function returns a CLOB report and supports report types such as TEXT, HTML, and XML. When execution_name is passed as NULL, Oracle generates the report for the last task execution.

SET SERVEROUTPUT ON SIZE UNLIMITED
SET LONG 100000

DECLARE
    v_report CLOB;
BEGIN
    v_report := DBMS_SPM.REPORT_AUTO_EVOLVE_TASK(
        TYPE    => 'TEXT', 
        LEVEL   => 'ALL', 
        SECTION => 'ALL'
    );
    DBMS_OUTPUT.PUT_LINE(v_report);
END;
/

This report is long. It can include multiple sections. It can include execution statistics, recommendations, failed benefit checks, adaptive plan cases, duplicate or final-plan cases, and errors. However, sometimes we first want to view a quick summary.

Oracle SPM Auto Evolve Report Summarizer

This script outputs the text from DBMS_SPM.REPORT_AUTO_EVOLVE_TASK, esp the Object ID and groups it into sections as follows

• Category A: Failed Benefit / Not Accepted
• Category B: Adaptive / Duplicate
• Category C: Unclassified / Review Required
• Category D: Accepted Improvements

Category A: Failed Benefit / Not Accepted

These are simply findings for SQLID’s where the test plan did not pass the benefit criterion. The script prints details such as:

• OBJ_ID
• SQL_ID
• SQL_HANDLE
• TEST_PLAN_NAME
• BASE_PLAN_NAME

Category B: Adaptive Plans

These are SQL IDs where the candidate plan was adaptive during SPM evolve verification. Oracle evaluated the plan by executing it, then compared the performance of the final resolved execution plan against the existing accepted baseline. If the final resolved plan met the benefit criterion, Oracle may drop the adaptive candidate and create a new accepted baseline matching the final executed plan.

This category is kept separate because the plan hash value of the originally captured adaptive plan may differ from the final resolved plan accepted into the baseline. It helps identify cases where the improved plan came from an adaptive execution path rather than a directly matching captured plan.

Category C: Unclassified / Review Required

This category catches anything the script does not confidently classify, including ORA errors.

Category D: Accepted Improvements

For entries that pass the benefit criterion, and PSM decides that the plans can be accepted, the script prints:

• OBJ_ID
• SQL_ID
• SQL_HANDLE
• BASE_PLAN_HASH
• TEST_PLAN_HASH
• LAST_VERIFIED
• BASE_ELAPSED_S
• TEST_ELAPSED_S
• BEN_PCT
• IMPROV_FACT

The plan hash values are derived by calling DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE for the base and test plans.

A note about BEN_PCT & IMPROV_FACT

The script currently derives BEN_PCT & IMPROV_FACTfrom the improvement factor like this:

BEN_PCT= round((p_factor - 1) * 100, 2)
IMPROV_FACT= round((1 - (1 / p_factor)) * 100, 2)

That means BEN_PCT is a speedup percentage derived from the reported improvement factor. That is useful if you think in terms of “times faster.”

IMPROV_FACT = 2.00  -> BEN_PCT = 100.00
IMPROV_FACT = 12.00 -> BEN_PCT = 1100.00

If you prefer elapsed-time reduction, the formula would be different:

2x speedup -> 50% elapsed-time reduction
12x speedup -> about 91.67% elapsed-time reduction

Please note this is just to summarize and make it easier to find SQLID in the SPM job and find a quick summary of the results for the SPM Evolve job.
TO dig in further, review SQL plan, validate the SQL plan baseline from the data dictionary, pull a focused report for that object_id.

SET SERVEROUTPUT ON SIZE UNLIMITED
SET LONG 100000

DECLARE
    v_report CLOB;
    v_pos    PLS_INTEGER := 1;
    v_len    PLS_INTEGER;
BEGIN
    v_report := DBMS_SPM.REPORT_AUTO_EVOLVE_TASK(
        object_id => :object_id,
        type      => 'TEXT',
        level     => 'ALL',
        section   => 'ALL'
    );

    v_len := DBMS_LOB.GETLENGTH(v_report);

    WHILE v_pos <= v_len LOOP
        DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(v_report, 32000, v_pos));
        v_pos := v_pos + 32000;
    END LOOP;
END;

/

If automatic acceptance matters in your environment, also check the task parameters:

select parameter_name,
       parameter_value
from   dba_advisor_parameters
where  task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'
and    parameter_name in (
         'ACCEPT_PLANS',
         'TIME_LIMIT',
         'ALTERNATE_PLAN_SOURCE',
         'ALTERNATE_PLAN_BASELINE'
       )
order  by parameter_name;

Note if ACCEPT_PLANS is TRUE, Recommended plans can be accepted automatically. If ACCEPT_PLANS is FALSE, the task can verify plans and report findings without automatically evolving the plans.

Final Thoughts

The purpose of this SPM Auto Evolve Report Summarizer is to help DBAs quickly identify:

• Which SQL IDs were evaluated
• Which plans failed verification
• Which plans were accepted
• and which findings require deeper review

Once an interesting SQL ID or OBJECT_ID is identified, you can still drill deeper into the original SPM evolve report, SQL plan baselines, and execution plans for full validation.