Oracle Oracle Certifications 1Z0-117 Questions & Answers

• Question 101:

  Examine the Exhibit.

  

  Which two options are true about the execution plan and the set of statements?

  A. The query uses a partial partition-wise join.

  B. The degree of parallelism is limited to the number of partitions in the EMP_RANGE_DID table.

  C. The DEPT table id dynamically distributed based on the partition keys of the EMP_RANGE_DID table.

  D. The server process serially scans the entire DEPT table for each range partition on the EMP_RANGE_DID table.

  E. The query uses a full partition-wise join.

  Correct Answer: AD

• Question 102:

  What are three common reasons for SQL statements to perform poorly?

  A. Full table scans for queries with highly selective filters

  B. Stale or missing optimizer statistics

  C. Histograms not existing on columns with evenly distributed data

  D. High index clustering factor

  E. OPTIMIZER_MODE parameter set to ALL_ROWS for DSS workload

  Correct Answer: ABD

  D: The clustering_factor measures how synchronized an index is with the data in a table. A table with a high clustering factor is out-of-sequence with the rows and large index range scans will consume lots of I/O. Conversely, an index with a low clustering_factor is closely aligned with the table and related rows reside together of each data block, making indexes very desirable for optimal access.

  Note:

  *

  (Not C) 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.

  *

  OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance.

  all_rows

  The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).

• Question 103:

  One of your databases supports a mixed workload.

  When monitoring SQL performance, you detect many direct paths reads full table scans.

  What are the two possible causes?

  A. Histograms statistics not available

  B. Highly selective filter on indexed columns

  C. Too many sort operations performed by queries

  D. Indexes not built on filter columns

  E. Too many similar type of queries getting executed with cursor sharing disabled

  Correct Answer: BD

  Note:

  * The direct path read Oracle metric occurs during Direct Path operations when the data is asynchronously read from the database files into the PGA instead of

  into the SGA data buffer.

  Direct reads occur under these conditions:

  -

  When reading from the TEMP tablespace (a sort operation)

  -

  When reading a parallel full-table scan (parallel query factotum (slave) processes)

  -Reading a LOB segment

  * The optimizer uses a full table scan in any of the following cases:

  -Lack of Index

  -Large Amount of Data

  -Small Table

  -High Degree of Parallelism

• Question 104:

  A new application module is deployed on middle tier and is connecting to your database. You want to monitor the performance of the SQL statements generated from the application.

  To accomplish this, identify the required steps in the correct order from the steps given below:

  1.

  Use DBNMS_APPLICATION_INFO to set the name of the module

  2.

  Use DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE to enable statistics gathering for the module.

  3.

  Use DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE to enable tracing for the service

  4.

  Use the trcsess utility to consolidate the trace files generated.

  5.

  Use the tkprof utility to convert the trace files into formatted output.

  A. 1, 2, 3, 4, 5

  B. 2, 3, 1, 4, 5

  C. 3, 1, 2, 4, 5

  D. 1, 2, 4, 5

  E. 1, 3, 4, 5

  F. 2, 1, 4, 5

  Correct Answer: A

  Note:

  *

  Before tracing can be enabled, the environment must first be configured to enable gathering of statistics.

  *

  (gather statistics): DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE

  Enables statistic gathering for a given combination of Service Name, MODULE and ACTION

  *

  DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE

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

  dbms_monitor.serv_mod_act_trace_enable(

  service_name IN VARCHAR2,

  module_name IN VARCHAR2 DEFAULT ANY_MODULE,

  action_name IN VARCHAR2 DEFAULT ANY_ACTION,

  waits IN BOOLEAN DEFAULT TRUE,

  binds IN BOOLEAN DEFAULT FALSE,

  instance_name IN VARCHAR2 DEFAULT NULL,

  plan_stat IN VARCHAR2 DEFAULT NULL);

  SELECT instance_name

  FROM gv$instance;

  exec dbms_monitor.serv_mod_act_trace_enable('TESTSERV', dbms_monitor.all_modules, dbms_monitor.all_actions, TRUE, TRUE, 'orabase');

  exec dbms_monitor.serv_mod_act_trace_disable('TESTSERV', dbms_monitor.all_modules, dbms_monitor.all_actions, 'orabase');

  *

  When solving tuning problems, session traces are very useful and offer vital information. Traces are simple and straightforward for dedicated server sessions,

  but for shared server sessions, many processes are involved. 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.

  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.

  *

  Once the trace files have been consolidated (with trcsess), tkprof can be run against the consolidated trace file for reporting purposes.

• Question 105:

  You are administering a database supporting an OLTP workload. A new module was added to one of the applications recently in which you notice that the SQL statements are highly resource intensive in terms of CPU, I/O and temporary space. You created a SQL Tuning Set (STS) containing all resource-intensive SQL statements. You want to analyze the entire workload captured in the STS. You plan to run the STS through the SQL Advisor.

  Which two recommendations can you get?

  A. Combing similar indexes into a single index

  B. Implementing SQL profiles for the statements

  C. Syntactic and semantic restructuring of SQL statements

  D. Dropping unused or invalid index.

  E. Creating invisible indexes for the workload

  F. Creating composite indexes for the workload

  Correct Answer: CF

  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 , creation of new indexes (F), restructuring of the SQL statement (C), or creation of a SQL profile. You can choose to accept the recommendation to complete the tuning of the SQL statements.

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

