Oracle OCP 1Z0-050（101-110题）解析

Oracle OCP 1Z0-050（101-110题）解析

QUESTION 101:

View the Exhibit for some of the parameter settings.

You start a session and issue the following command:

SQL>CREATE INDEX emp_ename ON emp(ename)

TABLESPACE users INVISIBLE;

What is the outcome of the above command?

A. The index is used by the optimizer only if a hint is specified in the query statement and is maintained during DML operations.

B. The index is not used by the optimizer but is maintained during DML operations.

C. The index is not used by the optimizer and is not maintained during DML operations.

D. The index is used by the optimizer only if a hint is specified in the query statement but is not maintained during DML operations.

Answer: B

解析：

参考Oracle官方文档：

https://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams173.htm#REFRN10301

Optimizer_use_invisible_indexes

OPTIMIZER_USE_INVISIBLE_INDEXES enables or disables the use of invisible indexes.

Values:

• true：Invisible indexes are treated as visible (normal) indexes.
• false：Invisible indexes will not be considered by the optimizer but will still be maintained by DML operations.

 

QUESTION 102:

You executed the following commands:

SQL> ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS = false;

SQL> EXECUTE DBMS_STATS.SET_TABLE_PREFS('SH', 'CUSTOMERS',

'PUBLISH','false');

SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SH', 'CUSTOMERS');

Which statement is correct regarding the above statistics collection on the SH.CUSTOMERS table in the above session?

A. The statistics are stored in the pending statistics table in the data dictionary.

B. The statistics are treated as the current statistics by the optimizer for the current sessions only.

C. The statistics are treated as the current statistics by the optimizer for all sessions.

D. The statistics are temporary and used by the optimizer for all sessions until this session terminates.

Answer: A

解析：

参考Oracle官方文档：

https://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams174.htm#REFRN10288

https://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF94751

OPTIMIZER_USE_PENDING_STATISTICS specifies whether or not the optimizer uses pending statistics when compiling SQL statements.

OPTIMIZER_USE_PENDING_STATISTICS 指定在SQL语句编译时是否使用未决定（pending）的统计信息。

Starting with Oracle Database 11g Release 2 (11.2), you have the following options when gathering statistics:

• Publish the statistics automatically at the end of the gather operation (default behavior)
• Save the new statistics saved as pending

Saving the new statistics as pending allows you to validate the new statistics and publish them only if they are satisfactory.

To check whether the statistics will be automatically published as soon as they are gathered, use the DBMS_STATS package as follows:

SELECT DBMS_STATS.GET_PREFS('PUBLISH') PUBLISH FROM DUAL;

The preceding query returns either TRUE or FALSE. TRUE indicates that the statistics will be published as and when they are gathered, while FALSE indicates that the statistics will be kept pending.

Note:

The database stores published statistics in data dictionary views such as USER_TAB_STATISTICS and USER_IND_STATISTICS. The database stores pending statistics in views such as USER_TAB_PENDING_STATS and USER_IND_PENDING_STATS.

You can change the PUBLISH setting at either the schema or the table level. For example, to change the PUBLISH setting for the customers table in the SH schema, execute the statement:

EXEC DBMS_STATS.SET_TABLE_PREFS('SH', 'CUSTOMERS', 'PUBLISH', 'false');

Subsequently, when you gather statistics on the customers table, the statistics will not be automatically published when the gather job completes. Instead, the database stores the newly gathered statistics in the USER_TAB_PENDING_STATS table.

By default, the optimizer uses the published statistics stored in the data dictionary views. If you want the optimizer to use the newly collected pending statistics, then set the initialization parameter OPTIMIZER_USE_PENDING_STATISTICS to TRUE (the default value is FALSE), and run a workload against the table or schema:

ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS = TRUE;

The optimizer will use the pending statistics instead of the published statistics when compiling SQL statements. If the pending statistics are valid, then you can make them public by executing the following statement:

EXEC DBMS_STATS.PUBLISH_PENDING_STATS(null, null);

You can also publish the pending statistics for a specific database object. For example, by using the following statement:

EXEC DBMS_STATS.PUBLISH_PENDING_STATS('SH','CUSTOMERS');

If you do not want to publish the pending statistics, delete them by executing the following statement:

EXEC DBMS_STATS.DELETE_PENDING_STATS('SH','CUSTOMERS');

You can export pending statistics using DBMS_STATS.EXPORT_PENDING_STATS function. Exporting pending statistics to a test system enables you to run a full workload against the new statistics.

 

QUESTION 103:

You plan to set up the Automatic Workload Repository (AWR) baseline metric thresholds for a moving window baseline. Which action would you take before

A. Take an immediate AWR snapshot.

B. Decrease the window size for the baseline.

C. Decrease the expiration time for the baseline.

D. Compute the baseline statistics.

Answer: D

解析：

参考Oracle官方文档：

https://docs.oracle.com/cd/E11882_01/server.112/e41573/autostat.htm#PFGRF94181

A moving window baselin