Oracle 1Z0-117 Online Practice Questions and Exam Preparation

Oracle 1Z0-117 Online Questions & Answers

• Question 101:

  Which four statements are correct about communication between parallel execution process?

  A. The number of logical pathways between parallel execution producers and consumers depends on the degree parallelism.
  B. The shared pool can be used for parallel execution messages buffers.
  C. The large pool can be used for parallel execution messages buffers.
  D. The buffer cache can be used for parallel execution message buffers.
  E. Communication between parallel execution processes is never required if a query uses full partition-wise joins.
  F. Each parallel execution process has an additional connection to the parallel execution coordinator.
  A. The number of logical pathways between parallel execution producers and consumers depends on the degree parallelism.
  B. The shared pool can be used for parallel execution messages buffers.
  E. Communication between parallel execution processes is never required if a query uses full partition-wise joins.
  F. Each parallel execution process has an additional connection to the parallel execution coordinator.

  ---

  A: Note that the degree of parallelism applies directly only to intra-operation parallelism. If inter-operation parallelism is possible, the total number of parallel execution servers for a statement can be twice the specified degree of parallelism. No more than two sets of parallel execution servers can run simultaneously. Each set of parallel execution servers may process multiple operations. Only two sets of parallel execution servers need to be active to guarantee optimal inter-operation parallelism.

  B: By default, Oracle allocates parallel execution buffers from the shared pool.

  F: When executing a parallel operation, the parallel execution coordinator obtains parallel execution servers from the pool and assigns them to the operation. If necessary, Oracle can create additional parallel execution servers for the operation. These parallel execution servers remain with the operation throughout job execution, then become available for other operations. After the statement has been processed completely, the parallel execution servers return to the pool.

  Reference: Oracle Database Data Warehousing Guide, Using Parallel Execution

• Question 102:

  An application user complains about statement execution taking longer than usual. You find that the query uses a bind variable in the WHERE clause as follows:

  

  You want to view the execution plan of the query that takes into account the value in the bind variable PCAT. Which two methods can you use to view the required execution plan?

  A. Use the DBMS_XPLAN.DISPLAY function to view the execution plan.
  B. Identify the SQL_ID for the statementsand use DBMS_XPLAN.DISPLAY_CURSOR for that SQL_ID to view the execution plan.
  C. Identify the SQL_ID for the statement and fetch the execution plan PLAN_TABLE.
  D. View the execution plan for the statement from V$SQL_PLAN.
  E. Execute the statement with different bind values and set AUTOTRACE enabled for session.
  B. Identify the SQL_ID for the statementsand use DBMS_XPLAN.DISPLAY_CURSOR for that SQL_ID to view the execution plan.
  D. View the execution plan for the statement from V$SQL_PLAN.

  ---

  D: V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache.

  B: The DBMS_XPLAN package supplies five table functions:

  DISPLAY_SQL_PLAN_BASELINE - to display one or more execution plans for the SQL statement identified by SQL handle

  DISPLAY - to format and display the contents of a plan table.

  DISPLAY_AWR - to format and display the contents of the execution plan of a stored SQL statement in the AWR.

  DISPLAY_CURSOR - to format and display the contents of the execution plan of any loaded cursor.

  DISPLAY_SQLSET - to format and display the contents of the execution plan of statements stored in a SQL tuning set.

• Question 103:

  Examine Exhibit 1 to view the query and its execution plan.

  

  Examine Exhibit 2 to view the structure and indexes for the EMPLOYEES and DEPARTMENTS tables.

  

  Examine Exhibit 3 to view the initialization parameters for the instance.

  

  Why is sort-merge join chosen as the access method?

  A. Because the OPTIMIZER_MODE parameter is set to ALL_ROWS.
  B. Because of an inequality condition.
  C. Because the data is not sorted in the LAST_NAME column of the EMPLOYEES table
  D. Because of the LIKE operator used in the query to filter out records
  A. Because the OPTIMIZER_MODE parameter is set to ALL_ROWS.

  ---

  Incorrect:

  B: There is not an inequality condition in the statement.

  C: Merge joins are beneficial if the columns are sorted.

  D: All regular joins should be able to use Hash or Sort Merge, except LIKE, !=, and NOT ... joins.

  Note:

  *

  A sort merge join is a join optimization method where two tables are sorted and then joined.

  *

  A "sort merge" join is performed by sorting the two data sets to be joined according to the join keys and then merging them together. The merge is very cheap, but the sort can be prohibitively expensive especially if the sort spills to disk. The cost of the sort can be lowered if one of the data sets can be accessed in sorted order via an index, although accessing a high proportion of blocks of a table via an index scan can also be very expensive in comparison to a full table scan.

  *

  Sort merge joins are useful when the join condition between two tables is an inequality condition (but not a nonequality) like <, <=, >, or >=. Sort merge joins perform better than nested loop joins for large data sets. You cannot use hash joins

  unless there is an equality condition.

  *

  When the Optimizer Uses Sort Merge Joins

  The optimizer can choose a sort merge join over a hash join for joining large amounts of data if any of the following conditions are true:

  / The join condition between two tables is not an equi-join.

  / Because of sorts already required by other operations, the optimizer finds it is cheaper to use a sort merge than a hash join. Reference: Oracle Database Performance Tuning Guide , Sort Merge Joins

