OCP Practice Test 2 — 25 Questions

Question 1

You are tuning a complex SQL statement. You want to see the execution plan that the optimizer is currently choosing. Which dynamic performance view is most useful?

Accepted Answer

V$SQL_PLAN. V$SQL_PLAN shows the execution plan for statements currently in the library cache.

Answer

V$SESSION

Answer

V$SQLAREA

Answer

V$SYSSTAT

Question 2

When using the VECTOR data type, what is the maximum number of dimensions supported?

Accepted Answer

2048. Oracle 23ai supports up to 2048 dimensions for the vector data type.

Answer

512

Answer

1024

Answer

4096

Question 3

Which file stores the metadata for a PDB being unplugged?

Accepted Answer

XML file. The unplug process generates an XML file to describe the PDB.

Answer

TXT file

Answer

JSON file

Answer

SQL file

Question 4

You are performing a tablespace recovery. What is the first thing that must happen to the tablespace before recovery can start?

Accepted Answer

The tablespace must be taken offline.. The tablespace must be taken offline to perform recovery on its constituent datafiles.

Answer

The tablespace must be set to read-only.

Answer

The database must be shut down.

Answer

The database must be in restricted mode.

Question 5

What is the consequence of not running the 'catupgrd.sql' script after a major version upgrade?

Accepted Answer

Data dictionary remains in old version format. The data dictionary will be inconsistent, and many objects will be invalid, leading to database failure.

Answer

Only performance is affected

Answer

The database will run in read-only mode

Answer

The database will not start

Question 6

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 7

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 8

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 9

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 10

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 11

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 12

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 13

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 14

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 15

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

Question 16

What is the primary benefit of Lock-Free Reservations in high-concurrency environments?

Accepted Answer

Reduced row-level locking contention. It eliminates wait events associated with row-level locking for numeric updates.

Answer

Faster queries

Answer

Better compression

Answer

Automatic indexing

Question 17

Which THREE configuration steps are required to implement True Cache?

Accepted Answer

Update the connection pool. To implement True Cache, you must update the connection pool (A) so the application can route read-only queries to the True Cache instance via the appropriate connection descriptor, which is essential for directing traffic to the cache. You must also configure the True Cache instance (B) itself, which involves creating it with the True Cache-specific initialization parameters and associating it with the primary database so it can automatically receive and cache data. Additionally, you must enable on the application side (C), meaning the application must be configured to use the True Cache connection and set to read-only mode where appropriate, allowing it to leverage the cached data. Manual data refresh scripts (D) are not required because True Cache automatically refreshes data from the primary database, and disabling Data Guard (E) is not required since True Cache can coexist with Data Guard and does not depend on disabling it.

Answer

Manual data refresh scripts

Answer

Disable Data Guard

Question 18

What is the primary benefit of using a SQL Profile?

Accepted Answer

They improve the accuracy of the optimizer. SQL Profiles improve the optimizer's choices by providing better selectivity and cardinality estimates.

Answer

They fix the execution plan forever

Answer

They speed up physical I/O

Answer

They automatically gather new statistics

Question 19

You want to enforce password complexity in 23ai using a profile. Which clause is used to reference a custom verification function?

Accepted Answer

PASSWORD_VERIFY_FUNCTION. The PASSWORD_VERIFY_FUNCTION clause in the CREATE PROFILE statement allows for custom verification logic.

Answer

FAILED_LOGIN_ATTEMPTS

Answer

PASSWORD_LOCK_TIME

Answer

PASSWORD_LIFE_TIME

Question 20

A retail company uses Oracle Database 23ai and wants to enable real-time analytics on a high-volume OLTP table without impacting transaction performance. They decide to use the True Cache feature. Which statement correctly describes how True Cache achieves this?

Accepted Answer

True Cache is a memory-optimized cache that stores frequently accessed data and serves consistent reads, reducing load on the primary database.. True Cache is an in-memory read-only cache that offloads read traffic from the primary database by serving consistent reads. It automatically caches frequently accessed data and is transparent to applications, making it ideal for real-time analytics without impacting OLTP performance.

Answer

True Cache uses Oracle Active Data Guard to synchronize data and provides read-only access to the standby database.

Answer

True Cache automatically redirects all read queries to a read-only replica, ensuring zero impact on the primary database.

Answer

True Cache requires the application to explicitly route read queries to the cache using a connection pool.