Loading ...
Sorry, an error occurred while loading the content.

Re: PeopleSoft DBA Forum Problem with PSTEMP getting very big

Expand Messages
  • David Kurtz
    If this is how you created the tablespace ... /global/gl_ha/oracledbfiles/oradata/FSPROD/pstemp01.dbf SIZE ... You have a tablespace that is of type
    Message 1 of 6 , Oct 10, 2003
    • 0 Attachment
      If this is how you created the tablespace

      > CREATE TABLESPACE PSTEMP
      > DATAFILE
      '/global/gl_ha/oracledbfiles/oradata/FSPROD/pstemp01.dbf' SIZE
      > 8704000K AUTOEXTEND ON NEXT 20480K MAXSIZE UNLIMITED
      > EXTENT MANAGEMENT DICTIONARY
      > LOGGING
      > DEFAULT STORAGE(INITIAL 1M
      > NEXT 1M
      > MINEXTENTS 1
      > PCTINCREASE 0)
      > ONLINE
      > TEMPORARY
      > /

      You have a tablespace that is of type temporary - so it cannot contain
      any permanent objects. It should behave as previously described.

      It uses a permanent datafile rather than a temp file. There are some
      advantages to using a temp file. For one thing it doesn't need to be
      backed up because it is not recoverable. There ought to be less redo
      logging, but I haven't tested that.

      It would appear that by the time you ran your query on v$sort_usage,
      somebody was already doing a sort to disk.

      Yes, if you use the kill command you can have shadow processes
      continuing to run in background. Especially when you kill the client
      while it is waiting for a query to return a row.

      You get the SID and SERIAL# from v$session.


      _________________________
      David Kurtz
      Go-Faster Consultancy Ltd.
      tel: +44 (0)7771 760660
      fax: +44 (0)7092 348865
      mailto:david.kurtz@...
      web: www.go-faster.co.uk
      PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
      <http://groups.yahoo.com/group/psftdba>
    • srini Mandala
      As David suggested its better to have temporary tablespace rather than permanent tablespace. One More suggestion, donot use Extent Managment Dicitionary. In
      Message 2 of 6 , Oct 10, 2003
      • 0 Attachment
        As David suggested its better to have temporary tablespace rather than permanent tablespace.
        One More suggestion, donot  use Extent Managment Dicitionary. In the case If you have bigger temp space its good idea to have extent managment Local, that way we can eliminate some Oracle Smon prosessing to claen up the extents.
         
        --Srini

        David Kurtz <info2@...> wrote:
        If this is how you created the tablespace

        > CREATE TABLESPACE PSTEMP
        >     DATAFILE
        '/global/gl_ha/oracledbfiles/oradata/FSPROD/pstemp01.dbf' SIZE
        > 8704000K AUTOEXTEND ON NEXT 20480K MAXSIZE UNLIMITED
        >     EXTENT MANAGEMENT DICTIONARY
        >     LOGGING
        >     DEFAULT STORAGE(INITIAL 1M
        >                     NEXT 1M
        >                     MINEXTENTS 1
        >                     PCTINCREASE 0)
        >     ONLINE
        >     TEMPORARY
        > /

        You have a tablespace that is of type temporary - so it cannot contain
        any permanent objects.  It should behave as previously described.

        It uses a permanent datafile rather than a temp file.  There are some
        advantages to using a temp file.  For one thing it doesn't need to be
        backed up because it is not recoverable.  There ought to be less redo
        logging, but I haven't tested that.

        It would appear that by the time you ran your query on v$sort_usage,
        somebody was already doing a sort to disk.

        Yes, if you use the kill command you can have shadow processes
        continuing to run in background.  Especially when you kill the client
        while it is waiting for a query to return a row.

        You get the SID and SERIAL# from v$session.


        _________________________
        David Kurtz
        Go-Faster Consultancy Ltd.
        tel: +44 (0)7771 760660
        fax: +44 (0)7092 348865
        mailto:david.kurtz@...
        web: www.go-faster.co.uk
        PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
        <http://groups.yahoo.com/group/psftdba




        To unsubscribe from this group, send an email to:
        psftdba-unsubscribe@yahoogroups.com

        The PeopleSoft DBA Forum is managed by Go-Faster Consultancy Ltd. http://www.go-faster.co.uk


        Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


        Do you Yahoo!?
        The New Yahoo! Shopping - with improved product search

      • michael.ngong@crown.com
        The question of books.......... There are many but in this case you may be looking at tuning books My recommendations would be The Arts and Science of Oracle
        Message 3 of 6 , Oct 13, 2003
        • 0 Attachment
          The question of books..........
          There are many but in this case you may be looking at tuning books
          My recommendations would be
          The Arts and Science of Oracle performance Tuning Christopher Lawson  ISBN 1-59059-199-2
          Oracle Performance Tuning 101 Gaja Krishna  Vaidyanatha ISBN- 0-07-213145-4

          Thank you
          Mike Ngong



          Phillip Eaton <phillip.eaton@...>

          10/13/2003 05:57 AM
          Please respond to psftdba

                 
                  To:        "'psftdba@yahoogroups.com'" <psftdba@yahoogroups.com>
                  cc:        
                  Subject:        RE: PeopleSoft DBA Forum Problem with PSTEMP getting very big



          Thanks to all of you for your suggestions.
           
          As it turns out, the problem was not caused by a big SQR being cancelled 1/2 way through, it was caused by a user running some crazy adhoc nVisions using the Windows client and then ctrl-alt-deleting & killing Excel when they were running slowly.
           
          Yes, yes, I know, I shouldn't have users running their own home made queries, but we're a small organisation and there's only two of us that support and develop the PeopleSoft system, among others. Our users run what they have to run as their Boss wants them to report something different every day - we just have to pick up the pieces when they break it all. (Fortunately not that often.)
           
          As my problem hadn't appeared in my TEST or DEV environment, I decided to put a log on the file size of the Oracle database file for that tablespace after dropping and recreating it. During the day, the file size shot up, so I took hold of my baseball bat and walked around our Financial Control department with a serious expression. The guilty man soon made himself known by hiding more than the others.
           
          ---------------
           
          As an aside, I've learned a fair amount about Oracle from your postings. Is there a good starter book that you would recommend for getting under the hood of Oracle, bearing in mind I'm coming from a PeopleSoft point of view? I guess I'm, looking for a DBA type book, overall, preferably something that tells you about Oracle for UNIX and Windows.
           
          BTW - regarding a previous post I made regarding Oracle killing my Windows 2000 machine after installation, I had a phone call from Gerry Leith from CoolTools who mentioned that it might be something to do with my Ethernet card not having actually being connected to anything. Has anyone else had this problem?
           
          I've not actually resolved the problem yet, as I had to uninstall Oracle to use the PC, and installing it again (for the 3rd time) is not something I fancy doing if I'm just going to have to uninstall it again.
           
          Cheers,
          Phillip Eaton
          ---------------------------------------

          PeopleSoft Administration & Development

          Gulf International Bank (UK) Ltd.

          +44 (0)20 7259 3402

          phillip.eaton@...

           
          -----Original Message-----
          From:
          srini Mandala [mailto:srinivasmandala@...]
          Sent:
          11 October 2003 00:29
          To:
          psftdba@yahoogroups.com
          Subject:
          Re: PeopleSoft DBA Forum Problem with PSTEMP getting very big

          As David suggested its better to have temporary tablespace rather than permanent tablespace.
          One More suggestion, donot  use Extent Managment Dicitionary. In the case If you have bigger temp space its good idea to have extent managment Local, that way we can eliminate some Oracle Smon prosessing to claen up the extents.
           
          --Srini

          David Kurtz <info2@...>
          wrote:

          If this is how you created the tablespace

          > CREATE TABLESPACE PSTEMP
          >     DATAFILE
          '/global/gl_ha/oracledbfiles/oradata/FSPROD/pstemp01.dbf' SIZE
          > 8704000K AUTOEXTEND ON NEXT 20480K MAXSIZE UNLIMITED
          >     EXTENT MANAGEMENT DICTIONARY
          >     LOGGING
          >     DEFAULT STORAGE(INITIAL 1M
          >                     NEXT 1M
          >                     MINEXTENTS 1
          >                     PCTINCREASE 0)
          >     ONLINE
          >     TEMPORARY
          > /

          You have a tablespace that is of type temporary - so it cannot contain
          any permanent objects.  It should behave as previously described.

          It uses a permanent datafile rather than a temp file.  There are some
          advantages to using a temp file.  For one thing it doesn't need to be
          backed up because it is not recoverable.  There ought to be less redo
          logging, but I haven't tested that.

          It would appear that by the time you ran your query on v$sort_usage,
          somebody was already doing a sort to disk.

          Yes, if you use the kill command you can have shadow processes
          continuing to run in background.  Especially when you kill the client
          while it is waiting for a query to return a row.


          You get the SID and SERIAL# from v$session.


          _________________________
          David Kurtz
          Go-Faster Consultancy Ltd.
          tel: +44 (0)7771 760660
          fax: +44 (0)7092 348865
          mailto:david.kurtz@...
          web: www.go-faster.co.uk
          PeopleSoft DBA Forum:
          http://groups.yahoo.com/group/psftdba
          <
          http://groups.yahoo.com/group/psftdba>  




          To unsubscribe from this group, send an email to:
          psftdba-unsubscribe@yahoogroups.com

          The PeopleSoft DBA Forum is managed by Go-Faster Consultancy Ltd.
          http://www.go-faster.co.uk

          Your use of Yahoo! Groups is subject to the
          Yahoo! Terms of Service.


          Do you Yahoo!?
          The New Yahoo! Shopping - with improved product search

          To unsubscribe from this group, send an email to:
          psftdba-unsubscribe@yahoogroups.com

          The PeopleSoft DBA Forum is managed by Go-Faster Consultancy Ltd.
          http://www.go-faster.co.uk

          Your use of Yahoo! Groups is subject to the
          Yahoo! Terms of Service.

          Yahoo! Groups Sponsor


          To unsubscribe from this group, send an email to:
          psftdba-unsubscribe@yahoogroups.com

          The PeopleSoft DBA Forum is managed by Go-Faster Consultancy Ltd.
          http://www.go-faster.co.uk

          Your use of Yahoo! Groups is subject to the
          Yahoo! Terms of Service.

        Your message has been successfully submitted and would be delivered to recipients shortly.