OCP Practice Test 6 — 10 Questions

Question 1

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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