Pass the Oracle Database 19c 1z0-084 Questions and answers with ExamsMirror

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 the RESULT_CACHE_MAX_SIZE parameter 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

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.

References:

Oracle Database Performance Tuning Guide: Minimizing Hard Parses

Oracle Database SQL Tuning Guide: CURSOR_SHARING

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

The DBMS_STATS.GATHER_DATABASE_STATS procedure is used to gather statistics for all schema objects in the database that do not have up-to-date statistics. These statistics are essential for the optimizer to make informed decisions about the most efficient way to execute a query. The procedure collects statistics such as table and column statistics, index statistics, and system statistics, which are all used in the execution plan generation.

References:

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

Oracle Database Performance Tuning Guide, 19c

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.

References:

Oracle Database Performance Tuning Guide, 19c

The execution plan shows a full table access for the SALES table. To reduce the execution time, creating an index on SALES.TIME_ID would be beneficial as it would allow the database to quickly sort and retrieve the most recent sales without the need to perform a full table scan, which is I/O intensive and slower. By indexing TIME_ID, which is used in the ORDER BY clause, the optimizer can take advantage of the index to efficiently sort and limit the result set to the ten most recent sales.

B (Incorrect): Replacing FETCH FIRST with ROWNUM would not necessarily improve the performance unless there is an appropriate index that the optimizer can use to avoid sorting the entire result set.

C (Incorrect): There is no indication that the current statistics are inaccurate; hence, collecting new statistics may not lead to performance improvement.

D (Incorrect): While adaptive plans can provide performance benefits by allowing the optimizer to adapt the execution strategy, the main issue here is the lack of an index on the ORDER BY column.

E (Incorrect): Creating an index on SALES.CUST_ID could improve join performance but would not address the performance issue caused by the lack of an index on the ORDER BY column.

References:

Oracle Database SQL Tuning Guide: Managing Indexes

Oracle Database SQL Tuning Guide: Using Indexes and Clusters

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

Baselines are typically taken during the production phase of the application lifecycle. They provide a snapshot of performance metrics under normal operating conditions which can be used for comparison against future performance. Baselines are essential for understanding how the system performs under its typical workload and for detecting deviations from this expected performance over time, especially after changes like migrations, upgrades, or significant changes in user activity.

References

Oracle Database 19c Performance Tuning Guide - Managing Performance Through Baselines

When you enable statistics gathering for a specific service and module using DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE, the output is aggregated and can be viewed using the V$SERV_MOD_ACT_STATS dynamic performance view. This view contains the cumulative statistics of database activity broken down by service and module, which is exactly what you collect when executing the provided command.

B (Incorrect): While many types of trace files are located in the Diagnostic Destination directory ($ORACLE_BASE/diag), the aggregated statistics for services and modules are not written to trace files but are instead viewable through dynamic performance views.

C (Incorrect): The V$SERVICE_STATS view provides service-level statistics but does not provide the combined service/module-level breakdown.

D (Incorrect): The output of the PL/SQL block is not written to a file in the current working directory; it is stored in the data dictionary and accessible via dynamic performance views.

References:

Oracle Database PL/SQL Packages and Types Reference: DBMS_MONITOR

Oracle Database Reference: V$SERV_MOD_ACT_STATS

In the provided code block, the DBMS_SERVER_ALERT.SET_THRESHOLD procedure 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 the SET_THRESHOLD procedure are as follows:

The first parameter DBMS_SERVER_ALERT.CPU_TIME_PER_CALL specifies the metric for which the threshold is being set, in this case, the CPU time consumed per database call.

The second and third parameters DBMS_SERVER_ALERT.OPERATOR_GE and '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 parameters DBMS_SERVER_ALERT.OPERATOR_GE and '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 the DBMS_SERVER_ALERT.SET_THRESHOLD procedure, 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.