OCP Performance Tuning & Optimization 3

Question 1

Which Oracle feature allows you to lock a SQL execution plan to prevent the optimizer from choosing a different plan after statistics changes?

Accepted Answer

SQL Plan Baselines (SPM)

Answer

SQL Plan Management (SPM) maintains a SQL Plan Baseline that ensures only accepted plans are used, preventing plan regression after changes.

Question 2

In Oracle, which initialization parameter controls the percentage of the buffer cache that can hold dirty buffers before DBWR is triggered?

Accepted Answer

FAST_START_MTTR_TARGET

Answer

FAST_START_MTTR_TARGET controls the mean time to recover (MTTR) target and indirectly governs how frequently DBWR flushes dirty blocks to limit recovery time.

Question 3

What is the primary benefit of using Automatic Memory Management (AMM) in Oracle?

Accepted Answer

Oracle automatically distributes memory between SGA and PGA based on workload demand

Answer

AMM allows Oracle to automatically size SGA and PGA components by setting only the MEMORY_TARGET parameter, adapting to workload changes.

Question 4

Which type of index is best suited for columns with very low cardinality (e.g., a gender column with 2 distinct values)?

Accepted Answer

Bitmap index

Answer

Bitmap indexes store a bitmap per distinct value and are efficient for low-cardinality columns, especially in data warehouse queries with multiple AND/OR conditions.

Question 5

What does a high 'buffer busy waits' event indicate in Oracle wait event analysis?

Accepted Answer

Multiple sessions contending to access or modify the same buffer block simultaneously

Answer

Buffer busy waits occur when a session must wait for another session to finish reading or modifying the same database buffer block.

(OCP) Oracle Certified Professional Practice Test

(OCP) Oracle Certified Professional Practice Test

OCP Performance Tuning & Optimization 3