OCP Performance Tuning & Optimization 3

Question 1

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

Accepted Answer

SQL Plan Baselines (SPM)

Answer

SQL Profiles

Answer

Stored Outlines

Answer

Adaptive Query Plans

Question 2

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

Accepted Answer

FAST_START_MTTR_TARGET

Answer

DB_WRITER_PROCESSES

Answer

DB_BLOCK_BUFFERS

Answer

_DB_BLOCK_MAX_DIRTY_TARGET

Question 3

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

Accepted Answer

Oracle automatically distributes memory between SGA and PGA based on workload demand

Answer

It eliminates the need for redo log files

Answer

It enables automatic index creation based on query patterns

Answer

It controls CPU allocation between sessions dynamically

Question 4

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

Accepted Answer

Bitmap index

Answer

B-tree index

Answer

Function-based index

Answer

Reverse key index

Question 5

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

Accepted Answer

Multiple sessions contending to access or modify the same buffer block simultaneously

Answer

Excessive I/O on the redo log buffer

Answer

The buffer cache is too small causing frequent misses

Answer

Parallel query slaves waiting for result sets

Question 6

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

Accepted Answer

STATISTICS_LEVEL = TYPICAL or ALL

Answer

STATISTICS_LEVEL = BASIC

Answer

SQL_TRACE = TRUE

Answer

TIMED_OS_STATISTICS = 1

Question 7

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

Accepted Answer

DBMS_STATS.RESTORE_TABLE_STATS

Answer

DBMS_STATS.IMPORT_SCHEMA_STATS

Answer

DBMS_STATS.UNLOCK_TABLE_STATS

Answer

DBMS_STATS.DIFF_TABLE_STATS