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.