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

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

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

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

In the provided code block, theDBMS_SERVER_ALERT.SET_THRESHOLDprocedure is used to set alert thresholds for the CPU time per call in Oracle Database. This procedure is a part of Oracle's Database Server Alert system, which monitors various metrics and generates alerts when certain thresholds are exceeded.

The parameters passed to theSET_THRESHOLDprocedure are as follows:

• The first parameterDBMS_SERVER_ALERT.CPU_TIME_PER_CALLspecifies the metric for which the threshold is being set, in this case, the CPU time consumed per database call.
• The second and third parametersDBMS_SERVER_ALERT.OPERATOR_GEand'8000'specify the warning threshold level and its value, respectively. However, these are not relevant to the answer as they are overridden by the critical threshold settings.
• The fourth and fifth parametersDBMS_SERVER_ALERT.OPERATOR_GEand'10000'set the critical threshold level and its value. This means that a critical alert will be generated when the CPU time per call exceeds 10000 microseconds.
• The remaining parameters specify the warning and critical alert intervals, the instance name, the object type, and the service name. These are not directly relevant to the behavior described in the options.

Thus, the correct answer is B, as the critical threshold for CPU time per call is set to 10000 microseconds, and the system is configured to issue a critical alert when this threshold is exceeded.

References:

• Oracle Database 19c documentation on theDBMS_SERVER_ALERT.SET_THRESHOLDprocedure, which details the parameters and usage of this procedure for setting alert thresholds within Oracle Database monitoring system.
• Oracle Database Performance Tuning Guide, which provides best practices and methodologies for monitoring and tuning Oracle Database performance, including the use of server alerts and thresholds.

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

Regarding space usage in temporary tablespaces, the following statements are true:

• A (Correct):When a global temporary table or a sort operation exceeds the available memory, Oracle Database allocates space in a temporary tablespace to store the temporary data or intermediate results.
• E (Correct):Using temporary tablespace groups can prevent insufficient temporary tablespace for sort operations by providing a collective pool of space from multiple temporary tablespaces, which can be used for user sorting operations.

The other options provided have inaccuracies:

• B (Incorrect):Oracle does not provide a mechanism for setting quotas on temporary tablespaces. Quotas can be set for permanent tablespaces but not for temporary ones.
• C (Incorrect):A sort operation may fail due to insufficient space, but Oracle will attempt to allocate space in the temporary tablespace dynamically. If no space can be allocated, an error is returned rather than a sort failure.
• D (Incorrect):If a session consumes all available temporary tablespace storage, Oracle will not hang the session; it will return an error to the session indicating that it has run out of temporary space.

References:

• Oracle Database Administrator's Guide:Managing Space for Schema Objects
• Oracle Database Concepts:Temporary Tablespaces

The SHRINK SPACE operation compacts the table, which can reduce fragmentation and thus improve performance for sequential reads of the table. This operation could improve full table scans, which are typically associated with db file sequential read wait events.

References:

• Oracle Database Administrator’s Guide, 19c

To produce a consolidated formatted trace file from multiple trace files generated by all clients for a single service, the combination oftrcsessandTKPROFutilities is used. Thetrcsessutility consolidates trace files based on specified criteria such as session, client identifier, or service name. This results in a single trace file that combines the desired tracing information. Next,TKPROFis used to format the output of the trace file generated bytrcsess, providing a readable summary of the trace, including execution counts, execution times, and SQL statement text along with execution plans.

Steps:

• Usetrcsessto combine trace files:
• UseTKPROFto format the consolidated trace file:

References:

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

To reduce the number of hard parses due to several almost identical SQL statements, you can take the following actions:

• C (Correct):Increasing the size of the library cache can help reduce hard parses by providing more memory to store more execution plans. This allows SQL statements to be shared more effectively.
• E (Correct):Setting theCURSOR_SHARINGparameter toFORCEwill cause Oracle to replace literals in SQL statements with bind variables, which can significantly reduce the number of hard parses by making it more likely that similar SQL statements will share the same execution plan.

The other options do not directly impact the number of hard parses:

• A (Incorrect):Creating the KEEP cache and caching tables accessed by the SQL statements can improve performance for those tables, but it does not directly reduce the number of hard parses.
• B (Incorrect):Creating the RECYCLE cache and caching tables accessed by the SQL statements can make it more likely that objects will be removed from the cache quickly, which does not help with hard parse issues.
• D (Incorrect):SettingOPTIMIZER_CAPTURE_SQL_PLAN_BASELINEStoTRUEcan help stabilize SQL execution plans but will not reduce the number of hard parses. This parameter is used to automatically capture SQL plan baselines for repeatable SQL statements, which can prevent performance regressions due to plan changes.

References:

• Oracle Database Performance Tuning Guide:Minimizing Hard Parses
• Oracle Database SQL Tuning Guide:CURSOR_SHARING

Database services in Oracle are used to manage how resources are allocated and how workloads are managed within the database. The features that use database services are:

• B (Correct):Database Resource Manager (DBRM) uses services to control resource allocation to different workloads. It ensures that resources are assigned to the most critical tasks first, based on the service associated with the workload.
• E (Correct):Oracle Scheduler can also utilize database services. Jobs in Oracle Scheduler can be assigned to different services to control resource allocation and prioritization.

The other features mentioned are related to SQL performance but do not directly utilize database services in the way Resource Manager and Scheduler do:

• A:Oracle Automatic Reoptimization is a feature that allows the database to automatically improve the execution plan of a SQL statement after it is executed, based on the actual performance metrics, but it does not directly use database services.
• C:Oracle SQL Performance Management involves various components of SQL tuning and monitoring, but it does not use database services to operate.
• D:Oracle SQL Tuning Advisor provides advice on how to tune SQL queries for better performance. While it can be used in conjunction with services for managing and analyzing workloads, it doesn't use services in its core functionality.

References:

• Oracle Database Administrator’s Guide:Administering Services
• Oracle Database Administrator's Guide:Managing Resources with Oracle Database Resource Manager
• Oracle Database Scheduler Developer’s Guide:Using the Scheduler

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

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

A. Statspack is a performance diagnostic tool that can help identify high CPU usage issues. High CPU time may indicate that SQL statements need to be tuned for better performance.

D. High wait times can often be reduced by instance tuning, such as adjusting database parameters or improving I/O performance.

F. DB Time is a cumulative time metric that includes the time spent by both user sessions and background processes executing database calls.References:

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

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

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

A high buffer cache hit ratio typically indicates that the database is effectively using the buffer cache and does not often need to read data from disk. However, this metric alone is not a reliable indicator of the I/O performance of the database for several reasons:

• Full table scans and fast full index scans (A) can bypass the buffer cache by design if the blocks are not deemed reusable shortly, which can impact the cache hit ratio.
• A high cache hit ratio (B) can be misleading if the database performance is poor due to other factors, such as inefficient queries or contention issues.
• The buffer cache advisory (C) is a more valuable tool for understanding the potential impact of different cache sizes on the database's I/O performance. It simulates scenarios with different cache sizes and provides a more targeted recommendation.
• The RECYCLE and KEEP buffer caches (D) are specialized caches designed for certain scenarios. While high hit ratios can be beneficial, they are not universally required; some workloads might not be significantly impacted by lower hit ratios in these caches.
• A lower cache hit ratio (E) does not necessarily mean poor I/O performance. In some cases, a system with a well-designed storage subsystem and efficient queries might perform well even with a lower cache hit ratio.

References

• Oracle Database 19c Performance Tuning Guide - Buffer Cache Hit Ratio
• Oracle Database 19c Performance Tuning Guide - v$db_cache_advice