Oracle 1Z0-117 Online Practice Questions and Exam Preparation

Oracle 1Z0-117 Online Questions & Answers

• Question 51:

  Examine the query and its execution plan:

  

  Which two statements are true regarding the execution plan?

  A. For every row of CUSTOMERS table, the row matching the join predicate from the ORDERS table are returned.
  B. An outer join returns NULL for the ORDERS table columns along with the CUSTOMERS table rows when it does not find any corresponding rows in the ORDER table.
  C. The data is aggregated from the ORDERS table before joining to CUSTOMERS.
  D. The NESTED LOOP OUTER join is performed because the OPTIMZER_MODE parameter is set to ALL_ROWS.
  B. An outer join returns NULL for the ORDERS table columns along with the CUSTOMERS table rows when it does not find any corresponding rows in the ORDER table.
  D. The NESTED LOOP OUTER join is performed because the OPTIMZER_MODE parameter is set to ALL_ROWS.

  ---

  B: An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition. Note:

  *

  All_rows attempts to optimize the query to get the very last row as fast as possible. This makes sense in a stored procedure for example where the client does not regain control until the stored procedure completes. You don't care if you

  have to wait to get the first row if the last row gets back to you twice as fast. In a client server/interactive application you may well care about that.

  *

  The optimizer uses nested loop joins to process an outer join in the following circumstances:

  / It is possible to drive from the outer table to inner table.

  / Data volume is low enough to make the nested loop method efficient.

  *

  First_rows attempts to optimize the query to get the very first row back to the client as fast as possible. This is good for an interactive client server environment where the client runs a query and shows the user the first 10 rows or so and

  waits for them to page down to get more.

• Question 52:

  You recently gathered statistics for a table by using the following commands:

  

  You noticed that the performance of queries has degraded after gathering statistics. You want to use the old statistics. The optimizer statistics retention period is default. What must you do to use the old statistics?

  A. Use the flashback to bring back the statistics to the desired time.
  B. Restore statistics from statistics history up to the desired time.
  C. Delete all the statistics collected after the desired time.
  D. Set OPTIMIZER_USE_PENDING_STATISTICS to TRUE.
  B. Restore statistics from statistics history up to the desired time.

  ---

  Whenever statistics in dictionary are modified, old versions of statistics are saved automatically for future restoration. Statistics can be restored using RESTORE procedures of DBMS_STATS package. These procedures use a time stamp as an argument and restore statistics as of that time stamp. This is useful in case newly collected statistics leads to some sub-optimal execution plans and the administrator wants to revert to the previous set of statistics.

  Reference: Oracle Database Performance Tuning Guide, Restoring Previous Versions of Statistics

• Question 53:

  View the code sequence:

  

  Which three statements are true about the execution of this query?

  A. The optimizer uses an index join as an access path.
  B. The optimizer uses a B* tree access path for the EMPLOYEES table.
  C. The optimizer uses a bitmap access path for the EMPLOYEES table.
  D. The optimizer performs bitmap conversion from row IDs.
  E. The optimizer performs bitmap conversion to row IDs of the combined maps.
  F. The optimizer performs bitmap conversion from the row IDs of the combined maps.
  A. The optimizer uses an index join as an access path.
  C. The optimizer uses a bitmap access path for the EMPLOYEES table.
  F. The optimizer performs bitmap conversion from the row IDs of the combined maps.

  ---

  C: The INDEX_COMBINE hint explicitly chooses a bitmap access path for the table.

  F: If you specify indexspec, then the optimizer tries to use some Boolean combination of the specified indexes. Each parameter serves the same purpose as in "INDEX Hint". For example:

  SELECT /*+ INDEX_COMBINE(e emp_manager_ix emp_department_ix) */ *

  FROM employees e

  WHERE manager_id = 108

  OR department_id = 110;

• Question 54:

  You execute the following query for the first time:

  

  Examine the SQL statement processing steps:

  1.

  The value of the variable SAL is obtained to run the query.

  2.

  The syntax of the query is checked

  3.

  A parse tree for the query is generated

  4.

  Semantics for the query are checked

  5.

  The required rows are fetched

  6.

  The SQL is executed to produce the required result.

  Which is the correct order of execution of the above query?

  A. 1, 2 3, 4, 5, 6
  B. 1, 4, 2, 3, 6, 5
  C. 2, 4, 1, 3, 6, 5
  D. 2, 3, 1, 4, 6, 5
  C. 2, 4, 1, 3, 6, 5

  ---

  Step 1: Syntax check (2)

  Step 2: Semantic check (4)

  Step 3: Shared pool check

  Step 4: Optimization

  Step 5: Row Source Optimization (generation of query plan) (3)

  Etc.

  Note:

  

  Incorrect:

  Not A: First execute then fetch rows.

  Not B: Check of syntax is before check of semantics.

  Not D: Parse tree is after semantics.

