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

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 theV$SERV_MOD_ACT_STATSdynamic 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):TheV$SERVICE_STATSview 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 this scenario, creating an index and generating frequency histograms are two methods that can potentially improve performance:
* A (Correct):Generating frequency histograms on theCITY_IDcolumn 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 theCITY_IDcolumn 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

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

The optimizer_adaptive_statistics parameter, when set to TRUE, enables the optimizer to use adaptive statistics, such as SQL Plan Directives, to help improve plans by automatically adjusting them based on the actual execution statistics.
References:
* Oracle Database SQL Tuning Guide, 19c