Lots of Session waiting on enq: TS - Contention Against Temporary Tablespace
(Doc ID 2729042.1)
Last updated on APRIL 17, 2023
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.4 to 12.2.0.1 [Release 11.2 to 12.2]Information in this document applies to any platform.
Symptoms
- Quite a lot of sessions waiting so long with TS - contention against temporary tablespace; severely impacting application jobs.
- Short stack would show :- ksliwat()<-kslwaitctx()<-kjusuc()<-ksipgetctxi() <-ksqcmi()<-ksqgtlctx()<-ktatmkref()<-kxttPutPh()<-kxttPut()<-kxttReceiveTSI() <-kxfxsui()<-kxfxsp()<-kxfxmai()<-kxfprdp()
- One off Fix for 21924131 has already been applied
- Affects 11gr2 and 12c
Chains most likely to have caused the hang:
Text seen in the file (around line 64) =
Chain 1:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 4 (XXXXXX)
os id: <>
process id: xxx, oracle@xxxxxxxxxxxx (J000)
session id: <>
session serial #: <>
}
is waiting for 'PX Deq Credit: send blkd' with wait info:
{
p1: 'sleeptime/senderid'=0x10040010
p2: 'passes'=0x75d
p3: 'qref'=0x3c034411a0
time in wait: 52 min 21 sec
timeout after: never
wait id: 9437
blocking: 0 sessions
current sql
short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+178<-ksliwat()+2047<-kslwaitctx()+163<-kxfpqidqr()+2923<-kxfprienq()+5638<-kxfpqrenq()+235<-kxfxpnd()+1103<-kxfxcp1()+8943<-kxfxcp()+563<-qerpxSendParse()+2079<-kxfpValidateSlaveGroup()+185<-kxfpgsg()+3765<-kxfrAllocSlaves()+696<-kxfrialo()+4076<-kxfralo()+745<-qerpx_rowsrc_start()+2586<-qerpxStart()+1551<-insExecSubQueryIni()+252<-insExecStmtExecIniEngine()+82<-insexe()+870<-qes3tExecSQL()+842<-qerleStart()+17
wait history:
* time between current wait and wait #1: 0.000051 sec
.
.
p3: 'qref'=0x0
}
and is blocked by
=> Oracle session identified by:
{
instance: 4 (<name>)
os id: xxx
process id: 68, (P016)
session id: <no>
session serial #:<no>
}
which is waiting for 'enq: TS - contention' with wait info:
{
p1: 'name|mode'=0x54530003
p2: 'tablespace ID'=<no>
p3: 'dba'=<value>
time in wait: 52 min 21 sec
timeout after: never
wait id: 2
blocking: 1 session
current sql: <none>
short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-__poll()+24<-sskgxp_selectex()+392<-skgxpiwait()+4318<-skgxpwaiti()+1916<-skgxpwait()+162<-ksxpwait()+2501<-ksliwat()+12717<-kslwaitctx()+163<-kjusuc()+3428<-ksipgetctxi()+1759<-ksqcmi()+20798<-ksqgtlctx()+3501<-ktatmkref()+929<-kxttPutPh()+421<-kxttPut()+1047<-kxttReceiveTSI()+300<-kxfxsui()+5851<-kxfxsp()+111<-kxfxmai()+2812<-kxfprdp()+1435<-opirip()+913<-opidrv()+603<-sou2o()+103<-opimai_real()+250<-ssthrdmain()+265<-main()+201<-__libc_
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 |