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

RE: PeopleSoft DBA Forum Long truncate times on the TOA tables

Expand Messages
  • Willem.Leenen@ingrealestate.com
    Hello David, I have tested your suggestions this weekend on the production server and the problem was indeed a matter of concurrent use of temptables. Thank
    Message 1 of 15 , Feb 2, 2009
    • 0 Attachment

      Hello David,

       

      I have tested your suggestions this weekend on the production server and the problem was indeed a matter of concurrent use of temptables. Thank you for your kind help,

       

      Regards,

      Willem

       

       


      From: psftdba@yahoogroups.com [mailto: psftdba@yahoogroups.com ] On Behalf Of David Kurtz
      Sent: vrijdag 30 januari 2009 14:14
      To: psftdba@yahoogroups.com
      Subject: RE: PeopleSoft DBA Forum Long truncate times on the TOA tables

       

      http://blog. psftdba.com/ 2009/01/performa nce-implications -of-concurrent. html

       

      regards
      ____________ _________ ____
      David Kurtz
      Go-Faster Consultancy Ltd.
      tel: +44 (0)7771 760660
      fax: +44 (0)7092 348865
      mailto:david. kurtz@go- faster.co. uk
      web: www.go-faster. co.uk
      Book: PeopleSoft for the Oracle DBA: http://www.psftdba. com
      DBA Blogs: PeopleSoft: http://blog. psftdba.com, Oracle: http://blog. go-faster. co.uk
      PeopleSoft DBA Forum: http://groups. yahoo.com/ group/psftdba

       

       


      From: psftdba@yahoogroups .com [mailto: psftdba@ yahoogroups. com ] On Behalf Of Willem.Leenen@ ingrealestate. com
      Sent: Friday, January 30, 2009 12:14 PM
      To: psftdba@yahoogroups .com
      Subject: PeopleSoft DBA Forum Long truncate times on the TOA tables

      Hello *,

       

      I am experiencing very long waits on Truncate statements during the night batches. Local Write Waits shows 5 truncates taking about 5 minutes each. RDBMS ipc reply is also very high. I checked the harddrives with sar, but I do not see a peak in performance degredation there. CPU has always idle time, so I turned again to Oracle. Most of the Metalink documents point into the direction of ‘make you DBWR process faster’ , check your memory (enough PGA etc) and other settings. However I cannot see anything wrong with my DB, and I start to hesitate that this is the correct direction.

      Is this a frequent issue in PPLsoft? Has anyone observed this behaviour before?

       

      Thanks for any pointers,

      W.L.

      ------------ --------- --------- --------- --------- --------- --------
      ATTENTION:
      The information in this electronic mail message is private and
      confidential, and only intended for the addressee. Should you
      receive this message by mistake, you are hereby notified that
      any disclosure, reproduction, distribution or use of this
      message is strictly prohibited. Please inform the sender by
      reply transmission and delete the message without copying or
      opening it.
        
      Messages and attachments are scanned for all viruses known.
      If this message contains password-protected attachments, the
      files have NOT been scanned for viruses by the ING mail domain.
      Always scan attachments before opening them.
      ------------ --------- --------- --------- --------- --------- --------
        

      -----------------------------------------------------------------
      ATTENTION:
      The information in this electronic mail message is private and
      confidential, and only intended for the addressee. Should you
      receive this message by mistake, you are hereby notified that
      any disclosure, reproduction, distribution or use of this
      message is strictly prohibited. Please inform the sender by
      reply transmission and delete the message without copying or
      opening it.
      
      Messages and attachments are scanned for all viruses known.
      If this message contains password-protected attachments, the
      files have NOT been scanned for viruses by the ING mail domain.
      Always scan attachments before opening them.
      -----------------------------------------------------------------
      
      
    • David Kurtz
      Have you tried moving the temp tables to a tablespace with a larger blocksize? Is so, how much difference did it make? regards _________________________ David
      Message 2 of 15 , Feb 2, 2009
      • 0 Attachment
        Have you tried moving the temp tables to a tablespace with a larger blocksize?
        Is so, how much difference did it make?
         

        regards
        _________________________
        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
        Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
        DBA Blogs: PeopleSoft: http://blog.psftdba.com, Oracle: http://blog.go-faster.co.uk
        PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

         


        From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of Willem.Leenen@...
        Sent: Monday, February 02, 2009 9:15 AM
        To: psftdba@yahoogroups.com
        Subject: RE: PeopleSoft DBA Forum Long truncate times on the TOA tables

        Hello David,

         

        I have tested your suggestions this weekend on the production server and the problem was indeed a matter of concurrent use of temptables. Thank you for your kind help,

         

        Regards,

        Willem

         

         


        From: psftdba@yahoogroups.com [mailto: psftdba@yahoogroups.com ] On Behalf Of David Kurtz
        Sent: vrijdag 30 januari 2009 14:14
        To: psftdba@yahoogroups.com
        Subject: RE: PeopleSoft DBA Forum Long truncate times on the TOA tables

         

        http://blog. psftdba.com/ 2009/01/performa nce-implications -of-concurrent. html

         

        regards
        ____________ _________ ____
        David Kurtz
        Go-Faster Consultancy Ltd.
        tel: +44 (0)7771 760660
        fax: +44 (0)7092 348865
        mailto:david. kurtz@go- faster.co. uk
        web: www.go-faster. co.uk
        Book: PeopleSoft for the Oracle DBA: http://www.psftdba. com
        DBA Blogs: PeopleSoft: http://blog. psftdba.com, Oracle: http://blog. go-faster. co.uk
        PeopleSoft DBA Forum: http://groups. yahoo.com/ group/psftdba

         

         


        From: psftdba@yahoogroups .com [mailto: psftdba@ yahoogroups. com ] On Behalf Of Willem.Leenen@ ingrealestate. com
        Sent: Friday, January 30, 2009 12:14 PM
        To: psftdba@yahoogroups .com
        Subject: PeopleSoft DBA Forum Long truncate times on the TOA tables

        Hello *,

         

        I am experiencing very long waits on Truncate statements during the night batches. Local Write Waits shows 5 truncates taking about 5 minutes each. RDBMS ipc reply is also very high. I checked the harddrives with sar, but I do not see a peak in performance degredation there. CPU has always idle time, so I turned again to Oracle. Most of the Metalink documents point into the direction of ‘make you DBWR process faster’ , check your memory (enough PGA etc) and other settings. However I cannot see anything wrong with my DB, and I start to hesitate that this is the correct direction.

        Is this a frequent issue in PPLsoft? Has anyone observed this behaviour before?

         

        Thanks for any pointers,

        W.L.

        ------------ --------- --------- --------- --------- --------- --------
        ATTENTION:
        The information in this electronic mail message is private and
        confidential, and only intended for the addressee. Should you
        receive this message by mistake, you are hereby notified that
        any disclosure, reproduction, distribution or use of this
        message is strictly prohibited. Please inform the sender by
        reply transmission and delete the message without copying or
        opening it.
          
        Messages and attachments are scanned for all viruses known.
        If this message contains password-protected attachments, the
        files have NOT been scanned for viruses by the ING mail domain.
        Always scan attachments before opening them.
        ------------ --------- --------- --------- --------- --------- --------
          

        -----------------------------------------------------------------
        ATTENTION:
        The information in this electronic mail message is private and
        confidential, and only intended for the addressee. Should you
        receive this message by mistake, you are hereby notified that
        any disclosure, reproduction, distribution or use of this
        message is strictly prohibited. Please inform the sender by
        reply transmission and delete the message without copying or
        opening it.
        
        Messages and attachments are scanned for all viruses known.
        If this message contains password-protected attachments, the
        files have NOT been scanned for viruses by the ING mail domain.
        Always scan attachments before opening them.
        -----------------------------------------------------------------
        
        
      • Willem.Leenen@ingrealestate.com
        No, I don t want to turn to oracle solutions yet for a problem I consider to be rooted from application design - or a scheduling matter. I m first looking for
        Message 3 of 15 , Feb 2, 2009
        • 0 Attachment

          No, I don’t want to turn to oracle solutions yet for a problem I consider to be rooted from application design – or a scheduling matter. I’m first looking for ways to serialize the reports, and I will make a report on which other batches potentially overlap each other.

           

          If I can reproduce the situation in my testenvironment I will include the scenario of the larger blocksize, if you are interested in the results.

           

          Regards,

          Willem

           


          From: psftdba@yahoogroups.com [mailto: psftdba@yahoogroups.com ] On Behalf Of David Kurtz
          Sent: maandag 2 februari 2009 10:29
          To: psftdba@yahoogroups.com
          Subject: RE: PeopleSoft DBA Forum Long truncate times on the TOA tables

           

          Have you tried moving the temp tables to a tablespace with a larger blocksize?

          Is so, how much difference did it make?

           

          regards
          ____________ _________ ____
          David Kurtz
          Go-Faster Consultancy Ltd.
          tel: +44 (0)7771 760660
          fax: +44 (0)7092 348865
          mailto:david. kurtz@go- faster.co. uk
          web: www.go-faster. co.uk
          Book: PeopleSoft for the Oracle DBA: http://www.psftdba. com
          DBA Blogs: PeopleSoft: http://blog. psftdba.com, Oracle: http://blog. go-faster. co.uk
          PeopleSoft DBA Forum: http://groups. yahoo.com/ group/psftdba

           

           


          From: psftdba@yahoogroups .com [mailto: psftdba@ yahoogroups. com ] On Behalf Of Willem.Leenen@ ingrealestate. com
          Sent: Monday, February 02, 2009 9:15 AM
          To: psftdba@yahoogroups .com
          Subject: RE: PeopleSoft DBA Forum Long truncate times on the TOA tables

          Hello David,

           

          I have tested your suggestions this weekend on the production server and the problem was indeed a matter of concurrent use of temptables. Thank you for your kind help,

           

          Regards,

          Willem

           

           


          From: psftdba@yahoogroups .com [mailto: psftdba@yahoogroups .com ] On Behalf Of David Kurtz
          Sent: vrijdag 30 januari 2009 14:14
          To: psftdba@yahoogroups .com
          Subject: RE: PeopleSoft DBA Forum Long truncate times on the TOA tables

           

          http://blog. psftdba.com/ 2009/01/performa nce-implications -of-concurrent. html

           

          regards
          ____________ _________ ____
          David Kurtz
          Go-Faster Consultancy Ltd.
          tel: +44 (0)7771 760660
          fax: +44 (0)7092 348865
          mailto:david. kurtz@go- faster.co. uk
          web: www.go-faster. co.uk
          Book: PeopleSoft for the Oracle DBA: http://www.psftdba. com
          DBA Blogs: PeopleSoft: http://blog. psftdba.com, Oracle: http://blog. go-faster. co.uk
          PeopleSoft DBA Forum: http://groups. yahoo.com/ group/psftdba

           

           


          From: psftdba@yahoogroups .com [mailto: psftdba@ yahoogroups. com ] On Behalf Of Willem.Leenen@ ingrealestate. com
          Sent: Friday, January 30, 2009 12:14 PM
          To: psftdba@yahoogroups .com
          Subject: PeopleSoft DBA Forum Long truncate times on the TOA tables

          Hello *,

           

          I am experiencing very long waits on Truncate statements during the night batches. Local Write Waits shows 5 truncates taking about 5 minutes each. RDBMS ipc reply is also very high. I checked the harddrives with sar, but I do not see a peak in performance degredation there. CPU has always idle time, so I turned again to Oracle. Most of the Metalink documents point into the direction of ‘make you DBWR process faster’ , check your memory (enough PGA etc) and other settings. However I cannot see anything wrong with my DB, and I start to hesitate that this is the correct direction.

          Is this a frequent issue in PPLsoft? Has anyone observed this behaviour before?

           

          Thanks for any pointers,

          W.L.

          ------------ --------- --------- --------- --------- --------- --------
          ATTENTION:
          The information in this electronic mail message is private and
          confidential, and only intended for the addressee. Should you
          receive this message by mistake, you are hereby notified that
          any disclosure, reproduction, distribution or use of this
          message is strictly prohibited. Please inform the sender by
          reply transmission and delete the message without copying or
          opening it.
            
          Messages and attachments are scanned for all viruses known.
          If this message contains password-protected attachments, the
          files have NOT been scanned for viruses by the ING mail domain.
          Always scan attachments before opening them.
          ------------ --------- --------- --------- --------- --------- --------
            
          ------------ --------- --------- --------- --------- --------- --------
          ATTENTION:
          The information in this electronic mail message is private and
          confidential, and only intended for the addressee. Should you
          receive this message by mistake, you are hereby notified that
          any disclosure, reproduction, distribution or use of this
          message is strictly prohibited. Please inform the sender by
          reply transmission and delete the message without copying or
          opening it.
            
          Messages and attachments are scanned for all viruses known.
          If this message contains password-protected attachments, the
          files have NOT been scanned for viruses by the ING mail domain.
          Always scan attachments before opening them.
          ------------ --------- --------- --------- --------- --------- --------
            

          -----------------------------------------------------------------
          ATTENTION:
          The information in this electronic mail message is private and
          confidential, and only intended for the addressee. Should you
          receive this message by mistake, you are hereby notified that
          any disclosure, reproduction, distribution or use of this
          message is strictly prohibited. Please inform the sender by
          reply transmission and delete the message without copying or
          opening it.
          
          Messages and attachments are scanned for all viruses known.
          If this message contains password-protected attachments, the
          files have NOT been scanned for viruses by the ING mail domain.
          Always scan attachments before opening them.
          -----------------------------------------------------------------
          
          
        • hemant_chitale
          Hmm. My understanding that these bugs were fixed in 10.2 I d even gone to Patch#6844739 which is a consolidated fix for a number of Bugs (see Note#6844739.8
          Message 4 of 15 , Feb 2, 2009
          • 0 Attachment
            Hmm. My understanding that these bugs were fixed in 10.2
            I'd even gone to Patch#6844739 which is a consolidated fix for a
            number of Bugs (see Note#6844739.8 for the "list and history of
            bugs").

            Have you tried with this Patch in place ?

            --- In psftdba@yahoogroups.com, "David Kurtz" <david.kurtz@...> wrote:
            >
            > Have you tried moving the temp tables to a tablespace with a larger
            > blocksize?
            > Is so, how much difference did it make?
            >
            >
            > regards
            > _________________________
            > 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
            > Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
            > <http://www.psftdba.com/>
            > DBA Blogs: PeopleSoft: http://blog.psftdba.com
            <http://blog.psftdba.com/> ,
            > Oracle: http://blog.go-faster.co.uk <http://blog.go-faster.co.uk/>
            > PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
            >
            >
            >
            >
            >
          • Willem.Leenen@ingrealestate.com
            I understand that you are talking about a bugfix for an indexspace leakage, but how does it relate to the concurrency problem David and I described? BTW We re
            Message 5 of 15 , Feb 2, 2009
            • 0 Attachment

              I understand that you are talking about a bugfix for an indexspace leakage, but how does it relate to the concurrency problem David and I described?

               

              BTW

              We’re having an oracle 9 here.

               


              From: psftdba@yahoogroups.com [mailto: psftdba@yahoogroups.com ] On Behalf Of hemant_chitale
              Sent: maandag 2 februari 2009 11:00
              To: psftdba@yahoogroups.com
              Subject: Re: PeopleSoft DBA Forum Long truncate times on the TOA tables

               


              Hmm. My understanding that these bugs were fixed in 10.2
              I'd even gone to Patch#6844739 which is a consolidated fix for a
              number of Bugs (see Note#6844739. 8 for the "list and history of
              bugs").

              Have you tried with this Patch in place ?

              --- In psftdba@yahoogroups .com, "David Kurtz" <david.kurtz@ ...> wrote:

              >
              > Have you tried moving the temp tables to a tablespace with a larger
              > blocksize?
              > Is so, how much difference did it make?
              >
              >
              > regards
              > ____________ _________ ____
              > 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
              > Book: PeopleSoft for the Oracle DBA: http://www.psftdba. com
              > <http://www.psftdba. com/>
              > DBA Blogs: PeopleSoft: http://blog. psftdba.com
              <http://blog. psftdba.com/> ,
              > Oracle: http://blog. go-faster. co.uk
              <http://blog. go-faster. co.uk/>
              > PeopleSoft DBA Forum: http://groups. yahoo.com/ group/psftdba
              >
              >
              >
              >
              >

              -----------------------------------------------------------------
              ATTENTION:
              The information in this electronic mail message is private and
              confidential, and only intended for the addressee. Should you
              receive this message by mistake, you are hereby notified that
              any disclosure, reproduction, distribution or use of this
              message is strictly prohibited. Please inform the sender by
              reply transmission and delete the message without copying or
              opening it.
              
              Messages and attachments are scanned for all viruses known.
              If this message contains password-protected attachments, the
              files have NOT been scanned for viruses by the ING mail domain.
              Always scan attachments before opening them.
              -----------------------------------------------------------------
              
              
            • hemant_chitale
              No, indexspace leakage is in my other message. Here I am talking of TRUNCATE performance bugs. In 9.2, you certainly should use a seperate block size for your
              Message 6 of 15 , Feb 3, 2009
              • 0 Attachment
                No, indexspace leakage is in my other message.

                Here I am talking of TRUNCATE performance bugs. In 9.2, you
                certainly should use a seperate block size for your temporary tables.
                However, in the 10.2 tests where David also used a seperate block
                size, Patch#6844739 should make it unnecessary, in my opinion.
                Note#6844739.8 lists patches that had earlier been released, each
                patch either not fixing the issue or introducing another issue.


                Hemant Chitale

                --- In psftdba@yahoogroups.com, <Willem.Leenen@...> wrote:
                >
                > I understand that you are talking about a bugfix for an indexspace
                > leakage, but how does it relate to the concurrency problem David
                and I
                > described?
                >
                >
                >
                > BTW
                >
                > We're having an oracle 9 here.
                >
                >
                >
                > ________________________________
                >
                > From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On
                Behalf
                > Of hemant_chitale
                > Sent: maandag 2 februari 2009 11:00
                > To: psftdba@yahoogroups.com
                > Subject: Re: PeopleSoft DBA Forum Long truncate times on the TOA
                tables
                >
                >
                >
                >
                > Hmm. My understanding that these bugs were fixed in 10.2
                > I'd even gone to Patch#6844739 which is a consolidated fix for a
                > number of Bugs (see Note#6844739.8 for the "list and history of
                > bugs").
                >
                > Have you tried with this Patch in place ?
                >
                > --- In psftdba@yahoogroups.com <mailto:psftdba%40yahoogroups.com> ,
                > "David Kurtz" <david.kurtz@> wrote:
                > >
                > > Have you tried moving the temp tables to a tablespace with a
                larger
                > > blocksize?
                > > Is so, how much difference did it make?
                > >
                > >
                > > regards
                > > _________________________
                > > 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
                > > Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
                > <http://www.psftdba.com>
                > > <http://www.psftdba.com/ <http://www.psftdba.com/> >
                > > DBA Blogs: PeopleSoft: http://blog.psftdba.com
                > <http://blog.psftdba.com>
                > <http://blog.psftdba.com/ <http://blog.psftdba.com/> > ,
                > > Oracle: http://blog.go-faster.co.uk <http://blog.go-faster.co.uk>
                > <http://blog.go-faster.co.uk/ <http://blog.go-faster.co.uk/> >
                > > PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
                > <http://groups.yahoo.com/group/psftdba>
                > >
                > >
                > >
                > >
                > >
                >
                >
                >
                > -----------------------------------------------------------------
                > ATTENTION:
                > The information in this electronic mail message is private and
                > confidential, and only intended for the addressee. Should you
                > receive this message by mistake, you are hereby notified that
                > any disclosure, reproduction, distribution or use of this
                > message is strictly prohibited. Please inform the sender by
                > reply transmission and delete the message without copying or
                > opening it.
                >
                > Messages and attachments are scanned for all viruses known.
                > If this message contains password-protected attachments, the
                > files have NOT been scanned for viruses by the ING mail domain.
                > Always scan attachments before opening them.
                > -----------------------------------------------------------------
                >
              • David Kurtz
                Serialisation of Truncates is a consequence of using the RO enqueue to protect local write wait. It is the way Oracle works. It has nothing to do with any
                Message 7 of 15 , Feb 3, 2009
                • 0 Attachment
                  Serialisation of Truncates is a consequence of using the RO enqueue to
                  protect local write wait.
                  It is the way Oracle works. It has nothing to do with any Oracle bug or
                  patch.

                  If you have a patched system, you can download the test script from my
                  website.

                  regards
                  _________________________
                  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
                  Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
                  DBA Blogs: PeopleSoft: http://blog.psftdba.com, Oracle:
                  http://blog.go-faster.co.uk
                  PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

                  >-----Original Message-----
                  >From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]
                  >On Behalf Of hemant_chitale
                  >Sent: Tuesday, February 03, 2009 2:32 PM
                  >To: psftdba@yahoogroups.com
                  >Subject: Re: PeopleSoft DBA Forum Long truncate times on the TOA tables
                  >
                  >
                  >No, indexspace leakage is in my other message.
                  >
                  >Here I am talking of TRUNCATE performance bugs. In 9.2, you
                  >certainly should use a seperate block size for your temporary tables.
                  >However, in the 10.2 tests where David also used a seperate
                  >block size, Patch#6844739 should make it unnecessary, in my opinion.
                  >Note#6844739.8 lists patches that had earlier been released,
                  >each patch either not fixing the issue or introducing another issue.
                  >
                  >
                  >Hemant Chitale
                  >
                  >--- In psftdba@yahoogroups.com, <Willem.Leenen@...> wrote:
                  >>
                  >> I understand that you are talking about a bugfix for an indexspace
                  >> leakage, but how does it relate to the concurrency problem David
                  >and I
                  >> described?
                  >>
                  >>
                  >>
                  >> BTW
                  >>
                  >> We're having an oracle 9 here.
                  >>
                  >>
                  >>
                  >> ________________________________
                  >>
                  >> From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On
                  >Behalf
                  >> Of hemant_chitale
                  >> Sent: maandag 2 februari 2009 11:00
                  >> To: psftdba@yahoogroups.com
                  >> Subject: Re: PeopleSoft DBA Forum Long truncate times on the TOA
                  >tables
                  >>
                  >>
                  >>
                  >>
                  >> Hmm. My understanding that these bugs were fixed in 10.2 I'd
                  >even gone
                  >> to Patch#6844739 which is a consolidated fix for a number of
                  >Bugs (see
                  >> Note#6844739.8 for the "list and history of bugs").
                  >>
                  >> Have you tried with this Patch in place ?
                  >>
                  >> --- In psftdba@yahoogroups.com <mailto:psftdba%40yahoogroups.com> ,
                  >> "David Kurtz" <david.kurtz@> wrote:
                  >> >
                  >> > Have you tried moving the temp tables to a tablespace with a
                  >larger
                  >> > blocksize?
                  >> > Is so, how much difference did it make?
                  >> >
                  >> >
                  >> > regards
                  >> > _________________________
                  >> > 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
                  >> > Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
                  >> <http://www.psftdba.com>
                  >> > <http://www.psftdba.com/ <http://www.psftdba.com/> > DBA Blogs:
                  >> > PeopleSoft: http://blog.psftdba.com
                  >> <http://blog.psftdba.com>
                  >> <http://blog.psftdba.com/ <http://blog.psftdba.com/> > ,
                  >> > Oracle: http://blog.go-faster.co.uk <http://blog.go-faster.co.uk>
                  >> <http://blog.go-faster.co.uk/ <http://blog.go-faster.co.uk/> >
                  >> > PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
                  >> <http://groups.yahoo.com/group/psftdba>
                  >> >
                  >> >
                  >> >
                  >> >
                  >> >
                  >>
                  >>
                  >>
                  >> -----------------------------------------------------------------
                  >> ATTENTION:
                  >> The information in this electronic mail message is private and
                  >> confidential, and only intended for the addressee. Should
                  >you receive
                  >> this message by mistake, you are hereby notified that any
                  >disclosure,
                  >> reproduction, distribution or use of this message is strictly
                  >> prohibited. Please inform the sender by reply transmission
                  >and delete
                  >> the message without copying or opening it.
                  >>
                  >> Messages and attachments are scanned for all viruses known.
                  >> If this message contains password-protected attachments, the files
                  >> have NOT been scanned for viruses by the ING mail domain.
                  >> Always scan attachments before opening them.
                  >> -----------------------------------------------------------------
                  >>
                  >
                  >
                  >
                  >------------------------------------
                  >
                  >PeopleSoft for the Oracle DBA is published by Apress - see
                  >http://www.psftdba.com.
                  >The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk.
                  >Yahoo! Groups Links
                  >
                  >
                  >
                • Willem.Leenen@ingrealestate.com
                  David, I have heard about RO enqueue to be connected to Oracle bugs. For example, the bug Hemant talks about is about the corruption of the bitmap segment for
                  Message 8 of 15 , Feb 3, 2009
                  • 0 Attachment

                    David,

                     

                    I have heard about RO enqueue to be connected to Oracle bugs. For example, the bug Hemant talks about is about the corruption of the bitmap segment for ASSM segments, and there has been a stack of patches for this ( so a patch for a patch for a patch).

                     

                    Unfortunately I am on oracle 9, where I can’t see the RO part of the enqueue waits, so I can’t estimate the theoretical impact of a change there.  Because of the workload at the moment, I may conduct the test of the larger blocksizes next weekend. I’ll keep you posted.

                     

                    Having said that, I will try to reschedule for less concurrency as the prime solution to this problem.

                     

                    W.L.

                     


                    From: psftdba@yahoogroups.com [mailto: psftdba@yahoogroups.com ] On Behalf Of David Kurtz
                    Sent: dinsdag 3 februari 2009 16:11
                    To: psftdba@yahoogroups.com
                    Subject: RE: PeopleSoft DBA Forum Long truncate times on the TOA tables

                     

                    Serialisation of Truncates is a consequence of using the RO enqueue to
                    protect local write wait.
                    It is the way Oracle works. It has nothing to do with any Oracle bug or
                    patch.

                    If you have a patched system, you can download the test script from my
                    website.

                    regards
                    ____________ _________ ____
                    David Kurtz
                    Go-Faster Consultancy Ltd.
                    tel: +44 (0)7771 760660
                    fax: +44 (0)7092 348865
                    mailto:david.kurtz@ go-faster. co.uk
                    web: www.go-faster. co.uk
                    Book: PeopleSoft for the Oracle DBA: http://www.psftdba. com
                    DBA Blogs: PeopleSoft: http://blog. psftdba.com, Oracle:
                    http://blog. go-faster. co.uk
                    PeopleSoft DBA Forum: http://groups. yahoo.com/ group/psftdba

                    >-----Original Message-----
                    >From: psftdba@yahoogroups .com
                    [mailto:psftdba@yahoogroups .com]
                    >On Behalf Of hemant_chitale
                    >Sent: Tuesday, February 03, 2009 2:32 PM
                    >To: psftdba@yahoogroups .com
                    >Subject: Re: PeopleSoft DBA Forum Long truncate times on the TOA tables
                    >
                    >
                    >No, indexspace leakage is in my other message.
                    >
                    >Here I am talking of TRUNCATE performance bugs. In 9.2, you
                    >certainly should use a seperate block size for your temporary tables.
                    >However, in the 10.2 tests where David also used a seperate
                    >block size, Patch#6844739 should make it unnecessary, in my opinion.
                    >Note#6844739. 8 lists patches that had earlier been released,
                    >each patch either not fixing the issue or introducing another issue.
                    >
                    >
                    >Hemant Chitale
                    >
                    >--- In psftdba@yahoogroups .com,
                    <Willem.Leenen@ ...> wrote:
                    >>
                    >> I understand that you are talking about a bugfix for an indexspace
                    >> leakage, but how does it relate to the concurrency problem David
                    >and I
                    >> described?
                    >>
                    >>
                    >>
                    >> BTW
                    >>
                    >> We're having an oracle 9 here.
                    >>
                    >>
                    >>
                    >> ____________ _________ _________ __
                    >>
                    >> From: psftdba@yahoogroups .com
                    [mailto:psftdba@yahoogroups .com] On
                    >Behalf
                    >> Of hemant_chitale
                    >> Sent: maandag 2 februari 2009 11:00
                    >> To: psftdba@yahoogroups .com
                    >> Subject: Re: PeopleSoft DBA Forum Long truncate times on the TOA
                    >tables
                    >>
                    >>
                    >>
                    >>
                    >> Hmm. My understanding that these bugs were fixed in 10.2 I'd
                    >even gone
                    >> to Patch#6844739 which is a consolidated fix for a number of
                    >Bugs (see
                    >> Note#6844739. 8 for the "list and history of bugs").
                    >>
                    >> Have you tried with this Patch in place ?
                    >>
                    >> --- In psftdba@yahoogroups .com
                    <mailto:psftdba% 40yahoogroups. com> ,
                    >> "David Kurtz" <david.kurtz@ > wrote:
                    >> >
                    >> > Have you tried moving the temp tables to a tablespace with a
                    >larger
                    >> > blocksize?
                    >> > Is so, how much difference did it make?
                    >> >
                    >> >
                    >> > regards
                    >> > ____________ _________ ____
                    >> > 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
                    >> > Book: PeopleSoft for the Oracle DBA:
                    href="http://www.psftdba.com">http://www.psftdba. com
                    >> <http://www.psftdba. com>
                    >> > <http://www.psftdba. com/
                    <http://www.psftdba. com/> > DBA Blogs:
                    >> > PeopleSoft: http://blog. psftdba.com
                    >> <http://blog. psftdba.com>
                    >> <http://blog. psftdba.com/
                    <http://blog. psftdba.com/>
                    > ,
                    >> > Oracle: http://blog. go-faster. co.uk
                    <http://blog. go-faster. co.uk>
                    >> <http://blog. go-faster. co.uk/
                    <http://blog. go-faster. co.uk/>
                    >
                    >> > PeopleSoft DBA Forum:
                    href="http://groups.yahoo.com/group/psftdba">http://groups. yahoo.com/ group/psftdba
                    >> <http://groups. yahoo.com/ group/psftdba>
                    >> >
                    >> >
                    >> >
                    >> >
                    >> >
                    >>
                    >>
                    >>
                    >> ------------ --------- --------- --------- --------- --------- -
                    >> ATTENTION:
                    >> The information in this electronic mail message is private and
                    >> confidential, and only intended for the addressee. Should
                    >you receive
                    >> this message by mistake, you are hereby notified that any
                    >disclosure,
                    >> reproduction, distribution or use of this message is strictly
                    >> prohibited. Please inform the sender by reply transmission
                    >and delete
                    >> the message without copying or opening it.
                    >>
                    >> Messages and attachments are scanned for all viruses known.
                    >> If this message contains password-protected attachments, the files
                    >> have NOT been scanned for viruses by the ING mail domain.
                    >> Always scan attachments before opening them.
                    >> ------------ --------- --------- --------- --------- --------- -
                    >>
                    >
                    >
                    >
                    >----------- --------- --------- -------
                    >
                    >PeopleSoft for the Oracle DBA is published by Apress - see
                    >http://www.psftdba. com.
                    >The PeopleSoft DBA Forum is managed by http://www.go- faster.co. uk.
                    >Yahoo! Groups Links
                    >
                    >
                    >

                    -----------------------------------------------------------------
                    ATTENTION:
                    The information in this electronic mail message is private and
                    confidential, and only intended for the addressee. Should you
                    receive this message by mistake, you are hereby notified that
                    any disclosure, reproduction, distribution or use of this
                    message is strictly prohibited. Please inform the sender by
                    reply transmission and delete the message without copying or
                    opening it.
                    
                    Messages and attachments are scanned for all viruses known.
                    If this message contains password-protected attachments, the
                    files have NOT been scanned for viruses by the ING mail domain.
                    Always scan attachments before opening them.
                    -----------------------------------------------------------------
                    
                    
                  • David Kurtz
                    It doesn t become a separate wait event until 10g, but you can see the RO enqueue as the lock mode in v$lock or dba_locks; If you can set event 10704 to trace
                    Message 9 of 15 , Feb 3, 2009
                    • 0 Attachment
                      It doesn't become a separate wait event until 10g, but you can see the RO enqueue as the lock mode in v$lock or dba_locks;
                      If you can set event 10704 to trace locks (acknowledgements to Jonathan Lewis for that one).

                      regards
                      _________________________
                      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
                      Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
                      DBA Blogs: PeopleSoft: http://blog.psftdba.com, Oracle: http://blog.go-faster.co.uk
                      PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

                       


                      From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of Willem.Leenen@...
                      Sent: Tuesday, February 03, 2009 3:46 PM
                      To: psftdba@yahoogroups.com
                      Subject: RE: PeopleSoft DBA Forum Long truncate times on the TOA tables

                      David,

                       

                      I have heard about RO enqueue to be connected to Oracle bugs. For example, the bug Hemant talks about is about the corruption of the bitmap segment for ASSM segments, and there has been a stack of patches for this ( so a patch for a patch for a patch).

                       

                      Unfortunately I am on oracle 9, where I can’t see the RO part of the enqueue waits, so I can’t estimate the theoretical impact of a change there.  Because of the workload at the moment, I may conduct the test of the larger blocksizes next weekend. I’ll keep you posted.

                       

                      Having said that, I will try to reschedule for less concurrency as the prime solution to this problem.

                       

                      W.L.

                       


                      From: psftdba@yahoogroups.com [mailto: psftdba@yahoogroups.com ] On Behalf Of David Kurtz
                      Sent: dinsdag 3 februari 2009 16:11
                      To: psftdba@yahoogroups.com
                      Subject: RE: PeopleSoft DBA Forum Long truncate times on the TOA tables

                       

                      Serialisation of Truncates is a consequence of using the RO enqueue to
                      protect local write wait.
                      It is the way Oracle works. It has nothing to do with any Oracle bug or
                      patch.

                      If you have a patched system, you can download the test script from my
                      website.

                      regards
                      ____________ _________ ____
                      David Kurtz
                      Go-Faster Consultancy Ltd.
                      tel: +44 (0)7771 760660
                      fax: +44 (0)7092 348865
                      mailto:david.kurtz@ go-faster. co.uk
                      web: www.go-faster. co.uk
                      Book: PeopleSoft for the Oracle DBA: http://www.psftdba. com
                      DBA Blogs: PeopleSoft: http://blog. psftdba.com, Oracle:
                      http://blog. go-faster. co.uk
                      PeopleSoft DBA Forum: http://groups. yahoo.com/ group/psftdba

                      >-----Original Message-----
                      >From: psftdba@yahoogroups .com [mailto:psftdba@yahoogroups .com]
                      >On Behalf Of hemant_chitale
                      >Sent: Tuesday, February 03, 2009 2:32 PM
                      >To: psftdba@yahoogroups .com
                      >Subject: Re: PeopleSoft DBA Forum Long truncate times on the TOA tables
                      >
                      >
                      >No, indexspace leakage is in my other message.
                      >
                      >Here I am talking of TRUNCATE performance bugs. In 9.2, you
                      >certainly should use a seperate block size for your temporary tables.
                      >However, in the 10.2 tests where David also used a seperate
                      >block size, Patch#6844739 should make it unnecessary, in my opinion.
                      >Note#6844739. 8 lists patches that had earlier been released,
                      >each patch either not fixing the issue or introducing another issue.
                      >
                      >
                      >Hemant Chitale
                      >
                      >--- In psftdba@yahoogroups .com, <Willem.Leenen@ ...> wrote:
                      >>
                      >> I understand that you are talking about a bugfix for an indexspace
                      >> leakage, but how does it relate to the concurrency problem David
                      >and I
                      >> described?
                      >>
                      >>
                      >>
                      >> BTW
                      >>
                      >> We're having an oracle 9 here.
                      >>
                      >>
                      >>
                      >> ____________ _________ _________ __
                      >>
                      >> From: psftdba@yahoogroups .com [mailto:psftdba@yahoogroups .com] On
                      >Behalf
                      >> Of hemant_chitale
                      >> Sent: maandag 2 februari 2009 11:00
                      >> To: psftdba@yahoogroups .com
                      >> Subject: Re: PeopleSoft DBA Forum Long truncate times on the TOA
                      >tables
                      >>
                      >>
                      >>
                      >>
                      >> Hmm. My understanding that these bugs were fixed in 10.2 I'd
                      >even gone
                      >> to Patch#6844739 which is a consolidated fix for a number of
                      >Bugs (see
                      >> Note#6844739. 8 for the "list and history of bugs").
                      >>
                      >> Have you tried with this Patch in place ?
                      >>
                      >> --- In psftdba@yahoogroups .com <mailto:psftdba% 40yahoogroups. com> ,
                      >> "David Kurtz" <david.kurtz@ > wrote:
                      >> >
                      >> > Have you tried moving the temp tables to a tablespace with a
                      >larger
                      >> > blocksize?
                      >> > Is so, how much difference did it make?
                      >> >
                      >> >
                      >> > regards
                      >> > ____________ _________ ____
                      >> > 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
                      >> > Book: PeopleSoft for the Oracle DBA: http://www.psftdba. com
                      >> <http://www.psftdba. com>
                      >> > <http://www.psftdba. com/ <http://www.psftdba. com/> > DBA Blogs:
                      >> > PeopleSoft: http://blog. psftdba.com
                      >> <http://blog. psftdba.com>
                      >> <http://blog. psftdba.com/ <http://blog. psftdba.com/> > ,
                      >> > Oracle: http://blog. go-faster. co.uk <http://blog. go-faster. co.uk>
                      >> <http://blog. go-faster. co.uk/ <http://blog. go-faster. co.uk/> >
                      >> > PeopleSoft DBA Forum: http://groups. yahoo.com/ group/psftdba
                      >> <http://groups. yahoo.com/ group/psftdba>
                      >> >
                      >> >
                      >> >
                      >> >
                      >> >
                      >>
                      >>
                      >>
                      >> ------------ --------- --------- --------- --------- --------- -
                      >> ATTENTION:
                      >> The information in this electronic mail message is private and
                      >> confidential, and only intended for the addressee. Should
                      >you receive
                      >> this message by mistake, you are hereby notified that any
                      >disclosure,
                      >> reproduction, distribution or use of this message is strictly
                      >> prohibited. Please inform the sender by reply transmission
                      >and delete
                      >> the message without copying or opening it.
                      >>
                      >> Messages and attachments are scanned for all viruses known.
                      >> If this message contains password-protected attachments, the files
                      >> have NOT been scanned for viruses by the ING mail domain.
                      >> Always scan attachments before opening them.
                      >> ------------ --------- --------- --------- --------- --------- -
                      >>
                      >
                      >
                      >
                      >----------- --------- --------- -------
                      >
                      >PeopleSoft for the Oracle DBA is published by Apress - see
                      >http://www.psftdba. com.
                      >The PeopleSoft DBA Forum is managed by http://www.go- faster.co. uk.
                      >Yahoo! Groups Links
                      >
                      >
                      >

                      -----------------------------------------------------------------
                      ATTENTION:
                      The information in this electronic mail message is private and
                      confidential, and only intended for the addressee. Should you
                      receive this message by mistake, you are hereby notified that
                      any disclosure, reproduction, distribution or use of this
                      message is strictly prohibited. Please inform the sender by
                      reply transmission and delete the message without copying or
                      opening it.
                      
                      Messages and attachments are scanned for all viruses known.
                      If this message contains password-protected attachments, the
                      files have NOT been scanned for viruses by the ING mail domain.
                      Always scan attachments before opening them.
                      -----------------------------------------------------------------
                      
                      
                    • neil tiratto
                      First of all: Thank you David for the excellent paper concerning this matter. Very interesting and helpful. There is little to add to what has already been
                      Message 10 of 15 , Feb 3, 2009
                      • 0 Attachment
                        First of all: Thank you David for the excellent paper concerning this matter.  Very interesting and helpful.   There is little to add to what has already been discussed here.   I will just point out to Willem that it is possible to measure the effects on the types of Enqueue waits these and other changes will produce.  Taking a set of Statspack snapshots during the testing on a quite system should enable you to measure any changes in both the types of Enqueues and their respective timings. 
                        Several months ago I had tested some of the changes myself to try to alleviate these Enqueues.  Below is a sample of the section of the Statspack report that was produced during one of my tests:
                         

                        Enqueue activity for DB: DBTEST  Instance: dbtest  Snaps: 28 -29

                        -> Enqueue stats gathered prior to 9i should not be compared with 9i data

                        -> ordered by Wait Time desc, Waits desc

                         

                                                                                Avg Wt         Wait

                        Eq     Requests    Succ Gets Failed Gets       Waits   Time (ms)     Time (s)

                        -- ------------ ------------ ----------- ----------- ------------- ------------

                        RO          324          324           0         320        993.02          318

                        CF        1,690        1,686           4           2         11.50            0

                        HW       49,447       49,447           0          10           .60            0

                        US        8,011        8,011           0          17           .18            0

                                  -------------------------------------------------------------

                        As you can see, the "RO" enqueues are displayed on the first line of this section of the Statspack report.

                         

                        Just as an aside, I was also testing the effects of limiting the number of extents of the segments being truncated as a way of avoiding the entire buffer cache being scanned for the blocks by the CKPT process.  I began testing this after reading about BUG 3282805 ("Truncate Table Scans For Small Tables Entire Buffercache").  This one was supposed to be fixed in 10.1.0.2 (I plan to check into it soon).  The description of the Diagnostic Analysis states: "For small table we should just scan the DBA range of the table, as drop table does".  According to what was documented with this bug, a segment is considered large if it's size is greater than half the size of the buffer cache; or it is non-RAC and the number of extents is greater than 5; or it is RAC and the number of extents > 1. 

                        I'm hesitant to state what I found without all the supporting test results included (maybe I'll dig them up). When the number of extents in my non-RAC database was increased above 5, the CI enqueues disappeared from the Statspack reports and were replaced by the RO enqueues. 

                         

                        Thanks again for an interesting discussion.

                        - Neil

                         

                         

                         


                        From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of David Kurtz
                        Sent: Tuesday, February 03, 2009 11:19 AM
                        To: psftdba@yahoogroups.com
                        Subject: RE: PeopleSoft DBA Forum Long truncate times on the TOA tables

                        It doesn't become a separate wait event until 10g, but you can see the RO enqueue as the lock mode in v$lock or dba_locks;
                        If you can set event 10704 to trace locks (acknowledgements to Jonathan Lewis for that one).

                        regards
                        ____________ _________ ____
                        David Kurtz
                        Go-Faster Consultancy Ltd.
                        tel: +44 (0)7771 760660
                        fax: +44 (0)7092 348865
                        mailto:david. kurtz@go- faster.co. uk
                        web: www.go-faster. co.uk
                        Book: PeopleSoft for the Oracle DBA: http://www.psftdba. com
                        DBA Blogs: PeopleSoft: http://blog. psftdba.com, Oracle: http://blog. go-faster. co.uk
                        PeopleSoft DBA Forum: http://groups. yahoo.com/ group/psftdba

                         


                        From: psftdba@yahoogroups .com [mailto:psftdba@ yahoogroups. com] On Behalf Of Willem.Leenen@ ingrealestate. com
                        Sent: Tuesday, February 03, 2009 3:46 PM
                        To: psftdba@yahoogroups .com
                        Subject: RE: PeopleSoft DBA Forum Long truncate times on the TOA tables

                        David,

                        I have heard about RO enqueue to be connected to Oracle bugs. For example, the bug Hemant talks about is about the corruption of the bitmap segment for ASSM segments, and there has been a stack of patches for this ( so a patch for a patch for a patch).

                        Unfortunately I am on oracle 9, where I can’t see the RO part of the enqueue waits, so I can’t estimate the theoretical impact of a change there.  Because of the workload at the moment, I may conduct the test of the larger blocksizes next weekend. I’ll keep you posted.

                        Having said that, I will try to reschedule for less concurrency as the prime solution to this problem.

                        W.L.


                        From: psftdba@yahoogroups .com [mailto: psftdba@yahoogroups .com ] On Behalf Of David Kurtz
                        Sent: dinsdag 3 februari 2009 16:11
                        To: psftdba@yahoogroups .com
                        Subject: RE: PeopleSoft DBA Forum Long truncate times on the TOA tables

                        Serialisation of Truncates is a consequence of using the RO enqueue to
                        protect local write wait.
                        It is the way Oracle works. It has nothing to do with any Oracle bug or
                        patch.

                        If you have a patched system, you can download the test script from my
                        website.

                        regards
                        ____________ _________ ____
                        David Kurtz
                        Go-Faster Consultancy Ltd.
                        tel: +44 (0)7771 760660
                        fax: +44 (0)7092 348865
                        mailto:david.kurtz@ go-faster. co.uk
                        web: www.go-faster. co.uk
                        Book: PeopleSoft for the Oracle DBA: http://www.psftdba. com
                        DBA Blogs: PeopleSoft: http://blog. psftdba.com, Oracle:
                        http://blog. go-faster. co.uk
                        PeopleSoft DBA Forum: http://groups. yahoo.com/ group/psftdba

                        >-----Original Message-----
                        >From: psftdba@yahoogroups .com [mailto:psftdba@yahoogroups .com]
                        >On Behalf Of hemant_chitale
                        >Sent: Tuesday, February 03, 2009 2:32 PM
                        >To: psftdba@yahoogroups .com
                        >Subject: Re: PeopleSoft DBA Forum Long truncate times on the TOA tables
                        >
                        >
                        >No, indexspace leakage is in my other message.
                        >
                        >Here I am talking of TRUNCATE performance bugs. In 9.2, you
                        >certainly should use a seperate block size for your temporary tables.
                        >However, in the 10.2 tests where David also used a seperate
                        >block size, Patch#6844739 should make it unnecessary, in my opinion.
                        >Note#6844739. 8 lists patches that had earlier been released,
                        >each patch either not fixing the issue or introducing another issue.
                        >
                        >
                        >Hemant Chitale
                        >
                        >--- In psftdba@yahoogroups .com, <Willem.Leenen@ ...> wrote:
                        >>
                        >> I understand that you are talking about a bugfix for an indexspace
                        >> leakage, but how does it relate to the concurrency problem David
                        >and I
                        >> described?
                        >>
                        >>
                        >>
                        >> BTW
                        >>
                        >> We're having an oracle 9 here.
                        >>
                        >>
                        >>
                        >> ____________ _________ _________ __
                        >>
                        >> From: psftdba@yahoogroups .com [mailto:psftdba@yahoogroups .com] On
                        >Behalf
                        >> Of hemant_chitale
                        >> Sent: maandag 2 februari 2009 11:00
                        >> To: psftdba@yahoogroups .com
                        >> Subject: Re: PeopleSoft DBA Forum Long truncate times on the TOA
                        >tables
                        >>
                        >>
                        >>
                        >>
                        >> Hmm. My understanding that these bugs were fixed in 10.2 I'd
                        >even gone
                        >> to Patch#6844739 which is a consolidated fix for a number of
                        >Bugs (see
                        >> Note#6844739. 8 for the "list and history of bugs").
                        >>
                        >> Have you tried with this Patch in place ?
                        >>
                        >> --- In psftdba@yahoogroups .com <mailto:psftdba% 40yahoogroups. com> ,
                        >> "David Kurtz" <david.kurtz@ > wrote:
                        >> >
                        >> > Have you tried moving the temp tables to a tablespace with a
                        >larger
                        >> > blocksize?
                        >> > Is so, how much difference did it make?
                        >> >
                        >> >
                        >> > regards
                        >> > ____________ _________ ____
                        >> > 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
                        >> > Book: PeopleSoft for the Oracle DBA: http://www.psftdba. com
                        >> <http://www.psftdba. com>
                        >> > <http://www.psftdba. com/ <http://www.psftdba. com/> > DBA Blogs:
                        >> > PeopleSoft: http://blog. psftdba.com
                        >> <http://blog. psftdba.com>
                        >> <http://blog. psftdba.com/ <http://blog. psftdba.com/> > ,
                        >> > Oracle: http://blog. go-faster. co.uk <http://blog. go-faster. co.uk>
                        >> <http://blog. go-faster. co.uk/ <http://blog. go-faster. co.uk/> >
                        >> > PeopleSoft DBA Forum: http://groups. yahoo.com/ group/psftdba
                        >> <http://groups. yahoo.com/ group/psftdba>
                        >> >
                        >> >
                        >> >
                        >> >
                        >> >
                        >>
                        >>
                        >>
                        >> ------------ --------- --------- --------- --------- --------- -
                        >> ATTENTION:
                        >> The information in this electronic mail message is private and
                        >> confidential, and only intended for the addressee. Should
                        >you receive
                        >> this message by mistake, you are hereby notified that any
                        >disclosure,
                        >> reproduction, distribution or use of this message is strictly
                        >> prohibited. Please inform the sender by reply transmission
                        >and delete
                        >> the message without copying or opening it.
                        >>
                        >> Messages and attachments are scanned for all viruses known.
                        >> If this message contains password-protected attachments, the files
                        >> have NOT been scanned for viruses by the ING mail domain.
                        >> Always scan attachments before opening them.
                        >> ------------ --------- --------- --------- --------- --------- -
                        >>
                        >
                        >
                        >
                        >----------- --------- --------- -------
                        >
                        >PeopleSoft for the Oracle DBA is published by Apress - see
                        >http://www.psftdba. com.
                        >The PeopleSoft DBA Forum is managed by http://www.go- faster.co. uk.
                        >Yahoo! Groups Links
                        >
                        >
                        >

                        ------------ --------- --------- --------- --------- --------- --------
                        ATTENTION:
                        The information in this electronic mail message is private and
                        confidential, and only intended for the addressee. Should you
                        receive this message by mistake, you are hereby notified that
                        any disclosure, reproduction, distribution or use of this
                        message is strictly prohibited. Please inform the sender by
                        reply transmission and delete the message without copying or
                        opening it.
                        
                        Messages and attachments are scanned for all viruses known.
                        If this message contains password-protected attachments, the
                        files have NOT been scanned for viruses by the ING mail domain.
                        Always scan attachments before opening them.
                        ------------ --------- --------- --------- --------- --------- --------
                        
                        

                      • Willem.Leenen@ingrealestate.com
                        Hello *, I noticed of a particular SQL that the TAO table in it did not have it s statistics updated, causing severe performance degradation. How can I get a
                        Message 11 of 15 , Feb 5, 2009
                        • 0 Attachment

                           Hello *,

                           

                          I noticed of a particular SQL that the TAO table in it did not have it’s statistics updated, causing severe performance degradation. How can I get a list of processes where the TAOstatistics are not updated?  

                           

                          Regards,

                          W.

                          -----------------------------------------------------------------
                          ATTENTION:
                          The information in this electronic mail message is private and
                          confidential, and only intended for the addressee. Should you
                          receive this message by mistake, you are hereby notified that
                          any disclosure, reproduction, distribution or use of this
                          message is strictly prohibited. Please inform the sender by
                          reply transmission and delete the message without copying or
                          opening it.
                          
                          Messages and attachments are scanned for all viruses known.
                          If this message contains password-protected attachments, the
                          files have NOT been scanned for viruses by the ING mail domain.
                          Always scan attachments before opening them.
                          -----------------------------------------------------------------
                          
                          
                        • David Kurtz
                          Whatever stats you collect on these tables, they will be wrong at run time because the tables will contain different data! Some application engine programs
                          Message 12 of 15 , Feb 5, 2009
                          • 0 Attachment
                            Whatever stats you collect on these tables, they will be wrong at run time because the tables will contain different data!

                            Some application engine programs collect statistics on tables at runtime with the %UpdateStats macro.

                            The other alternative is to delete and lock the stats on these tables, and use Oracle's optimizer dynamic sampling feature.

                            See http://blog.psftdba.com/search/label/Optimizer%20Dynamic%20Sampling



                            2009/2/5 <Willem.Leenen@...>

                             Hello *,

                             

                            I noticed of a particular SQL that the TAO table in it did not have it's statistics updated, causing severe performance degradation. How can I get a list of processes where the TAOstatistics are not updated?  

                             

                            Regards,

                            W.

                            -----------------------------------------------------------------
                            ATTENTION:
                            The information in this electronic mail message is private and
                            confidential, and only intended for the addressee. Should you
                            receive this message by mistake, you are hereby notified that
                            any disclosure, reproduction, distribution or use of this
                            message is strictly prohibited. Please inform the sender by
                            reply transmission and delete the message without copying or
                            opening it.
                            
                            Messages and attachments are scanned for all viruses known.
                            If this message contains password-protected attachments, the
                            files have NOT been scanned for viruses by the ING mail domain.
                            Always scan attachments before opening them.
                            -----------------------------------------------------------------
                            
                            





                            --
                            regards
                            _________________________
                            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
                            Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
                            The PeopleSoft DBA Blog: http://psftdba.blogspot.com
                            PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
                          • Willem.Leenen@ingrealestate.com
                            Yes, I do not want to collect statistics myself on these tables, but I am looking for a way to create a list of AE programs that do not use the %UpdateStats
                            Message 13 of 15 , Feb 5, 2009
                            • 0 Attachment

                              Yes, I do not want to collect statistics myself on these tables, but I am looking for a way to create a list of AE programs that do not use the %UpdateStats macro.

                               

                              Alternatively, I will monitor for tables that get touched, but do not have accurate statistics on them.

                               

                              Interesting blogpost you have on dynamic sampling. Some notes:

                               

                              Optimizer Dynamic Sampling was introduced in Oracle 9.0.2. as a solution to the same problem.”

                               

                              Small note: it was 9.2. Therefore, be sure to have your parameter OPTIMIZER_FEATURES_ENABLE set to 9.2.0 ,  (in stead of 9.0.0 ) otherwise dynamic sampling is not enabled.

                               

                              “Instead, simply delete statistics from the table, and lock them. A subsequent GATHER_SCHEMA_STATS will skip any locked tables”

                               

                              Note: if you gather your statistics with the option STALE, it will also skip tables without statistics, and will continue to gather statistics for tables with a 10% change.

                              This would avoid the problem of peoplesoft’s %UpdateStats-macro raising an error when encountering a table with a statistics-lock.

                               

                              Could you elaborate on the point :

                               

                              1. “The final piece of the puzzle has been to set OPTIMIZER_DYNAMIC_SAMPLING to 4 at instance level. I certainly have had problems with this parameter set to the default of 2.

                               

                              I don’t understand how this can be helpful. The value of 2 seems to take any working table without statistics, and for the other tables, there should be well defined statistics already in place. What’s the win and where?

                               

                              Thanks,

                              W.L.

                               

                               

                               


                              From: psftdba@yahoogroups.com [mailto: psftdba@yahoogroups.com ] On Behalf Of David Kurtz
                              Sent: donderdag 5 februari 2009 12:51
                              To: psftdba@yahoogroups.com
                              Subject: Re: PeopleSoft DBA Forum No statistics on TAO tables

                               

                              Whatever stats you collect on these tables, they will be wrong at run time because the tables will contain different data!

                              Some application engine programs collect statistics on tables at runtime with the %UpdateStats macro.

                              The other alternative is to delete and lock the stats on these tables, and use Oracle's optimizer dynamic sampling feature.

                              See http://blog. psftdba.com/ search/label/ Optimizer% 20Dynamic% 20Sampling


                              2009/2/5 <Willem.Leenen@ ingrealestate. com>

                               Hello *,

                               

                              I noticed of a particular SQL that the TAO table in it did not have it's statistics updated, causing severe performance degradation. How can I get a list of processes where the TAOstatistics are not updated?  

                               

                              Regards,

                              W.

                              ------------ --------- --------- --------- --------- --------- --------
                              ATTENTION:
                              The information in this electronic mail message is private and
                              confidential, and only intended for the addressee. Should you
                              receive this message by mistake, you are hereby notified that
                              any disclosure, reproduction, distribution or use of this
                              message is strictly prohibited. Please inform the sender by
                              reply transmission and delete the message without copying or
                              opening it.
                                
                              Messages and attachments are scanned for all viruses known.
                              If this message contains password-protected attachments, the
                              files have NOT been scanned for viruses by the ING mail domain.
                              Always scan attachments before opening them.
                              ------------ --------- --------- --------- --------- --------- --------
                                

                               




                              --
                              regards
                              ____________ _________ ____
                              David Kurtz
                              Go-Faster Consultancy Ltd.
                              tel: +44 (0)7771 760660
                              fax: +44 (0)7092 348865
                              mailto:david.kurtz@ go-faster. co.uk
                              web: www.go-faster. co.uk
                              Book: PeopleSoft for the Oracle DBA: http://www.psftdba. com
                              The PeopleSoft DBA Blog: http://psftdba. blogspot. com
                              PeopleSoft DBA Forum: http://groups. yahoo.com/ group/psftdba

                              -----------------------------------------------------------------
                              ATTENTION:
                              The information in this electronic mail message is private and
                              confidential, and only intended for the addressee. Should you
                              receive this message by mistake, you are hereby notified that
                              any disclosure, reproduction, distribution or use of this
                              message is strictly prohibited. Please inform the sender by
                              reply transmission and delete the message without copying or
                              opening it.
                              
                              Messages and attachments are scanned for all viruses known.
                              If this message contains password-protected attachments, the
                              files have NOT been scanned for viruses by the ING mail domain.
                              Always scan attachments before opening them.
                              -----------------------------------------------------------------
                              
                              
                            Your message has been successfully submitted and would be delivered to recipients shortly.