Skip to main content

Search

Welcome to Tibero GTS!

Meaning of WLOCK_TEMP_GRANULE

Document Type | Technical Information

Category | Monitoring/Inspection

Applicable Product Version | Tibero 7.2.3

Document Number | TMOTI045

Overview

This guide provides information about WLOCK_TEMP_GRANULE that may occur during Tibero monitoring. 

 

Method

WLOCK_TEMP_GRANULE

  • This is a type of LOCK that occurs when you want to use the TEMP TABLESPACE area in a TAC configuration.
  • When TEMP needs to be used during query execution, it can only be used after acquiring a LOCK on the GRANULE.
    (GRANULE unit: 64K * 100)
  • The acquired GRANULE cannot be used by other tasks, and a new GRANULE will be used instead. 
  • Even when you want to use a new GRANULE, it can only be used after acquiring a LOCK.
  • When checking V$LOCK, ID1 means TEMP DATAFILE ID, and ID2 means GRANULE NUMBER.
  • You may see the WLOCK_TEMP_GRANULE item in the LOCK list during system monitoring.
SQL> select * from v$lock;

TYPE                             ID1        ID2      LMODE  REQUESTED    SESS_ID      CTIME
------------------------- ---------- ---------- ---------- ---------- ---------- ----------
WLOCK_SESS                       784          0          5          0        784     108714
WLOCK_TEMP_GRANULE                 3          1          5          0        664      41857
WLOCK_TEMP_GRANULE                 3          0          5          0        664      42749
WLOCK_TX                     5505225         93          5          0        906      23083
WLOCK_RT                           0          0          5          0        722     108712
WLOCK_DD_OBJ                     490     102571          1          0        906      23083
WLOCK_UNDO_TS                      1          0          5          0        720     108712
...omitted

TYPE                             ID1MODE  REQUESTED    SESS_ID      CTIME
------------------------- ---------- ---------- ---------- ---------- ---------- ----------
WLOCK_RT_SWITCH                    0          0          5          0        722     108712
WLOCK_DML                     102572          6          2          0        906      23083
WLOCK_SESS                       866          0          5          0        866      35119
WLOCK_SESS                       778          0          5          0        778     108714
WLOCK_SESS                       777          0          5          0        777     108714
WLOCK_SESS                       781          0          5          0        781     108714

28 rows selected.

 

Meaning and Actions for WLOCK_TEMP_GRANULE

  • The appearance of WLOCK_TEMP_GRANULE means that there is a task using the TEMP area.
  • It can be inferred that there is a query using the TEMP TABLESPACE area due to insufficient PGA area.
  • The presence of WLOCK_TEMP_GRANULE is not a problem in itself, but excessive use of the TEMP area can cause DISK I/O, which may lead to DB performance degradation.
  • Along with DISK I/O monitoring, it may be necessary to optimize queries that use the TEMP area extensively.

 

 

 

Related to