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
In Oracle, what is the difference between a Soft Parse and a Hard Parse?
Answer: Soft parse reuses an existing cursor from the shared pool; hard parse requires full compilation including optimization
A soft parse finds a matching cursor in the shared pool and reuses it, while a hard parse must fully parse, optimize, and generate an execution plan for the SQL statement.
Which Oracle feature helps identify the root cause of a sudden performance degradation by comparing two AWR snapshots?
Answer: ADDM (Automatic Database Diagnostic Monitor)
ADDM analyzes AWR data between two snapshots and automatically identifies top performance bottlenecks along with recommendations.
What is the purpose of Oracle's Adaptive Query Optimization introduced in Oracle 12c?
Answer: It allows the optimizer to adjust execution plans mid-execution based on actual row counts observed
Adaptive Query Optimization allows Oracle to modify execution plans during runtime when actual row counts differ significantly from optimizer estimates.
Which Oracle SQL clause allows you to enforce a specific index usage when the optimizer chooses a full table scan?
Answer: INDEX hint with the index name
The INDEX hint explicitly tells the optimizer to use a specific index on a table, overriding its decision to perform a full table scan.
In Oracle, what does 'enq: TX - row lock contention' wait event indicate?
Answer: A session is waiting for another session to commit or rollback a transaction that holds a row-level lock on the same row
The enq: TX - row lock contention wait occurs when a session tries to modify a row that is already locked by an uncommitted transaction in another session.
Which Oracle feature allows you to simulate the effect of a new index on query performance without physically creating the index?
Answer: SQL Access Advisor with virtual indexes
SQL Access Advisor can recommend indexes and allows testing of hypothetical (virtual) indexes to evaluate their impact on workload performance before physical creation.
What does the GATHER_PLAN_STATISTICS hint do when added to a SQL query in Oracle?
Answer: Collects actual row counts and elapsed time per operation during execution, visible in V$SQL_PLAN_STATISTICS
GATHER_PLAN_STATISTICS captures actual execution metrics per plan operation, which can be viewed via DBMS_XPLAN.DISPLAY_CURSOR with the 'ALLSTATS LAST' format option.