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

Re: PeopleSoft DBA Forum Perpetually growing indexes

Expand Messages
  • John Whitehead
    Kevin, You say the process deletes the rows when done. The typical PeopleSoft approach to temp tables is to truncate the tables at the beginning of the process
    Message 1 of 12 , May 10, 2007
    • 0 Attachment
      Kevin,
      You say the process deletes the rows when done. The typical PeopleSoft approach to temp tables is to truncate the tables at the beginning of the process (for batch, I don't know if you are talking about the batch Journal Edit process or online edit). If your process truly is deleting, I don't believe deletes reclaim the space. As a result, repeatedly deleting and inserting to the table will cause continued space growth. I'm puzzled by your statement: 'BTW my index tablespaces are locally managed with uniform extent sizes (128K and 5MB)'. The uniform extent size can only be one or the other. With a next extent size of 5MB, it only takes 2 allocations to reach the 10MB you reached. The followup question might be, why isn't the table also growing to massive size? My guess is the uniform extent size for tables must be smaller.

      So, the question becomes can you truncate rather than delete. If this is a batch process, you probably can by using the same mechanism that PeopleSoft uses, dedicated temp tables. If it is online, truncate probably won't work. In that case, are you sure you want a uniform extent size of 5MB for indexes? That seems like you would have a lot of wasted space that way.

      Good luck,
      John


      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

    • 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 2 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 3 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 4 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 5 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 6 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 7 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 8 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 9 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 10 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.