Investigate Query Blocks and Delays | Teradata Vantage - Investigating Query Blocks and Delays - Analytics Database - Teradata Vantage

Database Administration

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-11-03
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
Product Category
Teradata Vantageā„¢

Most blocks are momentary and do not require attention. However, if a block persists, you can investigate it to identify the cause and determine whether further action is required.

  1. Use the Teradata Viewpoint Query Monitor portlet to monitor the parameters related to query blocks and delays.
    State Description
    Blocked Indicates that a session query is held up by a lock on an object that the query is attempting to access.
    Blocked Time How long the query has been blocked.

    Momentary locks are a normal part of database operation. Persistent locks may indicate:

    • A runaway query or hung query that cannot complete and release its locks, and which should be aborted
    • A long-executing query, such as large-scale update or backup operation, which should be rescheduled to avoid resource contention
    Delayed Indicates that the query is in a delay queue caused by a workload rule.
  2. Click the session ID for the query to access the Details View.
    • If a query is blocked, see the Blocked By tab for details
    • If the query is delayed, the Delay tab provides details about the cause of the delay
  3. You can also use the Lock Viewer portlet to find out additional details about the block:
    • Block Time
    • Database
    • Table
    • Delay
    • Blocked User
    • Blocking Level
    • Blocking User

Additional Tools for Analyzing Lock Problems

The following table provides suggestions for analyzing and solving lock problems.

Tool Analysis Solution
Lock Display Transaction locks Determine which session is holding the lock that blocks others.
Query Session Blocked session Abort the session causing blocked transactions.
Show Locks Host utility (HUT) locks; that is, locks placed by a client-based utility, such as DSA Submit RELEASE LOCKS as either an Archive and Recovery command or an SQL statement.