1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Problem with SQL Area GETHITRATION post migration 9.2.0.8 through 10.2.0.4.

Discussion in 'Server Administration and Options' started by gklech, Jan 8, 2009.

  1. gklech

    gklech Guest

    Hi everyone,

    I'm a newbie here and hoping one of you enlightened gurus might have some advice for me. I have just migrated one of our core applications from 9.2.0.8 to 10.2.0.4 and onto new hardware / OS (from RHEL4 to RHEL5.1 64-bit).

    All seems pretty stable apart from one thing - AWR reports (and Spotlight) are reporting the Library Cache GETHITRATIO for the SQL Areas to be 25% with a high number of reloads (13%). I'm surprised by this firstly because there has been no modification to the application and also because the SGA size for the instance on the old environment was 1.6 GB with a Shared Pool size of 400 MB whereas on the new environment we are using an SGA_TARGET of 12 GB which has resulted in a Shared Pool size of 1.6 GB.

    Now I know that this third-party application does not use bind variables so CURSOR_SHARING has been set to FORCE for a year (long before we migrated) so I hope you can see my reasons for being surprised. Shared Pool usage sits at around 69% on the new environment so it could do with being reduced but I would have expected Oracle to dynamically resize it. I am considering going back to manual management of the memory pools but would appreciated any insight from yourselves first.

    Kind regards,

    Giles
     
  2. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
  3. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    I can offer a couple of points to look for more details:

    1. Query V$DB_OBJECT_CACHE/V$SQLAREA to see if problems are limited to certain objects or spread across different objects. If invalidations are high, then it might be worth investigating the (invalidated object's) underlying objects.

    2. Check whether the statements in your application use bind-variables. Using CURSOR_SHARING = FORCE is actually a stop gap solution for applications which are not using bind variables properly.
     
  4. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    If you don't have Metalink access, Basically the Note no. 5452234.8 as simply_dba has mentioned above, refers to a BUG where The "GETS" column in V$LIBRARYCACHE can be wrong leading to reports of an incorrect hit ratio on the library cache.

    The note mentions that Versions >= 10.2 but < 11 are believed to be affected.