OCP Performance Tuning And Diagnostics Practice Test 4 — 15 Questions

Question 1

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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 11

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 12

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

Question 13

Which TWO methods can be used to tune SQL statements?

Accepted Answer

Manual addition of optimizer hints. Option C (Manual addition of optimizer hints) is correct because optimizer hints are directives embedded in SQL statements (e.g., /*+ INDEX(...) */ or /*+ FULL(...) */) that influence the query optimizer's execution plan, directly tuning statement performance. Option E (SQL Tuning Advisor) is correct because it is an Oracle tool that analyzes a SQL statement, its execution plan, and statistics, then produces tuning recommendations such as SQL profiles, indexes, or restructured SQL. The other options do not tune SQL statements: increasing backups (A) is a backup/recovery activity, dropping the database (B) is a destructive DDL operation, and changing user roles (D) is a security/privilege change, none of which affect query execution plans or SQL performance.

Answer

Increasing the number of backups

Answer

Dropping the database

Answer

Changing user roles

Question 14

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 15

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