Oracle 1Z0-084 Exam Dumps

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.

Oracle Database Performance Tuning Guide, 19c

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. Reference:

Oracle Database Performance Tuning Guide, 19c

Oracle Database Concepts, 19c

When importing objects that used the In-Memory (IM) column store in their source database using Oracle Data Pump, the following statements are true:

D (Correct): The TRANSFORM clause can be used to alter object creation DDL during import operations. This can include adding the INMEMORY clause to tables that were not originally using the IM column store.

F (Correct): The import operation can preserve the INMEMORY attributes of tables as they were at the time of export, effectively replicating the IM column store settings from the source database.

The other statements are not accurate in the context of Data Pump import:

A (Incorrect): Data Pump does not give preference to the IM column store clauses at the tablespace level over table-level definitions unless explicitly specified by the TRANSFORM clause.

B (Incorrect): While Data Pump can transport existing INMEMORY attributes, it is not mandatory. It is controlled by the INCLUDE or EXCLUDE Data Pump parameters or the TRANSFORM clause.

C (Incorrect): The INMEMORY_CLAUSE parameter is not part of the Data Pump Export utility. To modify the IM column store clauses, you would use the TRANSFORM parameter during import, not export.

E (Incorrect): Data Pump does not ignore the IM column store clause unless specifically instructed to do so via the EXCLUDE parameter.

Oracle Database Utilities: Data Pump Export

Oracle Database Utilities: Data Pump Import

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.

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.

Oracle Database SQL Tuning Guide, 19c