OCP Practice Test 19 — 15 Questions

Question 1

You are an Oracle Database 23ai administrator. A batch job that performs many small random reads on a large table is experiencing high 'db file sequential read' wait times. The table is stored in a tablespace with a block size of 8 KB. You suspect that the storage subsystem is the bottleneck. Which action would best confirm whether the wait time is due to slow disk I/O?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Query V$FILESTAT and compare the average read time per file.. The 'db file sequential read' wait event indicates single-block reads, often from indexes. To confirm if the storage subsystem is slow, you need to measure average I/O latency. V$FILESTAT provides cumulative read time and read count per file, allowing you to calculate average read time. High average read time points to slow disk I/O as the root cause.

Answer

Check the value of the DB_CACHE_SIZE parameter to see if the buffer cache is too small.

Answer

Run an EXPLAIN PLAN on the batch job's SQL statements to see if full table scans are used.

Answer

Use the SQL Tuning Advisor to analyze the batch SQL and recommend indexes.

Question 2

Your Oracle Database 23ai instance uses True Cache to offload read-only queries from the primary database. A developer reports that a query against a table modified by another session returns stale data from True Cache. You need to ensure that the True Cache returns the most recent committed data for that query. Which action should you take?

Accepted Answer

Verify that the True Cache is properly registered with the primary database and that the cache coherency mechanism is functioning.. True Cache maintains coherency with the primary database by receiving invalidation messages when data changes. If those messages are not delivered, the cache can return stale data. The correct action is to verify that the True Cache is properly registered and that the coherency mechanism is operational, rather than attempting to set nonexistent parameters or alter UNDO usage.

Answer

Set the session parameter TRUE_CACHE_CONSISTENCY to SESSION.

Answer

Configure the True Cache to use the PRIMARY database's UNDO tablespace for read consistency.

Answer

Ensure that the True Cache is synchronized by enabling the TRUE_CACHE_SYNC parameter.

Question 3

A database administrator is enabling True Cache for a primary database. The application will connect to the True Cache instance for read-only queries. Which statement about the default behavior of a session connected to True Cache is correct?

Accepted Answer

The session is read-only and any DML statement returns an error.. A session connected to a True Cache instance is read-only by default. Any DML or DDL statement returns an error, such as ORA-16000. This design ensures that the cache remains consistent with the primary database and does not handle writes. Applications must route write operations to the primary database. The read-only nature of True Cache sessions is a fundamental characteristic that administrators must understand when deploying True Cache for read scaling.

Answer

The session can perform DML, but the changes are applied only to the cache and not the primary.

Answer

The session can perform both read and write operations, with writes automatically forwarded to the primary.

Answer

The session can perform DML, but the changes are queued and applied to the primary when the cache refreshes.

Question 4

A DBA is analyzing an AWR report for an Oracle Database 23ai instance and notices a high number of 'enq: TX - row lock contention' waits. Which two actions should the DBA take to diagnose and resolve this issue? (Choose two.)

Accepted Answer

Query V$LOCK and V$SESSION to identify the blocking session and the locked object.. Row lock contention occurs when one transaction holds a lock on a row that another transaction needs. The first step is to identify the blocking session and the locked object using V$LOCK and V$SESSION. Then, examining the SQL of both sessions helps understand why the lock is held. This allows the DBA to tune the transaction or application to reduce lock duration. Increasing undo tablespaces or DML_LOCKS does not address the root cause.

Answer

Increase the number of undo tablespaces to reduce contention.

Answer

Enable automatic undo management and increase the undo retention period.

Answer

Set the parameter DML_LOCKS to a higher value.

Question 5

You are managing a high-concurrency OLTP system on Oracle Database 23ai. Multiple sessions frequently update a single row in an inventory table, causing contention and lock waits. You want to eliminate this contention without changing the application logic. Which 23ai feature should you implement?

Accepted Answer

Lock-Free Reservations. Lock-Free Reservations is a 23ai feature that allows concurrent updates to a numeric column without locking the row, using a reservation journal. This directly addresses the contention caused by multiple sessions updating the same row. Other features like True Cache, In-Memory Column Store, and Automatic Indexing do not reduce write contention and are not designed for this purpose.

Answer

True Cache

Answer

Automatic Indexing

Answer

In-Memory Column Store

Question 6

