GTT insert issue

  • From: "DBA Deepak" <oracle.tutorials@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 5 Oct 2007 17:13:45 +0530

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 on Solaris 9.

Please help me on this.


Oracle DBA

Other related posts: