All execution servers are currently available and the sessions use defaults for all parallel settings.
In which two cases will statements execute in parallel?
A. When parallel hints are used but only if estimated serial execution takes more than 10 seconds.
B. When parallelism is defined at the statement level.
C. When the degree of parallelism is explicitly defined in the data dictionary for tables and indexes accessed by a query.
D. Parallel DDL statements but only if estimated serial DDL execution time is greater than 10 seconds.
E. When the degree of parallelism is explicitly defined for tables and indexes but only if estimated serial execution takes more than 10 seconds.
Correct Answer: BC
Incorrect:
A, D, E: When PARALLEL_MIN_TIME_THRESHOLD is set to AUTO the PARALLEL_MIN_TIME_THRESHOLD is set to 30, not to 10. See note below.
Note:
* parallel_min_time_threshold
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.
Question 42:
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.
Correct Answer: AE
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.
Question 43:
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
Correct Answer: F
*
(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
Question 44:
Which three tasks are performed by the parallel execution coordinator process?
A. Allocating parallel execution processes from the parallel execution server pool.
B. Determining the parallel execution method for each operation in the execution plan.
C. Managing the data flow between the producers and consumers during inter-operation parallelism.
D. Any serial processing that is part of the execution plan.
E. Determining the desired number of parallel execution processes
F. Managing the data flow between the producers and consumers during intra-operation parallelism.
Correct Answer: ABC
A: When executing a parallel operation, the parallel execution coordinator obtains parallel execution servers from the pool and assigns them to the operation. If necessary, Oracle Database can create additional parallel execution servers for the operation. These parallel execution servers remain with the operation throughout execution. After the statement has been processed completely, the parallel execution servers return to the pool.
B:
*
The parallel execution coordinator examines each operation in a SQL statement's execution plan then determines the way in which the rows operated on by the operation must be divided or redistributed among the parallel execution servers.
*
After the optimizer determines the execution plan of a statement, the parallel execution coordinator determines the parallel execution method for each operation in the plan.
Note:
*
Oracle Database can process a parallel operation with fewer than the requested number of processes. If all parallel execution servers in the pool are occupied and the maximum number of parallel execution servers has been started, the parallel execution coordinator switches to serial processing.
Reference: Oracle Database VLDB and Partitioning Guide 11g, How Parallel Execution Works
Question 45:
Which three statements are true about the usage of optimizer hints?
A. Whenever a query uses table aliases, the hints in the query must use the aliases.
B. The OPTIMIZER_FEATURES_ENABLE parameter must be set to a version supports the hints used.
C. The optimizer uses the execution plan with lower cost even if a hint is specified.
D. A schema name for the table must be used in the hint if the table us qualified in the FROM clause.
E. Hints can be used to override the optimization approach specified with the OPTIMIZER_MODE parameter.
F. A statement block can have only one hint, and that hint must be immediately after SELECT, UPDATE, INSERT, MERGE, or DELETE keyword.
Correct Answer: ABE
*
You must specify the table to be accessed exactly as it appears in the statement. If the statement uses an alias for the table, then use the alias rather than the table name in the hint.
*
OPTIMIZER_FEATURES_ENABLE acts as an umbrella parameter for enabling a series of optimizer features based on an Oracle release number.
For example, if you upgrade your database from release 10.1 to release 11.1, but you want to keep the release 10.1 optimizer behavior, you can do so by setting this parameter to 10.1.0. At a later time, you can try the enhancements introduced in releases up to and including release 11.1 by setting the parameter to
11.1.0.6.
* If a SQL statement has a hint specifying an optimization approach and goal, then the optimizer uses the specified approach regardless of the presence or absence of statistics, the value of the OPTIMIZER_MODE initialization parameter, and the OPTIMIZER_MODE parameter of the ALTER SESSION statement.
Question 46:
Which three factors does the estimator depend on for overall cost estimation of a given execution plan?
A. Cardinality
B. Sort area size
C. OPTIMIZER_FEATURE_ENABLE parameter
D. NOT NULL_FEATURE_ENABLE parameter
E. NOT NULL constraint on a unique key column
F. Library cache size
G. The units of work such as disk input/output, CPU usage, and memory used in an operation
Correct Answer: ACG
C: OPTIMIZER_FEATURES_ENABLE acts as an umbrella parameter for enabling a series of optimizer features based on an Oracle release number.
Note: The estimator determines the overall cost of a given execution plan. The estimator generates three different types of measures to achieve this goal:
*
Selectivity
This measure represents a fraction of rows from a row set. The selectivity is tied to a query predicate, such as last_name='Smith', or a combination of predicates.
*
Cardinality
This measure represents the number of rows in a row set. 1,
*
Cost
This measure represents units of work or resource used. The query optimizer uses disk I/O, CPU usage, and memory usage as units of work.
If statistics are available, then the estimator uses them to compute the measures. The statistics improve the degree of accuracy of the measures.
Question 47:
Examine the parallelism parameters for you instance.
Now examine the DSS_PLAN with parallel statement directives:
Which two are true about the DSS_PLAN resource manager plan?
A. URGENT_GROUPS sessions will always be dequeued before sessions from other groups.
B. OTHER_GROUPS sessions are queued for maximum of six minutes.
C. ETL_GROUP sessions can collectively consume 64 parallel execution servers before queuing starts for this consumer group.
D. An ETL_GRP sessions will be switched to URGENT_GROUPS if the session requests more than eight parallel executions servers.
E. URGENT_GROUP sessions will not be queued if 64 parallel execution servers are busy because their PARALLEL_TARGET_PERCENTAGE is not specified.
Correct Answer: AB
B: PARALLEL_QUEUE_TIMEOUT Parallel Queue Timeout
When you use parallel statement queuing, if the database does not have sufficient resources to execute a parallel statement, the statement is queued until the
required resources become available. However, there is a chance that a parallel statement may be waiting in the parallel statement queue for longer than is
desired. You can prevent such scenarios by specifying the maximum time a parallel statement can wait in the parallel statement queue.
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.
Incorrect:
Not C: ETL_GROUP PARALLEL_TARGET_PERCENTAGE is 50%. So ETL_GROUP can only consume 32 servers.
Note:
* If you want more per-workload management, you must use the following directive attributes:
/ MGMT_Pn
Management attributes control how a parallel statement is selected from the parallel statement queue for execution. You can prioritize the parallel statements of one consumer group over another by setting a higher value for the management attributes of that group.
/ PARALLEL_TARGET_PERCENTAGE
/ PARALLEL_QUEUE_TIMEOUT
/ PARALLEL_DEGREE_LIMIT_P1
* PARALLEL_DEGREE_LIMIT_P1 Degree of Parallelism Limit
You can limit the maximum degree of parallelism for any operation within a consumer group. The degree of parallelism is the number of parallel execution servers that are associated with a single operation. Use the PARALLEL_DEGREE_LIMIT_P1 directive attribute to specify the degree of parallelism for a consumer group.
The degree of parallelism limit applies to one operation within a consumer group; it does not limit the total degree of parallelism across all operations within the consumer group. However, you can combine both the PARALLEL_DEGREE_LIMIT_P1 and the PARALLEL_TARGET_PERCENTAGE directive attributes to achieve the desired control.
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.
Question 48:
You executed the following statement:
Which three statements are true about EXPLAIN PLAN?
A. The execution plan is saved in PLAN_TABLE without executing the query.
B. The execution plan for the query is generated and displayed immediately as the output.
C. The execution plan generated may not necessarily be the execution plan used during query execution.
D. The execution plan is saved in DBA_HIST_SQL_PLAN without executing the query.
E. The execution plan generated can be viewed using the DBMS_XPLAIN.DISPLAY function.
F. The execution plan generated can be fetched from the library cache by using the DBMS_XPLAIN.DISPLAY function.
Correct Answer: ACE
*
(A, not D): The explain plan process stores data in the PLAN_TABLE.
*
EXPLAIN PLAN
The EXPLAIN PLAN method doesn't require the query to be run (A), greatly reducing the time it takes to get an execution plan for long-running queries compared to AUTOTRACE.
E: Use the DBMS_XPLAN.DISPLAY function to display the execution plan.
* The DBMS_XPLAN package provides an easy way to display the output of the EXPLAIN PLAN command in several, predefined formats. You can also use the DBMS_XPLAN package to display the plan of a statement stored in the Automatic Workload Repository (AWR) or stored in a SQL tuning set. It further provides a way to display the SQL execution plan and SQL execution runtime statistics for cached SQL cursors based on the information stored in the V$SQL_PLAN and V$SQL_PLAN_STATISTICS_ALL fixed views.
For parallel queries use the "utlxplp.sql" script instead of "utlxpls.sql".
Question 49:
Which four types of column filtering may benefit from partition pruning when accessing tables via partitioned indexes?
A. Equality operates on List-Partitioned Indexes
B. Not Equal operates on a Global Hash-Partitioned Indexes
C. Equality operates on System-Partitioned Tables
D. In-List operates on Range-Partitioned Indexes
E. Not Equal operates on a local Hash-Partitioned Indexes
F. Equality operates on Range-Partitioned Indexes
G. Equality operates on Hash-Partitioned Indexes
Correct Answer: ADFG
Oracle Database prunes partitions when you use range, LIKE, equality (A, F), and IN-list (D) predicates on the range or list partitioning columns, and when you use equality (G) and IN-list predicates on the hash partitioning columns.
Reference: Oracle Database VLDB and Partitioning Guide 11g, Information that can be Used for Partition Pruning
Question 50:
In your database, the CURSOR_SHARING parameter is set to FORCE.
A user issues the following SQL statement: Select * from SH.CUSTOMERS where REIGN='NORTH' Which two statements are correct?
A. The literal value `NORTH' is replaced by a system-generated bind variable.
B. Bind peeking will not happen and subsequent executions of the statement with different literal values will use the same plan.
C. Adaptive cursor sharing happens only if there is a histogram in the REIGN column of the CUSTOMERS table.
D. Adaptive cursor sharing happens irrespective of whether there is a histogram in the REIGN column of the CUSTOMERS table.
Correct Answer: AB
CURSOR_SHARING determines what kind of SQL statements can share the same cursors. 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.
Nowadays, the certification exams become more and more important and required by more and more enterprises when applying for a job. But how to prepare for the exam effectively? How to prepare for the exam in a short time with less efforts? How to get a ideal result and how to find the most reliable resources? Here on Vcedump.com, you will find all the answers. Vcedump.com provide not only Oracle exam questions, answers and explanations but also complete assistance on your exam preparation and certification application. If you are confused on your 1Z0-117 exam preparations and Oracle certification application, do not hesitate to visit our Vcedump.com to find your solutions here.
