OCP 1Z0 053 201

201.Examine the following PL/SQL block: 
SET SERVEROUTPUT ON
SET LONG 10000
DECLARE report clob; 
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(); 
DBMS_OUTPUT.PUT_LINE(report); 
END; 
Which statement describes the effect of the execution of the above PL/SQL block? 
A. The plan baselines are verified with the SQL profiles. 
B. All fixed plan baselines are converted into nonfixed plan baselines. 
C. All the nonaccepted SQL profiles are accepted into the plan baseline. 
D. The nonaccepted plans in the SQL Management Base are verified with the existing plan baselines. 

Answer: D

http://docs.oracle.com/cd/E11882_01/server.112/e41573/optplanmgmt.htm#PFGRF95117

15.2.3.1 Evolving Plans with Manual Plan Loading

You can evolve an existing SQL plan baseline by manually loading plans from the shared SQL area or from a SQL tuning set. When you manually load plans into a SQL plan baseline, the database adds these loaded plans as accepted plans.

See Also:

"Creating Baselines from Existing Plans"

15.2.3.2 Evolving Plans with DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE

The PL/SQL function DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE tries to evolve new plans that have been added by the optimizer to the plan history of existing plan baselines. If the function can verify that the new plan performs better than a plan chosen from the corresponding SQL plan baseline, then the database adds the new plan as an accepted plan.

The following example of the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function evolves a new plan for a SQL statement identified by its SQL handle, which is its unique SQL identifier in string form. You can find the SQL handle by querying DBA_SQL_PLAN_BASELINES.SQL_HANDLE.

SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
    report clob;
BEGIN
    report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
                  sql_handle => 'SYS_SQL_593bc74fca8e6738');
    DBMS_OUTPUT.PUT_LINE(report);
END;
/

The following output shows that Oracle Database successfully evolved a plan:

REPORT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
                       Evolve SQL Plan Baseline Report
--------------------------------------------------------------------------------
 
Inputs:
-------
 SQL_HANDLE = SYS_SQL_593bc74fca8e6738
 PLAN_NAME  =
 TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
 VERIFY     = YES
 COMMIT     = YES
 
Plan: SYS_SQL_PLAN_ca8e6738a57b5fc2
-----------------------------------
 Plan was verified: Time used .07 seconds.
 Passed performance criterion: Compound improvement ratio >= 7.32.
 Plan was changed to an accepted plan.
 
                     Baseline Plan      Test Plan     Improv. Ratio
                     -------------      ---------     -------------
 Execution Status:        COMPLETE       COMPLETE
 Rows Processed:                40             40
 Elapsed Time(ms):              23              8              2.88
 CPU Time(ms):                  23              8              2.88
 Buffer Gets:                  450             61              7.38
 Disk Reads:                     0              0
 Direct Writes:                  0              0
 Fetches:                        0              0
 Executions:                     1              1
 
-------------------------------------------------------------------------------
                                Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 1.

Alternatively, you can use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to specify:

• The name of a particular plan to evolve

• A list of plans to evolve

• No value

  By specifying no value, you enable Oracle Database to evolve all nonaccepted plans currently in the SMB.