Oracle 1Z0-117 Online Practice Questions and Exam Preparation

Oracle 1Z0-117 Online Questions & Answers

• Question 11:

  Exhibit

  

  A table has three distinct values in its ID column. In the ID column, values 10 and 20 have more than 20000 rows each and value 30 has only five rows. The statistics for the schema have been updated recently.

  The CURSOR_SHARING parameter is set to EXACT.

  The query was executed recently and the cursor for the query is bind aware. Examine the exhibits to view the commands executed.

  You plan to execute the same query with a value of 30 for the bind variable V_ID.

  Which statement is true in this scenario?

  A. The same execution plan will always be used irrespective of the value in the bind variable.
  B. A new execution plan will be generated depending on the access pattern and the bind value.
  C. Adaptive cursor sharing will ensure that a new cursor is generated for each distinct value in the bind variable.
  D. Adaptive cursor sharing will happen only if you use the literal values instead of bind variables in the query.
  C. Adaptive cursor sharing will ensure that a new cursor is generated for each distinct value in the bind variable.

  ---

  Note:

  *

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

  *

  Setting CURSOR_SHARING to EXACT allows SQL statements to share the SQL area only when their texts match exactly. This is the default behavior. Using this setting, similar statements cannot shared; only textually exact statements can

  be shared.

  *

  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 12:

  You instance has these parameter settings:

  

  Which three statements are true about these settings if no hints are used in a SQL statement?

  A. A statement estimated for more than 10 seconds always has its degree of parallelism computed automatically.
  B. A statement with a computed degree of parallelism greater than 8 will be queued for a maximum of 10 seconds.
  C. A statement that executes for more than 10 seconds always has its degree of parallelism computed automatically.
  D. A statement with a computed degree of parallelism greater than 8 will raise an error.
  E. A statement with any computed degree of parallelism will be queued if the number of busy parallel execution processes exceeds 64.
  F. A statement with a computed degree of parallelism of 20 will be queued if the number of available parallel execution processes is less 5.
  C. A statement that executes for more than 10 seconds always has its degree of parallelism computed automatically.
  E. A statement with any computed degree of parallelism will be queued if the number of busy parallel execution processes exceeds 64.
  F. A statement with a computed degree of parallelism of 20 will be queued if the number of available parallel execution processes is less 5.

  ---

  C (not A): PARALLEL_MIN_TIME_THRESHOLD specifies the minimum execution time a statement should have before the statement is considered for automatic degree of parallelism. By default, this is set to 30 seconds. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED.

  : PARALLEL_DEGREE_LIMIT integer

  A numeric value for this parameter specifies the maximum degree of parallelism the optimizer can choose for a SQL statement when automatic degree of parallelism is active. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED.

  E: PARALLEL_SERVERS_TARGET specifies the number of parallel server processes allowed to run parallel statements before statement queuing will be used. When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle will queue SQL statements that require parallel execution, if the necessary parallel server processes are not available. Statement queuing will begin once the number of parallel server processes active on the system is equal to or greater than PARALLEL_SERVER_TARGET.

  F: PARALELL_MIN_MINPERCENT PARALLEL_MIN_PERCENT operates in conjunction with PARALLEL_MAX_SERVERS and PARALLEL_MIN_SERVERS. It lets you specify the minimum percentage of parallel execution processes (of the value of

  PARALLEL_MAX_SERVERS) required for parallel execution. Setting this parameter ensures that parallel operations will not execute sequentially unless adequate resources are available. The default value of 0 means that no minimum

  percentage of processes has been set.

  Consider the following settings:

  PARALLEL_MIN_PERCENT = 50 PARALLEL_MIN_SERVERS = 5 PARALLEL_MAX_SERVERS = 10

  If 8 of the 10 parallel execution processes are busy, only 2 processes are available. If you then request a query with a degree of parallelism of 8, the minimum 50% will not be met.

  Note: With automatic degree of parallelism, Oracle automatically decides whether or not a statement should execute in parallel and what degree of parallelism the statement should use. The optimizer automatically determines the degree of

  parallelism for a statement based on the resource requirements of the statement. However, the optimizer will limit the degree of parallelism used to ensure parallel server processes do not flood the system. This limit is enforced by

  PARALLEL_DEGREE_LIMIT.

  Values:

  CPU

  IO

  integer

  A numeric value for this parameter specifies the maximum degree of parallelism the optimizer can choose for a SQL statement when automatic degree of parallelism is active. Automatic degree of parallelism is only enabled if

  PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED.

  Reference: PARALLEL_MIN_TIME_THRESHOLD PARALLEL_DEGREE_LIMIT PARALELL_MIN_MINPERCENT PARALELL_SERVERS_TARGET

