Curious about Actual Oracle Database (1Z0-084) Exam Questions?

Correct : C

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 a session 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.

Reference

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

Correct : A

Monitoring of Database Operations requires that the STATISTICS_LEVEL parameter be set to ALL and CONTROL_MANAGEMENT_PACK_ACCESS be set to DIAGNOSTIC+TUNING. These settings enable all the advisory features and automatic tuning features within the Oracle Database, including the Automatic Workload Repository (AWR), Automatic Database Diagnostic Monitor (ADDM), and the full functionality of the SQL Tuning Advisor and SQL Access Advisor, which are components of the Diagnostic and Tuning packs.

STATISTICS_LEVEL=ALL: This setting enables the collection of all system statistics for problem detection and self-tuning purposes.

CONTROL_MANAGEMENT_PACK_ACCESS=DIAGNOSTIC+TUNING: This grants access to both the Diagnostic Pack and the Tuning Pack, which are essential for detailed performance monitoring and tuning capabilities.

Oracle Database Reference: STATISTICS_LEVEL

Oracle Database Licensing Information User Manual: Oracle Database Management Packs

Correct : A

The right time to stop tuning an Oracle database is often determined by the point of diminishing returns - when the cost of further tuning (in terms of time, resources, or money) exceeds the performance benefits gained. This is often related to the budget allocated for performance tuning.

A (Correct): When the allocated budget for performance tuning has been exhausted, it may be time to stop tuning unless the benefits of further tuning justify requesting additional budget.

B (Incorrect): Eliminating all concurrency waits from the Top 10 is an unrealistic goal since some waits are inevitable and can occur due to application design, which might not be possible to eliminate completely.

C (Incorrect): The buffer cache and library cache hit ratio being above 95% does not necessarily indicate that the database is fully optimized. Hit ratios are not reliable indicators of database performance and should not be used as sole criteria to end tuning efforts.

D (Incorrect): Having I/O less than 10% of DB time is not a definitive indicator to stop tuning. It is essential to consider the overall performance goals and whether they have been met rather than focusing solely on I/O metrics.

Oracle Database Performance Tuning Guide: Introduction to Performance Tuning

Oracle Database 2 Day + Performance Tuning Guide: Understanding the Tuning Process

Correct : A, B

In this scenario, creating an index and generating frequency histograms are two methods that can potentially improve performance:

A (Correct): Generating frequency histograms on the CITY_ID column can help the optimizer make better decisions regarding the execution plan, especially if the data distribution is skewed. Histograms provide the optimizer with more detailed information about the data distribution in a column, which is particularly useful for columns with non-uniform distributions.

B (Correct): Creating an index on the CITY_ID column would speed up queries that filter on this column, especially if it's used frequently in the WHERE clause as a filter. An index would allow for an index range scan instead of a full table scan, reducing the I/O and time needed to execute such queries.

C (Incorrect): While SQL profiles can be used to improve the performance of specific SQL statements, they are usually not the first choice for such a problem, and creating a profile does not replace the need for proper indexing or statistics.

D (Incorrect): Forcing the subquery to use dynamic sampling might not provide a consistent performance benefit, especially if the table statistics are not representative or are outdated. However, dynamic sampling is not as effective as having accurate statistics and a well-chosen index.

E (Incorrect): Adaptive plans can adjust the execution strategy based on the conditions at runtime. While they can be useful in certain scenarios, in this case, creating an index and ensuring accurate statistics would likely provide a more significant performance improvement.

Oracle Database SQL Tuning Guide: Managing Optimizer Statistics

Oracle Database SQL Tuning Guide: Using Indexes and Clusters

Correct : D

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 the exp utility with a parameter file appropriate for Statspack (like spuexp.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): expdp is not designed to export from Statspack, and awrload is intended for loading from an AWR export file, not a Statspack export.

B (Incorrect): Although expdp and impdp are 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): Using expdp to 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.

Oracle Database Performance Tuning Guide: Migrating from Statspack to AWR