OCP Practice Test 22 — 15 Questions

Question 1

Which view displays the current settings for the AWR retention period?

Accepted Answer

DBA_HIST_WR_CONTROL. DBA_HIST_WR_CONTROL contains the snapshot settings including retention and interval.

Answer

DBA_SNAPSHOTS

Answer

V$DATABASE

Answer

V$INSTANCE

Question 2

You are investigating high CPU utilization in the database. Which view provides the top SQL statements currently in memory based on CPU usage?

Accepted Answer

V$SQLAREA. V$SQLAREA contains aggregated statistics for SQL statements in the library cache, including CPU_TIME.

Answer

V$SESSION

Answer

V$SYSSTAT

Answer

V$LIBRARYCACHE

Question 3

You have a high volume of parse time in your database. What could be the cause?

Accepted Answer

Using literals instead of bind variables. Hard parsing occurs when SQL is not shared, often due to literals instead of bind variables.

Answer

High memory usage

Answer

Too many indexes

Answer

Missing statistics

Question 4

You are analyzing an AWR report and notice a high value for 'db file scattered read'. What is the most likely cause of this wait event?

Accepted Answer

Full table scans or index fast full scans.. 'db file scattered read' is typically associated with full table scans or index fast full scans where multiple blocks are read into non-contiguous memory locations.

Answer

Insufficient PGA memory for sorting operations.

Answer

Excessive log file sync waits.

Answer

Heavy use of nested loop joins.

Question 5

An administrator needs to perform maintenance on a specific PDB while keeping the rest of the CDB and other PDBs fully operational. What is the correct state to set the PDB to?

Accepted Answer

MOUNTED. Setting a PDB to MOUNTED state allows administrators to perform administrative tasks, such as renaming datafiles or performing point-in-time recovery, without opening the PDB for user access.

Answer

RESTRICTED

Answer

SHUTDOWN

Answer

READ ONLY

Question 6

Which THREE features are specific to the Multitenant architecture and distinguish a CDB from a non-CDB?

Accepted Answer

Shared background processes for all PDBs. Multitenant architecture relies on shared background processes, centralized data dictionary views (CDB_ views), and the ability to plug and unplug databases.

Answer

The use of a separate SGA for every PDB

Answer

The requirement for a unique SID for each PDB

Question 7

What is the primary function of the Database Resident Connection Pooling (DRCP) in a 23ai environment?

Accepted Answer

Connection scaling. DRCP reduces the memory footprint of a database by pooling server processes.

Answer

Security authentication

Answer

Data encryption

Answer

Audit trail management

Question 8

An ADDM report suggests increasing the library cache size. What is the most appropriate action?

Accepted Answer

Increase SGA_TARGET. Oracle 23ai manages SGA memory automatically; adjusting the SGA_TARGET or SGA_MAX_SIZE is the primary way to influence this.

Answer

Manually set SHARED_POOL_SIZE

Answer

Drop and recreate the library cache

Answer

Rebuild the database

Question 9

How can you view the recommendations made by the Automatic Database Diagnostic Monitor (ADDM)?

Accepted Answer

Querying DBA_ADVISOR_FINDINGS. ADDM recommendations can be viewed in AWR or via the DBA_ADVISOR_FINDINGS view.

Answer

Checking the alert log

Answer

Querying V$SGA

Answer

Running the catproc script

Question 10

In which view can you find information about the current SQL Plan Baselines?

Accepted Answer

DBA_SQL_PLAN_BASELINES. DBA_SQL_PLAN_BASELINES is the primary view for information about SQL plan baselines.

Answer

V$SESSION

Answer

V$SQL

Answer

DBA_INDEXES

Question 11

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 12

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 13

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 14

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 15

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