Re: GTT insert issue
- From: "ajeet ojha" <oraclev28@xxxxxxxxx>
- To: oracle.tutorials@xxxxxxxxx
- Date: Wed, 10 Oct 2007 20:54:11 +0530
two things you may like to check -
1...total time the select distinct col1,col2 from t takes...that is elapsed
time to get all the records not just few records...
2...if 1 is ok - then size of your temp tablespace...if it is too less then
it could be causing the problem..
you can check that what are the segments on which db file sequential read
wait is..if it is temp segments then - your temp tblspc is too small..and
you should incresae it.
On 10/5/07, DBA Deepak <oracle.tutorials@xxxxxxxxx> wrote:
>
> 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
>
Other related posts:
