| High enqueue waits in statspack - lock contention on Primary key index showing 98% of db wait time [message #442930] |
Thu, 11 February 2010 07:23  |
mwansa
Messages: 14
Registered: December 2009
|
Junior Member |
|
|
Inserts in a table very slow. Primary key index on this table showing high internal lock contention. This is seeing using third party gui monitoring tool.
The table is only 30M with 101705 rows
statspack Top wait events show:
--
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
--------------------------- ------------ ----------- --------
enqueue 17,089 50,160 93.65
CPU time 2,925 5.46
sbtbackup 2 230 .43
sbtclose2 2 161 .30
SQL*Net message from dblink 3,267 29 .05
---
why is there a high lock contention on this index.
there are a number of insert statements on the same table and not appearing in top sqls.
any thing need to be done on the index.
ddl for index
CREATE UNIQUE INDEX "xxxx"."PKEY_xxxxx"
ON "xxxxxx"."xxxxxx" ("PPUID", "PPSEQ")
TABLESPACE "xxxxxx" PCTFREE 10 INITRANS 4 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING
---
They are no foreign keys referencing this index keys.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: High enqueue waits in statspack - lock contention on Primary key index showing 98% of db wait t [message #443329 is a reply to message #443101] |
Mon, 15 February 2010 02:57  |
mwansa
Messages: 14
Registered: December 2009
|
Junior Member |
|
|
The sql is an insert
the explain plan
[2]Insert statement (Optimizer mode: CHOSE)
[1]Table access (in insert)XXXXXX
The sequence used
CREATE SEQUENCE "xxxxxxxxx" INCREMENT
BY 1 START WITH 1839 MAXVALUE 9999999 MINVALUE 1 CYCLE
NOCACHE NOORDER
The table is a dictionary managed table and has INITRANS 4
I was looking at caching the sequence with a high value say 100 and increasing the initrans to say 20. I am looking at reducing the contention on the index.
This may be applying to our situation
----
Certain scenarios can require that Oracle apply locks above the row level of granularity. These include:
Unindexed foreign keys
Bitmap indexes
Inability to grow the Interested Transaction List (ITL)
Oracle sometimes applies internal locks and in some circumstances these can cause contention at the application level. Two examples of problematic internal locking are:
- Space Transaction (ST) locks in dictionary managed tablespaces
- Row cache locks caused by sequences with low CACHE settings.
----
As a proposal towards the solution, will be ok i initially start with my suggestion, to see if this will sortout the issue.
|
|
|
|
| Re: High enqueue waits in statspack - lock contention on Primary key index showing 98% of db wait t [message #443347 is a reply to message #442930] |
Mon, 15 February 2010 04:14 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Hi - I do wonder if you are going in the right direction here. It is hard to see how a sequence cache will solve a row enqueue problem. And I still wonder what your tool means by the phrase "internal lock contention": it doesn't sound like a TX enqueue.
By the way, you never did post the monitoring graphs, and the statspack report might help too.
Have you tried to find out what session is doing the blocking, and what it is (or more likely, isn't) doing? I would run the utllockt script to identify the blocking and waiting sessions, then make the joins from v$session to v$sql to see what code the blocking and waiting sessions are running: the join columns are sql_id and prev_sql_id in v$session to sql_id in v$sql.
|
|
|
|
| Re: High enqueue waits in statspack - lock contention on Primary key index showing 98% of db wait t [message #443360 is a reply to message #443347] |
Mon, 15 February 2010 05:12 |
mwansa
Messages: 14
Registered: December 2009
|
Junior Member |
|
|
I can not attach the statspack file as it may contain senstive data..
The problem happens once in a time. I can not get the sessions blocking as this will have dissappeard.
Statspack indicates ( period 14:30 - 15:50pm when problem happened)
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
------------------------------- --------- ----------- --------
enqueue 21,322 62,570 92.22
CPU time 4,771 7.03
sbtbackup 2 230 .34
sbtclose2 2 161 .24
SQL*Net message from dblink 3,267 29 .04
^LEnqueue activity for DB: xxxxxx Instance: xxxxxxx Snaps: 50318 -50352
-> Enqueue stats gathered prior to 9i should not be compared with 9i data
-> ordered by Wait Time desc, Waits desc
Avg Wt Wait
Eq Requests Succ Gets Failed Gets Waits Time (ms) Time (s)
-- ------------ ------------ ----------- ----------- ------------- ------------
TX 41,281 41,278 0 766 270,124.09 206,915
TC 20 20 0 4 11,926.00 48
CF 13,784 13,783 1 33 6.06 0
CU 211,493 211,493 0 29 1.48 0
US 166,026 166,026 0 1 10.00 0
---
I will try to get the proper file formate from the GUI and will attach soon.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: High enqueue waits in statspack - lock contention on Primary key index showing 98% of db wait t [message #443386 is a reply to message #443369] |
Mon, 15 February 2010 07:52 |
entekeralam
Messages: 19
Registered: January 2010
|
Junior Member |
|
|
If you are on 10g, Can you please run below sql. ( Modify the sample_time period to match the time "when problem happened")
select session_id,session_serial#,event,current_obj#,count(*) from v$active_session_history
where sample_time between to_timestamp('15-FEB-10 19:10','DD-MON-YY HH24:MI') and
to_timestamp('15-FEB-10 19:15','DD-MON-YY HH24:MI')
group by session_id,session_serial#,event,current_obj# order by count(*)
and
select session_id,session_serial#,event,current_obj#,count(*) from dba_hist_active_sess_history
where sample_time between to_timestamp('15-FEB-10 19:10','DD-MON-YY HH24:MI') and
to_timestamp('15-FEB-10 19:15','DD-MON-YY HH24:MI')
group by session_id,session_serial#,event,current_obj# order by count(*) |
|
|
|
|
|
|
|
|
|
| Re: High enqueue waits in statspack - lock contention on Primary key index showing 98% of db wait t [message #443426 is a reply to message #443425] |
Mon, 15 February 2010 11:56 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>because your problem is enqueues,
If above is true then following SQL will be conflicting sessions
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
/
|
|
|
|
| Re: High enqueue waits in statspack - lock contention on Primary key index showing 98% of db wait t [message #443427 is a reply to message #443387] |
Mon, 15 February 2010 11:58 |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
Avg Wt Wait
Eq Requests Succ Gets Failed Gets Waits Time (ms) Time (s)
-- ------------ ------------ ----------- ----------- ------------- ------------
TX 41,281 41,278 0 766 270,124.09 206,915
TC 20 20 0 4 11,926.00 48
CF 13,784 13,783 1 33 6.06 0
CU 211,493 211,493 0 29 1.48 0
US 166,026 166,026 0 1 10.00 0
To me it looks like you are hitting a tx enqueue which is most likely caused by a lock contention (as already mentioned). I don't think you can tune this by increasing the ITL or cache size. You have to fix the underlying the code.
Refer this link for more details about tx enqueue
http://hoopercharles.wordpress.com/2009/12/06/enqueue-experimentations/
Your best bet will be is to sample the v$lock and v$session_wait when this process is running.
Also from next time if you could format your report like I did it will be much appreciated.
Last but not least you are on a version 9.2.0.6 which is not supported (I think). Atleast upgrade to 9.2.0.8.
Happy profiling.
Cheers
Raj
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
