v$db_object_cache question

Hi

Am looking into minimizing parsing/object loading etc from a supplied application. While looking into this, I was checking those objects in the cache that could do with pinning and came across multiple instances of identical SQL.

There is one literal in there of the form "...start_date >=
nvl(:1,to_date('01-JAN-1901','DD-MON-YYYY')...", but that is constant (obv.
given the date). The rest are bind variables.

At the time of viewing (quiet period), I have 15 occurences of this identical SQL in the cache with an average of 5 executions each. Sharable mem for each is around the 150k mark so needless to say I'm interested in cutting this down to 1 instance.

Can anyone point me to why this is? I'm no beginner - so God help me if this is FAQ! - but I can't quite work this one out. I know Oracle used to be picky about punctuation for spotting repetitive SQL - and I've yet to determine if this is coming from a form (most likely), or a package or what - but I thought Oracle 8 (I'm on 8.1.6R2 btw), umm, 're-formatted' or stripped your punctuation on its way in thus shortening the chances of a parse/reload?

Thanks in advance for any pointers.

Andrew Received on Wed Feb 21 2001 - 11:31:33 CST