Get 1z0-084 Dumps : Verified Oracle Database 19c: Performance Management and Tuning

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

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

To reduce the number of hard parses due to several almost identical SQL statements, you can take the following actions:

• C (Correct):Increasing the size of the library cache can help reduce hard parses by providing more memory to store more execution plans. This allows SQL statements to be shared more effectively.
• E (Correct):Setting theCURSOR_SHARINGparameter toFORCEwill cause Oracle to replace literals in SQL statements with bind variables, which can significantly reduce the number of hard parses by making it more likely that similar SQL statements will share the same execution plan.

The other options do not directly impact the number of hard parses:

• A (Incorrect):Creating the KEEP cache and caching tables accessed by the SQL statements can improve performance for those tables, but it does not directly reduce the number of hard parses.
• B (Incorrect):Creating the RECYCLE cache and caching tables accessed by the SQL statements can make it more likely that objects will be removed from the cache quickly, which does not help with hard parse issues.
• D (Incorrect):SettingOPTIMIZER_CAPTURE_SQL_PLAN_BASELINEStoTRUEcan help stabilize SQL execution plans but will not reduce the number of hard parses. This parameter is used to automatically capture SQL plan baselines for repeatable SQL statements, which can prevent performance regressions due to plan changes.

References:

• Oracle Database Performance Tuning Guide:Minimizing Hard Parses
• Oracle Database SQL Tuning Guide:CURSOR_SHARING

The ORA-15505 error indicates that the instance encountered errors while trying to access the specified directory. This could be due to:

A. Insufficient privileges: The user attempting to start the workload capture might not have the required permissions to execute the DBMS_WORKLOAD_CAPTURE package or to read/write to the directory specified.

E. Accessibility: The database instance may not be able to access the directory due to issues such as incorrect directory path, directory does not exist, permission issues at the OS level, or the directory being on a file system that's not accessible to the database instance.

References:

• Oracle Database Error Messages, 19c
• Oracle Database Administrator’s Guide, 19c

DB time includes the time spent on user and background processes. It can be greater than the elapsed time because it accumulates the active time of all the processes. For example, if two sessions are each active for 2 seconds at the same time, DB time would accumulate 4 seconds, while the elapsed time would be only 2 seconds.References:

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