When you execute DBMS_AUTO_TASK_ADMIN.DISABLE , it disables all automated maintenance tasks related to the Auto Task framework. This includes tasks such as the Automatic SQL Tuning Advisor, Automatic Segment Advisor, and others. However, the Automatic Optimizer Statistics Collection (D) remains enabled as it is not part of the Auto Task framework. The gathering of optimizer statistics is controlled separately and is a critical part of the database ' s self-tuning mechanism to ensure the optimizer has up-to-date information about the data distribution within tables and indexes.

References

Oracle Database 19c PL/SQL Packages and Types Reference - DBMS_AUTO_TASK_ADMIN

Oracle Database 19c Database Administrator’s Guide - Managing Optimizer Statistics

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

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.

References:

Oracle Database SQL Tuning Guide: Managing Optimizer Statistics

Oracle Database SQL Tuning Guide: Using Indexes and Clusters

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 cursor: pin S wait on X wait event suggests contention for a cursor pin, which is associated with mutexes (a type of locking mechanism) that protect the library cache to prevent concurrent modifications. This issue can often be alleviated by deferring the invalidation of cursors until the end of the call to reduce contention. The correct command to use would be:

C (Correct): ALTER SYSTEM SET CURSOR_INVALIDATION=DEFERRED; This setting defers the invalidation of dependent cursors until the end of the PL/SQL call, which can reduce the cursor: pin S wait on X wait events.

The other options are incorrect in addressing this issue:

A (Incorrect): Setting CURSOR_SHARING to FORCE makes the optimizer replace literal values with bind variables. It doesn ' t address the contention for cursor pins directly.

B (Incorrect): CURSOR_SPACE_FOR_TIME=TRUE aims to reduce the parsing effort by keeping cursors for prepared statements open. It may increase memory usage but does not directly resolve cursor: pin S wait on X waits.

D (Incorrect): Increasing SESSION_CACHED_CURSORS caches more session cursors but doesn ' t necessarily prevent the contention indicated by the cursor: pin S wait on X wait events.

References:

Oracle Database Reference: CURSOR_INVALIDATION

Oracle Database Performance Tuning Guide: Reducing Cursor Invalidation

In an adaptive execution plan, the Optimizer makes runtime decisions between nested loop and hash joins using a statistics collector. The collector is a row source that collects statistics about the rows it processes and can adapt the plan based on the number of rows processed.

References:

Oracle Database SQL Tuning Guide, 19c

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