Home » RDBMS Server » Performance Tuning » enq: TS - contention (10.2.0.4)
enq: TS - contention [message #517581] |
Tue, 26 July 2011 12:40 |
varunvir
Messages: 389 Registered: November 2007
|
Senior Member |
|
|
Hi Experts,
I am getting enq: TS - contention as top event in AWR Report.
Please guide me where can i look into database to fix this issue.
Thanks,
Varun
|
|
|
|
|
Re: enq: TS - contention [message #517596 is a reply to message #517584] |
Tue, 26 July 2011 13:02 |
varunvir
Messages: 389 Registered: November 2007
|
Senior Member |
|
|
Thanks Michael/Blackswun for providing the documentation.
But i am still not able to figure out how to resolve this
issue.We have moved from single node solaris to linus Two
node RAC two weeks ago.Everything is same on both environments.
Thanks,
Varun[
|
|
|
|
|
Re: enq: TS - contention [message #517599 is a reply to message #517597] |
Tue, 26 July 2011 13:20 |
varunvir
Messages: 389 Registered: November 2007
|
Senior Member |
|
|
Blackswun,
Waits and time is extraordinarily high in top contention event.
Please find the values below:-
Waits:-314,264
Time:- 137,447
Avg Wait:-437
%Total call time:-81.3%
I dont know how to upload HTML file here otherwise i would
have uploaded awr report. Thanks,
Varun
|
|
|
|
|
|
|
Re: enq: TS - contention [message #517604 is a reply to message #517603] |
Tue, 26 July 2011 13:38 |
varunvir
Messages: 389 Registered: November 2007
|
Senior Member |
|
|
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
enq: TS - contention 314,264 80.51 137,447 437 0.26
gc buffer busy 132,356 0.38 3,698 28 0.11
gc current block busy 118,711 0.01 3,388 29 0.1
log file sync 1,175,468 0.35 3,200 3 0.99
db file sequential read 113,070 0 2,715 24 0.09
enq: PS - contention 550,826 0.63 2,621 5 0.46
log file parallel write 1,197,578 0 2,041 2 1
SQL*Net break/reset to client 409,869 0 1,305 3 0.34
rdbms ipc reply 302,675 0.06 930 3 0.25
gc current block 2-way 621,745 0.01 797 1 0.52
db file scattered read 49,783 0 657 13 0.04
gc cr block 2-way 470,277 0 517 1 0.39
cr request retry 728 94.64 472 649 0
reliable message 190,930 0.05 389 2 0.16
gcs log flush sync 241,418 9.89 368 2 0.2
IPC send completion sync 394,747 97.28 349 1 0.33
db file parallel write 191,615 0 348 2 0.16
enq: TX - index contention 3,266 6.71 339 104 0
PX Deq: reap credit 6,320,724 93.4 245 0 5.3
PX Deq: Signal ACK 490,462 23.1 218 0 0.41
db file parallel read 4,366 0 210 48 0
control file sequential read 121,013 0 200 2 0.1
gc cr multi block request 149,993 0.02 183 1 0.13
os thread startup 622 19.77 180 290 0
gc cr block busy 39,506 0 178 5 0.03
DFS lock handle 39,627 0.37 159 4 0.03
latch free 11,847 0.07 130 11 0.01
gc current grant busy 20,843 0.38 127 6 0.02
enq: SS - contention 16,176 0.28 92 6 0.01
enq: TX - row lock contention 2,572 4.51 87 34 0
Streams AQ: qmn coordinator waiting for slave to start 11 100 60 5442 0
name-service call wait 925 0 55 60 0
control file parallel write 15,586 0 49 3 0.01
PX Deq Credit: send blkd 121,377 58.34 42 0 0.1
SQL*Net more data from client 66,034 0 33 0 0.06
gc cr grant 2-way 64,658 0 29 0 0.05
gc current grant 2-way 32,732 0 27 1 0.03
SQL*Net message to client 7,224,513 0 26 0 6.06
gc current block lost 34 29.41 25 747 0
gc cr block lost 27 22.22 22 812 0
row cache lock 19,301 0 21 1 0.02
enq: WF - contention 125 28 18 145 0
buffer busy waits 31,106 0 17 1 0.03
ksxr poll remote instances 625,786 89.95 14 0 0.52
library cache pin 1,963 0 14 7 0
CGS wait for IPC msg 291,283 99.98 12 0 0.24
SQL*Net more data to client 370,191 0 11 0 0.31
read by other session 432 0 11 24 0
enq: CF - contention 747 2.01 9 12 0
cursor: pin S wait on X 747 99.73 9 12 0
latch: ges resource hash list 15,494 0 8 1 0.01
enq: PI - contention 173 8.67 8 46 0
gc current retry 120 0 7 56 0
gc current split 420 0 7 16 0
log file switch completion 36 0 5 141 0
gc current block congested 579 0 5 8 0
buffer deadlock 20,260 99.22 4 0 0.02
gc cr block congested 446 0 4 9 0
local write wait 3,371 0 4 1 0
library cache lock 1,438 0.14 4 2 0
latch: cache buffers chains 19,618 0 3 0 0.02
enq: TM - contention 158 3.8 3 20 0
library cache load lock 37 0 3 78 0
KJC: Wait for msg sends to complete 3,427 0 2 1 0
latch: session allocation 9,693 0 2 0 0.01
latch: shared pool 7,221 0 2 0 0.01
change tracking file synchronous read 520 0 2 3 0
LGWR wait for redo copy 10,586 0 1 0 0.01
global enqueue expand wait 75 92 1 11 0
latch: enqueue hash chains 943 0 1 1 0
log file sequential read 28 0 1 26 0
PX qref latch 8,225 83.38 1 0 0.01
wait for scn ack 274 0 1 2 0
direct path read 64 0 1 9 0
enq: AF - task serialization 55 0 1 9 0
latch: library cache 1,329 0 0 0 0
gc cr failure 621 0 0 1 0
enq: HW - contention 92 0 0 4 0
change tracking file synchronous write 516 0 0 1 0
enq: RO - fast object reuse 183 0 0 2 0
gc cr grant congested 52 0 0 4 0
latch: redo allocation 344 0 0 1 0
ges global resource directory to be frozen 17 82.35 0 10 0
latch: library cache lock 499 0 0 0 0
kksfbc child completion 4 75 0 38 0
log buffer space 86 0 0 2 0
enq: TA - contention 138 0 0 1 0
latch: messages 206 0 0 1 0
gc cr disk read 92 0 0 1 0
gc current grant congested 17 0 0 6 0
latch: row cache objects 134 0 0 1 0
enq: TT - contention 77 0 0 1 0
latch: gcs resource hash 131 0 0 1 0
enq: JS - queue lock 2 0 0 42 0
latch: parallel query alloc buffer 149 0 0 1 0
latch: library cache pin 199 0 0 0 0
enq: FB - contention 65 0 0 1 0
cursor: pin S 2,206 0 0 0 0
latch: KCL gc element parent latch 551 0 0 0 0
wait list latch free 5 0 0 13 0
gc current block unknown 72 0 0 1 0
direct path write 80 0 0 1 0
enq: TO - contention 45 0 0 1 0
lock deadlock retry 193 98.45 0 0 0
kjbdrmcvtq lmon drm quiesce: ping completion 17 5.88 0 3 0
gc current multi block request 184 0 0 0 0
latch: redo writing 33 0 0 1 0
log file single write 28 0 0 1 0
enq: MW - contention 18 0 0 1 0
enq: DR - contention 19 0 0 1 0
enq: MD - contention 18 0 0 1 0
enq: JQ - contention 15 0 0 1 0
latch: cache buffers lru chain 37 0 0 1 0
lock escalate retry 89 89.89 0 0 0
enq: JS - job run lock - synchronize 16 0 0 1 0
latch: object queue header operation 24 0 0 0 0
enq: US - contention 23 0 0 0 0
enq: PW - flush prewarm buffers 5 0 0 1 0
latch: undo global data 7 0 0 1 0
enq: CT - global space management 4 0 0 1 0
latch: checkpoint queue latch 4 0 0 0 0
enq: TX - allocate ITL entry 1 0 0 1 0
enq: WP - contention 1 0 0 1 0
SQL*Net message from client 7,224,509 0 755,952 105 6.06
PX Idle Wait 372,917 29.92 350,837 941 0.31
gcs remote message 11,388,678 88.41 251,022 22 9.55
jobq slave wait 51,330 49.32 124,306 2422 0.04
Streams AQ: qmn slave idle wait 18,432 0.08 104,154 5651 0.02
DIAG idle wait 160,668 0 31,631 197 0.13
ASM background timer 7,003 0 31,629 4517 0.01
Streams AQ: qmn coordinator idle wait 10,967 43.2 31,546 2876 0.01
ges remote message 1,780,011 77.53 31,362 18 1.49
class slave wait 296 88.51 31,230 105507 0
Streams AQ: waiting for messages in the queue 3,226 96.84 30,494 9453 0
Streams AQ: waiting for time management or cleanup tasks 108 0 30,327 280802 0
PX Deq: Execution Msg 2,399,430 24.14 2,764 1 2.01
PX Deq: Join ACK 346,040 32.05 551 2 0.29
PX Deq: Execute Reply 1,193,980 29.3 437 0 1
PX Deq: Parse Reply 335,893 26.85 232 1 0.28
PX Deq: Msg Fragment 157,596 27.3 60 0 0.13
PL/SQL lock timer 1 100 1 975 0
KSV master wait 8 0 1 73 0
Streams AQ: RAC qmn coordinator idle wait 11,098 92.57 0 0 0.01
PX Deq Credit: need buffer 5 60 0 0 0
|
|
|
|
|
Re: enq: TS - contention [message #518125 is a reply to message #517607] |
Mon, 01 August 2011 07:27 |
|
LNossov
Messages: 318 Registered: July 2011 Location: Germany
|
Senior Member |
|
|
Hello Varun,
could you please run this script and upload the result.
/* checks the contents of the temporary
tablespaces in use */
set pagesize 100
set linesize 1000
set verify off
set feedback off
col kwrites format 99999999990
select
t.name tablespace_name,
decode(
t.bitmapped, 0, decode(t.contents$, 0, 'PERMANENT', 'TEMPORARY'), 'TEMPFILE'
) contents,
u.users,
sum(i.writes)/1024 kwrites
from
(
select
tempts#,
count(*) users
from
sys.user$
where
type# = 1
group by
tempts#
) u,
( select
d.ts#,
d.block_size * s.phyblkwrt writes
from
sys.v_$datafile d,
sys.v_$filestat s
where
d.file# = s.file#
union all
select
t.ts#,
t.block_size * s.phyblkwrt writes
from
sys.v_$tempfile t,
sys.v_$tempstat s
where
t.file# = s.file#
) i,
sys.ts$ t
where
i.ts# = u.tempts# and
t.ts# = i.ts#
group by
t.name,
t.bitmapped,
t.contents$,
u.users
/
set linesize 80
Could you please upload additionally one awr report (complete please, not a part), where I could see your enqueue waits.
Regards
Leonid
|
|
|
Goto Forum:
Current Time: Sun Nov 24 21:41:56 CST 2024
|