• Question 106:

  Which two types of SQL statements will benefit from dynamic sampling?

  A. SQL statements that are executed parallel

  B. SQL statement that use a complex predicate expression when extended statistics are not available.

  C. SQL statements that are resource-intensive and have the current statistics

  D. SQL statements with highly selective filters on column that has missing index statistics

  E. Short-running SQL statements

  Correct Answer: AB

  A: he optimizer decides whether to use dynamic statistics based on several factors. For example, the database uses automatic dynamic statistics when the SQL statement uses parallel execution.

  B: One scenario where DS is used is when the statement contains a complex predicate expression and extended statistics are not available. Extended statistics were introduced in Oracle Database 11g Release 1 with the goal to help the optimizer get good quality cardinality estimates for complex predicate expressions.

  D: DS It is typically used to compensate for missing or insufficient statistics that would otherwise lead to a very bad plan.

  Reference: When the Optimizer Uses Dynamic Statistics

• Question 107:

  Exhibit

  

  Examine the following SQL statement:

  

  Examine the exhibit to view the execution plan. Which statement is true about the execution plan?

  A. The EXPLAIN PLAN generates the execution plan and stores it in c$SQL_PLAN after executing the query. Subsequent executions will use the same plan.

  B. The EXPLAIN PLAN generates the execution plan and stores it in PLAN_TABLE without executing the query. Subsequent executions will always use the same plan.

  C. The row with the ID 3 is the first step executed in the execution plan.

  D. The row with the ID 0 is the first step executed in the execution plan.

  E. The rows with the ID 3 and 4 are executed simultaneously.

  Correct Answer: E

  Note the other_tag parallel in the execution plan.

  Note:

  Within the Oracle plan_table, we see that Oracle keeps the parallelism in a column called other_tag. The other_tag column will tell you the type of parallel

  operation that is being performed within your query.

  For parallel queries, it is important to display the contents of the other_tag in the execution.

• Question 108:

  You instance has these parameter settings:

  

  Which three statements are true about these settings if no hints are used in a SQL statement?

  A. A statement estimated for more than 10 seconds always has its degree of parallelism computed automatically.

  B. A statement with a computed degree of parallelism greater than 8 will be queued for a maximum of 10 seconds.

  C. A statement that executes for more than 10 seconds always has its degree of parallelism computed automatically.

  D. A statement with a computed degree of parallelism greater than 8 will raise an error.

  E. A statement with any computed degree of parallelism will be queued if the number of busy parallel execution processes exceeds 64.

  F. A statement with a computed degree of parallelism of 20 will be queued if the number of available parallel execution processes is less 5.

  Correct Answer: CEF

  C (not A): 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.

  : PARALLEL_DEGREE_LIMIT integer

  A numeric value for this parameter specifies the maximum degree of parallelism the optimizer can choose for a SQL statement when automatic degree of parallelism is active. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED.

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

  F: PARALELL_MIN_MINPERCENT

  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.

  Note: With automatic degree of parallelism, Oracle automatically decides whether or not a statement should execute in parallel and what degree of parallelism the statement should use. The optimizer automatically determines the degree of parallelism for a statement based on the resource requirements of the statement.

  However, the optimizer will limit the degree of parallelism used to ensure parallel server processes do not flood the system. This limit is enforced by PARALLEL_DEGREE_LIMIT.

  Values:

  CPU

  IO

  integer

  A numeric value for this parameter specifies the maximum degree of parallelism the optimizer can choose for a SQL statement when automatic degree of parallelism is active. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED.

  Reference: PARALLEL_MIN_TIME_THRESHOLD PARALLEL_DEGREE_LIMIT PARALELL_MIN_MINPERCENT PARALELL_SERVERS_TARGET

• Question 109:

  When would bind peeking be done for queries that vary only in values used in the WHERE clause?

  A. When the column used in the WHERE clause has evenly distributed data and histogram exists on that column.

  B. When the column used in the WHERE clause has evenly distributed data and index exists on that column.

  C. When the column used in the WHERE clause has non uniform distribution of data, uses a bind variable, and no histogram exists for the column.

  D. When the column used in the WHERE clause has non uniform distribution of data and histogram exists for the column.

  Correct Answer: B

• Question 110:

  Which type of SQL statement would be selected for tuning by the automatic SQL framework?

  A. Serial queries that are among the costliest in any or all of the four categories: the past week, any day in the past week, any hour in the past week, or single response, and have the potential for improvement

  B. Serial queries that have been tuned within the last 30days and have been SQL profiled by the SQL tuning Advisor.

  C. Serial and parallel queries that top the AWR Top SQL in the past week only and have been SQL profiled by the SQL Tuning Advisor.

  D. Serial queries that top the AWR Top SQL in the past week only and whose poor performance can be traced to concurrency issues.

  E. Serial and parallel queries that are among the costliest in any or all of the four categories: the past week, and day in the past week, any hour in the past week, or a single response, and that can benefit from access method changes.

  Correct Answer: E

  The Automatic Tuning Optimizer is meant to be used for complex and high-load SQL statements that have non-trivial impact on the entire system. The Automatic Database Diagnostic Monitor (ADDM) proactively identifies high-load SQL statements which are good candidates for SQL tuning.

  Note:

  * When SQL statements are executed by the Oracle database, the query optimizer is used to generate the execution plans of the SQL statements. The query

  optimizer operates in two modes:

  a normal mode and a tuning mode.

  In normal mode, the optimizer compiles the SQL and generates an execution plan. The normal mode of the optimizer generates a reasonable execution plan for the vast majority of SQL statements. Under normal mode, the optimizer operates with very strict time constraints, usually a fraction of a second, during which it must find a good execution plan.

  In tuning mode, the optimizer performs additional analysis to check whether the execution plan produced under normal mode can be improved further. The output of the query optimizer is not an execution plan, but a series of actions, along with their rationale and expected benefit for producing a significantly superior plan. When running in the tuning mode, the optimizer is referred to as the Automatic Tuning Optimizer.

  Reference: Oracle Database Performance Tuning Guide, Automatic SQL Tuning