OCP Performance Tuning And Diagnostics Practice Test 4 — 15 Questions

Question 1

Which TWO methods can be used to tune SQL statements?

Accepted Answer

Manual addition of optimizer hints. Manual tuning with hints and automated tuning via advisors are common practices.

Answer

Increasing the number of backups

Answer

Dropping the database

Answer

Changing user roles

Question 2

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 3

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 4

Which TWO of these are common sources of performance degradation?

Accepted Answer

High resource contention. Suboptimal SQL and system-wide resource contention are the most common issues.

Answer

Renaming a table

Answer

Changing the database name

Answer

Adding a new user

Question 5

Which THREE pieces of information can be found in a SQL Tuning Advisor report?

Accepted Answer

Index recommendations. The report includes findings, recommendations, and execution statistics.

Answer

Instance parameter history

Answer

Database backup schedules

Question 6

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 7

Which THREE actions can you take to improve execution plan stability?

Accepted Answer

Use SQL Profiles. Stability is achieved through baselines, profiles, and optimizer plan stability features.

Answer

Frequently drop and recreate indexes

Answer

Increase the SGA size every day

Question 8

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 9

An administrator needs to identify the top wait events causing performance degradation in the database over the last hour. Which tool provides the most granular, session-level view of these wait events?

Accepted Answer

Active Session History (ASH). ASH provides real-time, session-level performance data, making it the ideal tool for analyzing short-term, granular performance spikes.

Answer

SQL Tuning Advisor

Answer

Automatic Database Diagnostic Monitor (ADDM)

Answer

Automatic Workload Repository (AWR)

Question 10

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 11

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 12

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 13

An administrator has been tasked with reducing the overhead of AWR snapshot collection. What is the most effective way to modify the snapshot interval?

Accepted Answer

Use the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure.. The DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure is the official method to adjust snapshot frequency and retention policies.

Answer

Edit the SPFILE to change the statistics_level to 'BASIC'.

Answer

Update the SGA_TARGET parameter.

Answer

Manually delete old records from the DBA_HIST_SNAPSHOT view.

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 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 15

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)