Re: TX wait on I_OBJ# and C

From: Sami Seerangan <dba.orcl_at_gmail.com>
Date: 2005-12-24 20:42:31
Message-id: f09dd6280512241142o30f147d4j6827a2c227ee6dd3@mail.gmail.com

Thanks Mark.

On 12/24/05, Mark W. Farnham 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?

> 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_at_freelists.org \
[mailto:oracle-l-bounce_at_freelists.org]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
>

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Dec 24 2005 - 20:42:31 CST

Re: TX wait on I_OBJ# and C_OBJ#