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 view provides real-time statistics about SQL statements currently executing in the database?

    Answer: V$SQL_MONITOR

    V$SQL_MONITOR provides real-time monitoring of SQL statements that have consumed significant CPU or I/O resources.

  2. What is the purpose of the RESULT_CACHE hint in Oracle SQL?

    Answer: Caches query results in the SGA for reuse by subsequent identical queries

    The RESULT_CACHE hint instructs Oracle to store query results in the result cache area of the SGA so repeated identical queries can be served without re-execution.

  3. Which optimizer statistic gathering option should you use to gather only stale or missing statistics efficiently?

    Answer: DBMS_STATS.GATHER_DATABASE_STATS with options=>'GATHER STALE'

    Using DBMS_STATS.GATHER_DATABASE_STATS with options=>'GATHER STALE' collects statistics only for objects whose statistics are missing or exceed the 10% staleness threshold.

  4. In Oracle, what does the 'NESTED LOOPS' join operation indicate in an execution plan?

    Answer: Each row from the outer table drives a lookup into the inner table, ideal for small result sets with index access

    Nested loops join retrieves each row from the outer (driving) table and performs a corresponding lookup in the inner table, typically using an index, making it efficient for small row counts.

  5. What is the AWR retention period default setting in Oracle Database?

    Answer: 7 days

    By default, Oracle AWR retains performance snapshots for 7 days before purging them automatically.

  6. Which SQL*Plus command is used to display the execution plan stored in the PLAN_TABLE for a previously explained query?

    Answer: DBMS_XPLAN.DISPLAY

    DBMS_XPLAN.DISPLAY reads from PLAN_TABLE and formats the execution plan in a readable hierarchical output.

  7. What does the 'Cost' column in an Oracle execution plan represent?

    Answer: An optimizer-estimated relative cost based on CPU and I/O estimates compared to a single-block I/O

    The Cost in an Oracle execution plan is a dimensionless optimizer estimate derived from I/O and CPU resource models, not actual time.