Re: TX wait on I_OBJ# and C_OBJ#
- From: Sami Seerangan <dba.orcl@xxxxxxxxx>
- To: "Mark W. Farnham" <mwf@xxxxxxxx>
- Date: Sat, 24 Dec 2005 14:42:31 -0500
Thanks Mark.
On 12/24/05, Mark W. Farnham <mwf@xxxxxxxx> wrote:
>
>
> By "with high number of concurrency (update)" do you mean several different
> client processes >are running in parallel updating a single table?
This is what we are trying to do. We are having an ITL contention issue in
Oracle Advanced Replication's Queue table's Index. We are trying to reproduce
the same issue by using scripts.
The script will start 300 new sessions and each session will update a
random row
in one of the application tables(The application table has 600+ rows) WITHOUT
COMMIT. We are making sure that random selection of row is always unique to
avoid row locks.
>
> Does the gathering of the data to perform the update involve the creation of
> temporary system or user objects on the fly?
NO
> I haven't lately scrutinized what they may have piled on to C_OBJ#, but I
> would think that merely updating a table would not cause anything except
> reads to find out references to the clusters, tables, indexes, lobs, and
> user-defined objects. Now if the overall process of getting to the update
> forces it to allocate objects on the fly, I suppose there could be a
> problem. Hmm, all the processes aren't doing something that can't thread
> like queuing up to truncate the same hard-wired scratch pad table, or
> something like that?
>
While this process is going on we see so many US(Undo Segment Type)
lock type in
v$lock and it got vanished when new undo segment has been created. However TX
locks on I_OBJ# and C_OBJ# remains there as I showed you earlier.
I got approx 2 of these locks when the concurrency is closer to 100 and it
increases when the concurrency increases.
Since the index is on sequence number (DEF$_TRANORDER index on AQ$CALL table)
and "right growing index", we saw all the 250+ updates had gone to
only 2 of the
leaf nodes. We almost proved ITL contention on this index with large number of
concurrency. However we are not clear about why there are so many TX locks on
data dictionary objects like I_OBJ# and C_OBJ#.
> Also, someone has to be blocking, so toss the query on page 178 of
> SHEE/DESHPANDE/GOPALALKRISHNAN (maybe with a little more filtering to see
> just this contention you're intereested in) at it and you should be
> enlightened.
>
> (If you don't have that book, drop what you're doing and acquire a copy, and
> no, I don't get money to say that. The index alone is worth the price of the
> book so you remember the name of the thing you forgot to remember you used
> to know.)
>
I'll use that query to get more information when I do the test again.
> Regards,
>
> mwf
>
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Sami Seerangan
> Sent: Friday, December 23, 2005 3:52 PM
> To: oracle-l
> Subject: TX wait on I_OBJ# and C_OBJ#
>
> While we are updating one of the application table with high number of
> concurrency(update) we started seeing
> TX wait on I_OBJ# and C_OBJ# (object id 2 and 3) as below.
>
> select * from gv$lock where request !=0;
> INST_ID SID TY ID1 ID2 LMODE REQUEST CTIME
> BLOCK
> ---------- -------- -- ---------- ---------- ---------- ----------
> ---------- ----------
> 1 50 TX 2424846 2 0 6 519
> 0
> 1 116 TX 7733248 2 0 6 4
> 0
> 1 24 TX 7667720 3 0 6 16
> 0
> 1 110 TX 7798790 3 0 6 5
> 0
>
> The above locks are increased by increasing the number of concurrency.
>
> SQL> select id2,count(*) from gv$lock where lmode=6 and id2 in (2,3)
> group by id2;
>
> ID2 COUNT(*)
> ---------- ----------
> 2 132
> 3 132
>
>
> 132, is it a limit to acquire number of TX locks on I_OBJ# and C_OBJ#?
>
> Thanks in advance.
> Sami
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
