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.
Refer to here for details.