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

Re: Perpetually growing indexes

Expand Messages
  • David Kurtz
    Kevin Can you confirm that you have not created this as a BITMAP index? There is a checkbox in Application Designer for this. regards _________________________
    Message 1 of 12 , May 16, 2007
    • 0 Attachment
      Kevin

      Can you confirm that you have not created this as a BITMAP index?
      There is a checkbox in Application Designer for this.

      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



      --- In psftdba@yahoogroups.com, "kevin_slack" <kevin_slack@...> wrote:
      >
      > In PSoft Financials we use Master selector tables to edit journals.
      > This results in the edit process populating some temporary tables,
      > then analyzing them and continuing on. We found through tracing that
      > the analyze (the table had less than 10,000 rows) was taking 10
      > minutes. The process deletes the rows from the table when done.
      >
      > Upon further tracing I determined it was waiting on the index. When I
      > checked the index size, it was over 300MB when the table was only 3MB.
      >
      > I deleted and recreated the indexes but by the end of the day they
      > were again up over 10 MB.
      >
      > Any ideas as to why the indexes keep growing? What can I do to stop
      > this behavior? BTW my index tablespaces are locally managed with
      > uniform extent sizes (128K and 5MB).
      >
      > Any ideas would be helpful.
      >
      > Thanks,
      > Kevin
      >
    • Wang, Philip
      We have been experiencing the similar issue for indexes on PS_COMB_EXP_TAO tables for 3 months and every day more than 4 indexes grows up to more than 4g.
      Message 2 of 12 , Jul 2, 2007
      • 0 Attachment

        We have been experiencing the similar issue for indexes on PS_COMB_EXP_TAO tables for 3 months and every day more than 4 indexes grows up to more than 4g. Those 75 PS_COMB_EXP_TAO temporary tables also start to jump to 4 g every days. System generated more than 300g archive logs per day and enforce us to turn off archive logs today for month end process. The version is Oracle 10.2.0.3 on Redhat as 4.

        Has anyone run into the same situation and I will really appreciate it if you have any recommendation.  Thanks.

        Philip

         


        From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of kevin_slack
        Sent: Tuesday, May 15, 2007 4:51 PM
        To: psftdba@yahoogroups.com
        Subject: PeopleSoft DBA Forum Re: Perpetually growing indexes

         

        For anyone who is interested, due to the way Oracle uses index space
        (it only overwrites when identical rows are inserted otherwise it
        extends), these tables PS_COMB_EXP_ TAO(n) get rows from any of the
        combo rules inserted for comparison in the journal edit process.

        My index rebuild scripts handled growing indexes to move them to a
        larger extent sized tablespace when they grow large enough, but not to
        shrink them. During the busy month end time I had one of the table's
        indexes grow to over a gigabyte which is why the analyze of that table
        was taking so long even when editing a 2 line journal.

        I have made changes to my index rebuild scripts and all is functioning
        well.

        Kevin

        --- In psftdba@yahoogroups .com, "kevin_slack" <kevin_slack@ ...> wrote:

        >
        > In PSoft Financials we use Master selector tables to edit journals.
        > This results in the edit process populating some temporary tables,
        > then analyzing them and continuing on. We found through tracing that
        > the analyze (the table had less than 10,000 rows) was taking 10
        > minutes. The process deletes the rows from the table when done.
        >
        > Upon further tracing I determined it was waiting on the index. When I
        > checked the index size, it was over 300MB when the table was only 3MB.
        >
        > I deleted and recreated the indexes but by the end of the day they
        > were again up over 10 MB.
        >
        > Any ideas as to why the indexes keep growing? What can I do to stop
        > this behavior? BTW my index tablespaces are locally managed with
        > uniform extent sizes (128K and 5MB).
        >
        > Any ideas would be helpful.
        >
        > Thanks,
        > Kevin
        >

        "The information in this electronic mail ("e-mail") message may contain information that is confidential and/or privileged, 
        or may otherwise be protected by work product or other legal rules. It is solely for the use of the individual(s) or the entity(ies)
        originally intended. Access to this electronic mail message by anyone else is unauthorized. If you are not the intended recipient,
        be advised that any unauthorized review, disclosure, copying, distribution or use of this information, or any action taken or
        omitted to be taken in reliance on it, is prohibited and may be unlawful. Please notify the sender immediately if you have received
        this electronic message bymistake, and destroy all copies of the original message."

        "The sender believes that this e-mail and any attachments were free of any virus, worm, Trojan horse, malicious code and/or other
        contaminants when sent. E-mail transmissions cannot be guaranteed to be secure or error-free, so this message and its attachments
        could have been infected, corrupted or made incomplete during transmission. By reading the message and opening any attachments,
        the recipient accepts full responsibility for any viruses or other defects that may arise, and for taking remedial action relating to such
        viruses and other defects. Neither Wyndham Worldwide Corporation nor any of its affiliated entities is liable for any loss or damage
        arising in any way from, orfor errors or omissions in the contents of, this message or its attachments."
      • kevin_slack
        Phillip, depending on how many rows you get in the tables you can just drop the indexes. I only had 5 sets of combo rules so only PS_COMB_EXP_TAO -
        Message 3 of 12 , Jul 3, 2007
        • 0 Attachment
          Phillip, depending on how many rows you get in the tables you can just
          drop the indexes. I only had 5 sets of combo rules so only
          PS_COMB_EXP_TAO - PS_COMB_EXP_TAO4 and related indexes to deal with. I
          never had more than 10,000 rows so I dropped the indexes altogether
          and let the process to full table scans.

          We haven't noticed any significant loss of performance and the edits
          never take any longer due to the analyzing the indexes. Test this
          before doing it, but it works for our situation.

          If that doesn't work you may want to consider a nightly script to
          'Alter Index ... Rebuild' to rebuild those indexes while the tables
          are empty. That would work as well.

          HTH,
          Kevin


          --- In psftdba@yahoogroups.com, "Wang, Philip" <philip.wang@...> wrote:
          >
          > We have been experiencing the similar issue for indexes on
          > PS_COMB_EXP_TAO tables for 3 months and every day more than 4 indexes
          > grows up to more than 4g. Those 75 PS_COMB_EXP_TAO temporary tables also
          > start to jump to 4 g every days. System generated more than 300g archive
          > logs per day and enforce us to turn off archive logs today for month end
          > process. The version is Oracle 10.2.0.3 on Redhat as 4.
          >
          > Has anyone run into the same situation and I will really appreciate it
          > if you have any recommendation. Thanks.
          >
          > Philip
          >
          >
          >
          > ________________________________
          >
          > From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf
          > Of kevin_slack
          > Sent: Tuesday, May 15, 2007 4:51 PM
          > To: psftdba@yahoogroups.com
          > Subject: PeopleSoft DBA Forum Re: Perpetually growing indexes
          >
          >
          >
          > For anyone who is interested, due to the way Oracle uses index space
          > (it only overwrites when identical rows are inserted otherwise it
          > extends), these tables PS_COMB_EXP_TAO(n) get rows from any of the
          > combo rules inserted for comparison in the journal edit process.
          >
          > My index rebuild scripts handled growing indexes to move them to a
          > larger extent sized tablespace when they grow large enough, but not to
          > shrink them. During the busy month end time I had one of the table's
          > indexes grow to over a gigabyte which is why the analyze of that table
          > was taking so long even when editing a 2 line journal.
          >
          > I have made changes to my index rebuild scripts and all is functioning
          > well.
          >
          > Kevin
          >
          > --- In psftdba@yahoogroups.com <mailto:psftdba%40yahoogroups.com> ,
          > "kevin_slack" <kevin_slack@> wrote:
          > >
          > > In PSoft Financials we use Master selector tables to edit journals.
          > > This results in the edit process populating some temporary tables,
          > > then analyzing them and continuing on. We found through tracing that
          > > the analyze (the table had less than 10,000 rows) was taking 10
          > > minutes. The process deletes the rows from the table when done.
          > >
          > > Upon further tracing I determined it was waiting on the index. When I
          > > checked the index size, it was over 300MB when the table was only 3MB.
          > >
          > > I deleted and recreated the indexes but by the end of the day they
          > > were again up over 10 MB.
          > >
          > > Any ideas as to why the indexes keep growing? What can I do to stop
          > > this behavior? BTW my index tablespaces are locally managed with
          > > uniform extent sizes (128K and 5MB).
          > >
          > > Any ideas would be helpful.
          > >
          > > Thanks,
          > > Kevin
          > >
          >
          >
          >
          >
          > "The information in this electronic mail ("e-mail") message may
          contain information that is confidential and/or privileged, <br>or may
          otherwise be protected by work product or other legal rules. It is
          solely for the use of the individual(s) or the entity(ies)
          <br>originally intended. Access to this electronic mail message by
          anyone else is unauthorized. If you are not the intended recipient,
          <br>be advised that any unauthorized review, disclosure, copying,
          distribution or use of this information, or any action taken or
          <br>omitted to be taken in reliance on it, is prohibited and may be
          unlawful. Please notify the sender immediately if you have received
          <br>this electronic message bymistake, and destroy all copies of the
          original message."<br><br>
          >
          > "The sender believes that this e-mail and any attachments were free
          of any virus, worm, Trojan horse, malicious code and/or other
          <br>contaminants when sent. E-mail transmissions cannot be guaranteed
          to be secure or error-free, so this message and its attachments
          <br>could have been infected, corrupted or made incomplete during
          transmission. By reading the message and opening any attachments,
          <br>the recipient accepts full responsibility for any viruses or other
          defects that may arise, and for taking remedial action relating to
          such <br>viruses and other defects. Neither Wyndham Worldwide
          Corporation nor any of its affiliated entities is liable for any loss
          or damage <br>arising in any way from, orfor errors or omissions in
          the contents of, this message or its attachments."
          >
        • Wang, Philip
          Thank you Kevin for your input.We already scheduled to truncate those 75 COMB temp tables every day and rebuild the indexes every 2 hours.This month end
          Message 4 of 12 , Jul 6, 2007
          • 0 Attachment

            Thank you Kevin for your input.

            We already scheduled to truncate those 75 COMB temp tables every day and rebuild the indexes every 2 hours.

            This month end we get the worst, after 6 hours, database dump more than 200g archivelog and filled up disk space.

            The space usage for most those tables also jumped to 4 G at noon. Did anyone run into the similar issue? How to resolve it. Thanks.

            Philip

             


            From: psftdba@yahoogroups.com [mailto: psftdba@yahoogroups.com ] On Behalf Of kevin_slack
            Sent: Tuesday, July 03, 2007 10:09 AM
            To: psftdba@yahoogroups.com
            Subject: PeopleSoft DBA Forum Re: Perpetually growing indexes

             

            Phillip, depending on how many rows you get in the tables you can just
            drop the indexes. I only had 5 sets of combo rules so only
            PS_COMB_EXP_ TAO - PS_COMB_EXP_ TAO4 and related indexes to deal with. I
            never had more than 10,000 rows so I dropped the indexes altogether
            and let the process to full table scans.

            We haven't noticed any significant loss of performance and the edits
            never take any longer due to the analyzing the indexes. Test this
            before doing it, but it works for our situation.

            If that doesn't work you may want to consider a nightly script to
            'Alter Index ... Rebuild' to rebuild those indexes while the tables
            are empty. That would work as well.

            HTH,
            Kevin

            --- In psftdba@yahoogroups .com, "Wang, Philip" <philip.wang@ ...> wrote:

            >
            > We have been experiencing the similar issue for indexes on
            > PS_COMB_EXP_ TAO tables for 3 months and every day more than 4 indexes
            > grows up to more than 4g. Those 75 PS_COMB_EXP_ TAO temporary tables
            also
            > start to jump to 4 g every days. System generated more than 300g archive
            > logs per day and enforce us to turn off archive logs today for month end
            > process. The version is Oracle 10.2.0.3 on Redhat as 4.
            >
            > Has anyone run into the same situation and I will really appreciate it
            > if you have any recommendation. Thanks.
            >
            > Philip
            >
            >
            >
            > ____________ _________ _________ __
            >
            > From: psftdba@yahoogroups .com
            [mailto:psftdba@yahoogroups .com] On Behalf
            > Of kevin_slack
            > Sent: Tuesday, May 15, 2007 4:51 PM
            > To: psftdba@yahoogroups .com
            > Subject: PeopleSoft DBA Forum Re: Perpetually growing indexes
            >
            >
            >
            > For anyone who is interested, due to the way Oracle uses index space
            > (it only overwrites when identical rows are inserted otherwise it
            > extends), these tables PS_COMB_EXP_ TAO(n) get rows from any of the
            > combo rules inserted for comparison in the journal edit process.
            >
            > My index rebuild scripts handled growing indexes to move them to a
            > larger extent sized tablespace when they grow large enough, but not to
            > shrink them. During the busy month end time I had one of the table's
            > indexes grow to over a gigabyte which is why the analyze of that table
            > was taking so long even when editing a 2 line journal.
            >
            > I have made changes to my index rebuild scripts and all is functioning
            > well.
            >
            > Kevin
            >
            > --- In psftdba@yahoogroups .com
            <mailto:psftdba% 40yahoogroups. com> ,
            > "kevin_slack" <kevin_slack@ > wrote:
            > >
            > > In PSoft Financials we use Master selector tables to edit journals.
            > > This results in the edit process populating some temporary tables,
            > > then analyzing them and continuing on. We found through tracing that
            > > the analyze (the table had less than 10,000 rows) was taking 10
            > > minutes. The process deletes the rows from the table when done.
            > >
            > > Upon further tracing I determined it was waiting on the index. When I
            > > checked the index size, it was over 300MB when the table was only
            3MB.
            > >
            > > I deleted and recreated the indexes but by the end of the day they
            > > were again up over 10 MB.
            > >
            > > Any ideas as to why the indexes keep growing? What can I do to stop
            > > this behavior? BTW my index tablespaces are locally managed with
            > > uniform extent sizes (128K and 5MB).
            > >
            > > Any ideas would be helpful.
            > >
            > > Thanks,
            > > Kevin
            > >
            >
            >
            >
            >
            > "The information in this electronic mail ("e-mail") message
            may
            contain information that is confidential and/or privileged, <br>or may
            otherwise be protected by work product or other legal rules. It is
            solely for the use of the individual(s) or the entity(ies)
            <br>originally intended. Access to this electronic mail message by
            anyone else is unauthorized. If you are not the intended recipient,
            <br>be advised that any unauthorized review, disclosure, copying,
            distribution or use of this information, or any action taken or
            <br>omitted to be taken in reliance on it, is prohibited and may be
            unlawful. Please notify the sender immediately if you have received
            <br>this electronic message bymistake, and destroy all copies of the
            original message."<br> <br>
            >
            > "The sender believes that this e-mail and any attachments were free
            of any virus, worm, Trojan horse, malicious code and/or other
            <br>contaminants when sent. E-mail transmissions cannot be guaranteed
            to be secure or error-free, so this message and its attachments
            <br>could have been infected, corrupted or made incomplete during
            transmission. By reading the message and opening any attachments,
            <br>the recipient accepts full responsibility for any viruses or other
            defects that may arise, and for taking remedial action relating to
            such <br>viruses and other defects. Neither Wyndham Worldwide
            Corporation nor any of its affiliated entities is liable for any loss
            or damage <br>arising in any way from, orfor errors or omissions in
            the contents of, this message or its attachments. "
            >

            "The information in this electronic mail ("e-mail") message may contain information that is confidential and/or privileged, 
            or may otherwise be protected by work product or other legal rules. It is solely for the use of the individual(s) or the entity(ies)
            originally intended. Access to this electronic mail message by anyone else is unauthorized. If you are not the intended recipient,
            be advised that any unauthorized review, disclosure, copying, distribution or use of this information, or any action taken or
            omitted to be taken in reliance on it, is prohibited and may be unlawful. Please notify the sender immediately if you have received
            this electronic message bymistake, and destroy all copies of the original message."

            "The sender believes that this e-mail and any attachments were free of any virus, worm, Trojan horse, malicious code and/or other
            contaminants when sent. E-mail transmissions cannot be guaranteed to be secure or error-free, so this message and its attachments
            could have been infected, corrupted or made incomplete during transmission. By reading the message and opening any attachments,
            the recipient accepts full responsibility for any viruses or other defects that may arise, and for taking remedial action relating to such
            viruses and other defects. Neither Wyndham Worldwide Corporation nor any of its affiliated entities is liable for any loss or damage
            arising in any way from, orfor errors or omissions in the contents of, this message or its attachments."
          • Wang, Philip
            Kevin,One senior DBA setup those tablespace and allocation_type is system.Should we use atoallocate or uniform extents. Thanks.Philip
            Message 5 of 12 , Jul 6, 2007
            • 0 Attachment

              Kevin,

              One senior DBA setup those tablespace and allocation_type is system.

              Should we use atoallocate or uniform extents. Thanks.

              Philip

               


              From: psftdba@yahoogroups.com [mailto: psftdba@yahoogroups.com ] On Behalf Of kevin_slack
              Sent: Tuesday, July 03, 2007 10:09 AM
              To: psftdba@yahoogroups.com
              Subject: PeopleSoft DBA Forum Re: Perpetually growing indexes

               

              Phillip, depending on how many rows you get in the tables you can just
              drop the indexes. I only had 5 sets of combo rules so only
              PS_COMB_EXP_ TAO - PS_COMB_EXP_ TAO4 and related indexes to deal with. I
              never had more than 10,000 rows so I dropped the indexes altogether
              and let the process to full table scans.

              We haven't noticed any significant loss of performance and the edits
              never take any longer due to the analyzing the indexes. Test this
              before doing it, but it works for our situation.

              If that doesn't work you may want to consider a nightly script to
              'Alter Index ... Rebuild' to rebuild those indexes while the tables
              are empty. That would work as well.

              HTH,
              Kevin

              --- In psftdba@yahoogroups .com, "Wang, Philip" <philip.wang@ ...> wrote:

              >
              > We have been experiencing the similar issue for indexes on
              > PS_COMB_EXP_ TAO tables for 3 months and every day more than 4 indexes
              > grows up to more than 4g. Those 75 PS_COMB_EXP_ TAO temporary tables
              also
              > start to jump to 4 g every days. System generated more than 300g archive
              > logs per day and enforce us to turn off archive logs today for month end
              > process. The version is Oracle 10.2.0.3 on Redhat as 4.
              >
              > Has anyone run into the same situation and I will really appreciate it
              > if you have any recommendation. Thanks.
              >
              > Philip
              >
              >
              >
              > ____________ _________ _________ __
              >
              > From: psftdba@yahoogroups .com
              [mailto:psftdba@yahoogroups .com] On Behalf
              > Of kevin_slack
              > Sent: Tuesday, May 15, 2007 4:51 PM
              > To: psftdba@yahoogroups .com
              > Subject: PeopleSoft DBA Forum Re: Perpetually growing indexes
              >
              >
              >
              > For anyone who is interested, due to the way Oracle uses index space
              > (it only overwrites when identical rows are inserted otherwise it
              > extends), these tables PS_COMB_EXP_ TAO(n) get rows from any of the
              > combo rules inserted for comparison in the journal edit process.
              >
              > My index rebuild scripts handled growing indexes to move them to a
              > larger extent sized tablespace when they grow large enough, but not to
              > shrink them. During the busy month end time I had one of the table's
              > indexes grow to over a gigabyte which is why the analyze of that table
              > was taking so long even when editing a 2 line journal.
              >
              > I have made changes to my index rebuild scripts and all is functioning
              > well.
              >
              > Kevin
              >
              > --- In psftdba@yahoogroups .com
              <mailto:psftdba% 40yahoogroups. com> ,
              > "kevin_slack" <kevin_slack@ > wrote:
              > >
              > > In PSoft Financials we use Master selector tables to edit journals.
              > > This results in the edit process populating some temporary tables,
              > > then analyzing them and continuing on. We found through tracing that
              > > the analyze (the table had less than 10,000 rows) was taking 10
              > > minutes. The process deletes the rows from the table when done.
              > >
              > > Upon further tracing I determined it was waiting on the index. When I
              > > checked the index size, it was over 300MB when the table was only
              3MB.
              > >
              > > I deleted and recreated the indexes but by the end of the day they
              > > were again up over 10 MB.
              > >
              > > Any ideas as to why the indexes keep growing? What can I do to stop
              > > this behavior? BTW my index tablespaces are locally managed with
              > > uniform extent sizes (128K and 5MB).
              > >
              > > Any ideas would be helpful.
              > >
              > > Thanks,
              > > Kevin
              > >
              >
              >
              >
              >
              > "The information in this electronic mail ("e-mail") message
              may
              contain information that is confidential and/or privileged, <br>or may
              otherwise be protected by work product or other legal rules. It is
              solely for the use of the individual(s) or the entity(ies)
              <br>originally intended. Access to this electronic mail message by
              anyone else is unauthorized. If you are not the intended recipient,
              <br>be advised that any unauthorized review, disclosure, copying,
              distribution or use of this information, or any action taken or
              <br>omitted to be taken in reliance on it, is prohibited and may be
              unlawful. Please notify the sender immediately if you have received
              <br>this electronic message bymistake, and destroy all copies of the
              original message."<br> <br>
              >
              > "The sender believes that this e-mail and any attachments were free
              of any virus, worm, Trojan horse, malicious code and/or other
              <br>contaminants when sent. E-mail transmissions cannot be guaranteed
              to be secure or error-free, so this message and its attachments
              <br>could have been infected, corrupted or made incomplete during
              transmission. By reading the message and opening any attachments,
              <br>the recipient accepts full responsibility for any viruses or other
              defects that may arise, and for taking remedial action relating to
              such <br>viruses and other defects. Neither Wyndham Worldwide
              Corporation nor any of its affiliated entities is liable for any loss
              or damage <br>arising in any way from, orfor errors or omissions in
              the contents of, this message or its attachments. "
              >

              "The information in this electronic mail ("e-mail") message may contain information that is confidential and/or privileged, 
              or may otherwise be protected by work product or other legal rules. It is solely for the use of the individual(s) or the entity(ies)
              originally intended. Access to this electronic mail message by anyone else is unauthorized. If you are not the intended recipient,
              be advised that any unauthorized review, disclosure, copying, distribution or use of this information, or any action taken or
              omitted to be taken in reliance on it, is prohibited and may be unlawful. Please notify the sender immediately if you have received
              this electronic message bymistake, and destroy all copies of the original message."

              "The sender believes that this e-mail and any attachments were free of any virus, worm, Trojan horse, malicious code and/or other
              contaminants when sent. E-mail transmissions cannot be guaranteed to be secure or error-free, so this message and its attachments
              could have been infected, corrupted or made incomplete during transmission. By reading the message and opening any attachments,
              the recipient accepts full responsibility for any viruses or other defects that may arise, and for taking remedial action relating to such
              viruses and other defects. Neither Wyndham Worldwide Corporation nor any of its affiliated entities is liable for any loss or damage
              arising in any way from, orfor errors or omissions in the contents of, this message or its attachments."
            • David Kurtz
              PeopleSoft deliver scripts that create the tablespaces with Uniform extents size set to 1M. There are thousands of tables that contain no rows, that will each
              Message 6 of 12 , Jul 6, 2007
              • 0 Attachment
                PeopleSoft deliver scripts that create the tablespaces with Uniform extents size set to 1M. 
                There are thousands of tables that contain no rows, that will each occupy 1M.
                 
                If you create the tablespace autoallocate they will only use 64Kb in the first extent.
                On my HCM8.9 demo database the difference is 4.7Gb -v- at least 40Gb
                 
                The sheer size to which a database will grow with Uniform extent size and the delivered tablespace model is a sufficient argument for me to use uniform extent size.


                From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of Wang, Philip
                Sent: Friday, July 06, 2007 10:12 PM
                To: psftdba@yahoogroups.com
                Subject: RE: PeopleSoft DBA Forum Re: Perpetually growing indexes

                Kevin,

                One senior DBA setup those tablespace and allocation_type is system.

                Should we use atoallocate or uniform extents. Thanks.

                Philip


                From: psftdba@yahoogroups .com [mailto: psftdba@yahoogroups .com ] On Behalf Of kevin_slack
                Sent: Tuesday, July 03, 2007 10:09 AM
                To: psftdba@yahoogroups .com
                Subject: PeopleSoft DBA Forum Re: Perpetually growing indexes

                Phillip, depending on how many rows you get in the tables you can just
                drop the indexes. I only had 5 sets of combo rules so only
                PS_COMB_EXP_ TAO - PS_COMB_EXP_ TAO4 and related indexes to deal with. I
                never had more than 10,000 rows so I dropped the indexes altogether
                and let the process to full table scans.

                We haven't noticed any significant loss of performance and the edits
                never take any longer due to the analyzing the indexes. Test this
                before doing it, but it works for our situation.

                If that doesn't work you may want to consider a nightly script to
                'Alter Index ... Rebuild' to rebuild those indexes while the tables
                are empty. That would work as well.

                HTH,
                Kevin

                --- In psftdba@yahoogroups .com, "Wang, Philip" <philip.wang@ ...> wrote:
                >
                > We have been experiencing the similar issue for indexes on
                > PS_COMB_EXP_ TAO tables for 3 months and every day more than 4 indexes
                > grows up to more than 4g. Those 75 PS_COMB_EXP_ TAO temporary tables also
                > start to jump to 4 g every days. System generated more than 300g archive
                > logs per day and enforce us to turn off archive logs today for month end
                > process. The version is Oracle 10.2.0.3 on Redhat as 4.
                >
                > Has anyone run into the same situation and I will really appreciate it
                > if you have any recommendation. Thanks.
                >
                > Philip
                >
                >
                >
                > ____________ _________ _________ __
                >
                > From: psftdba@yahoogroups .com [mailto:psftdba@yahoogroups .com] On Behalf
                > Of kevin_slack
                > Sent: Tuesday, May 15, 2007 4:51 PM
                > To: psftdba@yahoogroups .com
                > Subject: PeopleSoft DBA Forum Re: Perpetually growing indexes
                >
                >
                >
                > For anyone who is interested, due to the way Oracle uses index space
                > (it only overwrites when identical rows are inserted otherwise it
                > extends), these tables PS_COMB_EXP_ TAO(n) get rows from any of the
                > combo rules inserted for comparison in the journal edit process.
                >
                > My index rebuild scripts handled growing indexes to move them to a
                > larger extent sized tablespace when they grow large enough, but not to
                > shrink them. During the busy month end time I had one of the table's
                > indexes grow to over a gigabyte which is why the analyze of that table
                > was taking so long even when editing a 2 line journal.
                >
                > I have made changes to my index rebuild scripts and all is functioning
                > well.
                >
                > Kevin
                >
                > --- In psftdba@yahoogroups .com <mailto:psftdba% 40yahoogroups. com> ,
                > "kevin_slack" <kevin_slack@ > wrote:
                > >
                > > In PSoft Financials we use Master selector tables to edit journals.
                > > This results in the edit process populating some temporary tables,
                > > then analyzing them and continuing on. We found through tracing that
                > > the analyze (the table had less than 10,000 rows) was taking 10
                > > minutes. The process deletes the rows from the table when done.
                > >
                > > Upon further tracing I determined it was waiting on the index. When I
                > > checked the index size, it was over 300MB when the table was only 3MB.
                > >
                > > I deleted and recreated the indexes but by the end of the day they
                > > were again up over 10 MB.
                > >
                > > Any ideas as to why the indexes keep growing? What can I do to stop
                > > this behavior? BTW my index tablespaces are locally managed with
                > > uniform extent sizes (128K and 5MB).
                > >
                > > Any ideas would be helpful.
                > >
                > > Thanks,
                > > Kevin
                > >
                >
                >
                >
                >
                > "The information in this electronic mail ("e-mail") message may
                contain information that is confidential and/or privileged, <br>or may
                otherwise be protected by work product or other legal rules. It is
                solely for the use of the individual(s) or the entity(ies)
                <br>originally intended. Access to this electronic mail message by
                anyone else is unauthorized. If you are not the intended recipient,
                <br>be advised that any unauthorized review, disclosure, copying,
                distribution or use of this information, or any action taken or
                <br>omitted to be taken in reliance on it, is prohibited and may be
                unlawful. Please notify the sender immediately if you have received
                <br>this electronic message bymistake, and destroy all copies of the
                original message."<br> <br>
                >
                > "The sender believes that this e-mail and any attachments were free
                of any virus, worm, Trojan horse, malicious code and/or other
                <br>contaminants when sent. E-mail transmissions cannot be guaranteed
                to be secure or error-free, so this message and its attachments
                <br>could have been infected, corrupted or made incomplete during
                transmission. By reading the message and opening any attachments,
                <br>the recipient accepts full responsibility for any viruses or other
                defects that may arise, and for taking remedial action relating to
                such <br>viruses and other defects. Neither Wyndham Worldwide
                Corporation nor any of its affiliated entities is liable for any loss
                or damage <br>arising in any way from, orfor errors or omissions in
                the contents of, this message or its attachments. "
                >

                "The information in this electronic mail ("e-mail") message may contain information that is confidential and/or privileged, 
                or may otherwise be protected by work product or other legal rules. It is solely for the use of the individual(s) or the entity(ies)
                originally intended. Access to this electronic mail message by anyone else is unauthorized. If you are not the intended recipient,
                be advised that any unauthorized review, disclosure, copying, distribution or use of this information, or any action taken or
                omitted to be taken in reliance on it, is prohibited and may be unlawful. Please notify the sender immediately if you have received
                this electronic message bymistake, and destroy all copies of the original message."

                "The sender believes that this e-mail and any attachments were free of any virus, worm, Trojan horse, malicious code and/or other
                contaminants when sent. E-mail transmissions cannot be guaranteed to be secure or error-free, so this message and its attachments
                could have been infected, corrupted or made incomplete during transmission. By reading the message and opening any attachments,
                the recipient accepts full responsibility for any viruses or other defects that may arise, and for taking remedial action relating to such
                viruses and other defects. Neither Wyndham Worldwide Corporation nor any of its affiliated entities is liable for any loss or damage
                arising in any way from, orfor errors or omissions in the contents of, this message or its attachments. "

              • Bhimsingh Wadhwa
                We have to create demo database for financial, EPM and maximo ( I am not sure if I spelled it right). Where can I find disk and memory requirements f, any
                Message 7 of 12 , Jul 17, 2007
                • 0 Attachment
                  We have to create demo database for financial, EPM and
                  maximo ( I am not sure if I spelled it right). Where
                  can I find disk and memory requirements f, any
                  special init parameter setting requirement for these
                  modules.
                  database version si 10.2.0.3 and
                  peoplesoft tools we are targeting is 9.0


                  ____________________________________________________________________________________
                  Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us. http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7
                • Wang, Philip
                  Thank you very much Kevin for your recommendations.I suggested we drop indexes as you recommended but nobody buy it yet. We also have been experiencing
                  Message 8 of 12 , Jul 31, 2007
                  • 0 Attachment

                    Thank you very much Kevin for your recommendations.

                    I suggested we drop indexes as you recommended but nobody buy it yet. We also have been experiencing excessive redo/archived logs issues. The system generated more than 300g archive logs a day during month end process. Thanks.

                    Philip

                     

                     


                    From: psftdba@yahoogroups.com [mailto: psftdba@yahoogroups.com ] On Behalf Of kevin_slack
                    Sent: Tuesday, July 03, 2007 10:09 AM
                    To: psftdba@yahoogroups.com
                    Subject: PeopleSoft DBA Forum Re: Perpetually growing indexes

                     

                    Phillip, depending on how many rows you get in the tables you can just
                    drop the indexes. I only had 5 sets of combo rules so only
                    PS_COMB_EXP_ TAO - PS_COMB_EXP_ TAO4 and related indexes to deal with. I
                    never had more than 10,000 rows so I dropped the indexes altogether
                    and let the process to full table scans.

                    We haven't noticed any significant loss of performance and the edits
                    never take any longer due to the analyzing the indexes. Test this
                    before doing it, but it works for our situation.

                    If that doesn't work you may want to consider a nightly script to
                    'Alter Index ... Rebuild' to rebuild those indexes while the tables
                    are empty. That would work as well.

                    HTH,
                    Kevin

                    --- In psftdba@yahoogroups .com, "Wang, Philip" <philip.wang@ ...> wrote:

                    >
                    > We have been experiencing the similar issue for indexes on
                    > PS_COMB_EXP_ TAO tables for 3 months and every day more than 4 indexes
                    > grows up to more than 4g. Those 75 PS_COMB_EXP_ TAO temporary tables
                    also
                    > start to jump to 4 g every days. System generated more than 300g archive
                    > logs per day and enforce us to turn off archive logs today for month end
                    > process. The version is Oracle 10.2.0.3 on Redhat as 4.
                    >
                    > Has anyone run into the same situation and I will really appreciate it
                    > if you have any recommendation. Thanks.
                    >
                    > Philip
                    >
                    >
                    >
                    > ____________ _________ _________ __
                    >
                    > From: psftdba@yahoogroups .com
                    [mailto:psftdba@yahoogroups .com] On Behalf
                    > Of kevin_slack
                    > Sent: Tuesday, May 15, 2007 4:51 PM
                    > To: psftdba@yahoogroups .com
                    > Subject: PeopleSoft DBA Forum Re: Perpetually growing indexes
                    >
                    >
                    >
                    > For anyone who is interested, due to the way Oracle uses index space
                    > (it only overwrites when identical rows are inserted otherwise it
                    > extends), these tables PS_COMB_EXP_ TAO(n) get rows from any of the
                    > combo rules inserted for comparison in the journal edit process.
                    >
                    > My index rebuild scripts handled growing indexes to move them to a
                    > larger extent sized tablespace when they grow large enough, but not to
                    > shrink them. During the busy month end time I had one of the table's
                    > indexes grow to over a gigabyte which is why the analyze of that table
                    > was taking so long even when editing a 2 line journal.
                    >
                    > I have made changes to my index rebuild scripts and all is functioning
                    > well.
                    >
                    > Kevin
                    >
                    > --- In psftdba@yahoogroups .com
                    <mailto:psftdba% 40yahoogroups. com> ,
                    > "kevin_slack" <kevin_slack@ > wrote:
                    > >
                    > > In PSoft Financials we use Master selector tables to edit journals.
                    > > This results in the edit process populating some temporary tables,
                    > > then analyzing them and continuing on. We found through tracing that
                    > > the analyze (the table had less than 10,000 rows) was taking 10
                    > > minutes. The process deletes the rows from the table when done.
                    > >
                    > > Upon further tracing I determined it was waiting on the index. When I
                    > > checked the index size, it was over 300MB when the table was only
                    3MB.
                    > >
                    > > I deleted and recreated the indexes but by the end of the day they
                    > > were again up over 10 MB.
                    > >
                    > > Any ideas as to why the indexes keep growing? What can I do to stop
                    > > this behavior? BTW my index tablespaces are locally managed with
                    > > uniform extent sizes (128K and 5MB).
                    > >
                    > > Any ideas would be helpful.
                    > >
                    > > Thanks,
                    > > Kevin
                    > >
                    >
                    >
                    >
                    >
                    > "The information in this electronic mail ("e-mail") message
                    may
                    contain information that is confidential and/or privileged, <br>or may
                    otherwise be protected by work product or other legal rules. It is
                    solely for the use of the individual(s) or the entity(ies)
                    <br>originally intended. Access to this electronic mail message by
                    anyone else is unauthorized. If you are not the intended recipient,
                    <br>be advised that any unauthorized review, disclosure, copying,
                    distribution or use of this information, or any action taken or
                    <br>omitted to be taken in reliance on it, is prohibited and may be
                    unlawful. Please notify the sender immediately if you have received
                    <br>this electronic message bymistake, and destroy all copies of the
                    original message."<br> <br>
                    >
                    > "The sender believes that this e-mail and any attachments were free
                    of any virus, worm, Trojan horse, malicious code and/or other
                    <br>contaminants when sent. E-mail transmissions cannot be guaranteed
                    to be secure or error-free, so this message and its attachments
                    <br>could have been infected, corrupted or made incomplete during
                    transmission. By reading the message and opening any attachments,
                    <br>the recipient accepts full responsibility for any viruses or other
                    defects that may arise, and for taking remedial action relating to
                    such <br>viruses and other defects. Neither Wyndham Worldwide
                    Corporation nor any of its affiliated entities is liable for any loss
                    or damage <br>arising in any way from, orfor errors or omissions in
                    the contents of, this message or its attachments. "
                    >

                    "The information in this electronic mail ("e-mail") message may contain information that is confidential and/or privileged, or may otherwise be protected by work product or other legal rules. It is solely for the use of the individual(s) or the entity(ies) originally intended. Access to this electronic mail message by anyone else is unauthorized. If you are not the intended recipient, be advised that any unauthorized review, disclosure, copying, distribution or use of this information, or any action taken or 
                    omitted to be taken in reliance on it, is prohibited and may be unlawful. Please notify the sender immediately if you have received this electronic message by mistake, and destroy all copies of the original message."
                    
                    "The sender believes that this e-mail and any attachments were free of any virus, worm, Trojan horse, malicious code and/or other contaminants when sent. E-mail transmissions cannot be guaranteed to be secure or error-free, so this message and its attachments could have been infected, corrupted or made incomplete during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for any viruses or other defects that may arise, and for taking remedial action relating to such 
                    viruses and other defects. Neither Wyndham Worldwide Corporation nor any of its affiliated entities is liable for any loss or damage arising in any way from, or for errors or omissions in the contents of, this message or its attachments."
                  • hemant_chitale
                    I find that, besides, PS_COMB_EXP_TAO4 the PS_JRNL_IUW2_TAO4 / PS_JRNL_IUW2_TAO5 tables are very busy. Have you been able to implement DROPping the indexes on
                    Message 9 of 12 , Feb 2, 2009
                    • 0 Attachment
                      I find that, besides, PS_COMB_EXP_TAO4 the PS_JRNL_IUW2_TAO4 /
                      PS_JRNL_IUW2_TAO5 tables are very busy.

                      Have you been able to implement DROPping the indexes on
                      PS_COMB_EXP_TAO4 ?
                      Or does Rebuilding daily help ?

                      I think that ASSM bugs may be contributing to the Index space leakage.
                      The DELETE FROM PS_COMB_EXP_TAO4 WHERE PROCESS_INSTANCE >= 0
                      statement seems to be frequently executed and it does a LOT of
                      buffer gets.

                      Hemant

                      --- In psftdba@yahoogroups.com, "Wang, Philip" <philip.wang@...>
                      wrote:
                      >
                      > Thank you very much Kevin for your recommendations.
                      >
                      > I suggested we drop indexes as you recommended but nobody buy it
                      yet. We
                      > also have been experiencing excessive redo/archived logs issues. The
                      > system generated more than 300g archive logs a day during month end
                      > process. Thanks.
                      >
                      > Philip
                      >
                      >
                      >
                      >
                    Your message has been successfully submitted and would be delivered to recipients shortly.