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

RE: PeopleSoft DBA Forum PSTREESELNUM Problem

Expand Messages
  • David Kurtz
    It sounds like you are running lots of concurrent nVision reports that use dynamic selectors. What version of PeopleTools are you running? I dislike dynamic
    Message 1 of 7 , Sep 29, 2009
      It sounds like you are running lots of concurrent nVision reports that use
      dynamic selectors.
      What version of PeopleTools are you running?

      I dislike dynamic selectors because
      - The statistics are always going to be out of date on the PSTREESELECTnn
      tables, in particular the selector number which keeps increasing. Oracle is
      going to have trouble calculating cardinality correctly.
      - When an nVision crashes it leaves debris on these tables which grow over
      time

      Unfortunately, switching to static selectors is easier said than done.
      - you would have to change the tree definitions and probably some the
      nVision layout files
      - you would have to retest your reports in case that introduces any
      performance problems

      The update is waiting on another similar update in another session that
      hasn't been committed. The question is what is the holding session doing
      before it commits its update. I haven't seen this problem before, but I
      would guess that the session only commits after the dynamic tree selection
      is build.
      - Are you using separate PSQRYSRV processes in the app server? I would
      recommend that you do.
      - What is the value of DBFlags in the PSTOOLS section of PSAPPSRV.CFG? Have
      you disabled the second DB connection? It is enabled by default, and I
      would not normally disable it.


      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
      DBA Blogs: PeopleSoft: http://blog.psftdba.com, Oracle:
      http://blog.go-faster.co.uk
      PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

      >-----Original Message-----
      >From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]
      >On Behalf Of pams3005
      >Sent: Tuesday, September 29, 2009 9:10 PM
      >To: psftdba@yahoogroups.com
      >Subject: PeopleSoft DBA Forum PSTREESELNUM Problem
      >
      >Hi All,
      >I am facing frequent locking problem with pstreeselnum table.
      >Mostly 3 tier nVision users are having this problem.
      >
      >Here is the query.
      >update pstreeselnum set selector_num=selector_num+1;
      >
      >Can you please help to resolve this issue.
      >
      >Regards
      >Pawan
      >
      >
      >
      >------------------------------------
      >
      >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
      >
      >
      >
    • Pawan Verma
      Hi David, It sounds like you are running lots of concurrent nVision reports that use dynamic selectors. -Yes, we are. What version of PeopleTools are you
      Message 2 of 7 , Sep 30, 2009
        Hi David,

        It sounds like you are running lots of concurrent nVision reports that use
        dynamic selectors.
        -Yes, we are.

        What version of PeopleTools are you running?
        - 8.49.08

        Are you using separate PSQRYSRV processes in the app server?  I would
        recommend that you do.
        -Yes.

        What is the value of DBFlags in the PSTOOLS section of PSAPPSRV.CFG? 
        -8

        Have you disabled the second DB connection?  It is enabled by default, and I would not normally disable it.
        - I don't understand this point.

        Regards
        Pawan Verma
      • David Kurtz
        ... One PSAPPSRV or PSQRYSRV process can have two connections to the database. The second connection is used to increment counters. The idea is that it is a
        Message 3 of 7 , Sep 30, 2009
          >Have you disabled the second DB connection?  It is enabled by
          >default, and I would not normally disable it.
          >- I don't understand this point.

          One PSAPPSRV or PSQRYSRV process can have two connections to the database.
          The second connection is used to increment counters. The idea is that it is
          a bit like incrementing the counter and immediately commiting it in an
          autonomous transaction.

          I think you need to move away from dynamic selectors.

          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
          DBA Blogs: PeopleSoft: http://blog.psftdba.com, Oracle:
          http://blog.go-faster.co.uk
          PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

          >-----Original Message-----
          >From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]
          >On Behalf Of Pawan Verma
          >Sent: Wednesday, September 30, 2009 10:23 PM
          >To: psftdba@yahoogroups.com
          >Subject: RE: PeopleSoft DBA Forum PSTREESELNUM Problem
          >
          >Hi David,
          >
          >It sounds like you are running lots of concurrent nVision
          >reports that use dynamic selectors.
          >-Yes, we are.
          >
          >What version of PeopleTools are you running?
          >- 8.49.08
          >
          >Are you using separate PSQRYSRV processes in the app server? 
          >I would recommend that you do.
          >-Yes.
          >
          >What is the value of DBFlags in the PSTOOLS section of PSAPPSRV.CFG?
          >-8
          >
          >Have you disabled the second DB connection?  It is enabled by
          >default, and I would not normally disable it.
          >- I don't understand this point.
          >
          >Regards
          >Pawan Verma
          >
          >
          >
          >
          >
          >
          >------------------------------------
          >
          >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
          >
          >
          >
        • balbinder singh
          Due to performance issues the Dba team is planning to partition the Peoplesoft large tables.(oracle 10g HRMS 8.8 SP1)   Is there any issues about
          Message 4 of 7 , Oct 6, 2009

            Due to performance issues the Dba team is planning to partition the Peoplesoft large tables.(oracle 10g HRMS 8.8 SP1)
             
            Is there any issues about partitioning peoplesoft tables or any specific maintenance tasks we need to worried about after the dba team did their partitioning.
             
            any advice to go with partitioning in Peoplesoft or not.
             
             

          • BANKS, Richard
            Some non DBA things to consider: -have you exhausted the functional team s options, such as configuration based changes (assuming GP: restricting retro,
            Message 5 of 7 , Oct 8, 2009
              Some non DBA things to consider:
               
              -have you exhausted the functional team's options, such as configuration based changes (assuming GP: restricting retro, historical rules, formulas, store if 0, etc)
              -are you fully application patched
              -have you looked at PeopleTools Data Archive Manager functionality?  It only really became useful in it's second iteration (PT8.49?) when they delivered app templates (e.g. GPSRSLT).  See if you have the product at Home > PeopleTools > Data Archive Manager (old version is Home > PeopleTools > Archive Data ).  May be legislative drivers against using this tool.
               
               
               
               


              From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of balbinder singh
              Sent: Tuesday, 6 October 2009 23:46
              To: psftdba@yahoogroups.com
              Subject: PeopleSoft DBA Forum Partitioning Peoplesoft Tables

               


              Due to performance issues the Dba team is planning to partition the Peoplesoft large tables.(oracle 10g HRMS 8.8 SP1)
               
              Is there any issues about partitioning peoplesoft tables or any specific maintenance tasks we need to worried about after the dba team did their partitioning.
               
              any advice to go with partitioning in Peoplesoft or not.
               
               

              *********************************************************************************

              This e-mail message (along with any attachments) is intended only for the named addressee and could contain information that is confidential or privileged. If you are not the intended recipient you are notified that any dissemination, copying or use of any of the information is prohibited. Please notify us immediately by return e-mail if you are not the intended recipient and delete all copies of the original message and attachments.

               

              This footnote also confirms that this message has been checked for computer viruses.

               

              *********************************************************************************

               

            • David Kurtz
              You can partition tables within PeopleSoft applications. Partitioning is a licenced option on the Oracle database. You would not invalidate your PeopleSoft
              Message 6 of 7 , Oct 11, 2009
                You can partition tables within PeopleSoft applications.  
                 
                Partitioning is a licenced option on the Oracle database.  You would not invalidate your PeopleSoft support, but support would consider it to be a customisation, but they have also published red papers where they recommend it.
                 
                Once you have decided that partitioning is the right technical solution, your biggest problem is likely to be generation of DDL.  Application Designer will not generate the DDL for partitioned objects, you will be on your own.  If you have more than a few partitioned tables, then manually scripting (and maintaining the scripts) will become a significant overhead.  Everytime a table structure or index changes, the script needs to change.
                eg - With UK Global Payroll there are 44 tables that I partition (and a further 52 that are built as global temporary tables), most of the indexes are locally partitioned, but it is necessary to globally partition a couple of them.  If you have Time & Labour, then there are another 9 tables that need partitioning.  Audit tables can also be usefully partitioned.
                 
                My own solution is a PL/SQL package that generates the DDL for partitioned tables and indexes, based upon the contents of the PeopleTools tables, and some of my own meta-data.
                 
                You might also want to consider DDL triggers to prevent accidental loss of partitioned objects because somebody used the generated script.
                 
                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
                DBA Blogs: PeopleSoft: http://blog.psftdba.com, Oracle: http://blog.go-faster.co.uk
                PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
                 


                From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of balbinder singh
                Sent: Tuesday, 6 October 2009 23:46
                To: psftdba@yahoogroups.com
                Subject: PeopleSoft DBA Forum Partitioning Peoplesoft Tables

                 


                Due to performance issues the Dba team is planning to partition the Peoplesoft large tables.(oracle 10g HRMS 8.8 SP1)
                 
                Is there any issues about partitioning peoplesoft tables or any specific maintenance tasks we need to worried about after the dba team did their partitioning.
                 
                any advice to go with partitioning in Peoplesoft or not.
                 
                 

                *********************************************************************************

                This e-mail message (along with any attachments) is intended only for the named addressee and could contain information that is confidential or privileged. If you are not the intended recipient you are notified that any dissemination, copying or use of any of the information is prohibited. Please notify us immediately by return e-mail if you are not the intended recipient and delete all copies of the original message and attachments.

                 

                This footnote also confirms that this message has been checked for computer viruses.

                 

                *********************************************************************************

                 

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