OCP Practice Questions 2 — 50 Questions

Question 1

You want to capture a specific set of SQL statements and their execution plans to baseline them. Which package allows you to capture plans from the cursor cache?

Accepted Answer

DBMS_SPM. DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE is the standard way to load plans into the baseline.

Answer

DBMS_SCHEDULER

Answer

DBMS_SQLTUNE

Answer

DBMS_STATS

Question 2

In a multitenant environment, what is the best approach to backup only a specific PDB?

Accepted Answer

BACKUP PLUGGABLE DATABASE pdb_name. You can use the 'BACKUP PLUGGABLE DATABASE' command to target a specific PDB.

Answer

BACKUP CDB PDB pdb_name

Answer

BACKUP DATABASE PDBNAME

Answer

BACKUP PDB ALL

Question 3

You need to determine why a particular session is experiencing high 'db file sequential read' waits. Which view provides the most detailed information about the specific blocks being read?

Accepted Answer

V$SESSION_WAIT. V$SESSION_WAIT includes P1, P2, and P3 columns which often identify the file and block being accessed during the wait.

Answer

V$BH

Answer

V$SQL

Answer

V$FILESTAT

Answer

V$SQL_PLAN

Question 4

A critical application query is experiencing performance issues due to stale optimizer statistics. You want to use the Optimizer Statistics Advisor to identify the root cause. Which action must you perform to trigger the advisor?

Accepted Answer

Call DBMS_STATS.REPORT_STATS_ADVISOR_TASK to generate a report on statistics-related issues.. The Optimizer Statistics Advisor can be invoked using the DBMS_STATS.REPORT_STATS_ADVISOR_TASK function to analyze and report on statistics health.

Answer

Run the AWR report and look for 'Stats' in the top wait events.

Answer

Enable the SQL Tuning Advisor for the specific SQL_ID.

Answer

Execute DBMS_STATS.GATHER_SCHEMA_STATS with the 'AUTO' option.

Question 5

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 are specifically designed to capture, store, and transport SQL statements along with their execution context and statistics.

Answer

SQL Plan Baselines

Answer

ADDM Findings

Answer

AWR Snapshots

Question 6

A database is configured with a Flash Recovery Area (FRA). You want to ensure that RMAN automatically deletes obsolete backups to reclaim space in the FRA. Which command should you configure?

Accepted Answer

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;. The CONFIGURE RETENTION POLICY TO RECOVERY WINDOW or REDUNDANCY command, combined with the automatic management of the FRA, allows RMAN to delete obsolete backups when space is required.

Answer

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '...';

Answer

CONFIGURE CONTROLFILE AUTOBACKUP ON;

Answer

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

Question 7

When reviewing an AWR report, you notice a high value for 'db time'. What does this indicate?

Accepted Answer

The total time spent by sessions in the database. DB Time represents the total time spent by foreground processes in the database, indicating overall load.

Answer

The time spent on disk I/O only

Answer

The time spent on instance background processes

Answer

The time the database was open

Question 8

You notice that Automatic SQL Plan Management is not evolving your plans as expected. What is the most likely cause?

Accepted Answer

The SPM Evolve Task is disabled. The SYS_AUTO_SPM_EVOLVE_TASK must be enabled and have a sufficient maintenance window to run.

Answer

SQL Plan Baselines are full

Answer

Statistics are gathered manually

Answer

The database is in NOARCHIVELOG mode

Question 9

You are configuring a new CDB and want to ensure that PDBs use only a specific subset of the CDB's total SGA memory while allowing individual PDBs to scale up when demand is high. Which parameter should you configure to enforce this?

Accepted Answer

PDB_SGA_MAX_SIZE. The SGA_TARGET parameter at the CDB level defines the total SGA, while PDB_SGA_MIN_SIZE and PDB_SGA_MAX_SIZE parameters allow for fine-grained control and resource capping within the multitenant environment.

Answer

DB_CACHE_SIZE

Answer

SGA_MIN_SIZE

Answer

MEMORY_TARGET

Question 10

You have a query that is performing poorly due to a stale execution plan. You want to lock the current optimal plan to prevent the optimizer from changing it during subsequent runs. What should you do?

Accepted Answer

Use DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE. SQL Plan Baselines allow you to store and fix execution plans for specific SQL statements.

Answer

Run DBMS_STATS.LOCK_TABLE_STATS

Answer

Set OPTIMIZER_FEATURES_ENABLE to a previous version

Answer

Use SQL Tuning Advisor to accept a new profile

Question 11

Which report provides a summary of SQL statements that have the highest 'Elapsed Time'?

Accepted Answer

AWR Report. The AWR report has a section specifically for Top SQL by Elapsed Time.

Answer

Alert Log

Answer

Listener Log

Answer

ADDM Report

Question 12

Which advisor should you run to determine if your SGA and PGA memory settings are optimal?

Accepted Answer

Memory Advisor. Memory Advisor identifies if memory pools are undersized or oversized.

Answer

SQL Access Advisor

Answer

SQL Tuning Advisor

Answer

ADDM

Question 13

What is the default state of a PDB after creating it using the CREATE PLUGGABLE DATABASE command?

Accepted Answer

MOUNTED. By default, a new PDB is created in MOUNTED state.

Answer

READ WRITE

Answer

OPEN UPGRADE

Answer

READ ONLY

Question 14

You observe a significant performance regression after a database upgrade. You suspect that the optimizer has chosen a suboptimal execution plan. Which component of SQL Plan Management should you use to ensure the database only uses known, verified execution plans?

Accepted Answer

SQL Plan Baselines. SQL Plan Baselines are used to ensure that only verified execution plans are used for a specific SQL statement, protecting against performance regressions.

Answer

Optimizer Statistics Advisor

Answer

SQL Tuning Sets

Answer

Automatic SQL Tuning Advisor

Question 15

Which utility is used to perform a 'fixup' during the AutoUpgrade process?

Accepted Answer

preupgrade_fixups.sql. AutoUpgrade performs fixups automatically, but you can also run them manually using the pre-upgrade fixup scripts.

Answer

srvctl

Answer

utlu122s.sql

Answer

catupgrd.sql

Question 16

When migrating to 23ai, which file is used to specify the target home directory in AutoUpgrade?

Accepted Answer

config.cfg. The AutoUpgrade .cfg file uses the 'target_home' parameter.

Answer

init.ora

Answer

oratab

Answer

spfile.ora

Question 17

Which command is used to start the AutoUpgrade process?

Accepted Answer

java -jar autoupgrade.jar. The command is 'java -jar autoupgrade.jar -config <config_file>'.

Answer

sqlplus / as sysdba

Answer

opatch

Answer

dbua

Question 18

Which TWO are common audit types in Oracle?

Accepted Answer

Unified Auditing. Standard and Unified auditing are the two primary types.

Answer

Tablespace Auditing

Answer

Instance Auditing

Answer

Flashback Auditing

Question 19

Which THREE steps are required for a manual TDE configuration?

Accepted Answer

Create the keystore. Setting the location, creating the wallet, and opening the wallet are the required steps.

Answer

Rebuild all tables

Answer

Encrypt the entire database

Question 20

Which THREE actions are part of the AutoUpgrade process?

Accepted Answer

Post-upgrade cleanup. AutoUpgrade handles the Pre-check, Upgrade, and Post-upgrade phases.

Answer

Applying security patches

Answer

Rebuilding the OS