My Oracle Support Banner

Resize Tablespace Hangs with TT Enqueue Contention (Doc ID 2595875.1)

Last updated on OCTOBER 10, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information 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 slaves

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 slave
             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

Changes

 

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
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.