You are an Oracle Database 23ai administrator. A batch job that performs many small random reads on a large table is experiencing high 'db file sequential read' wait times. The table is stored in a tablespace with a block size of 8 KB. You suspect that the storage subsystem is the bottleneck. Which action would best confirm whether the wait time is due to slow disk I/O?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Query V$LOCK to identify the blocking session and the locked object.. Row lock contention occurs when one session holds a lock on a row that another session needs to modify. The first diagnostic step is to identify the blocking session and the object involved. V$LOCK provides this information by showing lock holders and requesters. Once identified, you can decide whether to wait, commit, or terminate the blocker.

Answer

Increase the value of the DB_BLOCK_CHECKING parameter to detect corruption.

Answer

Reduce the value of the PGA_AGGREGATE_TARGET to free memory for locks.

Answer

Run ADDM to automatically resolve the row lock contention.

Question 7

A DBA is troubleshooting a performance issue in Oracle Database 23ai. A query that previously used an index full scan now performs a full table scan. The DBA suspects that the optimizer's statistics are stale. Which action should the DBA take to confirm this suspicion?

Accepted Answer

Examine the execution plan and compare the estimated cardinality with the actual rows processed using DBMS_XPLAN.DISPLAY_CURSOR with the ALLSTATS LAST option.. To confirm that stale statistics caused the optimizer to choose a full table scan, the DBA should compare the optimizer's estimated cardinality with the actual rows processed. If the estimates are far off, it indicates that the statistics do not reflect the current data. DBMS_XPLAN.DISPLAY_CURSOR with ALLSTATS LAST provides this comparison. Merely checking LAST_ANALYZED dates or gathering statistics does not confirm the cause.

Answer

Query DBA_TAB_STATISTICS and DBA_IND_STATISTICS to compare LAST_ANALYZED dates with the table modification timestamps.

Answer

Use DBMS_STATS.GATHER_TABLE_STATS with the default parameters and see if the plan changes.

Answer

Check the OPTIMIZER_MODE parameter to ensure it is set to ALL_ROWS.

Question 8

You are an Oracle Database 23ai administrator. A batch job that performs many small random reads on a large table is experiencing high 'db file sequential read' wait times. The table is stored in a tablespace with a block size of 8 KB. You suspect that the storage subsystem is the bottleneck. Which action would best confirm whether the wait time is due to slow disk I/O?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Increase the PGA_AGGREGATE_TARGET parameter.. Automatic PGA Memory Management dynamically allocates memory to work areas based on PGA_AGGREGATE_TARGET. When a query spills to disk, it means the allocated work area was insufficient. Increasing PGA_AGGREGATE_TARGET provides more memory for sorts and hash joins, reducing temp space usage and improving performance. This is the direct solution for the described symptom.

Answer

Set the WORKAREA_SIZE_POLICY parameter to MANUAL and configure SORT_AREA_SIZE.

Answer

Increase the SGA_TARGET parameter.

Answer

Increase the DB_CACHE_SIZE parameter to cache more blocks and reduce temp usage.

Question 9

A database administrator is creating a table to support high-concurrency transactions using lock-free reservations. Which clause must be included in the CREATE TABLE statement to enable lock-free reservations on a numeric column?

Accepted Answer

RESERVABLE. To enable lock-free reservations on a numeric column, the column must be defined with the RESERVABLE clause. This clause allows the database to manage reservations in a separate journal, enabling multiple transactions to update the column without locking the row. The RESERVABLE clause is specific to Oracle Database 23ai and is essential for using this feature. Other clauses like LOCK_FREE or NO_LOCK are not recognized and would cause syntax errors.

Answer

NO_LOCK

Answer

CONCURRENT

Answer

LOCK_FREE

Question 10

Your Oracle 23ai production database runs in ARCHIVELOG mode with a fast recovery area configured. A media failure destroys the disk holding data file 7 of the SALES tablespace, but the control file and all archived redo logs remain intact. You still have the original data file header information available. Which RMAN command restores data file 7 and then applies only the redo needed to make it consistent, without restoring the entire database?

Accepted Answer

RESTORE DATAFILE 7; then RECOVER DATAFILE 7;. Restoring a single data file requires first bringing back the physical file from a backup, then applying redo to synchronize it with the rest of the database. Because the control file survived, RMAN retains the metadata needed to identify the correct backup and the recovery SCN. This granular approach avoids a full database restore and shortens the outage.

Answer

RESTORE DATABASE; then RECOVER DATABASE;

Answer

RECOVER DATAFILE 7; only, without a preceding RESTORE

Answer

SWITCH DATAFILE 7 TO COPY; then RECOVER DATAFILE 7;

Question 11

