You created a SQL Tuning Set (STS) containing resource-intensive SQL statements. You plan to run the SQL Tuning Advisor.
Which two types of recommendations can be provided by the SQL Tuning Advisor?
A. Semantic restructuring for each SQL statement
B. Gathering missing or stale statistics at the schema level for the entire workload
C. Creating a materialized view to benefit from query rewrite for the entire workload
D. Gathering missing or stale statistics for objects used by the statements.
E. Creating a partition table to benefit from partition pruning for each statement
Correct Answer: AD
The output of the SQL Tuning Advisor is in the form of an advice or recommendations, along with a rationale for each recommendation and its expected benefit. The recommendation relates to collection of statistics on objects ( D), creation of new indexes, restructuring of the SQL statement (A), or creation of a SQL profile. You can choose to accept the recommendation to complete the tuning of the SQL statements.
Note:
*
A SQL Tuning Set can be used as input to the SQL Tuning Advisor, which performs automatic tuning of the SQL statements based on other input parameters specified by the user.
*
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. The SQL statements can be loaded into a SQL Tuning Set from different SQL sources, such as the Automatic Workload Repository, the cursor cache, or custom SQL provided by the user.
In Your Database, The Cursor_Shareing Parameter is set to EXACT. In the Employees table, the data is significantly skewed in the DEPTNO column. The value 10 is found in 97% of rows.
Examine the following command and out put.
Which three statements are correct?
A. The DEPTNO column will become bind aware once histogram statistics are collected.
B. The value for the bind variable will considered by the optimizer to determine the execution plan.
C. The same execution plan will always be used irrespective of the bind variable value.
D. The instance collects statistics and based on the pattern of executions creates a histogram on the column containing the bind value.
E. Bind peeking will take place only for the first execution of the statement and subsequent execution will use the same plan.
Correct Answer: ABD
*
We here see that the cursor is marked as bind sensitive (IS_BIND_SEN is Y).
*
In 11g, the optimizer has been enhanced to allow multiple execution plans to be used for a single statement that uses bind variables. This ensures that the best
execution plan will be used depending on the bind value.
A cursor is marked bind sensitive if the optimizer believes the optimal plan may depend *
on the value of the bind variable. When a cursor is marked bind sensitive, Oracle monitors the behavior of the cursor using different bind values, to determine if a
different plan for different bind values is called for.
*
(B, not C): A cursor is marked bind sensitive if the optimizer believes the optimal plan may depend on the value of the bind variable. When a cursor is marked
bind sensitive, Oracle monitors the behavior of the cursor using different bind values, to determine if a different plan for different bind values is called for.
Note: 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.
Reference: Why are there more cursors in 11g for my query containing bind variables?
Question 113:
Examine the Exhibit 1 to view the structure of and indexes for EMPLOYEES and DEPARTMENTS tables.
Which three statements are true regarding the execution plan?
A. The view operator collects all rows from a query block before they can be processed but higher operations in the plan.
B. The in-line query in the select list is processed as a view and then joined.
C. The optimizer pushes the equality predicate into the view to satisfy the join condition.
D. The optimizer chooses sort-merge join because sorting is required for the join equality predicate.
E. The optimizer chooses sort-merge join as a join method because an equality predicate is used for joining the tables.
Correct Answer: ABC
Incorrect:
Not D, not E:
Sort Merge joins are used for UN-Equality and also there is no SORT clause in the SQL. Note: The optimizer may choose a sort merge join over a hash join for
joining large amounts of data when any of the following conditions is true:
*
The join condition between two tables is not an equijoin, that is, uses an inequality condition such as <, <=, >, or >=.
*
Because of sorts required by other operations, the optimizer finds it cheaper to use a sort merge.
Question 114:
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.
Correct Answer: ACDG
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.
Question 115:
Which are the two prerequisites for enabling star transformation on queries?
A. The STAR_TRANSFORMATION_ENABLED parameter should be set to TRUE or TEMP_DISABLE.
B. A B-tree index should be built on each of the foreign key columns of the fact table(s),
C. A bitmap index should be built on each of the primary key columns of the fact table(s).
D. A bitmap index should be built on each of the foreign key columns of the fact table(s).
E. A bitmap index must exist on all the columns that are used in the filter predicates of the query.
Correct Answer: AE
A: Enabling the transformation
E: Star transformation is essentially about adding subquery predicates corresponding to the constraint dimensions. These subquery predicates are referred to as bitmap semi-join predicates. The transformation is performed when there are indexes on the fact join columns (s.timeid, s.custid...). By driving bitmap AND and OR operations (bitmaps can be from bitmap indexes or generated from regular B-Tree indexes) of the key values supplied by the subqueries, only the relevant rows from the fact table need to be retrieved. If the filters on the dimension tables filter out a lot of data, this can be much more efficient than a full table scan on the fact table. After the relevant rows have been retrieved from the fact table, they may need to be joined back to the dimension tables, using the original predicates. In some cases, the join back can be eliminated.
Star transformation is controlled by the star_transformation_enabled parameter. The parameter takes 3 values.
TRUE - The Oracle optimizer performs transformation by identifying fact and constraint dimension tables automatically. This is done in a cost-based manner, i.e.
the transformation is performed only if the cost of the transformed plan is lower than the non-transformed plan. Also the optimizer will attempt temporary table
TEMP_DISABLE - This value has similar behavior as TRUE except that temporary table transformation is not tried.
The default value of the parameter is FALSE. You have to change the parameter value and create indexes on the joining columns of the fact table to take
advantage of this transformation.
Reference: Optimizer Transformations: Star Transformation
Question 116:
You plan to bulk load data INSERT INTO . . . SELECT FROM statements.
Which two situations benefit from parallel INSERT operations on tables that have no materialized views defined on them?
A. Direct path insert of a million rows into a partitioned, index-organized table containing one million rows and a conventional B*tree secondary index.
B. Direct path insert of a million rows into a partitioned, index-organized table containing 10 rows and a bitmapped secondary index.
C. Direct path insert of 10 rows into a partitioned, index-organized table containing one million rows and conventional B* tree secondary index.
D. Direct path insert of 10 rows into a partitioned, index-organized table containing 10 rows and a bitmapped secondary index
E. Conventional path insert of a million rows into a nonpartitioned, heap-organized containing 10 rows and having a conventional B* tree index.
F. Conventional path insert of 10 rows into a nonpartitioned, heap-organized table one million rows and a bitmapped index.
Correct Answer: AB
Note:
*
A materialized view is a database object that contains the results of a query.
*
You can use the INSERT statement to insert data into a table, partition, or view in two ways: conventional INSERTand direct-path INSERT.
*
With direct-path INSERT, the database appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused. This alternative enhances performance during insert operations and is similar to the functionality of the Oracle direct-path loader utility, SQL*Loader. When you insert into a table that has been created in parallel mode, direct-path INSERT is the default.
*
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.
*
When you issue a conventional INSERT statement, Oracle Database reuses free space in the table into which you are inserting and maintains referential integrity constraints
*
Conventional INSERT always generates maximal redo and undo for changes to both data and metadata, regardless of the logging setting of the table and the archivelog and force logging settings of the database
Question 117:
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
Correct Answer: ACD
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
Question 118:
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.
Correct Answer: DE
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
Question 119:
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
Correct Answer: AD
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;
p0 or
It is easy to see that none of the rows which ought to be returned will be in either of the partitions 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 "cutting away"
of unneeded partitions is known as pruning.
in the table. This
Question 120:
Examine Exhibit1 to view the query and its AUTOTRACE output.
Which two statements are true about tracing?
A. The displayed plan will be stored in PLAN_TABLE.
B. Subsequent execution of this statement will use the displayed plan that is stored in v$SQL.
C. The displayed plan may not necessarily be used by the optimizer.
D. The query will not fetch any rows; it will display only the execution plan and statistics.
E. The execution plan generated can be viewed from v$SQLAREA.
Correct Answer: AD
The PLAN_TABLE is automatically created as a public synonym to a global temporary table. This temporary table holds the output of EXPLAIN PLAN statements for all users. PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans
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.
