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

Re: PeopleSoft DBA Forum Shared Pool and Cursor_Sharing

Expand Messages
  • rsalido@yahoo.com
    Thanks. Whats the size of your shared pool? In our case its 128M and 160M for buffer cache. We did have an incident were the database had to be bounced because
    Message 1 of 10 , Oct 17, 2005
    • 0 Attachment

      Thanks. Whats the size of your shared pool? In our case its 128M and 160M for buffer cache. We did have an incident were the database had to be bounced because of ORA- errors.
       
      Roberto.

      R P <pjksel@...> wrote:
      Hi,
       
      I use cursor_sharing=force parameter currently in our 9i (9205) databases. It works much better in 9i than it did in 8i.  Peoplesoft uses both bind variables and literals in the application. I have never had a problem with shared pool fragmentation resulting in ORA- errors.
      Using cursor_sharing=force can cause some queries to perform poorly as they might not be able to use histogram information properly.
      Thanks,
      Richard

      rsalido@... wrote:
      Hello,
          I have been using statspack to monitor our database and I am a bit concerned about what I see. (Please see attached file).
          The memory usage seems a little high. Also, the Soft Parse % seems very low which might be an indicator of excessive hard parsing due to a bind variable problem.
       
      1.- What percentage of you shared pool memory do you see your systems using?
      2.- Do you use cursor_sharing=force?
      3.- Does PeopleSoft have a bind variable usage problem? I see a lot of SQL using literals out there.
       
      Thanks a lot.
       
       


      Yahoo! Music Unlimited - Access over 1 million songs. Try it free. {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fcharset0 Arial;}}
      \viewkind4\uc1\pard\f0\fs20{\pict\wmetafile8\picw14153\pich4867\picwgoal8024\pichgoal2759
      010009000003184202000000024202000000050000000b0200000000050000000c020313493702
      420200430f2000cc000000b80017020000000003134937000000002800000017020000b8000000
      0100180000000000c0830400c40e0000c40e00000000000000000000ffffffffffffffffffffff
      fffffffffffffffffffffffffffffffffffffffffbfffffffffffffff7f7f7ffffffffffffffff
      fff7f3f7fffffffffffff7f7f7fffbfffffffffffbfff7f7f7fffffffffffff7f3f7fffffffffb
      fffffbfffffbfffffbfffffbfff7f7f7fffffffffffff7f3f7fffffffffbfffffffffffffff7f7
      f7fffffffffffffffbfff7f3f7fffffffffffffffbfffffffffffbfffffbfffffffffffbffffff
      fffffffffffffffffffffffbfffffffffffbfffffffffffbfffffffffffbfffffffffffbfffffb
      fffffbfffffbfffffffff7f7f7fffffffffffffffbffffffffffffffefefefffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffefefeffffffffffffff7f3f7fffffffffb
      fffffffffffffffffffffffffffffbfffffffffffbfffffffffffbfffffbfffffbfff7f7f7ffff
      fff7f7f7fffffffffffff7f7f7ffffffffffffffffffffffffffffffffffffffffffffffffffff
      fff7f7f7f ffffffffbfff7f7f7fffffffffbfffffbfffffbfffffbfffffffffffffffffffffffb
      fff7f7f7fffffffffffffffbfffffbfffffffffffffffffffffffffffffbffffffffffffffffff
      fffffffffffffffffffffffffffffffffffffffffbfff7f7f7fffbfffffbfffffffff7f3f7ffff
      fffffffffffffff7f7f7ffffffefefeffffffff7f3f7fffffffffffffffbfffffffff7f7f7ffff
      fffffffffffbfffffffff7f3f7fffffff7f7f7fffffffffffffffbfffffbfffffffffffffffffb
      fffffffff7f7f7fffffffffffffffbfffffffffffbffffffffffffffffffffefefefffffffffff
      fffffffffffffffffbfffffffffffbfffffffffffbfffffbfffffffffffbffffffffffffffffff
      fff7f7f7fffffff7f3f7ffffffffffffffffffffffffefefeffffffffffffff7f7f7ffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffbfff7f7
      f7fffffff7f7f7fffffff7f7f7fffffffffffffffbfff7f7f7fffffff7f7f7fffffff7f7f7ffff
      fffffbfffffffff7f7f7fffffffffbfffffffffffbfffffffffffffffffffffffffffffbffffff
      fffffbfffffffffffbffff f ffffffffffffbfff7f3f7fffffff7f7f7fffbffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffbfff7f7f7ffff
      fff7f7f7fffffff7f7f7ffffffffffffefefeffffffffffffff7f3f7fffffffffbfffffffffffb
      fffffffff7f7f7fffffffffbfffffffffffbfffffffffffffffffbfffffffff7f7f7fffffffffb
      fffffffffffbfffffffffffffffffffffffbfffffffffffbfffffffffffbffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffff f fffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff000000ffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffe7e7e7ffffffffffffefebefffff
      fffffbffffffffefefeffffffff7f3f7efebeffffffffffffff7f7f7fffffffffffffffbfffffb
      fff7f7f7fffffffffffffffffffffffffffffffffffffffffffffffffffffffffbfffffbffffff
      fffffffffffbfff7f7f7fffffffffffffffffffffffff7f7f7fffffffffbfffffffffffbfffffb
      fffffffffffbfffffffff7f7f7f7f7f7fffffffffffff7f7f7 f ffffff7f7f7fffffffffffff7f7
      f7fffffffffbfffffffffffffff7f7f7fffbffffffffffffffefebeffffffffffffff7f3f7ffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      fffffffffffbfffffbfffffbfff7f7f7f7f7f7fffffffffffff7f7f7fffffff7f7f7ffffffffff
      fffffffffffffffffffff7f7f7fffbfffffbffffffffffffffffffffffffffffffffffffffffff
      fffffffffffffffffbffefefeffffffffffbffffffffefefeffffffffffffffffffffffbfff7f7
      f7fffffff7f7f7ffffffffffffefefeff7f7f7fffbfffffffffffbfffffbfffffbfffffffff7f3
      f7ffffffffffffffffffffffffffffffffffffffffffffffffffffffefebefffffffffffffffff
      fff7f3f7ffffffffffffffffffefefeffffbfffffffffffffffffbfffffbfff7f7f7f7f3f7ffff
      fffffbfff7f7f7fffbfffffffffffffffffbfffffffff7f3f7fffffffffbfffffbffffffffffff
      fffffffff7f3f7fffffffffbfff7f7f7fffbfffffffffffffffffbfffffffff7f7f7ffffffffff
      fffffffff7f3f7fffffffffbfffffbfffffbfffffbfffffffffffffffffffffffffff7f7f7ffff
      ffefebeffffbfffffffff7f7f7fffbfffffffffffbfff7f7f7f7f7f7ffffffff f fffffffffffff
      fffffffffffbffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      fffffbfffffffffffffffffffff7f7f7fffffffffffff7f7f7fffbfffffffffffffffffffff7f7
      f7fffffffffffff7f7f7fffffffffffffffffffffffffffbfffffffffffffffffffff7f7f7f7f7
      f7fffffffffffff7f7f7fffffff7f7f7fffffffffbfffffbfffffffffffffff7f3f7ffffffffff
      ffefebefffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffb
      fffffffffffffffffffff7f7f7fffffffffffff7f7f7fffffffffffffffffffffffffffffffffb
      fffffbfffffbfffffffffffffffffffffffffffffbfffffffffffffffffffff7f7f7fffffff7f7
      f7f7f7f7fffffffffbfffffbfffffffff7f7f7f7f7f7fffffffffffff7f7f7fffffff7f7f7ffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff < BR>ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      fffffff f000 000fffffffffffffffffffffffffffffffffffffffffffffffffffbfffffffff7f7
      f7f7f7f7fffffffffffffffffff7f3f7fffffff7f7f7fffffffffffffffffffffffffffffff7f7
      f7fffffff7f3f7fffffffffffffffbffefefefefefeffffbffefebeffffffffffbfff7f7f7efeb
      effffffffffffffffffff7f3f7fffffffffffff7f3f7fffffffffbfffffbfffffbfffffbfff7f7
      f7fffbfffffffffffffffffbfff7f7f7fffbfffffffffffffffffffff7f3f7fffffff7f7f7ffff
      fff7f7f7f7f7f7fffffff7f7f7f7f7f7ffffffefebeffffffffffffffffbfff7f7f7ffffffffff
      fffffffffffffffffffff7f3f7ffffffffffffffffffffffffffffffffffffffffffffffffefef
      effffbfffffffff7f3f7f7f7f7fffffffffffff7f7f7fffffffffffffffffff7f3f7fffffff7f7
      f7fffffff7f7f7ffffffefefefefefeffffbfffffffffffffff7f3f7ffffffffffffffffffffff
      fffffffffffffffffffffffffffffffffffffffffffffffbfff7f3f7f7f3f7fffffffffffffffb
      fffffbfffffffffffffff7f3f7fffbfffffffffffffffffffffffffffffffffffbffffffffefeb
      effffffffffbffffffffffffffffffffffffffffffffffffffffffffffffffffffffefefefffff
      fff7f7f7fffbfffffbfffffb f ffffffff7f7f7fffbfffffffffffbfff7f7f7f7f7f7f7f3f7ffff
      fffffffffffffff7f7f7ffffffffffffffffffefebeffffffff7f7f7f7f3f7fffffffffbffffff
      ffffffffefefeffffffff7f7f7fffffff7f7f7ffffffffffffffffffefebeffffffff7f7f7fffb
      fffffffffffbffefefeff7f7f7ffffffefefeffffffff7f7f7ffffffefebeff7f7f7ffffffffff
      fffffbfffffffffffffffffbfffffffffffbfffffffffffffffffffffffbffffffffffffffffff
      fffffbfff7f3f7f7f7f7fffbffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffe7e7e7fffbfffffffffffffff7f7f7ffffffffffffffff
      ffe7e7e7fffbfffffffffffffff7f7f7fffffffffffffffffff7f3f7f7f7f7fffffffffbffefef
      effffffffffffffffffffffffff7f3f7fffffff7f7f7fffffff7f7f7fffffff7f3f7ffffffefef
      effffffffffbfffffbffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffe7e7e7fffbff f ffffffffffff7f7f7ffffffefefeffffbffffff
      fff7f3f7f7f7f7fffffffffffff7f7f7fffffffffffff7f3f7f7f7f7fffffffffbffefefefffff
      fffffffffffffffffffff7f7f7f7f7f7fffbfff7f7f7fffffffffffffffffffffffff7f3f7ffff
      fff7f7f7fffffff7f7f7ffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffff f fffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffff000000ffffffffffffffffffffffffffffffffffffffffffffff
      fffffbfffffffff7f7f7ffffffffffffefebefffffffffffffffffffe7e7e7fffffff7f3f7ffff
      fffffffff7f7f7fffffffffffffffffffffbfff7f3f7fffbfffffffffffffffffffffffffffffb
      fffffffff7f7f7fffffffffbfffffbfffffffffffffffffffff7f7f7fffffffffffffffbffefeb
      effffffffffffffffffffffffff7f7f7f7f7f7fffffffffffffffffff7f3f7fffffffffffffffb
      fffffbffffffffefebeffffffffffbfffffffff7f7f7f7f7f7f7f7f7fffffffffbff0000000804
      08000000fffffff7f7f7f7f3f7fffffffffbffffffffffffffffffffffffffffffffffffffffff
      fffffffffffffffffffffffffffffbfffffffffffffffffbfff7f7f7fffffff7f3 f 7ffffffffff
      fffffbfffffbffffffffefebeffffffffffffffffffffffffffffbfff7f3f7fffbffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffefebeffffbffffffffffffffffff
      fff7f7f7f7f7f7fffffff7f7f7fffffff7f7f7f7f7f7ffffffffffffefefeffffffff7f7f7f7f3
      f7fffbfffffffffffffffffffffffbffffffffffffffffffffffffffffffffffffffffffffffff
      fffffffffffffffffbfffffffffffffffffbfffffffffffffffffbfffffffff7f3f7f7f7f7ffff
      fffffffffffffffffbfff7f7f7fffbffffffffefefefefefeffffbfffffffffffbffffffffffff
      ffffffffefefefffffffefebeffffffff7f7f7fffffffffbffffffffefefefefefeffffbffffff
      fffffbfffffffff7f3f7fffffffffffffffffffffffffffffff7f3f7f7f7f7fffffffffbffffff
      fffffffffffbfff7f3f7efebeffffffffffffffffbffffffffffffffefebeffffffffffffff7f7
      f7fffffff7f7f7fffffffffffffffbfffffffffffbffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      fffffffffffffffffffffffffffffffffffffff7f3f7fffffffffffffffffffffffff7f7f7fffbfffffbfff7f3f7fffffffffffffffffffffffff7f7f7fffbfffffbfffffbfff7f7f7ffffffffff
      fff7f3f7f7f7f7fffffffffbfff7f3f7fffffffffffffffbfffffbffffffffefebefffffffffff
      fffffffffffbfffffffffffffffffbfff7f3f7fffbffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      fffffffffffffffffffffffffffffffff7f3f7fffffffffffffffffffffffff7f7f7fffbfffffb
      fffffffffffffffffbfffffffffffffffffbfff7f7f7fffffffffbfff7f7f7fffffffffffff7f3
      f7f7f7f7fffffffffbfff7f7f7efefeffffffffffffffffbfffffffff7f7f7fffbfff7f3f7ffff
      fffffffffffbfffffbffffffffefebefffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffff f fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffff000000ffffffffffffffffffffffffffff
      fffffffffffffffffffff7f7f7ffffffffffffefebeffffffffffffff7f3f7ffffffffffffffff
      fffffffffffbfffffffffffbfffffffff7f3f7f7f7f7fffffffffbfffffffffffffff7f3f7f7f3
      f7ffffffefefefffffffffffff f ffffff7f3f7fffffffffbfffffffffffbfffffbfffffbffefef
      effffffffffffffffffffffffffffffffffffffffbfffffffffffffffffbffffffffffffffffff
      fff7f3f7fffffff7f7f7fffbfffffbfffffffff7f7f7fffffff7f3f7fffffffffffffffffff7f3
      f7fffbfffffbfff7f7f7080408000000fffffffffffffffbfff7f3f7ffffffffffffffffffffff
      fffffffffffffffffffffffffffffffff7f7f7fffffffffffffffbfff7f3f7fffffffffffffffb
      fffffffff7f3f7fffffff7f7f7fffbfffffbfffffffff7f7f7fffffff7f3f7efefefffffffffff
      fffffffffffffff7f7f7fffffffffffffffffffffffffffffffffffffffffffffffffffffff7f7
      f7fffffff7f3f7f7f7f7ffffffefefeff7f7f7f7f7f7ffffffffffffffffffffffff0000000000
      00fffffffffffffffffff7f7f7ffffffefefeffffbfffffffff7f3f7ffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffefebeffffffffffffff7f7f7fffbffffffff000000ffff
      fffffffffffffff7f7f7fffffffffffff7f7f7fffffffffbfffffbfffffffff7f3f7fffffff7f3
      f7efebeffffffffffbfffffffffffffffffffffffffffffbfffffffffffbfffffbfffffbffffff
      fff7f3f7fffffff7f3f7efebeffffffffffffff7 f 3f7fffffffffbffffffffefefefffffffffff
      fff7f7f7f7f3f7fffffffffffffffffffffffffffffff7f3f7f7f3f7fffbffffffffefefefffff
      fffffbfff7f7f7fffffffffffffffffffffffffffbfffffffffffffff7f3f7ffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffefefefffffffffff
      fffffbfffffbffffffffffffffffffffefefeffffffffffffffffbfffffbffffffffffffffffff
      fffffffff7f7f7fffbfffffffffffffff7f3f7fffffffffffff7f3f7fffffff7f7f7fffbfffffb
      fffffffff7f7f7f7f7f7fffffff7f7f7fffffffffbfffffbffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffefefeffffffffffffffffb
      fffffbfffffffffffffff7f7f7fffffffffffffffbfff7f3f7fffffffffffffffbffffffffffff
      fff7f7f7fffbfffffffffffffff7f3f7fffffffffffffffbfffffffff7f3f7f7f7f7fffbfffffb
      fffffffffffffff7f3f7fffffff7f7f7fffbfffffbfffffffff7f7 f 7ffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff f fffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff000000ffffffffff
      fffffffffffffffffffffffffffffffffffffffffffffffffff7f3f7fffffffffffff7f7f7ffff
      fffffffffffbffffffffefefeffffffffffbfffffffff7f7f7fffffffffffffffffff7f7f7ffff
      fffffffff7f3f7fffffffffbfffffffff7f3f7fffffff7f7f7f7f7f7fffffffffbffffffffffff
      fffffffffffbfffffffff7f7f7f7f7f7fffffff7f7f7fffbfffffffff7f3f7fffbfffffbfff7f3
      f7fffffffffbfffffbfffffffffffbffffffffffffffefefeffffffffffbffefefeffffffff7f7
      f7f7f7f7fffffffffbfffffffffffffffffffff7f7f7000000fffbfffffffff7f7f7fffffff7f3
      f7fffffffffffffffffffffffffffffffffffffffffffffffffffbfffffffffffffffffbffffff
      fff7f3f7fffffff7f3f7fffbfffffffffffbffffffffffffffefefeffffffffffbfffffbffffff
      ffefefeffffffffffffff7f7f7ffffffffffffffffffffffffffffffffffffffffffffffffffff
      fffffffffffffffffbfff7f7f7fffffffffffffffbfffffffffffffffffffff7f3f7ffffffffff
      f f000000000000fffffff7f7f7efefeffffffff7f7f7ffffffffffffefebefffffffffffffffff
      fffffffffffffffffffffffffffffffffffffffffffff7f7f7fffffffffbfff7f3f7ffffffffff
      fff7f7f7fffbff000000fffffff7f7f7f7f3f7fffbfffffffffffffffffbfffffffff7f3f7ffff
      fffffffff7f7f7fffffffffffffffffffffbfffffbffefefeff7f7f7fffffffffffffffffffffb
      fffffffff7f3f7fffffffffffff7f7f7fffffffffffffffffff7f3f7fffffffffffff7f7f7f7f7
      f7fffffff7f7f7f7f7f7fffffffffffffffffff7f3f7fffffffffbffefefefffffffffffffffff
      fff7f7f7f7f7f7fffffff7f7f7ffffffefefeff7f7f7f7f3f7fffffffffffffffbffefefefffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffb
      fffffffffffffffffffff7f3f7ffffffdedfdefffbfffffbfffffffffffffffffffff7f3f7ffff
      ffdedfdefffbfffffbfffffffffffbfffffffffffbfffffffff7f3f7fffffffffbfffffffffffb
      ffffffffffffffefefeffffffffffbfffffffff7f3f7f7f3f7fffffff7f3f7efefeffffffff7f7
      f7ffffffffffff f fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffbffffff
      fffffffffffffff7f3f7ffffffdedfdefffbfffffbfffffffffffffffffbfffffffff7f3f7ffff
      fff7f3f7fffbfffffffffffbfffffffffffbfffffffff7f3f7fffffffffffff7f7f7ffffffffff
      fffffbfffffffffffffff7f7f7fffbfffffffffffbffffffffffffffefefeffffffffffbffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffff f fffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
      ff000000fffffffffffffffffffffffffffffffffffffffffffffffffffbfff7f3f7f7f7f7ffff
      ffefefeffffffff7f7f7000000fffbfffffffffffffff7f3f7000000000000fffffff7f7f7f7f3

      === message truncated ===


      Yahoo! Music Unlimited - Access over 1 million songs. Try it free.


      Yahoo! Music Unlimited - Access over 1 million songs. Try it free.

    • R P
      The size of my shared pool is approx 550 meg. Until you can decide on what action you want to take to prevent the ORA-errors in the future, you might monitor
      Message 2 of 10 , Oct 17, 2005
      • 0 Attachment
        The size of my shared pool is approx 550 meg. Until you can decide on what action you want to take to prevent the ORA-errors in the future, you might monitor fragmentation and issue
        alter system flush shared pool to prevent the ORA-erros.  Of course, performance will take a hit when you issue that, but you might be able to prevent complete db outage with that.
        Thanks
        Richard
        rsalido@... wrote:

        Thanks. Whats the size of your shared pool? In our case its 128M and 160M for buffer cache. We did have an incident were the database had to be bounced because of ORA- errors.
         
        Roberto.

        R P <pjksel@...> wrote:
        Hi,
         
        I use cursor_sharing=force parameter currently in our 9i (9205) databases. It works much better in 9i than it did in 8i.  Peoplesoft uses both bind variables and literals in the application. I have never had a problem with shared pool fragmentation resulting in ORA- errors.
        Using cursor_sharing=force can cause some queries to perform poorly as they might not be able to use histogram information properly.
        Thanks,
        Richard

        rsalido@... wrote:
        Hello,
            I have been using statspack to monitor our database and I am a bit concerned about what I see. (Please see attached file).
            The memory usage seems a little high. Also, the Soft Parse % seems very low which might be an indicator of excessive hard parsing due to a bind variable problem.
         
        1.- What percentage of you shared pool memory do you see your systems using?
        2.- Do you use cursor_sharing=force?
        3.- Does PeopleSoft have a bind variable usage problem? I see a lot of SQL using literals out there.
         
        Thanks a lot.
         
         


        Yahoo! Music Unlimited - Access over 1 million songs. Try it free. {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fcharset0 Arial;}}
        \viewkind4\uc1\pard\f0\fs20{\pict\wmetafile8\picw14153\pich4867\picwgoal8024\pichgoal2759
        010009000003184202000000024202000000050000000b0200000000050000000c020313493702
        420200430f2000cc000000b80017020000000003134937000000002800000017020000b8000000
        0100180000000000c0830400c40e0000c40e00000000000000000000ffffffffffffffffffffff
        fffffffffffffffffffffffffffffffffffffffffbfffffffffffffff7f7f7ffffffffffffffff
        fff7f3f7fffffffffffff7f7f7fffbfffffffffffbfff7f7f7fffffffffffff7f3f7fffffffffb
        fffffbfffffbfffffbfffffbfff7f7f7fffffffffffff7f3f7fffffffffbfffffffffffffff7f7
        f7fffffffffffffffbfff7f3f7fffffffffffffffbfffffffffffbfffffbfffffffffffbffffff
        fffffffffffffffffffffffbfffffffffffbfffffffffffbfffffffffffbfffffffffffbfffffb
        fffffbfffffbfffffffff7f7f7fffffffffffffffbffffffffffffffefefefffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffefefeffffffffffffff7f3f7fffffffffb
        fffffffffffffffffffffffffffffbfffffffffffbfffffffffffbfffffbfffffbfff7f7f7ffff
        fff7f7f7fffffffffffff7f7f7ffffffffffffffffffffffffffffffffffffffffffffffffffff
        fff7f7f7f ffffffffbfff7f7f7fffffffffbfffffbfffffbfffffbfffffffffffffffffffffffb
        fff7f7f7fffffffffffffffbfffffbfffffffffffffffffffffffffffffbffffffffffffffffff
        fffffffffffffffffffffffffffffffffffffffffbfff7f7f7fffbfffffbfffffffff7f3f7ffff
        fffffffffffffff7f7f7ffffffefefeffffffff7f3f7fffffffffffffffbfffffffff7f7f7ffff
        fffffffffffbfffffffff7f3f7fffffff7f7f7fffffffffffffffbfffffbfffffffffffffffffb
        fffffffff7f7f7fffffffffffffffbfffffffffffbffffffffffffffffffffefefefffffffffff
        fffffffffffffffffbfffffffffffbfffffffffffbfffffbfffffffffffbffffffffffffffffff
        fff7f7f7fffffff7f3f7ffffffffffffffffffffffffefefeffffffffffffff7f7f7ffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffbfff7f7
        f7fffffff7f7f7fffffff7f7f7fffffffffffffffbfff7f7f7fffffff7f7f7fffffff7f7f7ffff
        fffffbfffffffff7f7f7fffffffffbfffffffffffbfffffffffffffffffffffffffffffbffffff
        fffffbfffffffffffbffff f ffffffffffffbfff7f3f7fffffff7f7f7fffbffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffbfff7f7f7ffff
        fff7f7f7fffffff7f7f7ffffffffffffefefeffffffffffffff7f3f7fffffffffbfffffffffffb
        fffffffff7f7f7fffffffffbfffffffffffbfffffffffffffffffbfffffffff7f7f7fffffffffb
        fffffffffffbfffffffffffffffffffffffbfffffffffffbfffffffffffbffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffff f fffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff000000ffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffe7e7e7ffffffffffffefebefffff
        fffffbffffffffefefeffffffff7f3f7efebeffffffffffffff7f7f7fffffffffffffffbfffffb
        fff7f7f7fffffffffffffffffffffffffffffffffffffffffffffffffffffffffbfffffbffffff
        fffffffffffbfff7f7f7fffffffffffffffffffffffff7f7f7fffffffffbfffffffffffbfffffb
        fffffffffffbfffffffff7f7f7f7f7f7fffffffffffff7f7f7 f ffffff7f7f7fffffffffffff7f7
        f7fffffffffbfffffffffffffff7f7f7fffbffffffffffffffefebeffffffffffffff7f3f7ffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        fffffffffffbfffffbfffffbfff7f7f7f7f7f7fffffffffffff7f7f7fffffff7f7f7ffffffffff
        fffffffffffffffffffff7f7f7fffbfffffbffffffffffffffffffffffffffffffffffffffffff
        fffffffffffffffffbffefefeffffffffffbffffffffefefeffffffffffffffffffffffbfff7f7
        f7fffffff7f7f7ffffffffffffefefeff7f7f7fffbfffffffffffbfffffbfffffbfffffffff7f3
        f7ffffffffffffffffffffffffffffffffffffffffffffffffffffffefebefffffffffffffffff
        fff7f3f7ffffffffffffffffffefefeffffbfffffffffffffffffbfffffbfff7f7f7f7f3f7ffff
        fffffbfff7f7f7fffbfffffffffffffffffbfffffffff7f3f7fffffffffbfffffbffffffffffff
        fffffffff7f3f7fffffffffbfff7f7f7fffbfffffffffffffffffbfffffffff7f7f7ffffffffff
        fffffffff7f3f7fffffffffbfffffbfffffbfffffbfffffffffffffffffffffffffff7f7f7ffff
        ffefebeffffbfffffffff7f7f7fffbfffffffffffbfff7f7f7f7f7f7ffffffff f fffffffffffff
        fffffffffffbffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        fffffbfffffffffffffffffffff7f7f7fffffffffffff7f7f7fffbfffffffffffffffffffff7f7
        f7fffffffffffff7f7f7fffffffffffffffffffffffffffbfffffffffffffffffffff7f7f7f7f7
        f7fffffffffffff7f7f7fffffff7f7f7fffffffffbfffffbfffffffffffffff7f3f7ffffffffff
        ffefebefffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffb
        fffffffffffffffffffff7f7f7fffffffffffff7f7f7fffffffffffffffffffffffffffffffffb
        fffffbfffffbfffffffffffffffffffffffffffffbfffffffffffffffffffff7f7f7fffffff7f7
        f7f7f7f7fffffffffbfffffbfffffffff7f7f7f7f7f7fffffffffffff7f7f7fffffff7f7f7ffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff < BR>ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        fffffff f000 000fffffffffffffffffffffffffffffffffffffffffffffffffffbfffffffff7f7
        f7f7f7f7fffffffffffffffffff7f3f7fffffff7f7f7fffffffffffffffffffffffffffffff7f7
        f7fffffff7f3f7fffffffffffffffbffefefefefefeffffbffefebeffffffffffbfff7f7f7efeb
        effffffffffffffffffff7f3f7fffffffffffff7f3f7fffffffffbfffffbfffffbfffffbfff7f7
        f7fffbfffffffffffffffffbfff7f7f7fffbfffffffffffffffffffff7f3f7fffffff7f7f7ffff
        fff7f7f7f7f7f7fffffff7f7f7f7f7f7ffffffefebeffffffffffffffffbfff7f7f7ffffffffff
        fffffffffffffffffffff7f3f7ffffffffffffffffffffffffffffffffffffffffffffffffefef
        effffbfffffffff7f3f7f7f7f7fffffffffffff7f7f7fffffffffffffffffff7f3f7fffffff7f7
        f7fffffff7f7f7ffffffefefefefefeffffbfffffffffffffff7f3f7ffffffffffffffffffffff
        fffffffffffffffffffffffffffffffffffffffffffffffbfff7f3f7f7f3f7fffffffffffffffb
        fffffbfffffffffffffff7f3f7fffbfffffffffffffffffffffffffffffffffffbffffffffefeb
        effffffffffbffffffffffffffffffffffffffffffffffffffffffffffffffffffffefefefffff
        fff7f7f7fffbfffffbfffffb f ffffffff7f7f7fffbfffffffffffbfff7f7f7f7f7f7f7f3f7ffff
        fffffffffffffff7f7f7ffffffffffffffffffefebeffffffff7f7f7f7f3f7fffffffffbffffff
        ffffffffefefeffffffff7f7f7fffffff7f7f7ffffffffffffffffffefebeffffffff7f7f7fffb
        fffffffffffbffefefeff7f7f7ffffffefefeffffffff7f7f7ffffffefebeff7f7f7ffffffffff
        fffffbfffffffffffffffffbfffffffffffbfffffffffffffffffffffffbffffffffffffffffff
        fffffbfff7f3f7f7f7f7fffbffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffe7e7e7fffbfffffffffffffff7f7f7ffffffffffffffff
        ffe7e7e7fffbfffffffffffffff7f7f7fffffffffffffffffff7f3f7f7f7f7fffffffffbffefef
        effffffffffffffffffffffffff7f3f7fffffff7f7f7fffffff7f7f7fffffff7f3f7ffffffefef
        effffffffffbfffffbffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffe7e7e7fffbff f ffffffffffff7f7f7ffffffefefeffffbffffff
        fff7f3f7f7f7f7fffffffffffff7f7f7fffffffffffff7f3f7f7f7f7fffffffffbffefefefffff
        fffffffffffffffffffff7f7f7f7f7f7fffbfff7f7f7fffffffffffffffffffffffff7f3f7ffff
        fff7f7f7fffffff7f7f7ffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffff f fffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffff000000ffffffffffffffffffffffffffffffffffffffffffffff
        fffffbfffffffff7f7f7ffffffffffffefebefffffffffffffffffffe7e7e7fffffff7f3f7ffff
        fffffffff7f7f7fffffffffffffffffffffbfff7f3f7fffbfffffffffffffffffffffffffffffb
        fffffffff7f7f7fffffffffbfffffbfffffffffffffffffffff7f7f7fffffffffffffffbffefeb
        effffffffffffffffffffffffff7f7f7f7f7f7fffffffffffffffffff7f3f7fffffffffffffffb
        fffffbffffffffefebeffffffffffbfffffffff7f7f7f7f7f7f7f7f7fffffffffbff0000000804
        08000000fffffff7f7f7f7f3f7fffffffffbffffffffffffffffffffffffffffffffffffffffff
        fffffffffffffffffffffffffffffbfffffffffffffffffbfff7f7f7fffffff7f3 f 7ffffffffff
        fffffbfffffbffffffffefebeffffffffffffffffffffffffffffbfff7f3f7fffbffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffefebeffffbffffffffffffffffff
        fff7f7f7f7f7f7fffffff7f7f7fffffff7f7f7f7f7f7ffffffffffffefefeffffffff7f7f7f7f3
        f7fffbfffffffffffffffffffffffbffffffffffffffffffffffffffffffffffffffffffffffff
        fffffffffffffffffbfffffffffffffffffbfffffffffffffffffbfffffffff7f3f7f7f7f7ffff
        fffffffffffffffffbfff7f7f7fffbffffffffefefefefefeffffbfffffffffffbffffffffffff
        ffffffffefefefffffffefebeffffffff7f7f7fffffffffbffffffffefefefefefeffffbffffff
        fffffbfffffffff7f3f7fffffffffffffffffffffffffffffff7f3f7f7f7f7fffffffffbffffff
        fffffffffffbfff7f3f7efebeffffffffffffffffbffffffffffffffefebeffffffffffffff7f7
        f7fffffff7f7f7fffffffffffffffbfffffffffffbffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        fffffffffffffffffffffffffffffffffffffff7f3f7fffffffffffffffffffffffff7f7f7fffbfffffbfff7f3f7fffffffffffffffffffffffff7f7f7fffbfffffbfffffbfff7f7f7ffffffffff
        fff7f3f7f7f7f7fffffffffbfff7f3f7fffffffffffffffbfffffbffffffffefebefffffffffff
        fffffffffffbfffffffffffffffffbfff7f3f7fffbffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        fffffffffffffffffffffffffffffffff7f3f7fffffffffffffffffffffffff7f7f7fffbfffffb
        fffffffffffffffffbfffffffffffffffffbfff7f7f7fffffffffbfff7f7f7fffffffffffff7f3
        f7f7f7f7fffffffffbfff7f7f7efefeffffffffffffffffbfffffffff7f7f7fffbfff7f3f7ffff
        fffffffffffbfffffbffffffffefebefffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        fffffffffff f f fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffff000000ffffffffffffffffffffffffffff
        fffffffffffffffffffff7f7f7ffffffffffffefebeffffffffffffff7f3f7ffffffffffffffff
        fffffffffffbfffffffffffbfffffffff7f3f7f7f7f7fffffffffbfffffffffffffff7f3f7f7f3
        f7ffffffefefefffffffffffff f ffffff7f3f7fffffffffbfffffffffffbfffffbfffffbffefef
        effffffffffffffffffffffffffffffffffffffffbfffffffffffffffffbffffffffffffffffff
        fff7f3f7fffffff7f7f7fffbfffffbfffffffff7f7f7fffffff7f3f7fffffffffffffffffff7f3
        f7fffbfffffbfff7f7f7080408000000fffffffffffffffbfff7f3f7ffffffffffffffffffffff
        fffffffffffffffffffffffffffffffff7f7f7fffffffffffffffbfff7f3f7fffffffffffffffb
        fffffffff7f3f7fffffff7f7f7fffbfffffbfffffffff7f7f7fffffff7f3f7efefefffffffffff
        fffffffffffffff7f7f7fffffffffffffffffffffffffffffffffffffffffffffffffffffff7f7
        f7fffffff7f3f7f7f7f7ffffffefefeff7f7f7f7f7f7ffffffffffffffffffffffff0000000000
        00fffffffffffffffffff7f7f7ffffffefefeffffbfffffffff7f3f7ffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffefebeffffffffffffff7f7f7fffbffffffff000000ffff
        fffffffffffffff7f7f7fffffffffffff7f7f7fffffffffbfffffbfffffffff7f3f7fffffff7f3
        f7efebeffffffffffbfffffffffffffffffffffffffffffbfffffffffffbfffffbfffffbffffff
        fff7f3f7fffffff7f3f7efebeffffffffffffff7 f 3f7fffffffffbffffffffefefefffffffffff
        fff7f7f7f7f3f7fffffffffffffffffffffffffffffff7f3f7f7f3f7fffbffffffffefefefffff
        fffffbfff7f7f7fffffffffffffffffffffffffffbfffffffffffffff7f3f7ffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffefefefffffffffff
        fffffbfffffbffffffffffffffffffffefefeffffffffffffffffbfffffbffffffffffffffffff
        fffffffff7f7f7fffbfffffffffffffff7f3f7fffffffffffff7f3f7fffffff7f7f7fffbfffffb
        fffffffff7f7f7f7f7f7fffffff7f7f7fffffffffbfffffbffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffefefeffffffffffffffffb
        fffffbfffffffffffffff7f7f7fffffffffffffffbfff7f3f7fffffffffffffffbffffffffffff
        fff7f7f7fffbfffffffffffffff7f3f7fffffffffffffffbfffffffff7f3f7f7f7f7fffbfffffb
        fffffffffffffff7f3f7fffffff7f7f7fffbfffffbfffffffff7f7 f 7ffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff f fffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff000000ffffffffff
        fffffffffffffffffffffffffffffffffffffffffffffffffff7f3f7fffffffffffff7f7f7ffff
        fffffffffffbffffffffefefeffffffffffbfffffffff7f7f7fffffffffffffffffff7f7f7ffff
        fffffffff7f3f7fffffffffbfffffffff7f3f7fffffff7f7f7f7f7f7fffffffffbffffffffffff
        fffffffffffbfffffffff7f7f7f7f7f7fffffff7f7f7fffbfffffffff7f3f7fffbfffffbfff7f3
        f7fffffffffbfffffbfffffffffffbffffffffffffffefefeffffffffffbffefefeffffffff7f7
        f7f7f7f7fffffffffbfffffffffffffffffffff7f7f7000000fffbfffffffff7f7f7fffffff7f3
        f7fffffffffffffffffffffffffffffffffffffffffffffffffffbfffffffffffffffffbffffff
        fff7f3f7fffffff7f3f7fffbfffffffffffbffffffffffffffefefeffffffffffbfffffbffffff
        ffefefeffffffffffffff7f7f7ffffffffffffffffffffffffffffffffffffffffffffffffffff
        fffffffffffffffffbfff7f7f7fffffffffffffffbfffffffffffffffffffff7f3f7ffffffffff
        f f000000000000fffffff7f7f7efefeffffffff7f7f7ffffffffffffefebefffffffffffffffff
        fffffffffffffffffffffffffffffffffffffffffffff7f7f7fffffffffbfff7f3f7ffffffffff
        fff7f7f7fffbff000000fffffff7f7f7f7f3f7fffbfffffffffffffffffbfffffffff7f3f7ffff
        fffffffff7f7f7fffffffffffffffffffffbfffffbffefefeff7f7f7fffffffffffffffffffffb
        fffffffff7f3f7fffffffffffff7f7f7fffffffffffffffffff7f3f7fffffffffffff7f7f7f7f7
        f7fffffff7f7f7f7f7f7fffffffffffffffffff7f3f7fffffffffbffefefefffffffffffffffff
        fff7f7f7f7f7f7fffffff7f7f7ffffffefefeff7f7f7f7f3f7fffffffffffffffbffefefefffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffb
        fffffffffffffffffffff7f3f7ffffffdedfdefffbfffffbfffffffffffffffffffff7f3f7ffff
        ffdedfdefffbfffffbfffffffffffbfffffffffffbfffffffff7f3f7fffffffffbfffffffffffb
        ffffffffffffffefefeffffffffffbfffffffff7f3f7f7f3f7fffffff7f3f7efefeffffffff7f7
        f7ffffffffffff f fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffbffffff
        fffffffffffffff7f3f7ffffffdedfdefffbfffffbfffffffffffffffffbfffffffff7f3f7ffff
        fff7f3f7fffbfffffffffffbfffffffffffbfffffffff7f3f7fffffffffffff7f7f7ffffffffff
        fffffbfffffffffffffff7f7f7fffbfffffffffffbffffffffffffffefefeffffffffffbffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffff f fffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
        ff000000fffffffffffffffffffffffffffffffffffffffffffffffffffbfff7f3f7f7f7f7ffff
        ffefefeffffffff7f7f7000000fffbfffffffffffffff7f3f7000000000000fffffff7f7f7f7f3

        === message truncated ===


        Yahoo! Music Unlimited - Access over 1 million songs. Try it free.


        Yahoo! Music Unlimited - Access over 1 million songs. Try it free.


        Yahoo! Music Unlimited - Access over 1 million songs. Try it free.

      • rsalido@yahoo.com
        RP, I tried that....but it did not work. The errors kept popping up until the point where nobody could get nothing done. Oracle said that there have been cases
        Message 3 of 10 , Oct 17, 2005
        • 0 Attachment
          RP,
             I tried that....but it did not work. The errors kept popping up until the point where nobody could get nothing done. Oracle said that there have been cases where this command would not work, specially if free memory is less than 10% which was the case at the time.
          R.
          R P <pjksel@...> wrote:
          The size of my shared pool is approx 550 meg. Until you can decide on what action you want to take to prevent the ORA-errors in the future, you might monitor fragmentation and issue
          alter system flush shared pool to prevent the ORA-erros.  Of course, performance will take a hit when you issue that, but you might be able to prevent complete db outage with that.
          Thanks
          Richard
          rsalido@... wrote:

          Thanks. Whats the size of your shared pool? In our case its 128M and 160M for buffer cache. We did have an incident were the database had to be bounced because of ORA- errors.
           
          Roberto.

          R P <pjksel@...> wrote:
          Hi,
           
          I use cursor_sharing=force parameter currently in our 9i (9205) databases. It works much better in 9i than it did in 8i.  Peoplesoft uses both bind variables and literals in the application. I have never had a problem with shared pool fragmentation resulting in ORA- errors.
          Using cursor_sharing=force can cause some queries to perform poorly as they might not be able to use histogram information properly.
          Thanks,
          Richard

          rsalido@... wrote:
          Hello,
              I have been using statspack to monitor our database and I am a bit concerned about what I see. (Please see attached file).
              The memory usage seems a little high. Also, the Soft Parse % seems very low which might be an indicator of excessive hard parsing due to a bind variable problem.
           
          1.- What percentage of you shared pool memory do you see your systems using?
          2.- Do you use cursor_sharing=force?
          3.- Does PeopleSoft have a bind variable usage problem? I see a lot of SQL using literals out there.
           
          Thanks a lot.
           
           


          Yahoo! Music Unlimited - Access over 1 million songs. Try it free. {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fcharset0 Arial;}}
          \viewkind4\uc1\pard\f0\fs20{\pict\wmetafile8\picw14153\pich4867\picwgoal8024\pichgoal2759
          010009000003184202000000024202000000050000000b0200000000050000000c020313493702
          420200430f2000cc000000b80017020000000003134937000000002800000017020000b8000000
          0100180000000000c0830400c40e0000c40e00000000000000000000ffffffffffffffffffffff
          fffffffffffffffffffffffffffffffffffffffffbfffffffffffffff7f7f7ffffffffffffffff
          fff7f3f7fffffffffffff7f7f7fffbfffffffffffbfff7f7f7fffffffffffff7f3f7fffffffffb
          fffffbfffffbfffffbfffffbfff7f7f7fffffffffffff7f3f7fffffffffbfffffffffffffff7f7
          f7fffffffffffffffbfff7f3f7fffffffffffffffbfffffffffffbfffffbfffffffffffbffffff
          fffffffffffffffffffffffbfffffffffffbfffffffffffbfffffffffffbfffffffffffbfffffb
          fffffbfffffbfffffffff7f7f7fffffffffffffffbffffffffffffffefefefffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffefefeffffffffffffff7f3f7fffffffffb
          fffffffffffffffffffffffffffffbfffffffffffbfffffffffffbfffffbfffffbfff7f7f7ffff
          fff7f7f7fffffffffffff7f7f7ffffffffffffffffffffffffffffffffffffffffffffffffffff
          fff7f7f7f ffffffffbfff7f7f7fffffffffbfffffbfffffbfffffbfffffffffffffffffffffffb
          fff7f7f7fffffffffffffffbfffffbfffffffffffffffffffffffffffffbffffffffffffffffff
          fffffffffffffffffffffffffffffffffffffffffbfff7f7f7fffbfffffbfffffffff7f3f7ffff
          fffffffffffffff7f7f7ffffffefefeffffffff7f3f7fffffffffffffffbfffffffff7f7f7ffff
          fffffffffffbfffffffff7f3f7fffffff7f7f7fffffffffffffffbfffffbfffffffffffffffffb
          fffffffff7f7f7fffffffffffffffbfffffffffffbffffffffffffffffffffefefefffffffffff
          fffffffffffffffffbfffffffffffbfffffffffffbfffffbfffffffffffbffffffffffffffffff
          fff7f7f7fffffff7f3f7ffffffffffffffffffffffffefefeffffffffffffff7f7f7ffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffbfff7f7
          f7fffffff7f7f7fffffff7f7f7fffffffffffffffbfff7f7f7fffffff7f7f7fffffff7f7f7ffff
          fffffbfffffffff7f7f7fffffffffbfffffffffffbfffffffffffffffffffffffffffffbffffff
          fffffbfffffffffffbffff f ffffffffffffbfff7f3f7fffffff7f7f7fffbffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffbfff7f7f7ffff
          fff7f7f7fffffff7f7f7ffffffffffffefefeffffffffffffff7f3f7fffffffffbfffffffffffb
          fffffffff7f7f7fffffffffbfffffffffffbfffffffffffffffffbfffffffff7f7f7fffffffffb
          fffffffffffbfffffffffffffffffffffffbfffffffffffbfffffffffffbffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffff f fffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff000000ffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffe7e7e7ffffffffffffefebefffff
          fffffbffffffffefefeffffffff7f3f7efebeffffffffffffff7f7f7fffffffffffffffbfffffb
          fff7f7f7fffffffffffffffffffffffffffffffffffffffffffffffffffffffffbfffffbffffff
          fffffffffffbfff7f7f7fffffffffffffffffffffffff7f7f7fffffffffbfffffffffffbfffffb
          fffffffffffbfffffffff7f7f7f7f7f7fffffffffffff7f7f7 f ffffff7f7f7fffffffffffff7f7
          f7fffffffffbfffffffffffffff7f7f7fffbffffffffffffffefebeffffffffffffff7f3f7ffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          fffffffffffbfffffbfffffbfff7f7f7f7f7f7fffffffffffff7f7f7fffffff7f7f7ffffffffff
          fffffffffffffffffffff7f7f7fffbfffffbffffffffffffffffffffffffffffffffffffffffff
          fffffffffffffffffbffefefeffffffffffbffffffffefefeffffffffffffffffffffffbfff7f7
          f7fffffff7f7f7ffffffffffffefefeff7f7f7fffbfffffffffffbfffffbfffffbfffffffff7f3
          f7ffffffffffffffffffffffffffffffffffffffffffffffffffffffefebefffffffffffffffff
          fff7f3f7ffffffffffffffffffefefeffffbfffffffffffffffffbfffffbfff7f7f7f7f3f7ffff
          fffffbfff7f7f7fffbfffffffffffffffffbfffffffff7f3f7fffffffffbfffffbffffffffffff
          fffffffff7f3f7fffffffffbfff7f7f7fffbfffffffffffffffffbfffffffff7f7f7ffffffffff
          fffffffff7f3f7fffffffffbfffffbfffffbfffffbfffffffffffffffffffffffffff7f7f7ffff
          ffefebeffffbfffffffff7f7f7fffbfffffffffffbfff7f7f7f7f7f7ffffffff f fffffffffffff
          fffffffffffbffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          fffffbfffffffffffffffffffff7f7f7fffffffffffff7f7f7fffbfffffffffffffffffffff7f7
          f7fffffffffffff7f7f7fffffffffffffffffffffffffffbfffffffffffffffffffff7f7f7f7f7
          f7fffffffffffff7f7f7fffffff7f7f7fffffffffbfffffbfffffffffffffff7f3f7ffffffffff
          ffefebefffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffb
          fffffffffffffffffffff7f7f7fffffffffffff7f7f7fffffffffffffffffffffffffffffffffb
          fffffbfffffbfffffffffffffffffffffffffffffbfffffffffffffffffffff7f7f7fffffff7f7
          f7f7f7f7fffffffffbfffffbfffffffff7f7f7f7f7f7fffffffffffff7f7f7fffffff7f7f7ffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff < BR>ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          fffffff f000 000fffffffffffffffffffffffffffffffffffffffffffffffffffbfffffffff7f7
          f7f7f7f7fffffffffffffffffff7f3f7fffffff7f7f7fffffffffffffffffffffffffffffff7f7
          f7fffffff7f3f7fffffffffffffffbffefefefefefeffffbffefebeffffffffffbfff7f7f7efeb
          effffffffffffffffffff7f3f7fffffffffffff7f3f7fffffffffbfffffbfffffbfffffbfff7f7
          f7fffbfffffffffffffffffbfff7f7f7fffbfffffffffffffffffffff7f3f7fffffff7f7f7ffff
          fff7f7f7f7f7f7fffffff7f7f7f7f7f7ffffffefebeffffffffffffffffbfff7f7f7ffffffffff
          fffffffffffffffffffff7f3f7ffffffffffffffffffffffffffffffffffffffffffffffffefef
          effffbfffffffff7f3f7f7f7f7fffffffffffff7f7f7fffffffffffffffffff7f3f7fffffff7f7
          f7fffffff7f7f7ffffffefefefefefeffffbfffffffffffffff7f3f7ffffffffffffffffffffff
          fffffffffffffffffffffffffffffffffffffffffffffffbfff7f3f7f7f3f7fffffffffffffffb
          fffffbfffffffffffffff7f3f7fffbfffffffffffffffffffffffffffffffffffbffffffffefeb
          effffffffffbffffffffffffffffffffffffffffffffffffffffffffffffffffffffefefefffff
          fff7f7f7fffbfffffbfffffb f ffffffff7f7f7fffbfffffffffffbfff7f7f7f7f7f7f7f3f7ffff
          fffffffffffffff7f7f7ffffffffffffffffffefebeffffffff7f7f7f7f3f7fffffffffbffffff
          ffffffffefefeffffffff7f7f7fffffff7f7f7ffffffffffffffffffefebeffffffff7f7f7fffb
          fffffffffffbffefefeff7f7f7ffffffefefeffffffff7f7f7ffffffefebeff7f7f7ffffffffff
          fffffbfffffffffffffffffbfffffffffffbfffffffffffffffffffffffbffffffffffffffffff
          fffffbfff7f3f7f7f7f7fffbffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffe7e7e7fffbfffffffffffffff7f7f7ffffffffffffffff
          ffe7e7e7fffbfffffffffffffff7f7f7fffffffffffffffffff7f3f7f7f7f7fffffffffbffefef
          effffffffffffffffffffffffff7f3f7fffffff7f7f7fffffff7f7f7fffffff7f3f7ffffffefef
          effffffffffbfffffbffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffe7e7e7fffbff f ffffffffffff7f7f7ffffffefefeffffbffffff
          fff7f3f7f7f7f7fffffffffffff7f7f7fffffffffffff7f3f7f7f7f7fffffffffbffefefefffff
          fffffffffffffffffffff7f7f7f7f7f7fffbfff7f7f7fffffffffffffffffffffffff7f3f7ffff
          fff7f7f7fffffff7f7f7ffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffff f fffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffff000000ffffffffffffffffffffffffffffffffffffffffffffff
          fffffbfffffffff7f7f7ffffffffffffefebefffffffffffffffffffe7e7e7fffffff7f3f7ffff
          fffffffff7f7f7fffffffffffffffffffffbfff7f3f7fffbfffffffffffffffffffffffffffffb
          fffffffff7f7f7fffffffffbfffffbfffffffffffffffffffff7f7f7fffffffffffffffbffefeb
          effffffffffffffffffffffffff7f7f7f7f7f7fffffffffffffffffff7f3f7fffffffffffffffb
          fffffbffffffffefebeffffffffffbfffffffff7f7f7f7f7f7f7f7f7fffffffffbff0000000804
          08000000fffffff7f7f7f7f3f7fffffffffbffffffffffffffffffffffffffffffffffffffffff
          fffffffffffffffffffffffffffffbfffffffffffffffffbfff7f7f7fffffff7f3 f 7ffffffffff
          fffffbfffffbffffffffefebeffffffffffffffffffffffffffffbfff7f3f7fffbffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffefebeffffbffffffffffffffffff
          fff7f7f7f7f7f7fffffff7f7f7fffffff7f7f7f7f7f7ffffffffffffefefeffffffff7f7f7f7f3
          f7fffbfffffffffffffffffffffffbffffffffffffffffffffffffffffffffffffffffffffffff
          fffffffffffffffffbfffffffffffffffffbfffffffffffffffffbfffffffff7f3f7f7f7f7ffff
          fffffffffffffffffbfff7f7f7fffbffffffffefefefefefeffffbfffffffffffbffffffffffff
          ffffffffefefefffffffefebeffffffff7f7f7fffffffffbffffffffefefefefefeffffbffffff
          fffffbfffffffff7f3f7fffffffffffffffffffffffffffffff7f3f7f7f7f7fffffffffbffffff
          fffffffffffbfff7f3f7efebeffffffffffffffffbffffffffffffffefebeffffffffffffff7f7
          f7fffffff7f7f7fffffffffffffffbfffffffffffbffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          fffffffffffffffffffffffffffffffffffffff7f3f7fffffffffffffffffffffffff7f7f7fffbfffffbfff7f3f7fffffffffffffffffffffffff7f7f7fffbfffffbfffffbfff7f7f7ffffffffff
          fff7f3f7f7f7f7fffffffffbfff7f3f7fffffffffffffffbfffffbffffffffefebefffffffffff
          fffffffffffbfffffffffffffffffbfff7f3f7fffbffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          fffffffffffffffffffffffffffffffff7f3f7fffffffffffffffffffffffff7f7f7fffbfffffb
          fffffffffffffffffbfffffffffffffffffbfff7f7f7fffffffffbfff7f7f7fffffffffffff7f3
          f7f7f7f7fffffffffbfff7f7f7efefeffffffffffffffffbfffffffff7f7f7fffbfff7f3f7ffff
          fffffffffffbfffffbffffffffefebefffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          fffffffffff f f fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffff000000ffffffffffffffffffffffffffff
          fffffffffffffffffffff7f7f7ffffffffffffefebeffffffffffffff7f3f7ffffffffffffffff
          fffffffffffbfffffffffffbfffffffff7f3f7f7f7f7fffffffffbfffffffffffffff7f3f7f7f3
          f7ffffffefefefffffffffffff f ffffff7f3f7fffffffffbfffffffffffbfffffbfffffbffefef
          effffffffffffffffffffffffffffffffffffffffbfffffffffffffffffbffffffffffffffffff
          fff7f3f7fffffff7f7f7fffbfffffbfffffffff7f7f7fffffff7f3f7fffffffffffffffffff7f3
          f7fffbfffffbfff7f7f7080408000000fffffffffffffffbfff7f3f7ffffffffffffffffffffff
          fffffffffffffffffffffffffffffffff7f7f7fffffffffffffffbfff7f3f7fffffffffffffffb
          fffffffff7f3f7fffffff7f7f7fffbfffffbfffffffff7f7f7fffffff7f3f7efefefffffffffff
          fffffffffffffff7f7f7fffffffffffffffffffffffffffffffffffffffffffffffffffffff7f7
          f7fffffff7f3f7f7f7f7ffffffefefeff7f7f7f7f7f7ffffffffffffffffffffffff0000000000
          00fffffffffffffffffff7f7f7ffffffefefeffffbfffffffff7f3f7ffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffefebeffffffffffffff7f7f7fffbffffffff000000ffff
          fffffffffffffff7f7f7fffffffffffff7f7f7fffffffffbfffffbfffffffff7f3f7fffffff7f3
          f7efebeffffffffffbfffffffffffffffffffffffffffffbfffffffffffbfffffbfffffbffffff
          fff7f3f7fffffff7f3f7efebeffffffffffffff7 f 3f7fffffffffbffffffffefefefffffffffff
          fff7f7f7f7f3f7fffffffffffffffffffffffffffffff7f3f7f7f3f7fffbffffffffefefefffff
          fffffbfff7f7f7fffffffffffffffffffffffffffbfffffffffffffff7f3f7ffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffefefefffffffffff
          fffffbfffffbffffffffffffffffffffefefeffffffffffffffffbfffffbffffffffffffffffff
          fffffffff7f7f7fffbfffffffffffffff7f3f7fffffffffffff7f3f7fffffff7f7f7fffbfffffb
          fffffffff7f7f7f7f7f7fffffff7f7f7fffffffffbfffffbffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffefefeffffffffffffffffb
          fffffbfffffffffffffff7f7f7fffffffffffffffbfff7f3f7fffffffffffffffbffffffffffff
          fff7f7f7fffbfffffffffffffff7f3f7fffffffffffffffbfffffffff7f3f7f7f7f7fffbfffffb
          fffffffffffffff7f3f7fffffff7f7f7fffbfffffbfffffffff7f7 f 7ffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff f fffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff000000ffffffffff
          fffffffffffffffffffffffffffffffffffffffffffffffffff7f3f7fffffffffffff7f7f7ffff
          fffffffffffbffffffffefefeffffffffffbfffffffff7f7f7fffffffffffffffffff7f7f7ffff
          fffffffff7f3f7fffffffffbfffffffff7f3f7fffffff7f7f7f7f7f7fffffffffbffffffffffff
          fffffffffffbfffffffff7f7f7f7f7f7fffffff7f7f7fffbfffffffff7f3f7fffbfffffbfff7f3
          f7fffffffffbfffffbfffffffffffbffffffffffffffefefeffffffffffbffefefeffffffff7f7
          f7f7f7f7fffffffffbfffffffffffffffffffff7f7f7000000fffbfffffffff7f7f7fffffff7f3
          f7fffffffffffffffffffffffffffffffffffffffffffffffffffbfffffffffffffffffbffffff
          fff7f3f7fffffff7f3f7fffbfffffffffffbffffffffffffffefefeffffffffffbfffffbffffff
          ffefefeffffffffffffff7f7f7ffffffffffffffffffffffffffffffffffffffffffffffffffff
          fffffffffffffffffbfff7f7f7fffffffffffffffbfffffffffffffffffffff7f3f7ffffffffff
          f f000000000000fffffff7f7f7efefeffffffff7f7f7ffffffffffffefebefffffffffffffffff
          fffffffffffffffffffffffffffffffffffffffffffff7f7f7fffffffffbfff7f3f7ffffffffff
          fff7f7f7fffbff000000fffffff7f7f7f7f3f7fffbfffffffffffffffffbfffffffff7f3f7ffff
          fffffffff7f7f7fffffffffffffffffffffbfffffbffefefeff7f7f7fffffffffffffffffffffb
          fffffffff7f3f7fffffffffffff7f7f7fffffffffffffffffff7f3f7fffffffffffff7f7f7f7f7
          f7fffffff7f7f7f7f7f7fffffffffffffffffff7f3f7fffffffffbffefefefffffffffffffffff
          fff7f7f7f7f7f7fffffff7f7f7ffffffefefeff7f7f7f7f3f7fffffffffffffffbffefefefffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffb
          fffffffffffffffffffff7f3f7ffffffdedfdefffbfffffbfffffffffffffffffffff7f3f7ffff
          ffdedfdefffbfffffbfffffffffffbfffffffffffbfffffffff7f3f7fffffffffbfffffffffffb
          ffffffffffffffefefeffffffffffbfffffffff7f3f7f7f3f7fffffff7f3f7efefeffffffff7f7
          f7ffffffffffff f fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffbffffff
          fffffffffffffff7f3f7ffffffdedfdefffbfffffbfffffffffffffffffbfffffffff7f3f7ffff
          fff7f3f7fffbfffffffffffbfffffffffffbfffffffff7f3f7fffffffffffff7f7f7ffffffffff
          fffffbfffffffffffffff7f7f7fffbfffffffffffbffffffffffffffefefeffffffffffbffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffff f fffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
          ff000000fffffffffffffffffffffffffffffffffffffffffffffffffffbfff7f3f7f7f7f7ffff
          ffefefeffffffff7f7f7000000fffbfffffffffffffff7f3f7000000000000fffffff7f7f7f7f3

          === message truncated ===


          Yahoo! Music Unlimited - Access over 1 million songs. Try it free.


          Yahoo! Music Unlimited - Access over 1 million songs. Try it free.


          Yahoo! Music Unlimited - Access over 1 million songs. Try it free.


          Yahoo! Music Unlimited - Access over 1 million songs. Try it free.

        • James Blanding
          nVision is particularly bad about bind variables. I had a case open for 10 months last year trying to convince the GSC it needed to be redesigned. They did
          Message 4 of 10 , Oct 18, 2005
          • 0 Attachment
            nVision is particularly bad about bind variables. I had a case open
            for 10 months last year trying to convince the GSC it needed to be
            redesigned. They did eventually admit that it was very inefficient
            under certain circumstances. In the end, though, it wound up as an
            Enhancement Request for PTools 8.42. A year later, the Incident still
            has a status of "Hold for Future." Such is life...

            --James

            --- In psftdba@yahoogroups.com, <rsalido@y...> wrote:
            >
            > Hello,
            > I have been using statspack to monitor our database and I am a
            bit concerned about what I see. (Please see attached file).
            > The memory usage seems a little high. Also, the Soft Parse %
            seems very low which might be an indicator of excessive hard parsing
            due to a bind variable problem.
            >
            > 1.- What percentage of you shared pool memory do you see your
            systems using?
            > 2.- Do you use cursor_sharing=force?
            > 3.- Does PeopleSoft have a bind variable usage problem? I see a lot
            of SQL using literals out there.
            >
            > Thanks a lot.
            >
            >
            >
            >
            >
            > ---------------------------------
            > Yahoo! Music Unlimited - Access over 1 million songs. Try it free.
            >
          • David Kurtz
            This thread has probably gone cold now, but I wrote this reply and forgot to send it. There is no doubt that PeopleSoft uses literal values in SQL where bind
            Message 5 of 10 , Oct 28, 2005
            • 0 Attachment
              This thread has probably gone cold now, but I wrote this reply and forgot to
              send it.

              There is no doubt that PeopleSoft uses literal values in SQL where bind
              variables would be more appropriate. But I have had some bad experiences
              with cursor_sharing, and I would not recommend its database-wide use.
              Cursor sharing does not mix well with the use of histograms (even with bind
              variable peeking in 9i), and I frequently apply histograms to the flag and
              process instance columns in Financials systems.

              Most of the literal values, and hence the parse overhead occurs in
              Application Engine programs, because most bind variables are converted to
              literals when constructing the SQL to be submitted to the database. This is
              why Financials systems are particularly affected by this problem. It would
              be possible to use the ReuseStatement flag at step level to make it use bind
              variables instead of literals. The problem that there are a lot of AE
              steps, and ReUseStatemnt is set to 'No; on most of them - and this would be
              a customisation.

              You could set cursor sharing selectively at session level with either an ON
              LOGON trigger or a trigger on PSPRCSRQST - then you could target specific AE
              programs, but it is often the same AE programs that need to use the
              histograms.

              nVision also uses literals, but here parse is not usually the dominant
              problem. Rather, that you are doing DSS style queries on tables that are
              part of an OLTP sytem. There is a presentation on my website about how to
              use the nVision performance options.

              If you have a Finanicals system on a unicode database then you are in deep
              trouble because the con$ table is not cached in the shared pool, and so
              whenever a statement is parsed, the length checking constraints that
              PeopleSoft created have to be reloaded. This increases the parse overhead
              by up to 300%.



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

              > -----Original Message-----
              > From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On Behalf
              > Of James Blanding
              > Sent: 18 October 2005 15:21
              > To: psftdba@yahoogroups.com
              > Subject: PeopleSoft DBA Forum Re: Shared Pool and Cursor_Sharing
              >
              >
              > nVision is particularly bad about bind variables. I had a case open
              > for 10 months last year trying to convince the GSC it needed to be
              > redesigned. They did eventually admit that it was very inefficient
              > under certain circumstances. In the end, though, it wound up as an
              > Enhancement Request for PTools 8.42. A year later, the Incident still
              > has a status of "Hold for Future." Such is life...
              >
              > --James
              >
              > --- In psftdba@yahoogroups.com, <rsalido@y...> wrote:
              > >
              > > Hello,
              > > I have been using statspack to monitor our database and I am a
              > bit concerned about what I see. (Please see attached file).
              > > The memory usage seems a little high. Also, the Soft Parse %
              > seems very low which might be an indicator of excessive hard parsing
              > due to a bind variable problem.
              > >
              > > 1.- What percentage of you shared pool memory do you see your
              > systems using?
              > > 2.- Do you use cursor_sharing=force?
              > > 3.- Does PeopleSoft have a bind variable usage problem? I see a lot
              > of SQL using literals out there.
              > >
              > > Thanks a lot.
              > >
              > >
              > >
              > >
              > >
              > > ---------------------------------
              > > Yahoo! Music Unlimited - Access over 1 million songs. Try it free.
              > >
              >
              >
              >
              >
              >
              >
              > 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
            • rsalido@yahoo.com
              David, So, seeing low parse ratios is not uncommon on an Oracle DB running Peoplesoft? How do you approach improving those ratios? Roberto. David Kurtz
              Message 6 of 10 , Oct 28, 2005
              • 0 Attachment
                David,
                 
                   So, seeing low parse ratios is not uncommon on an Oracle DB running Peoplesoft? How do you approach improving those ratios?
                 
                Roberto.

                David Kurtz <info2@...> wrote:
                This thread has probably gone cold now, but I wrote this reply and forgot to
                send it.

                There is no doubt that PeopleSoft uses literal values in SQL where bind
                variables would be more appropriate.  But I have had some bad experiences
                with cursor_sharing, and I would not recommend its database-wide use.
                Cursor sharing does not mix well with the use of histograms (even with bind
                variable peeking in 9i), and I frequently apply histograms to the flag and
                process instance columns in Financials systems.

                Most of the literal values, and hence the parse overhead occurs in
                Application Engine programs, because most bind variables are converted to
                literals when constructing the SQL to be submitted to the database.  This is
                why Financials systems are particularly affected by this problem.  It would
                be possible to use the ReuseStatement flag at step level to make it use bind
                variables instead of literals.  The problem that there are a lot of AE
                steps, and ReUseStatemnt is set to 'No; on most of them - and this would be
                a customisation.

                You could set cursor sharing selectively at session level with either an ON
                LOGON trigger or a trigger on PSPRCSRQST - then you could target specific AE
                programs, but it is often the same AE programs that need to use the
                histograms.

                nVision also uses literals, but here parse is not usually the dominant
                problem.  Rather, that you are doing DSS style queries on tables that are
                part of an OLTP sytem.  There is a presentation on my website about how to
                use the nVision performance options.

                If you have a Finanicals system on a unicode database then you are in deep
                trouble because the con$ table is not cached in the shared pool, and so
                whenever a statement is parsed, the length checking constraints that
                PeopleSoft created have to be reloaded.  This increases the parse overhead
                by up to 300%.



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

                > -----Original Message-----
                > From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On Behalf
                > Of James Blanding
                > Sent: 18 October 2005 15:21
                > To: psftdba@yahoogroups.com
                > Subject: PeopleSoft DBA Forum Re: Shared Pool and Cursor_Sharing
                >
                >
                > nVision is particularly bad about bind variables.  I had a case open
                > for 10 months last year trying to convince the GSC it needed to be
                > redesigned.  They did eventually admit that it was very inefficient
                > under certain circumstances.  In the end, though, it wound up as an
                > Enhancement Request for PTools 8.42.  A year later, the Incident still
                > has a status of "Hold for Future."  Such is life...
                >
                > --James
                >
                > --- In psftdba@yahoogroups.com, <rsalido@y...> wrote:
                > >
                > > Hello,
                > >     I have been using statspack to monitor our database and I am a
                > bit concerned about what I see. (Please see attached file).
                > >     The memory usage seems a little high. Also, the Soft Parse %
                > seems very low which might be an indicator of excessive hard parsing
                > due to a bind variable problem.
                > >
                > > 1.- What percentage of you shared pool memory do you see your
                > systems using?
                > > 2.- Do you use cursor_sharing=force?
                > > 3.- Does PeopleSoft have a bind variable usage problem? I see a lot
                > of SQL using literals out there.
                > >
                > > Thanks a lot.
                > >
                > >
                > >
                > >
                > >
                > > ---------------------------------
                > >  Yahoo! Music Unlimited - Access over 1 million songs. Try it free.
                > >
                >
                >
                >
                >
                >
                >
                > 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











                Yahoo! FareChase - Search multiple travel sites in one click.


                Yahoo! FareChase - Search multiple travel sites in one click.

              • David Kurtz
                First of all, don t focus on the parse ratio, look first at the amount of time you spend parsing. If that is not significant then you don t have a problem. On
                Message 7 of 10 , Oct 28, 2005
                • 0 Attachment
                  First of all, don't focus on the parse ratio, look first at the amount of time you spend parsing.  If that is not significant then you don't have a problem. 
                  On a system without much batch processing (so not Financials and not Payroll or T&L), parse is not normally a problem.
                  For these exceptions, especally on Unicode systems you may have a problem with batch processing.  On-line is not usually a problem in any PeopleSoft 8.x product.
                   
                  These are very broad sweeping statements, and I am sure some people will have exceptions. 
                  For instance if the database server is short of CPU, parse time can go up, and you can start to have a problem.
                   
                  PeopleSoft usually benefits from a large shared pool, but if the statements are full of literals then it doesn't how big you make the shared pool if each statement gets executed just once.  The real problem is not that the database is poorly tuned, but the application is poorly designed and coded.  However, your company has gone out and bought a packaged application and you have to live with it!
                   
                  My experience is that there are usually much larger savings to be made with a little SQL performance tuning, and these gains are much larger than the parse times.
                  However, when you have tuned the SQL as far as is reasonable, and parse time is becoming a significant part of the system response time you are left with few choices.
                  Your options are
                  i) use cursor sharing, but I would recommend doing so quite selectively - use a trigger at session level for particular processes.  Demonstrate that doing so does really have a beneficial effect.
                  ii) change the code.  In the case of AE programs set ReuseStatement for the most frequently executed steps (which you can determine from the batch timings tables).  This problem with this is that it is, of course, a customisation.  You have to made a judgement as to whether the overhead of the customisation is worth the benefit in performance - that's a political decision.  Customisation is a dirty word on some projects.
                   
                  regards
                  _________________________
                  David Kurtz
                  Go-Faster Consultancy Ltd.
                  tel: +44 (0)7771 760660
                  fax: +44 (0)7092 348865
                  web: www.go-faster.co.uk
                  mailto:david.kurtz@...
                  Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
                  PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
                  -----Original Message-----
                  From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On Behalf Of rsalido@...
                  Sent: 28 October 2005 17:49
                  To: psftdba@yahoogroups.com
                  Subject: RE: PeopleSoft DBA Forum Re: Shared Pool and Cursor_Sharing

                  David,
                   
                     So, seeing low parse ratios is not uncommon on an Oracle DB running Peoplesoft? How do you approach improving those ratios?
                   
                  Roberto.

                  David Kurtz <info2@...> wrote:
                  This thread has probably gone cold now, but I wrote this reply and forgot to
                  send it.

                  There is no doubt that PeopleSoft uses literal values in SQL where bind
                  variables would be more appropriate.  But I have had some bad experiences
                  with cursor_sharing, and I would not recommend its database-wide use.
                  Cursor sharing does not mix well with the use of histograms (even with bind
                  variable peeking in 9i), and I frequently apply histograms to the flag and
                  process instance columns in Financials systems.

                  Most of the literal values, and hence the parse overhead occurs in
                  Application Engine programs, because most bind variables are converted to
                  literals when constructing the SQL to be submitted to the database.  This is
                  why Financials systems are particularly affected by this problem.  It would
                  be possible to use the ReuseStatement flag at step level to make it use bind
                  variables instead of literals.  The problem that there are a lot of AE
                  steps, and ReUseStatemnt is set to 'No; on most of them - and this would be
                  a customisation.

                  You could set cursor sharing selectively at session level with either an ON
                  LOGON trigger or a trigger on PSPRCSRQST - then you could target specific AE
                  programs, but it is often the same AE programs that need to use the
                  histograms.

                  nVision also uses literals, but here parse is not usually the dominant
                  problem.  Rather, that you are doing DSS style queries on tables that are
                  part of an OLTP sytem.  There is a presentation on my website about how to
                  use the nVision performance options.

                  If you have a Finanicals system on a unicode database then you are in deep
                  trouble because the con$ table is not cached in the shared pool, and so
                  whenever a statement is parsed, the length checking constraints that
                  PeopleSoft created have to be reloaded.  This increases the parse overhead
                  by up to 300%.



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

                  > -----Original Message-----
                  > From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On Behalf
                  > Of James Blanding
                  > Sent: 18 October 2005 15:21
                  > To: psftdba@yahoogroups.com
                  > Subject: PeopleSoft DBA Forum Re: Shared Pool and Cursor_Sharing
                  >
                  >
                  > nVision is particularly bad about bind variables.  I had a case open
                  > for 10 months last year trying to convince the GSC it needed to be
                  > redesigned.  They did eventually admit that it was very inefficient
                  > under certain circumstances.  In the end, though, it wound up as an
                  > Enhancement Request for PTools 8.42.  A year later, the Incident still
                  > has a status of "Hold for Future."  Such is life...
                  >
                  > --James
                  >
                  > --- In psftdba@yahoogroups.com, <rsalido@y...> wrote:
                  > >
                  > > Hello,
                  > >     I have been using statspack to monitor our database and I am a
                  > bit concerned about what I see. (Please see attached file).
                  > >     The memory usage seems a little high. Also, the Soft Parse %
                  > seems very low which might be an indicator of excessive hard parsing
                  > due to a bind variable problem.
                  > >
                  > > 1.- What percentage of you shared pool memory do you see your
                  > systems using?
                  > > 2.- Do you use cursor_sharing=force?
                  > > 3.- Does PeopleSoft have a bind variable usage problem? I see a lot
                  > of SQL using literals out there.
                  > >
                  > > Thanks a lot.
                  > >
                  > >
                  > >
                  > >
                  > >
                  > > ---------------------------------
                  > >  Yahoo! Music Unlimited - Access over 1 million songs. Try it free.
                  > >
                  >
                  >
                  >
                  >
                  >
                  >
                  > 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











                  Yahoo! FareChase - Search multiple travel sites in one click.


                  Yahoo! FareChase - Search multiple travel sites in one click.

                • rsalido
                  Hi, It has been a while since the last time we encountered the ora- 04031 but it happened again. Is anybody else out there having this problem? How do you
                  Message 8 of 10 , Jun 30, 2006
                  • 0 Attachment
                    Hi,
                    It has been a while since the last time we encountered the ora-
                    04031 but it happened again. Is anybody else out there having this
                    problem?
                    How do you monitor your shared pools? Do you keep an eye on free
                    space and/or fragmentation?

                    Regards,
                    Roberto.
                    --- In psftdba@yahoogroups.com, "David Kurtz" <info2@...> wrote:
                    >
                    > First of all, don't focus on the parse ratio, look first at the
                    amount of
                    > time you spend parsing. If that is not significant then you don't
                    have a
                    > problem.
                    > On a system without much batch processing (so not Financials and
                    not Payroll
                    > or T&L), parse is not normally a problem.
                    > For these exceptions, especally on Unicode systems you may have a
                    problem
                    > with batch processing. On-line is not usually a problem in any
                    PeopleSoft
                    > 8.x product.
                    >
                    > These are very broad sweeping statements, and I am sure some people
                    will
                    > have exceptions.
                    > For instance if the database server is short of CPU, parse time can
                    go up,
                    > and you can start to have a problem.
                    >
                    > PeopleSoft usually benefits from a large shared pool, but if the
                    statements
                    > are full of literals then it doesn't how big you make the shared
                    pool if
                    > each statement gets executed just once. The real problem is not
                    that the
                    > database is poorly tuned, but the application is poorly designed
                    and coded.
                    > However, your company has gone out and bought a packaged
                    application and you
                    > have to live with it!
                    >
                    > My experience is that there are usually much larger savings to be
                    made with
                    > a little SQL performance tuning, and these gains are much larger
                    than the
                    > parse times.
                    > However, when you have tuned the SQL as far as is reasonable, and
                    parse time
                    > is becoming a significant part of the system response time you are
                    left with
                    > few choices.
                    > Your options are
                    > i) use cursor sharing, but I would recommend doing so quite
                    selectively -
                    > use a trigger at session level for particular processes.
                    Demonstrate that
                    > doing so does really have a beneficial effect.
                    > ii) change the code. In the case of AE programs set ReuseStatement
                    for the
                    > most frequently executed steps (which you can determine from the
                    batch
                    > timings tables). This problem with this is that it is, of course, a
                    > customisation. You have to made a judgement as to whether the
                    overhead of
                    > the customisation is worth the benefit in performance - that's a
                    political
                    > decision. Customisation is a dirty word on some projects.
                    >
                    > regards
                    > _________________________
                    > David Kurtz
                    > Go-Faster Consultancy Ltd.
                    > tel: +44 (0)7771 760660
                    > fax: +44 (0)7092 348865
                    > web: www.go-faster.co.uk
                    > mailto:david.kurtz@...
                    > Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
                    > PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
                    > -----Original Message-----
                    > From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On
                    Behalf Of
                    > rsalido@...
                    > Sent: 28 October 2005 17:49
                    > To: psftdba@yahoogroups.com
                    > Subject: RE: PeopleSoft DBA Forum Re: Shared Pool and
                    Cursor_Sharing
                    >
                    >
                    > David,
                    >
                    > So, seeing low parse ratios is not uncommon on an Oracle DB
                    running
                    > Peoplesoft? How do you approach improving those ratios?
                    >
                    > Roberto.
                    >
                    > David Kurtz <info2@...> wrote:
                    > This thread has probably gone cold now, but I wrote this reply
                    and
                    > forgot to
                    > send it.
                    >
                    > There is no doubt that PeopleSoft uses literal values in SQL
                    where bind
                    > variables would be more appropriate. But I have had some bad
                    > experiences
                    > with cursor_sharing, and I would not recommend its database-
                    wide use.
                    > Cursor sharing does not mix well with the use of histograms
                    (even with
                    > bind
                    > variable peeking in 9i), and I frequently apply histograms to
                    the flag
                    > and
                    > process instance columns in Financials systems.
                    >
                    > Most of the literal values, and hence the parse overhead occurs
                    in
                    > Application Engine programs, because most bind variables are
                    converted
                    > to
                    > literals when constructing the SQL to be submitted to the
                    database.
                    > This is
                    > why Financials systems are particularly affected by this
                    problem. It
                    > would
                    > be possible to use the ReuseStatement flag at step level to
                    make it use
                    > bind
                    > variables instead of literals. The problem that there are a
                    lot of AE
                    > steps, and ReUseStatemnt is set to 'No; on most of them - and
                    this would
                    > be
                    > a customisation.
                    >
                    > You could set cursor sharing selectively at session level with
                    either an
                    > ON
                    > LOGON trigger or a trigger on PSPRCSRQST - then you could target
                    > specific AE
                    > programs, but it is often the same AE programs that need to use
                    the
                    > histograms.
                    >
                    > nVision also uses literals, but here parse is not usually the
                    dominant
                    > problem. Rather, that you are doing DSS style queries on
                    tables that
                    > are
                    > part of an OLTP sytem. There is a presentation on my website
                    about how
                    > to
                    > use the nVision performance options.
                    >
                    > If you have a Finanicals system on a unicode database then you
                    are in
                    > deep
                    > trouble because the con$ table is not cached in the shared
                    pool, and so
                    > whenever a statement is parsed, the length checking constraints
                    that
                    > PeopleSoft created have to be reloaded. This increases the
                    parse
                    > overhead
                    > by up to 300%.
                    >
                    >
                    >
                    > regards
                    > _________________________
                    > David Kurtz
                    > Go-Faster Consultancy Ltd.
                    > tel: +44 (0)7771 760660
                    > fax: +44 (0)7092 348865
                    > web: www.go-faster.co.uk
                    > mailto:david.kurtz@...
                    > Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
                    > PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
                    >
                    > > -----Original Message-----
                    > > From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]
                    On
                    > Behalf
                    > > Of James Blanding
                    > > Sent: 18 October 2005 15:21
                    > > To: psftdba@yahoogroups.com
                    > > Subject: PeopleSoft DBA Forum Re: Shared Pool and
                    Cursor_Sharing
                    > >
                    > >
                    > > nVision is particularly bad about bind variables. I had a
                    case open
                    > > for 10 months last year trying to convince the GSC it needed
                    to be
                    > > redesigned. They did eventually admit that it was very
                    inefficient
                    > > under certain circumstances. In the end, though, it wound up
                    as an
                    > > Enhancement Request for PTools 8.42. A year later, the
                    Incident still
                    > > has a status of "Hold for Future." Such is life...
                    > >
                    > > --James
                    > >
                    > > --- In psftdba@yahoogroups.com, <rsalido@y...> wrote:
                    > > >
                    > > > Hello,
                    > > > I have been using statspack to monitor our database and
                    I am a
                    > > bit concerned about what I see. (Please see attached file).
                    > > > The memory usage seems a little high. Also, the Soft
                    Parse %
                    > > seems very low which might be an indicator of excessive hard
                    parsing
                    > > due to a bind variable problem.
                    > > >
                    > > > 1.- What percentage of you shared pool memory do you see
                    your
                    > > systems using?
                    > > > 2.- Do you use cursor_sharing=force?
                    > > > 3.- Does PeopleSoft have a bind variable usage problem? I
                    see a lot
                    > > of SQL using literals out there.
                    > > >
                    > > > Thanks a lot.
                    > > >
                    > > >
                    > > >
                    > > >
                    > > >
                    > > > ---------------------------------
                    > > > Yahoo! Music Unlimited - Access over 1 million songs. Try
                    it free.
                    > > >
                    > >
                    > >
                    > >
                    > >
                    > >
                    > >
                    > > 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
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    > --------------------------------------------------------------------
                    --------
                    > --
                    > Yahoo! FareChase - Search multiple travel sites in one click.
                    >
                    >
                    > --------------------------------------------------------------------
                    --------
                    > --
                    > Yahoo! FareChase - Search multiple travel sites in one click.
                    >
                    > 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
                    >
                    > a.. Visit your group "psftdba" on the web.
                    >
                    > 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.
                    >
                    >
                    > --------------------------------------------------------------------
                    --------
                    > --
                    >
                  Your message has been successfully submitted and would be delivered to recipients shortly.