Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Few questions about V$SQLAREA

RE: Few questions about V$SQLAREA

From: John Kanagaraj <>
Date: Tue, 18 Jan 2005 15:01:26 -0800
Message-ID: <>

Hi New DBA,

>I'm asking these questions because the DB performance
>is slow due to heavy contention on Shared Pool latch.
>As per the statspack report, 50% of the DB time is
>spent waiting on this latch.

Good catch - you knew wehere to look!

>1. In 11.5.9, the entire DB activity is done by a
>single user APPS. In such a scenario why are there SQL
>Statements in my SQLAREA with multiple versions (upto
>7-8)? They are not custom SQLs, they come from various
>seeded forms.

You should be looking at V$SQL rather than V$SQLAREA - the latter is a group by of the former and thus is able to provide a count of the child cursors. (You will see 'MULTIPLE CHILDREN PRESENT' under OPTIMIZER_MODE for such SQLs). Access to V$SQLAREA is latch intensive - if you have any pretty GUI tool searching V$SQLAREA every few seconds or minutes, you might want to switch that off and reduce your contention!

>2. What is the significance of open_versions,
>loaded_versions and version_count. how is one
>different from the other?

Read all about it in the Oracle 9i Database Reference manual. Better still, see the end of this note.

>3. I see multiple invalidations of the SQLs. As I
>understand invalidation is done if the objects
>referred by the SQLs undergo any DDL. In the absense
>of any DDLs including running of Gather Stats, why are
>my SQLs getting invalidated?

I am not the shared pool/lib cache expert here, but my understanding is that when there is space pressure on the shared pool, the kernel layer may purge out some parts of the cursor's area that are easily recreateable to make space (this is implied in the freeable components, but I don't remember my Steve Adams' notes). In this case, the head is left behind and is marked invalid. On reparsing, recreation is less resource intensive than may have otherwise been.

>4. Does the Parse_Count column list only the hard
>parse or it lists both hard and soft parses? If it
>lists both, how do I identify how many times a SQL was

I tend not to rely on V$SQL for a true picture of what happened. Look at V$SESSTAT for these figures. (How I wish Oracle would write out V$SESSTAT figures into the trace file before the session ends... Not even in 10g!!)

>5. What exactly is Library Cache (I probably know what
>it contains, just need to verify if my understanding
>it correct) and how is its size determined?

This is dynamically allocated from within the shared pool, and contends with the row cache (aka Data dictionary cache). The Oracle 9i Database Concepts manual and various Metalink notes must be read!

>6. Lastly, do you know of any white papers on sizing
>the Shared Pool? I want to know whether my Shared Pool
>is large enough or is too large.

I would recommend Steve Adam's most excellent book - Oracle8i Internal Services. Get it, read it, and re-read it (none of us, at least myself, grasped it first time around!) and sleep on it, and reread it some more.

John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W) - Manuals for DBAs (English only) - Manual for Life (in English, Deutsch, French, Italian, Spanish, Portugese, Turkish,...)

Received on Tue Jan 18 2005 - 21:50:02 CST

Original text of this message