You are the DBA for an Oracle 23ai database running in ARCHIVELOG mode with a fast recovery area configured. A user reports that a query against the SALES table returns ORA-01578: ORACLE data block corrupted (file # 7, block # 1482). You want RMAN to repair only the affected block using a backup while the database remains open. Which command should you execute?

Accepted Answer

RECOVER DATAFILE 7 BLOCK 1482;. Block media recovery with RECOVER DATAFILE ... BLOCK ... is designed precisely for isolated corruption reported by ORA-01578 while the database stays open. It restores and recovers only the damaged block from RMAN backups, keeping the rest of the data file online. Validation and whole-file restore/recover are either non-repairing or unnecessarily disruptive for a single corrupt block.

Answer

RESTORE DATAFILE 7 BLOCK 1482;

Answer

RECOVER DATAFILE 7;

Answer

VALIDATE DATAFILE 7;

Question 12

You are an Oracle Database 23ai administrator. A user reports that a specific SQL query is running slowly. You want to see the execution plan that the optimizer actually used for the most recent execution of that query. Which dynamic performance view should you query?

Accepted Answer

V$SQL_PLAN. To see the actual execution plan used for a recently executed SQL statement, you query V$SQL_PLAN. It stores the plan for each child cursor in the library cache, including the operations and their order. By joining with V$SQL, you can identify the specific SQL_ID and CHILD_NUMBER, and then retrieve the plan steps.

Answer

V$SESSION_LONGOPS

Answer

V$SQLAREA

Answer

DBA_HIST_SQL_PLAN

Question 13

You are an Oracle Database 23ai administrator. A batch job that performs many small random reads on a large table is experiencing high 'db file sequential read' wait times. The table is stored in a tablespace with a block size of 8 KB. You suspect that the storage subsystem is the bottleneck. Which action would best confirm whether the wait time is due to slow disk I/O?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Execute DBMS_STATS.GATHER_TABLE_STATS for the table.. The most direct and recommended way to refresh statistics for a specific table is to use the DBMS_STATS.GATHER_TABLE_STATS procedure. It collects current statistics that the optimizer needs to make accurate cardinality estimates. The other options either use deprecated methods, change unrelated parameters, or rely on automatic processes that do not provide immediate results.

Answer

Enable automatic statistics collection by setting STATISTICS_LEVEL to TYPICAL.

Answer

Run ANALYZE TABLE ... COMPUTE STATISTICS.

Answer

Set the OPTIMIZER_MODE initialization parameter to FIRST_ROWS.

Question 14

Your Oracle 23ai database is running in ARCHIVELOG mode. You have configured RMAN backups to a tape library using a media manager. You need to restore and recover a data file that was corrupted due to a hardware failure. The data file is currently offline. Which RMAN command should you use to restore and recover the data file?

Accepted Answer

RESTORE DATAFILE 5; RECOVER DATAFILE 5;. To recover a corrupted data file, you must first restore it from a backup and then apply archived redo logs using RECOVER. The correct sequence is RESTORE DATAFILE followed by RECOVER DATAFILE. The other options either omit recovery, have the wrong order, or use an inappropriate command.

Answer

RECOVER DATAFILE 5; RESTORE DATAFILE 5;

Answer

SWITCH DATAFILE 5; RECOVER DATAFILE 5;

Answer

RESTORE DATAFILE 5; ALTER DATABASE DATAFILE 5 ONLINE;

Question 15

You are managing an Oracle Database 23ai instance. A critical application query is performing poorly. You have identified the SQL ID and want to capture a detailed execution plan with runtime statistics for a specific execution. The query is currently running and has been for several minutes. Which action should you take to get the most detailed diagnostic information?

Accepted Answer

Generate an active SQL Monitor report using DBMS_SQL_MONITOR.REPORT_SQL_MONITOR for the SQL ID.. SQL Monitor is specifically designed to monitor long-running or parallel SQL statements in real time. It captures detailed execution statistics, including actual rows, memory usage, time, and wait events for each operation. The report generated by DBMS_SQL_MONITOR.REPORT_SQL_MONITOR provides a comprehensive view of the query's performance while it is running, making it the ideal tool for this scenario.

Answer

Use DBMS_XPLAN.DISPLAY_AWR with the SQL ID to get the plan from AWR.

Answer

Query V$SQL_PLAN for the SQL ID to retrieve the execution plan.

Answer

Enable SQL tracing for the session using DBMS_SESSION.SET_SQL_TRACE and then examine the trace file.