Home » RDBMS Server » Performance Tuning » Performance issue enq: TX index contention (Linux, Oracle 11g RAC)
Performance issue enq: TX index contention [message #648393] Tue, 23 February 2016 00:12 Go to next message
ninan
Messages: 158
Registered: June 2011
Location: bangalore
Senior Member
Hi,

I have generated the AWR report and able to see below contention.


   
  Application	enq: TX - row lock contention	34,295,747	99.98	

  Concurrency	enq: TX - index contention       1,834,797	0.00	23,049.70	12.56	1.69	

  


in Top Timed Foreground Events in both nodes of RAC.

Also in SQL ordered by Elapsed Time
I am able to see 0 executions and high Elapsed times for 3 ALTER INDEX statements.

Only 3 ALTER INDEX statements show 0 executions and high Elapsed times respectively.

For other SQLs which are having Elapsed time but the executions are greater than the Elapsed times.

My query is whether Index Leaf Block contention could be happening due to ALTERing of INDEXes which resulting in
Concurrency enq: TX - index contention and Application enq: TX - row lock contention.

From OS statistics there is no resource crunch in OS level and Time Model Statistics tell me that
SQL Execution Elapsed time is by average 88% in both nodes of RAC.

Parse Elapsed %DB Time is 0.38 and Hard Parse %DB Time is 0.02

Based on this analysis , it looks like a Index Leaf Block contention issue.

Any suggestions/ advise on this please.

Thanks,
Ninan

Re: Performance issue enq: TX index contention [message #648405 is a reply to message #648393] Tue, 23 February 2016 01:38 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
Ninan, you have posted some words and numbers, with no explanation of what they mean. Not even the column headings.

If you think you have a problem, you need to say what the problem is. Something like "the over night batch jobs don't finish till lunchtime" or "this query takes five minutes, I need it in five seconds" and the complete AWR report covering the period.
Re: Performance issue enq: TX index contention [message #648411 is a reply to message #648405] Tue, 23 February 2016 02:55 Go to previous messageGo to next message
ninan
Messages: 158
Registered: June 2011
Location: bangalore
Senior Member
Hi John,

Thanks for your reply. Sorry for the confusion.

My query is whether, ALTERing INDEXes with rebuild online can cause 'Concurrency enq: TX - index contention' issue.

As I am able to see among the SQL in order of Elapsed time, the Sqls which have 0 Executions and High Elapsed time are ALTER INDEX statements.

So I think the 'Concurrency enq: TX - index contention' in the Top Timed Foreground events is caused by these SQLs and this could be a Index leaf Block contention issue.

Also can see the wait class has value "Concurrency" which could be caused by the ALTER INDEX statements.

Looking forward for your advise.

Thanks,
Ninan.
Re: Performance issue enq: TX index contention [message #648413 is a reply to message #648411] Tue, 23 February 2016 03:17 Go to previous messageGo to next message
Roachcoach
Messages: 1509
Registered: May 2010
Location: UK
Senior Member
What does ASH tell you?
Re: Performance issue enq: TX index contention [message #648414 is a reply to message #648411] Tue, 23 February 2016 03:49 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
Quote:
My query is whether, ALTERing INDEXes with rebuild online can cause 'Concurrency enq: TX - index contention' issue.

No, the wait would be "enq: TX - row lock contention": this is the wait your ALTER INDEX...REBUILD ONLINE hits if there any transactions against the table. It needs a brief table lock before it can start executing, which it can't get. You need to quiesce the database for a few seconds to get it,
http://www.orafaq.com/node/2943

Re: Performance issue enq: TX index contention [message #648415 is a reply to message #648414] Tue, 23 February 2016 04:14 Go to previous messageGo to next message
ninan
Messages: 158
Registered: June 2011
Location: bangalore
Senior Member
Hi John,

Is quiesce the Database suggested as a permanent for Production environment? Because everytime the Production job runs, we may need to quiesce and unquiesce right?

While looking for this issue online, I came across some of the solutions provided for.

Strategies for dealing with the "Concurrency enq: TX - index contention" problem as:
(a) set the sequence cache to a large value.
(b) don't use NOCACHE on sequences,
(c) don't use ORDER on sequences,
(d) recreate the indexes as reverse key indexes.

Do you suggest the above to be permanent solution for this problem.

Re: Performance issue enq: TX index contention [message #648416 is a reply to message #648415] Tue, 23 February 2016 04:17 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
I don't know what your problem is. You have not even said what the ALTER INDEX command is, or why you are running it. One thing I am certain of is that your end users are not telephoning you to complain that "there are too many index wait events". What are they complaining about?

[Updated on: Tue, 23 February 2016 04:17]

Report message to a moderator

Re: Performance issue enq: TX index contention [message #648424 is a reply to message #648416] Tue, 23 February 2016 05:20 Go to previous messageGo to next message
ninan
Messages: 158
Registered: June 2011
Location: bangalore
Senior Member
They are complaining on the Batch process job taking too much time to finish. When I analyse the AWR report I find the
ALTER INDEX INDEX_XXX_NAME rebuild online parallel 16 storage (freelist groups 9 freelists 12) commands with
0 Executions and High Elapsed times in the 'SQL in order of Elapsed time'.

This ALTER INDEX command is part of the batch jobs.

Re: Performance issue enq: TX index contention [message #648426 is a reply to message #648424] Tue, 23 February 2016 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Remove it.

Re: Performance issue enq: TX index contention [message #648427 is a reply to message #648426] Tue, 23 February 2016 05:38 Go to previous messageGo to next message
ninan
Messages: 158
Registered: June 2011
Location: bangalore
Senior Member
But Online index rebuilds allows DML operations on the tables during index creation right. I thought that is the advantage of REBUILD ONLINE option.
So why remove it?

Instead does the below solutions help to resolve 'Concurrency enq: TX - index contention' issue.

(a) set the sequence cache to a large value.
(b) don't use NOCACHE on sequences,
(c) don't use ORDER on sequences,
(d) recreate the indexes as reverse key indexes.

Re: Performance issue enq: TX index contention [message #648428 is a reply to message #648413] Tue, 23 February 2016 05:39 Go to previous messageGo to next message
Roachcoach
Messages: 1509
Registered: May 2010
Location: UK
Senior Member
Roachcoach wrote on Tue, 23 February 2016 09:17
What does ASH tell you?

Re: Performance issue enq: TX index contention [message #648433 is a reply to message #648427] Tue, 23 February 2016 06:21 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
ninan wrote on Tue, 23 February 2016 11:38
But Online index rebuilds allows DML operations on the tables during index creation right. I thought that is the advantage of REBUILD ONLINE option.
So why remove it?

Instead does the below solutions help to resolve 'Concurrency enq: TX - index contention' issue.

(a) set the sequence cache to a large value.
(b) don't use NOCACHE on sequences,
(c) don't use ORDER on sequences,
(d) recreate the indexes as reverse key indexes.

REBUILD ONLINE means that the rebuild will not prevent DML. However, DML will prevent the rebuild from starting.

Your (a) through (d) are irrelevant, because you do not have a 'Concurrency enq: TX - index contention' issue. You have an 'Application enq: TX - row lock contention' issue, which you have forced upon yourself.

[Updated on: Tue, 23 February 2016 06:24]

Report message to a moderator

Re: Performance issue enq: TX index contention [message #648434 is a reply to message #648424] Tue, 23 February 2016 06:23 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
ninan wrote on Tue, 23 February 2016 11:20
They are complaining on the Batch process job taking too much time to finish. When I analyse the AWR report I find the
ALTER INDEX INDEX_XXX_NAME rebuild online parallel 16 storage (freelist groups 9 freelists 12) commands with
0 Executions and High Elapsed times in the 'SQL in order of Elapsed time'.

This ALTER INDEX command is part of the batch jobs.

Why are you rebuilding any indexes? You should fire the developer who put that code into your batch job. Furthermore, that syntax implies that you are not using ASSM, in which case you should fire your DBA as well.
Re: Performance issue enq: TX index contention [message #648439 is a reply to message #648433] Tue, 23 February 2016 06:44 Go to previous messageGo to next message
ninan
Messages: 158
Registered: June 2011
Location: bangalore
Senior Member
Hi John,

Thanks for your reply.

I have both 'Concurrency enq: TX - index contention' and 'Application enq: TX - row lock contention' in AWR.

I am attaching the screenshot for the Top Timed Foreground events from AWR.

From the report it seems to me that 'Application enq: TX - row lock contention' is caused by 'Concurrency enq: TX - index contention'.

Please correct me if am wrong.


Thanks,
Ninan
Re: Performance issue enq: TX index contention [message #648440 is a reply to message #648439] Tue, 23 February 2016 06:55 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
You are wasting your time. 100% wasting. Those wait events are consuming less than 2% of DB Time. Forget them.

You need to attack your db file sequential read events. You said the problem is with batch jobs. Almost certainly, those jobs should be running plans that are based on full scans and hash joins, but are actually running plans that use indexed access and nested loop joins.

You need to start tuning the SQLs, not fiddling around with wait events.
Re: Performance issue enq: TX index contention [message #648443 is a reply to message #648440] Tue, 23 February 2016 07:19 Go to previous messageGo to next message
ninan
Messages: 158
Registered: June 2011
Location: bangalore
Senior Member
Hi John,

I got this confusion when interpreting the SQL ordered by Elapsed Time.
Attaching the screenshot for the SQL ordered by Elapsed time.

The first SELECT statement is taking more DB time.
Whereas the second SELECT statement is taking more time on IOWait.

Whereas the ALTER INDEX statements is taking more elapsed time and 0 executions.



Thanks,
Ninan
Re: Performance issue enq: TX index contention [message #648445 is a reply to message #648443] Tue, 23 February 2016 07:40 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
You do not seem to have made any attempt to find out what the problem is that your users are facing. Which statements are too slow? Have you actually asked them? You may (shock, horror!) need to sit down with them and look at the code.


Re: Performance issue enq: TX index contention [message #648456 is a reply to message #648445] Tue, 23 February 2016 09:19 Go to previous messageGo to next message
ninan
Messages: 158
Registered: June 2011
Location: bangalore
Senior Member
Hi John,

As mentioned earlier, they have reported only Batch Process Job takes long time.
These are end users they have no technical exposure to tell which Queries are slower.
They gave only generic information that the Batch Process Job is running slow and takes long time.
Thought it could be a concurrency issue since the Indexes are being rebuilt online and that could be effecting the SQLs.

You mean to say SQLs should be tuned and 'Concurrency enq: TX - index contention' is not at all a problem ?

Thanks,
Ninan.

[Updated on: Tue, 23 February 2016 09:21]

Report message to a moderator

Re: Performance issue enq: TX index contention [message #648457 is a reply to message #648456] Tue, 23 February 2016 09:25 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
This sort of thing,
Quote:
You mean to say SQLs should be tuned and 'Concurrency enq: TX - index contention' is not at all a problem ?

looks like an attempt to annoy me, considering that it is exactly what I said.
I do not feed trolls.
Goodbye.
Re: Performance issue enq: TX index contention [message #648460 is a reply to message #648457] Tue, 23 February 2016 09:53 Go to previous messageGo to next message
ninan
Messages: 158
Registered: June 2011
Location: bangalore
Senior Member
Hi John,

Sorry if you have misunderstood my question.
I had no intention to annoy you.

I was just confirming with you since when I searched on internet for 'Concurrency enq: TX - index contention' found many
suggestions like the ones I had mention in (a) to (d) in one of the posts above.

http://serdarturgut.blogspot.in/2010/12/enq-tx-index-contention.html



Thanks,
Ninan.
Re: Performance issue enq: TX index contention [message #648462 is a reply to message #648460] Tue, 23 February 2016 09:59 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What John means is that even if you remove ALL these events you will have a benefit of at most 2%.
Is this what you want? 2% of improvement?

This figure just PROVES that this is NOT the problem.
Now do what you want to remove this point we don't care.
I suggested you, and I confirm, remove all ALTER INDEX REBUILD.

[Updated on: Tue, 23 February 2016 10:00]

Report message to a moderator

Re: Performance issue enq: TX index contention [message #648463 is a reply to message #648460] Tue, 23 February 2016 10:02 Go to previous messageGo to next message
BlackSwan
Messages: 25797
Registered: January 2009
Location: SoCal
Senior Member
If you totally eliminated 'Concurrency enq: TX - index contention' from ever occurring, how much faster would your application be?
Please quantify.
Simply because 'Concurrency enq: TX - index contention' exists, it does not mean that it is a problem that needs to be solved.
Re: Performance issue enq: TX index contention [message #648464 is a reply to message #648460] Tue, 23 February 2016 10:03 Go to previous message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
All right, I'll give you the benefit of the doubt. And repeat myself.

You need to find the problem statements. That means going through the bath process with your users working out what it is doing, and why. Try running each step individually, and timing them. This is very basic problem solving: start by idfentifying what the problem is.
Previous Topic: Please help for Improving the Explain plan
Next Topic: Select with Union Performance issue
Goto Forum:
  


Current Time: Tue Jan 23 05:03:57 CST 2018

Total time taken to generate the page: 0.05885 seconds