Few questions about V$SQLAREA
- From: New DBA <new_dba_on_the_block@xxxxxxxxx>
- To: ORACLE-L@xxxxxxxxxxxxx
- Date: Sat, 15 Jan 2005 09:39:05 -0800 (PST)
Hi All,
This is an Oracle apps 11.5.9 DB, version 9.2.0.5
I have a few questions about v$sqlarea and its
contents.
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.
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.
2. What is the significance of open_versions,
loaded_versions and version_count. how is one
different from the other?
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?
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?
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?
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.
New DBA
__________________________________
Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses.
http://promotions.yahoo.com/new_mail
--
//www.freelists.org/webpage/oracle-l
Other related posts:
