OCP Performance Tuning & Optimization 3 — Questions and Answers
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?
- SQL Profiles
- SQL Plan Baselines (SPM) (Correct answer)
- Stored Outlines
- Adaptive Query Plans
Correct answer: SQL Plan Baselines (SPM)
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?
- DB_WRITER_PROCESSES
- FAST_START_MTTR_TARGET (Correct answer)
- DB_BLOCK_BUFFERS
- _DB_BLOCK_MAX_DIRTY_TARGET
Correct answer: FAST_START_MTTR_TARGET
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?
- It eliminates the need for redo log files
- Oracle automatically distributes memory between SGA and PGA based on workload demand (Correct answer)
- It enables automatic index creation based on query patterns
- It controls CPU allocation between sessions dynamically
Correct answer: Oracle automatically distributes memory between SGA and PGA based on workload demand
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)?
- B-tree index
- Bitmap index (Correct answer)
- Function-based index
- Reverse key index
Correct answer: Bitmap index
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?
- Excessive I/O on the redo log buffer
- Multiple sessions contending to access or modify the same buffer block simultaneously (Correct answer)
- The buffer cache is too small causing frequent misses
- Parallel query slaves waiting for result sets
Correct answer: Multiple sessions contending to access or modify the same buffer block simultaneously
Buffer busy waits occur when a session must wait for another session to finish reading or modifying the same database buffer block.
Question 6: Which Oracle parameter enables automatic collection of real-time SQL statistics that feed into the SQL Monitoring facility?
- STATISTICS_LEVEL = BASIC
- STATISTICS_LEVEL = TYPICAL or ALL (Correct answer)
- SQL_TRACE = TRUE
- TIMED_OS_STATISTICS = 1
Correct answer: STATISTICS_LEVEL = TYPICAL or ALL
Setting STATISTICS_LEVEL to TYPICAL (default) or ALL enables collection of timed statistics required for SQL Monitoring and AWR.
Question 7: Which DBMS_STATS procedure allows you to restore optimizer statistics to a previous point in time?
- DBMS_STATS.IMPORT_SCHEMA_STATS
- DBMS_STATS.RESTORE_TABLE_STATS (Correct answer)
- DBMS_STATS.UNLOCK_TABLE_STATS
- DBMS_STATS.DIFF_TABLE_STATS
Correct answer: DBMS_STATS.RESTORE_TABLE_STATS
DBMS_STATS.RESTORE_TABLE_STATS restores statistics for a table to the values they held at a specified timestamp, using the stats history retained in the data dictionary.
OCP Performance Tuning & Optimization 3
Which Oracle feature allows you to lock a SQL execution plan to prevent the optimizer from choosing a different plan after statistics changes?