It is impossible to pass Oracle 1Z0-117 exam without any help in the short term. Come to Examcollection soon and find the most advanced, correct and guaranteed Oracle 1Z0-117 practice questions. You will get a surprising result by our Leading Oracle Database 11g Release 2: SQL Tuning Exam practice guides.


♥♥ 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

Free Instant Download NEW 1Z0-117 Exam Dumps (PDF & VCE):
Available on: http://www.surepassexam.com/1Z0-117-exam-dumps.html

Q11. View the code sequence: 

Which three statements are true about the execution of this query? 

A. The optimizer uses an index join as an access path. 

B. The optimizer uses a B* tree access path for the EMPLOYEES table. 

C. The optimizer uses a bitmap access path for the EMPLOYEES table. 

D. The optimizer performs bitmap conversion from row IDs. 

E. The optimizer performs bitmap conversion to row IDs of the combined maps. 

F. The optimizer performs bitmap conversion from the row IDs of the combined maps. 

Answer: C,D,E 


Q12. View Exhibit1 and examine the structure and indexes for the MYSALES table. 

The application uses the MYSALES table to insert sales record. But this table is also extensively used for generating sales reports. The PROD_ID and CUST_ID columns are frequently used in the WHERE clause of the queries. These columns have few distinct values relative to the total number of rows in the table. The MYSALES table has 4.5 million 

rows. 

View exhibit 2 and examine one of the queries and its autotrace output. 

Which two methods can examine one of the queries and its autotrace output? 

A. Drop the current standard balanced B* Tree indexes on the CUST_ID and PROD_ID columns and re-create as bitmapped indexes. 

B. Use the INDEX_COMBINE hint in the query. 

C. Create a composite index involving the CUST_ID and PROD_ID columns. 

D. Rebuild the index to rearrange the index blocks to have more rows per block by decreasing the value for PCTFRE attribute. 

E. Collect histogram statistics for the CUST_ID and PROD_ID columns. 

Answer: B,C 

Explanation: B: The INDEX hint explicitly chooses an index scan for the specified table. You can use the INDEX hint for domain, B-tree, bitmap, and bitmap join indexes. However, Oracle recommends using INDEX_COMBINE rather than INDEX for the combination of multiple indexes, because it is a more versatile hint. 

C: Combining the CUST_ID and PROD_ID columns into an composite index would improve performance. 


Q13. You created a SQL Tuning Set (STS) containing resource-intensive SQL statements. You plan to run the SQL Tuning Advisor. 

Which two types of recommendations can be provided by the SQL Tuning Advisor? 

A. Semantic restructuring for each SQL statement 

B. Gathering missing or stale statistics at the schema level for the entire workload 

C. Creating a materialized view to benefit from query rewrite for the entire workload 

D. Gathering missing or stale statistics for objects used by the statements. 

E. Creating a partition table to benefit from partition pruning for each statement 

Answer: A,D 

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

Note: 

*

 A SQL Tuning Set can be used as input to the SQL Tuning Advisor, which performs automatic tuning of the SQL statements based on other input parameters specified by the user. 

*

 A SQL Tuning Set (STS) is a database object that includes one or more SQL statements along with their execution statistics and execution context, and could include a user priority ranking. The SQL statements can be loaded into a SQL Tuning Set from different SQL sources, such as the Automatic Workload Repository, the cursor cache, or custom SQL provided by the user. 

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


Q14. You are administering a database that supports an OLTP workload. Automatic optimizer statistics collection is scheduled in the night maintenance window. Some of the tables get updated frequently during day time and you notice a performance degradation for queries using those tables due to stale statistics. 

Which two options might help to avoid the performance degradation of the queries? 

A. Set the global statistics preference STALE_PERCENT to 0. 

B. Use dynamically sampling hint for the queries on frequently updated tables. 

C. Create histogram for the columns used frequently in the WHERE clause. 

D. Gather statistics with global statistics preference NO_VALIDATE to TRUE. 

E. Set the OPTIMZER_USE_PENDING_STATISTICS parameter to TRUE. 

Answer: B,C 

Explanation: B: Dynamic sampling first became available in Oracle9i Database Release 2. It is the ability of the cost-based optimizer (CBO) to sample the tables a query references during a hard parse, to determine better default statistics for unanalyzed segments, and to verify its “guesses.” This sampling takes place only at hard parse time and is used to dynamically generate better statistics for the optimizer to use, hence the name dynamic sampling. 

There are two ways to use dynamic sampling: 

The OPTIMIZER_DYNAMIC_SAMPLING parameter can be set at the database instance level and can also be overridden at the session level with the ALTER SESSION command. The DYNAMIC_SAMPLING query hint can be added to specific queries. 

C: A histogram is a collection of information about the distribution of values within a column.In some cases, the distribution of values within a column of a table will affect the optimizer's decision to use an index vs. perform a full-table scan. This scenario occurs when the value with a where clause has a disproportional amount of values, making a full-table scan cheaper than index access. Histograms are also important for determinine the optimal table join order. 

Incorrect: 

A: Too much statistics would be gathered. 

Note: 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%. 

D: In Oracle PL/SQL, the VALIDATE keyword defines the state of a constraint on a column 

in a table. 

E: OPTIMZER_USE_PENDING_STATISTICS specifies whether or not the optimizer uses 

pending statistics when compiling SQL statements. 


Q15. 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. 

Answer: C,E,F 

