Re: Shared Pool info - V$sqlarea.

Particularly, this can be caused by a huge amount of logical IO (proportionately dwarfing the amount of disk io and resulting in a falsely 'positive' hit ratio). Take a look at your sqlarea again and look at buffer_gets/executions - that may kick up some queries doing more logical io than you want.

George
On Tuesday, June 5, 2001, at 10:40 PM, Jared Still wrote:

>
> Did you say 99% hit ratio?
>
> Contrary to a once popular opinion, that is
> not really a good thing.
>
> It often means there's a cartesian product
> in a join, caused by not fully specifying
> the join key. At least that's the reason
> I've usually seen for it.
>
> Keeps disk access down though. :)
>
> Jared
>
> On Tuesday 05 June 2001 05:25, Raj Gopalan wrote:
>> Thanks Chris.
>>
>> The problem I am facing is 100% CPU usage and memory paging out at
>> times. I
>> tought the starting point is v$sqlarea. Purchasing of Precise SQL or
>> SQL
>> Vision can not happen here immediately. The RAM size is 512MB and SGA
>> is
>> 210MB. The buffer cache hit ratio is 99%.
>>
>> I was just wondering is there any way to find out the cause of this
>> problem?
>>
>> TIA
>>
>> Cheers
>>
>> Raj
>>
>> -----Original Message-----
>> Sent: 01 June 2001 19:51
>> To: Multiple recipients of list ORACLE-L
>>
>>
>> Oracle has no guarentee how long statistics and plans will be
>> available for
>> statements, depending on the activity of the database they may be
>> there for
>> 2 seconds they may be there for 2 weeks.
>>
>> Products like Precise SQL and SQL Vision Lab help in that manor where
>> they
>> capture transactions and activity continuously in a non-intrusive
>> manor.
>> This is the only guarenteed way to get 99.999% of the transactions.
>>
>>
>> "Walking on water and developing software from a specification are
>> easy if
>> both are frozen."
>>
>> Christopher R. Spence
>> Oracle DBA
>> Fuelspot
>>
>>
>>
>> -----Original Message-----
>> Sent: Friday, June 01, 2001 12:16 PM
>> To: Multiple recipients of list ORACLE-L
>>
>>
>> DBAs
>>
>> The statistics in v$sqlarea is getting flushed very often.
>>
>> In the morning I found the a query which has more than 10,000 disk
>> reads as
>> the top one. But now the top most query with disk reads has not more
>> than
>> 100 disk reads. I have not bouncd the DB or flushed the shared pool.
>> The
>> overall library cache hit ratio is 97%.where us SQLAREA hit ratio is
>> 60%.
>>
>> Any Idea what could be reason?
>>
>> TIA,
>>
>> Raj
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jared Still
> INET: jkstill_at_cybcon.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: George Schlossnagle
  INET: george_at_omniti.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).