OCP-1Z0-053-V13.02-225题

225.View the Exhibit to examine the Automatic Database Diagnostic Monitor (ADDM) tasks. You executed

the following commands:

SQL> VAR tname VARCHAR2(60);

SQL> BEGIN :tname := 'my_instance_analysis_mode_task';

DBMS_ADDM.INSERT_SEGMENT_DIRECTIVE(:tname,'Sg_directive','SCOTT');

END;

Which statement describes the consequence?

A. The ADDM task is filtered to suppress the Segment Advisor suggestions for the SCOTT schema.

B. The ADDM task is filtered to produce the Segment Advisor suggestions for the SCOTT schema only.

C. The PL/SQL block produces an error because the my_instance_analysis_mode_task task has not been reset to its initial state.

D. All subsequent ADDM tasks including my_instance_analysis_mode_task are filtered to suppress the Segment Advisor suggestions for the SCOTT schema.

Answer: C

答案解析：

参考：http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_addm.htm#ARPLS65067

实验验证：

sys@TEST1107> VAR tname VARCHAR2(60);

sys@TEST1107> BEGIN :tname := 'my_instance_analysis_mode_task';

2 DBMS_ADDM.INSERT_SEGMENT_DIRECTIVE(:tname,'Sg_directive','SCOTT');

3 END;

4 /

PL/SQL procedure successfully completed.

sys@TEST1107> SELECT DBMS_ADVISOR.GET_TASK_REPORT(:tname, 'TEXT', 'ALL') FROM DUAL;

ERROR:

ORA-13631: The most recent execution of task my_instance_analysis_mode_task contains no results.

ORA-06512: at "SYS.PRVT_ADVISOR", line 3189

ORA-06512: at "SYS.DBMS_ADVISOR", line 590

ORA-06512: at line 1

no rows selected

DBMS_ADDM.ANALYZE_INST缺少分析，会报错。

INSERT_SEGMENT_DIRECTIVE Procedure

Creates a directive to prevent ADDM from creating actions to "run Segment Advisor" for specific segments

INSERT_SEGMENT_DIRECTIVE Procedure

This procedure creates a directive to prevent ADDM from creating actions to "run Segment Advisor" for specific segments. The directive can be created for a specific task (only when the task is in INITIAL status), or for all subsequently created ADDM tasks (such as a system directive).

Syntax

DBMS_ADDM.INSERT_SEGMENT_DIRECTIVE (

task_name IN VARCHAR2,

dir_name IN VARCHAR2,

owner_name IN VARCHAR2,

object_name IN VARCHAR2 := NULL,

sub_object_name IN VARCHAR2 := NULL);

DBMS_ADDM.INSERT_SEGMENT_DIRECTIVE (

task_name IN VARCHAR2,

dir_name IN VARCHAR2,

object_number IN NUMBER);

Parameters

Table 16-14 INSERT_SEGMENT_DIRECTIVE Procedure Parameters

Parameter	Description
`task_name`	Name of the task this directive applies to. If the value is `NULL`, it applies to all subsequently created ADDM Tasks.
`dir_name`	Name of the directive. All directives must be given unique names.
`owner_name`	Specifies the owner of the segment/s to be filtered. A wildcard is allowed in the same syntax used for "like" constraints.
`object_name`	Name of the main object to be filtered. Again, wildcards are allowed. The default value of `NULL` is equivalent to a value of '%'.
`sub_object_name`	Name of the part of the main object to be filtered. This could be a partition name, or even sub partitions (separated by a '.'). Again, wildcards are allowed. The default value of `NULL` is equivalent to a value of '%'.
`object_number`	Object number of the `SEGMENT` that this directive is to filter, found in views `DBA_OBJECTS` or `DBA_SEGMENTS`

Examples

A new ADDM task is created to analyze a local instance. However, it has special treatment for all segments that belong to user SCOTT. The result of GET_REPORT does not show actions for running Segment advisor for segments that belong to SCOTT.

var tname VARCHAR2(60);

BEGIN

DBMS_ADDM.INSERT_SEGMENT_DIRECTIVE(NULL,

'my Segment directive',

'SCOTT');

:tname := 'my_instance_analysis_mode_task';

DBMS_ADDM.ANALYZE_INST(:tname, 1, 2);

END;

To see a report containing all actions regardless of the directive:

SELECT DBMS_ADVISOR.GET_TASK_REPORT(:tname, 'TEXT', 'ALL') FROM DUAL;