Oracle 1z0-084 Exam With Confidence Using Practice 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.

References:

• Oracle Database SQL Tuning Guide, 19c
• Oracle Database Administrator’s Guide, 19c

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

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