Oracle Database 12c: Performance Management and Tuning — Question 6

Which two actions can reduce library cache latch contention for an OLTP application that repeatedly executes queries containing a mix of literals and bind variables? (Choose two.)

Answer options

• A. setting the OPEN_CURSORS parameter to hold a sufficient number of concurrently open cursors
• B. coding the application such that a frequently executed statement is parsed only once and executed repeatedly as required
• C. setting the CURSOR_SHARING parameter to EXACT
• D. avoiding the granting of privileges on objects associated with cursors during peak load
• E. enabling Automatic Memory Management and allocating at least 50% of the available memory for SHARED_POOL_SIZE
• F. configuring shared server connections

Correct answer: C, E

Explanation

The correct answers are C and E. Setting the CURSOR_SHARING parameter to EXACT helps to ensure that only identical literals are reused, reducing contention. Enabling Automatic Memory Management and allocating sufficient memory for SHARED_POOL_SIZE enhances the library cache's efficiency. The other options either do not directly address latch contention or pertain to different aspects of database performance.