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
    • 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 2 of 21 , Jun 16, 2004
      • 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 3 of 21 , Jun 16, 2004
        • 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 4 of 21 , Jun 21, 2004
          • 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 5 of 21 , Jun 21, 2004
            • 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 6 of 21 , Jun 21, 2004
              • 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 7 of 21 , Jun 21, 2004
                • 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 8 of 21 , Jun 21, 2004
                  • 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 9 of 21 , Jun 22, 2004
                    • 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 10 of 21 , Jun 22, 2004
                      • 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 11 of 21 , Jun 30, 2004
                        • 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 12 of 21 , Jun 30, 2004
                          • 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 13 of 21 , Jun 30, 2004
                            • 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.