GTT insert issue

Hi Experts,

Need your help in one issue pertaining to Global Temporary Tables(GTT).

Have created a GTT with "on commit preserve rows" option.

The purpose of creating GTT here is to insert distinct values into the GTT from a table. The source table has an index on the columns for which we need to compute the distinct values.

In my case multiple sessions keep doing the same operation simultaneously on the GTT.

My insert statement is as follows...

insert into my_gtt
select distinct col1,col2 from t1;

The above insert operation is waiting on "db file sequencial read" wait events for ever.

When I executed the query part of the above insert statement (select distinct col1,col2 from t1;) the result came in less than a second.

Am unable to really understand what is making the insert operation slow. When I examined the v$lock view I found that all the sessions have aquired TO & TM locks on the GTT and no one is blocking the other.

One thing I have observed is, when only one session is inserting into the GTT then the insertion is getting complete quite fast.

Am using Oracle 10.2.0.3 on Solaris 9.

Please help me on this.

--

Regards,

Deepak
Oracle DBA

--

http://www.freelists.org/webpage/oracle-l Received on Fri Oct 05 2007 - 06:43:45 CDT