Exam Code: 1Z0-117 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Oracle Database 11g Release 2: SQL Tuning Exam
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass 1Z0-117 Exam.
♥♥ 2017 NEW RECOMMEND ♥♥
Free VCE & PDF File for Oracle 1Z0-117 Real Exam (Full Version!)
★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions
Q41. 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.
Explanation: 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.
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
/ 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.
Q42. You are administering database that supports an OLTP workloads. Most of the queries use an index range scan or index unique scan as access methods.
Which three scenarios can prevent the index access being used by the queries?
A. When highly selective filters is applied on an indexed column of a table with sparsely populated blocks.
B. When the rows are filtered with an IS NULL operator on the column with a unique key defined
C. When the histogram statistics are not collected for the columns used in where clause.
D. When a highly selective filter is applied on the indexed column and the index has very low value for clustering factor.
E. When the statistics for the table are not current.
A: Low clustering factor promotes good performance.
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.
Oracle SQL not using an index is a common complaint, and it’s often because the optimizer thinks that a full-scan is cheaper than index access. Oracle not using an index can be due to:
(E) Bad/incomplete statistics – Make sure to re-analyze the table and index with dbms_stats to ensure that the optimizer has good metadata.
Wrong optimizer_mode – The first_rows optimizer mode is to minimize response time, and it is more likely to use an index than the default all_rows mode.
Bugs – See these important notes on optimizer changes in 10g that cause Oracle not to use an index.
Cost adjustment – In some cases, the optimizer will still not use an index, and you must decrease optimizer_index_cost_adj.
Q43. Which three factors does the estimator depend on for overall cost estimation of a given execution plan?
B. Sort area size
C. OPTIMIZER_FEATURE_ENABLE parameter
D. NOT NULL_FEATURE_ENABLE parameter
E. NOT NULL constraint on a unique key column
F. Library cache size
G. The units of work such as disk input/output, CPU usage, and memory used in an operation
Explanation: C: OPTIMIZER_FEATURES_ENABLE acts as an umbrella parameter for enabling a series of optimizer features based on an Oracle release number.
Note: The estimator determines the overall cost of a given execution plan. The estimator generates three different types of measures to achieve this goal:
This measure represents a fraction of rows from a row set. The selectivity is tied to a query predicate, such as last_name='Smith', or a combination of predicates.
This measure represents the number of rows in a row set. 1,
This measure represents units of work or resource used. The query optimizer uses disk I/O, CPU usage, and memory usage as units of work.
If statistics are available, then the estimator uses them to compute the measures. The statistics improve the degree of accuracy of the measures.
Q44. 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.
* 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
Q45. You identified some DSS queries that perform expensive join and aggregation operations.
The queries access historical data from noncurrent partition of the fact tables.
What three actions could you perform to improve the response time of the queries without modifying the SQL statements?
A. Set the QUERY_REWRITE_ENABLED to TRUE at the session level.
B. Create an STS for the statements, run SQL Tuning Advisor for the STS, and implement any generated recommendations for materialized views.
C. Set QUERY_REWRITE_ENABLED to TRUE at the instance level.
D. Create an STS for the statements, run SQL Access Advisor for the STS, and implement any generated recommendations for materialized views.
E. Set QUERY_REWRITE_INTEGRITY to ENFORCED at the instance level.
Explanation: * QUERY_REWRITE_ENABLED allows you to enable or disable query
rewriting globally for the database.
Oracle does not use rewrite.
Oracle costs the query with rewrite and without rewrite and chooses the method with the
Oracle always uses rewrite and does not evaluate the cost before doing so. Use force
when you know that the query will always benefit from rewrite and when reduction in
compile time is important.
To take advantage of query rewrite for a particular materialized view, you must enable
query rewrite for that materialized view, and you must enable cost-based optimization.
C: You can use SQL Tuning Advisor to tune one or more SQL statements
D: Using the SQL Access Advisor Wizard or API, you can do the following: . Recommend materialized views and indexes based on collected or hypothetical
. Manage workloads.
. Mark, update, and remove recommendations.
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.
Q46. You enabled auto degree of parallelism (DOP) for your instance. Examine the query:
Which two are true about the execution of this query?
A. Dictionary DOP will be used, if present, on the tables referred in the query.
B. DOP is calculated if the calculated DOP is 1.
C. DOP is calculated automatically.
D. Calculated DOP will always by 2 or more.
E. The statement will execute with auto DOP only when PARALLEL_DEGREE_POLICY is set to AUTO.
Explanation: * PARALLEL (AUTO): The database computes the degree of parallelism (C), which can be 1 or greater (not D). If the computed degree of parallelism is 1, then the statement runs serially.
You can use the PARALLEL hint to force parallelism. It takes an optional parameter: the DOP at which the statement should run. In addition, theNO_PARALLEL hint overrides a PARALLEL parameter in the DDL that created or altered the table.
The following example illustrates computing the DOP the statement should use:
SELECT /*+ parallel(auto) */ ename, dname FROM emp e, dept d
When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database automatically decides if a statement should execute in parallel or not and what DOP it should use. Oracle Database also determines if the statement can be executed immediately or if it is queued until more system resources are available. Finally, Oracle Database decides if the statement can take advantage of the aggregated cluster memory or not.
Q47. An application user complains about statement execution taking longer than usual. You find that the query uses a bind variable in the WHERE clause as follows:
You want to view the execution plan of the query that takes into account the value in the bind variable PCAT.
Which two methods can you use to view the required execution plan?
A. Use the DBMS_XPLAN.DISPLAY function to view the execution plan.
B. Identify the SQL_ID for the statementsand use DBMS_XPLAN.DISPLAY_CURSOR for that SQL_ID to view the execution plan.
C. Identify the SQL_ID for the statement and fetch the execution plan PLAN_TABLE.
D. View the execution plan for the statement from V$SQL_PLAN.
E. Execute the statement with different bind values and set AUTOTRACE enabled for session.
Explanation: D: V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache.
B: The DBMS_XPLAN package supplies five table functions:
DISPLAY_SQL_PLAN_BASELINE - to display one or more execution plans for the SQL statement identified by SQL handle
DISPLAY - to format and display the contents of a plan table.
DISPLAY_AWR - to format and display the contents of the execution plan of a stored SQL statement in the AWR.
DISPLAY_CURSOR - to format and display the contents of the execution plan of any loaded cursor.
DISPLAY_SQLSET - to format and display the contents of the execution plan of statements stored in a SQL tuning set.
Q48. 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
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.
(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.
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).
Q49. Examine the statements being executed for the first time:
Steps followed by a SQL statement during parsing:
Search for a similar statement in the shared pool.
Search for an identical statement in the shared pool.
Search the SQL area of identical statement already in the shared pool.
Proceed through the remaining steps of the parse phase to ensure that the execution plan of the existing statements is applicable to the view statement.
Perform hard parsing.
Share the SQL area of the similar statement already in the shared pool.
Identify the required steps in the correct sequence used by the third query.
A. 5, 1, 3, 4
B. 2, 4, 3
C. 5, 2, 3, 4
D. 1, 4, 3
E. Only 5
F. 2, 5
Explanation: 2) before 5).
* When application code is run, Oracle attempts to reuse existing code if it has been executed previously and can be shared. If the parsed representation of the statement does exist in the library cache and it can be shared, then Oracle reuses the existing code. This is known as a soft parse, or a library cache hit. If Oracle is unable to use existing code, then a new executable version of the application code must be built. This is known as a hard parse, or a library cache miss.
Reference: Oracle Database Performance Tuning Guide, SQL Sharing Criteria
Q50. Examine the following query and execution plan:
Which query transformation technique is used in this scenario?
A. Join predicate push-down
B. Subquery factoring
C. Subquery unnesting
D. Join conversion
Explanation: * Normally, a view cannot be joined with an index-based nested loop (i.e., index access) join, since a view, in contrast with a base table, does not have an index defined on it. A view can only be joined with other tables using three methods: hash, nested loop, and sort-merge joins.
* The following shows the types of views on which join predicate pushdown is currently supported.
UNION ALL/UNION view Outer-joined view Anti-joined view Semi-joined view DISTINCT view GROUP-BY view