Act now and download your Oracle 1Z0-117 test today! Do not waste time for the worthless Oracle 1Z0-117 tutorials. Download Down to date Oracle Oracle Database 11g Release 2: SQL Tuning Exam exam with real questions and answers and begin to learn Oracle 1Z0-117 with a classic professional.
2021 Oct 1Z0-117 practice test
Q21. Examine the parallelism parameters for your instance:
Examine the Exhibit to view the query and its explain plan output.
All sessions use default parallelism settings.
What two steps could you take to make the query execute in parallel?
A. Add a parallel hint.
B. Decrease the value of PARALLEL_MIN_TIMETHRESHOLD.
C. Increase the value of PARALLEL_MIN_SERVERS.
D. Increase the value of PARALLEL_MAX_SERVERS.
E. Decrease the value of PARALLEL_MIN_PERCENT.
Explanation: A: You can rely on hints to set the degree of parallelism, but these can be hard to set correctly.
Decision tree for query parallelization
Q22. View the exhibit and examine the findings provided by the SQL Tuning Advisor for SELECT Statement.
A SWL plan baseline already exists for the execution plan.
What two methods can you use to ensure that an alternate plan becomes an accepted plan?
A. Use the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function.
B. Use the DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE function.
C. Use the DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE function and run the DBMS_STATS to manually refresh stale statistics.
D. Use the DBMS_SPM.LOAD_PLANS_FROM_SQLSET function.
C: To adopt an alternative plan regardless of whether SQL Tuning Advisor recommends it, call DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE. You can use this procedure to create a SQL plan baseline on any existing reproducible plan.
D: LOAD_PLANS_FROM_SQLSET Function
This function loads plans stored in a SQL tuning set (STS) into SQL plan baselines. The plans loaded from STS are not verified for performance but added as accepted plans to existing or new SQL plan baselines. This procedure can be used to seed SQL management base with new SQL plan baselines.
* While tuning a SQL statement, SQL Tuning Advisor searches real-time and historical performance data for alternative execution plans for the statement. If plans other than the original plan exist, then SQL Tuning Advisor reports an alternative plan finding.
SQL Tuning Advisor validates the alternative execution plans and notes any plans that are
not reproducible. When reproducible alternative plans are found, you can create a SQL
plan baseline to instruct the optimizer to choose these plans in the future.
Not A: ALTER_SQL_PLAN_BASELINE Function
This function changes an attribute of a single plan or all plans associated with a SQL
statement using the attribute name/value format.
When a single plan is specified, one of various statuses, or plan name, or description can
be altered. When all plans for a SQL statement are specified, one of various statuses, or
description can be altered. This function can be called numerous times, each time setting a
different plan attribute of same plan(s) or different plan(s).
Reference: Oracle Database Performance Tuning Guide, Alternative Plan Analysis
Q23. You enable auto degree of parallelism (DOP) for your database instance.
Examine the following query:
Which two are true about the execution of statement?
A. Dictionary DOP for the objects accessed by the query is used to determine the statement DOP.
B. Auto DOP is used to determine the statement DOP only if the estimated serial execution time exceeds PARALLEL_MIN_TIME_THRESHOLD.
C. Dictionary DOP is used to determine the statement DOP only if the estimated serial execution time exceeds PARALLEL_MIN_TIME_THRESHOLD.
D. The statement will be queued if insufficient parallel execution slaves are available to satisfy the statements DOP.
E. The statement will be queued if the number of busy parallel execution servers exceeds PARALLEL_SERVERS_TARGET.
F. The statements may execute serially.
Explanation: * Parallel (Manual): The optimizer is forced to use the parallel settings of the objects in the statement.
MANUAL - This is the default. Disables Auto DOP (not B), statement queuing (not D, Not
E) and in-memory parallel execution. It reverts the behavior of parallel execution to what it
was previous to Oracle Database 11g, Release 2 (11.2).
You can use the PARALLEL hint to force parallelism. It takes an optional parameter: the
DOP at which the statement should run.
The following example forces the statement to use Oracle Database 11g Release 1 (11.1)
SELECT /*+ parallel(manual) */ ename, dname FROM emp e, dept d WHERE e.deptno=d.deptno;
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.
Q24. Which two types of SQL statements will benefit from dynamic sampling?
A. SQL statements that are executed parallel
B. SQL statement that use a complex predicate expression when extended statistics are not available.
C. SQL statements that are resource-intensive and have the current statistics
D. SQL statements with highly selective filters on column that has missing index statistics
E. Short-running SQL statements
Explanation: A: he optimizer decides whether to use dynamic statistics based on several factors. For example, the database uses automatic dynamic statistics when the SQL statement uses parallel execution.
B: One scenario where DS is used is when the statement contains a complex predicate expression and extended statistics are not available. Extended statistics were introduced in Oracle Database 11g Release 1 with the goal to help the optimizer get good quality cardinality estimates for complex predicate expressions.
D: DS It is typically used to compensate for missing or insufficient statistics that would otherwise lead to a very bad plan.
Reference: When the Optimizer Uses Dynamic Statistics
Q25. 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.
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 =>
* 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.
Renewal 1Z0-117 rapidshare:
Q26. View the code sequence: Examine the Exhibit to view the execution plan.
Which two statements are true about the query execution?
A. The optimizer joins specified tables in the order as they appear in the FROM clause.
B. The CUSTOMERS and ORDERS tables are joined first and the resultant is then joined with rows returned by the ORDER_ITEMS table.
C. The CUSTOMERS and ORDER_ITEMS tables are joined first the resultant is then joined with rows by the ORDERS table.
D. The optimizer has chosen hash join as an access method as the OPTIMIZER_MODE parameter is set to FIRST_ROWS.
Explanation: The first executed join is in line 6. The second executed join is in line 1.
A: Line 7 and 8 are executed first.
Q27. Which statement is true about an SQL plan baselines that are fixed?
A. New plans are added automatically by the optimizer to the baseline and are automatically evolved.
B. New, better plans are added automatically as a fixed plan baseline.
C. New plan can be manually loaded to the baseline from the cursor cache or a SQL tuning set.
D. New plans can be added as fixed plans to the baseline by using the SQL Tuning Advisor to generate a SQL profile and by accepting the SQL profile.
Explanation: When a SQL statement with a fixed SQL plan baseline is tuned using the SQL Tuning Advisor, a SQL profile recommendation has special meaning. When the SQL profile is accepted, the tuned plan is added to the fixed SQL plan baseline as a non-fixed plan. However, as described above, the optimizer will not use the tuned plan as long as a reproducible fixed plan is present. Therefore, the benefit of SQL tuning may not be realized. To enable the use of the tuned plan, manually alter the tuned plan to a fixed plan by setting its FIXED attribute to YES.
Note: It is also possible to influence the optimizer’s choice of plan when it is selecting a plan from a SQL plan baseline. SQL plan baselines can be marked as fixed. Fixed SQL plan baselines indicate to the optimizer that they are preferred. If the optimizer is costing SQL plan baselines and one of the plans is fixed, the optimizer will only cost the fixed plan and go with that if it is reproducible. If the fixed plan(s) are not reproducible the optimizer will go back and cost the remaining SQL plan baselines and select the one with the lowest cost. Note that costing a plan is nowhere near as expensive as a hard parse. The optimizer is not looking at all possible access methods but at one specific access path.
Reference: Oracle Database Performance Tuning Guide 11g, Using Fixed SQL Plan Baselines
Reference: SQL Plan Management in Oracle Database 11g
Q28. 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.
* 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.
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.
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
Q29. You plan to bulk load data using INSERT /*+PARALLEL*/ INTO . . . . SELECT FROM statements.
Which four types of operations can execute in parallel on tables that have no bitmapped indexes or materialized views defined on term?
A. Direct path insert of a million rows into a partitioned, index-organized table containing one million rows.
B. Direct path insert of a million rows into a partitioned, index-organized table containing 10 million rows.
C. Direct path insert of a million rows into a nonpartitioned, index-organized table containing one million rows.
D. Direct path insert of a million rows into a nonpartitioned, heap-organized table containing 10 million rows.
E. Direct path insert of a million rows into a nonpartitioned, heap-organized table containing one million rows.
Explanation: Direct-path INSERT is not supported for an index-organized table (IOT) if it is not partitioned, if it has a mapping table, or if it is reference by a materialized view.
Q30. Which four statements are correct about communication between parallel execution process?
A. The number of logical pathways between parallel execution producers and consumers depends on the degree parallelism.
B. The shared pool can be used for parallel execution messages buffers.
C. The large pool can be used for parallel execution messages buffers.
D. The buffer cache can be used for parallel execution message buffers.
E. Communication between parallel execution processes is never required if a query uses full partition-wise joins.
F. Each parallel execution process has an additional connection to the parallel execution coordinator.
Explanation: A: Note that the degree of parallelism applies directly only to intra-operation parallelism. If inter-operation parallelism is possible, the total number of parallel execution servers for a statement can be twice the specified degree of parallelism. No more than two sets of parallel execution servers can run simultaneously. Each set of parallel execution servers may process multiple operations. Only two sets of parallel execution servers need to be active to guarantee optimal inter-operation parallelism.
B: By default, Oracle allocates parallel execution buffers from the shared pool.
F: 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 can create additional parallel execution servers for the operation. These parallel execution servers remain with the operation throughout job execution, then become available for other operations. After the statement has been processed completely, the parallel execution servers return to the pool.
Reference: Oracle Database Data Warehousing Guide, Using Parallel Execution