Oracle Oracle Certifications 1Z0-117 Questions & Answers

• Question 71:

  You executed the following statements:

  

  Which two statements are true about the query execution?

  A. The execution plan is generated and fetched from the library cache.

  B. The query executes and displays the execution plan and statistics.

  C. The query executes and inserts the execution plan in PLAN_TABLE.

  D. The query executes and execution plan is stored in the library cache and can be viewed using v$SQL_PLAN.

  E. The query will always use the plan displayed by the AUTOTRACE output.

  Correct Answer: BD

  B: set autotrace traceonly: Displays the execution plan and the statistics (as set autotrace on does), but doesn't print a query's result.

  Note:

  / Autotrace

  Autotrace can be configured to run the SQL and gives a plan and statistics afterwards or just give you an explain plan without executing the query. To achieve this

  use the following:

  *

  Explain only

  set autotrace traceonly explain

  *

  Execute with stats and explain plan

  set autotrace on explain stat (with data returned by query)

  or

  autotrace traceo expl stat (without data returned by query)

  *

  To make the output from an autotrace more readable

  col plan_plus_exp format a100

  *

  Turn off autotrace

  set autotrace off

  / V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache. http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3054.htm#REFRN30250

• Question 72:

  Examine the initialization parameters for a database an OLTP overload.

  

  What is the effect of changing the value of the parameter?

  A. It influences the optimizer to always use the value of the parameter?

  B. It influences the optimizer to use indexes instead of full table scans as the estimated cost of the using index is reduced.

  C. It influences the optimizer to use full table scans instead of index scans as the estimated cost of full table scan is reduced.

  D. It influenced the optimizer to use bitmap indexes as the estimated cost conversion from bimap is rowed us reduced.

  Correct Answer: B

  OPTIMIZER_INDEX_COST_ADJ

  OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly--that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.

  The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.

• Question 73:

  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.

  Correct Answer: A

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

  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.

  Correct Answer: A

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

  Examine the parallelism parameters for your instance:

  

  What are true about the execution of the query?

  A. It will execute in parallel only if the LINEITEM table has a dictionary DOP defined.

  B. DOP for the statement is determined by the dictionary DOP of the accessed objects.

  C. It is generated to execute in parallel.

  D. It will execute in parallel only if the estimated execution time is 10 or more seconds.

  E. DOP for the statement is calculated automatically.

  F. It may execute serially.

  Correct Answer: EF

  E:

  F (not C): It may execute serially. See note below.

  Incorrect:

  A, B: Dictionary DOP not used with PARALLEL (AUTO) hint.

  D: The default value of parallel_min_time_threshold is 30 (not 10) seconds.

  Note:

  * parallel_min_percent 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.

• Question 76:

  View the code sequence:

  

  Examine the Exhibit to view the execution plan.

  

  

  Which two statements are true about the query execution?

  A. The optimizer joins specified tables in the order as they appear in the FROM clause.

  B. The CUSTOMERS and ORDERS tables are joined first and the resultant is then joined with rows returned by the ORDER_ITEMS table.

  C. The CUSTOMERS and ORDER_ITEMS tables are joined first the resultant is then joined with rows by the ORDERS table.

  D. The optimizer has chosen hash join as an access method as the OPTIMIZER_MODE parameter is set to FIRST_ROWS.

  Correct Answer: CD

  The first executed join is in line 6. The second executed join is in line 1.

  Incorrect:

  A: Line 7 and 8 are executed first.

