OCP Practice Test 23 — 15 Questions

Question 1

Which TWO components are essential for the operation of the Automatic Database Diagnostic Monitor (ADDM)?

Accepted Answer

AWR snapshots. ADDM needs AWR snapshots and the ADDM analysis task.

Answer

SQL Plan Baselines

Answer

Data Pump files

Answer

Listener control

Question 2

Which TWO methods are used to verify the success of an upgrade?

Accepted Answer

Reviewing log files. Checking the log files and querying the dictionary version are standard verification methods.

Answer

Reinstalling the OS

Answer

Running a test report

Answer

Checking user login

Question 3

Which TWO views can be used to monitor current session performance?

Accepted Answer

V$SESSION_WAIT. V$SESSION and V$SESSION_WAIT are standard tools for real-time monitoring.

Answer

V$CONTROLFILE

Answer

DBA_TABLES

Answer

DBA_USERS

Question 4

Which THREE of the following are valid components or features of the Automatic SQL Tuning process in Oracle 23ai?

Accepted Answer

SQL Plan Management (SPM). The Automatic SQL Tuning process involves the SQL Tuning Advisor, SQL Tuning Sets, and SQL Plan Baselines as part of its automated diagnostic and correction cycle.

Answer

Automatic Maintenance Tasks

Answer

Automatic Database Diagnostic Monitor (ADDM)

Question 5

What is the primary benefit of using Unified Auditing in 23ai?

Accepted Answer

Simplified audit management and unified audit trail. Unified Auditing provides a single, unified audit trail for all audit records from various sources.

Answer

Improved query performance for application tables

Answer

Increased storage capacity

Answer

Automatic data masking

Question 6

You are using SQL Plan Management. What happens when a new execution plan is discovered for a SQL statement that already has a baseline?

Accepted Answer

It is stored as an unaccepted plan. The new plan is marked as 'accepted' only if it is proven to be better than the existing plan; otherwise, it is stored as 'unaccepted'.

Answer

It is ignored by the optimizer

Answer

It is automatically accepted

Answer

It replaces the existing baseline

Question 7

When running the SQL Tuning Advisor, what does the 'Accept' action do?

Accepted Answer

It implements the recommended SQL Profile. Accepting a recommendation (like a profile) implements it in the database for future executions.

Answer

It permanently changes the SQL code

Answer

It deletes the SQL from the cache

Answer

It updates the global statistics

Question 8

When using AutoUpgrade to move to 23ai, which file format is required for the configuration?

Accepted Answer

CFG. AutoUpgrade uses a configuration file in a specific text-based key-value format (cfg file).

Answer

JSON

Answer

YAML

Answer

XML

Question 9

When migrating to 23ai, which method allows for the least amount of downtime?

Accepted Answer

Transportable Tablespaces. Transportable Tablespaces combined with Data Guard or GoldenGate typically offers the lowest downtime.

Answer

Full Export/Import

Answer

Upgrade in-place

Answer

Manual Database Re-creation

Question 10

You have a query that is using a nested loops join, but it should be using a hash join. The optimizer statistics are correct. How can you force a hash join without changing the SQL text?

Accepted Answer

Use a SQL Profile. SQL Plan Baselines or SQL Profiles can influence the optimizer without changing application code.

Answer

Change the STATISTICS_LEVEL

Answer

Use SQL Plan Management

Answer

Create an index

Question 11

Which background process is responsible for capturing AWR snapshots?

Accepted Answer

MMNL. The MMNL (Manageability Monitor) process is responsible for AWR snapshots.

Answer

PMON

Answer

DBWn

Answer

LGWR

Question 12

Which THREE items are required for the Automatic SQL Plan Management to effectively stabilize plans?

Accepted Answer

SQL Plan Baselines. SPM requires SQL baselines, a plan history, and an evolution process.

Answer

An external monitoring server

Answer

A manual index rebuild

Question 13

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 14

What does the 'Elapsed Time' in a SQL statement represent?

Accepted Answer

The total duration of the SQL execution. It is the total wall-clock time spent executing the statement from start to finish.

Answer

The time spent on CPU only

Answer

The time since the SQL was parsed

Answer

The time spent waiting for I/O

Question 15

What does the 'SQL ID' in an Oracle database represent?

Accepted Answer

A unique identifier for a SQL statement. The SQL ID is a unique hash of the SQL text, used to identify statements in the library cache.

Answer

A table name

Answer

A user ID

Answer

An execution plan version