This can be quick altering strong globe any that is altering we are often on switch. Case a usual work information that lots of persons vision to obtain one particular, however, you really need to struggle along with skilled yourself to become capable individual that matches almost all previously requiems, anyone aren?¡¥t going get those vision work readily, the particular tip of which skilled yourself for you to new dimension than me turn out to be a Oracle 1Z0-117 Certificaion.
2016 Sep 1Z0-117 actual test
Q51. You are administering a database supporting an OLTP workload where the users perform frequent queries for fetching a new rows as possible, involving join operations on recently inserted data. In addition at night, a few DSS queries are also performed. Examine the initialization parameters for the instance:
Which two options would you use for the optimizer?
A. Set the OPTIMIZER_MODE initialization parameter to FIRST_ROWS_n.
B. Add the hint ALL_ROWS in the DOS queries.
C. Set the OPTIMIZER_INDEX_CACHING initialization parameter to 0.
D. Add a hint INDEX_COMBINE in all DSS queries.
E. Set the OPTIMIZER_INDEX_COST_ADJ initialization parameter to 100.
Answer: A,E
Explanation: The last appended rows are more likely to be found quickly with FIRST_ROWS_n.
E: Make it not to prioritize index instead if table scan. OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan. The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index
access path look half as expensive as normal.
Q52. You plan to bulk load data using INSERT /*+PARALLEL*/ INTO . . . . SELECT FROM statements.
Which four types of operations can execute in parallel on tables that have no bitmapped indexes or materialized views defined on term?
A. Direct path insert of a million rows into a partitioned, index-organized table containing one million rows.
B. Direct path insert of a million rows into a partitioned, index-organized table containing 10 million rows.
C. Direct path insert of a million rows into a nonpartitioned, index-organized table containing one million rows.
D. Direct path insert of a million rows into a nonpartitioned, heap-organized table containing 10 million rows.
E. Direct path insert of a million rows into a nonpartitioned, heap-organized table containing one million rows.
Answer: A,B,D,E
Explanation: Direct-path INSERT is not supported for an index-organized table (IOT) if it is not partitioned, if it has a mapping table, or if it is reference by a materialized view.
Q53. Examine the initializing parameters:
An index exists on the column used in the WHERE of a query. You execute the query for the first time today and notice that the query is not using the index. The CUSTOMERS table has 55000 rows.
View the exhibit and examine the query and its execution plan.
What can be the two reasons for full table scan?
A. The value of the OPTIMIZER_INDEX_COST_ADJ parameter is set to a low value.
B. The blocks fetched by the query are greater than the value specified by the DB_FILE_MULTIBLOCK_READ_COUNT parameter.
C. The statistics for the CUSTOMERS table and the indexes stale.
D. The OPTIMIZER_MODE parameter is set to ALL_ROWS.
E. Histogram statistics for CUST_CITY_ID are missing.
F. Average number of rows per block for the CUSTOMERS table is low.
Answer: C,E
Explanation: C: Old statistics could cause this problem. "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."
Q54. 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.
Answer: C,D
Explanation:
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
Q55. 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.
Answer: A
Explanation: 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.
Up to the minute 1Z0-117 exam cost:
Q56. Which three options are true about parallel queries when PARALLEL_DEGREE_POLICY is set to MANUAL and the session is using the default settings for parallel query, DDL, and DML?
A. A subquery in a parallel DML is parallelized only if it includes a parallel hint.
B. The number of parallel execution servers requested for a cursor is based on the greatest degree of parallelism associated with any object accessed by the cursor.
C. A SELECT statement can be executed in parallel only if no scalar subqueries are contained in the SELECT list.
D. In a CREATE TABLE . . . AS SELECT (CTAS) statement, SELECT is parallelized only if create TABLE is parallelized.
E. In an INSERT INTO . . . SELECT FROM statement, INSERT is parallelized if select is parallelized.
F. Single row inserts are never executed is parallel.
Answer: C,E,F
Explanation:
* Decision to Parallelize
A SELECT statement can be parallelized only if the following conditions are satisfied:
/ The query includes a parallel hint specification (PARALLEL or PARALLEL_INDEX) or the schema objects referred to in the query have a PARALLEL declaration associated with them.
/ At least one of the tables specified in the query requires one of the following:
A full table scan An index range scan spanning multiple partitions
/ (C) No scalar subqueries are in the SELECT list.
*
By default, the system only uses parallel execution when a parallel degree has been explicitly set on an object or if a parallel hint is specified in the SQL statement.
*
CREATE TABLE ... AS SELECT in Parallel
Parallel execution lets you parallelize the query and create operations of creating a table as a subquery from another table or set of tables. This can be extremely useful in the creation of summary or rollup tables.
Clustered tables cannot be created and populated in parallel.
* PARALLEL_DEGREE_POLICY specifies whether or not automatic degree of Parallelism, statement queuing, and in-memory parallel execution will be enabled.
MANUAL
Disables automatic degree of parallelism, statement queuing, and in-memory parallel execution. This reverts the behavior of parallel execution to what it was prior to Oracle Database 11g Release 2 (11.2). This is the default.
Incorrect:
A:
*
For parallel DML (INSERT, UPDATE, MERGE, and DELETE), the reference object that determines the DOP (degree of parallelism) is the table being modified by and insert, update, or delete operation. Parallel DML also adds some limits to the DOP to prevent deadlock. If the parallel DML statement includes a subquery, the subquery's DOP is the same as the DML operation.
*
For parallel DDL, the reference object that determines the DOP is the table, index, or partition being created, rebuilt, split, or moved. If the parallel DDL statement includes a subquery, the subquery's DOP is the same as the DDL operation.
D: The CREATE TABLE ... AS SELECT statement contains two parts: a CREATE part (DDL) and a SELECT part (query). Oracle Database can parallelize both parts of the statement.
The query part of a CREATE TABLE ... AS SELECT statement can be parallelized only if the following conditions are satisfied:
. The query includes a parallel hint specification (PARALLEL or PARALLEL_INDEX) or the CREATE part of the statement has a PARALLEL clause specification or the schema objects referred to in the query have a PARALLEL declaration associated with them.
. At least one of the tables specified in the query requires one of the following: a full table scan or an index range scan spanning multiple partitions.
Reference: Oracle Database VLDB and Partitioning Guide, Using Parallel Execution
Q57. You are administering a database that supports an OLTP workload in which one of the applications inserts rows in a table until 12 noon every, after which multiple years perform frequent queries on the table. You want the statistics to be more representative of the table population.
What must be done to ensure that an optimizer uses the latest statistics of the table?
A. Set the STALE_PERCENT preference to 0.
B. Set the OPTIMIZER_MODE parameter to ALL_ROWS.
C. Set the OPTIMIZER_DYNAMIC_SAMPLING parameter to 0.
D. Use the FIRST_ROWS_n hint in the queries.
E. Unlock and gather statistics for the table after inserts are done and lock them again.
Answer: E
Explanation: *
For tables that are substantially modified in batch operations, such as with bulk loads,
gather statistics on these tables as part of the batch operation. Call the DBMS_STATS
procedure as soon as the load operation completes.
* Statistics for a table or schema can be locked. After statistics are locked, you can make no modifications to the statistics until the statistics have been unlocked. Locking procedures are useful in a static environment in which you want to guarantee that the statistics never change. The DBMS_STATS package provides two procedures for locking (LOCK_SCHEMA_STATS and LOCK_TABLE_STATS) and two procedures for unlocking statistics (UNLOCK_SCHEMA_STATS andUNLOCK_TABLE_STATS).
Incorrect: A: STALE_PERCENT cannot be set to 0.
* With the DBMS_STATS package you can view and modify optimizer statistics gathered for database objects.
STALE_PERCENT - This value determines the percentage of rows in a table that have to change before the statistics on that table are deemed stale and should be regathered. The default value is 10%.
B: Optimizer_mode applies to the database, not to a specific table.
*
Possible values for optimizer_mode = choose/ all_rows/ first_rows/ first_rows[n]
Important facts about ALL_ROWS . ALL_ROWS considers both index scan and full scan and based on their contribution to the overall query, it uses them. If Selectivity of a column is low, optimizer may use index to fetch the data (for example ‘where employee_code=7712’), but if selectivity of column is quite high ('where deptno=10'), optimizer may consider doing Full table scan. With ALL_ROWS, optimizer has more freedom to its job at its best. . Good for OLAP system, where work happens in batches/procedures. (While some of the report may still use FIRST_ROWS depending upon the anxiety level of report reviewers) . Likes hash joins over nested loop for larger data sets.
C: Optimizer dynamic sampling refers to the ability of the SQL optimizer to take a sample of rows from a table to calculate missing statistics. Dynamic sampling can be controlled with the OPTIMIZER_DYNAMIC_SAMPLING parameter or the DYNAMIC_SAMPLING hint.
level 0 - do not use dynamic sampling
D: First_row_n cannot be used in this way.
Q58. 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.
Q59. 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?
Q60. 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.