Correct Answer: C

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

Correct Answer: E

The Automatic Tuning Optimizer is meant to be used for complex and high-load SQL statements that have non-trivial impact on the entire system. The Automatic Database Diagnostic Monitor (ADDM) proactively identifies high-load SQL statements which are good candidates for SQL tuning.

Note:

* When SQL statements are executed by the Oracle database, the query optimizer is used to generate the execution plans of the SQL statements. The query

optimizer operates in two modes:

a normal mode and a tuning mode.

In normal mode, the optimizer compiles the SQL and generates an execution plan. The normal mode of the optimizer generates a reasonable execution plan for the vast majority of SQL statements. Under normal mode, the optimizer operates with very strict time constraints, usually a fraction of a second, during which it must find a good execution plan.

In tuning mode, the optimizer performs additional analysis to check whether the execution plan produced under normal mode can be improved further. The output of the query optimizer is not an execution plan, but a series of actions, along with their rationale and expected benefit for producing a significantly superior plan. When running in the tuning mode, the optimizer is referred to as the Automatic Tuning Optimizer.

Reference: Oracle Database Performance Tuning Guide, Automatic SQL Tuning

Correct Answer: C

Note:

*

With the DBMS_STATS package you can view and modify optimizer statistics gathered for database objects.

*

The SET_GLOBAL_PREFS procedure e is used to set the global statistics preferences.

*

ESTIMATE_PERCENT - The value determines the percentage of rows to estimate. The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.

Correct Answer: D

OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance.

Values:

FIRST_ROWS_N - The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100,

1000).

FIRST_ROWS - The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.

ALL_ROWS - The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource

use to complete the entire statement).

Correct Answer: D

Note:

* In the execution plan BX, note the keyword 'VIEW PUSHED PREDICATE' indicates that the view has undergone the join predicate pushdown transformation.

Correct Answer: ADF

Under tuning mode, the optimizer can take several minutes to tune a single statement. It is both time and resource intensive to invoke Automatic Tuning Optimizer every time a query must be hard-parsed. Automatic Tuning Optimizer is meant for complex and high-load SQL statements that have nontrivial impact on the database.

Automatic Database Diagnostic Monitor (ADDM) proactively identifies high-load SQL state- ments that are good candidates for SQL tuning. The automatic SQL tuning feature also automati- cally identifies problematic SQL statements and implements tuning recommendations during system maintenance windows as an automated maintenance task.

The Automatic Tuning Optimizer performs the following types of tuning analysis:

Statistics Analysis SQL Profiling Access Path Analysis SQL Structure Analysis Alternative Plan Analysis

Note:

* Oracle Database uses the optimizer to generate the execution plans for submitted SQL state- ments. The optimizer operates in the following modes:

Normal mode The optimizer compiles the SQL and generates an execution plan. The normal mode generates a reasonable plan for the vast majority of SQL statements. Under normal mode, the optimizer op- erates with very strict time constraints, usually a fraction of a second.

Tuning mode

The optimizer performs additional analysis to check whether it can further improve the plan pro- duced in normal mode. The optimizer output is not an execution plan, but a series of actions, along with their rationale and expected benefit for producing a significantly better plan. When running in tuning mode, the optimizer is known as the Automatic Tuning Optimizer.

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.

Correct Answer: AD

Note:

*

result_cache_mode: the result cache can be enabled in three ways: via hint, alter session or alter system. Default is MANUAL which means that we need to explicitly request caching via the RESULT_CACHE hint;

*

As its name suggests, the query result cache is used to store the results of SQL queries for re- use in subsequent executions. By caching the results of queries, Oracle can avoid having to repeat the potentially time-consuming and intensive operations that generated the resultset in the first place (for example, sorting/ aggregation, physical I/O, joins etc). The cache results themselves are available across the instance (i.e. for use by sessions other than the one that first executed the query) and are maintained by Oracle in a dedicated area of memory. Unlike our homegrown solutions using associative arrays or global temporary tables, the query result cache is completely transparent to our applications. It is also maintained for consistency automatically, unlike our own caching programs.

*

RESULT_CACHE_MODE specifies when a ResultCache operator is spliced into a query's execution plan.

Values:

/ MANUAL

The ResultCache operator is added only when the query is annotated (that is, hints).

/FORCE

The ResultCache operator is added to the root of all SELECT statements (provided that it is valid to do so).

For the FORCE setting, if the statement contains a NO_RESULT_CACHE hint, then the hint takes precedence over the parameter setting.

Correct Answer: D

Example:

Additional set operations will be done for the customer dimension and the product dimension. At this point in the star query processing, there are three bitmaps. Each bitmap corresponds to a separate dimension table, and each bitmap represents the set of rows of the fact table that satisfy that individual dimension's constraints.

These three bitmaps are combined into a single bitmap using the bitmap AND operation. This final bitmap represents the set of rows in the fact table that satisfy all of the constraints on the dimension table.

Reference: Oracle Database Data Warehousing Guide, Star Transformation with a Bitmap Index