Oracle Oracle Certifications 1Z0-117 Questions & Answers

• Question 31:

  Your database has the OLTP_SRV service configured for an OLTP application running on a middle tier. This service is used to connect to the database by using

  connection pools. The application has three modules. You enabled tracing at the service by executing the following command:

  SQL exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE (`OLTP_SRV');

  What is the correct method of consolidating the trace files generated by the procedure?

  A. Use all trace files as input for the tkprof utility to consolidate the trace files for a module.

  B. Use one trace file at a time as input for the trcess utility and use tkprof utility to consolidate all the output files for a module.

  C. Use the trcess utility to consolidate all trace files into a single output file, which can then be processed by the tkprof utility.

  D. Use the tkprof utility to consolidate the trace files and create an output that can directly be used for diagnostic purposes.

  Correct Answer: C

  Note:

  * Oracle provides the trcsess command-line utility that consolidates tracing information based on specific criteria.

  The SQL Trace facility and TKPROF are two basic performance diagnostic tools that can help you monitor applications running against the Oracle Server.

  Note: SERV_MOD_ACT_TRACE_ENABLE Procedure

  Enables SQL tracing for a given combination of Service Name, MODULE and ACTION globally unless an instance_name is specified

  Reference: Oracle Database Performance Tuning Guide

• Question 32:

  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.

  Correct Answer: B

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

  Examine the Exhibit1 to view the structure of an indexes for the EMPLOYEES table.

  

  Examine the query:

  SQL> SELECT * FROM employees WHERE employees_id IN (7876, 7900, 7902);

  EMPLOYEE_ID is a primary key in the EMPLOYEES table that has 50000 rows.

  Which statement is true regarding the execution of the query?

  A. The query uses an index skip scan on the EMP_EMP_ID_PK index to fetch the rows.

  B. The query uses the INLIST ITERATOR operator to iterate over the enumerated value list, and values are evaluated using an index range scan on the EMP_EMP_ID_PK index.

  C. The query uses the INLIST ITERATOR operator to iterate over the enumerated value list, and values are evaluated using a fast full index scan on the EMP_EMP_ID_PK index.

  D. The query uses the INLIST ITERATOR operator to iterate over the enumerated value list, and values are evaluated using an index unique scan on the EMP_EMP_ID_PK index.

  E. The query uses a fast full index scan on the EMP_EMP_ID_PK index fetch the rows.

  Correct Answer: B

  How the CBO Evaluates IN-List Iterators

  The IN-list iterator is used when a query contains an IN clause with values. The execution plan is identical to what would result for a statement with an equality clause instead of IN except for one additional step. That extra step occurs when the IN-list iterator feeds the equality clause with unique values from the IN-list.

  Both of the statements in Example 2-1 and Example 2-1 are equivalent and produce the same plan.

  Example 2-1 IN-List Iterators Initial Statement

  SELECT header_id, line_id, revenue_amount FROM so_lines_all WHERE header_id IN (1011,1012,1013);

  SELECT header_id, line_id, revenue_amount FROM so_lines_all WHERE header_id = 1011 OR header_id = 1012 OR header_id = 1013;

  Plan

  SELECT STATEMENT INLIST ITERATOR TABLE ACCESS BY INDEX ROWID SO_LINES_ALL INDEX RANGE SCAN SO_LINES_N1

  Reference: Database Performance Tuning Guide and Reference

• Question 34:

  Examine the execution plan:

  

  Which two are true regarding the execution plan?

  A. The CUSTOMERS table is hash partitioned.

  B. The SALES table is hash partitioned.

  C. The CUSTOMERS table is scanned first and selected partitions from the SALES table are scanned based on the BLOOM Filter created during the scan of the CUSTOMERS table.

  D. The SALES table is scanned first and selected partitions from the CUSTOMERS table are scanned based on the Bloom Filter created during the scan of the SALES table.

  E. Both the CUSTOMERS and SALES tables are scanned simultaneously and rows from the CUSTOMERS table are joined to row of the SALES table.

  F. The CUSTOMERS table is range partitioned.

  Correct Answer: BC

  B: As per line 14 and 15.

  C: As per exhibit line 13 is execute before line 15.

  Incorrect:

  Not E: As per line 13 and 15 they are not executed simultaneously.

• Question 35:

  Which two tasks are performed during the optimization stage of a SQL statement?

  A. Evaluating the expressions and conditions in the query

  B. Checking the syntax and analyzing the semantics of the statement

  C. Separating the clauses of the SQL statement into structures that can be processed

  D. Inspecting the integrity constraints and optimizing the query based on this metadata

  E. Gathering the statistics before creating the execution plan for the statement

  Correct Answer: DE

  Note:

  Oracle SQL is parsed before execution, and a hard parse includes these steps: *

  1.

  Loading into shared pool - The SQL source code is loaded into RAM for parsing. (the "hard" parse step)

  2.

  Syntax parse - Oracle parses the syntax to check for misspelled SQL keywords.

  3.

  Semantic parse - Oracle verifies all table and column names from the dictionary and checks to see if you are authorized to see the data.

  4.

  Query Transformation - If enabled (query_rewrite=true), Oracle will transform complex SQL into simpler, equivalent forms and replace aggregations with

  materialized views, as appropriate.

  5.

  Optimization - Oracle then creates an execution plan, based on your schema statistics (or maybe with statistics from dynamic sampling in 10g).

  6.

  Create executable - Oracle builds an executable file with native file calls to service the SQL query.

  *

  The parsing process performs two main functions:

  o Syntax Check: is the statement a valid one. Does it make sense given the SQL grammar documented in the SQL Reference Manual. Does it follow all of the

  rules for SQL.

  o Semantic Analysis: Going beyond the syntax ? is the statement valid in light of the objects in the database (do the tables and columns referenced exist). Do you

  have access to the objects ? are the proper privileges in place? Are there ambiguities in the statement ? for example if there are two tables T1 and T2 and both

  have a column X, the query ?select X from T1, T2 where ?? is ambiguous, we don?t know which table to get X from. And so on.

  So, you can think of parsing as basically a two step process, that of a syntax check to check the validity of the statement and that of a semantic check ? to ensure

  the statement can execute properly.

  Reference: Oracle hard-parse vs. soft parse

• Question 36:

  An application frequently executed similar types of queries that vary only in the usage of literals in the WHERE clause. You plan to use bind variable in place of literal values.

  The CURSOR_SHARING parameter to set to EXACT.

  Which two statements are true about the usage of bind variables?

  A. The number of latch gets in the library cache will be reduced.

  B. Bind peeking will take place and subsequent execution of queries can have different plans based on the cardinality of the bind value in the column.

  C. Bind peeking will take place and subsequent execution of queries can have different plans only when the histograms exists on a column used in WHERE clause.

  D. Bind peeking will not happen and the optimizer will use the same plan for all bind values if no histograms exist on a column use in the WHERE clause.

  E. Bind peeking will happen and subsequent execution of queries will have the same parent cursor but different child cursors.

  Correct Answer: AB

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

  EXACT

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

  Note:

  EXACT-This is the default setting. With this value in place, the query is not rewritten to use bind variables.

  With CURSOR_SHARING=EXACT (the default), every unique SQL statement executed will create a new entry in V$SQL, it will be hard-parsed, and an execution plan will be created just for it. There can be hundreds or thousands of very similar queries in the shared pool that differ only in the literals used in the SQL statement itself. This implies that the database is forced to hard-parse virtually every query, which, in turn, not only consumes a lot of CPU cycles but also leads to decreased scalability.

  The database just cannot hard-parse hundreds or thousands of SQL statements concurrently-- the application ends up waiting for the shared pool to become available. One of the major scalability inhibitors in the database is not using bind variables. That was the motivation behind adding CURSOR_SHARING=FORCE .

• Question 37:

  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.

  Correct Answer: BD

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

  In which three situations must you collect optimizer statistics manually for database objects in addition to automatic statistics collection?

  A. When substantial DML activity occurs between the nightly automatic stats gathering maintenance job

  B. When substantial activity occurs on a partition of the partitioned table.

  C. When a table is used for bulk loads that add 10% or more to the total size of the table

  D. When an index is created or dropped for a column

  E. When the degree of parallelism is explicitly defined for a table

  Correct Answer: ABC

  http://docs.oracle.com/cd/E11882_01/server.112/e23633/changes.htm#UPGRD12509

• Question 39:

  A database instance is configured in the shared server mode and it supports multiple applications running on a middle tier. These applications connect to the database using different services. You enabled the statistics gathering for the service by using the following command:

  SQL > EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE (`APPS1', NULL, NULL);

  Which two statements are true regarding statistics gathered for APPS1 service?

  A. The statistics are gathered for all the modules and actions within the service.

  B. The statistics are collected at the session level for all sessions connected using the service.

  C. The statistics are aggregated and stored in the V$SERV_MOD_ACT_STATS view.

  D. The statistics are gathered for all the modules using the service only when DBMS_APPLICATION_INFO.SET_MODULE is executed to register with the service.

  E. Statistics gathering is enabled only for the subsequent sessions using the service.

  F. The statistics are gathered for all the applications using the service only when DBMS_APPLICATION_INFO.SET_ACTION is executed to register with the service.

  Correct Answer: AC

  SERV_MOD_ACT_STAT_ENABLE Procedure

  This procedure enables statistic gathering for a given combination of Service Name, MODULE and ACTION. Calling this procedure enables statistic gathering for a hierarchical combination of Service name, MODULE name, and ACTION name on all instances for the same database. Statistics are accessible by means of the V$SERV_MOD_ACT_STATS view.

  Note:

  * Syntax

  DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(

  service_name IN VARCHAR2,

  module_name IN VARCHAR2,

  action_name IN VARCHAR2 DEFAULT ALL_ACTIONS);

  Parameter, Description

  service_name

  Name of the service for which statistic aggregation is enabled

  module_name

  Name of the MODULE. An additional qualifier for the service. It is a required parameter.

  action_name

  Name of the ACTION. An additional qualifier for the Service and MODULE name. Omitting the parameter (or supplying ALL_ACTIONS constant) means enabling

  aggregation for all Actions for a given Server/Module combination. In this case, statistics are aggregated on the module level.

  Reference: Oracle Database PL/SQL Packages and Types Reference 11g, SERV_MOD_ACT_STAT_ENABLE Procedure

• Question 40:

  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

  Correct Answer: C

  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.