Oracle OCP 1Z0-050（111-120题）解析-白红宇

Oracle OCP 1Z0-050（111-120题）解析

阅读量：4041 次

发布时间：2019-05-24

本文共 11922 字，大约阅读时间需要 39 分钟。

Oracle OCP 1Z0-050（111-120题）解析

QUESTION 111:

Sales details are being stored on a daily basis in the SALES_2007 table. A large amount of data is added to the table daily. To save disk space, you issued the following command:

ALTER TABLE sales_2007 COMPRESS FOR ALL OPERATIONS;

What would be the outcome of this command?

A. It immediately compresses all existing data as well as new data, resulting from either fresh additions or modifications to existing data.

B. It produces an error because data already exists in the table.

C. It compresses all data added or modified henceforth but the existing data in the table is not compressed immediately.

D. It produces an error because compression can be enabled at table creation only.

Answer: C

解析：

关于表压缩（Table Comprression）的内容，参考Oracle官方文档：

Table Compression 表压缩：

当数据库大小增长到几百G或T时,可以考虑压缩表。压缩表节省空间，减少buffer cache缓存池的内存使用。表压缩还能提高读数据时的查询速度。但牺牲的是DML，数据装载时CPU的使用率，在OLAP系统上特点尤为突出，OLTP系统也可以使用。

可以在创建表时指定表压缩，也可以把已经建好的表改为表压缩，但只有更改后的数据是压缩的，同样也可以把一个压缩表更改为一个非压缩表，只有在更改后的数据才是非压缩的。

压缩表有两种选项：

COMPRESS FOR DIRECT_LOAD OPERATIONS：这是默认的压缩选项,即只对直接插入的数据压缩。

COMPRESS FOR ALL OPERATIONS：对所以的DML操作的数据都压缩，但COMPATIBLE参数必须设为11.1.0或更高。

示例：（说明一下示例是在Oracle 12c 上进行的，与Oracle 11g中给出的信息略有不同）

SQL> CREATE TABLE t1 (a number, b varchar2(10)) COMPRESS FOR ALL OPERATIONS;表已创建。SQL> CREATE TABLE t2 (a number, b varchar2(10)) COMPRESS FOR DIRECT_LOAD OPERATIONS;表已创建。SQL> CREATE TABLE t3 (a number, b varchar2(10)) COMPRESS;表已创建。SQL> CREATE TABLE t4 (a number, b varchar2(10));表已创建。SQL> col table_name format a10;SQL> SELECT table_name, compression, compress_for FROM user_tables;TABLE_NAME COMPRESSION      COMPRESS_FOR---------- ---------------- ------------------------------------------------------------EMP        DISABLEDDEPT       DISABLEDT1         ENABLED          ADVANCEDT2         ENABLED          BASICT3         ENABLED          BASICT4         DISABLED已选择 6 行。SQL> ALTER TABLE t4 COMPRESS FOR ALL OPERATIONS;表已更改。SQL> SELECT table_name, compression, compress_for FROM user_tables;TABLE_NAME COMPRESSION      COMPRESS_FOR---------- ---------------- ------------------------------------------------------------EMP        DISABLEDDEPT       DISABLEDT1         ENABLED          ADVANCEDT2         ENABLED          BASICT3         ENABLED          BASICT4         ENABLED          ADVANCED已选择 6 行。SQL> ALTER TABLE t4 COMPRESS FOR DIRECT_LOAD OPERATIONS;表已更改。SQL> SELECT table_name, compression, compress_for FROM user_tables;TABLE_NAME COMPRESSION      COMPRESS_FOR---------- ---------------- ------------------------------------------------------------EMP        DISABLEDDEPT       DISABLEDT1         ENABLED          ADVANCEDT2         ENABLED          BASICT3         ENABLED          BASICT4         ENABLED          BASIC已选择 6 行。

创建分区表后，进行Table Compress修改：

