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 Feb 1Z0-117 exam answers

Q41. Examine the Exhibit to view the structure of an indexes for the SALES table. 

The SALES table has 4594215 rows. The CUST_ID column has 2079 distinct values. 

What would you do to influence the optimizer for better selectivity? 

A. Drop bitmap index and create balanced B*Tree index on the CUST_ID column. 

B. Create a height-balanced histogram for the CUST_ID column. 

C. Gather statistics for the indexes on the SALES table. 

D. Use the ALL_ROWS hint in the query. 

Answer:

Explanation: OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance. Values: FIRST_ROWS_N - The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000). FIRST_ROWS - The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows. 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). 


Q42. Partial details of an execution plan. 

Which statement correctly describes the BITMAP AND operation? 

A. It produces a bitmap, representing dimension table rows from all dimension tables that join with qualified fact table rows. 

B. It produces a concentration of the bitmaps for all dimension tables. 

C. It produces a bitmap, representing fact table rows that do not join with qualified dimension table rows from all dimension tables. 

D. It produces a bitmap, representing fact table rows that join with qualified dimension table rows from all dimension tables. 

Answer:

Explanation: 

Example: Additional set operations will be done for the customer dimension and the product dimension. At this point in the star query processing, there are three bitmaps. Each bitmap corresponds to a separate dimension table, and each bitmap represents the set of rows of the fact table that satisfy that individual dimension's constraints. 

These three bitmaps are combined into a single bitmap using the bitmap AND operation. This final bitmap represents the set of rows in the fact table that satisfy all of the constraints on the dimension table. 

Reference: Oracle Database Data Warehousing Guide, Star Transformation with a Bitmap Index 


Q43. Which statement is true about the usage of the STAR_TRANSFORMATION hint in a query? 

A. The optimizer always uses a plan in which the transformation is used. 

B. The optimizer uses transformation only if the cost is less than a query executing without transformation. 

C. The optimizer always generates subqueries to transform a query. 

D. The optimizer always uses bitmap indexes on the primary key column for any dimension table to transform a query. 

Answer:

Explanation: 

(http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#SQLRF50 

508) 

says that "Even if the hint is specified, there is no guarantee that the transformation will 

take place". 


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

Answer:


Q45. You need to upgrade you Oracle Database 10g to 11g. You want to ensure that the same SQL plans that are currently in use in the 10g database are used in the upgraded database initially, but new, better plans are allowed subsequently. 

Steps to accomplish the task: 

1.

 Set the OPTIMIZER_USE_SQL_BASELINE and OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE to TRUE. 

2.

 Bulk load the SQL Management Base as part of an upgrade using an STS containing the plans captured in Oracle Database 10g. 

3.

 Evolve the plan baseline using the DBMS_SPM.EVOLVE_PLAN_BASELINE procedure. 

4.

 Fix the plan baseline – using the DBMS_SPM.ALTER_SQL_PLANBASELINE procedure. 

5.

 Accept new, better plans using the DBMS_SPM.ALTER_SQL_PLAN_BASELINE procedure and manually load them to the existing baseline. 

6.

 Set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to FALSE. 

Identify the required steps. 

A. 1, 3, 4, 5 

B. 1, 6, 3, 4, 5 

C. 1, 2, 3, 5 

D. 1, 2, 3, 4 

E. 1, 6, 3 

F. 1 and 2 

Answer:

Explanation: * (1) OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES In Oracle Database 11g a new feature called SQL Plan Management (SPM) has been introduced to guarantees any plan changes that do occur lead to better performance. When OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is set to TRUE (default FALSE) Oracle will automatically capture a SQL plan baseline for every repeatable SQL statement on the system. The execution plan found at parse time will be added to the SQL plan baseline as an accepted plan. 

* (2) Once you have completed the software upgrade, but before you restart the applications and allow users back on the system, you should populate SQL Plan Management (SPM) with the 10g execution plans you captured before the upgrade. Seeding SPM with the 10g execution plans ensures that the application will continue to use the same execution plans you had before the upgrade. Any new execution plans found in Oracle Database 11g will be recorded in the plan history for that statement but they will not be used. When you are ready you can evolve or verify the new plans and only implement those that perform better than the 10g plan. 

Incorrect: 

Not (3): DBMS_SPM.EVOLVE_PLAN_BASELINE is not used to evolve new plans. 

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE should be used: 

It is possible to evolve a SQL statement’s execution plan using Oracle Enterprise Manager 

or by 

running the command-line function DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE. U 

Note: 

* SQL plan management (SPM) ensures that runtime performance will never degrade due 

to the 

change of an execution plan. To guarantee this, only accepted (trusted) execution plans 

will be 

used; any plan will be tracked and evaluated at a later point in time and only accepted as 

verified 

if the new plan performs better than an accepted plan. SQL Plan Management has three 

main 

components: 

1.

 SQL plan baseline capture: 

Create SQL plan baselines that represents accepted execution plans for all relevant SQL 

statements. The SQL plan baselines are stored in a plan history inside the SQL 

Management Base in the SYSAUX tablespace. 

2.

 SQL plan baseline selection 

Ensure that only accepted execution plans are used for statements with a SQL plan 

baseline and track all new execution plans in the history for a statement as unaccepted 

