Oracle Oracle Certifications 1Z0-117 Questions & Answers

• Question 51:

  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

  Correct Answer: A

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

  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.

  Correct Answer: A

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

  Which three statements are true the Automatic Tuning Optimizer (ATO)?

  A. It identifies the objects with stale or missing statistics and gathers statistics automatically.

  B. It investigates the effect of new or modified indexes on the access paths for a workload and recommends running that statistics through the SQL Access Advisor.

  C. It recommends a SQL profile to help create a better execution plan.

  D. It picks up resource-intensive SQL statements from the ADDM and recommends the use of materialized views to improve query performance.

  E. It identifies the syntactic, semantic, or design problems with structure of SQL statements leading to poor performance and suggests restricting the statements.

  F. It identifies resource-intensive SQL statements, runs them through the SQL Tuning Advisor, and implements the recommendations automatically.

  Correct Answer: ADF

  Under tuning mode, the optimizer can take several minutes to tune a single statement. It is both time and resource intensive to invoke Automatic Tuning Optimizer every time a query must be hard-parsed. Automatic Tuning Optimizer is meant for complex and high-load SQL statements that have nontrivial impact on the database.

  Automatic Database Diagnostic Monitor (ADDM) proactively identifies high-load SQL state- ments that are good candidates for SQL tuning. The automatic SQL tuning feature also automati- cally identifies problematic SQL statements and implements tuning recommendations during system maintenance windows as an automated maintenance task.

  The Automatic Tuning Optimizer performs the following types of tuning analysis:

  Statistics Analysis SQL Profiling Access Path Analysis SQL Structure Analysis Alternative Plan Analysis

  Note:

  * Oracle Database uses the optimizer to generate the execution plans for submitted SQL state- ments. The optimizer operates in the following modes:

  Normal mode The optimizer compiles the SQL and generates an execution plan. The normal mode generates a reasonable plan for the vast majority of SQL statements. Under normal mode, the optimizer op- erates with very strict time constraints, usually a fraction of a second.

  Tuning mode

  The optimizer performs additional analysis to check whether it can further improve the plan pro- duced in normal mode. The optimizer output is not an execution plan, but a series of actions, along with their rationale and expected benefit for producing a significantly better plan. When running in tuning mode, the optimizer is known as the Automatic Tuning Optimizer.

• Question 54:

  The following parameter values are set for the instance:

  OPTIMIZER_CAPTURE_SQL_BASELINE = FALSE OPTIMIZER_USESQL_PLAN_BASELINE = TRUE

  The SQL plan baseline for a SQL statement contains an accepted plan.

  You want to add a new plan automatically as an accepted plan to the existing SQL plan baseline.

  Examine the following tasks.

  1.

  Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE parameter to TRUE.

  2.

  Evolve the new plan.

  3.

  Fix the existing accepted plan.

  4.

  Manually load the new plan.

  Identify the task(s) that must be performed to accomplish this.

  A. 1, 2, and 3

  B. 4 and 3

  C. 1, 4, and 3

  D. Only 4

  E. 1, 2, 4, and 3

  F. 1 and 2

  Correct Answer: D

  Manual Plan Loading Manual plan loading can be used in conjunction with, or as an alternative to automatic plan capture. The load operations are performed using the DBMS_SPM package, which allows SQL plan baselines to be loaded from SQL tuning sets or from specific SQL statements in the cursor cache. Manually loaded statements are flagged as accepted by default. If a SQL plan baseline is present for a SQL statement, the plan is added to the baseline, otherwise a new baseline is created.

  Note:

  *

  The value of the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter, whose default value is FALSE, determines if the system should automatically capture SQL plan baselines. When set to TRUE, the system records a plan history for SQL statements. The first plan for a specific statement is automatically flagged as accepted. Alternative plans generated after this point are not used until it is verified they do not cause performance degradations. Plans with acceptable performance are added to the SQL plan baseline during the evolution phase.

  *

  Managing SQL plan baselines involves three phases:

  Capturing SQL Plan Baselines Selecting SQL Plan Baselines Evolving SQL Plan Baselines

  *

  Evolving SQL Plan Baselines

  Evolving a SQL plan baseline is the process by which the optimizer determines if non-accepted plans in the baseline should be accepted. As mentioned

  previously, manually loaded plans are automatically marked as accepted, so manual loading forces the evolving process. When plans are loaded automatically,

  the baselines are evolved using the

  EVOLVE_SQL_PLAN_BASELINE function, which returns a CLOB reporting its results.

  Reference: SQL Plan Management in Oracle Database 11g Release 1

