Oracle 1Z0-117 Online Practice Questions and Exam Preparation

Oracle 1Z0-117 Online Questions & Answers

• Question 121:

  Which two statements about In-Memory Parallel Execution are true?

  A. It can be configured using the Database Resource Manager.
  B. It increases the number of duplicate block images in the global buffer cache.
  C. It requires setting PARALLEL_DEGREE_POLICY to LIMITED.
  D. Objects selected for In-Memory Parallel Execution have blocks mapped to specific RAC instances.
  E. It requires setting PARALLEL_DEGREE_POLICY to AUTO
  F. Objects selected for In-Memory Parallel Execution must be partitioned tables or indexes.
  D. Objects selected for In-Memory Parallel Execution have blocks mapped to specific RAC instances.
  E. It requires setting PARALLEL_DEGREE_POLICY to AUTO

  ---

  D, E: In-Memory Parallel Execution

  When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database decides if an object that is accessed using parallel execution would benefit from being cached in the SGA (also called the buffer cache). The decision to cache an object is based on a well-defined set of heuristics including the size of the object and frequency on which it is accessed. In an Oracle RAC environment, Oracle Database maps pieces of the object into each of the buffer caches on the active instances. By creating this mapping, Oracle Database automatically knows which buffer cache to access to find different parts or pieces of the object. Using this information, Oracle Database prevents multiple instances from reading the same information from disk over and over again, thus maximizing the amount of memory that can cache objects. If the size of the object is larger than the size of the buffer cache (single instance) or the size of the buffer cache multiplied by the number of active instances in an Oracle RAC cluster, then the object is read using direct-path reads.

  E: PARALLEL_DEGREE_POLICY specifies whether or not automatic degree of Parallelism, statement queuing, and in-memory parallel execution will be enabled.

  AUTO

  Enables automatic degree of parallelism, statement queuing, and in-memory parallel execution.

  Incorrect:

  C:

  LIMITED

  Enables automatic degree of parallelism for some statements but statement queuing and in- memory Parallel Execution are disabled. Automatic degree of parallelism is only applied to those statements that access tables or indexes decorated

  explicitly with the PARALLEL clause. Tables and indexes that have a degree of parallelism specified will use that degree of parallelism.

  Reference: Oracle Database VLDB and Partitioning Guide 11g, How Parallel Execution Works

• Question 122:

  In your database, the CURSOR_SHARING parameter is set to FORCE.

  A user issues the following SQL statement:

  Select * from SH.CUSTOMERS where REIGN='NORTH'

  Which two statements are correct?

  A. The literal value `NORTH' is replaced by a system-generated bind variable.
  B. Bind peeking will not happen and subsequent executions of the statement with different literal values will use the same plan.
  C. Adaptive cursor sharing happens only if there is a histogram in the REIGN column of the CUSTOMERS table.
  D. Adaptive cursor sharing happens irrespective of whether there is a histogram in the REIGN column of the CUSTOMERS table.
  A. The literal value `NORTH' is replaced by a system-generated bind variable.
  B. Bind peeking will not happen and subsequent executions of the statement with different literal values will use the same plan.

  ---

  CURSOR_SHARING determines what kind of SQL statements can share the same cursors. Values:

  *

  FORCE

  Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

  *

  SIMILAR

  Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

  *

  EXACT

  Only allows statements with identical text to share the same cursor.

• Question 123:

  What are three common reasons for SQL statements to perform poorly?

  A. Full table scans for queries with highly selective filters
  B. Stale or missing optimizer statistics
  C. Histograms not existing on columns with evenly distributed data
  D. High index clustering factor
  E. OPTIMIZER_MODE parameter set to ALL_ROWS for DSS workload
  A. Full table scans for queries with highly selective filters
  B. Stale or missing optimizer statistics
  D. High index clustering factor

  ---

  D: The clustering_factor measures how synchronized an index is with the data in a table. A table with a high clustering factor is out-of-sequence with the rows and large index range scans will consume lots of I/O. Conversely, an index with a low clustering_factor is closely aligned with the table and related rows reside together of each data block, making indexes very desirable for optimal access.

  Note:

  *

  (Not C) Histograms are feature in CBO and it helps to optimizer to determine how data are skewed(distributed) with in the column. Histogram is good to create for the column which are included in the WHERE clause where the column is highly skewed. Histogram helps to optimizer to decide whether to use an index or full-table scan or help the optimizer determine the fastest table join order.

  *

  OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance.

  all_rows

  The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).

• Question 124:

  You plan to bulk load data using INSERT /*+PARALLEL*/ INTO . . . . SELECT FROM statements.

  Which four types of operations can execute in parallel on tables that have no bitmapped indexes or materialized views defined on term?

  A. Direct path insert of a million rows into a partitioned, index-organized table containing one million rows.
  B. Direct path insert of a million rows into a partitioned, index-organized table containing 10 million rows.
  C. Direct path insert of a million rows into a nonpartitioned, index-organized table containing one million rows.
  D. Direct path insert of a million rows into a nonpartitioned, heap-organized table containing 10 million rows.
  E. Direct path insert of a million rows into a nonpartitioned, heap-organized table containing one million rows.
  A. Direct path insert of a million rows into a partitioned, index-organized table containing one million rows.
  B. Direct path insert of a million rows into a partitioned, index-organized table containing 10 million rows.
  D. Direct path insert of a million rows into a nonpartitioned, heap-organized table containing 10 million rows.
  E. Direct path insert of a million rows into a nonpartitioned, heap-organized table containing one million rows.

  ---

  Direct-path INSERT is not supported for an index-organized table (IOT) if it is not partitioned, if it has a mapping table, or if it is reference by a materialized view.

• Question 125:

  Partial details of an execution plan.

  

  Which statement correctly describes the BITMAP AND operation?

  A. It produces a bitmap, representing dimension table rows from all dimension tables that join with qualified fact table rows.
  B. It produces a concentration of the bitmaps for all dimension tables.
  C. It produces a bitmap, representing fact table rows that do not join with qualified dimension table rows from all dimension tables.
  D. It produces a bitmap, representing fact table rows that join with qualified dimension table rows from all dimension tables.
  D. It produces a bitmap, representing fact table rows that join with qualified dimension table rows from all dimension tables.

  ---

  Example:

  Additional set operations will be done for the customer dimension and the product dimension. At this point in the star query processing, there are three bitmaps. Each bitmap corresponds to a separate dimension table, and each bitmap

  represents the set of rows of the fact table that satisfy that individual dimension's constraints.

  These three bitmaps are combined into a single bitmap using the bitmap AND operation. This final bitmap represents the set of rows in the fact table that satisfy all of the constraints on the dimension table.

  Reference: Oracle Database Data Warehousing Guide, Star Transformation with a Bitmap Index