Practice Test Geeks(OCP) Oracle Certified Professional Practice Test
← All OCP Flashcard Decks

Performance Tuning & Optimization Flashcards

7 cards from real OCP practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.

Read the first 7 Performance Tuning & Optimization flashcards as text

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

    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.

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

    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.

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

    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.

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

    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.

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

    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.

  6. Which Oracle parameter enables automatic collection of real-time SQL statistics that feed into the SQL Monitoring facility?

    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.

  7. Which DBMS_STATS procedure allows you to restore optimizer statistics to a previous point in time?

    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.