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

RE: PeopleSoft DBA Forum accurate statistics for temp tables

Expand Messages
  • 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 1 of 21 , Jun 16, 2004
      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 2 of 21 , Jun 16, 2004
        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 3 of 21 , Jun 16, 2004
          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 4 of 21 , Jun 16, 2004
            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 5 of 21 , Jun 21, 2004
              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 6 of 21 , Jun 21, 2004

                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 7 of 21 , Jun 21, 2004
                  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 8 of 21 , Jun 21, 2004
                    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 9 of 21 , Jun 21, 2004
                      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 10 of 21 , Jun 22, 2004
                        > -----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 11 of 21 , Jun 22, 2004
                          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 12 of 21 , Jun 30, 2004
                            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 13 of 21 , Jun 30, 2004
                              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 14 of 21 , Jun 30, 2004

                                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.