Oracle 1z0-084 Test Dumps

D.   Active Session History report

Answer DescriptionExplanation:

The Active Session History (ASH) report is a tool that provides detailed information about active sessions for the time period specified. Since the AWR snapshot interval is one hour and does not capture the granularity needed for this issue, ASH reports aremore suitable as they contain more granular data for sessions that were active during the period of interest.

References:

Oracle Database Performance Tuning Guide, 19c

A.   Parent and child latches


E.   Enqueue statistics

Answer DescriptionExplanation:

Statspack is a performance diagnostic tool provided by Oracle prior to the introduction of the Automatic Workload Repository (AWR). At snap level 6, Statspack captures the following types of statistics:

A (Correct):Parent and child latches are captured. Latch statistics provide information about contention for latches, which are low-level serialization mechanisms used by Oracle. E (Correct):Enqueue statistics, which provide information on the waits for locks that manage the concurrency between users.

F (Correct):Segment-level statistics, which provide detailed information on database segments such as tables, indexes, etc., to identify I/O and contention issues.

C (Incorrect):While optimizer execution plans are an essential aspect of performance tuning, detailed execution plan capture is not part of the Statspack report at level 6.

D (Incorrect):Plan usage data refers to how frequently a plan is being used, which is more associated with AWR and not typically captured in Statspack reports.

References:

Oracle Database Performance Tuning Guide:Using Statspack

A.   One or more results were aged out of the result cache.


B.   result_cache_max_size is set dynamically to 0.

Answer DescriptionExplanation:

The SQL Query Result Cache stores the results of queries and PL/SQL function calls for reuse. However, entries in the result cache can be invalidated or lost under certain conditions:

A. Results can be aged out of the cache when the cache becomes full and new results need to be stored. This process ensures that the cache does not exceed its allocated memory and that it contains the most recently used entries.

B. Setting theRESULT_CACHE_MAX_SIZEparameter to 0 will effectively disable the result cache and all cached results will be lost, as Oracle will no longer allocate any memory to the result cache.

References:

Oracle Database Performance Tuning Guide, 19c

A.   Setting Cursor_sharing to FORCE can result in a plan that is suboptimal for the majority of values bound to a bind variable when executing a cursor with one or more bind variables.


D.   Setting cursor_sharing to EXACT prevents Adaptive Cursor Sharing from being used.

Answer DescriptionExplanation:

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

C.   enq: HW - contention

Answer DescriptionExplanation:

When sessions concurrently insert data into a table with an LOB column and one session needs to allocate a new extent because it cannot find available space, the wait event associated with this contention is "enq: HW - contention". The HW stands for High Water Mark which is related to space allocation in the database segment. When asession needs to allocate a new extent, it may raise this wait event in other sessions that are also attempting to allocate space in the same LOB segment.

References

Oracle Database 19c Reference Guide - enq: HW - contention

C.   The execution plan is the one enforced by the SQL Profile.


E.   The SQL Plan Baseline must be accepted in order to be used for the execution plan.

Answer DescriptionExplanation:

When both a SQL Profile and a SQL Plan Baseline are in place, the SQL Profile has a stronger preference and the optimizer is more likely to choose the execution plan from the SQL Profile.

C. A SQL Profile is generally more influential than a SQL Plan Baseline because it contains additional statistics and directives that help the optimizer to generate a more efficient execution plan. If both exist, the optimizer will use the profile's plan unless the baseline's plan is proven to be better through the SQL performance monitoring process.

E. SQL Plan Baselines must be accepted before they can be used by the optimizer. If a SQL Plan Baseline is not accepted, it will not be considered for generating the execution plan. Therefore, the presence of an unaccepted SQL Plan Baseline will not automatically force the optimizer to use its plan.

References:

Oracle Database SQL Tuning Guide, 19c

Oracle Database Administrator’s Guide, 19c

D.   All SGA components retain their current sizes at the time of disabling.


E.   The SGA size remains unaffected after disabling ASMM.

Answer DescriptionExplanation:

When ASMM is disabled, the sizes of the automatically managed SGA components remain at their current values. ASMM is controlled by theSGA_TARGETparameter. IfSGA_TARGETis set to a non-zero value, ASMM is enabled and Oracle will automatically manage the sizes of the various SGA components. When ASMM is disabled, by settingSGA_TARGETto zero, the SGA components that were automatically sized will retain their current sizes rather than being reset to their original user-defined values. The overall size of the SGA remains the same unless manually changed by modifying individual component sizes orSGA_MAX_SIZE.

References:

Oracle Database Administration Guide, 19c
Oracle Database Performance Tuning Guide, 19c