OCP Performance Tuning & Optimization 2 — Questions and Answers
Question 1: Which Oracle view provides real-time statistics about SQL statements currently executing in the database?
- V$SQL
- V$SESSION_LONGOPS
- V$SQL_MONITOR (Correct answer)
- V$ACTIVE_SESSION_HISTORY
Correct answer: V$SQL_MONITOR
V$SQL_MONITOR provides real-time monitoring of SQL statements that have consumed significant CPU or I/O resources.
Question 2: What is the purpose of the RESULT_CACHE hint in Oracle SQL?
- Forces a full table scan
- Caches query results in the SGA for reuse by subsequent identical queries (Correct answer)
- Pins the query plan in the cursor cache
- Enables parallel query execution
Correct 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.
Question 3: Which optimizer statistic gathering option should you use to gather only stale or missing statistics efficiently?
- DBMS_STATS.GATHER_DATABASE_STATS with cascade=>TRUE
- DBMS_STATS.GATHER_STALE_STATS
- DBMS_STATS.GATHER_DATABASE_STATS with options=>'GATHER STALE' (Correct answer)
- DBMS_STATS.GATHER_SCHEMA_STATS with method_opt=>'FOR ALL COLUMNS'
Correct 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.
Question 4: In Oracle, what does the 'NESTED LOOPS' join operation indicate in an execution plan?
- Each row from the outer table drives a lookup into the inner table, ideal for small result sets with index access (Correct answer)
- Both tables are sorted and merged together
- A hash table is built from the smaller table for probe operations
- The query requires a parallel full table scan of both tables
Correct 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.
Question 5: What is the AWR retention period default setting in Oracle Database?
- 3 days
- 7 days (Correct answer)
- 14 days
- 30 days
Correct answer: 7 days
By default, Oracle AWR retains performance snapshots for 7 days before purging them automatically.
Question 6: Which SQL*Plus command is used to display the execution plan stored in the PLAN_TABLE for a previously explained query?
- SHOW PLAN
- SELECT * FROM PLAN_TABLE
- DBMS_XPLAN.DISPLAY (Correct answer)
- V$SQL_PLAN query
Correct answer: DBMS_XPLAN.DISPLAY
DBMS_XPLAN.DISPLAY reads from PLAN_TABLE and formats the execution plan in a readable hierarchical output.
Question 7: What does the 'Cost' column in an Oracle execution plan represent?
- Elapsed wall-clock time in milliseconds
- An optimizer-estimated relative cost based on CPU and I/O estimates compared to a single-block I/O (Correct answer)
- The number of logical reads required
- The actual number of rows processed
Correct 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.
OCP Performance Tuning & Optimization 2
Which Oracle view provides real-time statistics about SQL statements currently executing in the database?