SQL Performance Analyzer (SPA) can be used to assess the impact of different types of changes on SQL performance. These changes can include database initialization parameters, which can significantly affect how SQL statements are executed and therefore their performance. SPA allows you to capture a workload before and after the change and compare the performance of each SQL statement. Database consolidation, including moving to pluggable databases (PDBs), can also affect SQL performance. SPA can analyze the SQL workload to see how consolidation impacts performance, by comparing metrics such as elapsed time and CPU time before and after the consolidation. Reference: Oracle Database SQL Tuning Guide, 19c Oracle Database Performance Tuning Guide, 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 the CURSOR_SHARING parameter to FORCE will 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): Setting OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE can 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. Reference: Oracle Database Performance Tuning Guide: Minimizing Hard Parses Oracle Database SQL Tuning Guide: CURSOR_SHARING Thank you for your visit.

In the V$SESSION view, Oracle provides information about the session waits: B) When the WAIT_TIME column 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, the WAIT_TIME column shows the time the session spent waiting for the last wait event. If the STATE column is showing "WAITED KNOWN TIME", it means the session is not currently waiting, but it indicates the time for which it had waited. Reference: Oracle Database Reference, 19c Oracle Database Performance Tuning Guide, 19c

When ASMM is disabled, the sizes of the automatically managed SGA components remain at their current values. ASMM is controlled by the SGA_TARGET parameter. If SGA_TARGET is set to a non- zero value, ASMM is enabled and Oracle will automatically manage the sizes of the various SGA components. When ASMM is disabled, by setting SGA_TARGET to zero, the SGA components that were automatically sized will retain their current sizes rather than being reset to their original user- defined values. The overall size of the SGA remains the same unless manually changed by modifying individual component sizes or SGA_MAX_SIZE. Reference: Oracle Database Administration Guide, 19c Oracle Database Performance Tuning Guide, 19c

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. Reference Oracle® Database Administrator's Guide 19c: Automatic Memory Management Oracle® Database Performance Tuning Guide 19c: Using Automatic Memory Management

The DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE procedure 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. The parameters of the

CREATE_BASELINE_TEMPLATE procedure 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.

Reference:

Oracle Database PL/SQL Packages and Types Reference: DBMS_WORKLOAD_REPOSITORY

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 are more suitable as they contain more

granular data for sessions that were active during the period of interest.

Reference:

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. Reference: 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

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. Reference Oracle Database 19c Performance Tuning Guide - Buffer Cache Hit Ratio Oracle Database 19c Performance Tuning Guide - v$db_cache_advice

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 from running and thus

reduce

space

usage

in

the

SYSAUX

tablespace.

Reference:

Oracle Database PL/SQL Packages and Types Reference, 19c