It is more faster and easier to pass the Oracle 1Z0-117 exam by using Exact Oracle Oracle Database 11g Release 2: SQL Tuning Exam questuins and answers. Immediate access to the Up to the immediate present 1Z0-117 Exam and find the same core area 1Z0-117 questions with professionally verified answers, then PASS your exam with a high score now.

2016 Nov 1Z0-117 latest exam

Q61. Examine the following anonymous PL/SQL code block of code: 

Which two are true concerning the use of this code? 

A. The user executing the anonymous PL/SQL code must have the CREATE JOB system privilege. 

B. ALTER SESSION ENABLE PARALLEL DML must be executed in the session prior to executing the anonymous PL/SQL code. 

C. All chunks are committed together once all tasks updating all chunks are finished. 

D. The user executing the anonymous PL/SQL code requires execute privilege on the DBMS_JOB package. 

E. The user executing the anonymous PL/SQL code requires privilege on the DBMS_SCHEDULER package. 

F. Each chunk will be committed independently as soon as the task updating that chunk is finished. 

Answer: A,E 

Explanation: A (not D, not E): 

To use DBMS_PARALLEL_EXECUTE to run tasks in parallel, your schema will need the 

CREATE JOB system privilege. 

E (not C): DBMS_PARALLEL_EXECUTE now provides the ability to break up a large table 

according to a variety of criteria, from ROWID ranges to key values and user-defined 

methods. You can then run a SQL statement or a PL/SQL block against these different 

“chunks” of the table in parallel, using the database scheduler to manage the processes 

running in the background. Error logging, automatic retries, and commits are integrated into 

the processing of these chunks. 

Note: 

*

 The DBMS_PARALLEL_EXECUTE package allows a workload associated with a base table to be broken down into smaller chunks which can be run in parallel. This process involves several distinct stages. 1.Create a task 2.Split the workload into chunks CREATE_CHUNKS_BY_ROWID CREATE_CHUNKS_BY_NUMBER_COL CREATE_CHUNKS_BY_SQL 3.Run the task RUN_TASK User-defined framework Task control 4.Check the task status 5.Drop the task 

*

 The workload is associated with a base table, which can be split into subsets or chunks of rows. There are three methods of splitting the workload into chunks. 

CREATE_CHUNKS_BY_ROWID CREATE_CHUNKS_BY_NUMBER_COL CREATE_CHUNKS_BY_SQL The chunks associated with a task can be dropped using the DROP_CHUNKS procedure. 

*

 CREATE_CHUNKS_BY_ROWID The CREATE_CHUNKS_BY_ROWID procedure splits the data by rowid into chunks specified by the CHUNK_SIZE parameter. If the BY_ROW parameter is set to TRUE, the CHUNK_SIZE refers to the number of rows, otherwise it refers to the number of blocks. 

Reference: TECHNOLOGY: PL/SQL Practices, On Working in Parallel 


Q62. An application accessing your database got the following error in response to SQL query: 

ORA-12827: insufficient parallel query slaves available 

View the parallel parameters for your instance: 

No hints are used and the session use default parallel settings. 

What four changes could you make to help avoid the error and ensure that the query executes in parallel? 

A. Set PARELLEL_DEGREE_POLICY to AUTO. 

B. Increase the value of PARELLEL_MAX_SERVERS. 

C. Increase PARELLEL_SERVERS_TARGET. 

D. Decrease PARELLEL_MIN_PERCENT. 

E. Increase PARELLEL_MIN_SERVERS. 

F. Decrease PARELLEL_MIN_TIME_THRESHOLD. 

G. Increase PARELLEL__MIN_TIME_THRESHOLD. 

Answer: A,C,D,G 

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

By default, PARALLEL_SERVER_TARGET is set lower than the maximum number of parallel server processes allowed on the system (PARALLEL_MAX_SERVERS) to ensure each parallel statement will get all of the parallel server resources required and to prevent overloading the system with parallel server processes. 

D: Note: ORA-12827: insufficient parallel query slaves available Cause: PARALLEL_MIN_PERCENT parameter was specified and fewer than minimum slaves were acquired Action: either re-execute query with lower PARALLEL_MIN_PERCENT or wait until some running queries are completed, thus freeing up slaves 

A, G: 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. 


Q63. You are working on a database that supports an OLTP workload. You see a large number of hard parses occurring and several almost identical SQL statements in the library cache that vary only in the literal values in the WHERE clause conditions. 

Which two methods can you use to reduce hard parsing? 

A. Replace literals with bind variables and evolve a baseline for the statement. 

B. Use the RESULT_CACHE hint in the queries. 

C. Create baselines for the almost identical SQL statement by manually loading them from the cursor cache. 

D. Set the CURSOR_SHARING parameter to SIMILAR. 

Answer: A,D 

Explanation: A: We can reduce this Hard parsing by using bindvariables 

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

Note: A hard parse is when your SQL must be re-loaded into the shared pool. A hard parse is worse than a soft parse because of the overhead involved in shared pool RAM allocation and memory management. Once loaded, the SQL must then be completely re-checked for syntax & semantics and an executable generated. Excessive hard parsing can occur when your shared_pool_size is too small (and reentrant SQL is paged out), or when you have non-reusable SQL statements without host variables. See the cursor_sharing parameter for a easy way to make SQL reentrant and remember that you should always use host variables in you SQL so that they can be reentrant. 

Reference: Oracle Database Reference, CURSOR_SHARING 


Q64. Examine the parallel parameters for your instance. 

All parallel execution servers are available and the session use default parallelism settings. 

In which two cases will the degree of parallelism be automatically calculated? 

A. Statements accessing tables whom dictionary DOP is 2 or more 

B. Statements accessing tables whose dictionary DOP is DEFAULT 

C. Statements that are estimated to execute for more than 10 seconds serially 

D. Statements accessing tables with any setting for Dictionary DOP 

E. Statements with parallel hints 

Answer: B,E 

Explanation: PARALLEL_DEGREE_POLICY specifies whether or not automatic degree of Parallelism, statement queuing, and in-memory parallel execution will be enabled. 

SyntaxPARALLEL_DEGREE_POLICY = { MANUAL | LIMITED | AUTO } LIMITED is used here: Enables automatic degree of parallelism for some statements but statement queuing and in-memory Parallel Execution are disabled. Automatic degree of parallelism is only applied to those statements that access tables or indexes decorated explicitly with the DEFAULT degree of parallelism using the PARALLEL clause. Statements that do not access any tables or indexes decorated with the DEFAULT degree of parallelism will retain the MANUAL behavior. 

Note: 

* In earlier versions of the Oracle Database, we had to determine the DOP more or less manually, either with a parallel hint or by setting a parallel degree with alter table. There was an automatic computation of the DOP available for the objects with dictionary DOP of default, derived from the simple formula CPU_COUNT * PARALLEL_THREADS_PER_CPU. 


Q65. Which three are benefits of In-Memory Parallel Execution? 

A. Reduction in the duplication of block images across multiple buffer caches 

B. Reduction in CPU utilization 

C. Reduction in the number of blocks accessed 

D. Reduction in physical I/O for parallel queries 

E. Ability to exploit parallel execution servers on remote instance 

Answer: A,C,D 

Explanation: 

Note: In-Memory Parallel Execution 

When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database decides if an object that is accessed using parallel execution would benefit from being cached in the SGA (also called the buffer cache). The decision to cache an object is based on a well-defined set of heuristics including the size of the object and frequency on which it is accessed. In an Oracle RAC environment, Oracle Database maps pieces of the object into each of the buffer caches on the active instances. By creating this mapping, Oracle Database automatically knows which buffer cache to access to find different parts or pieces of the object. Using this information, Oracle Database prevents multiple instances from reading the same information from disk over and over again, thus maximizing the amount of memory that can cache objects. If the size of the object is larger than the size of the buffer cache (single instance) or the size of the buffer cache multiplied by the number of active instances in an Oracle RAC cluster, then the object is read using direct-path reads. 

Reference: Oracle Database VLDB and Partitioning Guide 11g, How Parallel Execution Works 


Renewal 1Z0-117 download:

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


