OCP Performance Tuning And Diagnostics Practice Test 3 — 15 Questions

Question 1

You are seeing 'enq: TX - row lock contention' in your performance reports. What does this mean?

Accepted Answer

Sessions are competing for the same row locks. TX lock contention indicates that multiple sessions are trying to update the same rows.

Answer

The library cache is full

Answer

The tablespace is full

Answer

The database is waiting for I/O

Question 2

What does the 'Elapsed Time' in a SQL statement represent?

Accepted Answer

The total duration of the SQL execution. It is the total wall-clock time spent executing the statement from start to finish.

Answer

The time spent on CPU only

Answer

The time since the SQL was parsed

Answer

The time spent waiting for I/O

Question 3

You notice that a specific query is performing very slowly due to excessive 'direct path read' waits. What is the most likely cause?

Accepted Answer

The query is performing a full table scan. Direct path reads often occur during full table scans when the data is not in the buffer cache.

Answer

The library cache is too small

Answer

The PGA is too large

Answer

The redo logs are too small

Question 4

Which of these is the most effective way to address performance issues caused by suboptimal plans?

Accepted Answer

SQL Plan Management. SQL Plan Management is specifically designed to manage and enforce optimal plans.

Answer

Dropping and recreating the database

Answer

Increasing the number of CPUs

Answer

Disabling indexes

Question 5

When using SQL Tuning Advisor, what is a 'SQL Tuning Set' (STS)?

Accepted Answer

A container for SQL statements and their metadata. An STS is a collection of SQL statements with their associated execution context.

Answer

A backup set of the database

Answer

A list of database users

Answer

A set of database indexes

Question 6

What is the primary benefit of using a SQL Profile?

Accepted Answer

They improve the accuracy of the optimizer. SQL Profiles improve the optimizer's choices by providing better selectivity and cardinality estimates.

Answer

They fix the execution plan forever

Answer

They speed up physical I/O

Answer

They automatically gather new statistics

Question 7

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 8

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 9

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 10

Which feature allows you to capture SQL statements from the cursor cache and load them into a SQL Tuning Set?

Accepted Answer

DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET. DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET is the procedure used to pull queries from memory.

Answer

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE

Answer

DBMS_STATS.GATHER_SYSTEM_STATS

Answer

DBMS_WORKLOAD_REPOSITORY.EXTRACT_SQL

Question 11

What does the 'SQL ID' in an Oracle database represent?

Accepted Answer

A unique identifier for a SQL statement. The SQL ID is a unique hash of the SQL text, used to identify statements in the library cache.

Answer

A table name

Answer

A user ID

Answer

An execution plan version

Question 12

Which THREE of the following are components of the Oracle performance tuning lifecycle?

Accepted Answer

Implementing a solution. Tuning is an iterative process involving identification, analysis, and implementation.

Answer

Deleting all users

Answer

Reinstalling the database

Question 13

Which TWO views can be used to monitor current session performance?

Accepted Answer

V$SESSION_WAIT. V$SESSION and V$SESSION_WAIT are standard tools for real-time monitoring.

Answer

V$CONTROLFILE

Answer

DBA_TABLES

Answer

DBA_USERS

Question 14

You are tuning a query that is performing poorly due to high 'buffer busy waits'. What is the likely cause?

Accepted Answer

Multiple sessions accessing the same blocks. Buffer busy waits occur when multiple sessions try to modify the same block at the same time.

Answer

PGA memory exhausted

Answer

Library cache contention

Answer

Excessive disk I/O

Question 15

Which THREE factors influence the optimizer's choice of an execution plan?

Accepted Answer

Optimizer statistics. The optimizer considers statistics, system parameters, and schema objects.

Answer

The current time of day

Answer

User password strength