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

RE: PeopleSoft DBA Forum Oracle optimizer stability

Expand Messages
  • Jeremy Haight
    I have come across this before once... Are you working at WF? If you know what the innitials are then I know what you are working and I may know the
    Message 1 of 5 , Dec 18, 2003
    • 0 Attachment
      I have come across this before once... Are you working at WF? If you know
      what the innitials are then I know what you are working and I may know the
      problem... Otherwise I may not be able to assist you.

      Thanks,

      Jeremy

      >From: "litbighor" <lehuyluan@...>
      >Reply-To: psftdba@yahoogroups.com
      >To: psftdba@yahoogroups.com
      >Subject: PeopleSoft DBA Forum Oracle optimizer stability
      >Date: Thu, 18 Dec 2003 10:38:03 -0000
      >
      >Hi,
      >
      >I find interesting the use of Stored Outlines with Oracle
      >I exported the stats from a 18 Go HRMS 8.3 database
      >(dbms_stats.export_schema_stats of SYSADM)
      >It took 12 hours and the stat table has about 240000 rows !
      >
      >Stored outlines look great for optimizer stability and tuning
      >execution plan when we can't put hints in the code but I fear for the
      >performance impact.
      >
      >Does anyone have experience with this feature ?
      >
      >Thanks.
      >
      >
      >

      _________________________________________________________________
      Grab our best dial-up Internet access offer: 6 months @$9.95/month.
      http://join.msn.com/?page=dept/dialup
    • litbighor
      I don t know what is WF. I don t have problem, I think may be I ll have to use them soon The only problem is exporting stat takes a long time... ... you know
      Message 2 of 5 , Dec 19, 2003
      • 0 Attachment
        I don't know what is WF.
        I don't have problem, I think may be I'll have to use them soon
        The only problem is exporting stat takes a long time...

        --- In psftdba@yahoogroups.com, "Jeremy Haight" <jeremy_haight@h...>
        wrote:
        > I have come across this before once... Are you working at WF? If
        you know
        > what the innitials are then I know what you are working and I may
        know the
        > problem... Otherwise I may not be able to assist you.
        >
        > Thanks,
        >
        > Jeremy
        >
        > >From: "litbighor" <lehuyluan@h...>
        > >Reply-To: psftdba@yahoogroups.com
        > >To: psftdba@yahoogroups.com
        > >Subject: PeopleSoft DBA Forum Oracle optimizer stability
        > >Date: Thu, 18 Dec 2003 10:38:03 -0000
        > >
        > >Hi,
        > >
        > >I find interesting the use of Stored Outlines with Oracle
        > >I exported the stats from a 18 Go HRMS 8.3 database
        > >(dbms_stats.export_schema_stats of SYSADM)
        > >It took 12 hours and the stat table has about 240000 rows !
        > >
        > >Stored outlines look great for optimizer stability and tuning
        > >execution plan when we can't put hints in the code but I fear for
        the
        > >performance impact.
        > >
        > >Does anyone have experience with this feature ?
        > >
        > >Thanks.
        > >
        > >
        > >
        >
        > _________________________________________________________________
        > Grab our best dial-up Internet access offer: 6 months
        @$9.95/month.
        > http://join.msn.com/?page=dept/dialup
      • David Kurtz
        I have always avoided using stored outlines in conjuction with PeopleSoft. My concern is that there are so many different statements that PeopleSoft
        Message 3 of 5 , Dec 19, 2003
        • 0 Attachment
          I have always avoided using stored outlines in conjuction with PeopleSoft.  My concern is that there are so many different statements that PeopleSoft dynamically generates.  If a stored outline is enabled the database takes every statement that it parses and looks up the hash value of the statement in the outline.  That is going to introduce an overhead on every SQL statement that you submit to the database.
           
          An outline is a collection of hints that are applied to the database in order to force a particular execution plan. 
          Before considering using an outline I would consider
          i) where does the SQL statement come from.  Is it in a place where a developer could alter it, or a part of it.  The answer is not always no.
          ii) Often the optimiser chooses an inappropriate plan because it does not have appropriate information.  Things to consider include histograms, indexes and initialisation parameters (optimizer_index_cost_adj, optimizer_index_caching, hash_join_enable, db_file_multiblock_read_count - see Tim Gormans article about the CBO).

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

          -----Original Message-----
          From: litbighor [mailto:lehuyluan@...]
          Sent: 19 December 2003 16:20
          To: psftdba@yahoogroups.com
          Subject: Re: PeopleSoft DBA Forum Oracle optimizer stability

          I don't know what is WF.
          I don't have problem, I think may be I'll have to use them soon
          The only problem is exporting stat takes a long time...

          --- In psftdba@yahoogroups.com, "Jeremy Haight" <jeremy_haight@h...>
          wrote:
          > I have come across this before once...  Are you working at WF?  If
          you know
          > what the innitials are then I know what you are working and I may
          know the
          > problem...  Otherwise I may not be able to assist you.
          >
          > Thanks,
          >
          > Jeremy
          >
          > >From: "litbighor" <lehuyluan@h...>
          > >Reply-To: psftdba@yahoogroups.com
          > >To: psftdba@yahoogroups.com
          > >Subject: PeopleSoft DBA Forum Oracle optimizer stability
          > >Date: Thu, 18 Dec 2003 10:38:03 -0000
          > >
          > >Hi,
          > >
          > >I  find interesting the use of Stored Outlines with Oracle
          > >I exported the stats from a 18 Go HRMS 8.3 database
          > >(dbms_stats.export_schema_stats of SYSADM)
          > >It took 12 hours and the stat table has about 240000 rows !
          > >
          > >Stored outlines look great for optimizer stability and tuning
          > >execution plan when we can't put hints in the code but I fear for
          the
          > >performance impact.
          > >
          > >Does anyone have experience with this feature ?
          > >
          > >Thanks.
          > >
          > >
          > >
          >
          > _________________________________________________________________
          > Grab our best dial-up Internet access offer: 6 months
          @$9.95/month. 
          > http://join.msn.com/?page=dept/dialup




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

          The PeopleSoft DBA Forum is managed by Go-Faster Consultancy Ltd. http://www.go-faster.co.uk




          Yahoo! Groups Links

        • litbighor
          Thanks for your answer. My understanding is now better. I agree about the parsing cost with PS , it kills the shared pool. And what about cursor_sharing force
          Message 4 of 5 , Dec 22, 2003
          • 0 Attachment
            Thanks for your answer. My understanding is now better.
            I agree about the parsing cost with PS , it kills the shared pool.
            And what about cursor_sharing force with stored outlines ?
            ( I like playing with Oracle :) )
            Will it kill the shared pool ?

            Well in fact my problem is : how can i change the exec plan for
            1 sql from the standards PS without changing instance parameters ?
            For example :
            SEARCH from PeopleTools > Query Manager > Use > Query Manager

            Where can i add hints ?



            --- In psftdba@yahoogroups.com, "David Kurtz" <david.kurtz@g...>
            wrote:
            > I have always avoided using stored outlines in conjuction with
            PeopleSoft.
            > My concern is that there are so many different statements that
            PeopleSoft
            > dynamically generates. If a stored outline is enabled the database
            takes
            > every statement that it parses and looks up the hash value of the
            statement
            > in the outline. That is going to introduce an overhead on every SQL
            > statement that you submit to the database.
            >
            > An outline is a collection of hints that are applied to the
            database in
            > order to force a particular execution plan.
            > Before considering using an outline I would consider
            > i) where does the SQL statement come from. Is it in a place where a
            > developer could alter it, or a part of it. The answer is not
            always no.
            > ii) Often the optimiser chooses an inappropriate plan because it
            does not
            > have appropriate information. Things to consider include
            histograms,
            > indexes and initialisation parameters (optimizer_index_cost_adj,
            > optimizer_index_caching, hash_join_enable,
            db_file_multiblock_read_count -
            > see Tim Gormans article about the CBO).
            > _________________________
            > David Kurtz
            > Go-Faster Consultancy Ltd.
            > tel: +44 (0)7771 760660
            > fax: +44 (0)7092 348865
            > mailto:david.kurtz@g...
            > web: www.go-faster.co.uk
            > PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
            >
            > -----Original Message-----
            > From: litbighor [mailto:lehuyluan@h...]
            > Sent: 19 December 2003 16:20
            > To: psftdba@yahoogroups.com
            > Subject: Re: PeopleSoft DBA Forum Oracle optimizer stability
            >
            >
            > I don't know what is WF.
            > I don't have problem, I think may be I'll have to use them soon
            > The only problem is exporting stat takes a long time...
            >
            > --- In psftdba@yahoogroups.com, "Jeremy Haight"
            <jeremy_haight@h...>
            > wrote:
            > > I have come across this before once... Are you working at WF?
            If
            > you know
            > > what the innitials are then I know what you are working and I
            may
            > know the
            > > problem... Otherwise I may not be able to assist you.
            > >
            > > Thanks,
            > >
            > > Jeremy
            > >
            > > >From: "litbighor" <lehuyluan@h...>
            > > >Reply-To: psftdba@yahoogroups.com
            > > >To: psftdba@yahoogroups.com
            > > >Subject: PeopleSoft DBA Forum Oracle optimizer stability
            > > >Date: Thu, 18 Dec 2003 10:38:03 -0000
            > > >
            > > >Hi,
            > > >
            > > >I find interesting the use of Stored Outlines with Oracle
            > > >I exported the stats from a 18 Go HRMS 8.3 database
            > > >(dbms_stats.export_schema_stats of SYSADM)
            > > >It took 12 hours and the stat table has about 240000 rows !
            > > >
            > > >Stored outlines look great for optimizer stability and tuning
            > > >execution plan when we can't put hints in the code but I fear
            for
            > the
            > > >performance impact.
            > > >
            > > >Does anyone have experience with this feature ?
            > > >
            > > >Thanks.
            > > >
            > > >
            > > >
            > >
            > >
            _________________________________________________________________
            > > Grab our best dial-up Internet access offer: 6 months
            > @$9.95/month.
            > > http://join.msn.com/?page=dept/dialup
            >
            >
            >
            >
            > To unsubscribe from this group, send an email to:
            > psftdba-unsubscribe@yahoogroups.com
            >
            > The PeopleSoft DBA Forum is managed by Go-Faster Consultancy Ltd.
            > http://www.go-faster.co.uk
            >
            >
            >
            > --------------------------------------------------------------------
            --------
            > --
            > Yahoo! Groups Links
            >
            > a.. To visit your group on the web, go to:
            > http://groups.yahoo.com/group/psftdba/
            >
            > b.. To unsubscribe from this group, send an email to:
            > psftdba-unsubscribe@yahoogroups.com
            >
            > c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of
            Service.
          • David Kurtz
            First of all give the optimiser the best information check that the statistics are valid, and consider setting the OPTIMIZER_* parameters (see Tim Gorman s
            Message 5 of 5 , Jan 6, 2004
            • 0 Attachment
              First of all give the optimiser the best information
               
              check that the statistics are valid, and consider setting the OPTIMIZER_* parameters (see Tim Gorman's paper Search for Intelligent Life In the CBO - http://www.evdbt.com/SearchIntelligenceCBO.doc)
               
               
               
              Some instance parameters can be set at session level, and many more can be set in Oracle 9i that 8i.  This brings the possibility to set them just for the PSQRYSRV process using an on-connect trigger.
               
               
               
              There are a number of strategies for getting hints into PS/Query - none of them are great
               
              i) use an expression in place of the first column of the query and put the hint in there along with the column.  The problem is that if the query has the distinct option selected this will put the hint in the wrong place after the DISTINCT, but you could also put the distinct into the expression if you need it.
               
              ii) create query in a view and put the hint in the view.  It depends on your attitude to query.  This requires development involvement, and prevents the query (developer) user from removing or changing the hint.
               
              iii) create a view that returns one row and containts the hint
                  CREATE VIEW PS_CBO_ORDERED AS SELECT /*+ORDERED*/ 'x' FROM DUAL;
              and then add the view to the query.  In my tests on 8i the scope of the hint does not seem to be limited to just the view, it is inherited by the whole query.

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

              -----Original Message-----
              From: litbighor [mailto:lehuyluan@...]
              Sent: 22 December 2003 09:42
              To: psftdba@yahoogroups.com
              Subject: PeopleSoft DBA Forum Re: Oracle optimizer stability

              Thanks for your answer. My understanding is now better.
              I agree about the parsing cost with PS , it kills the shared pool.
              And what about cursor_sharing force with stored outlines ?
              ( I like playing with Oracle :) )
              Will it kill the shared pool ?

              Well in fact my problem is : how can i change the exec plan for
              1 sql from the standards PS without changing instance parameters ?
              For example : 
              SEARCH from  PeopleTools > Query Manager > Use > Query Manager

              Where can i add hints ?



              --- In psftdba@yahoogroups.com, "David Kurtz" <david.kurtz@g...>
              wrote:
              > I have always avoided using stored outlines in conjuction with
              PeopleSoft.
              > My concern is that there are so many different statements that
              PeopleSoft
              > dynamically generates.  If a stored outline is enabled the database
              takes
              > every statement that it parses and looks up the hash value of the
              statement
              > in the outline.  That is going to introduce an overhead on every SQL
              > statement that you submit to the database.
              >
              > An outline is a collection of hints that are applied to the
              database in
              > order to force a particular execution plan.
              > Before considering using an outline I would consider
              > i) where does the SQL statement come from.  Is it in a place where a
              > developer could alter it, or a part of it.  The answer is not
              always no.
              > ii) Often the optimiser chooses an inappropriate plan because it
              does not
              > have appropriate information.  Things to consider include
              histograms,
              > indexes and initialisation parameters (optimizer_index_cost_adj,
              > optimizer_index_caching, hash_join_enable,
              db_file_multiblock_read_count -
              > see Tim Gormans article about the CBO).
              > _________________________
              > David Kurtz
              > Go-Faster Consultancy Ltd.
              > tel: +44 (0)7771 760660
              > fax: +44 (0)7092 348865
              > mailto:david.kurtz@g...
              > web: www.go-faster.co.uk
              > PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
              >
              >   -----Original Message-----
              >   From: litbighor [mailto:lehuyluan@h...]
              >   Sent: 19 December 2003 16:20
              >   To: psftdba@yahoogroups.com
              >   Subject: Re: PeopleSoft DBA Forum Oracle optimizer stability
              >
              >
              >   I don't know what is WF.
              >   I don't have problem, I think may be I'll have to use them soon
              >   The only problem is exporting stat takes a long time...
              >
              >   --- In psftdba@yahoogroups.com, "Jeremy Haight"
              <jeremy_haight@h...>
              >   wrote:
              >   > I have come across this before once...  Are you working at WF? 
              If
              >   you know
              >   > what the innitials are then I know what you are working and I
              may
              >   know the
              >   > problem...  Otherwise I may not be able to assist you.
              >   >
              >   > Thanks,
              >   >
              >   > Jeremy
              >   >
              >   > >From: "litbighor" <lehuyluan@h...>
              >   > >Reply-To: psftdba@yahoogroups.com
              >   > >To: psftdba@yahoogroups.com
              >   > >Subject: PeopleSoft DBA Forum Oracle optimizer stability
              >   > >Date: Thu, 18 Dec 2003 10:38:03 -0000
              >   > >
              >   > >Hi,
              >   > >
              >   > >I  find interesting the use of Stored Outlines with Oracle
              >   > >I exported the stats from a 18 Go HRMS 8.3 database
              >   > >(dbms_stats.export_schema_stats of SYSADM)
              >   > >It took 12 hours and the stat table has about 240000 rows !
              >   > >
              >   > >Stored outlines look great for optimizer stability and tuning
              >   > >execution plan when we can't put hints in the code but I fear
              for
              >   the
              >   > >performance impact.
              >   > >
              >   > >Does anyone have experience with this feature ?
              >   > >
              >   > >Thanks.
              >   > >
              >   > >
              >   > >
              >   >
              >   >
              _________________________________________________________________
              >   > Grab our best dial-up Internet access offer: 6 months
              >   @$9.95/month.
              >   > http://join.msn.com/?page=dept/dialup
              >
              >
              >
              >
              >   To unsubscribe from this group, send an email to:
              >   psftdba-unsubscribe@yahoogroups.com
              >
              >   The PeopleSoft DBA Forum is managed by Go-Faster Consultancy Ltd.
              > http://www.go-faster.co.uk
              >
              >
              >
              > --------------------------------------------------------------------
              --------
              > --
              >   Yahoo! Groups Links
              >
              >     a.. To visit your group on the web, go to:
              >     http://groups.yahoo.com/group/psftdba/
              >
              >     b.. To unsubscribe from this group, send an email to:
              >     psftdba-unsubscribe@yahoogroups.com
              >
              >     c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of
              Service.




              The PeopleSoft DBA Forum is managed by Go-Faster Consultancy Ltd. http://www.go-faster.co.ukeopleSoft DBA Forum is managed by Go-Faster Consultancy Ltd. http://www.go-faster.co.uk



              Yahoo! Groups Links

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