1z0-084 Oracle Database 19c: Performance Management and Tuning Questions and Answers

Given that index range scan operations onIX_SALES_TIME_IDare slower due to an increase in buffer gets, the aim is to improve the efficiency of the index access. In this scenario:

• B (Correct):Re-creating the index usingADVANCED COMPRESSIONcan reduce the size of the index, which can lead to fewer physical reads (reduced I/O) and buffer gets when the index is accessed, as more of the index can fit into memory.

The other options would not be appropriate because:

• A (Incorrect):Re-creating theSALEStable sorted in order of the index might not address the issue of excessive buffer gets. Sorting the table would not improve the efficiency of the index itself.
• C (Incorrect):Using the columns inIX_SALES_TIME_IDas a hash partitioning key for theSALEStable is more relevant to data distribution and does not necessarily improve index scan performance.
• D (Incorrect):Hash partitioning the index is generally used to improve the scan performance in a parallel query environment, but it may not reduce the number of buffer gets in a single-threaded query environment.

References:

• Oracle Database SQL Tuning Guide:Managing Indexes
• Oracle Database SQL Tuning Guide:Index Compression

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

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

The CONCATENATION step in an execution plan is introduced during the SQL Transformation phase. This phase is part of the optimizer's query transformations which can include various techniques to rewrite the query for more efficient execution. The CONCATENATION operation is used to combine the results of two separate SQL operations, typically when there is an OR condition in the WHERE clause, as seen in the provided query.

References:

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

Big Table caching is a feature that allows frequently accessed large tables to be cached in memory to improve I/O performance. From the parameter settings provided, Big Table caching is not occurring becauseDB_BIG_TABLE_CACHE_PERCENT_TARGETis set to 10, which is the minimum threshold for enabling the feature, but the size of the cache is too small for the big tables to be effectively cached.

To enable Big Table caching, one of the following actions could be taken:

• C (Correct):IncreasingDB_BIG_TABLE_CACHE_PERCENT_TARGETto at least 25. This action would allocate a larger percentage of the buffer cache for storing big tables, which could allow for caching large tables and thus improve I/O performance.
• D (Correct):IncreasingDB_CACHE_SIZEto 1G. Since the size of the buffer cache is a determining factor for how much data can be cached, increasing this parameter would provide more memory space for big tables to be cached.

Options A, B, E, and F will not enable Big Table caching because:

• A:IncreasingDB_BIG_TABLE_CACHE_PERCENT_TARGETto 50 without adjusting the overall size of the cache might still not be sufficient if theDB_CACHE_SIZEis not large enough to hold the big tables.
• B:SettingDB_KEEP_CACHE_SIZEto at least 50M only specifies a separate buffer pool for objects with the KEEP cache attribute and does not affect Big Table caching.
• E:andF:Changing thePARALLEL_DEGREE_POLICYtoADAPTIVEorAUTOinfluences the behavior of parallel execution but does not directly enable or influence Big Table caching.

References:

• Oracle Database Performance Tuning Guide:Big Table Caching
• Oracle Database Reference:DB_BIG_TABLE_CACHE_PERCENT_TARGET
• Oracle Database Reference:DB_CACHE_SIZE

To reduce the impact of database I/O without increasing the size of the buffer cache and without modifying SQL statements, you can use table compression. Among the given options,ROW STORE COMPRESS ADVANCEDis the most suitable form of table compression to satisfy this requirement.

Advanced row compression (ROW STORE COMPRESS ADVANCED) is designed to work well with all supported types of data, whether it's OLTP or data warehouse environments. It offers a higher level of compression than basic table compression (ROW STORE COMPRESS BASIC)without significant overhead during DML operations. This feature can help reduce the amount of I/O required to retrieve data by storing it more efficiently on disk.

• A, B, D:WhileCOLUMN STORE COMPRESS FOR QUERY HIGHandROW STORE COMPRESSare both valid compression types,COLUMN STORE COMPRESS FOR QUERY HIGHapplies to the In-Memory column store and is not available in all versions and editions, andROW STORE COMPRESSis less advanced thanROW STORE COMPRESS ADVANCED.

References:

• Oracle Database Concepts Guide:Table Compression
• Oracle Database Performance Tuning Guide:Row Compression

TheDBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATEprocedure is used to create a repeating baseline template in the Automatic Workload Repository (AWR). This template will generate baselines for a specified duration of time on a repeating schedule. Theparameters of theCREATE_BASELINE_TEMPLATEprocedure include the start and end times, as well as the day of the week and hour in the day when the baseline should be captured.

Given that the command specifies a repeating baseline every Monday at 5 PM with a duration of 3 hours and it expires after 30 days, the number of baselines generated by this command that you can have at any given time depends on how many Mondays fall within the most recent 30-day period.

Since the maximum number of Mondays that can occur within any 30-day period is 5 (four to five weeks), but considering the baseline has a duration of 3 hours and starts every Monday at 5 PM, only one baseline for each Monday can exist at a time. However, since baselines are preserved for 30 days, you could have multiple instances of Monday baselines preserved at a time.

• A (Incorrect):There can be more than one baseline at a time because the template will generate a baseline for every Monday during the 30-day expiration period.
• B (Incorrect):There will be more than three baselines because the template creates a baseline for every Monday within the 30-day expiration period.
• C (Correct):Over a 30-day period, considering the duration of the baselines and their frequency, you could have up to a maximum of 52 baselines if you consider the entire year.
• D (Incorrect):There is no option that restricts the number of baselines to 5 specifically, the answer relies on the calculation of how many baselines can exist over a period of time considering their expiration.

References:

• Oracle Database PL/SQL Packages and Types Reference:DBMS_WORKLOAD_REPOSITORY

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

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

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

When an ORA-00060 deadlock error occurs, detailed information about the error and the deadlock graph are dumped into the alert log. This log contains a trace file name that you can use to find additional detailed information about the sessions involved in the deadlock and the SQL statements they were executing.References:

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

The Oracle In-Memory (IM) column store feature enhances the performance of databases by providing a fast columnar storage format for analytical workloads while also potentially benefiting OLTP workloads.

• C (True):It can improve OLTP workload performance by providing a faster access path for full table scans and reducing the need for indexes in certain scenarios, as the In-Memory store allows for efficient in-memory scans.
• E (True):The In-Memory column store does not require all database data to fit in memory. It can be used selectively for performance-critical tables or partitions, and Oracle Database will manage the population and eviction of data as needed.
• F (True):In-Memory column store can significantly improve performance for queries joining several tables, especially when bloom filters are used, as they are highly efficient with the columnar format for large scans and join processing.

The other options provided are not correct in the context of the In-Memory column store:

• A (False):While In-Memory column store is designed for analytical queries rather than caching results of function evaluations, it does not specifically avoid improving performance for queries using cached results of function evaluations.
• B (False):In-Memory column store can improve the performance of queries that use join groups, which can be used to optimize joins on columns from different tables.
• D (False):In-Memory column store can improve the performance of queries using expressions, including user-defined virtual columns, because it supports expression statistics which help in optimizing such queries.

References:

• Oracle Database In-Memory Guide:In-Memory Column Store in Oracle Database
• Oracle Database In-Memory Guide:In-Memory Joins
• Oracle Database In-Memory Guide:In-Memory Aggregation