OCP Performance Tuning And Diagnostics Practice Test 2 — 15 Questions

Question 1

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 2

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 3

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 4

You have a query that is being incorrectly optimized because the statistics are not representative of the actual data distribution. What is the best method to handle this without changing the global statistics?

Accepted Answer

Use DBMS_STATS.SET_TABLE_PREFS. Table-specific statistics collection with specific parameters or using preference settings can help.

Answer

Run the SQL Tuning Advisor

Answer

Modify the init.ora parameter

Answer

Create a SQL Plan Baseline

Question 5

Which procedure in the DBMS_SPM package is used to evolve a SQL plan baseline?

Accepted Answer

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE. DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE is the standard procedure to evolve baselines.

Answer

DBMS_SPM.DROP_SQL_PLAN_BASELINE

Answer

DBMS_SPM.ALTER_SQL_PLAN_BASELINE

Answer

DBMS_SPM.LOAD_PLANS_FROM_SQLSET

Question 6

What is the result of setting STATISTICS_LEVEL to 'BASIC'?

Accepted Answer

It disables AWR and ADDM. Setting it to BASIC disables many diagnostic features, including AWR and ADDM.

Answer

It increases CPU overhead

Answer

It enables SQL Tuning Advisor

Answer

It enables all features

Question 7

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 8

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 9

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 10

You are analyzing a query with the SQL Tuning Advisor. The advisor recommends an 'SQL Profile'. What does the profile actually do?

Accepted Answer

It adds optimizer hints and cardinality adjustments. A SQL Profile provides additional optimizer hints and statistics corrections.

Answer

It updates the table statistics

Answer

It creates a new index

Answer

It rewrites the SQL query

Question 11

Which of the following is true regarding SQL Plan Baselines?

Accepted Answer

They provide stability for SQL execution plans. They allow the database to maintain a stable execution plan even if the environment (stats, indexes) changes.

Answer

They automatically gather statistics

Answer

They store the actual table data

Answer

They require the Oracle Enterprise Manager

Question 12

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 13

What is the purpose of the 'ASH' report?

Accepted Answer

To analyze short-term performance. ASH reports analyze transient problems by looking at recent active session history.

Answer

To backup the database

Answer

To analyze long-term trends

Answer

To manage user accounts

Question 14

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 15

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