• Question 104:

  Which three options are true about parallel queries when PARALLEL_DEGREE_POLICY is set to MANUAL and the session is using the default settings for parallel query, DDL, and DML?

  A. A subquery in a parallel DML is parallelized only if it includes a parallel hint.
  B. The number of parallel execution servers requested for a cursor is based on the greatest degree of parallelism associated with any object accessed by the cursor.
  C. A SELECT statement can be executed in parallel only if no scalar subqueries are contained in the SELECT list.
  D. In a CREATE TABLE . . . AS SELECT (CTAS) statement, SELECT is parallelized only if create TABLE is parallelized.
  E. In an INSERT INTO . . . SELECT FROM statement, INSERT is parallelized if select is parallelized.
  F. Single row inserts are never executed is parallel.
  C. A SELECT statement can be executed in parallel only if no scalar subqueries are contained in the SELECT list.
  E. In an INSERT INTO . . . SELECT FROM statement, INSERT is parallelized if select is parallelized.
  F. Single row inserts are never executed is parallel.

  ---

  * Decision to Parallelize

  A SELECT statement can be parallelized only if the following conditions are satisfied:

  / The query includes a parallel hint specification (PARALLEL or PARALLEL_INDEX) or the schema objects referred to in the query have a PARALLEL declaration associated with them. / At least one of the tables specified in the query requires

  one of the following:

  A full table scan An index range scan spanning multiple partitions / (C) No scalar subqueries are in the SELECT list.

  *

  By default, the system only uses parallel execution when a parallel degree has been explicitly set on an object or if a parallel hint is specified in the SQL statement.

  *

  CREATE TABLE ... AS SELECT in Parallel

  Parallel execution lets you parallelize the query and create operations of creating a table as a subquery from another table or set of tables. This can be extremely useful in the creation of summary or rollup tables.

  Clustered tables cannot be created and populated in parallel.

  *

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

  MANUAL

  Disables automatic degree of parallelism, statement queuing, and in-memory parallel execution. This reverts the behavior of parallel execution to what it was prior to Oracle Database 11g Release 2 (11.2). This is the default.

  Incorrect:

  A:

  *

  For parallel DML (INSERT, UPDATE, MERGE, and DELETE), the reference object that determines the DOP (degree of parallelism) is the table being modified by and insert, update, or delete operation. Parallel DML also adds some limits to the DOP to prevent deadlock. If the parallel DML statement includes a subquery, the subquery's DOP is the same as the DML operation.

  *

  For parallel DDL, the reference object that determines the DOP is the table, index, or partition being created, rebuilt, split, or moved. If the parallel DDL statement includes a subquery, the subquery's DOP is the same as the DDL operation.

  D: The CREATE TABLE ... AS SELECT statement contains two parts: a CREATE part (DDL) and a SELECT part (query). Oracle Database can parallelize both parts of the statement. The query part of a CREATE TABLE ... AS SELECT statement can be parallelized only if the following conditions are satisfied:

  The query includes a parallel hint specification (PARALLEL or PARALLEL_INDEX) or the CREATE part of the statement has a PARALLEL clause specification or the schema objects referred to in the query have a PARALLEL declaration associated with them.

  At least one of the tables specified in the query requires one of the following: a full table scan or an index range scan spanning multiple partitions.

  Reference: Oracle Database VLDB and Partitioning Guide, Using Parallel Execution

• Question 105:

  You are administering a database supporting a DDS workload in which some tables are updated frequently but not queried often. You have SQL plan baseline for these tables and you do not want the automatic maintenance task to gather statistics for these tables regularly.

  Which task would you perform to achieve this?

  A. Set the INCREMENTAL statistic preference FALSE for these tables.
  B. Set the STALE_PERCENT static preference to a higher value for these tables.
  C. Set the GRANULARITY statistic preference to AUTO for these tables.
  D. Set the PUBLISH statistic preference to TRUE for these tables.
  B. Set the STALE_PERCENT static preference to a higher value for these tables.

  ---

  With the DBMS_STATS package you can view and modify optimizer statistics gathered for database objects.

  STALE_PERCENT - This value determines the percentage of rows in a table that have to change before the statistics on that table are deemed stale and should be regathered. The default value is 10%.

  Reference: Oracle Database PL/SQL Packages and Types Reference

• Question 106:

  Examine the Exhibit and view the query and its execution plan.

  

  Which statement is correct about the parallel executions plan?

  A. The CUSTOMERS and SALES tables are scanned simultaneously in parallel and then joined in parallel.
  B. First, the CUSTOMERS table is scanned in parallel, then the SALES table is scanned in parallel, and then they are joined serially.
  C. First, the SALES table is scanned in parallel, then the CUSTOMERS table us scanned in parallel, and then they are joined in parallel.
  D. The CUSTOMERS and SALES tables are scanned simultaneously in parallel and then joined serially.
  E. First, the CUSTOMERS table is scanned in parallel, then the SALES table us scanned in parallel, and then they are joined in parallel.
  A. The CUSTOMERS and SALES tables are scanned simultaneously in parallel and then joined in parallel.

  ---

  As per exhibit:

  Line 7 and line 11 are run in parallel.

  Line 8 and line 12 are run in parallel.

  Line 9 and line 13 are run in parallel.

  Line 10 and line 14 are run in parallel.

  Line 6 is a PCWP (parallel combined with parent) and the parent is a P-> P (Parallel to parallel) operation.