• Question 55:

  Which two statements are true about the trcsess utility?

  A. It merges multiple trace files and produces a formatted output file.

  B. It merges multiple trace files from a particular session into one single trace file.

  C. It produces multiple files only for DBA sessions, which can be consolidated into one formatted file using the tkprof utility.

  D. It produces multiple files for a service, which can be consolidated into one formatted file using the tkprof utility.

  E. It merges files pertaining to a user session scattered across different processes in a shared server configuration.

  Correct Answer: AB

  The trcsess utility consolidates trace output from selected trace files based on several criteria:

  Session ID Client ID Service name Action name Module name After trcsess merges the trace information into a single output file, the output file could be processed by TKPROF.

  Note:

  *

  trcsess is useful for consolidating the tracing of a particular session for performance or debugging purposes. Tracing a specific session is usually not a problem in the dedicated server model as a single dedicated process serves a session during its lifetime. You can see the trace information for the session from the trace file belonging to the dedicated server serving it. However, in a shared server configuration a user session is serviced by different processes from time to time. The trace pertaining to the user session is scattered across different trace files belonging to different processes. This makes it difficult to get a complete picture of the life cycle of a session.

  Reference: Oracle Database Performance Tuning Guide 11g , Using the trcsess Utility

  *

  Now there is a new tool, a command line utility called trcsess to help read the trace files. The trcsess command-line utility consolidates trace information from selected trace files, based on specified criteria. The criteria include session id, client id, service name, action name and module name.

• Question 56:

  You ran a high load SQL statement that used an index through the SQL Tuning Advisor and accepted its recommendation for SQL profile creation. Subsequently you noticed that there has been a 2% growth in number of rows in the tables used by the SQL statement and database statistics have also been refreshed. How does this impact the created SQL profile?

  A. It becomes invalid and no longer used the optimizer.

  B. It remains valid and ensures that the optimizer always use the execution plan that was created before the changes happened.

  C. It remains and allows the optimizer to pick a different plan required.

  D. It becomes invalid and a new SQL profile is created for the statement by the auto tuning task.

  Correct Answer: C

• Question 57:

  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.

  Correct Answer: BCE

  C: As per ALTER TABLE employees PARALLEL 2;

  Incorrect:

  not D: As per ALTER TABLE departments NOPARALLEL;

• Question 58:

  View the exhibit and examine the findings provided by the SQL Tuning Advisor for SELECT Statement.

  

  A SWL plan baseline already exists for the execution plan.

  What two methods can you use to ensure that an alternate plan becomes an accepted plan?

  A. Use the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function.

  B. Use the DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE function.

  C. Use the DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE function and run the DBMS_STATS to manually refresh stale statistics.

  D. Use the DBMS_SPM.LOAD_PLANS_FROM_SQLSET function.

  Correct Answer: CD

  C: To adopt an alternative plan regardless of whether SQL Tuning Advisor recommends it, call DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE. You can use this procedure to create a SQL plan baseline on any existing reproducible plan.

  D: LOAD_PLANS_FROM_SQLSET Function

  This function loads plans stored in a SQL tuning set (STS) into SQL plan baselines. The plans loaded from STS are not verified for performance but added as

  accepted plans to existing or new SQL plan baselines. This procedure can be used to seed SQL management base with new SQL plan baselines.

  Note:

  * While tuning a SQL statement, SQL Tuning Advisor searches real-time and historical performance data for alternative execution plans for the statement. If plans

  other than the original plan exist, then SQL Tuning Advisor reports an alternative plan finding.

  SQL Tuning Advisor validates the alternative execution plans and notes any plans that are not reproducible. When reproducible alternative plans are found, you

  can create a SQL plan baseline to instruct the optimizer to choose these plans in the future.

  Incorrect:

  Not A: ALTER_SQL_PLAN_BASELINE Function

  This function changes an attribute of a single plan or all plans associated with a SQL statement using the attribute name/value format.

  Usage Notes

  When a single plan is specified, one of various statuses, or plan name, or description can be altered. When all plans for a SQL statement are specified, one of

  various statuses, or description can be altered. This function can be called numerous times, each time setting a different plan attribute of same plan(s) or different

  plan(s).

  Reference: Oracle Database Performance Tuning Guide, Alternative Plan Analysis

