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

Correct Answer: AD

INSERT Creates a SQL script that stores the trace file statistics in the database. TKPROF creates this script with the name filename3. This script creates a table and inserts a row of statistics for each traced SQL statement into the table.

Correct Answer: D

Note:

*

Statistics quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions.

*

INDEX_COMBINE Forces a bitmap index access path on tab. Primarily this hint just tells Oracle to use the bitmap indexes on table tab. Otherwise Oracle will choose the best combination of indexes it can think of based on the statistics. If it is ignoring a bitmap index that you think would be helpful, you may specify that index plus all of the others taht you want to be used. Note that this does not force the use of those indexes, Oracle will still make cost based choices.

*

Histograms Opportunities Any column used in a where clause with skewed data Histograms are NOT just for indexed columns.

Adding a histogram to an un-indexed column that is used in

Correct Answer: D

Manual Plan Loading 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.

Note:

*

The value of the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter, whose default value is FALSE, determines if the system should automatically capture SQL plan baselines. When set to TRUE, the system records a plan history for SQL statements. The first plan for a specific statement is automatically flagged as accepted. Alternative plans generated after this point are not used until it is verified they do not cause performance degradations. Plans with acceptable performance are added to the SQL plan baseline during the evolution phase.

*

Managing SQL plan baselines involves three phases:

Capturing SQL Plan Baselines Selecting SQL Plan Baselines Evolving SQL Plan Baselines

*

Evolving SQL Plan Baselines

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.

Reference: SQL Plan Management in Oracle Database 11g Release 1

Correct Answer: ABG

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

B: Your query doesn't run because you've told Oracle not to run it unless at least 5% of the parallel execution processes are available for your query.

Set PARALLEL_MIN_PERCENT=0 or increase the number of parallel execution processes by increasing the PARALLEL_MAX_SERVERS parameter.

G: PARALLEL_DEGREE_POLICY

PARALLEL_DEGREE_POLICY specifies whether or not automatic degree of Parallelism, statement queuing, and in-memory parallel execution will be enabled.

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.

Note: PARALLEL_MIN_PERCENT operates in conjunction with PARALLEL_MAX_SERVERS and PARALLEL_MIN_SERVERS. It lets you specify the minimum percentage of parallel execution processes (of the value of PARALLEL_MAX_SERVERS) required for parallel execution. Setting this parameter ensures that parallel operations will not execute sequentially unless adequate resources are available. The default value of 0 means that no minimum percentage of processes has been set.

Consider the following settings:

PARALLEL_MIN_PERCENT = 50 PARALLEL_MIN_SERVERS = 5 PARALLEL_MAX_SERVERS = 10

If 8 of the 10 parallel execution processes are busy, only 2 processes are available. If you then request a query with a degree of parallelism of 8, the minimum 50% will not be met.

You can use this parameter in conjunction with PARALLEL_ADAPTIVE_MULTI_USER. In a multi-user environment, an individual user or application can set PARALLEL_MIN_PERCENT to a minimum value until sufficient resources are available on the system and an acceptable degree of parallelism is returned.