OCP Practice Test 4 — 10 Questions

Question 1

Which feature allows you to capture SQL statements from the cursor cache and load them into a SQL Tuning Set?

Accepted Answer

DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET. DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET is the procedure used to pull queries from memory.

Answer

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE

Answer

DBMS_STATS.GATHER_SYSTEM_STATS

Answer

DBMS_WORKLOAD_REPOSITORY.EXTRACT_SQL

Question 2

When using Wide Tables, what is the impact on performance for queries that select only a small subset of columns?

Accepted Answer

No significant performance degradation. Wide Tables in 23ai are optimized to handle many columns without significant performance degradation compared to vertical splitting.

Answer

Severe performance degradation

Answer

Requires partitioning

Answer

Requires a full table scan

Question 3

Which THREE types of information are captured in an AWR snapshot?

Accepted Answer

Library cache statistics. AWR captures workload, system statistics, and session activity.

Answer

Tablespace creation scripts

Answer

Database user passwords

Question 4

The Automatic SQL Plan Management (ASPM) feature is enabled. A new execution plan is identified for a high-load query that performs better during testing. How does the database verify this plan before evolving the baseline?

Accepted Answer

It executes the SQL statement using the new plan and compares its performance metrics against the current baseline.. The database performs a verification task where it executes the new plan and compares it against the baseline performance to decide if it should be accepted.

Answer

It flags the plan for manual DBA approval via the DBMS_SPM package.

Answer

It automatically promotes the plan based on the optimizer cost estimation.

Answer

It relies on the Advisor to run a full analysis of the explain plan tree.

Question 5

You need to ensure that RMAN automatically deletes archived redo logs after they have been backed up twice to tape. Which configuration command should you use?

Accepted Answer

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DEVICE TYPE SBT;. The correct RMAN syntax to automatically delete archived redo logs after they have been backed up a specified number of times to a device type is 'CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP n TIMES TO DEVICE TYPE <device>'. This policy tells RMAN to delete archived logs once the specified redundancy to the given device (SBT = tape) is satisfied.

Answer

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;

Answer

SET ARCHIVELOG DELETION POLICY TO 2 COPIES;

Answer

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

Question 6

You have a vector index and notice search performance is slow. Which index type should you use to optimize similarity search?

Accepted Answer

HNSW. HNSW (Hierarchical Navigable Small World) is a graph-based index specifically designed for approximate nearest neighbor search in high-dimensional vector spaces. It provides fast and accurate similarity search by building a multi-layer graph structure. Other index types like inverted, B-tree, or bitmap are not optimized for vector similarity.

Answer

Inverted Index

Answer

B-tree

Answer

Bitmap

Question 7

Which of the following is true regarding Vector embeddings in 23ai?

Accepted Answer

They are stored as arrays of floats. Vectors are typically generated by AI models and stored as arrays of floats.

Answer

They are stored as metadata

Answer

They are stored as images

Answer

They are stored as text

Question 8

A production database experiences a sudden performance degradation. You want to analyze the last 10 minutes of activity to pinpoint blocked sessions. Which approach is best?

Accepted Answer

Query V$ACTIVE_SESSION_HISTORY. ASH data is stored in memory and is the primary source for recent, short-term performance diagnostics.

Answer

Generate an AWR report for the last hour

Answer

Check the Alert Log for ORA errors

Answer

Run the SQL Access Advisor

Question 9

You need to capture a specific set of SQL statements that are causing high CPU usage for later tuning. Which tool allows you to group these statements and transport them across different database environments?

Accepted Answer

SQL Tuning Sets. SQL Tuning Sets (STS) are designed to capture a specific set of SQL statements along with their execution context, and they can be exported and transported to other databases for tuning or plan comparison. This makes them the correct tool for grouping high-CPU SQL and moving it across environments.

Answer

SQL Plan Baselines

Answer

ADDM Findings

Answer

AWR Snapshots

Question 10

Which TWO methods can be used to tune SQL statements?

Accepted Answer

Manual addition of optimizer hints. Option C (Manual addition of optimizer hints) is correct because optimizer hints are directives embedded in SQL statements (e.g., /*+ INDEX(...) */ or /*+ FULL(...) */) that influence the query optimizer's execution plan, directly tuning statement performance. Option E (SQL Tuning Advisor) is correct because it is an Oracle tool that analyzes a SQL statement, its execution plan, and statistics, then produces tuning recommendations such as SQL profiles, indexes, or restructured SQL. The other options do not tune SQL statements: increasing backups (A) is a backup/recovery activity, dropping the database (B) is a destructive DDL operation, and changing user roles (D) is a security/privilege change, none of which affect query execution plans or SQL performance.

Answer

Increasing the number of backups

Answer

Dropping the database

Answer

Changing user roles