OCP 1Z0 053 226

226.Examine the following PL/SQL block: 
DECLARE 
my_plans pls_integer; 
BEGIN 
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (sql_id 
=> ?9twu5t2dn5xd?; 
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. 
Answer: B

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_spm.htm#ARPLS68168

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.

Syntax

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (   sql_id            IN  VARCHAR2,   plan_hash_value   IN  NUMBER   := NULL,   sql_text          IN  CLOB,   fixed             IN  VARCHAR2 := 'NO',   enabled           IN  VARCHAR2 := 'YES') RETURN PLS_INTEGER;DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (   sql_id            IN  VARCHAR2,   plan_hash_value   IN  NUMBER   := NULL,   sql_handle        IN  VARCHAR2,   fixed             IN  VARCHAR2 := 'NO',   enabled           IN  VARCHAR2 := 'YES') RETURN PLS_INTEGER;DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (   sql_id            IN  VARCHAR2,   plan_hash_value   IN  NUMBER   := NULL,   fixed             IN  VARCHAR2 := 'NO',   enabled           IN  VARCHAR2 := 'YES') RETURN PLS_INTEGER;DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (   attribute_name   IN VARCHAR2,   attribute_value  IN VARCHAR2,   fixed            IN VARCHAR2 := 'NO',   enabled          IN VARCHAR2 := 'YES')  RETURN PLS_INTEGER;

Parameters

Table 136-10 LOAD_PLANS_FROM_CURSOR_CACHE Function Parameters

ParameterDescription

sql_id

SQL statement identifier. Identifies a SQL statement in the cursor cache. Note: In the third overload the text of identified SQL statement is extracted from cursor cache and is used to identify the SQL plan baseline into which the plan(s) are loaded. If the SQL plan baseline doesn't exist it is created.

plan_hash_value

Plan identifier. Default NULL means capture all plans present in the cursor cache for the SQL statement identified by SQL_ID.

sql_text

SQL text to use in identifying the SQL plan baseline into which the plans are loaded. If the SQL plan baseline does not exist, it is created. The use of text is crucial when the user tunes a SQL statement by adding hints to its text and then wants to load the resulting plan(s) into the SQL plan baseline of the original SQL statement.

sql_handle

SQL handle to use in identifying the SQL plan baseline into which the plans are loaded. The sql_handle must denote an existing SQL plan baseline. The use of handle is crucial when the user tunes a SQL statement by adding hints to its text and then wants to load the resulting plan(s) into the SQL plan baseline of the original SQL statement.

fixed

Default 'NO' means the loaded plans are used as non-fixed plans. Value 'YES' means the loaded plans are used as fixed plans and the SQL plan baseline will not be evolved over time.

attribute_name

One of possible attribute names:

• SQL_TEXT''

• 'PARSING_SCHEMA_NAME'

• 'MODULE'

• 'ACTION'

attribute_value

Attribute value is used as a search pattern of LIKE predicate if attribute name is'SQL_TEXT'. Otherwise, it is used as an equality search value. (for example, for specifying attribute_name => 'SQL_TEXT', and attribute_value => '% HR-123 %' means applying SQL_TEXT LIKE '% HR-123 %' as a selection filter. Similarly, specifyingattribute_name => 'MODULE', and attribute_value => 'HR' means applying 'MODULE = 'HR' as a plan selection filter). The attribute value is upper-cased except when it is enclosed in double quotes or attribute name is 'SQL_TEXT'.

enabled

Default 'YES' means the loaded plans are enabled for use by the optimizer

Return Values

Number of plans loaded

Usage Notes

Invoking this subprogram requires the ADMINISTER SQL MANAGEMENT OBJECT privilege.