• Question 13:

  You execute the following query:

  

  Which statement is true about the usage of these hints in the query?

  A. The optimizer pushes the join predicate into the inline view.
  B. The optimizer evaluates the subquery as a first and then filters out rows.
  C. The optimizer performs a join operation first and then filters out the rows.
  D. The hint will have no effect because one of the join resultsets is an inline view.
  A. The optimizer pushes the join predicate into the inline view.

  ---

  The PUSH_PRED hint forces pushing of a join predicate into the view.

  For example:

  SELECT /*+ NO_MERGE(v) PUSH_PRED(v) */ *

  FROM employees e,

  (SELECT manager_id

  FROM employees

  ) v

  WHERE e.manager_id = v.manager_id(+)

  AND e.employee_id = 100;

  When the PUSH_PRED hint is used without an argument, it should be placed in the view query block. When PUSH_PRED is used with the view name as an argument, it should be placed in the surrounding query.

  Reference: Oracle Database Performance Tuning Guide, PUSH_PRED

• Question 14:

  Which two types of SQL statements will benefit from dynamic sampling?

  A. SQL statements that are executed parallel
  B. SQL statement that use a complex predicate expression when extended statistics are not available.
  C. SQL statements that are resource-intensive and have the current statistics
  D. SQL statements with highly selective filters on column that has missing index statistics
  E. Short-running SQL statements
  A. SQL statements that are executed parallel
  B. SQL statement that use a complex predicate expression when extended statistics are not available.

  ---

  A: he optimizer decides whether to use dynamic statistics based on several factors. For example, the database uses automatic dynamic statistics when the SQL statement uses parallel execution.

  B: One scenario where DS is used is when the statement contains a complex predicate expression and extended statistics are not available. Extended statistics were introduced in Oracle Database 11g Release 1 with the goal to help the optimizer get good quality cardinality estimates for complex predicate expressions.

  D: DS It is typically used to compensate for missing or insufficient statistics that would otherwise lead to a very bad plan. Reference: When the Optimizer Uses Dynamic Statistics

• Question 15:

  Auto DOP is enabled for your instance. You execute the following statements:

  

  Which three are true about the execution of the join?

  A. Dictionary DOP is used to calculate statements DOP.
  B. Hinted DOP is used to calculate statement DOP.
  C. The EMPLOYEES table is accessed in parallel.
  D. The DEPARTMENTS table is accessed in parallel.
  E. The hint operates at the level of each table accessed by the statement.
  B. Hinted DOP is used to calculate statement DOP.
  C. The EMPLOYEES table is accessed in parallel.
  E. The hint operates at the level of each table accessed by the statement.

  ---

  C: As per ALTER TABLE employees PARALLEL 2;

  Incorrect:

  not D: As per ALTER TABLE departments NOPARALLEL;

• Question 16:

  Examine the following anonymous PL/SQL code block of code: Which two are true concerning the use of this code?

  

  A. The user executing the anonymous PL/SQL code must have the CREATE JOB system privilege.
  B. ALTER SESSION ENABLE PARALLEL DML must be executed in the session prior to executing the anonymous PL/SQL code.
  C. All chunks are committed together once all tasks updating all chunks are finished.
  D. The user executing the anonymous PL/SQL code requires execute privilege on the DBMS_JOB package.
  E. The user executing the anonymous PL/SQL code requires privilege on the DBMS_SCHEDULER package.
  F. Each chunk will be committed independently as soon as the task updating that chunk is finished.
  A. The user executing the anonymous PL/SQL code must have the CREATE JOB system privilege.
  E. The user executing the anonymous PL/SQL code requires privilege on the DBMS_SCHEDULER package.

  ---

  A (not D, not E):

  To use DBMS_PARALLEL_EXECUTE to run tasks in parallel, your schema will need the CREATE JOB system privilege. E (not C): DBMS_PARALLEL_EXECUTE now provides the ability to break up a large table according to a variety of

  criteria, from ROWID ranges to key values and user-defined methods. You can then run a SQL statement or a PL/SQL block against these different "chunks" of the table in parallel, using the database scheduler to manage the processes

  running in the background. Error logging, automatic retries, and commits are integrated into the processing of these chunks.

  Note:

  *

  The DBMS_PARALLEL_EXECUTE package allows a workload associated with a base table to be broken down into smaller chunks which can be run in parallel. This process involves several distinct stages. 1.Create a task 2.Split the workload into chunks CREATE_CHUNKS_BY_ROWID CREATE_CHUNKS_BY_NUMBER_COL CREATE_CHUNKS_BY_SQL 3.Run the task RUN_TASK User-defined framework Task control 4.Check the task status 5.Drop the task

  *

  The workload is associated with a base table, which can be split into subsets or chunks of rows. There are three methods of splitting the workload into chunks.

  CREATE_CHUNKS_BY_ROWID CREATE_CHUNKS_BY_NUMBER_COL CREATE_CHUNKS_BY_SQL The chunks associated with a task can be dropped using the DROP_CHUNKS procedure.

  *

  CREATE_CHUNKS_BY_ROWID The CREATE_CHUNKS_BY_ROWID procedure splits the data by rowid into chunks specified by the CHUNK_SIZE parameter. If the BY_ROW parameter is set to TRUE, the CHUNK_SIZE refers to the number of rows, otherwise it refers to the number of blocks.

  Reference: TECHNOLOGY: PL/SQL Practices, On Working in Parallel

