Resize Tablespace Hangs with TT Enqueue Contention
(Doc ID 2595875.1)
Last updated on APRIL 17, 2023
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and laterInformation in this document applies to any platform.
Symptoms
Have issued a tablespace resize command to increase tablespace bigfile datafile.
ALTER TABLESPACE YOURNAME RESIZE 1073741824000;
Session appears to hang with waits on enq: TT
An AWR report may show this wait as a top wait when resize command is run.
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn % DB time
enq: TX - contention 24,218 40,747 1682.49 0.43 51.69
enq: TM - contention 167 21,723 130078.85 0.00 27.56
db file sequential read 114,124 5,592 49.00 2.02 7.09
enq: TT - contention 2 2,027 1013591.95 0.00 2.57 <<<<<<<<<<
Per hanganalyze dump it will show chain with final blocker being Wxxx secondary
Chain 2:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 ()
os id: 10895
process id: 140, oracle@
session id: 2445
session serial #: 20077
}
is waiting for 'enq: TT - contention' with wait info: <<<<<<<<<<<<<<<<
{
p1: 'name|mode'=0x54540004
p2: 'tablespace ID'=0x15
p3: 'operation'=0x10
time in wait: 1 min 52 sec
timeout after: never
wait id: 5180
blocking: 0 sessions
wait history:
* time between current wait and wait #1: 0.000436 sec
1. event: 'db file sequential read'
time waited: 0.000289 sec
wait id: 5179 p1: 'file#'=0x23
p2: 'block#'=0x420b
p3: 'blocks'=0x1
* time between wait #1 and #2: 0.000027 sec
2. event: 'gc buffer busy acquire'
time waited: 0.000341 sec
wait id: 5178 p1: 'file#'=0x23
p2: 'block#'=0x420b
p3: 'class#'=0x9
* time between wait #2 and #3: 0.000016 sec
3. event: 'db file sequential read'
time waited: 0.000284 sec
wait id: 5177 p1: 'file#'=0x23
p2: 'block#'=0x420a
p3: 'blocks'=0x1
}
and is blocked by 'instance: 2, os id: 62898, session id: 1215', <<<<<<<<<<<<<<<<<
which is a member of 'Chain 1'.
Chain 1:
-------------------------------------------------------------------------------
and is blocked by
=> Oracle session identified by:
{
instance: 2 ()
os id: 62898
process id: 100, oracle@ (TNS ) <<<<<<<<<< systemstate dump or v$process shows Wxxx secondary
session id: 1215
session serial #: 20040
}
which is waiting for 'Data file init write' with wait info: <<<<<<<<<<<<<<<<<<<<< Final blocking process
{
p1: 'count'=0x1
p2: 'intr'=0x100
p3: 'timeout'=0x7fffffff
time in wait: 0.007505 sec
heur. time in wait: 4 min 22 sec
timeout after: never
wait id: 208610
blocking: 2 sessions
wait history:
* time between current wait and wait #1: 0.000502 sec
1. event: 'Data file init write'
time waited: 0.005058 sec
wait id: 208609 p1: 'count'=0x1
p2: 'intr'=0x100
p3: 'timeout'=0x7fffffff
* time between wait #1 and #2: 0.000248 sec
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |
| References |