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

Re: PeopleSoft Temp Tables and Collecting Stats

Expand Messages
  • hitechdba
    Friends - What is the best practice regarding STATS and PeopleSoft Temp Tables? We are currently using Oracle 9.2.0.5.0 with HRMS and Oracle 10g Rel 2 with
    Message 1 of 8 , Apr 28, 2006
      Friends -

      What is the best practice regarding STATS and PeopleSoft Temp
      Tables? We are currently using Oracle 9.2.0.5.0 with HRMS and Oracle
      10g Rel 2 with CRM Applications.

      I recently found a scenario where some of the custom [not peoplesoft
      delivered] Temp Tables were pre-seeded with STATS. They pretty much
      salted the catalog based on some estimated values for these Custom
      Temp Tables. STATS are collected once and ignored rest of the time

      But the DbFlag was not disabled [DbFlags=0]
      Also The Meta-SQL %UpdateStats is still being used in these custom
      App Engine Programs.

      I am having difficulty understanding the benefit of Pre-Seeding
      Custom Temp Tables with some values.

      If DbFlags=0 PeopleSoft delivered App Engine replaces the meta-SQL
      construct with platform dependent SQL statement that updates system
      catalog tables used by the database optimizer in choosing optimal
      query plans.

      I have scanned through all the messages but did not find
      discussion on STATS and PeopleSoft Temp Tables and recommended
      best practices.

      Appreciate your feedback on this topic.

      Best,

      Ramesh
    • David Kurtz
      The UK Oracle user group (UKOUUG) conference has been extended to 4 days this year to cater for the additional scope of what is an Oracle product. For this
      Message 2 of 8 , May 1, 2006
        The UK Oracle user group (UKOUUG) conference has been extended to 4 days
        this year to cater for the additional scope of what is an Oracle product.

        For this e-mail I am writing as a chair of a UKOUG SIG - so when I say 'we'
        it is because I am part of the organisation.

        And just because we are a UK group doesn't mean that it is restricted to UK
        presenters or UK customers. It isn't. We get speakers and attendees from
        all over the world.

        So, for those of you on list, this conference is larger that the PeopleSoft
        EMEA conferences at their peak. We are hoping to have a lot of PeopleSoft
        and Fusion material, some technical, some application related. Some will be
        sourced from Oracle, but we want as much as possible to come from end users.

        That means you and your colleages!

        Research indicates that people like to come to user groups and conferences
        to listen to war stories.

        "We did this and it worked" - very usedful
        "We did that and it was a disaster" - even better (in fact this sort of
        presentation gets the best feedback).

        As I understand it, the early part of the conferece will be more database
        orientated, and the later part will be more applications related so you
        don't have to do all 4 days if you just want the PeopleSoft material. Or,
        like me, you can do both database and PeopleSoft material, without too many
        clashes.

        So please go to http://conference.ukoug.org/ and submit your war story.
        Needless to say, if you present you get a free ticket to the conference!




        regards
        _________________________
        David Kurtz
        tel: +44 (0)7771 760660
        fax: +44 (0)7092 348865
        mailto:david.kurtz@...
      • the dragon
        We are running into an issue with our WBI/JMS crap in PT v8.47.04. Everything worked fine in v8.45.08. We think this is going to be a password issue in our
        Message 3 of 8 , May 2, 2006
          We are running into an issue with our WBI/JMS crap in PT v8.47.04.
          Everything worked fine in v8.45.08. We think this is going to be a password
          issue in our intergration gateways file, as the encryption algorithm has
          changed between the two versions. We have an encrypted password in v8.45.08
          that we do no know what the source password is. Does anyone have a method
          to unencrypt a password (I can send it to you if you don't want to tell me
          how to do this) so we can get it reencrypted in the IB for v8.47.04??

          This issue is going to cause a project to fail if we can't get it fixed
          today.

          peace,
          clark



          PSA: Salary <> Slavery. If you earn a salary, your employer is renting your
          services for 40 hours a week, not purchasing your soul. Your time is the
          only real finite asset that you have, and once used it can never be
          recovered, so don't waste it by giving it away.

          I work to live; I don't live to work.

          "Time is the coin of your life. It is the only coin you have, and only you
          can determine how it will be spent. Be careful lest you let other people
          spend it for you."

          Carl Sandburg
          (1878 - 1967)

          _________________________________________________________________
          Express yourself instantly with MSN Messenger! Download today - it's FREE!
          http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
        • negi2u
          I believe first you have to isolate the problem. use the exhibit step to see what is the exact problem .... step-1) check the following things: a) is node is
          Message 4 of 8 , May 3, 2006
            I believe first you have to isolate the problem. use the exhibit step
            to see what is the exact problem ....

            step-1) check the following things:
            a) is node is paused?
            b) is node is inactive?
            c) is message channel is paused?
            d) is message is in inactive status.
            e) Gateway URL? copy the URL in new IE window and check the
            version and other things.
            f) status of the message instance in message monitor.
            g) check whether the message handlers is up and running. you
            could do this by...
            ______________
            c:\>psadmin... Domain Status, Server Status or Domain Status, Queue
            Status
            ______________

            step-2) are you using transformation only AE to publish and subscribe
            the message. if yes, then you have to set the TraceAE flag in
            psappsrv.cfg to 8192 to cornerdown this issue. setting this flag will
            instructs the application server to generate a transformation
            trace... you will have the Original XML structure (before
            transformation) and Output XML structure (after the transformation).
            Read the PS_HOME\appserv\<Domain>\LOGS\<operID>_<machine name>.AET
            trace file to know the exact cause...

            step-3) when you are publishing a message, are you getting any error
            in application server log file.... i.e "appserv\<Domain>\LOGS\
            appsrv.log". if this file records any error it means it's application
            server exception and it should be a configuration issue not
            development issue. you need to againg verify the step-1.

            step-4) open the integrationGateway.properties and set the
            ig.log.level to 5, publish the message and then read the first
            message recorded in ig.messageLog.filename file. ususally
            ig.messagelog.filename is msgLog.html and it reside under
            peoplesoft\application\peoplesoft\psigw\msgLog.html.

            step-5) if you are using the digital certificate then make sure all
            the following setting should be correct...

            ig.certificateAlias
            ig.certificatePasswd
            SecureFileKeystorePath
            secureFileKeystorePasswd

            what password you would like to decrypt, if you really would like to
            decrypt the password used the DJ Java decomplier and decompile all
            the class file reside under the PSIGW directory + class directory
            reside under the PS_HOME....

            FYI: their is one file called KEYSTORE that hold the public key in
            case you are using the digital certificate for messaging...

            cheers,
            manoj

            --- In psftdba@yahoogroups.com, "the dragon" <ceprn@...> wrote:
            >
            > We are running into an issue with our WBI/JMS crap in PT v8.47.04.
            > Everything worked fine in v8.45.08. We think this is going to be a
            password
            > issue in our intergration gateways file, as the encryption
            algorithm has
            > changed between the two versions. We have an encrypted password in
            v8.45.08
            > that we do no know what the source password is. Does anyone have a
            method
            > to unencrypt a password (I can send it to you if you don't want to
            tell me
            > how to do this) so we can get it reencrypted in the IB for
            v8.47.04??
            >
            > This issue is going to cause a project to fail if we can't get it
            fixed
            > today.
            >
            > peace,
            > clark
            >
            >
            >
            > PSA: Salary <> Slavery. If you earn a salary, your employer is
            renting your
            > services for 40 hours a week, not purchasing your soul. Your time
            is the
            > only real finite asset that you have, and once used it can never be
            > recovered, so don't waste it by giving it away.
            >
            > I work to live; I don't live to work.
            >
            > "Time is the coin of your life. It is the only coin you have, and
            only you
            > can determine how it will be spent. Be careful lest you let other
            people
            > spend it for you."
            >
            > Carl Sandburg
            > (1878 - 1967)
            >
            > _________________________________________________________________
            > Express yourself instantly with MSN Messenger! Download today -
            it's FREE!
            > http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
            >
          • negi2u
            if i understand it correctly, then you want to convey... %UpdateStats is not generating the statistics again and again (if generated once).... read the
            Message 5 of 8 , May 3, 2006
              if i understand it correctly, then you want to convey... %UpdateStats
              is not generating the statistics again and again (if generated
              once)....

              read the following extracts from the pdf document...

              "Use of ANALYZE command or DBMS_STATS package alone may result in
              incomplete and wrong statistics. Use of FND_STATS also facilitates
              producing run time details using FND_STATS_HIST table which keeps
              track of the time taken for generating the CBO statistics for
              different types of objects. The feedback from the report can be used
              to schedule the gather stats program at a convenient time."

              so, use FND_STATS:

              EXEC FND_STATS.VERIFY_STATS(schemaname=>'MRP',tablelist=>
              'MRP_SALES_ORDER_UPDATES');

              see the enclosed PDF document.... Files section.... SRNL0801.pdf

              --- In psftdba@yahoogroups.com, "hitechdba" <hitechdba@...> wrote:
              >
              > Friends -
              >
              > What is the best practice regarding STATS and PeopleSoft Temp
              > Tables? We are currently using Oracle 9.2.0.5.0 with HRMS and
              Oracle
              > 10g Rel 2 with CRM Applications.
              >
              > I recently found a scenario where some of the custom [not
              peoplesoft
              > delivered] Temp Tables were pre-seeded with STATS. They pretty
              much
              > salted the catalog based on some estimated values for these Custom
              > Temp Tables. STATS are collected once and ignored rest of the time
              >
              > But the DbFlag was not disabled [DbFlags=0]
              > Also The Meta-SQL %UpdateStats is still being used in these custom
              > App Engine Programs.
              >
              > I am having difficulty understanding the benefit of Pre-Seeding
              > Custom Temp Tables with some values.
              >
              > If DbFlags=0 PeopleSoft delivered App Engine replaces the meta-SQL
              > construct with platform dependent SQL statement that updates system
              > catalog tables used by the database optimizer in choosing optimal
              > query plans.
              >
              > I have scanned through all the messages but did not find
              > discussion on STATS and PeopleSoft Temp Tables and recommended
              > best practices.
              >
              > Appreciate your feedback on this topic.
              >
              > Best,
              >
              > Ramesh
              >
            • David Kurtz
              The point of the %UpdateStats marco, is that it generates statistics on working storage and reporting tables in the processes where they are updated. So you
              Message 6 of 8 , May 3, 2006
                The point of the %UpdateStats marco, is that it generates statistics on
                working storage and reporting tables in the processes where they are
                updated. So you rebuild a table, generate fresh stats to relect the fresh
                data, and the work with it.

                Oracle certainly recommend use of DBMS_STATS instead of ANALYZE. It does
                produce different (better?) statistics and so it does sometimes result in
                different (better?) execution plans. You can change the PeopleSoft DDL
                model to call DBMS_STATS in AE.

                The article you refer to is more about general statistics management, and
                when to use histograms. Especially in PeopleSoft Financials, much of the
                batch processing is status flag driven, and often, a histogram on those flag
                columns is useful. However, I would be cautious. I would only add a
                histogram when I can show that it is producing a better execution plan. I
                would not add histograms to any columns with skewed data in the hope that
                some of them are beneficial, because the ones that are not are adding
                overhead.



                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 negi2u
                > Sent: 03 May 2006 11:29
                > To: psftdba@yahoogroups.com
                > Subject: PeopleSoft DBA Forum Re: PeopleSoft Temp Tables and Collecting
                > Stats
                >
                >
                > if i understand it correctly, then you want to convey... %UpdateStats
                > is not generating the statistics again and again (if generated
                > once)....
                >
                > read the following extracts from the pdf document...
                >
                > "Use of ANALYZE command or DBMS_STATS package alone may result in
                > incomplete and wrong statistics. Use of FND_STATS also facilitates
                > producing run time details using FND_STATS_HIST table which keeps
                > track of the time taken for generating the CBO statistics for
                > different types of objects. The feedback from the report can be used
                > to schedule the gather stats program at a convenient time."
                >
                > so, use FND_STATS:
                >
                > EXEC FND_STATS.VERIFY_STATS(schemaname=>'MRP',tablelist=>
                > 'MRP_SALES_ORDER_UPDATES');
                >
                > see the enclosed PDF document.... Files section.... SRNL0801.pdf
                >
                > --- In psftdba@yahoogroups.com, "hitechdba" <hitechdba@...> wrote:
                > >
                > > Friends -
                > >
                > > What is the best practice regarding STATS and PeopleSoft Temp
                > > Tables? We are currently using Oracle 9.2.0.5.0 with HRMS and
                > Oracle
                > > 10g Rel 2 with CRM Applications.
                > >
                > > I recently found a scenario where some of the custom [not
                > peoplesoft
                > > delivered] Temp Tables were pre-seeded with STATS. They pretty
                > much
                > > salted the catalog based on some estimated values for these Custom
                > > Temp Tables. STATS are collected once and ignored rest of the time
                > >
                > > But the DbFlag was not disabled [DbFlags=0]
                > > Also The Meta-SQL %UpdateStats is still being used in these custom
                > > App Engine Programs.
                > >
                > > I am having difficulty understanding the benefit of Pre-Seeding
                > > Custom Temp Tables with some values.
                > >
                > > If DbFlags=0 PeopleSoft delivered App Engine replaces the meta-SQL
                > > construct with platform dependent SQL statement that updates system
                > > catalog tables used by the database optimizer in choosing optimal
                > > query plans.
                > >
                > > I have scanned through all the messages but did not find
                > > discussion on STATS and PeopleSoft Temp Tables and recommended
                > > best practices.
                > >
                > > Appreciate your feedback on this topic.
                > >
                > > Best,
                > >
                > > Ramesh
                > >
                >
                >
                >
                >
                >
                >
                >
                > 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
              • Ramesh Chamala
                Thanks!! %UpdateStats is in-fact generating the STATS when the program is run. But an Oracle consultant came in and Pre-Populated dummy seed data for these
                Message 7 of 8 , May 3, 2006
                  Thanks!!
                   
                  %UpdateStats is in-fact generating the STATS when the program is run.
                   
                  But an Oracle consultant came in and Pre-Populated "dummy" seed data for these Temp Tables with some values, and updated Stats once manually.  Is this an accepted practice? What are the pros and cons of this practice.

                  Well let me re-phrase my questions:
                   
                  1. PeopleSoft Temp Tables are empty most of the time.  Is it necessary to gather STATS on PeopleSoft Temp Tables?  Yes or No:  Why?
                   
                  2. Temp Tables are constantly populated with data and deleted, over and over.  It leaves the High Water Mark and produces full table scans.  Is it OK to truncate Temp tables on a regular basis?  Yes or No: Why and how often
                   
                  Appreciate your feedback.
                   
                  Regards,
                  negi2u <negi2u@...> wrote:
                  if i understand it correctly, then you want to convey... %UpdateStats
                  is not generating the statistics again and again (if generated
                  once)....

                  read the following extracts from the pdf document...

                  "Use of ANALYZE command or DBMS_STATS package alone may result in
                  incomplete and wrong statistics. Use of FND_STATS also facilitates
                  producing run time details using FND_STATS_HIST table which keeps
                  track of the time taken for generating the CBO statistics for
                  different types of objects. The feedback from the report can be used
                  to schedule the gather stats program at a convenient time."

                  so, use FND_STATS:

                  EXEC FND_STATS.VERIFY_STATS(schemaname=>'MRP',tablelist=>
                  'MRP_SALES_ORDER_UPDATES');

                  see the enclosed PDF document.... Files section.... SRNL0801.pdf

                  --- In psftdba@yahoogroups.com, "hitechdba" <hitechdba@...> wrote:
                  >
                  > Friends -
                  >
                  > What is the best practice regarding STATS and PeopleSoft Temp
                  > Tables?  We are currently using Oracle 9.2.0.5.0 with HRMS and
                  Oracle
                  > 10g Rel 2 with CRM Applications.
                  >
                  > I recently found a scenario where some of the custom [not
                  peoplesoft
                  > delivered] Temp Tables were pre-seeded with STATS.  They pretty
                  much
                  > salted the catalog based on some estimated values for these Custom
                  > Temp Tables.  STATS are collected once and ignored rest of the time
                  >
                  > But the DbFlag was not disabled [DbFlags=0] 
                  > Also The Meta-SQL %UpdateStats is still being used in these custom
                  > App Engine Programs.
                  >
                  > I am having difficulty understanding the benefit of Pre-Seeding
                  > Custom Temp Tables with some values.
                  >
                  > If DbFlags=0 PeopleSoft delivered App Engine replaces the meta-SQL
                  > construct with platform dependent SQL statement that updates system
                  > catalog tables used by the database optimizer in choosing optimal
                  > query plans.
                  >
                  > I have scanned through all the messages but did not find 
                  > discussion on STATS and PeopleSoft Temp Tables and recommended
                  > best practices.
                  >
                  > Appreciate your feedback on this topic.
                  >
                  > Best,
                  >
                  > Ramesh
                  >







                  Yahoo! Messenger with Voice. PC-to-Phone calls for ridiculously low rates.

                • Robert Ellis
                  1. PeopleSoft Temp Tables are empty most of the time. Is it necessary to gather STATS on PeopleSoft Temp Tables? Yes or No: Why? Yes, depending on their
                  Message 8 of 8 , May 3, 2006

                    1. PeopleSoft Temp Tables are empty most of the time.  Is it necessary to gather STATS on PeopleSoft Temp Tables?  Yes or No:  Why?

                     

                    Yes, depending on their size and purpose.  The execution plan for an sql statement in the middle of an app engine will be better for having stats on some of these tables.

                     

                    2. Temp Tables are constantly populated with data and deleted, over and over.  It leaves the High Water Mark and produces full table scans.  Is it OK to truncate Temp tables on a regular basis?  Yes or No: Why and how often

                     

                    Yes.  For the reason you mentioned.  At the end of the successful process.

                     

                    As for the Oracle consultant poking stats that is OK too – and sometimes (often)  necessary in order to get the cbo to behave how you want it to.

                     

                     

                     

                    Robert Ellis

                    PSE Data Security GmbH

                    http://www.psedatasecurity.com


                    From: Ramesh Chamala [mailto:hitechdba@...]
                    Sent: 03 May 2006 13:55
                    To: psftdba@yahoogroups.com
                    Subject: Re: PeopleSoft DBA Forum Re: PeopleSoft Temp Tables and Collecting Stats

                     

                    Thanks!!

                     

                    %UpdateStats is in-fact generating the STATS when the program is run.

                     

                    But an Oracle consultant came in and Pre-Populated "dummy" seed data for these Temp Tables with some values, and updated Stats once manually.  Is this an accepted practice? What are the pros and cons of this practice.


                    Well let me re-phrase my questions:

                     

                    1. PeopleSoft Temp Tables are empty most of the time.  Is it necessary to gather STATS on PeopleSoft Temp Tables?  Yes or No:  Why?

                     

                    2. Temp Tables are constantly populated with data and deleted, over and over.  It leaves the High Water Mark and produces full table scans.  Is it OK to truncate Temp tables on a regular basis?  Yes or No: Why and how often

                     

                    Appreciate your feedback.

                     

                    Regards,
                    negi2u <negi2u@...> wrote:

                    if i understand it correctly, then you want to convey... %UpdateStats
                    is not generating the statistics again and again (if generated
                    once)....

                    read the following extracts from the pdf document...

                    "Use of ANALYZE command or DBMS_STATS package alone may result in
                    incomplete and wrong statistics. Use of FND_STATS also facilitates
                    producing run time details using FND_STATS_HIST table which keeps
                    track of the time taken for generating the CBO statistics for
                    different types of objects. The feedback from the report can be used
                    to schedule the gather stats program at a convenient time."

                    so, use FND_STATS:

                    EXEC FND_STATS.VERIFY_STATS(schemaname=>'MRP',tablelist=>
                    'MRP_SALES_ORDER_UPDATES');

                    see the enclosed PDF document.... Files section.... SRNL0801.pdf

                    --- In psftdba@yahoogroups.com , "hitechdba" <hitechdba@...> wrote:
                    >
                    > Friends -
                    >
                    > What is the best practice regarding STATS and PeopleSoft Temp
                    > Tables?  We are currently using Oracle 9.2.0.5.0 with HRMS and
                    Oracle
                    > 10g Rel 2 with CRM Applications.
                    >
                    > I recently found a scenario where some of the custom [not
                    peoplesoft
                    > delivered] Temp Tables were pre-seeded with STATS.  They pretty
                    much
                    > salted the catalog based on some estimated values for these Custom
                    > Temp Tables.  STATS are collected once and ignored rest of the time
                    >
                    > But the DbFlag was not disabled [DbFlags=0] 
                    > Also The Meta-SQL %UpdateStats is still being used in these custom
                    > App Engine Programs.
                    >
                    > I am having difficulty understanding the benefit of Pre-Seeding
                    > Custom Temp Tables with some values.
                    >
                    > If DbFlags=0 PeopleSoft delivered App Engine replaces the meta-SQL
                    > construct with platform dependent SQL statement that updates system
                    > catalog tables used by the database optimizer in choosing optimal
                    > query plans.
                    >
                    > I have scanned through all the messages but did not find 
                    > discussion on STATS and PeopleSoft Temp Tables and recommended
                    > best practices.
                    >
                    > Appreciate your feedback on this topic.
                    >
                    > Best,
                    >
                    > Ramesh
                    >




                     


                    Yahoo! Messenger with Voice. PC-to-Phone calls for ridiculously low rates.

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