RE: Oracle Library Cache

From: Vana <mvshelton_at_chartermi.net>
Date: Wed, 23 Oct 2013 21:08:12 -0400
Message-ID: <001501ced055$83603140$8a2093c0$_at_chartermi.net>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark Bobak
Sent: Wednesday, October 23, 2013 4:32 PM To: mvshelton_at_chartermi.net; oracle-l_at_freelists.org 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_at_chartermi.net" <mvshelton_at_chartermi.net> 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
>
>
>--
>http://www.freelists.org/webpage/oracle-l
>
>
>
>

--
http://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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Oct 24 2013 - 03:08:12 CEST