Free Oracle 1z0-084 Practice Exam with Questions & Answers | Set: 2

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.

References:

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

When MEMORY_TARGET is set to a nonzero value, Oracle automatically manages the memory allocation between the System Global Area (SGA) and the Program Global Area(PGA). If MEMORY_MAX_TARGET is not explicitly set, Oracle will behave in the following manner:

• MEMORY_MAX_TARGET will default to the value of MEMORY_TARGET, assuming the platform allows for the value of MEMORY_TARGET to be increased dynamically. This means that MEMORY_TARGET represents both the initial allocation and the maximum limit for the dynamically managed memory unless MEMORY_MAX_TARGET is specified differently.
• If MEMORY_TARGET is set to a value that is less than the sum of the current values of SGA_TARGET and PGA_AGGREGATE_TARGET, Oracle will use the higher sum as the default value for MEMORY_MAX_TARGET to ensure that there is adequate memory for both areas. The database instance will not start if MEMORY_TARGET is not sufficient to accommodate the combined SGA and PGA requirements.

References

• Oracle® Database Administrator's Guide 19c: Automatic Memory Management
• Oracle® Database Performance Tuning Guide 19c: Using Automatic Memory Management

When both a SQL Profile and a SQL Plan Baseline are in place, the SQL Profile has a stronger preference and the optimizer is more likely to choose the execution plan from the SQL Profile.

C. A SQL Profile is generally more influential than a SQL Plan Baseline because it contains additional statistics and directives that help the optimizer to generate a more efficient execution plan. If both exist, the optimizer will use the profile's plan unless the baseline's plan is proven to be better through the SQL performance monitoring process.

E. SQL Plan Baselines must be accepted before they can be used by the optimizer. If a SQL Plan Baseline is not accepted, it will not be considered for generating the execution plan. Therefore, the presence of an unaccepted SQL Plan Baseline will not automatically force the optimizer to use its plan.

References:

• Oracle Database SQL Tuning Guide, 19c
• Oracle Database Administrator’s Guide, 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

Monitoring of Database Operations requires that theSTATISTICS_LEVELparameter be set toALLandCONTROL_MANAGEMENT_PACK_ACCESSbe set toDIAGNOSTIC+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.

References:

• Oracle Database Reference:STATISTICS_LEVEL
• Oracle Database Licensing Information User Manual:Oracle Database Management Packs

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