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
NameDescriptionPossible ValuesDefault 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
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
ParameterDescription 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
NameDescriptionPossible ValuesDefault 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 aplan 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.
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 than10% 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 conditionsis 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题
版权声明:本文为博主原创文章,未经博主允许不得转载。