V$LIBRARYCACHE - Poor hit ratios

[samdba]

Hi all,

Foll. is output from v$librarycache.

It shows very poor hit ratios for INDEX namespace. Also very high RELOADS for SQL AREA and TABLE/PROCEDURE.

Why it shows very high INVALIDATIONS for SQL AREA?

I have pinned all the packages. I regulary rebuild/compress indexes. Our SHARED_POOL_SIZE is 491462656, which is high for our size of shop.

Over all Library hit ration is more than 96%.

Why it shows poor figures for Indexes and RELOADS?



[code]
NAMESPACE GETHITRATIO PINHITRATIO RELOADS INVALIDATIONS
SQL AREA 0.698951541090972 0.938545804045995 2248 8506
TABLE/PROCEDURE 0.976092429193028 0.976542355182859 2100 0
BODY 0.78415406488303 0.782785562084329 3 0
TRIGGER 0.926737967914439 0.833155080213904 97 0
INDEX 0.37037037037037 0.37037037037037 0 0
CLUSTER 0.99606962380685 0.990448199853049 3 0
OBJECT 1 1 0 0
PIPE 0.99971678074109 0.999716794109317 0 0
[\code]

Any suggesions?

Thanks in Adv.




[Edited by samdba on 04-29-2002 at 02:33 PM]

[oravijay]

Hi,

Whenever the structure of the object changes all the sql statement referring to this object
will be marked as 'Invalidated'. Any reference to those statements from there on should be
re-parsed.

As far as Indexes are considered, check whether the frequently accessed table's are properly
indexed.

Vijay.

[oravijay]

Hi,

Whenever the structure of the object changes all the sql statement referring to this object
will be marked as 'Invalidated'. Any reference to those statements from there on should be
re-parsed.

As far as Indexes are considered, check whether the frequently accessed table's are properly
indexed.

Also check the output of the Explain plan to find out whether Oracle retrieves the record
using Indexes.

Vijay.

[anandkl]

It could be because your index is scaning more blocks for reterving the record.
Just use autotrace to see how many physical I/O i happening.

regards
anandkl

[samdba]

Originally posted by oravijay
Hi,
Whenever the structure of the object changes all the sql statement referring to this object
will be marked as 'Invalidated'. Any reference to those statements from there on should be
re-parsed.

Nobody except me changes the structure of objects. I've not made any DDL changes in objects since the instance startup than why it shows invalidations so high?

As far as Indexes are considered, check whether the frequently accessed table's are properly
indexed.

Also check the output of the Explain plan to find out whether Oracle retrieves the record
using Indexes.

We use Oracle Financial Apps so there is no doubt that its standard queries are not using indexes.


Thanks,