• Question 55:

  Examine the Exhibit.

  

  Given two sets of parallel execution processes, SS1 and SS2, which is true?

  A. Each process SS1 reads some of the rows from the CUSTOMERS table and sends all the rows it reads to each process in SS2.
  B. Each process in SS1 reads all the rows from the CUSTOMERS table and distributes the rows evenly among the processes in SS2.
  C. Each process in SS1 reads some of the rows from the SALES table and sends all the rows it reads to each process in SS2.
  D. Each process in SS1 reads all the rows from the SALES table and distributes the rows evenly among the processes in SS2.
  E. Each process in SS1 reads some of the rows from the SALES table and distributes the rows evenly among the processes in SS2.
  F. Each process in the SS1 reads some of the rows from the CUSTOMERS table and distributes the rows evenly among the processes in SS2.
  D. Each process in SS1 reads all the rows from the SALES table and distributes the rows evenly among the processes in SS2.

  ---

  Note:

  *

  The execution starts with line 16 (accessing the SALES table), followed by line 15.

  *

  PX BLOCKITERATOR The PX BLOCK ITERATOR row source represents the splitting up of the table EMP2 into pieces so as to divide the scan workload between the parallel scan slaves. The PX SEND and PX RECEIVE row sources represent the pipe that connects the two slave sets as rows flow up from the parallel scan, get repartitioned through the HASHtable queue, and then read by and aggregated on the top slave set.

• Question 56:

  You created a SQL Tuning Set (STS) containing resource-intensive SQL statements. You plan to run the SQL Tuning Advisor. Which two types of recommendations can be provided by the SQL Tuning Advisor?

  A. Semantic restructuring for each SQL statement
  B. Gathering missing or stale statistics at the schema level for the entire workload
  C. Creating a materialized view to benefit from query rewrite for the entire workload
  D. Gathering missing or stale statistics for objects used by the statements.
  E. Creating a partition table to benefit from partition pruning for each statement
  A. Semantic restructuring for each SQL statement
  D. Gathering missing or stale statistics for objects used by the statements.

  ---

  The output of the SQL Tuning Advisor is in the form of an advice or recommendations, along with a rationale for each recommendation and its expected benefit. The recommendation relates to collection of statistics on objects ( D), creation of new indexes, restructuring of the SQL statement (A), or creation of a SQL profile. You can choose to accept the recommendation to complete the tuning of the SQL statements.

  Note:

  *

  A SQL Tuning Set can be used as input to the SQL Tuning Advisor, which performs automatic tuning of the SQL statements based on other input parameters specified by the user.

  *

  A SQL Tuning Set (STS) is a database object that includes one or more SQL statements along with their execution statistics and execution context, and could include a user priority ranking. The SQL statements can be loaded into a SQL Tuning Set from different SQL sources, such as the Automatic Workload Repository, the cursor cache, or custom SQL provided by the user.

  Reference: Oracle Database Performance Tuning Guide 11g , SQL Tuning Advisor

• Question 57:

  Which three statements are true about the usage of optimizer hints?

  A. Whenever a query uses table aliases, the hints in the query must use the aliases.
  B. The OPTIMIZER_FEATURES_ENABLE parameter must be set to a version supports the hints used.
  C. The optimizer uses the execution plan with lower cost even if a hint is specified.
  D. A schema name for the table must be used in the hint if the table us qualified in the FROM clause.
  E. Hints can be used to override the optimization approach specified with the OPTIMIZER_MODE parameter.
  F. A statement block can have only one hint, and that hint must be immediately after SELECT, UPDATE, INSERT, MERGE, or DELETE keyword.
  A. Whenever a query uses table aliases, the hints in the query must use the aliases.
  B. The OPTIMIZER_FEATURES_ENABLE parameter must be set to a version supports the hints used.
  E. Hints can be used to override the optimization approach specified with the OPTIMIZER_MODE parameter.

  ---

  *

  You must specify the table to be accessed exactly as it appears in the statement. If the statement uses an alias for the table, then use the alias rather than the table name in the hint.

  *

  OPTIMIZER_FEATURES_ENABLE acts as an umbrella parameter for enabling a series of optimizer features based on an Oracle release number.

  For example, if you upgrade your database from release 10.1 to release 11.1, but you want to keep the release 10.1 optimizer behavior, you can do so by setting this parameter to 10.1.0. At a later time, you can try the enhancements introduced in releases up to and including release 11.1 by setting the parameter to 11.1.0.6.

  *

  If a SQL statement has a hint specifying an optimization approach and goal, then the optimizer uses the specified approach regardless of the presence or absence of statistics, the value of the OPTIMIZER_MODE initialization parameter,

  and the OPTIMIZER_MODE parameter of the ALTER SESSION statement.

