Examine the following PL/SQL block:
DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => ’99twu5t2dn5xd’);
END;
/
Which statement is true about the plan being loaded into the SQL plan baseline by the above command?
A.
It is loaded with the FIXED status.
B.
It is loaded with the ACCEPTED status.
C.
It is not loaded with the ENABLED status.
D.
It is not loaded with the ACCEPTED status.
Explanation:
You can create SQL plan baselines by manually loading existing plans for a set of SQL statements as plan
baselines. The database does not verify manually loaded plans for performance, but adds them as accepted
plans to existing or new SQL plan baselines. (Refer to here 15.2.1.2 Creating Baselines from Existing Plans)
The DBMS_SPM package supports the SQL plan management feature by providing an interface for the DBA or
other user to perform controlled manipulation of plan history and SQL plan baselines maintained for various
SQL statements.
LOAD_PLANS_FROM_CURSOR_CACHE Functions, This function loads one or more plans present in the
cursor cache for a SQL statement, or a set of SQL statements. It has four overloads: using SQL statement text,
using SQL handle, using SQL ID, or using attribute_name and attribute_value pair.
By default, the parameter of LOAD_PLANS_FROM_CURSOR_CACHE has:
1. fixed = ‘NO’, means loaded with non-fixed plans.
2. enabled = ‘YES’, means that it is enabled for use by the optimizer.