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

accurate statistics for temp tables

Expand Messages
  • pjksel
    We are running Oracle 817 with the optimizer mode set to choose. I am trying to get accurate statistics for the temp tables that Peoplesoft uses for various
    Message 1 of 21 , Jun 16 7:24 AM
    • 0 Attachment
      We are running Oracle 817 with the optimizer mode set to choose. I am
      trying to get accurate statistics for the temp tables that Peoplesoft
      uses for various processes. Everytime I try to gather statistics I
      end up with zero rows for the temp tables because peoplesoft deletes
      the data after the process is complete. I thought about not computing
      statistics for the temp tables and forcing rule-based on those
      tables; however peoplesoft joins the temp tables to other non
      temporary tables and thus Oracle will force cost based on all tables.

      What is the best way to get statistics for those temp tables?

      Thanks,
    • Satish KARRY
      Its really tough to get statistics on those tables. As you said they will be wiped out as soon as the process completes. But most of the processes have
      Message 2 of 21 , Jun 16 7:40 AM
      • 0 Attachment
        Its really tough to get statistics on those tables. As you said they will be wiped out as soon as the process completes.
         
        But most of the processes have updatestatistics built into the program. So if you set your optimizer mode to say cost, you are fine.
         
         


        From: pjksel [mailto:pjksel@...]
        Sent: Wednesday, June 16, 2004 7:25 AM
        To: psftdba@yahoogroups.com
        Subject: PeopleSoft DBA Forum accurate statistics for temp tables

        We are running Oracle 817 with the optimizer mode set to choose. I am
        trying to get accurate statistics for the temp tables that Peoplesoft
        uses for various processes.  Everytime I try to gather statistics I
        end up with zero rows for the temp tables because peoplesoft deletes
        the data after the process is complete. I thought about not computing
        statistics for the temp tables and forcing rule-based on those
        tables; however peoplesoft joins the temp tables to other non
        temporary tables and thus Oracle will force cost based on all tables.

        What is the best way to get statistics for those temp tables?

        Thanks,



      • Brian Thompson
        We had exactly the same problem with Oracle817 / PeopleSoft 7.53. We came up with two possible ways of tackling it. 1. Capture the statistics for the temporary
        Message 3 of 21 , Jun 16 8:02 AM
        • 0 Attachment
          We had exactly the same problem with Oracle817 / PeopleSoft 7.53.
          We came up with two possible ways of tackling it.
          1. Capture the statistics for the temporary tables whilst the process is
          actually running.
          This can be quite tricky as you can imagine, but it did work for us.
          2. Use the DBMS_STATS package to create statistics for the temporary tables.
          Of course you need to have a reasonable idea about how many records are in
          the temporary tables,
          but this should work.

          Now I believe that in PeopleSoft V8 the statistics for the temporary tables
          are calculated as part of the process itself
          (as alluded to in Satish's reply), so this issue should be less severe.
          We do not have much experience with V8 yet, so I don't know if
          'updatestatistics' is implemented throughout the product.
          I would be interested to know if this is actually true. If it is it would
          certainly make the PeopleSoft DBA's job easier !
          Best Regards,
          Brian Thompson

          -----Original Message-----
          From: Satish KARRY [mailto:karry@...]
          Sent: 16 June 2004 15:40
          To: psftdba@yahoogroups.com
          Subject: RE: PeopleSoft DBA Forum accurate statistics for temp tables


          Its really tough to get statistics on those tables. As you said they will be
          wiped out as soon as the process completes.

          But most of the processes have updatestatistics built into the program. So
          if you set your optimizer mode to say cost, you are fine.



          _____

          From: pjksel [mailto:pjksel@...]
          Sent: Wednesday, June 16, 2004 7:25 AM
          To: psftdba@yahoogroups.com
          Subject: PeopleSoft DBA Forum accurate statistics for temp tables


          We are running Oracle 817 with the optimizer mode set to choose. I am
          trying to get accurate statistics for the temp tables that Peoplesoft
          uses for various processes. Everytime I try to gather statistics I
          end up with zero rows for the temp tables because peoplesoft deletes
          the data after the process is complete. I thought about not computing
          statistics for the temp tables and forcing rule-based on those
          tables; however peoplesoft joins the temp tables to other non
          temporary tables and thus Oracle will force cost based on all tables.

          What is the best way to get statistics for those temp tables?

          Thanks,





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



          Yahoo! Groups Sponsor

          ADVERTISEMENT

          <http://us.ard.yahoo.com/SIG=129njr57u/M=298184.5022502.6152625.3001176/D=gr
          oups/S=1707698825:HM/EXP=1087483932/A=2164331/R=0/SIG=11eaelai9/*http://www.
          netflix.com/Default?mqso=60183351> click here

          <http://us.adserver.yahoo.com/l?M=298184.5022502.6152625.3001176/D=groups/S=
          :HM/A=2164331/rand=242550640>


          _____

          Yahoo! Groups Links


          * To visit your group on the web, go to:
          http://groups.yahoo.com/group/psftdba/
          <http://groups.yahoo.com/group/psftdba/>


          * To unsubscribe from this group, send an email to:
          psftdba-unsubscribe@yahoogroups.com
          <mailto:psftdba-unsubscribe@yahoogroups.com?subject=Unsubscribe>


          * Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
          <http://docs.yahoo.com/info/terms/> .




          _________________________________________________________

          Zetex Semiconductors - Solutions for an analog world

          EID Award Winners for 'Best Use of Technology' 2003 for the
          AcoustarTM ZXCW8100 End-to-End Digital Audio Amplifier Controller

          http://www.zetex.com
          _________________________________________________________

          ######################################################################
          E-MAILS are susceptible to interference. You should not assume that
          the contents originated from the sender or the Zetex Group or that they
          have been accurately reproduced from their original form.
          Zetex accepts no responsibility for information, errors or omissions in
          this e-mail nor for its use or misuse nor for any act committed or
          omitted in connection with this communication.
          If in doubt, please verify the authenticity with the sender.
          ######################################################################
        • Zeng, Lei
          For 817, I think we need to put analyze command inside program and right before join. If it is in Oracle 9i, it will be easier since we can use dynamic
          Message 4 of 21 , Jun 16 8:06 AM
          • 0 Attachment
            For 817, I think we need to put 'analyze' command inside program and
            right before join.
            If it is in Oracle 9i, it will be easier since we can use dynamic
            sampling feature, (either setup OPTIMIZER_DYNAMIC_SAMPLING in
            init.ora/spfile, or use hint 'dynamic_sampling' in your query).

            Lei

            -----Original Message-----
            From: pjksel [mailto:pjksel@...]
            Sent: Wednesday, June 16, 2004 10:25 AM
            To: psftdba@yahoogroups.com
            Subject: PeopleSoft DBA Forum accurate statistics for temp tables

            We are running Oracle 817 with the optimizer mode set to choose. I am
            trying to get accurate statistics for the temp tables that Peoplesoft
            uses for various processes. Everytime I try to gather statistics I end
            up with zero rows for the temp tables because peoplesoft deletes the
            data after the process is complete. I thought about not computing
            statistics for the temp tables and forcing rule-based on those tables;
            however peoplesoft joins the temp tables to other non temporary tables
            and thus Oracle will force cost based on all tables.

            What is the best way to get statistics for those temp tables?

            Thanks,





            ------------------------ Yahoo! Groups Sponsor --------------------~-->
            Yahoo! Domains - Claim yours for only $14.70
            http://us.click.yahoo.com/Z1wmxD/DREIAA/yQLSAA/JqWylB/TM
            --------------------------------------------------------------------~->

            The PeopleSoft DBA Forum is managed by Go-Faster Consultancy Ltd.
            http://www.go-faster.co.uk Yahoo! Groups Links
          • Gerry Leith
            It is always possible to set the statistics for these temp tables independently. As correctly noted, a compute stats will only record zero rows for temp
            Message 5 of 21 , Jun 16 8:08 AM
            • 0 Attachment
              Message
              It is always possible to set the statistics for these temp tables independently.  As correctly noted, a compute stats will only record zero rows for temp tables.  Accessing them once they have been populated could take the optimiser up the garden path.....
               
              You could consider setting these table statistics using dbms_stats with a value which more accurately reflects the number of rows and blocks to be expected in the table once populated. 
               
              <Minor plug>  We do this with our diagnostic tool, Eagle Eye in its Peoplesoft context </Minor plug>
               
              The challenge is assessing the row count during population to get realistic values. 
               
              Gerry
               
               
              -----Original Message-----
              From: Satish KARRY [mailto:karry@...]
              Sent: Wednesday, June 16, 2004 15:40
              To: psftdba@yahoogroups.com
              Subject: RE: PeopleSoft DBA Forum accurate statistics for temp tables

              Its really tough to get statistics on those tables. As you said they will be wiped out as soon as the process completes.
               
              But most of the processes have updatestatistics built into the program. So if you set your optimizer mode to say cost, you are fine.
               
               


              From: pjksel [mailto:pjksel@...]
              Sent: Wednesday, June 16, 2004 7:25 AM
              To: psftdba@yahoogroups.com
              Subject: PeopleSoft DBA Forum accurate statistics for temp tables

              We are running Oracle 817 with the optimizer mode set to choose. I am
              trying to get accurate statistics for the temp tables that Peoplesoft
              uses for various processes.  Everytime I try to gather statistics I
              end up with zero rows for the temp tables because peoplesoft deletes
              the data after the process is complete. I thought about not computing
              statistics for the temp tables and forcing rule-based on those
              tables; however peoplesoft joins the temp tables to other non
              temporary tables and thus Oracle will force cost based on all tables.

              What is the best way to get statistics for those temp tables?

              Thanks,





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



              ---
              Incoming mail is certified Virus Free.
              Checked by AVG anti-virus system (http://www.grisoft.com).
              Version: 6.0.701 / Virus Database: 458 - Release Date: 6/7/04


              ---
              Outgoing mail is certified Virus Free.
              Checked by AVG anti-virus system (http://www.grisoft.com).
              Version: 6.0.701 / Virus Database: 458 - Release Date: 6/7/04

            • Henry Poras
              If you know the size of the table you can always tell Oracle the statistics in advance using dbms_stats.set_table_stats Henry ... am ... Peoplesoft ... deletes
              Message 6 of 21 , Jun 16 8:09 AM
              • 0 Attachment
                If you know the size of the table you can always tell Oracle the
                statistics in advance using dbms_stats.set_table_stats

                Henry

                --- In psftdba@yahoogroups.com, "pjksel" <pjksel@y...> wrote:
                > We are running Oracle 817 with the optimizer mode set to choose. I
                am
                > trying to get accurate statistics for the temp tables that
                Peoplesoft
                > uses for various processes. Everytime I try to gather statistics I
                > end up with zero rows for the temp tables because peoplesoft
                deletes
                > the data after the process is complete. I thought about not
                computing
                > statistics for the temp tables and forcing rule-based on those
                > tables; however peoplesoft joins the temp tables to other non
                > temporary tables and thus Oracle will force cost based on all
                tables.
                >
                > What is the best way to get statistics for those temp tables?
                >
                > Thanks,
              • David Kurtz
                I see that this topic has touched a nerve - there were 4 new messages just while I was writting this. For clarity, we are talking about permanent database
                Message 7 of 21 , Jun 16 8:15 AM
                • 0 Attachment
                  I see that this topic has touched a nerve - there were 4 new messages just
                  while I was writting this.

                  For clarity, we are talking about permanent database tables used by mainly
                  Application Engine (AE) processes to temporarily store data for working
                  storage. These are not Oracle Global Temporary tables (unless you make them
                  so which raises lots of other points).

                  In PeopleTools 8, PeopleSoft introduced a new record type, Temporary Tables.
                  You can have multiple Oracle tables that correspond to the same PeopleSoft
                  Temporary Table record. There is the concept of a set of temporary tables
                  that are allocated to an AE instance. So each temporary table is only
                  referenced by a single AE process (unless there are no free temp table
                  instances).

                  What PeopleSoft are starting to do is that when an AE truncates and
                  repopulates an temporary table it regenerated the CBO statistics. In general
                  this works quite well. You have valid statistics when you need them,
                  although not if you refresh the statistics at any other time. Although I do
                  have a few criticisms.

                  PeopleSoft always collects the statistics with the RECSTATS metacode, which
                  does an ANALYZE TABLE ... ESTIMATE STATISTICS on a default sample size.
                  They don't use DBMS_STATISTICS, and they never use a larger sample size nor
                  do they generate histograms. If you want to change this behaviour you have
                  to customise the AE.
                  They don't always generate the statistics on all tables. So you may have to
                  add this yourself.

                  In PT7, multiple instances of the same AE process could reference the same
                  working storage table. The tables were usually keyed on process instance.
                  They couldn't truncate the table because it would affect all the processes
                  that used the same table. If a process crashed it could leave debris in the
                  table. So you could get working storage tables with very high high water
                  marks.

                  The statistics don't get wiped out when the process finishes. The
                  statistics will be removed by the truncate command (mostly the processes
                  only do this at the beginning not the end - the data is kept for potential
                  debugging). The real problem is that the statistics don't match the data.
                  The truncate-populate-analyze processing in PT8 resolves this.

                  Note: If you optimiser mode is choose, and none of the tables reference in a
                  query have statistics (and none of them are partitioned or have a degree of
                  parallelism, and there are no hints) then you get rule based optimisation.
                  If some of the tables have stats then you get cost based optimisation and
                  the optimiser makes some guesses about the tables without statistics.

                  Rule based optimisation is now a deprecated feature in Oracle. You really
                  should be aiming to use Cost-based optimisation.

                  _________________________
                  David Kurtz
                  Go-Faster Consultancy Ltd.
                  tel: +44 (0)7771 760660
                  fax: +44 (0)7092 348865
                  mailto:david.kurtz@...
                  web: www.go-faster.co.uk
                  PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
                • R P
                  I appreciate all the quick responses. This is a great group. I had a few more questions regarding this topic. If peoplesoft is generating the statistics
                  Message 8 of 21 , Jun 16 9:15 AM
                  • 0 Attachment
                    I appreciate all the quick responses. This is a great group. I had a few more questions regarding this topic.
                    If peoplesoft is generating the statistics through the app engine program I would have thought that I would have seen the information about the statistics when I query the data dictionary but I don't see any. Does peoplesoft generate the statistics by default on all processes that use temp tables or do the app engine programs have to be modified
                     
                    Are app engines the only programs that use temp tables? Does any online process, cobol, sqr or anything else use temp tables?
                     
                    Statspack is indicating that there are temp tables with extremely high i/o rates.
                     
                    Thanks,

                    David Kurtz <info@...> wrote:
                    I see that this topic has touched a nerve - there were 4 new messages just
                    while I was writting this.

                    For clarity, we are talking about permanent database tables used by mainly
                    Application Engine (AE) processes to temporarily store data for working
                    storage.  These are not Oracle Global Temporary tables (unless you make them
                    so which raises lots of other points).

                    In PeopleTools 8, PeopleSoft introduced a new record type, Temporary Tables.
                    You can have multiple Oracle tables that correspond to the same PeopleSoft
                    Temporary Table record.  There is the concept of a set of temporary tables
                    that are allocated to an AE instance.  So each temporary table is only
                    referenced by a single AE process (unless there are no free temp table
                    instances).

                    What PeopleSoft are starting to do is that when an AE truncates and
                    repopulates an temporary table it regenerated the CBO statistics. In general
                    this works quite well.  You have valid statistics when you need them,
                    although not if you refresh the statistics at any other time.  Although I do
                    have a few criticisms.

                    PeopleSoft always collects the statistics with the RECSTATS metacode, which
                    does an ANALYZE TABLE ... ESTIMATE STATISTICS on a default sample size.
                    They don't use DBMS_STATISTICS, and they never use a larger sample size nor
                    do they generate histograms.  If you want to change this behaviour you have
                    to customise the AE.
                    They don't always generate the statistics on all tables.  So you may have to
                    add this yourself.

                    In PT7, multiple instances of the same AE process could reference the same
                    working storage table.  The tables were usually keyed on process instance.
                    They couldn't truncate the table because it would affect all the processes
                    that used the same table. If a process crashed it could leave debris in the
                    table.  So you could get working storage tables with very high high water
                    marks.

                    The statistics don't get wiped out when the process finishes.  The
                    statistics will be removed by the truncate command (mostly the processes
                    only do this at the beginning not the end - the data is kept for potential
                    debugging).  The real problem is that the statistics don't match the data.
                    The truncate-populate-analyze processing in PT8 resolves this.

                    Note: If you optimiser mode is choose, and none of the tables reference in a
                    query have statistics (and none of them are partitioned or have a degree of
                    parallelism, and there are no hints) then you get rule based optimisation.
                    If some of the tables have stats then you get cost based optimisation and
                    the optimiser makes some guesses about the tables without statistics.

                    Rule based optimisation is now a deprecated feature in Oracle.  You really
                    should be aiming to use Cost-based optimisation.

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







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



                    Do you Yahoo!?
                    Read only the mail you want - Yahoo! Mail SpamGuard.

                  • R P
                    I appreciate all the quick responses. This is a great group. I had a few more questions regarding this topic. If peoplesoft is generating the statistics
                    Message 9 of 21 , Jun 16 9:16 AM
                    • 0 Attachment
                      I appreciate all the quick responses. This is a great group. I had a few more questions regarding this topic.
                      If peoplesoft is generating the statistics through the app engine program I would have thought that I would have seen the information about the statistics when I query the data dictionary but I don't see any. Does peoplesoft generate the statistics by default on all processes that use temp tables or do the app engine programs have to be modified
                       
                      Are app engines the only programs that use temp tables? Does any online process, cobol, sqr or anything else use temp tables?
                       
                      Statspack is indicating that there are temp tables with extremely high i/o rates.
                       
                      Thanks,

                      David Kurtz <info@...> wrote:
                      I see that this topic has touched a nerve - there were 4 new messages just
                      while I was writting this.

                      For clarity, we are talking about permanent database tables used by mainly
                      Application Engine (AE) processes to temporarily store data for working
                      storage.  These are not Oracle Global Temporary tables (unless you make them
                      so which raises lots of other points).

                      In PeopleTools 8, PeopleSoft introduced a new record type, Temporary Tables.
                      You can have multiple Oracle tables that correspond to the same PeopleSoft
                      Temporary Table record.  There is the concept of a set of temporary tables
                      that are allocated to an AE instance.  So each temporary table is only
                      referenced by a single AE process (unless there are no free temp table
                      instances).

                      What PeopleSoft are starting to do is that when an AE truncates and
                      repopulates an temporary table it regenerated the CBO statistics. In general
                      this works quite well.  You have valid statistics when you need them,
                      although not if you refresh the statistics at any other time.  Although I do
                      have a few criticisms.

                      PeopleSoft always collects the statistics with the RECSTATS metacode, which
                      does an ANALYZE TABLE ... ESTIMATE STATISTICS on a default sample size.
                      They don't use DBMS_STATISTICS, and they never use a larger sample size nor
                      do they generate histograms.  If you want to change this behaviour you have
                      to customise the AE.
                      They don't always generate the statistics on all tables.  So you may have to
                      add this yourself.

                      In PT7, multiple instances of the same AE process could reference the same
                      working storage table.  The tables were usually keyed on process instance.
                      They couldn't truncate the table because it would affect all the processes
                      that used the same table. If a process crashed it could leave debris in the
                      table.  So you could get working storage tables with very high high water
                      marks.

                      The statistics don't get wiped out when the process finishes.  The
                      statistics will be removed by the truncate command (mostly the processes
                      only do this at the beginning not the end - the data is kept for potential
                      debugging).  The real problem is that the statistics don't match the data.
                      The truncate-populate-analyze processing in PT8 resolves this.

                      Note: If you optimiser mode is choose, and none of the tables reference in a
                      query have statistics (and none of them are partitioned or have a degree of
                      parallelism, and there are no hints) then you get rule based optimisation.
                      If some of the tables have stats then you get cost based optimisation and
                      the optimiser makes some guesses about the tables without statistics.

                      Rule based optimisation is now a deprecated feature in Oracle.  You really
                      should be aiming to use Cost-based optimisation.

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







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



                      Do you Yahoo!?
                      Take Yahoo! Mail with you! Get it on your mobile phone.

                    • David Kurtz
                      Only AE processes use the PeopleSoft temporary tables. Cobol and SQR do not. PeopleSoft are continuing to move batch processing away from Cobol and SQR and
                      Message 10 of 21 , Jun 16 9:45 AM
                      • 0 Attachment
                        Only AE processes use the PeopleSoft temporary tables.  Cobol and SQR do not.  PeopleSoft are continuing to move batch processing away from Cobol and SQR and towards AE (although the Global payroll engine is written in Cobol !!!). 
                         
                        I'm not surprised that the temp tables have high I/O rates.  Is it the tables or their indexes?  First you populate them, with the indexes in place, and then they tend to be used to drive processing (often involving full scans).  But is this actually a problem?
                         
                        If accessing the temp tables does is not costing a lot of time, does the I/O matter?
                         
                         
                         
                        If you are not seeing statistics on the table then either the statistics are not being built by the process, or the table is being truncated at the end of the process.  What product and PeopleTools version are you using?  and which table and process?
                         
                         
                        _________________________
                        David Kurtz
                        Go-Faster Consultancy Ltd.
                        tel: +44 (0)7771 760660
                        fax: +44 (0)7092 348865
                        mailto:david.kurtz@...
                        web: www.go-faster.co.uk
                        PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
                        -----Original Message-----
                        From: R P [mailto:pjksel@...]
                        Sent: 16 June 2004 17:17
                        To: psftdba@yahoogroups.com
                        Subject: RE: PeopleSoft DBA Forum accurate statistics for temp tables

                        I appreciate all the quick responses. This is a great group. I had a few more questions regarding this topic.
                        If peoplesoft is generating the statistics through the app engine program I would have thought that I would have seen the information about the statistics when I query the data dictionary but I don't see any. Does peoplesoft generate the statistics by default on all processes that use temp tables or do the app engine programs have to be modified
                         
                        Are app engines the only programs that use temp tables? Does any online process, cobol, sqr or anything else use temp tables?
                         
                        Statspack is indicating that there are temp tables with extremely high i/o rates.
                         
                        Thanks,

                        David Kurtz <info@...> wrote:
                        I see that this topic has touched a nerve - there were 4 new messages just
                        while I was writting this.

                        For clarity, we are talking about permanent database tables used by mainly
                        Application Engine (AE) processes to temporarily store data for working
                        storage.  These are not Oracle Global Temporary tables (unless you make them
                        so which raises lots of other points).

                        In PeopleTools 8, PeopleSoft introduced a new record type, Temporary Tables.
                        You can have multiple Oracle tables that correspond to the same PeopleSoft
                        Temporary Table record.  There is the concept of a set of temporary tables
                        that are allocated to an AE instance.  So each temporary table is only
                        referenced by a single AE process (unless there are no free temp table
                        instances).

                        What PeopleSoft are starting to do is that when an AE truncates and
                        repopulates an temporary table it regenerated the CBO statistics. In general
                        this works quite well.  You have valid statistics when you need them,
                        although not if you refresh the statistics at any other time.  Although I do
                        have a few criticisms.

                        PeopleSoft always collects the statistics with the RECSTATS metacode, which
                        does an ANALYZE TABLE ... ESTIMATE STATISTICS on a default sample size.
                        They don't use DBMS_STATISTICS, and they never use a larger sample size nor
                        do they generate histograms.  If you want to change this behaviour you have
                        to customise the AE.
                        They don't always generate the statistics on all tables.  So you may have to
                        add this yourself.

                        In PT7, multiple instances of the same AE process could reference the same
                        working storage table.  The tables were usually keyed on process instance.
                        They couldn't truncate the table because it would affect all the processes
                        that used the same table. If a process crashed it could leave debris in the
                        table.  So you could get working storage tables with very high high water
                        marks.

                        The statistics don't get wiped out when the process finishes.  The
                        statistics will be removed by the truncate command (mostly the processes
                        only do this at the beginning not the end - the data is kept for potential
                        debugging).  The real problem is that the statistics don't match the data.
                        The truncate-populate-analyze processing in PT8 resolves this.

                        Note: If you optimiser mode is choose, and none of the tables reference in a
                        query have statistics (and none of them are partitioned or have a degree of
                        parallelism, and there are no hints) then you get rule based optimisation.
                        If some of the tables have stats then you get cost based optimisation and
                        the optimiser makes some guesses about the tables without statistics.

                        Rule based optimisation is now a deprecated feature in Oracle.  You really
                        should be aiming to use Cost-based optimisation.

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







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



                        Do you Yahoo!?
                        Take Yahoo! Mail with you! Get it on your mobile phone.

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


                      • Henry Poras
                        David, Thanks. I didn t know PeopleSoft was running an ANALYZE on the temp tables within the AE code. Henry ... messages just ... mainly ... working ... make
                        Message 11 of 21 , Jun 16 10:34 AM
                        • 0 Attachment
                          David,

                          Thanks. I didn't know PeopleSoft was running an ANALYZE on the temp
                          tables within the AE code.

                          Henry

                          --- In psftdba@yahoogroups.com, "David Kurtz" <info@g...> wrote:
                          > I see that this topic has touched a nerve - there were 4 new
                          messages just
                          > while I was writting this.
                          >
                          > For clarity, we are talking about permanent database tables used by
                          mainly
                          > Application Engine (AE) processes to temporarily store data for
                          working
                          > storage. These are not Oracle Global Temporary tables (unless you
                          make them
                          > so which raises lots of other points).
                          >
                          > In PeopleTools 8, PeopleSoft introduced a new record type,
                          Temporary Tables.
                          > You can have multiple Oracle tables that correspond to the same
                          PeopleSoft
                          > Temporary Table record. There is the concept of a set of temporary
                          tables
                          > that are allocated to an AE instance. So each temporary table is
                          only
                          > referenced by a single AE process (unless there are no free temp
                          table
                          > instances).
                          >
                          > What PeopleSoft are starting to do is that when an AE truncates and
                          > repopulates an temporary table it regenerated the CBO statistics.
                          In general
                          > this works quite well. You have valid statistics when you need
                          them,
                          > although not if you refresh the statistics at any other time.
                          Although I do
                          > have a few criticisms.
                          >
                          > PeopleSoft always collects the statistics with the RECSTATS
                          metacode, which
                          > does an ANALYZE TABLE ... ESTIMATE STATISTICS on a default sample
                          size.
                          > They don't use DBMS_STATISTICS, and they never use a larger sample
                          size nor
                          > do they generate histograms. If you want to change this behaviour
                          you have
                          > to customise the AE.
                          > They don't always generate the statistics on all tables. So you
                          may have to
                          > add this yourself.
                          >
                          > In PT7, multiple instances of the same AE process could reference
                          the same
                          > working storage table. The tables were usually keyed on process
                          instance.
                          > They couldn't truncate the table because it would affect all the
                          processes
                          > that used the same table. If a process crashed it could leave
                          debris in the
                          > table. So you could get working storage tables with very high high
                          water
                          > marks.
                          >
                          > The statistics don't get wiped out when the process finishes. The
                          > statistics will be removed by the truncate command (mostly the
                          processes
                          > only do this at the beginning not the end - the data is kept for
                          potential
                          > debugging). The real problem is that the statistics don't match
                          the data.
                          > The truncate-populate-analyze processing in PT8 resolves this.
                          >
                          > Note: If you optimiser mode is choose, and none of the tables
                          reference in a
                          > query have statistics (and none of them are partitioned or have a
                          degree of
                          > parallelism, and there are no hints) then you get rule based
                          optimisation.
                          > If some of the tables have stats then you get cost based
                          optimisation and
                          > the optimiser makes some guesses about the tables without
                          statistics.
                          >
                          > Rule based optimisation is now a deprecated feature in Oracle. You
                          really
                          > should be aiming to use Cost-based optimisation.
                          >
                          > _________________________
                          > David Kurtz
                          > Go-Faster Consultancy Ltd.
                          > tel: +44 (0)7771 760660
                          > fax: +44 (0)7092 348865
                          > mailto:david.kurtz@g...
                          > web: www.go-faster.co.uk
                          > PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
                        • Joan Hsieh
                          Hi David, We are running pt 8.4 for FM, I didn t see any tables is temporary table as I query the dba_tables.temporary. Did I use the wrong dictionary table?
                          Message 12 of 21 , Jun 21 8:12 AM
                          • 0 Attachment
                            Hi David,

                            We are running pt 8.4 for FM, I didn't see any tables is temporary table
                            as I query the dba_tables.temporary. Did I use the wrong dictionary
                            table? Currectly we analyzed all the tables and deleted all of joined
                            statistics tables for some of bad performance sql to force it run rule
                            based optim.

                            Thanks,

                            Joan

                            David Kurtz wrote:

                            > I see that this topic has touched a nerve - there were 4 new messages just
                            > while I was writting this.
                            >
                            > For clarity, we are talking about permanent database tables used by mainly
                            > Application Engine (AE) processes to temporarily store data for working
                            > storage. These are not Oracle Global Temporary tables (unless you make them
                            > so which raises lots of other points).
                            >
                            > In PeopleTools 8, PeopleSoft introduced a new record type, Temporary Tables.
                            > You can have multiple Oracle tables that correspond to the same PeopleSoft
                            > Temporary Table record. There is the concept of a set of temporary tables
                            > that are allocated to an AE instance. So each temporary table is only
                            > referenced by a single AE process (unless there are no free temp table
                            > instances).
                            >
                            > What PeopleSoft are starting to do is that when an AE truncates and
                            > repopulates an temporary table it regenerated the CBO statistics. In general
                            > this works quite well. You have valid statistics when you need them,
                            > although not if you refresh the statistics at any other time. Although I do
                            > have a few criticisms.
                            >
                            > PeopleSoft always collects the statistics with the RECSTATS metacode, which
                            > does an ANALYZE TABLE ... ESTIMATE STATISTICS on a default sample size.
                            > They don't use DBMS_STATISTICS, and they never use a larger sample size nor
                            > do they generate histograms. If you want to change this behaviour you have
                            > to customise the AE.
                            > They don't always generate the statistics on all tables. So you may have to
                            > add this yourself.
                            >
                            > In PT7, multiple instances of the same AE process could reference the same
                            > working storage table. The tables were usually keyed on process instance.
                            > They couldn't truncate the table because it would affect all the processes
                            > that used the same table. If a process crashed it could leave debris in the
                            > table. So you could get working storage tables with very high high water
                            > marks.
                            >
                            > The statistics don't get wiped out when the process finishes. The
                            > statistics will be removed by the truncate command (mostly the processes
                            > only do this at the beginning not the end - the data is kept for potential
                            > debugging). The real problem is that the statistics don't match the data.
                            > The truncate-populate-analyze processing in PT8 resolves this.
                            >
                            > Note: If you optimiser mode is choose, and none of the tables reference in a
                            > query have statistics (and none of them are partitioned or have a degree of
                            > parallelism, and there are no hints) then you get rule based optimisation.
                            > If some of the tables have stats then you get cost based optimisation and
                            > the optimiser makes some guesses about the tables without statistics.
                            >
                            > Rule based optimisation is now a deprecated feature in Oracle. You really
                            > should be aiming to use Cost-based optimisation.
                            >
                            > _________________________
                            > David Kurtz
                            > Go-Faster Consultancy Ltd.
                            > tel: +44 (0)7771 760660
                            > fax: +44 (0)7092 348865
                            > mailto:david.kurtz@...
                            > web: www.go-faster.co.uk
                            > PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
                            >
                            >
                            >
                            >
                            >
                            >
                            >
                            >
                            > The PeopleSoft DBA Forum is managed by Go-Faster Consultancy Ltd. http://www.go-faster.co.uk
                            > Yahoo! Groups Links
                            >
                            >
                            >
                            >
                            >
                            >
                          • ZOUAGHI Jaouad
                            hi Joan select table_name from dba_tables where temporary= Y ... De : sentto-8490032-601-1087833766-jzouaghi=sin-et-stes.fr@returns.groups.yahoo.c om
                            Message 13 of 21 , Jun 21 9:09 AM
                            • 0 Attachment

                              hi Joan

                              select

                              table_name from dba_tables where temporary='Y'
                              -----Message d'origine-----
                              De : sentto-8490032-601-1087833766-jzouaghi=sin-et-stes.fr@... [mailto:sentto-8490032-601-1087833766-jzouaghi=sin-et-stes.fr@...]De la part de Joan Hsieh
                              Envoyé : lundi 21 juin 2004 17:13
                              À : psftdba@yahoogroups.com
                              Objet : Re: PeopleSoft DBA Forum accurate statistics for temp tables

                              Hi David,

                              We are running pt 8.4 for FM, I didn't see any tables is temporary table
                              as I query the dba_tables.temporary. Did I use the wrong dictionary
                              table? Currectly we analyzed all the tables and deleted all of joined
                              statistics tables for some of bad performance sql to force it run rule
                              based optim.

                              Thanks,

                              Joan

                              David Kurtz wrote:

                              > I see that this topic has touched a nerve - there were 4 new messages just
                              > while I was writting this.
                              >
                              > For clarity, we are talking about permanent database tables used by mainly
                              > Application Engine (AE) processes to temporarily store data for working
                              > storage.  These are not Oracle Global Temporary tables (unless you make them
                              > so which raises lots of other points).
                              >
                              > In PeopleTools 8, PeopleSoft introduced a new record type, Temporary Tables.
                              > You can have multiple Oracle tables that correspond to the same PeopleSoft
                              > Temporary Table record.  There is the concept of a set of temporary tables
                              > that are allocated to an AE instance.  So each temporary table is only
                              > referenced by a single AE process (unless there are no free temp table
                              > instances).
                              >
                              > What PeopleSoft are starting to do is that when an AE truncates and
                              > repopulates an temporary table it regenerated the CBO statistics. In general
                              > this works quite well.  You have valid statistics when you need them,
                              > although not if you refresh the statistics at any other time.  Although I do
                              > have a few criticisms.
                              >
                              > PeopleSoft always collects the statistics with the RECSTATS metacode, which
                              > does an ANALYZE TABLE ... ESTIMATE STATISTICS on a default sample size.
                              > They don't use DBMS_STATISTICS, and they never use a larger sample size nor
                              > do they generate histograms.  If you want to change this behaviour you have
                              > to customise the AE.
                              > They don't always generate the statistics on all tables.  So you may have to
                              > add this yourself.
                              >
                              > In PT7, multiple instances of the same AE process could reference the same
                              > working storage table.  The tables were usually keyed on process instance.
                              > They couldn't truncate the table because it would affect all the processes
                              > that used the same table. If a process crashed it could leave debris in the
                              > table.  So you could get working storage tables with very high high water
                              > marks.
                              >
                              > The statistics don't get wiped out when the process finishes.  The
                              > statistics will be removed by the truncate command (mostly the processes
                              > only do this at the beginning not the end - the data is kept for potential
                              > debugging).  The real problem is that the statistics don't match the data.
                              > The truncate-populate-analyze processing in PT8 resolves this.
                              >
                              > Note: If you optimiser mode is choose, and none of the tables reference in a
                              > query have statistics (and none of them are partitioned or have a degree of
                              > parallelism, and there are no hints) then you get rule based optimisation.
                              > If some of the tables have stats then you get cost based optimisation and
                              > the optimiser makes some guesses about the tables without statistics.
                              >
                              > Rule based optimisation is now a deprecated feature in Oracle.  You really
                              > should be aiming to use Cost-based optimisation.
                              >
                              > _________________________
                              > David Kurtz
                              > Go-Faster Consultancy Ltd.
                              > tel: +44 (0)7771 760660
                              > fax: +44 (0)7092 348865
                              > mailto:david.kurtz@...
                              > web: www.go-faster.co.uk
                              > PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
                              >
                              >
                              >
                              >
                              >
                              >
                              >
                              >
                              > The PeopleSoft DBA Forum is managed by Go-Faster Consultancy Ltd. http://www.go-faster.co.uk
                              > Yahoo! Groups Links
                              >
                              >
                              >

                              >
                              >




                              PeopleSoft for the Oracle DBA will be published by Apress in September.  The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk.



                            • Henry Poras
                              Joan, As David noted, these are not Oracle temporary tables. This means you will not find them using a dba_tables query on temporary . You can find them in
                              Message 14 of 21 , Jun 21 9:16 AM
                              • 0 Attachment
                                Joan,

                                As David noted, these are not Oracle temporary tables. This means you
                                will not find them using a dba_tables query on 'temporary'. You can
                                find them in App Designer, and probably through querying a
                                PeopleTools table on the database (though at the moment I don't know
                                which one).

                                Henry

                                --- In psftdba@yahoogroups.com, Joan Hsieh <joan.hsieh@t...> wrote:
                                > Hi David,
                                >
                                > We are running pt 8.4 for FM, I didn't see any tables is temporary
                                table
                                > as I query the dba_tables.temporary. Did I use the wrong dictionary
                                > table? Currectly we analyzed all the tables and deleted all of
                                joined
                                > statistics tables for some of bad performance sql to force it run
                                rule
                                > based optim.
                                >
                                > Thanks,
                                >
                                > Joan
                                >
                                > David Kurtz wrote:
                                >
                                > > I see that this topic has touched a nerve - there were 4 new
                                messages just
                                > > while I was writting this.
                                > >
                                > > For clarity, we are talking about permanent database tables used
                                by mainly
                                > > Application Engine (AE) processes to temporarily store data for
                                working
                                > > storage. These are not Oracle Global Temporary tables (unless
                                you make them
                                > > so which raises lots of other points).
                                > >
                                > > In PeopleTools 8, PeopleSoft introduced a new record type,
                                Temporary Tables.
                                > > You can have multiple Oracle tables that correspond to the same
                                PeopleSoft
                                > > Temporary Table record. There is the concept of a set of
                                temporary tables
                                > > that are allocated to an AE instance. So each temporary table is
                                only
                                > > referenced by a single AE process (unless there are no free temp
                                table
                                > > instances).
                                > >
                              • Joan Hsieh
                                Thanks, I did select * from dba_tables where temporary= Y ; NO rows returned. So all the peoplesoft tables are not oracle temporary tables and all those table
                                Message 15 of 21 , Jun 21 10:46 AM
                                • 0 Attachment
                                  Thanks, I did select * from dba_tables where temporary='Y' ; NO rows
                                  returned. So all the peoplesoft tables are not oracle temporary tables
                                  and all those table get analyzed in the AE processing?

                                  Joan


                                  Henry Poras wrote:

                                  > Joan,
                                  >
                                  > As David noted, these are not Oracle temporary tables. This means you
                                  > will not find them using a dba_tables query on 'temporary'. You can
                                  > find them in App Designer, and probably through querying a
                                  > PeopleTools table on the database (though at the moment I don't know
                                  > which one).
                                  >
                                  > Henry
                                  >
                                  > --- In psftdba@yahoogroups.com, Joan Hsieh <joan.hsieh@t...> wrote:
                                  >
                                  >>Hi David,
                                  >>
                                  >>We are running pt 8.4 for FM, I didn't see any tables is temporary
                                  >
                                  > table
                                  >
                                  >>as I query the dba_tables.temporary. Did I use the wrong dictionary
                                  >>table? Currectly we analyzed all the tables and deleted all of
                                  >
                                  > joined
                                  >
                                  >>statistics tables for some of bad performance sql to force it run
                                  >
                                  > rule
                                  >
                                  >>based optim.
                                  >>
                                  >>Thanks,
                                  >>
                                  >>Joan
                                  >>
                                  >>David Kurtz wrote:
                                  >>
                                  >>
                                  >>>I see that this topic has touched a nerve - there were 4 new
                                  >
                                  > messages just
                                  >
                                  >>>while I was writting this.
                                  >>>
                                  >>>For clarity, we are talking about permanent database tables used
                                  >
                                  > by mainly
                                  >
                                  >>>Application Engine (AE) processes to temporarily store data for
                                  >
                                  > working
                                  >
                                  >>>storage. These are not Oracle Global Temporary tables (unless
                                  >
                                  > you make them
                                  >
                                  >>>so which raises lots of other points).
                                  >>>
                                  >>>In PeopleTools 8, PeopleSoft introduced a new record type,
                                  >
                                  > Temporary Tables.
                                  >
                                  >>>You can have multiple Oracle tables that correspond to the same
                                  >
                                  > PeopleSoft
                                  >
                                  >>>Temporary Table record. There is the concept of a set of
                                  >
                                  > temporary tables
                                  >
                                  >>>that are allocated to an AE instance. So each temporary table is
                                  >
                                  > only
                                  >
                                  >>>referenced by a single AE process (unless there are no free temp
                                  >
                                  > table
                                  >
                                  >>>instances).
                                  >>>
                                  >
                                  >
                                  >
                                  >
                                  >
                                  >
                                  >
                                  > PeopleSoft for the Oracle DBA will be published by Apress in September. The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk
                                  >
                                  > Yahoo! Groups Links
                                  >
                                  >
                                  >
                                  >
                                  >
                                  >
                                • David Kurtz
                                  Let me repeat ... These are type 7 PeopleSoft records (PSRECDEFN.RECTYPE = 7). If you have a record called FRED, with 10 instances you get tables PS_FRED
                                  Message 16 of 21 , Jun 21 3:11 PM
                                  • 0 Attachment
                                    Let me repeat
                                    > For clarity, we are talking about permanent database tables used by mainly
                                    > Application Engine (AE) processes to temporarily store data for working
                                    > storage. These are NOT Oracle Global Temporary tables

                                    These are type 7 PeopleSoft records (PSRECDEFN.RECTYPE = 7).

                                    If you have a record called FRED, with 10 instances you get tables
                                    PS_FRED
                                    PS_FRED1
                                    PS_FRED2
                                    ..
                                    PS_FRED10

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


                                    -----Original Message-----
                                    From: Joan Hsieh [mailto:joan.hsieh@...]
                                    Sent: 21 June 2004 16:13
                                    To: psftdba@yahoogroups.com
                                    Subject: Re: PeopleSoft DBA Forum accurate statistics for temp tables


                                    Hi David,

                                    We are running pt 8.4 for FM, I didn't see any tables is temporary table
                                    as I query the dba_tables.temporary. Did I use the wrong dictionary
                                    table? Currectly we analyzed all the tables and deleted all of joined
                                    statistics tables for some of bad performance sql to force it run rule
                                    based optim.

                                    Thanks,

                                    Joan

                                    David Kurtz wrote:

                                    > I see that this topic has touched a nerve - there were 4 new messages just
                                    > while I was writting this.
                                    >
                                    > For clarity, we are talking about permanent database tables used by mainly
                                    > Application Engine (AE) processes to temporarily store data for working
                                    > storage. These are not Oracle Global Temporary tables (unless you make
                                    them
                                    > so which raises lots of other points).
                                    >
                                    > In PeopleTools 8, PeopleSoft introduced a new record type, Temporary
                                    Tables.
                                    > You can have multiple Oracle tables that correspond to the same PeopleSoft
                                    > Temporary Table record. There is the concept of a set of temporary tables
                                    > that are allocated to an AE instance. So each temporary table is only
                                    > referenced by a single AE process (unless there are no free temp table
                                    > instances).
                                    >
                                    > What PeopleSoft are starting to do is that when an AE truncates and
                                    > repopulates an temporary table it regenerated the CBO statistics. In
                                    general
                                    > this works quite well. You have valid statistics when you need them,
                                    > although not if you refresh the statistics at any other time. Although I
                                    do
                                    > have a few criticisms.
                                    >
                                    > PeopleSoft always collects the statistics with the RECSTATS metacode,
                                    which
                                    > does an ANALYZE TABLE ... ESTIMATE STATISTICS on a default sample size.
                                    > They don't use DBMS_STATISTICS, and they never use a larger sample size
                                    nor
                                    > do they generate histograms. If you want to change this behaviour you
                                    have
                                    > to customise the AE.
                                    > They don't always generate the statistics on all tables. So you may have
                                    to
                                    > add this yourself.
                                    >
                                    > In PT7, multiple instances of the same AE process could reference the same
                                    > working storage table. The tables were usually keyed on process instance.
                                    > They couldn't truncate the table because it would affect all the processes
                                    > that used the same table. If a process crashed it could leave debris in
                                    the
                                    > table. So you could get working storage tables with very high high water
                                    > marks.
                                    >
                                    > The statistics don't get wiped out when the process finishes. The
                                    > statistics will be removed by the truncate command (mostly the processes
                                    > only do this at the beginning not the end - the data is kept for potential
                                    > debugging). The real problem is that the statistics don't match the data.
                                    > The truncate-populate-analyze processing in PT8 resolves this.
                                    >
                                    > Note: If you optimiser mode is choose, and none of the tables reference in
                                    a
                                    > query have statistics (and none of them are partitioned or have a degree
                                    of
                                    > parallelism, and there are no hints) then you get rule based optimisation.
                                    > If some of the tables have stats then you get cost based optimisation and
                                    > the optimiser makes some guesses about the tables without statistics.
                                    >
                                    > Rule based optimisation is now a deprecated feature in Oracle. You really
                                    > should be aiming to use Cost-based optimisation.
                                    >
                                    > _________________________
                                    > David Kurtz
                                    > Go-Faster Consultancy Ltd.
                                    > tel: +44 (0)7771 760660
                                    > fax: +44 (0)7092 348865
                                    > mailto:david.kurtz@...
                                    > web: www.go-faster.co.uk
                                    > PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
                                    >
                                    >
                                    >
                                    >
                                    >
                                    >
                                    >
                                    >
                                    > The PeopleSoft DBA Forum is managed by Go-Faster Consultancy Ltd.
                                    http://www.go-faster.co.uk
                                    > Yahoo! Groups Links
                                    >
                                    >
                                    >
                                    >
                                    >
                                    >





                                    PeopleSoft for the Oracle DBA will be published by Apress in September. The
                                    PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk

                                    Yahoo! Groups Links
                                  • Phillip Eaton
                                    ... You could try having a read through the Application Engine PeopleBook regarding this seemingly strange setup of PeopleSoft temporary tables . I read them
                                    Message 17 of 21 , Jun 22 2:53 AM
                                    • 0 Attachment
                                      > -----Original Message-----
                                      > From: Joan Hsieh [mailto:joan.hsieh@...]
                                      >
                                      > Thanks, I did select * from dba_tables where temporary='Y' ; NO rows
                                      > returned. So all the peoplesoft tables are not oracle
                                      > temporary tables
                                      > and all those table get analyzed in the AE processing?
                                      >
                                      > Joan

                                      You could try having a read through the Application Engine PeopleBook
                                      regarding this seemingly strange setup of PeopleSoft 'temporary tables'.

                                      I read them completely a few months ago and found that it explains it
                                      reasonably well.

                                      Phillip Eaton
                                      Business Applications Administration Group
                                      Gulf International Bank (UK) Ltd.
                                    • Finkelstein, Alec
                                      Hello, I have found that on Oracle 8i the only reliable way to get a meaningful stats on the temp tables is to do it dynamically. While you can get away with
                                      Message 18 of 21 , Jun 22 10:53 AM
                                      • 0 Attachment
                                        Hello,

                                        I have found that on Oracle 8i the only reliable way to get a meaningful stats on the temp tables is to do it dynamically. While you can get away with exp/imp of a useful stats, it will only work if sizes of the batches you run are fairly static.

                                        Not having the luxury, I had to resort to a workaround:

                                        1. Created a table to hold "batch_active" flag.
                                        2. Create 2 triggers on the process request (sysadm.psprcsrqst) table that set/unset the batch_active flag if certain batches (in my case AR_UPDATE for Financials) are running.
                                        3. Parsing the traces the interested batches, compiled a list of temp tables used.
                                        4. On each instance of these temp tables I created a "before insert" trigger. The trigger would schedule "analyze table estimate 1 percent" job for the table. Having enough job_queue_processes and job_queue_interval = 1 is important.

                                        If this sounds pretty cumbersome... well, it is. It does the trick, though. I have a procedure to generate the triggers that takes a table name as a parameter. I can send all the code to anybody interested.

                                        -Alec.


                                        --- In psftdba@yahoogroups.com, "Satish KARRY" <karry@x> wrote:
                                        > Its really tough to get statistics on those tables. As you said they will be
                                        > wiped out as soon as the process completes.
                                        >
                                        > But most of the processes have updatestatistics built into the program. So
                                        > if you set your optimizer mode to say cost, you are fine.
                                        >
                                        >
                                        >
                                        > _____
                                        >
                                        > From: pjksel [mailto:pjksel@y...]
                                        > Sent: Wednesday, June 16, 2004 7:25 AM
                                        > To: psftdba@yahoogroups.com
                                        > Subject: PeopleSoft DBA Forum accurate statistics for temp tables
                                        >
                                        >
                                        > We are running Oracle 817 with the optimizer mode set to choose. I am
                                        > trying to get accurate statistics for the temp tables that Peoplesoft
                                        > uses for various processes. Everytime I try to gather statistics I
                                        > end up with zero rows for the temp tables because peoplesoft deletes
                                        > the data after the process is complete. I thought about not computing
                                        > statistics for the temp tables and forcing rule-based on those
                                        > tables; however peoplesoft joins the temp tables to other non
                                        > temporary tables and thus Oracle will force cost based on all tables.
                                        >
                                        > What is the best way to get statistics for those temp tables?
                                        >
                                        > Thanks,
                                      • litbighor
                                        Hi I have a suggestion : what about putting a trigger on the Peoplesoft xxx_TMP table when insert into xxx_TMP then insert in a xxx_COPY then you have the
                                        Message 19 of 21 , Jun 30 6:18 AM
                                        • 0 Attachment
                                          Hi

                                          I have a suggestion :
                                          what about putting a trigger on the Peoplesoft xxx_TMP table
                                          when insert into xxx_TMP then insert in a xxx_COPY

                                          then you have the biggest image of xxx_TMP
                                          Use the stats of this xxx_COPY to set the stat for xxx_TMP ?

                                          LitBigHor
                                        • R P
                                          Hi, Thanks for your suggestion. Actually I implemented this a few days ago after thinking about all the different ways. It seems to be working great. I have
                                          Message 20 of 21 , Jun 30 7:33 AM
                                          • 0 Attachment
                                            Hi,
                                             
                                            Thanks for your suggestion. Actually I implemented this a few days ago after thinking about all the different ways. It seems to be working great. I have accumulated stats for several temp tables. Although the stats will not be 100% accurate it is much better than not having any at all. I have noticed Oracle using the index on several of the temp tables which has greatly improved system performance. I appreciate everyones suggestion.
                                             
                                             
                                             


                                            litbighor <lehuyluan@...> wrote:
                                            Hi

                                            I have a suggestion :
                                            what about putting a trigger on the Peoplesoft xxx_TMP table
                                            when insert into xxx_TMP then insert in a xxx_COPY

                                            then you have the biggest image of xxx_TMP
                                            Use the stats of this xxx_COPY to set the stat for xxx_TMP ?

                                            LitBigHor




                                            PeopleSoft for the Oracle DBA will be published by Apress in September.  The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk.




                                            Do you Yahoo!?
                                            New and Improved Yahoo! Mail - 100MB free storage!

                                          • michael.ngong@crown.com
                                            Excuse my ignorance.Do we need to analyze the temp tablespace/temp tables in peoplesoft for any reason. These discussion has been going on for a while but does
                                            Message 21 of 21 , Jun 30 2:55 PM
                                            • 0 Attachment

                                              Excuse my ignorance.Do we need to analyze the temp tablespace/temp tables in peoplesoft for any reason.
                                              These discussion has been going on for a while but does not make sense to me(Hope I am not alone:))
                                              At least on my other applications I do not ,If peoplesoft requires us to, is it pstemp /or
                                              some temp tables that I do not know.From an Oracle standpoint not sure
                                              Again excuse my ignorance.....


                                              Michael




                                              R P <pjksel@...>

                                              06/30/2004 10:33 AM

                                              Please respond to
                                              psftdba@yahoogroups.com

                                              To
                                              psftdba@yahoogroups.com
                                              cc
                                              Subject
                                              Re: PeopleSoft DBA Forum Re: accurate statistics for temp tables





                                              Hi,
                                               
                                              Thanks for your suggestion. Actually I implemented this a few days ago after thinking about all the different ways. It seems to be working great. I have accumulated stats for several temp tables. Although the stats will not be 100% accurate it is much better than not having any at all. I have noticed Oracle using the index on several of the temp tables which has greatly improved system performance. I appreciate everyones suggestion.
                                               
                                               
                                               


                                              litbighor <lehuyluan@...>
                                              wrote:

                                              Hi

                                              I have a suggestion :
                                              what about putting a trigger on the Peoplesoft xxx_TMP table
                                              when insert into xxx_TMP then insert in a xxx_COPY

                                              then you have the biggest image of xxx_TMP
                                              Use the stats of this xxx_COPY to set the stat for xxx_TMP ?

                                              LitBigHor




                                              PeopleSoft for the Oracle DBA will be published by Apress in September.  The PeopleSoft DBA Forum is managed by
                                              http://www.go-faster.co.uk.



                                              Do you Yahoo!?
                                              New and Improved Yahoo! Mail - 100MB free storage!

                                              PeopleSoft for the Oracle DBA will be published by Apress in September.  The PeopleSoft DBA Forum is managed by
                                              http://www.go-faster.co.uk.


                                              Yahoo! Groups Sponsor

                                              ADVERTISEMENT
                                              click here




                                              Yahoo! Groups Links
                                            Your message has been successfully submitted and would be delivered to recipients shortly.