Explanation: 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 


Q16. 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 


Q17. View the exhibit and examine the plans in the SQL baseline for a given statement. Which interpretation is correct? 

A. A new plan cannot be evolved because SYS_SQL_bbedc41f554c408 is accepted. 

B. Plan SYS_SQL_PLAN_bbdc741f554c408 will always be used by the optimizer for the query. 

C. A new plan must be evolved using the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function before it can be used. 

D. Plan SYS_SQL_bbedc741a57b5fc2 can be used by the optimizer if the cost of the query is less than plan SYS_SQL_PLAN_bbedc741f554c408. 

E. Plan SYS_SQL_PLAN_bbedc741f554c408 will not be used until it is fixed by using the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function. 

Answer:

Explanation: 

Note: 

*

 Evolving a SQL plan baseline is the process by which the optimizer determines if non-accepted plans in the baseline should be accepted. As mentioned previously, manually loaded plans are automatically marked as accepted, so manual loading forces the evolving process. When plans are loaded automatically, the baselines are evolved using the EVOLVE_SQL_PLAN_BASELINE function, which returns a CLOB reporting its results. 

SET LONG 10000 

SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 

'SYS_SQL_7b76323ad90440b9') 

FROM dual; 

* Manual plan loading can be used in conjunction with, or as an alternative to automatic plan capture. The load operations are performed using the DBMS_SPM package, which allows SQL plan baselines to be loaded from SQL tuning sets or from specific SQL statements in the cursor cache. Manually loaded statements are flagged as accepted by default. If a SQL plan baseline is present for a SQL statement, the plan is added to the baseline, otherwise a new baseline is created. 

* fixed (YES/NO) : If YES, the SQL plan baseline will not evolve over time. Fixed plans are used in preference to non-fixed plans. 


Q18. Examine the initialization parameters for a database an OLTP overload. 

What is the effect of changing the value of the parameter? 

A. It influences the optimizer to always use the value of the parameter? 

B. It influences the optimizer to use indexes instead of full table scans as the estimated cost of the using index is reduced. 

C. It influences the optimizer to use full table scans instead of index scans as the estimated cost of full table scan is reduced. 

D. It influenced the optimizer to use bitmap indexes as the estimated cost conversion from bimap is rowed us reduced. 

Answer:

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


Q19. Exhibit A table has three distinct values in its ID column. In the ID column, values 10 and 20 have more than 20000 rows each and value 30 has only five rows. The statistics for the schema have been updated recently. 

The CURSOR_SHARING parameter is set to EXACT. 

The query was executed recently and the cursor for the query is bind aware. Examine the exhibits to view the commands executed. 

You plan to execute the same query with a value of 30 for the bind variable V_ID. 

Which statement is true in this scenario? 

A. The same execution plan will always be used irrespective of the value in the bind variable. 

B. A new execution plan will be generated depending on the access pattern and the bind value. 

C. Adaptive cursor sharing will ensure that a new cursor is generated for each distinct value in the bind variable. 

D. Adaptive cursor sharing will happen only if you use the literal values instead of bind variables in the query. 

Answer:

Explanation: 

Note: 

*

 CURSOR_SHARING determines what kind of SQL statements can share the same 

cursors. 

*

 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. 

*

 Values: 

*

 FORCE 

Forces statements that may differ in some literals, but are otherwise identical, to share a 

cursor, unless the literals affect the meaning of the statement. 

*

 SIMILAR 

Causes statements that may differ in some literals, but are otherwise identical, to share a 

cursor, unless the literals affect either the meaning of the statement or the degree to which 

the plan is optimized. 

*

 EXACT 

Only allows statements with identical text to share the same cursor. 


Q20. Which three are tasks performed in the hard parse stage of a SQL statement executions? 

A. Semantics of the SQL statement are checked. 

B. The library cache is checked to find whether an existing statement has the same hash value. 

C. The syntax of the SQL statement is checked. 

D. Information about location, size, and data type is defined, which is required to store fetched values in variables. 

E. Locks are acquired on the required objects. 

Answer: B,D,E 

Explanation: Parse operations fall into the following categories, depending on the type of 

statement submitted and the result of the hash check: 

A) Hard parse 

If Oracle Database cannot reuse existing code, then it must build a new executable version 

of the application code. This operation is known as a hard parse, or a library cache miss. 

The database always perform a hard parse of DDL. 

During the hard parse, the database accesses the library cache and data dictionary cache 

numerous times to check the data dictionary. When the database accesses these areas, it 

uses a serialization device called a latch on required objects so that their definition does 

not change (see "Latches"). Latch contention increases statement execution time and 

decreases concurrency. 

B) Soft parse 

A soft parse is any parse that is not a hard parse. If the submitted statement is the same as 

a reusable SQL statement in the shared pool, then Oracle Database reuses the existing 

code. This reuse of code is also called a library cache hit. 

Soft parses can vary in the amount of work they perform. For example, configuring the 

session cursor cache can sometimes reduce the amount of latching in the soft parses, 

making them "softer." 

In general, a soft parse is preferable to a hard parse because the database skips the 

optimization and row source generation steps, proceeding straight to execution. 

Incorrect: 

A, C: During the parse call, the database performs the following checks: 

Syntax Check 

Semantic Check 

Shared Pool Check 

The hard parse is within Shared Pool check. 

Reference: Oracle Database Concepts 11g, SQL Parsing