Proper study guides for Renew Oracle Oracle Database 11g Release 2: SQL Tuning Exam certified begins with Oracle 1Z0-117 preparation products which designed to deliver the High value 1Z0-117 questions by making you pass the 1Z0-117 test at your first time. Try the free 1Z0-117 demo right now.
2016 Sep 1Z0-117 latest exam
Q11. 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.
Explanation: CURSOR_SHARING determines what kind of SQL statements can share the
Only allows statements with identical text to share the same cursor.
EXACT-This is the default setting. With this value in place, the query is not rewritten to use
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 .
Q12. Tracing has been enabled for the HR user. You execute the following command to check the contents of the orcl_25052.trc trace file, which was generated during tracing:
Which two statements are correct about the execution of the command?
A. SCRIPT.SQL stores the statistics for all traced SWL statements.
B. Execution plans for SQL statements are stored in TEMP_PLAN_TABLE and can be queried by the user.
C. SQL statements in the output files are stored in the order of elapsed time.
D. TKPROF use TEMP_PLAN_TABLE in the HR schema as a temporary plan table.
E. Recursive SQL statements are included in the output file.
Explanation: INSERT Creates a SQL script that stores the trace file statistics in the database. TKPROF creates this script with the name filename3. This script creates a table and inserts a row of statistics for each traced SQL statement into the table.
Q13. 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.
Explanation: 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 statements that are good candidates for SQL tuning. The automatic SQL tuning feature also automatically 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
* Oracle Database uses the optimizer to generate the execution plans for submitted SQL statements. The optimizer operates in the following modes:
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 operates with very strict time constraints, usually a fraction of a second.
The optimizer performs additional analysis to check whether it can further improve the plan produced 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.
Q14. Examine the query and its execution plan:
Which statement is true regarding the execution plan?
A. This query first fetches rows from the CUSTOMERS table that satisfy the conditions, and then the join return NULL from the CUSTOMER_ID column when it does not find any corresponding rows in the ORDERS table.
B. The query fetches rows from CUSTOMERS and ORDERS table simultaneously, and filters the rows that satisfy the conditions from the resultset.
C. The query first fetches rows from the ORDERS table that satisfy the conditions, and then the join returns NULL form CUSTOMER_ID column when it does not find any corresponding rows in the CUSTOMERS table.
D. The query first joins rows from the CUSTOMERS and ORDERS tables and returns NULL for the ORDERS table columns when it does not find any corresponding rows in the ORDERS table, and then fetches the rows that satisfy the conditions from the result set.
Q15. See the table below:
All execution servers are currently available and the sessions use defaults for all parallel settings.
In which two cases will statements execute in parallel?
A. When parallel hints are used but only if estimated serial execution takes more than 10 seconds.
B. When parallelism is defined at the statement level.
C. When the degree of parallelism is explicitly defined in the data dictionary for tables and indexes accessed by a query.
D. Parallel DDL statements but only if estimated serial DDL execution time is greater than 10 seconds.
E. When the degree of parallelism is explicitly defined for tables and indexes but only if estimated serial execution takes more than 10 seconds.
A, D, E: When PARALLEL_MIN_TIME_THRESHOLD is set to AUTO the
PARALLEL_MIN_TIME_THRESHOLD is set to 30, not to 10. See note below.
* parallel_min_time_threshold 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.
Far out 1Z0-117 book:
Q16. Examine the parallelism parameters for your instance:
Examine the Exhibit to view the query and its explain plan output.
All sessions use default parallelism settings.
What two steps could you take to make the query execute in parallel?
A. Add a parallel hint.
B. Decrease the value of PARALLEL_MIN_TIMETHRESHOLD.
C. Increase the value of PARALLEL_MIN_SERVERS.
D. Increase the value of PARALLEL_MAX_SERVERS.
E. Decrease the value of PARALLEL_MIN_PERCENT.
Explanation: A: You can rely on hints to set the degree of parallelism, but these can be hard to set correctly.
Decision tree for query parallelization
Q17. 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.
Q18. Examine Exhibit1 to view the query and its AUTOTRACE output.
Which two statements are true about tracing?
A. The displayed plan will be stored in PLAN_TABLE.
B. Subsequent execution of this statement will use the displayed plan that is stored in v$SQL.
C. The displayed plan may not necessarily be used by the optimizer.
D. The query will not fetch any rows; it will display only the execution plan and statistics.
E. The execution plan generated can be viewed from v$SQLAREA.
Explanation: The PLAN_TABLE is automatically created as a public synonym to a global temporary table. This temporary table holds the output of EXPLAIN PLAN statements for all users. PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans
Q19. Examine the parallelism parameter for your instance:
Now examine the resource plan containing parallel statement directives:
Consumer Group resource plan containing parallel statement directives:
Which two are true about parallel statement queuing when this plan is active?
A. Urgent_group sessions collectively can consume up to 64 parallel execution servers before queuing starts for this consumer group.
B. ETL_GROUP sessions can collectively consume up to 64 parallel execution servers before the queuing starts for this consumer.
C. A single OTHER_GROUPS session will execute serially once it is queued for six minutes.
D. A single ETL_GROUP session can consume up to eight parallel execution servers.
E. A single ETL_GROUP session can consume up to 32 parallel execution servers.
F. A single OTHER_GROUPS session will execute in parallel once it is queued for six minutes.
Q20. Examine the exhibit.
Which is true based on the information obtainable from the execution plan?
A. A full partition-wise join performed between the EMPLOYEES and DEPARTMENTS tables.
B. A full table scan on the DEPARTMENTS table performed serially by the query coordinator.
C. A full table scan on the DEPARTMENTS table is performed serially by a single parallel execution server process.
D. A partial partition-wise join performed between the EMPLOYEES and DEPARTMENTS tables.
E. A full table scan on the EMPLOYEES table is done in parallel.
Explanation: PX BLOCK ITERATORThis operation is typically the first step in a parallel pipeline. The BLOCK ITERATOR breaks up the table into chunks that are processed by each of the parallel servers involved.
B, C: The scan on the Departsments table is done in parallel.
* As per exhibit: Line 7 is run first, followed by line 6.
Example with same structure of execution plan:
Here’s how to read the plan:1. The first thing done is at line 9 – an index fast full scan on SYS.OBJ$.I_OBJ1 index. This is done in parallel, as indicated from the “PX SEND” line above.2. In line 8, we’re doing a “PX SEND BROADCAST” operation. When joining tables in parallel, Oracle can choose to either broadcast results (rows) from one operation to apply to the other table scan, or it can choose PX SEND HASH. In this case, our CBO determined that a BROADCOAST was appropriate because the results from the OBJ$ table were much lower than the MYOBJ table3. Line 7, the PX RECEIVE step, is basically the consumer of the broadcasted rows in step 84. Line 6 is an in-memory BUFFER SORT of the rows returned from the index scan on OBJ$5. Lines 11 and 10, respectively, indicate the full scan and PX BOCK ITERATOR operation for the granules involved in the 8 PQ servers6. In line 5, Oracle is doing a hash join on the resulting rows from the parallel scans on MYOBJ and OBJ$7. Line 4 is a per-PQ server sort of data from the joind PQ servers8. Line 3 is the consumer QC that holds the result of the each of the PQ servers9. Line 2 is the PX Coordinator (QC) collecting, or consuming the rows of the joined data10. Line 1 is the final SORT AGGREGATE line that performs the grouping function