Correct Answer: CEF

C: A histogram is a frequency distribution (metadata) that describes the distribution of data values within a table.

E: It's well established that histograms are very useful for helping the optimizer choose between a full-scan and and index-scan.

F: Histograms may help the Oracle optimizer in deciding whether to use an index vs. a full-table scan (where index values are skewed) or help the optimizer determine the fastest table join order. For determining the best table join order, the WHERE clause of the query can be inspected along with the execution plan for the original query. If the cardinality of the table is too-high, then histograms on the most selective column in the WHERE clause will tip-off the optimizer and change the table join order. Note:

* The Oracle Query Optimizer uses histograms to predict better query plans. The ANALYZE command or DBMS_STATS package can be used to compute these histograms.

Incorrect:

B: Histograms are NOT just for indexed columns.

Adding a histogram to an un-indexed column that is used in a where clause can improve performance.

D: Histograms Opportunities Any column used in a where clause with skewed data Columns that are not queried all the time Reduced overhead for insert, update, delete

Correct Answer: ABE

A: Low clustering factor promotes good performance.

The clustering_factor measures how synchronized an index is with the data in a table. A table with a high clustering factor is out-of-sequence with the rows and

large index range scans will consume lots of I/O. Conversely, an index with a low clustering_factor is closely aligned with the table and related rows reside

together of each data block, making indexes very desirable for optimal access.

Note:

*

Oracle SQL not using an index is a common complaint, and it's often because the optimizer thinks that a full-scan is cheaper than index access. Oracle not using an index can be due to:

*

(E) Bad/incomplete statistics Make sure to re-analyze the table and index with dbms_stats to ensure that the optimizer has good metadata.

*

Wrong optimizer_mode The first_rows optimizer mode is to minimize response time, and it is more likely to use an index than the default all_rows mode.

*

Bugs See these important notes on optimizer changes in 10g that cause Oracle not to use an index.

*

Cost adjustment In some cases, the optimizer will still not use an index, and you must de- crease optimizer_index_cost_adj.

Correct Answer: AF

*

Parallel (Manual): The optimizer is forced to use the parallel settings of the objects in the state- ment.

*

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 pre- vious to Oracle Database 11g, Release 2 (11.2).

*

PARELLEL (MANUAL)

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) behav- ior:

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 PARAL- LEL_DEGREE_POLICY is set to AUTO, Oracle will queue SQL statements that require paral- lel 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 sys- tem with parallel server processes.

Note that all serial (non-parallel) statements will execute immediately even if statement queuing has been activated.

Correct Answer: AC

*

PARALLEL (AUTO): The database computes the degree of parallelism (C), which can be 1 or greater (not D). If the computed degree of parallelism is 1, then the statement runs serially.

*

You can use the PARALLEL hint to force parallelism. It takes an optional parameter: the DOP at which the statement should run. In addition, theNO_PARALLEL hint overrides a PARALLEL parameter in the DDL that created or altered the table.

The following example illustrates computing the DOP the statement should use: SELECT /*+ parallel(auto) */ ename, dname FROM emp e, dept d

WHERE e.deptno=d.deptno;

* When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database auto- matically decides if a statement should execute in parallel or not and what DOP it should use. Oracle Database also determines if the statement can be executed immediately or if it is queued until more system resources are available. Finally, Oracle Database decides if the statement can take advantage of the aggregated cluster memory or not.

Correct Answer: BC

B: The INDEX hint explicitly chooses an index scan for the specified table. You can use the INDEX hint for domain, B-tree, bitmap, and bitmap join indexes. However, Oracle recommends using INDEX_COMBINE rather than INDEX for the combination of multiple indexes, because it is a more versatile hint.

C: Combining the CUST_ID and PROD_ID columns into an composite index would improve performance.

Correct Answer: C

Step 1: Syntax check (2)

Step 2: Semantic check (4)

Step 3: Shared pool check

Step 4: Optimization

Step 5: Row Source Optimization (generation of query plan) (3)

Etc.

Note:

Incorrect:

Not A: First execute then fetch rows.

Not B: Check of syntax is before check of semantics.

Not D: Parse tree is after semantics.

Correct Answer: ACD

A (not B, Not E): PARALLEL_MIN_TIME_THRESHOLD : Oracle 11gR2 will ascertain if the query's estimated execution time is likely to run longer than the

acceptable value (in seconds) for pARALLEL_MIN_TIME_THRESHOLD and, if sufficient resources for parallel execution exist right now, it will allow the query to

execute; otherwise, it will delay its execution until sufficient resources exist. This helps prevent a single parallel query from consuming excessive resources at the

cost of other non-parallelizable operations. The default of this parameter is 10 seconds. C, D: In earlier versions of the Oracle Database, we had to determine the

DOP more or less manually, either with a parallel hint or by setting a parallel degree with alter table. There was an automatic computation of the DOP available for

the objects with dictionary DOP of default, derived from the simple formula CPU_COUNT * PARALLEL_THREADS_PER_CPU. If there were insufficient parallel

servers to satisfy the requested DOP, one of three things could occur:

/ The SQL would be run at a reduced DOP (be downgraded)

/ The SQL would run in serial mode (be serialized)

/ If PARALLEL_MIN_PERCENT was specified and less than the nominated percentage of the DOP was achievable, then the the SQL statement might terminate

with "ORA-12827:

insufficient parallel query slaves available".

Note:

*

PARALLEL_DEGREE_POLICY. It can have 3 values : MANUAL, LIMITED and AUTO

. MANUAL - This is the default. Disables Auto DOP, statement queuing 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).

*

Oracle supports parallel processing for a wide range of operations, including queries, DDL and DML:

· Queries that involve table or index range scans.

· Bulk insert, update or delete operations.

· Table and index creation.

*

Oracle's parallel execution framework enables you to either explicitly chose - or even enforce - a specific degree of parallelism (DOP) or to rely on Oracle to

control it.

*

Three modes are available to request a DOP :

default fixed adaptive

*

The DOP is determined in the following priority order:

hint session table and limited by the Oracle Database Resource Manager (DBRM) settings.

Reference: AUTOMATIC DEGREE OF PARALLELISM (DOP) IN ORACLE 11G R2

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: BD

B: An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

Note:

*

All_rows attempts to optimize the query to get the very last row as fast as possible. This makes sense in a stored procedure for example where the client does

not regain control until the stored procedure completes. You don't care if you have to wait to get the first row if the last row gets back to you twice as fast. In a

client server/interactive application you may well care about that.

*

The optimizer uses nested loop joins to process an outer join in the following circumstances:

/ It is possible to drive from the outer table to inner table.

/ Data volume is low enough to make the nested loop method efficient.

*

First_rows attempts to optimize the query to get the very first row back to the client as fast as possible. This is good for an interactive client server environment

where the client runs a query and shows the user the first 10 rows or so and waits for them to page down to get more.

Correct Answer: DE

D: For parallel statements, the optimizer automatically decides whether to use dynamic sampling and which level to use. The decision depends on the size of the tables and the complexity of the predicates. The optimizer expects parallel statements to be resource-intensive, so the additional overhead at compile time is worth it to ensure the best plan. The database ignores the For serially processed SQL statements, the dynamic sampling level depends on the value of the OPTIMIZER_DYNAMIC_SAMPLING parameter and is not triggered automatically by the optimizer. Serial statements are typically short-running, so that any overhead at compile time could have a huge impact on their performance. the value is honored.

Reference: Oracle Database Administrator's Guide, About Oracle Database Resource Manager