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

RE: PeopleSoft DBA Forum Partitioning

Expand Messages
  • David Kurtz
    I have partitioned JOB as a part of a wider partitioning strategy for Global Payroll. This has been done on a number of customer sites with considerable
    Message 1 of 6 , May 10, 2006
      I have partitioned JOB as a part of a wider partitioning strategy for Global
      Payroll. This has been done on a number of customer sites with considerable
      success.

      Job was range partitioned on EMPLID to match the streaming of the payroll
      processing (see http://homepage.ntlworld.com/david.kurtz/gp.streaming.doc).
      The indexes were locally partitioned.
      It it performed well, and the indexes don't go invalid when you do partition
      management.

      The catch is that you have to manage the objects yourself, App Desginer
      won't build partitioned tables or indexes. We had
      over 50 partitioned and global temporary tables. So I built a PL/SQL
      procedure to replace the build script generation in App Designer.

      What problem are you trying to resolve?


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

      > -----Original Message-----
      > From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On Behalf
      > Of Steve
      > Sent: 10 May 2006 13:10
      > To: psftdba@yahoogroups.com
      > Subject: PeopleSoft DBA Forum Partitioning
      >
      >
      > Good day,
      > We had some sucess partitioning tables and I'm looking at partitioning
      > PS_JOB now. Can anyone provide any advice on which partitioning method
      > to use and on what field to partition. Also are partitioned indexes of
      > any benefit?
      >
      > Thanks
      >
      > Steve
      >
      >
      >
      >
      >
      >
      >
      > PeopleSoft for the Oracle DBA is published by Apress - see
      http://www.psftdba.com
      The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk

      Yahoo! Groups Links
    • Montgomerie, Steve (SSL US)
      Thanks Dave, Looking at PER099 an other ps_job related queries. For PER099 I ve had most success with partitioning by list based on the company code as we
      Message 2 of 6 , May 11, 2006
        Thanks Dave,
        Looking at PER099 an other ps_job related queries.

        For PER099 I've had most success with partitioning by list
        based on the company code as we process payroll for 150 companies
        In one environment. I'm to the point again where my greatest wait time
        is spent on SQL*Net message from client which I guess is Oracle waiting on SQRW.

        I wish I knew hoe to make SQRW go faster. We have a beefy machine and it's
        still not even touching the CPU or Memory. My inclination is to buy a faster
        machine but I have a hard time with that as the resources on the current machine
        are hardly even touched.

        Steve

        Elapsed times include waiting on following events:
        Event waited on Times Max. Wait Total Waited
        ---------------------------------------- Waited ---------- ------------
        SQL*Net message to client 3050320 0.00 6.75
        SQL*Net message from client 3050320 0.21 2091.75
        log file sync 26 0.00 0.01
        db file sequential read 98212 1.76 319.38
        SQL*Net more data from client 458235 0.01 58.63
        SQL*Net more data to client 18884 0.03 6.29
        db file scattered read 16946 2.08 66.46
        latch free 6 0.00 0.00


        Steve Montgomerie
        PeopleSoft/Oracle Administrator
        SIEMENS Shared Services, LLC
        4400 Alafaya Trail, M/S AL-999
        Orlando, FL 32826
        Phone: 407 487-5219
        Cell : 407 925 4179
        Fax: 407 487-5850

        -----Original Message-----
        From: David Kurtz [mailto:info2@...]
        Sent: Wednesday, May 10, 2006 8:39 AM
        To: psftdba@yahoogroups.com
        Subject: RE: PeopleSoft DBA Forum Partitioning

        I have partitioned JOB as a part of a wider partitioning strategy for Global
        Payroll. This has been done on a number of customer sites with considerable
        success.

        Job was range partitioned on EMPLID to match the streaming of the payroll
        processing (see http://homepage.ntlworld.com/david.kurtz/gp.streaming.doc).
        The indexes were locally partitioned.
        It it performed well, and the indexes don't go invalid when you do partition
        management.

        The catch is that you have to manage the objects yourself, App Desginer
        won't build partitioned tables or indexes. We had
        over 50 partitioned and global temporary tables. So I built a PL/SQL
        procedure to replace the build script generation in App Designer.

        What problem are you trying to resolve?


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

        > -----Original Message-----
        > From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On Behalf
        > Of Steve
        > Sent: 10 May 2006 13:10
        > To: psftdba@yahoogroups.com
        > Subject: PeopleSoft DBA Forum Partitioning
        >
        >
        > Good day,
        > We had some sucess partitioning tables and I'm looking at partitioning
        > PS_JOB now. Can anyone provide any advice on which partitioning method
        > to use and on what field to partition. Also are partitioned indexes of
        > any benefit?
        >
        > Thanks
        >
        > Steve
        >
        >
        >
        >
        >
        >
        >
        > PeopleSoft for the Oracle DBA is published by Apress - see
        http://www.psftdba.com
        The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk

        Yahoo! Groups Links
      • David Kurtz
        SQR has become very CPU hungry in the latest release. Yes, the SQL*Net message from client suggests that it is probably not the database. There are a lot of
        Message 3 of 6 , May 11, 2006
          SQR has become very CPU hungry in the latest release.

          Yes, the SQL*Net message from client suggests that it is probably not the
          database. There are a lot of them so I expect that there are lots dynamic
          SQL statements being submitted by the SQR? There is also a fair amount of
          SQL*Net more data from client, could there be a network issue? Where is the
          SQR running? Close to the database server?


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

          > -----Original Message-----
          > From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On Behalf
          > Of Montgomerie, Steve (SSL US)
          > Sent: 11 May 2006 13:05
          > To: info2@...; psftdba@yahoogroups.com
          > Subject: RE: PeopleSoft DBA Forum Partitioning
          >
          >
          > Thanks Dave,
          > Looking at PER099 an other ps_job related queries.
          >
          > For PER099 I've had most success with partitioning by list
          > based on the company code as we process payroll for 150 companies
          > In one environment. I'm to the point again where my greatest wait time
          > is spent on SQL*Net message from client which I guess is Oracle
          > waiting on SQRW.
          >
          > I wish I knew hoe to make SQRW go faster. We have a beefy machine
          > and it's
          > still not even touching the CPU or Memory. My inclination is to
          > buy a faster
          > machine but I have a hard time with that as the resources on the
          > current machine
          > are hardly even touched.
          >
          > Steve
          >
          > Elapsed times include waiting on following events:
          > Event waited on Times Max. Wait
          > Total Waited
          > ---------------------------------------- Waited ----------
          > ------------
          > SQL*Net message to client 3050320 0.00
          > 6.75
          > SQL*Net message from client 3050320 0.21
          > 2091.75
          > log file sync 26 0.00
          > 0.01
          > db file sequential read 98212 1.76
          > 319.38
          > SQL*Net more data from client 458235 0.01
          > 58.63
          > SQL*Net more data to client 18884 0.03
          > 6.29
          > db file scattered read 16946 2.08
          > 66.46
          > latch free 6 0.00
          > 0.00
          >
          >
          > Steve Montgomerie
          > PeopleSoft/Oracle Administrator
          > SIEMENS Shared Services, LLC
          > 4400 Alafaya Trail, M/S AL-999
          > Orlando, FL 32826
          > Phone: 407 487-5219
          > Cell : 407 925 4179
          > Fax: 407 487-5850
          >
          > -----Original Message-----
          > From: David Kurtz [mailto:info2@...]
          > Sent: Wednesday, May 10, 2006 8:39 AM
          > To: psftdba@yahoogroups.com
          > Subject: RE: PeopleSoft DBA Forum Partitioning
          >
          > I have partitioned JOB as a part of a wider partitioning strategy
          > for Global
          > Payroll. This has been done on a number of customer sites with
          > considerable
          > success.
          >
          > Job was range partitioned on EMPLID to match the streaming of the payroll
          > processing (see
          > http://homepage.ntlworld.com/david.kurtz/gp.streaming.doc).
          > The indexes were locally partitioned.
          > It it performed well, and the indexes don't go invalid when you
          > do partition
          > management.
          >
          > The catch is that you have to manage the objects yourself, App Desginer
          > won't build partitioned tables or indexes. We had
          > over 50 partitioned and global temporary tables. So I built a PL/SQL
          > procedure to replace the build script generation in App Designer.
          >
          > What problem are you trying to resolve?
          >
          >
          > regards
          > _________________________
          > David Kurtz
          > Go-Faster Consultancy Ltd.
          > tel: +44 (0)7771 760660
          > fax: +44 (0)7092 348865
          > mailto:david.kurtz@...
          > web: www.go-faster.co.uk
          > Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
          > The PeopleSoft DBA Blog: http://psftdba.blogspot.com
          > PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
          >
          > > -----Original Message-----
          > > From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On Behalf
          > > Of Steve
          > > Sent: 10 May 2006 13:10
          > > To: psftdba@yahoogroups.com
          > > Subject: PeopleSoft DBA Forum Partitioning
          > >
          > >
          > > Good day,
          > > We had some sucess partitioning tables and I'm looking at partitioning
          > > PS_JOB now. Can anyone provide any advice on which partitioning method
          > > to use and on what field to partition. Also are partitioned indexes of
          > > any benefit?
          > >
          > > Thanks
          > >
          > > Steve
          > >
          > >
          > >
          > >
          > >
          > >
          > >
          > > PeopleSoft for the Oracle DBA is published by Apress - see
          > http://www.psftdba.com
          > The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk
          >
          > Yahoo! Groups Links
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          > PeopleSoft for the Oracle DBA is published by Apress - see
          http://www.psftdba.com
          The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk

          Yahoo! Groups Links
        • tpleighton@yahoo.com
          Steve, I do not know of an optimizer for SQR, however, hints should help you. Also, you could look at all (if any) of your in house or customized SQR programs
          Message 4 of 6 , May 11, 2006
            Steve,

            I do not know of an optimizer for SQR, however, hints
            should help you. Also, you could look at all (if any)
            of your in house or customized SQR programs with an
            eye toward optimizing those. There are SQR debuggers
            available. I cannot recommend on for I have not used
            them. Just remember that SQR is a lot like Cobol in
            the sense that both are antiquitated languages that
            are quite unwieldy.

            Tom Leighton

            --- "Montgomerie, Steve (SSL US)"
            <steve.montgomerie@...> wrote:

            > Thanks Dave,
            > Looking at PER099 an other ps_job related queries.
            >
            > For PER099 I've had most success with partitioning
            > by list
            > based on the company code as we process payroll for
            > 150 companies
            > In one environment. I'm to the point again where my
            > greatest wait time
            > is spent on SQL*Net message from client which I
            > guess is Oracle waiting on SQRW.
            >
            > I wish I knew hoe to make SQRW go faster. We have a
            > beefy machine and it's
            > still not even touching the CPU or Memory. My
            > inclination is to buy a faster
            > machine but I have a hard time with that as the
            > resources on the current machine
            > are hardly even touched.
            >
            > Steve
            >
            > Elapsed times include waiting on following events:
            > Event waited on Times
            > Max. Wait Total Waited
            > ---------------------------------------- Waited
            > ---------- ------------
            > SQL*Net message to client 3050320
            > 0.00 6.75
            > SQL*Net message from client 3050320
            > 0.21 2091.75
            > log file sync 26
            > 0.00 0.01
            > db file sequential read 98212
            > 1.76 319.38
            > SQL*Net more data from client 458235
            > 0.01 58.63
            > SQL*Net more data to client 18884
            > 0.03 6.29
            > db file scattered read 16946
            > 2.08 66.46
            > latch free 6
            > 0.00 0.00
            >
            >
            > Steve Montgomerie
            > PeopleSoft/Oracle Administrator
            > SIEMENS Shared Services, LLC
            > 4400 Alafaya Trail, M/S AL-999
            > Orlando, FL 32826
            > Phone: 407 487-5219
            > Cell : 407 925 4179
            > Fax: 407 487-5850
            >
            > -----Original Message-----
            > From: David Kurtz [mailto:info2@...]
            > Sent: Wednesday, May 10, 2006 8:39 AM
            > To: psftdba@yahoogroups.com
            > Subject: RE: PeopleSoft DBA Forum Partitioning
            >
            > I have partitioned JOB as a part of a wider
            > partitioning strategy for Global
            > Payroll. This has been done on a number of customer
            > sites with considerable
            > success.
            >
            > Job was range partitioned on EMPLID to match the
            > streaming of the payroll
            > processing (see
            >
            http://homepage.ntlworld.com/david.kurtz/gp.streaming.doc).
            > The indexes were locally partitioned.
            > It it performed well, and the indexes don't go
            > invalid when you do partition
            > management.
            >
            > The catch is that you have to manage the objects
            > yourself, App Desginer
            > won't build partitioned tables or indexes. We had
            > over 50 partitioned and global temporary tables. So
            > I built a PL/SQL
            > procedure to replace the build script generation in
            > App Designer.
            >
            > What problem are you trying to resolve?
            >
            >
            > regards
            > _________________________
            > David Kurtz
            > Go-Faster Consultancy Ltd.
            > tel: +44 (0)7771 760660
            > fax: +44 (0)7092 348865
            > mailto:david.kurtz@...
            > web: www.go-faster.co.uk
            > Book: PeopleSoft for the Oracle DBA:
            > http://www.psftdba.com
            > The PeopleSoft DBA Blog: http://psftdba.blogspot.com
            > PeopleSoft DBA Forum:
            > http://groups.yahoo.com/group/psftdba
            >
            > > -----Original Message-----
            > > From: psftdba@yahoogroups.com
            > [mailto:psftdba@yahoogroups.com]On Behalf
            > > Of Steve
            > > Sent: 10 May 2006 13:10
            > > To: psftdba@yahoogroups.com
            > > Subject: PeopleSoft DBA Forum Partitioning
            > >
            > >
            > > Good day,
            > > We had some sucess partitioning tables and I'm
            > looking at partitioning
            > > PS_JOB now. Can anyone provide any advice on which
            > partitioning method
            > > to use and on what field to partition. Also are
            > partitioned indexes of
            > > any benefit?
            > >
            > > Thanks
            > >
            > > Steve
            > >
            > >
            > >
            > >
            > >
            > >
            > >
            > > PeopleSoft for the Oracle DBA is published by
            > Apress - see
            > http://www.psftdba.com
            > The PeopleSoft DBA Forum is managed by
            > http://www.go-faster.co.uk
            >
            > Yahoo! Groups Links
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >


            __________________________________________________
            Do You Yahoo!?
            Tired of spam? Yahoo! Mail has the best spam protection around
            http://mail.yahoo.com
          • kbaker@enwin.com
            Checking with our developers, hints in the sqr and/or views is their #1 tuning option. However, with large tables, ( in our Psoft/CIS app. this is 50 - 250
            Message 5 of 6 , May 11, 2006
              Checking with our developers, hints in the sqr and/or views is their #1 tuning option.

              However, with large tables, ( in our Psoft/CIS app. this is 50 - 250 million+ rows) they have had
              success by creating temp/staging tables and processing smaller groups of data
              (old 4th gl - Powehouse qtp technique when not rewriting in cobol)....

              sqr, cobol and old tuning methods...just can't get rid of us yet....

              remember to analyze temp tables for cbo.

              Kevin Baker



              <tpleighton@...>
              Sent by: psftdba@yahoogroups.com

              2006-05-11 08:18 AM

              Please respond to
              psftdba@yahoogroups.com

              To
              psftdba@yahoogroups.com
              cc
              Subject
              RE: PeopleSoft DBA Forum Partitioning





              Steve,

              I do not know of an optimizer for SQR, however, hints
              should help you.  Also, you could look at all (if any)
              of your in house or customized SQR programs with an
              eye toward optimizing those.  There are SQR debuggers
              available.  I cannot recommend on for I have not used
              them.  Just remember that SQR is a lot like Cobol in
              the sense that both are antiquitated languages that
              are quite unwieldy.

              Tom Leighton

              --- "Montgomerie, Steve (SSL US)"
              <steve.montgomerie@...> wrote:

              > Thanks Dave,
              > Looking at PER099 an other ps_job related queries.
              >
              > For PER099 I've had most success with partitioning
              > by list
              > based on the company code as we process payroll for
              > 150 companies
              > In one environment. I'm to the point again where my
              > greatest wait time
              > is spent on SQL*Net message from client which I
              > guess is Oracle waiting on SQRW.
              >
              > I wish I knew hoe to make SQRW go faster. We have a
              > beefy machine and it's
              > still not even touching the CPU or Memory. My
              > inclination is to buy a faster
              > machine but I have a hard time with that as the
              > resources on the current machine
              > are hardly even touched.
              >
              > Steve
              >
              > Elapsed times include waiting on following events:
              >   Event waited on              
                            Times
              >  Max. Wait  Total Waited
              >   ----------------------------------------   Waited
              > ----------  ------------
              >   SQL*Net message to client          
                    3050320
              >       0.00          6.75
              >   SQL*Net message from client          
                  3050320
              >       0.21       2091.75
              >   log file sync              
                                 26
              >       0.00          0.01
              >   db file sequential read          
                        98212
              >       1.76        319.38
              >   SQL*Net more data from client          
                 458235
              >       0.01         58.63
              >   SQL*Net more data to client          
                    18884
              >       0.03          6.29
              >   db file scattered read            
                       16946
              >       2.08         66.46
              >   latch free              
                                     6
              >       0.00          0.00
              >
              >
              > Steve Montgomerie
              > PeopleSoft/Oracle Administrator
              > SIEMENS Shared Services, LLC
              > 4400 Alafaya Trail, M/S AL-999
              > Orlando, FL 32826
              > Phone: 407 487-5219
              > Cell : 407 925 4179
              > Fax: 407 487-5850
              >
              > -----Original Message-----
              > From: David Kurtz [mailto:info2@...]
              > Sent: Wednesday, May 10, 2006 8:39 AM
              > To: psftdba@yahoogroups.com
              > Subject: RE: PeopleSoft DBA Forum Partitioning
              >
              > I have partitioned JOB as a part of a wider
              > partitioning strategy for Global
              > Payroll.  This has been done on a number of customer
              > sites with considerable
              > success.
              >
              > Job was range partitioned on EMPLID to match the
              > streaming of the payroll
              > processing (see
              >
              http://homepage.ntlworld.com/david.kurtz/gp.streaming.doc).
              > The indexes were locally partitioned.
              > It it performed well, and the indexes don't go
              > invalid when you do partition
              > management.
              >
              > The catch is that you have to manage the objects
              > yourself, App Desginer
              > won't build partitioned tables or indexes.  We had
              > over 50 partitioned and global temporary tables.  So
              > I built a PL/SQL
              > procedure to replace the build script generation in
              > App Designer.
              >
              > What problem are you trying to resolve?
              >
              >
              > regards
              > _________________________
              > David Kurtz
              > Go-Faster Consultancy Ltd.
              > tel: +44 (0)7771 760660
              > fax: +44 (0)7092 348865
              > mailto:david.kurtz@...
              > web: www.go-faster.co.uk
              > Book: PeopleSoft for the Oracle DBA:
              > http://www.psftdba.com
              > The PeopleSoft DBA Blog: http://psftdba.blogspot.com
              > PeopleSoft DBA Forum:
              > http://groups.yahoo.com/group/psftdba
              >
              > > -----Original Message-----
              > > From: psftdba@yahoogroups.com
              > [mailto:psftdba@yahoogroups.com]On Behalf
              > > Of Steve
              > > Sent: 10 May 2006 13:10
              > > To: psftdba@yahoogroups.com
              > > Subject: PeopleSoft DBA Forum Partitioning
              > >
              > >
              > > Good day,
              > > We had some sucess partitioning tables and I'm
              > looking at partitioning
              > > PS_JOB now. Can anyone provide any advice on which
              > partitioning method
              > > to use and on what field to partition. Also are
              > partitioned indexes of
              > > any benefit?
              > >
              > > Thanks
              > >
              > > Steve
              > >
              > >
              > >
              > >
              > >
              > >
              > >
              > > PeopleSoft for the Oracle DBA is published by
              > Apress - see
              > http://www.psftdba.com.
              > The PeopleSoft DBA Forum is managed by
              > http://www.go-faster.co.uk.
              >
              > Yahoo! Groups Links
              >
              >
              >
              >
              >
              >
              >
              >
              >
              >
              >


              __________________________________________________
              Do You Yahoo!?
              Tired of spam?  Yahoo! Mail has the best spam protection around
              http://mail.yahoo.com


              ------------------------ Yahoo! Groups Sponsor --------------------~-->
              Home is just a click away.  Make Yahoo! your home page now.
              http://us.click.yahoo.com/DHchtC/3FxNAA/yQLSAA/JqWylB/TM
              --------------------------------------------------------------------~->

              PeopleSoft for the Oracle DBA is published by Apress - see http://www.psftdba.com.
              The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk.

              Yahoo! Groups Links

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

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

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





            Your message has been successfully submitted and would be delivered to recipients shortly.