plan. The plan history consists of accepted and unaccepted plans. An unaccepted plan can be unverified (newly found but not verified) or rejected (verified but not found to performant). 

3.

 SQL plan baseline evolution Evaluate all unverified execution plans for a given statement in the plan history to become either accepted or rejected 


Far out 1Z0-117 exam cost:

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. 

Answer: A,C 

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 

WHERE e.deptno=d.deptno; 

*

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


Q48. Which two types of column filtering may benefit from partition pruning? 

A. Equally operates on range-partitioned tables. 

B. In-list operators on system-partitioned tables 

C. Equality operators on system-partitioned tables 

D. Operators on range-partitioned tables E. Greater than operators on hash-partitioned tables 

Answer: A,D 

Explanation: The query optimizer can perform pruning whenever a WHERE condition can be reduced to either one of the following two cases: 

partition_column = constant 

partition_column IN (constant1, constant2, ..., constantN) 

In the first case, the optimizer simply evaluates the partitioning expression for the value given, determines which partition contains that value, and scans only this partition. In many cases, the equal sign can be replaced with another arithmetic comparison, including <, >, <=, >=, and <>. Some queries using BETWEEN in the WHERE clause can also take advantage of partition pruning. 

Note: 

*

 The core concept behind partition pruning is relatively simple, and can be described as “Do not scan partitions where there can be no matching values”. 

When the optimizer can make use of partition pruning in performing a query, execution of the query can be an order of magnitude faster than the same query against a nonpartitioned table containing the same column definitions and data. 

*

 Example: 

Suppose that you have a partitioned table t1 defined by this statement: 

CREATE TABLE t1 ( 

fname VARCHAR(50) NOT NULL, 

lname VARCHAR(50) NOT NULL, 

region_code TINYINT UNSIGNED NOT NULL, 

dob DATE NOT NULL 

PARTITION BY RANGE( region_code ) ( 

PARTITION p0 VALUES LESS THAN (64), 

PARTITION p1 VALUES LESS THAN (128), 

PARTITION p2 VALUES LESS THAN (192), 

PARTITION p3 VALUES LESS THAN MAXVALUE 

); 

Consider the case where you wish to obtain results from a query such as this one: 

SELECT fname, lname, region_code, dob 

FROM t1 WHERE region_code > 125 AND region_code < 130; It is easy to see that none of the rows which ought to be returned will be in either of the partitions p0 or p3; that is, we need to search only in partitions p1 and p2 to find matching rows. By doing so, it is possible to expend much less time and effort in finding matching rows than would be required to scan all partitions in the table. This“cutting away” of unneeded partitions is known as pruning. 


Q49. Examine the Exhibit to view the structure of and indexes for the EMPLOYEES and DEPARTMENTS tables: 

EXAMINE the SQL statement and its execution plan: 

Which two statements are correct regarding the execution plan? 

A. Step 2 is performing nested operation on JOB_ID column of the JOBS table, which is the driven table and the EMPLOYEES table is the driven table. 

B. In step 2 for every row returned by the JOBS table matching rows from the EMPLOYEES table are accessed. 

C. Step 1 is performing nested loop operation on the DEPARTMENT_ID column of the DEPARTMENTS table, which is the driven table and results returned by step 2 in the driving resultset. 

D. The performance of the query can be improved by creating bitmap index on the JOB_ID column of the EMPLOYEES table. 

E. The performance of the query can be improved by creating bitmapped index on the DEPARTMENT_ID column of the EMPLOYEES table. 

Answer: A,C 

Explanation: 

Note: 

*

 Oracle bitmap indexes are very different from standard b-tree indexes. In bitmap structures, a two-dimensional array is created with one column for every row in the table being indexed. Each column represents a distinct value within the bitmapped index. This two-dimensional array represents each value within the index multiplied by the number of rows in the table. 

At row retrieval time, Oracle decompresses the bitmap into the RAM data buffers so it can be rapidly scanned for matching values. These matching values are delivered to Oracle in the form of a Row-ID list, and these Row-ID values may directly access the required information. 

*

 The real benefit of bitmapped indexing occurs when one table includes multiple bitmapped indexes. Each individual column may have low cardinality. The creation of multiple bitmapped indexes provides a very powerful method for rapidly answering difficult SQL queries. 

*

 Oracle bitmap indexes are very different from standard b-tree indexes. In bitmap structures, a two-dimensional array is created with one column for every row in the table being indexed. Each column represents a distinct value within the bitmapped index. This two-dimensional array represents each value within the index multiplied by the number of rows in the table. 

At row retrieval time, Oracle decompresses the bitmap into the RAM data buffers so it can be rapidly scanned for matching values. These matching values are delivered to Oracle in the form of a Row-ID list, and these Row-ID values may directly access the required information. 


Q50. View the exhibit and examine the query and its execution plan from the PLAN_TABLE. 

Which statement is true about the execution? 

A. The row with the ID column having the value 0 is the first step execution plan. 

B. Rows are fetched from the indexes on the PRODUCTS table and from the SALES table using full table scan simultaneously, and then hashed into memory. 

C. Rows are fetched from the SALES table, and then a hash join operator joins with rows fetched from indexes on the PRODUCTS table. 

D. All the partitions of the SALES table are read in parallel. 

Answer: