Cause all that matters here is passing the Oracle 1Z0-117 exam. Cause all that you need is a high score of 1Z0-117 Oracle Database 11g Release 2: SQL Tuning Exam exam. The only one thing you need to do is downloading Pass4sure 1Z0-117 exam study guides now. We will not let you down with our money-back guarantee.
2016 Aug 1Z0-117 exams
Q31. Refer to the Exhibit. Execution plan:
What must be the correct order of steps that the optimizer executes based on the ID column the execution plan?
A. 3, 5, 4, 6, 7
B. 3, 5, 4, 7, 6
C. 3, 4, 5, 7, 6
D. 4, 5, 3, 7, 6
Q32. Examine the parallelism parameters for your instance:
What are true about the execution of the query?
A. It will execute in parallel only if the LINEITEM table has a dictionary DOP defined.
B. DOP for the statement is determined by the dictionary DOP of the accessed objects.
C. It is generated to execute in parallel.
D. It will execute in parallel only if the estimated execution time is 10 or more seconds.
E. DOP for the statement is calculated automatically.
F. It may execute serially.
F (not C): It may execute serially. See note below.
A, B: Dictionary DOP not used with PARALLEL (AUTO) hint.
D: The default value of parallel_min_time_threshold is 30 (not 10) seconds.
* parallel_min_percent 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.
Q33. 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.
Q34. Your database supports a workload consisting of three categories of SQL statements:
Statements that should execute in less than one minute
Statement that may execute for up to 15 minutes
Statements that may be executed for more than 15 minutes
You set PARALLEL_DEGREE_POLICY to Auto.
You plan to prioritize queued statements by using the Database Resource manager.
Which two are true about parallelism prioritization by a consumer group?
A. PARALLEL_TARGET_PERCENTAGE is used to prioritize a consumer group’s use of the overall PARALLEL_SERVER_TARGET.
B. Queuing is done for a consumer group exceeding its percentage, even if the number of busy PX servers in the instance has not reached PARALLEL_SERVERS_TARGET.
C. PARALLEL_TARGET_PERCENTAGE us used to prioritize a consumer group’s use of the overall SPAN CLASS = ‘OracleCode’> PARALLEL_MAX_SERVERS.
D. Having separate queues for consumer groups requires the use of management attributes (MGMT_P1, MGMT_P2 etc. . . )
E. Separate queue timeout using PARALLEL_QUEUE_TIMEOUT require the use of management attributes (MGMT_P1, MGMT_P2 etc . . . groups)
Explanation: A: Parallel Target Percentage
It is possible for a single consumer group to launch enough parallel statements to use all the available parallel servers. If this happens, when a high-priority parallel statement from a different consumer group is run, no parallel servers are available to allocate to this group. You can avoid such a scenario by limiting the number of parallel servers that can be used by a particular consumer group. Use the PARALLEL_TARGET_PERCENTAGE directive attribute to specify the maximum percentage of the parallel server pool that a particular consumer group can use. The number of parallel servers used by a particular consumer group is counted as the sum of the parallel servers used by all sessions in that consumer group.
Incorrect: Not B: 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. Not C: Would be true if we replace PARALLEL_MAX_SERVERS with PARALLEL_SERVER_TARGET. Not E: The PARALLEL_QUEUE_TIMEOUT directive attribute enables you to specify the maximum time, in seconds, that a parallel statement can wait in the parallel statement queue before it is timed out. The PARALLEL_QUEUE_TIMEOUT attribute can be set for each consumer group. This attribute is applicable even if you do not specify other management attributes (mgmt_p1, mgmt_p2, and so on) in your resource plan. Note:
PARALLEL_DEGREE_POLICY AUTO Enables automatic degree of parallelism, statement queuing, and in-memory parallel execution.
The PARALLEL_TARGET_PERCENTAGE attribute enables you to specify when parallel statements from a consumer group can be queued. Oracle Database maintains a separate parallel statement queue for each consumer group.
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.
Note that all serial (non-parallel) statements will execute immediately even if statement queuing has been activated.
* Oracle Database Resource Manager (the Resource Manager) enables you to optimize resource allocation among the many concurrent database sessions. The elements of the Resource Manager are: / Resource consumer groupA group of sessions that are grouped together based on resource requirements. The Resource Manager allocates resources to resource consumer groups, not to individual sessions. / Resource planA container for directives that specify how resources are allocated to resource consumer groups. You specify how the database allocates resources by activating a specific resource plan.
/ Resource plan directiveAssociates a resource consumer group with a particular plan and specifies how resources are to be allocated to that resource consumer group.
Q35. 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.
Reference: Database Performance Tuning Guide and Reference
Far out 1Z0-117 exam answers:
Q36. You are logged in as the HR user and you execute the following procedure:
SQL > exec DBMS_STATS.SET_TABLE_PREFS (‘HR’, ‘EMPLOYEES’, ‘PUBLISH’, ‘FALSE’);
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (‘HR’, ‘EMPLOYEES’);
Which statement is true about the newly gathered statistics?
A. They are temporary and purged when the session exits.
B. They are used by the optimizer for all sessions.
C. They are locked and cannot be overwritten.
D. They are marked as pending and stored in the pending statistics table.
Explanation: In previous database versions, new optimizer statistics were automatically published when they were gathered. In 11g this is still the default action, but you now have the option of keeping the newly gathered statistics in a pending state until you choose to publish them.
The DBMS_STATS.GET_PREFS function allows you to check the 'PUBLISH' attribute to see if statistics are automatically published. The default value of TRUE means they are automatically published, while FALSE indicates they are held in a pending state.
Reference: Statistics Collection Enhancements in Oracle Database 11g Release 1, Pending Statistics
Q37. Which two statements are true about the trcsess utility?
A. It merges multiple trace files and produces a formatted output file.
B. It merges multiple trace files from a particular session into one single trace file.
C. It produces multiple files only for DBA sessions, which can be consolidated into one formatted file using the tkprof utility.
D. It produces multiple files for a service, which can be consolidated into one formatted file using the tkprof utility.
E. It merges files pertaining to a user session scattered across different processes in a shared server configuration.
Explanation: The trcsess utility consolidates trace output from selected trace files based on several criteria:
Session ID Client ID Service name Action name Module name After trcsess merges the trace information into a single output file, the output file could be processed by TKPROF.
trcsess is useful for consolidating the tracing of a particular session for performance or debugging purposes. Tracing a specific session is usually not a problem in the dedicated server model as a single dedicated process serves a session during its lifetime. You can see the trace information for the session from the trace file belonging to the dedicated server serving it. However, in a shared server configuration a user session is serviced by different processes from time to time. The trace pertaining to the user session is scattered across different trace files belonging to different processes. This makes it difficult to get a complete picture of the life cycle of a session.
Now there is a new tool, a command line utility called trcsess to help read the trace files.
Reference: Oracle Database Performance Tuning Guide 11g , Using the trcsess Utility The trcsess command-line utility consolidates trace information from selected trace files, based on specified criteria. The criteria include session id, client id, service name, action name and module name.
Q38. 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.
CURSOR_SHARING determines what kind of SQL statements can share the same
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.
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.
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.
Only allows statements with identical text to share the same cursor.
Q39. You want to run SQL Tuning Advisor statements that are not captured by ADDM, AWR, and are not in the library cache.
What is the prerequisite?
A. Enable SQL plan management
B. Create a SQL plan baseline for each query
C. Create a SQL Tuning Set (STS) containing the SQL statements
D. Gather statistics for objects used in the application
Explanation: You can use an STS as input to SQL Tuning Advisor, which performs
automatic tuning of the SQL statements based on other user-specified input parameters.
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. You can load SQL statements into a SQL tuning set from different SQL sources,
such as AWR, the shared SQL area, or customized SQL provided by the user. An STS
A set of SQL statements
Associated execution context, such as user schema, application module name and action, list of bind values, and the cursor compilation environment Associated basic execution statistics, such as elapsed time, CPU time, buffer gets, disk
reads, rows processed, cursor fetches, the number of executions, the number of complete executions, optimizer cost, and the command type Associated execution plans and row source statistics for each SQL statement (optional). Reference: Oracle Database Performance Tuning Guide, Managing SQL Tuning Sets
Q40. Examine the following query and execution plan:
Which query transformation technique is used in this scenario?
A. Join predicate push-down
B. Subquery factoring
C. Subquery unnesting
D. Join conversion
Explanation: * Normally, a view cannot be joined with an index-based nested loop (i.e., index access) join, since a view, in contrast with a base table, does not have an index defined on it. A view can only be joined with other tables using three methods: hash, nested loop, and sort-merge joins.
* The following shows the types of views on which join predicate pushdown is currently supported.
UNION ALL/UNION view Outer-joined view Anti-joined view Semi-joined view DISTINCT view GROUP-BY view