Oracle 1Z0-084 Exam Dumps

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.

Oracle Database SQL Tuning Guide, 19c

Oracle Database Administrator's Guide, 19c

When an ORA-00060 deadlock error occurs, detailed information about the error and the deadlock graph are dumped into the alert log. This log contains a trace file name that you can use to find additional detailed information about the sessions involved in the deadlock and the SQL statements they were executing. Reference:

Oracle Database Administrator's Guide, 19c

Oracle Database Error Messages, 19c

Given that index range scan operations on IX_SALES_TIME_ID are slower due to an increase in buffer gets, the aim is to improve the efficiency of the index access. In this scenario:

B (Correct): Re-creating the index using ADVANCED COMPRESSION can reduce the size of the index, which can lead to fewer physical reads (reduced I/O) and buffer gets when the index is accessed, as more of the index can fit into memory.

The other options would not be appropriate because:

A (Incorrect): Re-creating the SALES table sorted in order of the index might not address the issue of excessive buffer gets. Sorting the table would not improve the efficiency of the index itself.

C (Incorrect): Using the columns in IX_SALES_TIME_ID as a hash partitioning key for the SALES table is more relevant to data distribution and does not necessarily improve index scan performance.

D (Incorrect): Hash partitioning the index is generally used to improve the scan performance in a parallel query environment, but it may not reduce the number of buffer gets in a single-threaded query environment.

Oracle Database SQL Tuning Guide: Managing Indexes

Oracle Database SQL Tuning Guide: Index Compression

When sessions concurrently insert data into a table with an LOB column and one session needs to allocate a new extent because it cannot find available space, the wait event associated with this contention is 'enq: HW - contention'. The HW stands for High Water Mark which is related to space allocation in the database segment. When a session needs to allocate a new extent, it may raise this wait event in other sessions that are also attempting to allocate space in the same LOB segment.

Reference

Oracle Database 19c Reference Guide - enq: HW - contention

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.

Reference

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

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