Oracle Database Administration II — Question 85

Which two are true about poorly performing SQL statements and their tuning in Oracle database instances? (Choose two.)

Answer options

• A. SQL statements performing many physical I/Os can always have their performance improved by creating indexes.
• B. SQL statements doing only logical reads never require tuning because they do no physical I/O.
• C. The solution that best optimizes one SQL statement can degrade the performance of others.
• D. SQL statements doing joins always do more physical I/O than single table queries.
• E. Poorly performing SQL statements can be tuned automatically by the Oracle server.

Correct answer: C, E

Explanation

Option C is correct because optimizing one SQL statement can lead to a decrease in performance for others due to resource contention or changes in execution plans. Option E is also correct as Oracle has features that allow for automatic tuning of poorly performing SQL statements. The other options are false; not all SQL statements with many physical I/Os can be improved by indexes, logical reads can still require tuning under certain circumstances, and joins do not always result in more physical I/O compared to single table queries.