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

RE: PeopleSoft DBA Forum Peoplesoft and oracle

Expand Messages
  • David Kurtz
    There isn t much in PeopleSoft that manages the database. The main issue for the DBA is that all DDL changes to the SYSADM schema (where the PeopleSoft
    Message 1 of 5 , Nov 16, 2008
      There isn't much in PeopleSoft that manages the database. The main issue
      for the DBA is that all DDL changes to the SYSADM schema (where the
      PeopleSoft objects are usually located) must be managed by the PeopleSoft
      Application Designer. So, the DBA cannot, for example, just create an
      index. It should also be defined in App Desginer.

      Dealing with statistics, some Application Engine processes use the
      PeopleCode %UpdateStats macro to gather statistics on specific working
      storage tables during a batch process. This behaviour is also hard coded
      into some Cobol processes.

      However, if you mean how should you generally manage statistics for a
      PeopleSoft system, then I think that is open to some discussion, and is also
      dependent on the version of the database (there are lots of other people on
      this list who have to manage PeopleSoft systems on a day-to-day basis). My
      experience is that most PeopleSoft systems on Oracle 10g that I have seen,
      simply rely on the automatic job that runs in the default maintenance window
      (10pm to 6am and all day at weekened). This is generally good enough for
      most situations. However, sometimes you need to step in and collect
      statistics differently and/or at different times for some tables.
      Sometimes, it is necessary to customise a batch process to collect those
      statistics at the appropriate point.

      My apologies to everyone for the following shameless self-publicity!
      But....
      Questions 2 and 3, are dealt with in my book. Some information relating to
      these questions is in the free chapter that you can download from
      www.psftdba.com. For the information on PeopleSoft Performance Monitor,
      you need to buy the book to read chapter 10 (sorry, but I have to fund my
      retirement somehow), but there are also some presentations on PPM on
      www.go-faster.co.uk.

      You mentioned 'concurrent requests'. If you are coming from an Oracle
      E-Business Suite environment you will be used to the Concurrent Manager.
      Its equivalent in PeopleSoft is the Process Scheduler. Every scheduled
      process has a request record on PSPRCSRQST, and most of them also write to
      PS_MESSAGE_LOG. You can query these tables to find out about the
      performance of a process.
      I understand that in E-Business Suite it is not supported by Oracle to
      collect statistics except through a supplied PL/SQL package, and this
      package can be executed by the concurrent manager. There is nothing
      equivalent

      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 SuRaj Sharma
      >Sent: Sunday, November 16, 2008 11:49 AM
      >To: psftdba@yahoogroups.com
      >Subject: PeopleSoft DBA Forum Peoplesoft and oracle
      >
      >Hi,
      >
      >I am quite new in Peoplesoft, I would really appreciate if
      >someone rever back to my queries:
      >
      >1. How do we analyze objects in peoplesoft (eg. analyze object...
      >compute statistics). Is it through some job/concurrent
      >requests. How to monitor whether this job is running OR not
      >from Oracle end (some peoplesoft internal tables etc)?
      >
      >2. How to check top 10 long runnigng concurrent requests
      >(batch jobs) from Oracle end (some peoplesoft internal tables etc)?
      >
      >3. What are the other metrics from Peoplesoft end can be
      >monitored for middle Tier performance.
      >
      >Suraj Sharma
      >
      >
      >------------------------------------
      >
      >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
      >
      >
      >
    • SuRaj Sharma
      Thanks a lot David for you reply. I have purchsed your book. Now going through the same. One small questions though, On page 232 of this book you have a script
      Message 2 of 5 , Nov 18, 2008
        Thanks a lot David for you reply. I have purchsed your book. Now going through the same. One small questions though, On page 232 of this book you have a script "top10logqry.sql" (top 1o queries in the last 7 days) and you are using psqryexeclog table to get that information. Does this table give the same information with V$QLSTAT of Oracle give or something else?

         
        SuRaj Sharma
        __________________________________________
        Phone:     +91-120-6627000
        Direct:      +91-120-6627445
        Mobile:    +91-9871779068
        Email      : suraj.sharma@... 



        From: David Kurtz <david.kurtz@...>
        To: psftdba@yahoogroups.com
        Sent: Monday, November 17, 2008 3:07:32 AM
        Subject: RE: PeopleSoft DBA Forum Peoplesoft and oracle

        There isn't much in PeopleSoft that manages the database. The main issue
        for the DBA is that all DDL changes to the SYSADM schema (where the
        PeopleSoft objects are usually located) must be managed by the PeopleSoft
        Application Designer. So, the DBA cannot, for example, just create an
        index. It should also be defined in App Desginer.

        Dealing with statistics, some Application Engine processes use the
        PeopleCode %UpdateStats macro to gather statistics on specific working
        storage tables during a batch process. This behaviour is also hard coded
        into some Cobol processes.

        However, if you mean how should you generally manage statistics for a
        PeopleSoft system, then I think that is open to some discussion, and is also
        dependent on the version of the database (there are lots of other people on
        this list who have to manage PeopleSoft systems on a day-to-day basis). My
        experience is that most PeopleSoft systems on Oracle 10g that I have seen,
        simply rely on the automatic job that runs in the default maintenance window
        (10pm to 6am and all day at weekened). This is generally good enough for
        most situations. However, sometimes you need to step in and collect
        statistics differently and/or at different times for some tables.
        Sometimes, it is necessary to customise a batch process to collect those
        statistics at the appropriate point.

        My apologies to everyone for the following shameless self-publicity!
        But....
        Questions 2 and 3, are dealt with in my book. Some information relating to
        these questions is in the free chapter that you can download from
        www.psftdba. com. For the information on PeopleSoft Performance Monitor,
        you need to buy the book to read chapter 10 (sorry, but I have to fund my
        retirement somehow), but there are also some presentations on PPM on
        www.go-faster. co.uk.

        You mentioned 'concurrent requests'. If you are coming from an Oracle
        E-Business Suite environment you will be used to the Concurrent Manager.
        Its equivalent in PeopleSoft is the Process Scheduler. Every scheduled
        process has a request record on PSPRCSRQST, and most of them also write to
        PS_MESSAGE_LOG. You can query these tables to find out about the
        performance of a process.
        I understand that in E-Business Suite it is not supported by Oracle to
        collect statistics except through a supplied PL/SQL package, and this
        package can be executed by the concurrent manager. There is nothing
        equivalent

        regards
        ____________ _________ ____
        David Kurtz
        Go-Faster Consultancy Ltd.
        tel: +44 (0)7771 760660
        fax: +44 (0)7092 348865
        mailto:david.kurtz@ go-faster. co.uk
        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 SuRaj Sharma
        >Sent: Sunday, November 16, 2008 11:49 AM
        >To:
        target=_blank rel=nofollow ymailto="mailto:psftdba%40yahoogroups.com">psftdba@yahoogroups .com
        >Subject: PeopleSoft DBA Forum Peoplesoft and oracle
        >
        >Hi,
        >
        >I am quite new in Peoplesoft, I would really appreciate if
        >someone rever back to my queries:
        >
        >1. How do we analyze objects in peoplesoft (eg. analyze object...
        >compute statistics). Is it through some job/concurrent
        >requests. How to monitor whether this job is running OR not
        >from Oracle end (some peoplesoft internal tables etc)?
        >
        >2. How to check top 10 long runnigng concurrent requests
        >(batch jobs) from Oracle end (some peoplesoft internal tables etc)?
        >
        >3. What are the other metrics from Peoplesoft end can be
        >monitored for middle Tier performance.
        >
        >Suraj Sharma
        >
        >
        >----------- --------- --------- -------
        >
        >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
        Not really. PSQRYLOGEXEC has one row per execution of a PS/Query. A PS/Query is a specific ad-hoc query that is defined with a PeopleSoft tool or via the
        Message 3 of 5 , Nov 18, 2008
          Not really.  PSQRYLOGEXEC has one row per execution of a PS/Query.  A PS/Query is a specific ad-hoc query that is defined with a PeopleSoft tool or via the PIA.  The V$SQL views tell you about all the SQL submitted to the database that is still in the library cache.
           
          If you could match rows in the two, which would not be easy, you might get similar information for just the PS/Queries.  I have never tried.

          regards
          _________________________
          David Kurtzn
          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 SuRaj Sharma
          Sent: Tuesday, November 18, 2008 12:27 PM
          To: psftdba@yahoogroups.com
          Subject: Re: PeopleSoft DBA Forum Peoplesoft and oracle

          Thanks a lot David for you reply. I have purchsed your book. Now going through the same. One small questions though, On page 232 of this book you have a script "top10logqry.sql" (top 1o queries in the last 7 days) and you are using psqryexeclog table to get that information. Does this table give the same information with V$QLSTAT of Oracle give or something else?

           
          SuRaj Sharma
          __________________________________________
          Phone:     +91-120-6627000
          Direct:      +91-120-6627445
          Mobile:    +91-9871779068
          Email      : suraj.sharma@... 



          From: David Kurtz <david.kurtz@...>
          To: psftdba@yahoogroups.com
          Sent: Monday, November 17, 2008 3:07:32 AM
          Subject: RE: PeopleSoft DBA Forum Peoplesoft and oracle

          There isn't much in PeopleSoft that manages the database. The main issue
          for the DBA is that all DDL changes to the SYSADM schema (where the
          PeopleSoft objects are usually located) must be managed by the PeopleSoft
          Application Designer. So, the DBA cannot, for example, just create an
          index. It should also be defined in App Desginer.

          Dealing with statistics, some Application Engine processes use the
          PeopleCode %UpdateStats macro to gather statistics on specific working
          storage tables during a batch process. This behaviour is also hard coded
          into some Cobol processes.

          However, if you mean how should you generally manage statistics for a
          PeopleSoft system, then I think that is open to some discussion, and is also
          dependent on the version of the database (there are lots of other people on
          this list who have to manage PeopleSoft systems on a day-to-day basis). My
          experience is that most PeopleSoft systems on Oracle 10g that I have seen,
          simply rely on the automatic job that runs in the default maintenance window
          (10pm to 6am and all day at weekened). This is generally good enough for
          most situations. However, sometimes you need to step in and collect
          statistics differently and/or at different times for some tables.
          Sometimes, it is necessary to customise a batch process to collect those
          statistics at the appropriate point.

          My apologies to everyone for the following shameless self-publicity!
          But....
          Questions 2 and 3, are dealt with in my book. Some information relating to
          these questions is in the free chapter that you can download from
          www.psftdba. com. For the information on PeopleSoft Performance Monitor,
          you need to buy the book to read chapter 10 (sorry, but I have to fund my
          retirement somehow), but there are also some presentations on PPM on
          www.go-faster. co.uk.

          You mentioned 'concurrent requests'. If you are coming from an Oracle
          E-Business Suite environment you will be used to the Concurrent Manager.
          Its equivalent in PeopleSoft is the Process Scheduler. Every scheduled
          process has a request record on PSPRCSRQST, and most of them also write to
          PS_MESSAGE_LOG. You can query these tables to find out about the
          performance of a process.
          I understand that in E-Business Suite it is not supported by Oracle to
          collect statistics except through a supplied PL/SQL package, and this
          package can be executed by the concurrent manager. There is nothing
          equivalent

          regards
          ____________ _________ ____
          David Kurtz
          Go-Faster Consultancy Ltd.
          tel: +44 (0)7771 760660
          fax: +44 (0)7092 348865
          mailto:david.kurtz@ go-faster. co.uk
          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 SuRaj Sharma
          >Sent: Sunday, November 16, 2008 11:49 AM
          >To: psftdba@yahoogroups .com
          >Subject: PeopleSoft DBA Forum Peoplesoft and oracle
          >
          >Hi,
          >
          >I am quite new in Peoplesoft, I would really appreciate if
          >someone rever back to my queries:
          >
          >1. How do we analyze objects in peoplesoft (eg. analyze object...
          >compute statistics). Is it through some job/concurrent
          >requests. How to monitor whether this job is running OR not
          >from Oracle end (some peoplesoft internal tables etc)?
          >
          >2. How to check top 10 long runnigng concurrent requests
          >(batch jobs) from Oracle end (some peoplesoft internal tables etc)?
          >
          >3. What are the other metrics from Peoplesoft end can be
          >monitored for middle Tier performance.
          >
          >Suraj Sharma
          >
          >
          >----------- --------- --------- -------
          >
          >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
          >
          >
          >


        • kiranmayi -
            Dear Sir thanks a  lot for  providing the basic information on the subject to get some idea i go through it and get back to u for further queries regards
          Message 4 of 5 , Nov 30, 2008
             
            Dear Sir
            thanks a  lot for  providing the basic information on the subject to get some idea
            i go through it and get back to u for further queries
            regards
            kiran
             


            --- On Wed, 11/19/08, David Kurtz <david.kurtz@...> wrote:
            From: David Kurtz <david.kurtz@...>
            Subject: RE: PeopleSoft DBA Forum Peoplesoft and oracle
            To: psftdba@yahoogroups.com
            Date: Wednesday, November 19, 2008, 12:24 PM

            Not really.  PSQRYLOGEXEC has one row per execution of a PS/Query.  A PS/Query is a specific ad-hoc query that is defined with a PeopleSoft tool or via the PIA.  The V$SQL views tell you about all the SQL submitted to the database that is still in the library cache.
             
            If you could match rows in the two, which would not be easy, you might get similar information for just the PS/Queries.  I have never tried.
            regards
            ____________ _________ ____
            David Kurtzn
            Go-Faster Consultancy Ltd.
            tel: +44 (0)7771 760660
            fax: +44 (0)7092 348865
            mailto:david. kurtz@go- faster.co. uk
            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 SuRaj Sharma
            Sent: Tuesday, November 18, 2008 12:27 PM
            To: psftdba@yahoogroups .com
            Subject: Re: PeopleSoft DBA Forum Peoplesoft and oracle

            Thanks a lot David for you reply. I have purchsed your book. Now going through the same. One small questions though, On page 232 of this book you have a script "top10logqry. sql" (top 1o queries in the last 7 days) and you are using psqryexeclog table to get that information. Does this table give the same information with V$QLSTAT of Oracle give or something else?

             
            SuRaj Sharma
            ____________ _________ _________ _________ ___
            Phone:     +91-120-6627000
            Direct:      +91-120-6627445
            Mobile:    +91-9871779068
            Email      : suraj.sharma@ oracle.com 



            From: David Kurtz <david.kurtz@ go-faster. co.uk>
            To: psftdba@yahoogroups .com
            Sent: Monday, November 17, 2008 3:07:32 AM
            Subject: RE: PeopleSoft DBA Forum Peoplesoft and oracle

            There isn't much in PeopleSoft that manages the database. The main issue
            for the DBA is that all DDL changes to the SYSADM schema (where the
            PeopleSoft objects are usually located) must be managed by the PeopleSoft
            Application Designer. So, the DBA cannot, for example, just create an
            index. It should also be defined in App Desginer.

            Dealing with statistics, some Application Engine processes use the
            PeopleCode %UpdateStats macro to gather statistics on specific working
            storage tables during a batch process. This behaviour is also hard coded
            into some Cobol processes.

            However, if you mean how should you generally manage statistics for a
            PeopleSoft system, then I think that is open to some discussion, and is also
            dependent on the version of the database (there are lots of other people on
            this list who have to manage PeopleSoft systems on a day-to-day basis). My
            experience is that most PeopleSoft systems on Oracle 10g that I have seen,
            simply rely on the automatic job that runs in the default maintenance window
            (10pm to 6am and all day at weekened). This is generally good enough for
            most situations. However, sometimes you need to step in and collect
            statistics differently and/or at different times for some tables.
            Sometimes, it is necessary to customise a batch process to collect those
            statistics at the appropriate point.

            My apologies to everyone for the following shameless self-publicity!
            But....
            Questions 2 and 3, are dealt with in my book. Some information relating to
            these questions is in the free chapter that you can download from
            www.psftdba. com. For the information on PeopleSoft Performance Monitor,
            you need to buy the book to read chapter 10 (sorry, but I have to fund my
            retirement somehow), but there are also some presentations on PPM on
            www.go-faster. co.uk.

            You mentioned 'concurrent requests'. If you are coming from an Oracle
            E-Business Suite environment you will be used to the Concurrent Manager.
            Its equivalent in PeopleSoft is the Process Scheduler. Every scheduled
            process has a request record on PSPRCSRQST, and most of them also write to
            PS_MESSAGE_LOG. You can query these tables to find out about the
            performance of a process.
            I understand that in E-Business Suite it is not supported by Oracle to
            collect statistics except through a supplied PL/SQL package, and this
            package can be executed by the concurrent manager. There is nothing
            equivalent

            regards
            ____________ _________ ____
            David Kurtz
            Go-Faster Consultancy Ltd.
            tel: +44 (0)7771 760660
            fax: +44 (0)7092 348865
            mailto:david.kurtz@ go-faster. co.uk
            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 SuRaj Sharma
            >Sent: Sunday, November 16, 2008 11:49 AM
            >To: psftdba@yahoogroups .com
            >Subject: PeopleSoft DBA Forum Peoplesoft and oracle
            >
            >Hi,
            >
            >I am quite new in Peoplesoft, I would really appreciate if
            >someone rever back to my queries:
            >
            >1. How do we analyze objects in peoplesoft (eg. analyze object...
            >compute statistics). Is it through some job/concurrent
            >requests. How to monitor whether this job is running OR not
            >from Oracle end (some peoplesoft internal tables etc)?
            >
            >2. How to check top 10 long runnigng concurrent requests
            >(batch jobs) from Oracle end (some peoplesoft internal tables etc)?
            >
            >3. What are the other metrics from Peoplesoft end can be
            >monitored for middle Tier performance.
            >
            >Suraj Sharma
            >
            >
            >----------- --------- --------- -------
            >
            >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
            >
            >
            >



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