SQL> CREATE TABLE sales(  2  saleskey number,  3  quarter number,  4  product number,  5  salesperson number,  6  amount number(12,2),  7  region varchar2(10)) COMPRESS  8  PARTITION BY LIST(region)  9  (    PARTITION northwest VALUES ('NORTHWEST'), 10       PARTITION southwest VALUES ('SOUTHWEST'), 11       PARTITION northeast VALUES ('NORTHEAST') NOCOMPRESS, 12       PARTITION southeast VALUES ('SOUTHEAST'));表已创建。SQL> SELECT table_name, compression, compress_for FROM user_tables;TABLE_NAME COMPRESSION      COMPRESS_FOR---------- ---------------- ------------------------------------------------------------EMP        DISABLEDDEPT       DISABLEDT1         ENABLED          ADVANCEDT2         ENABLED          BASICT3         ENABLED          BASICT4         ENABLED          BASICSALES已选择 7 行。SQL> select TABLE_NAME,PARTITION_NAME,PARTITION_POSITION,COMPRESSION,COMPRESS_FOR FROM user_tab_partitions order by 3;TABLE_NAME PARTITION_NAME       PARTITION_POSITION COMPRESSION      COMPRESS_FOR---------- -------------------- ------------------ ---------------- ------------------------------------------------------------SALES      NORTHWEST                             1 ENABLED          BASICSALES      SOUTHWEST                             2 ENABLED          BASICSALES      NORTHEAST                             3 DISABLEDSALES      SOUTHEAST                             4 ENABLED          BASICSQL>

QUESTION 112:

When executing a SQL workload, you choose to generate execution plans only, without collecting execution statistics. Which two statements describe the implications of this?

(Choose two.)

A. Only the changes in the execution plan, and not performance regression, are detected.

B. It shortens the time of execution and reduces the impact on system resources.

C. It produces less accurate results of the comparison analysis.

D. It automatically calls the SQL Tuning Advisor for recommendations.

Answer: B,C

解析：参考Oracle官方文档：

Depending on its size, executing a SQL workload can be time and resource intensive. With the explain plan method, you can choose to generate execution plans only, without collecting execution statistics. This technique shortens the time to run the trial and lessens the effect on system resources, but a comprehensive performance analysis is not possible because only the execution plans will be available during the analysis. However, unlike generating a plan with the EXPLAIN PLAN command, SQL Performance Analyzer provides bind values to the optimizer when generating execution plans, which provides a more reliable prediction of what the plan will be when the SQL statement is executed.

QUESTION 113:

Which dependent object will get invalidated even if it is not affected by the table redefinition?

A. packages

B. triggers

C. synonyms

D. views

Answer: B

QUESTION 114:

A PL/SQL procedure queries only those columns of a redefined table that were unchanged by the online table redefinition. What happens to the PL/SQL procedure after the online table redefinition?

A. It remains valid.

B. It becomes invalid for all options of online table redefinition but automatically gets revalidated the next time it is used.

C. It becomes invalid only if the storage parameters have been modified and it automatically gets revalidated the next time it is used.

D. It becomes invalid for all options of online table redefinition and is automatically recompiled during online redefinition of the table.

Answer: A

解析：参考Oracle官方文档：

文档中描述了这个题目中所描述的场景，参考下面内容：For example, if a PL/SQL procedure queries only columns of the redefined table that were unchanged by the redefinition, the procedure remains valid.

Results of the Redefinition Process

The following are the end results of the redefinition process:

The original table is redefined with the columns, indexes, constraints, grants, triggers, and statistics of the interim table.

Dependent objects that were registered, either explicitly using REGISTER_DEPENDENT_OBJECT or implicitly using COPY_TABLE_DEPENDENTS, are renamed automatically so that dependent object names on the redefined table are the same as before redefinition.

If no registration is done or no automatic copying is done, then you must manually rename the dependent objects.

The referential constraints involving the interim table now involve the redefined table and are enabled.

Any indexes, triggers, materialized view logs, grants, and constraints defined on the original table (prior to redefinition) are transferred to the interim table and are dropped when the user drops the interim table. Any referential constraints involving the original table before the redefinition now involve the interim table and are disabled.