• Question 58:

  You notice some performance degradation for a high-load SQL statement in your database. After investigations, you run the SQL Tuning Advisor, which recommends a SQL Profile. You accept the profile recommendation resulting in a new,

  tuned execution plan for the statement.

  Your database uses SQL plan management and a SQL plan baseline exists for this SQL statement.

  Which statement is true?

  A. The database adds the tuned plan to the SQL plan baseline as a nonfixed plan.
  B. The database adds the tuned plan to the SQL plan baseline as a fixed plan.
  C. The optimizer uses the new tuned plan only when a reproducible fixed plan is present.
  D. The created SQL profile will continuously adapt to all changes made to the database, the object, and to the system statistics over an extended length of time.
  A. The database adds the tuned plan to the SQL plan baseline as a nonfixed plan.

  ---

  Note:

  *

  When the SQL Tuning Advisor recommends that a SQL Profile be used, you should accept the SQL Profile that is recommended. In cases where the SQL Tuning Advisor recommends that an index and a SQL Profile be used, both should be used. You can use the DBMS_SQLTUNE.ACCEPT_SQL_PROFILE procedure to accept a SQL Profile recommended by the SQL Tuning Advisor. This creates and stores a SQL Profile in the database.

  *

  When tuning SQL statements with the SQL Tuning Advisor, if the advisor finds a tuned plan and verifies its performance to be better than a plan chosen from the corresponding SQL plan baseline, it makes a recommendation to accept a SQL profile. When the SQL profile is accepted, the tuned plan is added to the corresponding SQL plan baseline.

  *

  If SQL plan management is used and there is already an existing plan baseline for the SQL statement, a new plan baseline will be added when a SQL profile is created.

  *

  SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system.

  *

  SQL plan baseline is fixed if it contains at least one enabled plan whose FIXED attribute is set to YES.

  *

  ACCEPT_SQL_PROFILE Procedure and Function

  This procedure creates a SQL Profile recommended by the SQL Tuning Advisor. The SQL text is normalized for matching purposes though it is stored in the data dictionary in de-normalized form for readability.

• Question 59:

  Examine the Exhibit.

  

  Which two statements are true about the bloom filter in the execution plan?

  A. The bloom filter prevents all rows from table T1 that do not join T2 from being needlessly distributed.
  B. The bloom filter prevents all rows from table T2 that do not join table T1 from being needlessly distributed.
  C. The bloom filter prevents some rows from table T2 that do not join table T1 from being needlessly distributed.
  D. The bloom filter is created in parallel by the set of parallel execution processes that scanned table T2.
  E. The bloom filter is created in parallel by the set of parallel execution processes that later perform join.
  F. The bloom filter is created in parallel by the set of parallel execution processes that scanned table T1.
  B. The bloom filter prevents all rows from table T2 that do not join table T1 from being needlessly distributed.
  F. The bloom filter is created in parallel by the set of parallel execution processes that scanned table T1.

  ---

  *

  PX JOIN FILTER CREATE The bloom filter is created in line 4.

  *

  PX JOIN FILTER USE The bloom filter is used in line 11.

  Note:

  *

  You can identify a bloom pruning in a plan when you see :BF0000 in the Pstart and Pstop columns of the execution plan and PART JOIN FILTER CREATE in the operations column.

  *

  A Bloom filter is a probabilistic algorithm for doing existence tests in less memory than a full list of keys would require. In other words, a Bloom filter is a method for representing a set of n elements (also called keys) to support membership queries.

  *

  The Oracle database makes use of Bloom filters in the following 4 situations:

  -

  To reduce data communication between slave processes in parallel joins: mostly in RAC

  -

  To implement join-filter pruning: in partition pruning, the optimizer analyzes FROM and WHERE clauses in SQL statements to eliminate unneeded partitions when building the partition access list

  -

  To support result caches: when you run a query, Oracle will first see if the results of that query have already been computed and cached by some session or user, and if so, it will retrieve the answer from the server result cache instead of gathering all of the database blocks

  -

  To filter members in different cells in Exadata: Exadata performs joins between large tables and small lookup tables, a very common scenario for data warehouses with star schemas. This is implemented using Bloom filters as to determine whether a row is a member of the desired result set.

• Question 60:

  An application accessing your database got the following error in response to SQL query:

  ORA-12827: insufficient parallel query slaves available

  View the parallel parameters for your instance:

  

  No hints are used and the session use default parallel settings.

  What four changes could you make to help avoid the error and ensure that the query executes in parallel?

  A. Set PARELLEL_DEGREE_POLICY to AUTO.
  B. Increase the value of PARELLEL_MAX_SERVERS.
  C. Increase PARELLEL_SERVERS_TARGET.
  D. Decrease PARELLEL_MIN_PERCENT.
  E. Increase PARELLEL_MIN_SERVERS.
  F. Decrease PARELLEL_MIN_TIME_THRESHOLD.
  G. Increase PARELLEL__MIN_TIME_THRESHOLD.
  A. Set PARELLEL_DEGREE_POLICY to AUTO.
  C. Increase PARELLEL_SERVERS_TARGET.
  D. Decrease PARELLEL_MIN_PERCENT.
  G. Increase PARELLEL__MIN_TIME_THRESHOLD.

  ---

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

  D: Note: ORA-12827: insufficient parallel query slaves available Cause: PARALLEL_MIN_PERCENT parameter was specified and fewer than minimum slaves were acquired Action: either re-execute query with lower PARALLEL_MIN_PERCENT or wait until some running queries are completed, thus freeing up slaves

  A, G: 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.