Q67. Your database has the OLTP_SRV service configured for an OLTP application running on a middle tier. This service is used to connect to the database by using connection pools. The application has three modules. You enabled tracing at the service by executing the following command: 

SQL exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE (‘OLTP_SRV’); 

What is the correct method of consolidating the trace files generated by the procedure? 

A. Use all trace files as input for the tkprof utility to consolidate the trace files for a module. 

B. Use one trace file at a time as input for the trcess utility and use tkprof utility to consolidate all the output files for a module. 

C. Use the trcess utility to consolidate all trace files into a single output file, which can then be processed by the tkprof utility. 

D. Use the tkprof utility to consolidate the trace files and create an output that can directly be used for diagnostic purposes. 

Answer:

Explanation: 

Note: 

* Oracle provides the trcsess command-line utility that consolidates tracing information 

based on specific criteria. 

The SQL Trace facility and TKPROF are two basic performance diagnostic tools that can 

help you monitor applications running against the Oracle Server. 

Note: SERV_MOD_ACT_TRACE_ENABLE Procedure Enables SQL tracing for a given combination of Service Name, MODULE and ACTION globally unless an instance_name is specified 

Reference: Oracle Database Performance Tuning Guide 


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

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. 


Q69. Which type of SQL statement would be selected for tuning by the automatic SQL framework? 

A. Serial queries that are among the costliest in any or all of the four categories: the past week, any day in the past week, any hour in the past week, or single response, and have the potential for improvement 

B. Serial queries that have been tuned within the last 30days and have been SQL profiled by the SQL tuning Advisor. 

C. Serial and parallel queries that top the AWR Top SQL in the past week only and have been SQL profiled by the SQL Tuning Advisor. 

D. Serial queries that top the AWR Top SQL in the past week only and whose poor performance can be traced to concurrency issues. 

E. Serial and parallel queries that are among the costliest in any or all of the four categories: the past week, and day in the past week, any hour in the past week, or a single response, and that can benefit from access method changes. 

Answer:

Reference: Oracle Database Performance Tuning Guide, Automatic SQL Tuning 


Q70. Which two statements about In-Memory Parallel Execution are true? 

A. It can be configured using the Database Resource Manager. 

B. It increases the number of duplicate block images in the global buffer cache. 

C. It requires setting PARALLEL_DEGREE_POLICY to LIMITED. 

D. Objects selected for In-Memory Parallel Execution have blocks mapped to specific RAC instances. 

E. It requires setting PARALLEL_DEGREE_POLICY to AUTO 

F. Objects selected for In-Memory Parallel Execution must be partitioned tables or indexes. 

Answer: D,E 

Explanation: 

D, E: In-Memory Parallel Execution 

When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database decides if an object that is accessed using parallel execution would benefit from being cached in the SGA (also called the buffer cache). The decision to cache an object is based on a well-defined set of heuristics including the size of the object and frequency on which it is accessed. In an Oracle RAC environment, Oracle Database maps pieces of the object into each of the buffer caches on the active instances. By creating this mapping, Oracle Database automatically knows which buffer cache to access to find different parts or pieces of the object. Using this information, Oracle Database prevents multiple instances from reading the same information from disk over and over again, thus maximizing the amount of memory that can cache objects. If the size of the object is larger than the size of the buffer cache (single instance) or the size of the buffer cache multiplied by the number of active instances in an Oracle RAC cluster, then the object is read using direct-path reads. 

E: PARALLEL_DEGREE_POLICY specifies whether or not automatic degree of Parallelism, statement queuing, and in-memory parallel execution will be enabled. 

AUTO Enables automatic degree of parallelism, statement queuing, and in-memory parallel execution. 

Incorrect: 

C: LIMITED Enables automatic degree of parallelism for some statements but statement queuing and in-memory Parallel Execution are disabled. Automatic degree of parallelism is only applied to those statements that access tables or indexes decorated explicitly with the PARALLEL clause. Tables and indexes that have a degree of parallelism specified will use that degree of parallelism. 

Reference: Oracle Database VLDB and Partitioning Guide 11g, How Parallel Execution Works