Exactprep 1z0-084 Questions

Thecursor: pin S wait on Xwait event suggests contention for a cursor pin, which is associated with mutexes (a type of locking mechanism) that protect the library cache to prevent concurrent modifications. This issue can often be alleviated by deferring the invalidation of cursors until the end of the call to reduce contention. The correct command to use would be:

• C (Correct):ALTER SYSTEM SET CURSOR_INVALIDATION=DEFERRED;This setting defers the invalidation of dependent cursors until the end of the PL/SQL call, which can reduce thecursor: pin S wait on Xwait events.

The other options are incorrect in addressing this issue:

• A (Incorrect):SettingCURSOR_SHARINGtoFORCEmakes the optimizer replace literal values with bind variables. It doesn't address the contention for cursor pins directly.
• B (Incorrect):CURSOR_SPACE_FOR_TIME=TRUEaims to reduce the parsing effort by keeping cursors for prepared statements open. It may increase memory usage but does not directly resolvecursor: pin S wait on Xwaits.
• D (Incorrect):IncreasingSESSION_CACHED_CURSORScaches more session cursors but doesn't necessarily prevent the contention indicated by thecursor: pin S wait on Xwait events.

References:

• Oracle Database Reference:CURSOR_INVALIDATION
• Oracle Database Performance Tuning Guide:Reducing Cursor Invalidation