• Question 17:

  Which three are benefits of In-Memory Parallel Execution?

  A. Reduction in the duplication of block images across multiple buffer caches
  B. Reduction in CPU utilization
  C. Reduction in the number of blocks accessed
  D. Reduction in physical I/O for parallel queries
  E. Ability to exploit parallel execution servers on remote instance
  A. Reduction in the duplication of block images across multiple buffer caches
  C. Reduction in the number of blocks accessed
  D. Reduction in physical I/O for parallel queries

  ---

  Note: 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.

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

• Question 18:

  Your database supports a workload consisting of three categories of SQL statements: Statements that should execute in less than one minute

  

  Statement that may execute for up to 15 minutes

  

  Statements that may be executed for more than 15 minutes

  

  You set PARALLEL_DEGREE_POLICY to Auto.

  You plan to prioritize queued statements by using the Database Resource manager.

  Which two are true about parallelism prioritization by a consumer group?

  A. PARALLEL_TARGET_PERCENTAGE is used to prioritize a consumer group's use of the overall PARALLEL_SERVER_TARGET.
  B. Queuing is done for a consumer group exceeding its percentage, even if the number of busy PX servers in the instance has not reached PARALLEL_SERVERS_TARGET.
  C. PARALLEL_TARGET_PERCENTAGE us used to prioritize a consumer group's use of the overall SPAN CLASS = `OracleCode'> PARALLEL_MAX_SERVERS.
  D. Having separate queues for consumer groups requires the use of management attributes (MGMT_P1, MGMT_P2 etc. . . )
  E. Separate queue timeout using PARALLEL_QUEUE_TIMEOUT require the use of management attributes (MGMT_P1, MGMT_P2 etc . . . groups)
  A. PARALLEL_TARGET_PERCENTAGE is used to prioritize a consumer group's use of the overall PARALLEL_SERVER_TARGET.
  D. Having separate queues for consumer groups requires the use of management attributes (MGMT_P1, MGMT_P2 etc. . . )

  ---

  A: Parallel Target Percentage

  It is possible for a single consumer group to launch enough parallel statements to use all the available parallel servers. If this happens, when a high-priority parallel statement from a different consumer group is run, no parallel servers are

  available to allocate to this group. You can avoid such a scenario by limiting the number of parallel servers that can be used by a particular consumer group.

  Use the PARALLEL_TARGET_PERCENTAGE directive attribute to specify the maximum percentage of the parallel server pool that a particular consumer group can use. The number of parallel servers used by a particular consumer group is

  counted as the sum of the parallel servers used by all sessions in that consumer group.

  Incorrect:

  Not B: PARALLEL_SERVERS_TARGET specifies the number of parallel server processes allowed to run parallel statements before statement queuing will be used. When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle

  will queue SQL statements that require parallel execution, if the necessary parallel server processes are not available. Statement queuing will begin once the number of parallel server processes active on the system is equal to or greater than

  PARALLEL_SERVER_TARGET.

  Not C: Would be true if we replace PARALLEL_MAX_SERVERS with

  PARALLEL_SERVER_TARGET.

  Not E: The PARALLEL_QUEUE_TIMEOUT directive attribute enables you to specify the maximum time, in seconds, that a parallel statement can wait in the parallel statement queue before it is timed out. The PARALLEL_QUEUE_TIMEOUT

  attribute can be set for each consumer group. This attribute is applicable even if you do not specify other management attributes (mgmt_p1, mgmt_p2, and so on) in your resource plan.

  Note:

  *

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

  *

  The PARALLEL_TARGET_PERCENTAGE attribute enables you to specify when parallel statements from a consumer group can be queued. Oracle Database maintains a separate parallel statement queue for each consumer group.

  *

  PARALLEL_SERVERS_TARGET specifies the number of parallel server processes allowed to run parallel statements before statement queuing will be used. When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle will queue SQL statements that require parallel execution, if the necessary parallel server processes are not available. Statement queuing will begin once the number of parallel server processes active on the system is equal to or greater than PARALLEL_SERVER_TARGET.

  By default, PARALLEL_SERVER_TARGET is set lower than the maximum number of parallel server processes allowed on the system (PARALLEL_MAX_SERVERS) to ensure each parallel statement will get all of the parallel server resources required and to prevent overloading the system with parallel server processes.

  Note that all serial (non-parallel) statements will execute immediately even if statement queuing has been activated.

  * Oracle Database Resource Manager (the Resource Manager) enables you to optimize resource allocation among the many concurrent database sessions.

  The elements of the Resource Manager are:

  / Resource consumer group A group of sessions that are grouped together based on resource requirements. The Resource Manager allocates resources to resource consumer groups, not to individual sessions.

  / Resource plan A container for directives that specify how resources are allocated to resource consumer groups. You specify how the database allocates resources by activating a specific resource plan.

  / Resource plan directive Associates a resource consumer group with a particular plan and specifies how resources are to be allocated to that resource consumer group.

