RE: Oracle Library Cache
- From: "Vana" <mvshelton@xxxxxxxxxxxxx>
- To: <Mark.Bobak@xxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 23 Oct 2013 21:08:12 -0400
I fully agree with you that tuning from this perspective would not be a good
idea. I am just trying to figure out if this is related to a library cache
mutex x wait which I believe maybe related to a vpd policy. I noticed
these high miss percentages. I cannot find SQL AREA STATS or SQL AREA
BUILD documented and it makes want to know the answer. The library cache
mutex x wait was fixed by recreating the VPD policy which make me
suspicious. To explain further I had about 6 query's running in parallel 8
on a Oracle RAC environment. The query's were running with parallel local
so each query was not running across the cluster. These query's were
selecting against 2 very large range hash partitioned tables.
Thanks, Matt
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Mark Bobak
Sent: Wednesday, October 23, 2013 4:32 PM
To: mvshelton@xxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Oracle Library Cache
Hi Matt,
My first thought would be, why were you compelled to look at that section of
the AWR report? Do you have a performance problem? If so, what performance
indicators pointed you in the direction of library cache hit ratios?
In general, looking at hit ratios in isolation is probably not a valid
tuning strategy.
And no, I must admit, I don¹t know what ŒSQL AREA STATS¹ or ŒSQL AREA BUILD¹
means, in this context.
-Mark
On 10/23/13, 2:32 PM, "mvshelton@xxxxxxxxxxxxx" <mvshelton@xxxxxxxxxxxxx>
wrote:
>SQL> select namespace,gets,gethits,round(gethitratio,2) gethitratio
> 2 from V$LIBRARYCACHE
> 3 where namespace like 'SQL%';
>
>NAMESPACE
>GETS
>----------------------------------------------------------------
>----------
> GETHITS GETHITRATIO
>---------- -----------
>SQL AREA
>13927433
> 13762659 .99
>
>SQL AREA STATS
>201188
> 27167 .14
>
>SQL AREA BUILD
>265640
> 109679 .41
>
>I noticed in my AWR report that a database had high pct misses for SQL
>AREA STATS of 87.56% and SQL AREA BUILD of 37% but I could not find any
>information that describes these library cache waits. I can query from
>v$library cache and get the hit ratio but I am not sure what this stat
>is. Any help would be appreciated.
>
>Thanks, Matt
>
>
>--
>//www.freelists.org/webpage/oracle-l
>
>
>
>
--
//www.freelists.org/webpage/oracle-l
-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.3426 / Virus Database: 3222/6775 - Release Date: 10/23/13
--
//www.freelists.org/webpage/oracle-l
Other related posts:
