Hi,

Today I looked at v$lock and noticed a lot of TO locks. I can't find any reference on Metalink so I did some trial-and-error and research. I find that these are all locks placed on global temporary tables. v$lock.ID1 is the temporay table's object_id. In PUNOCI, they all point to APPLSYS.MO_GLOB_ORG_ACCESS_TMP (for its description, see http://etrm.oracle.com/pls/etrmlatest/etrm_pnav.show_object?c_name=MO_GLOB_ORG_ACCESS_TMP&c_owner=APPLSYS&c_type=TABLE). Why do I talk about this? Look at this:

SQL> select segtype, count(*) from v$sort_usage group by segtype;

SEGTYPE     COUNT(*)
--------- ----------
DATA             327
HASH               2
INDEX            327
LOB_DATA          32
LOB_INDEX         16
SORT               8

6 rows selected.

When segtype = 'DATA', it tells us that the sort segment is not really used for sorting. Instead, it's the temporary table those sessions are using (when it's 'INDEX', it's the index on that temporary table). The above SQL tells us that most of the activities in TEMP tablespace are on that temporary table, not due to sorting. I admit I didn't find this earlier and suggested we increase sort_area_size. (Although setting sort_area_size to 20 MB is good generally, it probably won't help improve performance for most our sessions.)

Yong



> I have TO type locks in my db, and can't find what those are.
> The object is a table and it's an exclusive row-lock
> 
> The article "V$LOCK" Reference Note" on Metalink does not show a type TO

TO locks are locks on global temporary tables. v$lock.id1 is
dba_objects.object_id. For instance, in my Apps 11i database, I
currently have more than 200 TO locks all placed on
applsys.MO_GLOB_ORG_ACCESS_TMP. They correspond to the rows in
v$sort_usage where segtype = 'DATA'. In my case, the lock mode is 3
(row exclusive).

Because this name happens to be a common English word, it's hard to
search either on Metalink or Google.

Yong Huang