Oracle Database Administration 2022 Certified Professional — Question 29

An Oracle 19c database uses default values for all optimizer initialization parameters.
After a table undergoes partition maintenance, a large number of wait events occur for: cursor: pin S wait on X
Which command reduces the number of these wait events?

Answer options

• A. ALTER SYSTEM SET CURSOR_SHARING = FORCE;
• B. ALTER SYSTEM SET CURSOR_SPACE_FOR_TIME = TRUE;
• C. ALTER SYSTEM SET CURSOR_INVALIDATION = DEFERRED;
• D. ALTER SYSTEM SET SESSION_CACHED_CURSORS = 500;

Correct answer: C

Explanation

The correct answer is C because setting CURSOR_INVALIDATION to DEFERRED allows the database to postpone invalidating cursors, thus reducing the wait events associated with cursor pinning. The other options either do not directly address the issue of wait events related to cursor pinning or are not relevant to the specific problem at hand.