nerdexam
Oracle

1Z0-062 · Question #310

Which two would you recommend to an application developer for reducing locking conflicts on tables in an Oracle database? (Choose two.)

The correct answer is A. Create objects in locally managed tablespaces with Automatic Segment Space Management (ASSM) C. Allow the database to handle locks ln default locking mode. To reduce locking conflicts, an application developer should create objects in locally managed tablespaces with Automatic Segment Space Management (ASSM) and allow the database to manage locks using its default row-level locking mechanism.

Managing Data and Concurrency

Question

Which two would you recommend to an application developer for reducing locking conflicts on tables in an Oracle database? (Choose two.)

Options

  • ACreate objects in locally managed tablespaces with Automatic Segment Space Management (ASSM)
  • BAvoid coding long-running transactions.
  • CAllow the database to handle locks ln default locking mode.
  • DCreate objects in locally managed tablespaces with manual segment space management enabled.
  • EEnable undo retention guarantee.

How the community answered

(43 responses)
  • A
    74% (32)
  • B
    2% (1)
  • D
    16% (7)
  • E
    7% (3)

Why each option

To reduce locking conflicts, an application developer should create objects in locally managed tablespaces with Automatic Segment Space Management (ASSM) and allow the database to manage locks using its default row-level locking mechanism.

ACreate objects in locally managed tablespaces with Automatic Segment Space Management (ASSM)Correct

Creating objects in locally managed tablespaces with Automatic Segment Space Management (ASSM) significantly reduces contention for segment header blocks, as space management is handled using bitmaps, allowing concurrent DML without common bottlenecks.

BAvoid coding long-running transactions.

Avoiding long-running transactions is a good practice for overall performance and resource utilization, but it primarily minimizes the *duration* of locks rather than preventing the initial *occurrence* of locking conflicts.

CAllow the database to handle locks ln default locking mode.Correct

Oracle's default locking mode employs fine-grained row-level locking, which minimizes conflicts by locking only the affected rows, making it highly efficient for concurrent transactions and generally superior to manual locking schemes.

DCreate objects in locally managed tablespaces with manual segment space management enabled.

Using manual segment space management (MSSM) can actually *increase* locking conflicts, as multiple sessions may contend for the same segment header blocks when trying to allocate or deallocate space.

EEnable undo retention guarantee.

Enabling undo retention guarantee ensures undo information is preserved for a specified duration, primarily supporting long-running queries and Flashback features, and does not directly reduce locking conflicts on tables.

Concept tested: Reducing locking conflicts, ASSM, default locking

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/oracle-database-architecture.html#GUID-AE943962-E93C-4322-92D9-4B551C1C8659

Topics

#locking conflicts#concurrency#ASSM#transaction management

Community Discussion

No community discussion yet for this question.

Full 1Z0-062 Practice