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_MONITOR provides real-time monitoring of SQL statements that have consumed significant CPU or I/O resources.

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

The RESULT_CACHE hint instructs Oracle to store query results in the result cache area of the SGA so repeated identical queries can be served without re-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

Using DBMS_STATS.GATHER_DATABASE_STATS with options=>'GATHER STALE' collects statistics only for objects whose statistics are missing or exceed the 10% staleness threshold.

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

Nested loops join retrieves each row from the outer (driving) table and performs a corresponding lookup in the inner table, typically using an index, making it efficient for small row counts.

Question 5

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

Accepted Answer

7 days

Answer

By default, Oracle AWR retains performance snapshots for 7 days before purging them automatically.

(OCP) Oracle Certified Professional Practice Test

(OCP) Oracle Certified Professional Practice Test

OCP Performance Tuning & Optimization 2