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

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

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

The AWR excerpt points to excessive physical reads on the SALES table and index, suggesting the need for optimizing table storage and access.

Partitioning the SALES table (A) can reduce 'db file sequential read' waits by breaking down the large SALES table into smaller, more manageable pieces. This can localize the data and reduce the I/O necessary for query operations.

Compressing the SALES table (D) can also help reduce I/O by minimizing the amount of data that needs to be read from disk. This can also improve cache utilization and reduce the 'db file sequential read' waits.

References:

• Oracle Database VLDB and Partitioning Guide, 19c
• Oracle Database Administrator’s Guide, 19c

These changes are recommended based on Oracle's best practices for managing large tables and reducing I/O waits, ensuring better performance and efficiency.

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

To transport performance data from an Oracle Database Standard Edition, which uses Statspack, to an Enterprise Edition database, which uses AWR, you must consider the compatibility of data structures and repository schemas between these tools. The recommended method is:

• D (Correct):Export the data using theexputility with a parameter file appropriate for Statspack (likespuexp.par) from the Statspack repository and import it into a dedicated Statspack schema on the destination. Since Statspack and AWR use different schemas, it's not recommended to import Statspack data directly into the AWR repository.

The other options are incorrect because:

• A (Incorrect):expdpis not designed to export from Statspack, andawrloadis intended for loading from an AWR export file, not a Statspack export.
• B (Incorrect):Althoughexpdpandimpdpare used for exporting and importing data, the AWR repository schema is different from the Statspack schema, so importing Statspack data directly into the AWR repository is not recommended.
• C (Incorrect):Usingexpdpto export from Statspack and then importing directly into the AWR repository is not the correct approach due to the schema differences between Statspack and AWR.

References:

• Oracle Database Performance Tuning Guide:Migrating from Statspack to AWR

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

In an adaptive execution plan, the Optimizer makes runtime decisions between nested loop and hash joins using a statistics collector. The collector is a row source that collects statistics about the rows it processes and can adapt the plan based on the number of rows processed.

References:

• Oracle Database SQL Tuning Guide, 19c

TheDBMS_STATS.GATHER_DATABASE_STATSprocedure is used to gather statistics for all schema objects in the database that do not have up-to-date statistics. These statistics are essential for the optimizer to make informed decisions about the most efficient way to execute a query. The procedure collects statistics such as table and column statistics, index statistics, and system statistics, which are all used in the execution plan generation.

References:

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