DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Urgent: ora-1652 after using PARALLEL hint

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    Urgent: ora-1652 after using PARALLEL hint

    Hi all,

    We are getting ora-1652 unable to extend temp tablespace for one particular report after using PARALLEL hint in the report query. We were not getting this error before using the hint. We are using Oracle 11i Apps with 8.1.7.4 database.
    This is happening in Production and this report is very urgent. Without Parallel hint, it is taking more time and also consuming lots of resources.

    Thanks in Adv.
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    So what is preventing you from extending the temporary tablespace?

    It is normal that execution plan has changed with PARALEL hint and the new plan might introduce nuch more sorting than the original one.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Originally posted by jmodic
    So what is preventing you from extending the temporary tablespace?

    It is normal that execution plan has changed with PARALEL hint and the new plan might introduce nuch more sorting than the original one.
    Hi,

    I added 500M to the existing temp tablespace. Now, it is 2.5GB. We did not have this problem in last many months.

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Doesn't each parallel worker consume TEMP space? If you're using DEGREE N, you might need N times more temp space.
    Jeff Hunter

  5. #5
    Join Date
    Jan 2001
    Posts
    3,134
    Originally posted by marist89
    Doesn't each parallel worker consume TEMP space? If you're using DEGREE N, you might need N times more temp space.
    I beleive so, that is the whole concept of parallel, to oopen multiple sessions and run your (query, DDL, whatever) faster.
    But would that explain the TEMP growth? Does it matter if it is one session or multiple sessions, shouldn't the TEMP usage be the same?


    MH
    I remember when this place was cool.

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Yeah, maybe that's right. I could see where you might need more, but maybe not N times more.
    Jeff Hunter

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Can we see the execution plan of the query in question with and without the parallel hint (plus some additional and relevant info about the table(s), index(es) etc.. involwed would be nice, too).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width