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. What is the effect of setting the NO_MERGE hint in an Oracle SQL query containing an inline view?

    Answer: Prevents the optimizer from merging the inline view query into the outer query block

    The NO_MERGE hint prevents view merging, keeping the inline view as a separate query block so its results are materialized before being joined to the outer query.

  2. In Oracle partitioning, what is 'partition pruning'?

    Answer: The optimizer eliminating irrelevant partitions from a query's scan based on the WHERE clause

    Partition pruning is an optimization where Oracle reads only the partitions that can satisfy the query's filter conditions, reducing I/O dramatically.

  3. Which Oracle feature automatically identifies high-frequency SQL statements and creates SQL profiles to improve their performance without manual tuning?

    Answer: Automatic SQL Tuning (DBMS_AUTO_SQLTUNE)

    Automatic SQL Tuning runs nightly as an automated maintenance task and can create SQL Profiles for high-load SQL identified in AWR.

  4. What does a 'Cartesian join' in an Oracle execution plan signify?

    Answer: A join between two row sources with no join condition, producing all combinations of rows

    A Cartesian join (MERGE JOIN CARTESIAN) occurs when two tables are joined without a join predicate, multiplying every row from one source with every row from the other.

  5. Which Oracle data dictionary view shows the current invalid objects that may affect query execution after statistics or code changes?

    Answer: DBA_OBJECTS where status='INVALID'

    DBA_OBJECTS filtered on status='INVALID' lists all database objects that are currently in an invalid state and need recompilation.

  6. What is the purpose of the CARDINALITY hint in Oracle SQL?

    Answer: Provides the optimizer with a manual estimate of the number of rows a query or subquery will return

    The CARDINALITY hint overrides the optimizer's estimated row count for a query block, which can correct plan errors caused by statistics inaccuracies.

  7. Which Oracle wait event class indicates that sessions are waiting for resources related to Redo log file I/O?

    Answer: log file parallel write

    The 'log file parallel write' wait event occurs when the LGWR process is writing redo log buffers to the online redo log files on disk.