Home » SQL & PL/SQL » SQL & PL/SQL » GTT Problem (Oracle 10g)
GTT Problem [message #409920] Wed, 24 June 2009 08:02 Go to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
hi,

I am using GTT(Global Temp Tables) in my procedure to insert values from large queries and at the end insert it one static table.
Many times while inserting the rows from queries in to GTT will hung my session.And this happened many times.
Any information about this will really be appritiated.

Thanks Ravi
Re: GTT Problem [message #409922 is a reply to message #409920] Wed, 24 June 2009 08:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
With information that sparse, there's not a lot wa can do to help.

Can you find a situation where it repeatably hangs?

Oracle very rarely 'hangs' - it's almost invariably either waiting for something, or doing something that is taking a very long time.
Next time it 'hangs', have a look at the contents of v$session_wait for the session that has stopped responding.
Re: GTT Problem [message #409925 is a reply to message #409920] Wed, 24 June 2009 08:25 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
Thanks for replying ,

But i am unable to find any information with this view.
below is the out put of this view.
        SID     SEQ#    EVENT   P1TEXT  P1      P1RAW   P2TEXT  P2      P2RAW   P3TEXT  P3      P3RAW   WAIT_CLASS_ID   WAIT_CLASS#     WAIT_CLASS      WAIT_TIME       SECONDS_IN_WAIT STATE
1       669     59      db file scattered read  file#   216     00000000000000D8        block#  13769   00000000000035C9        blocks  32      0000000000000020        1740759767      8       User I/O        -1      1269    WAITED SHORT TIME
Re: GTT Problem [message #409926 is a reply to message #409920] Wed, 24 June 2009 08:32 Go to previous messageGo to next message
cookiemonster
Messages: 12418
Registered: September 2008
Location: Rainy Manchester
Senior Member
try tracing the session
Re: GTT Problem [message #409928 is a reply to message #409920] Wed, 24 June 2009 08:44 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
how to trace the session!
Re: GTT Problem [message #409930 is a reply to message #409925] Wed, 24 June 2009 09:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ok, the fact that the wait event is a DB IO event strongly suggests that the Db session is not hung - it's just running a long-running piece of SQL.

Look at v$session_longops for that session, for rows where SOFAR != TOTALWORK - this shuold show you what it's doing.

Do you know what the SQL it's running is?
Re: GTT Problem [message #409934 is a reply to message #409928] Wed, 24 June 2009 09:16 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
ravi214u wrote on Wed, 24 June 2009 06:44
how to trace the session!


try using SEARCH of this forum!
Re: GTT Problem [message #409937 is a reply to message #409920] Wed, 24 June 2009 09:20 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
Yes i know , If i run the same sql it takes only few sec to complete.

INSERT INTO CAL_UNUSED_TAB1C
with TAB1 as (
select V_MCODE from (
SELECT A.V_MCODE,A.N_PRE_AMT,V_F_AMT , SUM(CASE WHEN (A.N_PRE_AMT) < 2* V_F_AMT THEN 0 ELSE 1 END) AS CNDO
FROM FCT_SUB A,FCT_SEC_EXP_TEMP B
WHERE A.S_DATE=TO_DATE(20090623,'YYYYMMDD')
AND A.ID NOT IN ('1189693873984','1232118571716','1228486119630','1216933564454','1232117765378','1214424191247')
AND A.F_EL='C'
AND A.E_ID=B.E_ID
GROUP BY  A.V_MCODE ,A.N_PRE_AMT,V_F_AMT)X where X.CNDO=0
),TAB2 as (select * from UNS_TEMP2C )
select * from TAB2,TAB1
where TAB2.V_MCODE=TAB1.V_MCODE;


Re: GTT Problem [message #409958 is a reply to message #409937] Wed, 24 June 2009 10:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
how many of those hard coded values are acutally bind variables?
Re: GTT Problem [message #409963 is a reply to message #409920] Wed, 24 June 2009 10:29 Go to previous message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
only A.S_DATE=TO_DATE(20090623,'YYYYMMDD') is bind variable


A.ID NOT IN ('1189693873984','1232118571716','1228486119630','1216933564454','1232117765378','1214424191247')
is hard coded.

I just put /* + APPEND */ in this query and it come out very quickly.
Previous Topic: "ALTER TABLE ... ORGANIZATION INDEX" error
Next Topic: PRIVILEGES LIST/REFERENCE
Goto Forum:
  


Current Time: Thu Dec 08 04:23:20 CST 2016

Total time taken to generate the page: 0.08803 seconds