OCP Performance Tuning & Optimization

Question 1

Which of the following is a common method for tuning SQL queries in Oracle?

Accepted Answer

Indexing and optimizing joins

Answer

Increasing the CPU speed

Answer

Adding unnecessary indexes

Answer

Using subqueries only

Question 2

What is the main purpose of Oracle's Automatic Workload Repository (AWR)?

Accepted Answer

To gather performance data for tuning

Answer

To store backup logs

Answer

To monitor user activity

Answer

To collect data for system recovery

Question 3

How can Oracle SQL Developer help with performance tuning?

Accepted Answer

By identifying and analyzing slow-running queries

Answer

By automatically rewriting SQL code

Answer

By performing manual backups

Answer

By providing network diagnostics

Question 4

What is the Oracle instance parameter 'OPTIMIZER_MODE'?

Accepted Answer

It influences the query optimization strategy

Answer

It controls the memory allocation

Answer

It determines the level of parallelism in queries

Answer

It specifies the data retention period

Question 5

What is an Oracle database index used for?

Accepted Answer

To speed up query performance

Answer

To store data in sorted order

Answer

To perform data backups

Answer

To store database logs

Question 6

Which of the following is a way to improve Oracle database performance by reducing disk I/O?

Accepted Answer

Using large memory caches and indexes

Answer

Adding more data files

Answer

Disabling parallel query execution

Answer

Increasing disk storage size

Question 7

What is the effect of using parallel queries in Oracle?

Accepted Answer

It speeds up query processing by using multiple CPUs

Answer

It reduces network traffic

Answer

It optimizes memory usage

Answer

It reduces the number of database connections

Question 8

What does the Oracle parameter 'SORT_AREA_SIZE' control?

Accepted Answer

The memory allocated for sorting operations

Answer

The maximum number of concurrent users

Answer

The size of the redo log buffer

Answer

The memory allocated for shared pools

Question 9

What does 'EXPLAIN PLAN' in Oracle do?

Accepted Answer

It shows the query's execution plan

Answer

It generates a backup of the database

Answer

It runs the SQL query without optimization

Answer

It converts SQL queries into PL/SQL