DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: Urgent Issue on temporary tablespace

  1. #11
    Join Date
    May 2001
    Posts
    57

    Angry

    Thanks a lot, looks like it worked. But the other problem is the stored procedure which my developer runs gets into a long operation of an hash join. Is there a way to tune this. It is almost taking 20mins to execute this procedure. The same procedure runs at just a click from the application on my Acceptance environment. Can you suggest me a lead into this please.

  2. #12
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by dba_akram
    Thanks a lot, looks like it worked. But the other problem is the stored procedure which my developer runs gets into a long operation of an hash join. Is there a way to tune this. It is almost taking 20mins to execute this procedure. The same procedure runs at just a click from the application on my Acceptance environment. Can you suggest me a lead into this please.
    Good to hear the TEMP stuff is fixed.

    Now, is it so that same procedure runs for 20' and less than 1' in two different platforms or what? Is it a long procedure? Post it here if possible.

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  3. #13
    Join Date
    May 2001
    Posts
    57
    Can email the procedure to you at ocp_9i@yahoo.com? Please let me know if it is ok. The same procedure when run on same platform OS but two different servers. I have 4 dev instances on server1 and two test instances on server2. The proc when run on all the 4 dev instances takes this long but on test it is very fast indeed. I was just wondering if there is any OS environement which Iam not aware of to look at. The init parm files look the same. Please advice.

  4. #14
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Email it to me and let's start from there.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  5. #15
    Join Date
    May 2001
    Posts
    57

    Angry

    Hi Julian,
    I have just sent you an email attaching the stored proc. I ran the stored proc and got the message as below. Now i have to get my sys admn to free some space for me in the OS. For me it looks that the proc should not take more 1Gb of space. My temp tablespace size is 1 gb. Please look at the stored procedure and provide me with suggestions. This stored procedure goes into a long operation of Hash join and the subsequent plan had lot of hash outer joins. I will also send you the explain plan output.
    STAGE.IDB>exec sopvcconfig(2348,vc)
    BEGIN sopvcconfig(2348,vc); END;

    *
    ERROR at line 1:
    ORA-01652: unable to extend temp segment by 448 in tablespace TEMP
    ORA-06512: at "IDBOWNER.SOPVCCONFIG", line 185
    ORA-06512: at line 1


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