nerdexam
Oracle

1Z0-052 · Question #119

You receive complaints from users regarding the high waiting time for their transactions. On investigation, you find that some users are not committing their transactions though they are not…

The correct answer is A. Decrease the IDLE_TIME resource limit in the profile assigned to the blocking users. B. Use Database Resource Manager to automatically log out sessions that block others and are idle. To prevent idle sessions from holding row-level locks indefinitely, the IDLE_TIME profile resource limit and the Database Resource Manager idle-blocker policy can both force termination of the blocking session.

Managing Data and Concurrency

Question

You receive complaints from users regarding the high waiting time for their transactions. On investigation, you find that some users are not committing their transactions though they are not performing any activity for a long time. As a result, SQL statements need to wait for rowlevel locks. Which two actions could you take to prevent this locking problem in the future? (Choose two.)

Options

  • ADecrease the IDLE_TIME resource limit in the profile assigned to the blocking users.
  • BUse Database Resource Manager to automatically log out sessions that block others and are idle
  • CSet the limit in the profile of blocking users to control the number of blocks to be accessed in a session.
  • DDecrease the maximum number of interested transaction list (ITL) slots for the segments on which the

How the community answered

(37 responses)
  • A
    73% (27)
  • C
    19% (7)
  • D
    8% (3)

Why each option

To prevent idle sessions from holding row-level locks indefinitely, the IDLE_TIME profile resource limit and the Database Resource Manager idle-blocker policy can both force termination of the blocking session.

ADecrease the IDLE_TIME resource limit in the profile assigned to the blocking users.Correct

Setting a low IDLE_TIME value in the user profile causes Oracle to automatically terminate sessions inactive beyond that limit, forcing a rollback and releasing any row-level locks held by the uncommitted transaction.

BUse Database Resource Manager to automatically log out sessions that block others and are idleCorrect

Oracle Database Resource Manager provides a consumer group directive that can automatically log out sessions identified as both idle and blocking other sessions, directly targeting the scenario described.

CSet the limit in the profile of blocking users to control the number of blocks to be accessed in a session.

Controlling the number of blocks accessed per session is an I/O resource limit and has no effect on how long a session can remain idle while holding uncommitted row-level locks.

DDecrease the maximum number of interested transaction list (ITL) slots for the segments on which the

Decreasing ITL (Interested Transaction List) slots would worsen block-level contention by reducing the number of concurrent transactions a block can support, not resolve the idle session locking problem.

Concept tested: Resolving idle session lock contention via profiles and Resource Manager

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-resources-with-oracle-database-resource-manager.html

Topics

#row-level locks#IDLE_TIME profile#Database Resource Manager#blocking sessions

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 1Z0-052 Practice