RE: Few questions about V$SQLAREA
- From: John Kanagaraj <john.kanagaraj@xxxxxxx>
- To: "'new_dba_on_the_block@xxxxxxxxx'" <new_dba_on_the_block@xxxxxxxxx>, ORACLE-L@xxxxxxxxxxxxx
- Date: Tue, 18 Jan 2005 15:01:26 -0800
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
>parsed?
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.
Hth,
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)
http://tahiti.oracle.com - Manuals for DBAs (English only)
http://www.bibleserver.com - Manual for Life (in English, Deutsch, French,
Italian, Spanish, Portugese, Turkish,...)
** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
--
//www.freelists.org/webpage/oracle-l
Other related posts:
