1z0-084 Exam Dumps - Oracle Certification Questions and Answers

During a soft parse, Oracle checks the shared SQL area to see if an incoming SQL statement matches one already in the shared pool. This operation includes syntax and semantic checks. The syntax check ensures the statement is properly formed, and the semantic check confirms that all the objects referenced in the SQL statement exist and that the user has the necessary privileges to access them.References:

• Oracle Database Concepts, 19c
• Oracle Database SQL Tuning Guide, 19c

The Oracle Optimizer statistics advisor, which is part of the automated tasks framework, can be disabled using the DBMS_AUTO_TASK_ADMIN package. This will prevent it fromrunning and thus reduce space usage in the SYSAUX tablespace.References:

• Oracle Database PL/SQL Packages and Types Reference, 19c

The optimizer_adaptive_statistics parameter, when set to TRUE, enables the optimizer to use adaptive statistics, such as SQL Plan Directives, to help improve plans by automatically adjusting them based on the actual execution statistics.

References:

• Oracle Database SQL Tuning Guide, 19c

Baselines are typically taken during the production phase of the application lifecycle. They provide a snapshot of performance metrics under normal operating conditions which can be used for comparison against future performance. Baselines are essential for understanding how the system performs under its typical workload and for detecting deviations from this expected performance over time, especially after changes like migrations, upgrades, or significant changes in user activity.

References

• Oracle Database 19c Performance Tuning Guide - Managing Performance Through Baselines

A. WhenCursor_sharingis set toFORCE, Oracle tries to avoid hard parses by replacing literals in SQL statements with bind variables, even if the original statement didn't include bind variables. This can lead to the use of a single execution plan for multiple executions of a statement with different literal values, which might not be optimal for all executions.

D. Settingcursor_sharingtoEXACTensures that SQL statements must match exactly for them to share a cursor. This setting prevents the use of Adaptive Cursor Sharing (ACS) since ACS relies on the ability to share cursors among similar statements that differ only in their literal values. WithEXACT, there's no cursor sharing for statements with different literals, hence no opportunity for ACS to operate.

References:

• Oracle Database SQL Tuning Guide, 19c
• Oracle Database Reference, 19c

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