• Question 59:

  An application issues many expensive join aggregations type queries.

  Examine the Exhibit to view the queries.

  

  Which two could help improve the performance of these SQL statements without changing application code?

  A. Create B*-Tree indexes on the join columns.

  B. Create a materialized view with query rewrite enabled for the first statement and nested MVIEWs for the other statements.

  C. Collect histogram statistics on columns for which aggregating functions are performed.

  D. Create an STS for these queries and use SQL Access Advisor, which may generate advice about MVIEWs.

  E. Create an STS for these queries and use SQL Performance Analyzer, which may generate advice about MVIEWs.

  Correct Answer: BD

  B: Materialized views and indexes are essential when tuning a database to achieve optimum performance for complex, data-intensive queries.

  D:

  *

  STS SQL tuning set.

  *

  A SQL Tuning Set is a database object that includes one or more SQL statements and their execution statistics and execution context. You can use the set as an

  input source for various advisors, such as SQL Tuning Advisor, SQL Access Advisor, and SQL Performance Analyzer.

  *

  SQL Access Advisor:

  Materialized views and indexes are essential when tuning a database to achieve optimum performance for complex, data-intensive queries. The SQL Access

  Advisor helps you achieve your performance goals by recommending the proper set of materialized views, materialized view logs, and indexes for a given

  workload. Understanding and using these structures is essential when optimizing SQL as they can result in significant performance improvements in data retrieval.

  The advantages, however, do not come without a cost. Creation and maintenance of these objects can be time consuming, and space requirements can be

  significant.

  The SQL Access Advisor recommends bitmap, function-based, and B-tree indexes. A bitmap index offers a reduced response time for many types of ad hoc queries and reduced storage requirements compared to other indexing techniques. B-tree indexes are most commonly used in a data warehouse to index unique or near-unique keys.

  Note:

  *

  Conventional wisdom holds that bitmap indexes are most appropriate for columns having low distinct values--such as GENDER, MARITAL_STATUS, and RELATION. This assumption is not completely accurate, however. In reality, a bitmap index is always advisable for systems in which data is not frequently updated by many concurrent systems. A bitmap index on a column with 100-percent unique values (a column candidate for primary key) is as efficient as a B-tree index.

  *

  By default, the Oracle creates a b_tree index. In a b-tree, you walk the branches until you get to the node that has the data you want to use. In the classic b-tree structure, there are branches from the top that lead to leaf nodes that contain the data.

  Incorrect:

  not E: SQL Performance Analyzer enables you to assess the performance impact of any system change resulting in changes to SQL execution plans and

  performance characteristics. Examples of common system changes for which you can use SQL Performance Analyzer include:

  Database upgrade Configuration changes to the operating system, hardware, or database Database initialization parameter changes Schema changes, for example, adding new indexes or materialized views Gathering optimizer statistics Validating SQL tuning actions, for example, creating SQL profiles or implementing partitioning

• Question 60:

  See the table below:

  

  All parallel execution servers are available and sessions use default settings for parallelism.

  Which three are true about parallel execution in your instance?

  A. Parallel execution occurs when estimated serial execution time exceeds the minimum time threshold.

  B. Parallel execution occurs for all DML statements.

  C. Parallel execution occurs for those statements that access tables with dictionary DOP defined.

  D. Parallel execution occurs for those statements that access tables with no dictionary DOP defined.

  E. Parallel execution occurs for all DDL statements.

  Correct Answer: ACD

  A (not B, Not E): PARALLEL_MIN_TIME_THRESHOLD : Oracle 11gR2 will ascertain if the query's estimated execution time is likely to run longer than the

  acceptable value (in seconds) for pARALLEL_MIN_TIME_THRESHOLD and, if sufficient resources for parallel execution exist right now, it will allow the query to

  execute; otherwise, it will delay its execution until sufficient resources exist. This helps prevent a single parallel query from consuming excessive resources at the

  cost of other non-parallelizable operations. The default of this parameter is 10 seconds. C, D: 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. If there were insufficient parallel

  servers to satisfy the requested DOP, one of three things could occur:

  / The SQL would be run at a reduced DOP (be downgraded)

  / The SQL would run in serial mode (be serialized)

  / If PARALLEL_MIN_PERCENT was specified and less than the nominated percentage of the DOP was achievable, then the the SQL statement might terminate

  with "ORA-12827:

  insufficient parallel query slaves available".

  Note:

  *

  PARALLEL_DEGREE_POLICY. It can have 3 values : MANUAL, LIMITED and AUTO

  . MANUAL - This is the default. Disables Auto DOP, statement queuing and in-memory parallel execution. It reverts the behavior of parallel execution to what it was previous to Oracle Database 11g, Release 2 (11.2).

  *

  Oracle supports parallel processing for a wide range of operations, including queries, DDL and DML:

  · Queries that involve table or index range scans.

  · Bulk insert, update or delete operations.

  · Table and index creation.

  *

  Oracle's parallel execution framework enables you to either explicitly chose - or even enforce - a specific degree of parallelism (DOP) or to rely on Oracle to

  control it.

  *

  Three modes are available to request a DOP :

  default fixed adaptive

  *

  The DOP is determined in the following priority order:

  hint session table and limited by the Oracle Database Resource Manager (DBRM) settings.

  Reference: AUTOMATIC DEGREE OF PARALLELISM (DOP) IN ORACLE 11G R2