• Question 77:

  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.

  Correct Answer: ACF

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

  You have created some materialized views to improve the performance of several queries.

  Which four must be true to enable sessions to benefit from improved response time made possible by these materialized views?

  A. Query rewrite must be enabled for the sessions.

  B. Bitmap indexes must exist on all the columns involved in the join operations for the defining query of the MVIEWs.

  C. All or part of the query results must be obtainable from one or more MVIEWs.

  D. Bitmap join indexes must exist on all the columns involved in the join operations.

  E. Session users must have query rewrite privilege.

  F. The MVIEWs must be enabled for query rewrite.

  G. All or part of the query results must be obtainable from one MVIEW.

  Correct Answer: ABCF

  A: For a given user's session, ALTER SESSION can be used to disable or enable query rewrite for that session only.

  B: Bitmap indexes on the join columns would improve performance.

  C (not G) : One of the major benefits of creating and maintaining materialized views is the ability to take advantage of query rewrite, which transforms a SQL statement expressed in terms of tables or views into a statement accessing one or more materialized views that are defined on the detail tables.

  F:

  *

  A materialized view is only eligible for query rewrite if the ENABLE QUERY REWRITE clause has been specified, either initially when the materialized view was first created or subsequently with an ALTER MATERIALIZED VIEW statement.

  *

  Enabling or disabling query rewrite: by the CREATE or ALTER statement for individual materialized views by the initialization parameter QUERY_REWRITE_ENABLED by the REWRITE and NOREWRITE hints in SQL statements

  Note:

  *

  A materialized view is a replica of a target master from a single point in time. The master can be either a master table at a master site or a master materialized view at a materialized view site. Whereas in multimaster replication tables are continuously updated by other master sites, materialized views are updated from one or more masters through individual batch updates, known as a refreshes, from a single master site or master materialized view site.

• Question 79:

  Examine the initializing parameters:

  

  An index exists on the column used in the WHERE of a query. You execute the query for the first time today and notice that the query is not using the index. The CUSTOMERS table has 55000 rows.

  View the exhibit and examine the query and its execution plan.

  

  What can be the two reasons for full table scan?

  A. The value of the OPTIMIZER_INDEX_COST_ADJ parameter is set to a low value.

  B. The blocks fetched by the query are greater than the value specified by the DB_FILE_MULTIBLOCK_READ_COUNT parameter.

  C. The statistics for the CUSTOMERS table and the indexes stale.

  D. The OPTIMIZER_MODE parameter is set to ALL_ROWS.

  E. Histogram statistics for CUST_CITY_ID are missing.

  F. Average number of rows per block for the CUSTOMERS table is low.

  Correct Answer: CE

  C: Old statistics could cause this problem. "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.

• Question 80:

  Which statement is true about an SQL plan baselines that are fixed?

  A. New plans are added automatically by the optimizer to the baseline and are automatically evolved.

  B. New, better plans are added automatically as a fixed plan baseline.

  C. New plan can be manually loaded to the baseline from the cursor cache or a SQL tuning set.

  D. New plans can be added as fixed plans to the baseline by using the SQL Tuning Advisor to generate a SQL profile and by accepting the SQL profile.

  Correct Answer: D

  When a SQL statement with a fixed SQL plan baseline is tuned using the SQL Tuning Advisor, a SQL profile recommendation has special meaning. When the

  SQL profile is accepted, the tuned plan is added to the fixed SQL plan baseline as a non-fixed plan. However, as described above, the optimizer will not use the

  tuned plan as long as a reproducible fixed plan is present. Therefore, the benefit of SQL tuning may not be realized. To enable the use of the tuned plan, manually

  alter the tuned plan to a fixed plan by setting its FIXED attribute to YES.

  Note:

  It is also possible to influence the optimizer's choice of plan when it is selecting a plan from a SQL plan baseline. SQL plan baselines can be marked as fixed.

  Fixed SQL plan baselines indicate to the optimizer that they are preferred. If the optimizer is costing SQL plan baselines and one of the plans is fixed, the optimizer

  will only cost the fixed plan and go with that if it is reproducible.

  If the fixed plan(s) are not reproducible the optimizer will go back and cost the remaining SQL plan baselines and select the one with the lowest cost. Note that

  costing a plan is nowhere near as expensive as a hard parse. The optimizer is not looking at all possible access methods but at one specific access path.

  Reference: Oracle Database Performance Tuning Guide 11g, Using Fixed SQL Plan Baselines Reference: SQL Plan Management in Oracle Database 11g