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#ARPLS68158
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
| Parameter |
Description |
|---|
|
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_handlemust 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',
andattribute_value => '% HR-123 %' means applying SQL_TEXT LIKE '% HR-123 %' as a selection filter. Similarly, specifying attribute_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, It may be used depending on accepted status.
|
Return Values
Number of plans loaded
Usage Notes
Invoking this subprogram requires the ADMINISTER SQL MANAGEMENT OBJECT privilege.