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

In Oracle Database, theV$SESSION_LONGOPSview provides insights into various operations within the database that are expected to take more than six seconds to complete. These include operations related to RMAN (Recovery Manager), such as database duplication tasks. This view displays information about the progress of these long-running operations, including the start time, elapsed time, and estimated time to completion.

When an RMAN session is performing a database duplication and is observed to be slower than usual, checking theV$SESSION_LONGOPSview can give an approximation ofwhen the RMAN operation might complete. This view includes fields likeTIME_REMAININGandELAPSED_SECONDSthat help in estimating the completion time of the operation based on its current progress.

References:

• Oracle Database Reference:V$SESSION_LONGOPS
• Oracle Database Backup and Recovery User's Guide:Monitoring RMAN Jobs

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

In theV$SESSIONview, Oracle provides information about the session waits:

B. When theWAIT_TIMEcolumn has a value of 0, it signifies that the session is currently waiting for a resource. This column represents the duration of the current or last wait.

C. If the session is not actively waiting, theWAIT_TIMEcolumn shows the time the session spent waiting for the last wait event. If theSTATEcolumn is showing "WAITED KNOWN TIME", it means the session is not currently waiting, but it indicates the time for which it had waited.

References:

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

When importing objects that used the In-Memory (IM) column store in their source database using Oracle Data Pump, the following statements are true:

• D (Correct):TheTRANSFORMclause can be used to alter object creation DDL during import operations. This can include adding theINMEMORYclause to tables that were not originally using the IM column store.
• F (Correct):The import operation can preserve theINMEMORYattributes of tables as they were at the time of export, effectively replicating the IM column store settings from the source database.

The other statements are not accurate in the context of Data Pump import:

• A (Incorrect):Data Pump does not give preference to the IM column store clauses at the tablespace level over table-level definitions unless explicitly specified by theTRANSFORMclause.
• B (Incorrect):While Data Pump can transport existingINMEMORYattributes, it is not mandatory. It is controlled by theINCLUDEorEXCLUDEData Pump parameters or theTRANSFORMclause.
• C (Incorrect):TheINMEMORY_CLAUSEparameter is not part of the Data Pump Export utility. To modify the IM column store clauses, you would use theTRANSFORMparameter during import, not export.
• E (Incorrect):Data Pump does not ignore the IM column store clause unless specifically instructed to do so via theEXCLUDEparameter.

References:

• Oracle Database Utilities:Data Pump Export
• Oracle Database Utilities:Data Pump Import

To configure and enable Database Smart Flash Cache, you must set the DB_FLASH_CACHE_SIZE parameter to reflect the combined size of the flash devices youintend to use for the cache. In this scenario, two flash devices are configured: /dev/sdj with 128G and /dev/sdk with 64G.

• Determine the combined size of the flash devices intended for the Database Smart Flash Cache. In this case, it's 128G + 64G = 192G.
• However, Oracle documentation suggests setting DB_FLASH_CACHE_SIZE to the exact sizes of the individual devices, separated by a comma when multiple devices are used.
• Modify the parameter in the database initialization file (init.ora or spfile.ora) or using an ALTER SYSTEM command. Here's the command for altering the system setting:

ALTER SYSTEM SET DB_FLASH_CACHE_SIZE='128G,64G' SCOPE=SPFILE;

• Since this is a static parameter, a database restart is required for the changes to take effect.
• Upon database startup, it will allocate the Database Smart Flash Cache using the provided sizes for the specified devices.

It is important to note that MEMORY_TARGET and MEMORY_MAX_TARGET parameters should be configured independently of DB_FLASH_CACHE_SIZE. They control the Oracle memory management for the SGA and PGA, and do not directly correlate with the flash cache configuration.

References

• Oracle Database 19c Documentation on Database Smart Flash Cache
• Oracle Support Articles and Community Discussions on DB_FLASH_CACHE_SIZE Configuration

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