Some PL/SQL objects, views, synonyms, and other table-dependent objects may become invalidated. Only those objects that depend on elements of the table that were changed are invalidated. For example, if a PL/SQL procedure queries only columns of the redefined table that were unchanged by the redefinition, the procedure remains valid. See "Managing Object Dependencies" for more information about schema object dependencies.

QUESTION 115:

The ADMIN_EMP table has columns EMPNO, ENAME, DEPTNO, and SAL. It has a materialized view EMP_MV with a materialized log and an ENAME_IDX index on the ENAME column.

You need to perform an online table redefinition on the ADMIN_EMP table to move it from the TBS1 tablespace to the TBS2 tablespace in the same schema.

What action is required for the dependent objects when you perform online redefinition on the table?

A. The materialized view should have a complete refresh performed after the online table redefinition is completed.

B. The materialized view should have a fast refresh performed after the online table redefinition is completed.

C. The materialized view, materialized log, and the index should be dropped and re-created after the online table redefinition is complete.

D. The materialized view and materialized log should be dropped and all constraints disabled and re-created after the online table redefinition is complete.

Answer: A

参考Oracle官方文档

QUESTION 116:

Which setting enables the baselines by default in Oracle Database 11g?

A. enabling Automated Maintenance Task using Oracle Enterprise Manager

B. setting the STATISTICS_LEVEL parameter to TYPICAL

C. adding filters to the Automatic Database Diagnostic Monitor (ADDM)

D. setting the OPTIMIZER_USE_PENDING_STATISTICS parameter to TRUE

Answer: B

解析：参考Oracle官方文档：

当STATISTICS_LEVEL参数设置为TYPICAL时，确保收集数据库自我管理功能所需的所有主要统计数据，并提供最佳总体性能。默认值应该适合于大多数环境。当STATISTICS_LEVEL参数设置为ALL时，额外的统计信息被添加到通过TYPICAL设置收集的统计信息集中。额外的统计数据是OS统计和计划执行统计的时间。

QUESTION 117:

Identify the activities performed as part of the Automatic SQL Tuning process in the maintenance window? (Choose all that apply.)

A. tuning each SQL statement in the order of importance

B. generating baselines that include candidate SQLs for tuning

C. generating the SQL profile

D. adding tuned SQL plans into the SQL plan baseline

E. testing and accepting the SQL profile

F. generating a list of candidate SQLs for tuning

Answer: A,C,E,F

参考Oracle官方文档：

QUESTION 118:

You installed Oracle Database 11g and are performing a manual upgrade of the Oracle9i database. As a part of the upgrade process, you execute the following script:

SQL>@utlu111i.sql

Which statement about the execution of this script is true?

A. It must be executed from the Oracle Database 11g environment.

B. It must be executed only after the SYSAUX tablespace has been created.

C. It must be executed from both the Oracle Database 11g and Oracle Database 9i environments.

D. It must be executed from the environment of the database that is being upgraded.

E. It must be executed only after AUTOEXTEND is set to ON for all existing tablespaces.

Answer: D

参考Oracle官方文档：

QUESTION 119:

Exhibit #1:

Exhibit #2:

View the Exhibit exhibit1.

In the CUSTOMERS_OBE table, when the value of CUST_STATE_PROVINCE is "CA", the value of COUNTRY_ID is "US."

View the Exhibit exhibit2 to examine the commands and query plans.

The optimizer can sense 8 rows instead of 29 rows, which is the actual number of rows in the table. What can you do to make the optimizer detect the actual selectivity?

A. Change the STALE_PERCENT value for the CUSTOMERS_OBE table.

B. Set the STATISTICS_LEVEL parameter to TYPICAL.

C. Create extended statistics for the CUST_STATE_PROVINCE and CUSTOMERS_OBE columns.

D. Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to FALSE.

Answer: C

QUESTION 120:

Exhibit: