1Z0-117 | The Refresh Guide To 1Z0-117 examcollection
Q1. 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
Answer: A,B,D
Explanation:
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).
Q2. An application supplied by a new vendor is being deployed and the SQL statements have plan baselines provided by the supplier. The plans have been loaded from a SQL tuning set. You require the optimizer to use these baselines, but allow better plans to used, should any be created.
Which two tasks would you perform to achieve this?
A. Set the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter to TRUE.
B. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to TRUE.
C. Use the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function to fix the plans.
D. Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to fix the new plans.
E. Use the DBMS_SPM.ALTER_SQL_BASELINE function to accept new plans.
Answer: A,B
Explanation: 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 B ase, then the optimizer will cost each of the baseline plans and pick one with the lowest cost.
Q3. You are administering a database that supports an OLTP application. To set statistics preferences, you issued the following command:
SQL > DBMS_STATS.SET_GLOBAL_PREFS (‘ESTIMATE_PERCENT’, ‘9’);
What will be the effect of executing this procedure?
A. It will influence the gathering of statistics for a table based on the value specified for ESTIMATE_PERCENT provided on table preferences for the same table exist.
B. It will influence dynamic sampling for a query to estimate the statistics based on ESTIMATE_PERCENT.
C. The automatic statistics gathering job running in the maintenance window will use global preferences unless table preferences for the same table exist.
D. New objects created will use global preference even if table preferences are specified.
Answer: D
Explanation:
https://blogs.oracle.com/optimizer/entry/understanding_dbms_statsset__prefs_procedures
Q4. View the code sequence: Examine the Exhibit to view the execution plan.
Which two statements are true about the query execution?
A. The optimizer joins specified tables in the order as they appear in the FROM clause.
B. The CUSTOMERS and ORDERS tables are joined first and the resultant is then joined with rows returned by the ORDER_ITEMS table.
C. The CUSTOMERS and ORDER_ITEMS tables are joined first the resultant is then joined with rows by the ORDERS table.
D. The optimizer has chosen hash join as an access method as the OPTIMIZER_MODE parameter is set to FIRST_ROWS.
Answer: C,D
Explanation: The first executed join is in line 6. The second executed join is in line 1.
Incorrect:
A: Line 7 and 8 are executed first.
Q5. 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.
Answer: D
Reference: Database Performance Tuning Guide and Reference
Q6. While tuning a SQL statement, the SQL Tuning Advisor finds an existing SQL profile for a statement that has stale statistics. Automatic optimizer statistics is enabled for the database.
What does the optimizer do in this situation?
A. Updates the existing SQL profiles for which the statistics are stale.
B. Makes the statistics information available to GATHER_DATABASE_STATS_JOB_PROC
C. Starts the statistics collection process by running GATHER_STATS_JOB
D. Writes a warning message in the alert log file
Answer: B
Explanation: Automatic optimizer statistics collection calls the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure. This internal procedure operates similarly to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option. The main difference is that GATHER_DATABASE_STATS_JOB_PROCprioritizes database objects that require statistics, so that objects that most need updated statistics are processed first, before the maintenance window closes.
Note:
* The optimizer relies on object statistics to generate execution plans. If these statistics are stale or missing, then the optimizer does not have the necessary information it needs and can generate poor execution plans. The Automatic Tuning Optimizer checks each query object for missing or stale statistics, and produces two types of output:
/ Recommendations to gather relevant statistics for objects with stale or no statistics
Because optimizer statistics are automatically collected and refreshed, this problem occurs only when automatic optimizer statistics collection is disabled. See "Managing Automatic Optimizer Statistics Collection".
/ Auxiliary statistics for objects with no statistics, and statistic adjustment factor for objects with stale statistics
The database stores this auxiliary information in an object called a SQL profile.
* Oracle recommends that you enable automatic optimizer statistics collection. In this case, the database automatically collects optimizer statistics for tables with absent or stale statistics. If fresh statistics are required for a table, then the database collects them both for the table and associated indexes.
Automatic collection eliminates many manual tasks associated with managing the optimizer. It also significantly reduces the risks of generating poor execution plans because of missing or stale statistics.
Automatic optimizer statistics collection calls the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure. This internal procedure operates similarly to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option. The main difference is that GATHER_DATABASE_STATS_JOB_PROC prioritizes database objects that require statistics, so that objects that most need updated statistics are processed first, before the maintenance window closes.
Reference: Oracle Database Performance Tuning Guide, Managing Automatic Optimizer Statistics Collection
Q7. 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.
Answer: B,D
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.
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.
Q8. 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.
Answer: B,D
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.
Q9. Which three statements are true about histograms?
A. They capture the distribution of different values in an index for better selectivity estimates.
B. They can be used only with indexed columns.
C. They provide metadata about distribution of and occurrences of values in a table column.
D. They provide improved selectivity estimates in the presence of data skew, resulting in execution plans with uniform distribution.
E. They help the optimizer in deciding whether to use an index or a full table scan.
F. They help the optimizer to determine the fastest table join order.
Answer: C,E,F
Explanation: C: A histogram is a frequency distribution (metadata) that describes the distribution of data values within a table.
E: It's well established that histograms are very useful for helping the optimizer choose between a full-scan and and index-scan.
F: Histograms may help the Oracle optimizer in deciding whether to use an index vs. a full-table scan (where index values are skewed) or help the optimizer determine the fastest table join order. For determining the best table join order, the WHERE clause of the query can be inspected along with the execution plan for the original query. If the cardinality of the table is too-high, then histograms on the most selective column in the WHERE clause will tip-off the optimizer and change the table join order. Note:
* The Oracle Query Optimizer uses histograms to predict better query plans. The ANALYZE command or DBMS_STATS package can be used to compute these histograms.
Incorrect:
B: Histograms are NOT just for indexed columns.
– Adding a histogram to an un-indexed column that is used in a where clause can improve performance.
D: Histograms Opportunities Any column used in a where clause with skewed data Columns that are not queried all the time Reduced overhead for insert, update, delete
Q10. In Your Database, The Cursor_Shareing Parameter is set to EXACT. In the Employees table, the data is significantly skewed in the DEPTNO column. The value 10 is found in 97% of rows.
Examine the following command and out put.
Which three statements are correct?
A. The DEPTNO column will become bind aware once histogram statistics are collected.
B. The value for the bind variable will considered by the optimizer to determine the execution plan.
C. The same execution plan will always be used irrespective of the bind variable value.
D. The instance collects statistics and based on the pattern of executions creates a histogram on the column containing the bind value.
E. Bind peeking will take place only for the first execution of the statement and subsequent execution will use the same plan.
Answer: A,B,D
Explanation: * We here see that the cursor is marked as bind sensitive (IS_BIND_SEN is Y).
*
In 11g, the optimizer has been enhanced to allow multiple execution plans to be used for a single statement that uses bind variables. This ensures that the best execution plan will be used depending on the bind value.
*
A cursor is marked bind sensitive if the optimizer believes the optimal plan may depend on the value of the bind variable. When a cursor is marked bind sensitive, Oracle monitors the behavior of the cursor using different bind values, to determine if a different plan for different bind values is called for.
*
(B, not C): A cursor is marked bind sensitive if the optimizer believes the optimal plan may depend on the value of the bind variable. When a cursor is marked bind sensitive, Oracle monitors the behavior of the cursor using different bind values, to determine if a different plan for different bind values is called for.
Note: Setting CURSOR_SHARING to EXACT allows SQL statements to share the SQL area only when their texts match exactly. This is the default behavior. Using this setting, similar statements cannot shared; only textually exact statements can be shared.
Reference: Why are there more cursors in 11g for my query containing bind variables?