• Question 19:

  Examine the parallel parameters for your instance.

  

  All parallel execution servers are available and the session use default parallelism settings. In which two cases will the degree of parallelism be automatically calculated?

  A. Statements accessing tables whom dictionary DOP is 2 or more
  B. Statements accessing tables whose dictionary DOP is DEFAULT
  C. Statements that are estimated to execute for more than 10 seconds serially
  D. Statements accessing tables with any setting for Dictionary DOP
  E. Statements with parallel hints
  B. Statements accessing tables whose dictionary DOP is DEFAULT
  E. Statements with parallel hints

  ---

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

  Syntax PARALLEL_DEGREE_POLICY = { MANUAL | LIMITED | AUTO }

  LIMITED is used here:

  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 DEFAULT degree of parallelism using the PARALLEL clause. Statements that do not access any tables or indexes decorated with the

  DEFAULT degree of parallelism will retain the MANUAL behavior.

  Note:

  *

  In earlier versions of the Oracle Database, we had to determine the DOP more or less manually, either with a parallel hint or by setting a parallel degree with alter table. There was an automatic computation of the DOP available for the objects with dictionary DOP of default, derived from the simple formula CPU_COUNT

  *

  PARALLEL_THREADS_PER_CPU.

• Question 20:

  Which three are tasks performed in the hard parse stage of a SQL statement executions?

  A. Semantics of the SQL statement are checked.
  B. The library cache is checked to find whether an existing statement has the same hash value.
  C. The syntax of the SQL statement is checked.
  D. Information about location, size, and data type is defined, which is required to store fetched values in variables.
  E. Locks are acquired on the required objects.
  B. The library cache is checked to find whether an existing statement has the same hash value.
  D. Information about location, size, and data type is defined, which is required to store fetched values in variables.
  E. Locks are acquired on the required objects.

  ---

  Parse operations fall into the following categories, depending on the type of statement submitted and the result of the hash check: A) Hard parse

  If Oracle Database cannot reuse existing code, then it must build a new executable version of the application code. This operation is known as a hard parse, or a library cache miss. The database always perform a hard parse of DDL.

  During the hard parse, the database accesses the library cache and data dictionary cache numerous times to check the data dictionary. When the database accesses these areas, it uses a serialization device called a latch on required

  objects so that their definition does not change (see "Latches"). Latch contention increases statement execution time and decreases concurrency.

  B) Soft parse

  A soft parse is any parse that is not a hard parse. If the submitted statement is the same as a reusable SQL statement in the shared pool, then Oracle Database reuses the existing code. This reuse of code is also called a library cache hit.

  Soft parses can vary in the amount of work they perform. For example, configuring the session cursor cache can sometimes reduce the amount of latching in the soft parses, making them "softer."

  In general, a soft parse is preferable to a hard parse because the database skips the optimization and row source generation steps, proceeding straight to execution.

  Incorrect:

  A, C: During the parse call, the database performs the following checks:

  Syntax Check Semantic Check Shared Pool Check The hard parse is within Shared Pool check. Reference: Oracle Database Concepts 11g, SQL Parsing