OCP Performance Tuning & Optimization 2

Question 1

Which Oracle view provides real-time statistics about SQL statements currently executing in the database?

Accepted Answer

V$SQL_MONITOR

Answer

V$SQL

Answer

V$SESSION_LONGOPS

Answer

V$ACTIVE_SESSION_HISTORY

Question 2

What is the purpose of the RESULT_CACHE hint in Oracle SQL?

Accepted Answer

Caches query results in the SGA for reuse by subsequent identical queries

Answer

Forces a full table scan

Answer

Pins the query plan in the cursor cache

Answer

Enables parallel query execution

Question 3

Which optimizer statistic gathering option should you use to gather only stale or missing statistics efficiently?

Accepted Answer

DBMS_STATS.GATHER_DATABASE_STATS with options=>'GATHER STALE'

Answer

DBMS_STATS.GATHER_DATABASE_STATS with cascade=>TRUE

Answer

DBMS_STATS.GATHER_STALE_STATS

Answer

DBMS_STATS.GATHER_SCHEMA_STATS with method_opt=>'FOR ALL COLUMNS'

Question 4

In Oracle, what does the 'NESTED LOOPS' join operation indicate in an execution plan?

Accepted Answer

Each row from the outer table drives a lookup into the inner table, ideal for small result sets with index access

Answer

Both tables are sorted and merged together

Answer

A hash table is built from the smaller table for probe operations

Answer

The query requires a parallel full table scan of both tables

Question 5

What is the AWR retention period default setting in Oracle Database?

Accepted Answer

7 days

Answer

3 days

Answer

14 days

Answer

30 days

Question 6

Which SQL*Plus command is used to display the execution plan stored in the PLAN_TABLE for a previously explained query?

Accepted Answer

DBMS_XPLAN.DISPLAY

Answer

SHOW PLAN

Answer

SELECT * FROM PLAN_TABLE

Answer

V$SQL_PLAN query

Question 7

What does the 'Cost' column in an Oracle execution plan represent?

Accepted Answer

An optimizer-estimated relative cost based on CPU and I/O estimates compared to a single-block I/O

Answer

Elapsed wall-clock time in milliseconds

Answer

The number of logical reads required

Answer

The actual number of rows processed