Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: GTT insert issue

Re: GTT insert issue

From: ajeet ojha <oraclev28_at_gmail.com>
Date: Wed, 10 Oct 2007 20:54:11 +0530
Message-ID: <4303736f0710100824wed2e130jf2e2d819bb36147f@mail.gmail.com>


 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_at_gmail.com> 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
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 10 2007 - 10:24:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US