Home » RDBMS Server » Performance Tuning » enq: TS - contention (10.2.0.4)
enq: TS - contention [message #517581] Tue, 26 July 2011 12:40 Go to next message
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 #517584 is a reply to message #517581] Tue, 26 July 2011 12:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
when all else fails Read The Fine Manual

http://download.oracle.com/docs/cd/A57673_01/DOC/server/doc/SPS73/ape.htm#ets


>I am getting enq: TS - contention as top event in AWR Report.
post formatted & actual excerpt from report for this section
Re: enq: TS - contention [message #517592 is a reply to message #517581] Tue, 26 July 2011 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.lmgtfy.com/?q=oracle+enq+TS+contention+-site%3Awww.dba-oracle.com

Regards
Michel
Re: enq: TS - contention [message #517596 is a reply to message #517584] Tue, 26 July 2011 13:02 Go to previous messageGo to next message
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 #517597 is a reply to message #517596] Tue, 26 July 2011 13:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Not every item in any TOP 5 report is something that needs to be "fixed"?
Realize that as soon as you "fix" TS enque, some other item will now be on the top of the list.
Will then you be back here wanting to solve it?

Do you suffer from Compulsive Tuning Disorder?

Prove to us than what you report is really an issue that needs to be fixed.
Re: enq: TS - contention [message #517598 is a reply to message #517597] Tue, 26 July 2011 13:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, I doubt that in 10 minutes you read the links and documentation we provided you, understood, analyzed it to know how to apply them in your case.

So FIRST do it instead of just relying on us to make your work and then come back if you have any further question with a complete description of your problem and why you can't solve it with what you read.

Regards
Michel
Re: enq: TS - contention [message #517599 is a reply to message #517597] Tue, 26 July 2011 13:20 Go to previous messageGo to next message
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 #517600 is a reply to message #517599] Tue, 26 July 2011 13:21 Go to previous messageGo to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member
Michael,
I had already read the documets befor posting.But could not
understand how to fix this issue.That is the reason i seeked
help.
Thanks,
Varun
Re: enq: TS - contention [message #517601 is a reply to message #517600] Tue, 26 July 2011 13:26 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I dont know how to upload HTML file here otherwise i would have uploaded awr report.

generate AWR in plain text.

I really do NOT care to see whole reported; just that section that caused you to conclude a problem exists.
Re: enq: TS - contention [message #517602 is a reply to message #517601] Tue, 26 July 2011 13:30 Go to previous messageGo to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member
BlackSwun,
Please find the part of awr attached to the message.
Thanks,
Varun
Re: enq: TS - contention [message #517603 is a reply to message #517602] Tue, 26 July 2011 13:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Please find the part of awr attached to the message.
Attached where?

please just post inline & directly here!
Re: enq: TS - contention [message #517604 is a reply to message #517603] Tue, 26 July 2011 13:38 Go to previous messageGo to next message
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 #517605 is a reply to message #517604] Tue, 26 July 2011 13:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
By the way, over what amount of elapsed wall clock time is this report based?

why are you not concerned about wait below
IPC send completion sync	394,747	97.28	349	1	0.33

since it has larger values than Enque: TS?

No problem exists. Now go worry about more important issues.
Re: enq: TS - contention [message #517607 is a reply to message #517605] Tue, 26 July 2011 13:56 Go to previous messageGo to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member
Total wait time for enq: TS - contention 137,447
whereas for IPC send completion sync it is 349

Thanks,
Varun
Re: enq: TS - contention [message #518125 is a reply to message #517607] Mon, 01 August 2011 07:27 Go to previous message
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
Previous Topic: PERFORMANCE HIT DUE TO NVL FUNCTION
Next Topic: How to reduce the consistent gets of this sql
Goto Forum:
  


Current Time: Fri Apr 26 16:21:52 CDT 2024