• Question 107:

  You need to migrate database from oracle Database 10g to 11g. You want the SQL workload to start the 10g plans in the 11g database instance and evolve better plans.

  Examine the following steps:

  1.

  Capture the pre-Oracle Database 11g plans in a SQL Tuning Set (STS)

  2.

  Export the STS from the 10g system.

  3.

  Import the STS into Oracle Database 11g.

  4.

  Set the OPTIMIZER_FEATURES_ENABLE parameter to 10.2.0.

  5.

  Run SQL Performance Analyzer for the STS.

  6.

  Set the OPTIMIZER_FEATURES_ENABLE parameter to 11.2.0.

  7.

  Rerun the SQL Performance Analyzer for the STS.

  8.

  Set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE to TRUE.

  9.

  Use DBMS_SPM.EVOLVE_SQL_BASELINE function to evolve the plans.

  10.

  Set the OPTIMIZER_USE_SQL_PLAN_BASELINE to TRUE.

  Identify the required steps in the correct order.

  A. 1, 2, 3, 4, 5, 6, 7,
  B. 4, 8, 10
  C. 1, 2, 3, 4, 8, 10
  D. 1, 2, 3, 6, 9, 5
  E. 1, 2, 3, 5, 9, 10
  C. 1, 2, 3, 4, 8, 10

  ---

  Step 1: (1)

  Step 2: (2)

  Step 3: (3)

  Step 4: (4)

  By setting the parameter OPTIMIZER_FEATURES_ENABLE to the 10g version used before the upgrade, you should be able to revert back to the same execution plans you had prior to the upgrade.

  Step 5: (8)

  OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

  In Oracle Database 11g a new feature called SQL Plan Management (SPM) has been introduced to guarantees any plan changes that do occur lead to better performance. When OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is set to

  TRUE (default FALSE) Oracle will automatically capture a SQL plan baseline for every repeatable SQL statement on the system. The execution plan found at parse time will be added to the SQL plan baseline as an accepted plan.

  Step 6: (10)

  OPTIMIZER_USE_SQL_PLAN_BASELINES enables or disables the use of SQL plan baselines stored in SQL Management Base. When enabled, the optimizer looks for a SQL plan baseline for the SQL statement being compiled. If one is

  found in SQL Management Base, then the optimizer will cost each of the baseline plans and pick one with the lowest cost.

• Question 108:

  Examine the following query and execution plan:

  

  Which query transformation technique is used in this scenario?

  A. Join predicate push-down
  B. Subquery factoring
  C. Subquery unnesting
  D. Join conversion
  A. Join predicate push-down

  ---

  *

  Normally, a view cannot be joined with an index-based nested loop (i.e., index access) join, since a view, in contrast with a base table, does not have an index defined on it. A view can only be joined with other tables using three methods: hash, nested loop, and sort-merge joins.

  *

  The following shows the types of views on which join predicate pushdown is currently supported. UNION ALL/UNION view Outer-joined view Anti-joined view Semi-joined view DISTINCT view GROUP-BY view

• Question 109:

  Which two statements are true about the use of the DYNAMIC_SAMPLING hint in a query?

  A. It estimates selectivity better for the filters.
  B. It is always used for flashback queries that contain the AS OF clause.
  C. It cannot be used if there is a single-table predicate in the WHERE clause.
  D. It cannot be used for processing SQL statements in parallel.
  E. It can compensate for the lack of extended statistics to get accurate cardinality estimates for complex predicate expressions.
  D. It cannot be used for processing SQL statements in parallel.
  E. It can compensate for the lack of extended statistics to get accurate cardinality estimates for complex predicate expressions.

  ---

  D: For parallel statements, the optimizer automatically decides whether to use dynamic sampling and which level to use. The decision depends on the size of the tables and the complexity of the predicates. The optimizer expects parallel statements to be resource-intensive, so the additional overhead at compile time is worth it to ensure the best plan. The database ignores the For serially processed SQL statements, the dynamic sampling level depends on the value of the OPTIMIZER_DYNAMIC_SAMPLING parameter and is not triggered automatically by the optimizer. Serial statements are typically short-running, so that any overhead at compile time could have a huge impact on their performance. the value is honored.

  Reference: Oracle Database Administrator's Guide, About Oracle Database Resource Manager

• Question 110:

  Refer to the Exhibit.

  

  Execution plan:

  

  What must be the correct order of steps that the optimizer executes based on the ID column the execution plan?

  A. 3, 5, 4, 6, 7
  B. 3, 5, 4, 7, 6
  C. 3, 4, 5, 7, 6
  D. 4, 5, 3, 7, 6
  D. 4, 5, 3, 7, 6