| Performance issue enq: TX index contention [message #648393] |
Tue, 23 February 2016 00:12  |
 |
ninan
Messages: 163
Registered: June 2011
Location: Noida
|
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  |
John Watson
Messages: 8929
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 #648414 is a reply to message #648411] |
Tue, 23 February 2016 03:49 |
John Watson
Messages: 8929
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 #648433 is a reply to message #648427] |
Tue, 23 February 2016 06:21 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
ninan wrote on Tue, 23 February 2016 11:38But 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 |
John Watson
Messages: 8929
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 #648440 is a reply to message #648439] |
Tue, 23 February 2016 06:55 |
John Watson
Messages: 8929
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 #648445 is a reply to message #648443] |
Tue, 23 February 2016 07:40 |
John Watson
Messages: 8929
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 |
|
ninan
Messages: 163
Registered: June 2011
Location: Noida
|
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 |
John Watson
Messages: 8929
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 #648462 is a reply to message #648460] |
Tue, 23 February 2016 09:59 |
 |
Michel Cadot
Messages: 68641
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 #648464 is a reply to message #648460] |
Tue, 23 February 2016 10:03 |
John Watson
Messages: 8929
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
