Get Oracle 1Z0-084 Exam Practice Questions - Real and Updated

Answer : C

To reduce the impact of database I/O without increasing the size of the buffer cache and without modifying SQL statements, you can use table compression. Among the given options, ROW STORE COMPRESS ADVANCED is the most suitable form of table compression to satisfy this requirement.

Advanced row compression (ROW STORE COMPRESS ADVANCED) is designed to work well with all supported types of data, whether it's OLTP or data warehouse environments. It offers a higher level of compression than basic table compression (ROW STORE COMPRESS BASIC) without significant overhead during DML operations. This feature can help reduce the amount of I/O required to retrieve data by storing it more efficiently on disk.

A, B, D: While COLUMN STORE COMPRESS FOR QUERY HIGH and ROW STORE COMPRESS are both valid compression types, COLUMN STORE COMPRESS FOR QUERY HIGH applies to the In-Memory column store and is not available in all versions and editions, and ROW STORE COMPRESS is less advanced than ROW STORE COMPRESS ADVANCED.

Oracle Database Concepts Guide: Table Compression

Oracle Database Performance Tuning Guide: Row Compression

Answer : B, E, F

For this SQL statement and output, we can analyze the EVENT column to understand the type of wait:

B) The event 'SQL*Net message from client' typically indicates that the session is waiting for a response from the client. This can be due to a network issue, user response, or an application processing delay.

E) The event 'SQL*Net message from client' also implies that the session is idle waiting for the client (a user or an application) to send a request to the server. This event usually indicates that the session is not actively working but is instead waiting for the next command.

F) The wait event 'enq: TX - row lock contention' suggests that session 9822 is waiting for a row-level lock held by another session. If the holding session issues a COMMIT or ROLLBACK, the lock will be released, and session 9822 will stop waiting. Since this session is experiencing row lock contention, it implies it's waiting for a specific transaction to complete.

Oracle Database Reference, 19c

Oracle Wait Events Documentation

Answer : D

In Oracle Database, the V$SESSION_LONGOPS view provides insights into various operations within the database that are expected to take more than six seconds to complete. These include operations related to RMAN (Recovery Manager), such as database duplication tasks. This view displays information about the progress of these long-running operations, including the start time, elapsed time, and estimated time to completion.

When an RMAN session is performing a database duplication and is observed to be slower than usual, checking the V$SESSION_LONGOPS view can give an approximation of when the RMAN operation might complete. This view includes fields like TIME_REMAINING and ELAPSED_SECONDS that help in estimating the completion time of the operation based on its current progress.

Oracle Database Reference: V$SESSION_LONGOPS

Oracle Database Backup and Recovery User's Guide: Monitoring RMAN Jobs

Answer : D

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

Answer : B

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