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

103.Observe the following PL/SQL block:

BEGIN

dbms_spm.configure('SPACE_BUDGET_PERCENT', 30);

END;

Which statement is correct regarding the above PL/SQL block?

A. It automatically purges the SQL management objects when SMB occupies more than 30% of the

SYSAUX tablespace.

B. It reserves 30% of the space in the SYSAUX tablespace for SQL Management Base (SMB).

C. It reserves 30% of the space in the SYSTEM tablespace for SMB.

D. It generates a weekly warning in the alert log file when SMB occupies more than 30% of the SYSAUX

tablespace.

Answer: D

答案解析：

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

参考：http://docs.oracle.com/cd/E11882_01/server.112/e41573/optplanmgmt.htm#PFGRF95123

When the space occupied by SQL management base exceeds the defined space budget limit, a weekly database alert is generated.

Table 136-6 Names & Values for CONFIGURE Procedure Parameters

Name	Description	Possible Values	Default Value
space_budget_percent	Maximum percent of SYSAUX space that can be used for SQL management base	1,2, …, 50	10
plan_retention_weeks	Number of weeks to retain unused plans before they are purged	5,6, …, 523	53

sys@TEST0910> begin

  2  dbms_spm.configure('SPACE_BUDGET_PERCENT',30);

  3  END;

  4  /

 

PL/SQL procedure successfully completed.

    DBMS_SPM包允许用户使用SQL计划管理功能来管理SQL执行计划，SQL计划管理功能可以通过长时间的记录和分析SQL语句执行计划来有效的防止由于突然间更改一个SQL语句执行计划所导致数据库性能的衰退，而且还可以通过已知的一组高效的执行计划生成一些执行计划基线。这些SQL计划基线能够随后用于保证适当的性能，即使是在系统发生改变的时候，通常在如下的情况  使用SQL计划功能来管理SQL执行计划：
    数据库升级安装改变优化器时，通常会有少部分的执行计划改变，大部分可能是有变化的或者是有提高的。无论怎样，还是存在一部分的计划改变所导致性能的衰退问题。此时利用SQL计划基线能显著地减少由于数据库更新带来的性能问题。
    正在运行的系统和不断变化的数据会带来一些性能问题。利用SQL计划基线可以减少性能回退同时可以维持系统稳定。
    有时部署新的系统模块相当于引用新的SQL语句到系统中，应用程序需要有适当的SQL执行计划，而这些新的执行计划需要通过一些标准的测试获得，使用SQL计划基线能在随时间的变化产生更好的性能
    DBMS_SPM包属于SYS用户，其他用户需要被分配到ADMINISTER SQL MANAGEMENT OBJECT权限才可以执行这个包
    ● CONFIGURE 程序
    这个程序用于设置SQL管理程序的配置选项，使用parameter/value 的格式。这个函数可能被调用多次，每次可以设置不同的值。
     

CONFIGURE Procedure

This procedure sets configuration options for SQL management base, in parameter/value format. This function can be called numerous times, each time setting a different configuration option.

Syntax

DBMS_SPM.CONFIGURE (

   parameter_name    IN VARCHAR2,

   parameter_value   IN NUMBER);

Parameters

Table 136-5 CONFIGURE Procedure Parameters

Parameter	Description
parameter_name	Name of parameter to set (see table below)
parameter_value	Value of parameter to use (see table below)

Table 136-6 Names & Values for CONFIGURE Procedure Parameters

Name	Description	Possible Values	Default Value
space_budget_percent	Maximum percent of SYSAUX space that can be used for SQL management base	1,2, …, 50	10
plan_retention_weeks	Number of weeks to retain unused plans before they are purged	5,6, …, 523	53

Usage Notes

• The default space budget for SQL management base is no more than ten percent of the size of SYSAUX tablespace. The space budget can be set to a maximum of 50%. The default unused plan retention period is one year and one week, which means a plan will be automatically purged if it has not been used for more than a year. The retention period can be set to a maximum of 523 weeks (i.e. a little over 10 years).

• When the space occupied by SQL management base exceeds the defined space budget limit, a weekly database alert is generated.

这个参数是设置最大的在SYSAUX空间上的百分比，并且如果超过空间阈值则每周会告警,所以选择D

参考：http://docs.oracle.com/cd/E11882_01/server.112/e41573/optplanmgmt.htm#PFGRF95123

15.6.1 Disk Space Usage

Disk space used by the SMB is regularly checked against a limit based on the size of the SYSAUX tablespace. By default, the limit for the SMB is no more than 10% of the size of SYSAUX. The allowable range for this limit is between 1% and 50%.

A weekly background process measures the total space occupied by the SMB. When the defined limit is exceeded, the process writes a warning to the alert log. The database generates alerts weekly until one of the following conditions is met:

• The SMB space limit is increased

• The size of the SYSAUX tablespace is increased

• The disk space used by the SMB is decreased by purging SQL management objects (SQL plan baselines or SQL profiles)

To change the percentage limit, use the CONFIGURE procedure of the DBMS_SPM package. The following example changes the space limit to 30%:

BEGIN

  DBMS_SPM.CONFIGURE('space_budget_percent',30);

END;

/

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